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

CREATE TABLE OBJECT_COMMENT (
    ID                             bigint NOT NULL
    , OBJECT_ID                      bigint          NOT NULL
    , OBJECT_TYPE                    int8            NOT NULL
    , INFO_TYPE                      int8            NOT NULL
    , SEQUENCE_NUMBER                int8            NOT NULL
    , TAG                            lvarchar(64)        NULL
    , DESCRIPTION                    lvarchar(1900)  NOT NULL
    , CREATOR_U_ID                   bigint          NOT NULL
    , CREATE_TS                      bigint          NOT NULL
    , CHANGER_U_ID                   bigint          NOT NULL
    , CHANGE_TS                      bigint          NOT NULL
    , VALID_FROM                   bigint NOT NULL
    , VALID_TO                     bigint NOT NULL
);
CREATE INDEX PK_OBJECT_COMMENT
ON OBJECT_COMMENT(ID);
CREATE VIEW SCI_C_OBJECT_COMMENT (ID,OBJECT_ID,OBJECT_TYPE,INFO_TYPE,SEQUENCE_NUMBER,TAG,DESCRIPTION,CREATOR_U_ID,CREATE_TS,CHANGER_U_ID,CHANGE_TS) AS
SELECT 
    ID 
    , OBJECT_ID                      AS OBJECT_ID
    , DECODE (OBJECT_TYPE, 9, 'JOB_DEFINITION', 2, 'EXIT_STATE_DEFINITION', 3, 'EXIT_STATE_PROFILE', 4, 'EXIT_STATE_MAPPING', 5, 'EXIT_STATE_TRANSLATION', 6, 'FOLDER', 15, 'SCOPE', 10, 'NAMED_RESOURCE', 31, 'NICE_PROFILE', 11, 'RESOURCE', 1, 'ENVIRONMENT', 7, 'FOOTPRINT', 13, 'RESOURCE_STATE_DEFINITION', 14, 'RESOURCE_STATE_PROFILE', 12, 'RESOURCE_STATE_MAPPING', 8, 'USER', 16, 'TRIGGER', 17, 'JOB', 18, 'EVENT', 19, 'INTERVAL', 20, 'SCHEDULE', 22, 'SCHEDULED_EVENT', 21, 'GROUP', 23, 'PARAMETER', 24, 'POOL', 25, 'DISTRIBUTION', 29, 'WATCH_TYPE', 30, 'OBJECT_MONITOR') AS OBJECT_TYPE
    , DECODE (INFO_TYPE, 0, 'TEXT', 1, 'URL') AS INFO_TYPE
    , SEQUENCE_NUMBER                AS SEQUENCE_NUMBER
    , TAG                            AS TAG
    , DESCRIPTION                    AS DESCRIPTION
    , CREATOR_U_ID                   AS CREATOR_U_ID
    , dbinfo ('utc_to_datetime', round(mod(CREATE_TS, 1125899906842624)/1000)) AS CREATE_TS
    , CHANGER_U_ID                   AS CHANGER_U_ID
    , dbinfo ('utc_to_datetime', round(mod(CHANGE_TS, 1125899906842624)/1000)) AS CHANGE_TS
  FROM OBJECT_COMMENT
 WHERE VALID_TO = 9223372036854775807;
CREATE VIEW SCI_V_OBJECT_COMMENT (ID,OBJECT_ID,OBJECT_TYPE,INFO_TYPE,SEQUENCE_NUMBER,TAG,DESCRIPTION,CREATOR_U_ID,CREATE_TS,CHANGER_U_ID,CHANGE_TS, VALID_FROM, VALID_TO) AS
SELECT 
    ID 
    , OBJECT_ID                      AS OBJECT_ID
    , DECODE (OBJECT_TYPE, 9, 'JOB_DEFINITION', 2, 'EXIT_STATE_DEFINITION', 3, 'EXIT_STATE_PROFILE', 4, 'EXIT_STATE_MAPPING', 5, 'EXIT_STATE_TRANSLATION', 6, 'FOLDER', 15, 'SCOPE', 10, 'NAMED_RESOURCE', 31, 'NICE_PROFILE', 11, 'RESOURCE', 1, 'ENVIRONMENT', 7, 'FOOTPRINT', 13, 'RESOURCE_STATE_DEFINITION', 14, 'RESOURCE_STATE_PROFILE', 12, 'RESOURCE_STATE_MAPPING', 8, 'USER', 16, 'TRIGGER', 17, 'JOB', 18, 'EVENT', 19, 'INTERVAL', 20, 'SCHEDULE', 22, 'SCHEDULED_EVENT', 21, 'GROUP', 23, 'PARAMETER', 24, 'POOL', 25, 'DISTRIBUTION', 29, 'WATCH_TYPE', 30, 'OBJECT_MONITOR') AS OBJECT_TYPE
    , DECODE (INFO_TYPE, 0, 'TEXT', 1, 'URL') AS INFO_TYPE
    , SEQUENCE_NUMBER                AS SEQUENCE_NUMBER
    , TAG                            AS TAG
    , DESCRIPTION                    AS DESCRIPTION
    , CREATOR_U_ID                   AS CREATOR_U_ID
    , dbinfo ('utc_to_datetime', round(mod(CREATE_TS, 1125899906842624)/1000)) AS CREATE_TS
    , CHANGER_U_ID                   AS CHANGER_U_ID
    , dbinfo ('utc_to_datetime', round(mod(CHANGE_TS, 1125899906842624)/1000)) AS CHANGE_TS
    , VALID_FROM
    , VALID_TO
  FROM OBJECT_COMMENT;
