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

CREATE TABLE `NAMED_RESOURCE` (
    `ID`                           decimal(20) NOT NULL
    , `NAME`                         varchar(64)     NOT NULL
    , `OWNER_ID`                     decimal(20)     NOT NULL
    , `PARENT_ID`                    decimal(20)         NULL
    , `USAGE`                        integer         NOT NULL
    , `RSP_ID`                       decimal(20)         NULL
    , `FACTOR`                       float               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
    , `INHERIT_PRIVS`                decimal(20)     NOT NULL
) ENGINE = INNODB;
CREATE UNIQUE INDEX PK_NAMED_RESOURCE
ON `NAMED_RESOURCE`(`ID`);
CREATE VIEW SCI_NAMED_RESOURCE AS
SELECT 
    ID 
    , `NAME`                         AS `NAME`
    , `OWNER_ID`                     AS `OWNER_ID`
    , `PARENT_ID`                    AS `PARENT_ID`
    , CASE `USAGE` WHEN 1 THEN 'STATIC' WHEN 2 THEN 'SYSTEM' WHEN 4 THEN 'SYNCHRONIZING' WHEN 8 THEN 'CATEGORY' WHEN 3 THEN 'POOL' END AS `USAGE`
    , `RSP_ID`                       AS `RSP_ID`
    , `FACTOR`                       AS `FACTOR`
    , `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`
    , `INHERIT_PRIVS`                AS `INHERIT_PRIVS`
  FROM `NAMED_RESOURCE`;
