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

CREATE TABLE [SCOPE] (
    [ID]                           decimal (38) NOT NULL
    , [NAME]                         varchar(64)     NOT NULL
    , [OWNER_ID]                     decimal (38)    NOT NULL
    , [PARENT_ID]                    decimal (38)        NULL
    , [TYPE]                         integer         NOT NULL
    , [IS_TERMINATE]                 integer             NULL
    , [HAS_ALTEREDCONFIG]            integer             NULL
    , [IS_SUSPENDED]                 integer             NULL
    , [IS_ENABLED]                   integer             NULL
    , [IS_REGISTERED]                integer             NULL
    , [STATE]                        integer             NULL
    , [PASSWD]                       varchar(64)         NULL
    , [SALT]                         varchar(64)         NULL
    , [METHOD]                       integer         NOT NULL
    , [PID]                          varchar(32)         NULL
    , [NODE]                         varchar(64)         NULL
    , [ERRMSG]                       varchar(256)        NULL
    , [LAST_ACTIVE]                  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
    , [INHERIT_PRIVS]                decimal (38)    NOT NULL
);
go
CREATE UNIQUE INDEX PK_SCOPE
ON [SCOPE]([ID]);
go
CREATE VIEW SCI_SCOPE AS
SELECT 
    ID 
    , [NAME]                         AS [NAME]
    , [OWNER_ID]                     AS [OWNER_ID]
    , [PARENT_ID]                    AS [PARENT_ID]
    , CASE [TYPE] WHEN 1 THEN 'SCOPE' WHEN 2 THEN 'SERVER' END AS [TYPE]
    , CASE [IS_TERMINATE] WHEN 1 THEN 'TRUE' WHEN 0 THEN 'FALSE' END AS [IS_TERMINATE]
    , CASE [HAS_ALTEREDCONFIG] WHEN 1 THEN 'TRUE' WHEN 0 THEN 'FALSE' END AS [HAS_ALTEREDCONFIG]
    , CASE [IS_SUSPENDED] WHEN 1 THEN 'TRUE' WHEN 0 THEN 'FALSE' END AS [IS_SUSPENDED]
    , CASE [IS_ENABLED] WHEN 1 THEN 'TRUE' WHEN 0 THEN 'FALSE' END AS [IS_ENABLED]
    , CASE [IS_REGISTERED] WHEN 1 THEN 'TRUE' WHEN 0 THEN 'FALSE' END AS [IS_REGISTERED]
    , CASE [STATE] WHEN 1 THEN 'NOMINAL' WHEN 2 THEN 'NONFATAL' WHEN 3 THEN 'FATAL' END AS [STATE]
    , [PID]                          AS [PID]
    , [NODE]                         AS [NODE]
    , [ERRMSG]                       AS [ERRMSG]
    , [LAST_ACTIVE]                  AS [LAST_ACTIVE]
    , [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]
    , [INHERIT_PRIVS]                AS [INHERIT_PRIVS]
  FROM [SCOPE];
go
