CREATE TABLE RbtSecurityObjectType (
  typeName varchar(16) not null,
  PRIMARY KEY(typeName)
)
go

CREATE TABLE RbtSecurityObject (
    name varchar(128) not null,
    type varchar(16)  not null,
    data varchar(256),
    data2 varchar(256),
    lastModified datetime NOT NULL DEFAULT current_timestamp,
    PRIMARY KEY (name, type),
    CONSTRAINT fk_securityobject_securityobjecttype FOREIGN KEY (type) REFERENCES RbtSecurityObjectType (typeName)
)
go

CREATE TABLE RbtSecurityObjectMember (
    parentName varchar(128) not null,
    parentType varchar(16) not null,
    memberName varchar(128) not null,
    memberType varchar(16) not null,
    data varchar(256),
    data2 varchar(256),
    displayName varchar(256),
    PRIMARY KEY (parentName, parentType, memberName, memberType),
    CONSTRAINT fk_securityobjectmember_securityobject1 FOREIGN KEY (parentName, parentType) REFERENCES RbtSecurityObject (name, type),
    CONSTRAINT fk_securityobjectmember_securityobjecttype FOREIGN KEY (membertype) REFERENCES RbtSecurityObjectType (typeName)
)

go

CREATE TABLE RbtDocument (
    groupName varchar(128) not null,
    ownerName varchar(128) not null,
    documentName varchar(128) not null,
    document TEXT not null,
    lastModified datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
    modifiedBy varchar(128),
    PRIMARY KEY (groupName, ownerName, documentName)
)
go

CREATE TABLE RbtTableInfo (
    datasource varchar(128) not null,
    tableName varchar(128) not null,
    displayName varchar(128) not null,
    lastModified datetime NOT NULL DEFAULT current_timestamp,
    modifiedBy varchar(128),
    PRIMARY KEY (datasource, tableName)
)

go

CREATE TABLE RbtColumnInfo (
    datasource varchar(128) not null,
    tableName varchar(128) not null,
    columnName varchar(128) not null,
    displayName varchar(128),
    lastModified datetime NOT NULL DEFAULT current_timestamp,
    modifiedBy varchar(128),
    PRIMARY KEY (datasource, tableName, columnName),
    CONSTRAINT fk_columninfo_tableinfo FOREIGN KEY (datasource, tableName) REFERENCES RbtTableInfo (datasource, tableName)
)

go

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

insert into RbtSecurityObject (name, type) values ('administrator', 'role')
go

insert into RbtSecurityObject (name, type) values ('administrators', 'group')
go

insert into RbtSecurityObject (name, type, data) values ('admin', 'user', 'admin')
go

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

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

insert into RbtSecurityObject (name, type) values ('designer', 'role')
go
insert into RbtSecurityObject (name, type) values ('designers', 'group')
go

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

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