CREATE TABLE RbtSecurityObjectType (
  typeName varchar(16) not null,
  PRIMARY KEY(typeName)
)  ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE RbtSecurityObject (
    name varchar(128) not null,
    type varchar(16)  not null,
    data varchar(256),
    data2 varchar(256),
    lastModified TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (name, type),
    CONSTRAINT `fk_securityobject_securityobjecttype` FOREIGN KEY (type) REFERENCES RbtSecurityObjectType (typeName) ON DELETE RESTRICT
)  ENGINE=InnoDB DEFAULT CHARSET=utf8;

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) ON DELETE RESTRICT,
    CONSTRAINT `fk_securityobjectmember_securityobjecttype` FOREIGN KEY (membertype) REFERENCES RbtSecurityObjectType (typeName) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE RbtDocument (
    groupName varchar(128) not null,
    ownerName varchar(128) not null,
    documentName varchar(128) not null,
    document TEXT not null,
    lastModified TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    modifiedBy varchar(128),
    PRIMARY KEY (groupName, ownerName, documentName)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE RbtTableInfo (
    datasource varchar(128) not null,
    tableName varchar(128) not null,
    displayName varchar(128),
    lastModified TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    modifiedBy varchar(128),
    PRIMARY KEY (datasource, tableName)
)  ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE RbtColumnInfo (
    datasource varchar(128) not null,
    tableName varchar(128) not null,
    columnName varchar(128) not null,
    displayName varchar(128),
    lastModified TIMESTAMP 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) ON DELETE CASCADE
)  ENGINE=InnoDB DEFAULT CHARSET=utf8;

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

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

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

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

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

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

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