CREATE TABLE rbt.RbtSecurityObjectType (
  typeName varchar2(16) not null,
  PRIMARY KEY(typeName)
);

CREATE TABLE rbt.RbtSecurityObject (
    name varchar2(128) not null,
    type varchar2(16)  not null,
    data varchar2(256),
    data2 varchar2(256),
    lastModified DATE DEFAULT SYSDATE NOT NULL,
    PRIMARY KEY (name, type),
    FOREIGN KEY (type) REFERENCES rbt.RbtSecurityObjectType (typeName)
);


CREATE TABLE rbt.RbtSecurityObjectMember (
    parentName varchar2(128) not null,
    parentType varchar2(16) not null,
    memberName varchar2(128) not null,
    memberType varchar2(16) not null,
    data varchar2(256),
    data2 varchar2(256),
    displayName varchar2(256),
    PRIMARY KEY (parentName, parentType, memberName, memberType),
    CONSTRAINT fk_som_so1 FOREIGN KEY (parentName, parentType) REFERENCES rbt.RbtSecurityObject (name, type),
    CONSTRAINT fk_som_sot FOREIGN KEY (membertype) REFERENCES RbtSecurityObjectType (typeName)
);

CREATE TABLE rbt.RbtDocument (
    groupName varchar2(128) not null,
    ownerName varchar2(128) not null,
    documentName varchar2(128) not null,
    document CLOB not null,
    lastModified DATE DEFAULT SYSDATE NOT NULL,
    modifiedBy varchar(128),
    PRIMARY KEY (groupName, ownerName, documentName)
);

CREATE TABLE rbt.RbtTableInfo (
    datasource varchar(128) not null,
    tableName varchar(128) not null,
    displayName varchar(128) not null,
    lastModified DATE DEFAULT SYSDATE NOT NULL,
    modifiedBy varchar(128),
    PRIMARY KEY (datasource, tableName)
);


CREATE TABLE rbt.RbtColumnInfo (
    datasource varchar(128) not null,
    tableName varchar(128) not null,
    columnName varchar(128) not null,
    displayName varchar(128),
    lastModified DATE DEFAULT SYSDATE NOT NULL,
    modifiedBy varchar(128),
    PRIMARY KEY (datasource, tableName, columnName),
    CONSTRAINT fk_columninfo_tableinfo FOREIGN KEY (datasource, tableName) REFERENCES rbt.RbtTableInfo (datasource, tableName)
);


GRANT ALL on rbt.RbtSecuritObject TO rbt;
GRANT ALL on rbt.RbtSecuritObjectMember TO rbt;
GRANT ALL on rbt.RbtSecuritObjectType TO rbt;
GRANT ALL on rbt.RbtDocument TO rbt;
GRANT ALL on rbt.RbtTableInfo TO rbt;
GRANT ALL on rbt.RbtColumnInfo TO rbt;

insert into rbt.RbtSecurityObjectType (typeName) values ('role');
insert into rbt.RbtSecurityObjectType (typeName) values ('group');
insert into rbt.RbtSecurityObjectType (typeName) values ('user');
insert into rbt.RbtSecurityObjectType (typeName) values ('table');
insert into rbt.RbtSecurityObjectType (typeName) values ('view');
insert into rbt.RbtSecurityObjectType (typeName) values ('column');
insert into rbt.RbtSecurityObjectType (typeName) values ('foreignKey');
insert into rbt.RbtSecurityObjectType (typeName) values ('hiddencolumn');

insert into rbt.RbtSecurityObject (name, type) values ('administrator', 'role');
insert into rbt.RbtSecurityObject (name, type) values ('administrators', 'group');
insert into rbt.RbtSecurityObject (name, type, data) values ('admin', 'user', 'admin');

insert into rbt.RbtSecurityObjectMember (parentName, parentType, memberName, memberType)
    values('administrator', 'role', 'administrators', 'group');

insert into rbt.RbtSecurityObjectMember (parentName, parentType, memberName, memberType)
    values('administrators', 'group', 'admin', 'user');

insert into rbt.RbtSecurityObject (name, type) values ('designer', 'role');
insert into rbt.RbtSecurityObject (name, type) values ('designers', 'group');

insert into rbt.RbtSecurityObjectMember (parentName, parentType, memberName, memberType)
    values('designer', 'role', 'designers', 'group');

insert into rbt.RbtSecurityObjectMember (parentName, parentType, memberName, memberType)
    values('designers', 'group', 'admin', 'user');