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

CREATE TABLE `ENTITY_VARIABLE` (
    `ID`                           decimal(20) NOT NULL
    , `SME_ID`                       decimal(20)     NOT NULL
    , `NAME`                         varchar(64)     NOT NULL
    , `VALUE`                        varchar(256)        NULL
    , `IS_LOCAL`                     integer         NOT NULL
    , `EV_LINK`                      decimal(20)         NULL
    , `IS_LONG`                      integer         NOT NULL
    , `CREATOR_U_ID`                 decimal(20)     NOT NULL
    , `CREATE_TS`                    decimal(20)     NOT NULL
    , `CHANGER_U_ID`                 decimal(20)     NOT NULL
    , `CHANGE_TS`                    decimal(20)     NOT NULL
) ENGINE = INNODB;
CREATE UNIQUE INDEX PK_ENTITY_VARIABLE
ON `ENTITY_VARIABLE`(`ID`);
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`
    , from_unixtime((`CREATE_TS` & ~1125899906842624)/1000) AS `CREATE_TS`
    , `CHANGER_U_ID`                 AS `CHANGER_U_ID`
    , from_unixtime((`CHANGE_TS` & ~1125899906842624)/1000) AS `CHANGE_TS`
  FROM `ENTITY_VARIABLE`;
CREATE TABLE ARC_ENTITY_VARIABLE (
    ID                             decimal(20) NOT NULL
    , `SME_ID`                       decimal(20)      NULL
    , `NAME`                         varchar(64)      NULL
    , `VALUE`                        varchar(256)     NULL
    , `IS_LOCAL`                     integer          NULL
    , `EV_LINK`                      decimal(20)      NULL
    , `IS_LONG`                      integer          NULL
    , `CREATOR_U_ID`                 decimal(20)      NULL
    , `CREATE_TS`                    decimal(20)      NULL
    , `CHANGER_U_ID`                 decimal(20)      NULL
    , `CHANGE_TS`                    decimal(20)      NULL
) ENGINE = INNODB;
