CREATE TABLESPACE iserver
    DATAFILE '/iws/databases/iserver.ora' SIZE 512K
    AUTOEXTEND ON
    ONLINE;

COMMIT;


DROP VIEW Select_ACLRights;
DROP VIEW Select_ACL;
DROP VIEW Select_ACLs;
DROP VIEW Select_GroupMembers;

COMMIT;


DROP TABLE ACLRights CASCADE CONSTRAINTS;
DROP TABLE ACL CASCADE CONSTRAINTS;
DROP TABLE GroupMembers CASCADE CONSTRAINTS;
DROP TABLE Groups CASCADE CONSTRAINTS;
DROP TABLE Users  CASCADE CONSTRAINTS;
DROP TABLE Computers  CASCADE CONSTRAINTS;
DROP TABLE Types CASCADE CONSTRAINTS;
DROP TABLE AccessRights CASCADE CONSTRAINTS;
DROP TABLE EventLog CASCADE CONSTRAINTS;
DROP TABLE ErrorLog CASCADE CONSTRAINTS;
DROP TABLE AccessLog CASCADE CONSTRAINTS;

COMMIT;


CREATE TABLE AccessLog
(
  Date_Time       DATE NOT NULL,
  Remote_Address  VARCHAR2(15),
  Remote_Host     VARCHAR2(128),
  Remote_User     VARCHAR2(50),
  Host            VARCHAR2(50),
  Port            NUMBER(5) NOT NULL,
  Local_Address   VARCHAR2(15),
  Content_Length  NUMBER(8) NOT NULL,
  Request_Length  NUMBER(8) NOT NULL,
  Response_Time   NUMBER(8) NOT NULL,
  Status_Code     NUMBER(8) NOT NULL,
  Error_Code      NUMBER(8) NOT NULL,
  Request_Method  VARCHAR2(10),
  Request_URI     VARCHAR2(128),
  User_Agent      VARCHAR2(128),
  Referer         VARCHAR2(128),
  Protocol        VARCHAR2(10),
  Server_Service  VARCHAR2(10)
)
TABLESPACE iserver;

COMMIT;


CREATE TABLE ErrorLog
(
  Date_Time       DATE NOT NULL,
  Exception       VARCHAR2(255),
  Message         VARCHAR2(255)
)
TABLESPACE iserver;

COMMIT;


CREATE TABLE EventLog
(
  Date_Time       DATE NOT NULL,
  Message         VARCHAR2(255)
)
TABLESPACE iserver;

COMMIT;


CREATE TABLE AccessRights
(
  ID              NUMBER(8) NOT NULL,
  Access_Right   VARCHAR2(15) UNIQUE NOT NULL,
  CONSTRAINT pkAccessRight_ID PRIMARY KEY (ID)
)
TABLESPACE iserver;

COMMIT;


CREATE TABLE Types
(
  ID              NUMBER(8) NOT NULL,
  Type            VARCHAR2(15) UNIQUE NOT NULL,
  CONSTRAINT pkType_ID PRIMARY KEY (ID)
)
TABLESPACE iserver;

COMMIT;


CREATE TABLE Users
(
  ID              NUMBER(8) NOT NULL,
  Username        VARCHAR2(15) UNIQUE NOT NULL,
  Password        VARCHAR2(15),
  CONSTRAINT pkUser_ID PRIMARY KEY (ID)
)
TABLESPACE iserver;

COMMIT;


CREATE TABLE Computers
(
  ID              NUMBER(8) NOT NULL,
  Type_ID         NUMBER(8) NOT NULL,
  Computer        VARCHAR2(128),
  CONSTRAINT pkComputer_ID PRIMARY KEY (ID)
)
TABLESPACE iserver;

COMMIT;


CREATE TABLE Groups
(
  ID              NUMBER(8) NOT NULL,
  Groupname       VARCHAR2(15) UNIQUE NOT NULL,
  CONSTRAINT pkGroup_ID PRIMARY KEY (ID)
)
TABLESPACE iserver;

COMMIT;


CREATE TABLE GroupMembers
(
  ID              NUMBER(8) NOT NULL,
  Group_ID        NUMBER(8) NOT NULL,
  User_ID         NUMBER(8) NOT NULL,
  CONSTRAINT pkGroupMember_ID PRIMARY KEY (ID)
)
TABLESPACE iserver;

COMMIT;


CREATE TABLE ACL
(
  ID              NUMBER(8) NOT NULL,
  Type_ID         NUMBER(8) NOT NULL,
  Entity_ID       NUMBER(8) NOT NULL,
  CONSTRAINT pkACL_ID PRIMARY KEY (ID)
)
TABLESPACE iserver;

COMMIT;


CREATE TABLE ACLRights
(
  ID              NUMBER(8) NOT NULL,
  ACL_ID          NUMBER(8) NOT NULL,
  AccessRight_ID  NUMBER(8) NOT NULL,
  CONSTRAINT pkACLRight_ID PRIMARY KEY (ID)
)
TABLESPACE iserver;

COMMIT;


DROP SEQUENCE seq_ACLRights;
DROP SEQUENCE seq_ACL;
DROP SEQUENCE seq_GroupMembers;
DROP SEQUENCE seq_Groups;
DROP SEQUENCE seq_Users;
DROP SEQUENCE seq_Computers;
DROP SEQUENCE seq_Types;
DROP SEQUENCE seq_AccessRights;

COMMIT;


CREATE SEQUENCE seq_ACLRights;
CREATE SEQUENCE seq_ACL;
CREATE SEQUENCE seq_GroupMembers;
CREATE SEQUENCE seq_Groups;
CREATE SEQUENCE seq_Users;
CREATE SEQUENCE seq_Computers;
CREATE SEQUENCE seq_Types;
CREATE SEQUENCE seq_AccessRights;

COMMIT;


DROP TRIGGER ti_ACLRights_insert;
DROP TRIGGER ti_ACL_insert;
DROP TRIGGER ti_GroupMembers_insert;
DROP TRIGGER ti_Groups_insert;
DROP TRIGGER ti_Users_insert;
DROP TRIGGER ti_Computers_insert;
DROP TRIGGER ti_Types_insert;
DROP TRIGGER ti_AccessRights_insert;

COMMIT;


CREATE TRIGGER ti_ACLRights_insert
BEFORE INSERT
ON ACLRights
FOR EACH ROW
BEGIN
    SELECT seq_ACLRights.nextval INTO :new.ID FROM dual;
END;
/

CREATE TRIGGER ti_ACL_insert
BEFORE INSERT
ON ACL
FOR EACH ROW
BEGIN
    SELECT seq_ACL.nextval INTO :new.ID FROM dual;
END;
/

CREATE TRIGGER ti_GroupMembers_insert
BEFORE INSERT
ON GroupMembers
FOR EACH ROW
BEGIN
    SELECT seq_GroupMembers.nextval INTO :new.ID FROM dual;
END;
/

CREATE TRIGGER ti_Groups_insert
BEFORE INSERT
ON Groups
FOR EACH ROW
BEGIN
    SELECT seq_Groups.nextval INTO :new.ID FROM dual;
END;
/

CREATE TRIGGER ti_Users_insert
BEFORE INSERT
ON Users
FOR EACH ROW
BEGIN
    SELECT seq_Users.nextval INTO :new.ID FROM dual;
END;
/

CREATE TRIGGER ti_Computers_insert
BEFORE INSERT
ON Computers
FOR EACH ROW
BEGIN
    SELECT seq_Computers.nextval INTO :new.ID FROM dual;
END;
/

CREATE TRIGGER ti_Types_insert
BEFORE INSERT
ON Types
FOR EACH ROW
BEGIN
    SELECT seq_Types.nextval INTO :new.ID FROM dual;
END;
/

CREATE TRIGGER ti_AccessRights_insert
BEFORE INSERT
ON AccessRights
FOR EACH ROW
BEGIN
    SELECT seq_AccessRights.nextval INTO :new.ID FROM dual;
END;
/

COMMIT;


CREATE VIEW Select_ACLRights AS
(
  SELECT
    ACLRights.ACL_ID AS ID, AccessRights.Access_Right
  FROM
    ACLRights, AccessRights
  WHERE
    ACLRights.AccessRight_ID = AccessRights.ID
);

COMMIT;


CREATE VIEW Select_ACLs AS
(
  SELECT
    ID, Type, Name
  FROM
  (
    SELECT
      xid AS ID, Types.Type AS Type, Groups.Groupname AS Name
    FROM
      Types, Groups,
      (
        SELECT
          ACL.ID AS xid, ACL.Type_ID AS xtype_id, ACL.Entity_ID AS xgroup_id
        FROM
          ACL
        WHERE
          ACL.Type_id =
          (
            SELECT  
              ID
            FROM
              Types
            WHERE
              Type = 'group'
          )
      )
    WHERE
        Types.ID = xtype_id
      AND
        Groups.ID = xgroup_id
  )
  UNION
  (
    SELECT
      xid AS ID, Types.Type AS Type, Users.Username AS Name
    FROM
      Types, Users,
      (
        SELECT
          ACL.iD AS xid, ACL.Type_ID AS xtype_id, ACL.Entity_ID AS xuser_id
        FROM
          ACL
        WHERE
          ACL.Type_ID =
          (
            SELECT
              ID
            FROM
              Types
            WHERE
              Type = 'user'
          )
      )
    WHERE
        Types.ID = xtype_id
      AND
        Users.ID = xuser_id
  )
  UNION
  (
    SELECT
      xid AS ID, Types.Type AS Type, Computers.Computer AS Name
    FROM
      Types, Computers,
      (
        SELECT
          ACL.iD AS xid, ACL.Type_ID AS xtype_id, ACL.Entity_ID AS xcomputer_id
        FROM
          ACL
        WHERE
          ACL.Type_ID =
          (
            SELECT
              ID
            FROM
              Types
            WHERE
              Type = 'hostname'
          )
      )
    WHERE
        Types.ID = xtype_id
      AND
        Computers.ID = xcomputer_id
  )
  UNION
  (
    SELECT
      xid AS ID, Types.Type AS Type, Computers.Computer AS Name
    FROM
      Types, Computers,
      (
        SELECT
          ACL.iD AS xid, ACL.Type_ID AS xtype_id, ACL.Entity_ID AS xcomputer_id
        FROM
          ACL
        WHERE
          ACL.Type_ID =
          (
            SELECT
              ID
            FROM
              Types
            WHERE
              Type = 'ip_address'
          )
      )
    WHERE
        Types.ID = xtype_id
      AND
        Computers.ID = xcomputer_id
  )
  UNION
  (
    SELECT
      xid AS ID, Types.Type AS Type, Computers.Computer AS Name
    FROM
      Types, Computers,
      (
        SELECT
          ACL.iD AS xid, ACL.Type_ID AS xtype_id, ACL.Entity_ID AS xcomputer_id
        FROM
          ACL
        WHERE
          ACL.Type_ID =
          (
            SELECT
              ID
            FROM
              Types
            WHERE
              Type = 'ip_range'
          )
      )
    WHERE
        Types.ID = xtype_id
      AND
        Computers.ID = xcomputer_id
  )
);

COMMIT;


CREATE VIEW Select_ACL AS
(
  SELECT ID, (Type || '.' || Name) AS ACL FROM Select_ACLs
);

COMMIT;


CREATE VIEW Select_GroupMembers AS
(
  SELECT
    Group_ID AS ID, Username
  FROM
    GroupMembers, Users
  WHERE
    GroupMembers.User_ID = Users.ID
);

COMMIT;


INSERT INTO Types (Type) VALUES ('group');
INSERT INTO Types (Type) VALUES ('user');
INSERT INTO Types (Type) VALUES ('hostname');
INSERT INTO Types (Type) VALUES ('ip_address');
INSERT INTO Types (Type) VALUES ('ip_range');

COMMIT;


INSERT INTO AccessRights (Access_Right) VALUES ('*');
INSERT INTO AccessRights (Access_Right) VALUES ('get');
INSERT INTO AccessRights (Access_Right) VALUES ('post');

COMMIT;


INSERT INTO Users (Username, Password) VALUES ('guest','guest');
INSERT INTO Users (Username, Password) VALUES ('admin','admin');

COMMIT;


INSERT INTO Groups (Groupname) VALUES ('public');
INSERT INTO Groups (Groupname) VALUES ('admin');

COMMIT;


INSERT INTO GroupMembers (Group_ID, User_ID) VALUES (1,1);
INSERT INTO GroupMembers (Group_ID, User_ID) VALUES (2,2);

COMMIT;


INSERT INTO ACL (Type_ID, Entity_ID) VALUES (1,1);
INSERT INTO ACL (Type_ID, Entity_ID) VALUES (1,2);

COMMIT;


INSERT INTO ACLRights (ACL_ID, AccessRight_ID) VALUES (1,2);
INSERT INTO ACLRights (ACL_ID, AccessRight_ID) VALUES (1,3);
INSERT INTO ACLRights (ACL_ID, AccessRight_ID) VALUES (2,1);

COMMIT;
