-- Copyright (C) 2001,2002 topIT Informationstechnologie GmbH
-- Copyright (C) 2003-2014 independIT Integrative Technologies GmbH

CREATE TABLE [ENTITY_VARIABLE] (
    [ID]                           decimal (38) NOT NULL
    , [SME_ID]                       decimal (38)    NOT NULL
    , [NAME]                         varchar(64)     NOT NULL
    , [VALUE]                        varchar(256)        NULL
    , [IS_LOCAL]                     integer         NOT NULL
    , [EV_LINK]                      decimal (38)        NULL
    , [IS_LONG]                      integer         NOT NULL
    , [CREATOR_U_ID]                 decimal (38)    NOT NULL
    , [CREATE_TS]                    decimal (38)    NOT NULL
    , [CHANGER_U_ID]                 decimal (38)    NOT NULL
    , [CHANGE_TS]                    decimal (38)    NOT NULL
);
go
CREATE UNIQUE INDEX PK_ENTITY_VARIABLE
ON [ENTITY_VARIABLE]([ID]);
go
CREATE VIEW SCI_ENTITY_VARIABLE AS
SELECT 
    ID 
    , [SME_ID]                       AS [SME_ID]
    , [NAME]                         AS [NAME]
    , [VALUE]                        AS [VALUE]
    , CASE [IS_LOCAL] WHEN 1 THEN 'TRUE' WHEN 0 THEN 'FALSE' END AS [IS_LOCAL]
    , CASE [IS_LONG] WHEN 1 THEN 'TRUE' WHEN 0 THEN 'FALSE' END AS [IS_LONG]
    , [CREATOR_U_ID]                 AS [CREATOR_U_ID]
    , dateadd(ss, round(([CREATE_TS] % 1125899906842624)/1000, 0), '1970-01-01') AS [CREATE_TS]
    , [CHANGER_U_ID]                 AS [CHANGER_U_ID]
    , dateadd(ss, round(([CHANGE_TS] % 1125899906842624)/1000, 0), '1970-01-01') AS [CHANGE_TS]
  FROM [ENTITY_VARIABLE];
go
CREATE TABLE ARC_ENTITY_VARIABLE (
    ID                             decimal (38) NOT NULL
    , [SME_ID]                       decimal (38)     NULL
    , [NAME]                         varchar(64)      NULL
    , [VALUE]                        varchar(256)     NULL
    , [IS_LOCAL]                     integer          NULL
    , [EV_LINK]                      decimal (38)     NULL
    , [IS_LONG]                      integer          NULL
    , [CREATOR_U_ID]                 decimal (38)     NULL
    , [CREATE_TS]                    decimal (38)     NULL
    , [CHANGER_U_ID]                 decimal (38)     NULL
    , [CHANGE_TS]                    decimal (38)     NULL
);
go
