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

CREATE TABLE [GRANTS] (
    [ID]                           decimal (38) NOT NULL
    , [OBJECT_ID]                    decimal (38)    NOT NULL
    , [G_ID]                         decimal (38)    NOT NULL
    , [OBJECT_TYPE]                  integer         NOT NULL
    , [PRIVS]                        decimal (38)    NOT NULL
    , [DELETE_VERSION]               decimal (38)        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_GRANTS
ON [GRANTS]([ID]);
go
CREATE VIEW SCI_GRANTS AS
SELECT 
    ID 
    , [OBJECT_ID]                    AS [OBJECT_ID]
    , [G_ID]                         AS [G_ID]
    , CASE [OBJECT_TYPE] WHEN 1 THEN 'ENVIRONMENT' WHEN 18 THEN 'EVENT' WHEN 6 THEN 'FOLDER' WHEN 19 THEN 'INTERVAL' WHEN 17 THEN 'JOB' WHEN 9 THEN 'JOB_DEFINITION' WHEN 10 THEN 'NAMED_RESOURCE' WHEN 20 THEN 'SCHEDULE' WHEN 22 THEN 'SCHEDULED_EVENT' WHEN 15 THEN 'SCOPE' WHEN 21 THEN 'GROUP' WHEN 11 THEN 'RESOURCE' WHEN 2 THEN 'EXIT_STATE_DEFINITION' WHEN 31 THEN 'NICE_PROFILE' WHEN 3 THEN 'EXIT_STATE_PROFILE' WHEN 4 THEN 'EXIT_STATE_MAPPING' WHEN 5 THEN 'EXIT_STATE_TRANSLATION' WHEN 13 THEN 'RESOURCE_STATE_DEFINITION' WHEN 14 THEN 'RESOURCE_STATE_PROFILE' WHEN 29 THEN 'WATCH_TYPE' WHEN 12 THEN 'RESOURCE_STATE_MAPPING' WHEN 7 THEN 'FOOTPRINT' WHEN 8 THEN 'USER' WHEN 30 THEN 'OBJECT_MONITOR' WHEN 0 THEN 'SYSTEM' END AS [OBJECT_TYPE]
    , [PRIVS]                        AS [PRIVS]
    , [DELETE_VERSION]               AS [DELETE_VERSION]
    , [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 [GRANTS];
go
