no.tsl2.nano.h5.2.5.3.source-code.anyway-admin.sql_ Maven / Gradle / Ivy

Go to download
-- SQL-92
-- problems on hsqldb...

create schema ADMIN

CREATE TABLE ADMIN.Grouprole (
                id INTEGER NOT NULL,
                CONSTRAINT idx_admin_grouprole PRIMARY KEY (id)
);


CREATE TABLE ADMIN.Role (
                id INTEGER NOT NULL,
                name VARCHAR(64) NOT NULL,
                CONSTRAINT idx_admin_role PRIMARY KEY (id)
);


CREATE TABLE ADMIN.History (
                id INTEGER NOT NULL,
                table VARCHAR(64) NOT NULL,
                column VARCHAR(64) NOT NULL,
                action VARCHAR(64) NOT NULL,
                date TIMESTAMP NOT NULL,
                CONSTRAINT idx_admin_history PRIMARY KEY (id)
);


CREATE TABLE ADMIN.User (
                id INTEGER NOT NULL,
                short VARCHAR(8) NOT NULL,
                name VARCHAR(64) NOT NULL,
                name2 VARCHAR(64) NOT NULL,
                passwd VARCHAR(64) NOT NULL,
                CONSTRAINT idx_admin_user PRIMARY KEY (id)
);
-- COMMENT ON TABLE ADMIN.User IS 'application user';


CREATE TABLE ADMIN.Usergroup (
                id INTEGER NOT NULL,
                CONSTRAINT idx_admin_usergroup PRIMARY KEY (id)
);


CREATE TABLE ADMIN.Group (
                id INTEGER NOT NULL,
                name VARCHAR(64) NOT NULL,
                CONSTRAINT idx_admin_group PRIMARY KEY (id)
);


CREATE TABLE ADMIN.Version (
                id INTEGER NOT NULL,
                name VARCHAR(64) NOT NULL,
                date TIMESTAMP NOT NULL,
                CONSTRAINT idx_admin_version PRIMARY KEY (id)
);
-- COMMENT ON TABLE ADMIN.Version IS 'Version der Datenbank-Instanz';


ALTER TABLE ADMIN.Group ADD CONSTRAINT ADMIN_Grouprole_ADMIN_Group_fk
FOREIGN KEY (id)
REFERENCES ADMIN.Grouprole (id)
ON DELETE NO ACTION
ON UPDATE NO ACTION
NOT DEFERRABLE;

ALTER TABLE ADMIN.Role ADD CONSTRAINT ADMIN_Grouprole_ADMIN_Role_fk
FOREIGN KEY (id)
REFERENCES ADMIN.Grouprole (id)
ON DELETE NO ACTION
ON UPDATE NO ACTION
NOT DEFERRABLE;

ALTER TABLE ADMIN.Usergroup ADD CONSTRAINT ADMIN_User_ADMIN_Usergroup_fk
FOREIGN KEY (id)
REFERENCES ADMIN.User (id)
ON DELETE NO ACTION
ON UPDATE NO ACTION
NOT DEFERRABLE;

ALTER TABLE ADMIN.Group ADD CONSTRAINT ADMIN_Usergroup_ADMIN_Group_fk
FOREIGN KEY (id)
REFERENCES ADMIN.Usergroup (id)
ON DELETE NO ACTION
ON UPDATE NO ACTION
NOT DEFERRABLE;