
-----------------------------------------------------------------------------
-- SECURITY_PRINCIPAL
-----------------------------------------------------------------------------

CREATE TABLE SECURITY_PRINCIPAL
(
                    PRINCIPAL_ID INTEGER NOT NULL,
                CLASSNAME VARCHAR (254) NOT NULL,
                    IS_MAPPING_ONLY INTEGER NOT NULL,
                    IS_ENABLED INTEGER NOT NULL,
                FULL_PATH VARCHAR (254) NOT NULL,
                CREATION_DATE TIMESTAMP NOT NULL,
                MODIFIED_DATE TIMESTAMP NOT NULL,
    UNIQUE (FULL_PATH)
);

ALTER TABLE SECURITY_PRINCIPAL
    ADD PRIMARY KEY (PRINCIPAL_ID);







-----------------------------------------------------------------------------
-- SECURITY_PERMISSION
-----------------------------------------------------------------------------

CREATE TABLE SECURITY_PERMISSION
(
                    PERMISSION_ID INTEGER NOT NULL,
                CLASSNAME VARCHAR (254) NOT NULL,
                NAME VARCHAR (254) NOT NULL,
                ACTIONS VARCHAR (254) NOT NULL,
                CREATION_DATE TIMESTAMP NOT NULL,
                MODIFIED_DATE TIMESTAMP NOT NULL
);

ALTER TABLE SECURITY_PERMISSION
    ADD PRIMARY KEY (PERMISSION_ID);







-----------------------------------------------------------------------------
-- PRINCIPAL_PERMISSION
-----------------------------------------------------------------------------

CREATE TABLE PRINCIPAL_PERMISSION
(
                    PRINCIPAL_ID INTEGER NOT NULL,
                    PERMISSION_ID INTEGER NOT NULL
);

ALTER TABLE PRINCIPAL_PERMISSION
    ADD PRIMARY KEY (PRINCIPAL_ID,PERMISSION_ID);







-----------------------------------------------------------------------------
-- SECURITY_CREDENTIAL
-----------------------------------------------------------------------------

CREATE TABLE SECURITY_CREDENTIAL
(
                    CREDENTIAL_ID INTEGER NOT NULL,
                    PRINCIPAL_ID INTEGER NOT NULL,
                COLUMN_VALUE VARCHAR (254) NOT NULL,
                TYPE SMALLINT NOT NULL,
                CLASSNAME VARCHAR (254),
                    UPDATE_REQUIRED INTEGER NOT NULL,
                    IS_ENCODED INTEGER NOT NULL,
                    IS_ENABLED INTEGER NOT NULL,
                AUTH_FAILURES SMALLINT NOT NULL,
                    IS_EXPIRED INTEGER NOT NULL,
                CREATION_DATE TIMESTAMP NOT NULL,
                MODIFIED_DATE TIMESTAMP NOT NULL,
                PREV_AUTH_DATE TIMESTAMP,
                LAST_AUTH_DATE TIMESTAMP,
                EXPIRATION_DATE DATE
);

ALTER TABLE SECURITY_CREDENTIAL
    ADD PRIMARY KEY (CREDENTIAL_ID);







-----------------------------------------------------------------------------
-- SSO_SITE
-----------------------------------------------------------------------------

CREATE TABLE SSO_SITE
(
                    SITE_ID INTEGER NOT NULL,
                NAME VARCHAR (254) NOT NULL,
                URL VARCHAR (254) NOT NULL,
                    ALLOW_USER_SET INTEGER default 0,
                    REQUIRES_CERTIFICATE INTEGER default 0,
                    CHALLENGE_RESPONSE_AUTH INTEGER default 0,
                    FORM_AUTH INTEGER default 0,
                FORM_USER_FIELD VARCHAR (128),
                FORM_PWD_FIELD VARCHAR (128),
                REALM VARCHAR (128),
    UNIQUE (URL)
);

ALTER TABLE SSO_SITE
    ADD PRIMARY KEY (SITE_ID);







-----------------------------------------------------------------------------
-- SSO_COOKIE
-----------------------------------------------------------------------------

CREATE TABLE SSO_COOKIE
(
                    COOKIE_ID INTEGER NOT NULL,
                COOKIE VARCHAR (1024) NOT NULL,
                CREATE_DATE TIMESTAMP NOT NULL
);

ALTER TABLE SSO_COOKIE
    ADD PRIMARY KEY (COOKIE_ID);







-----------------------------------------------------------------------------
-- SSO_SITE_TO_PRINCIPALS
-----------------------------------------------------------------------------

CREATE TABLE SSO_SITE_TO_PRINCIPALS
(
                    SITE_ID INTEGER NOT NULL,
                    PRINCIPAL_ID INTEGER NOT NULL
);

ALTER TABLE SSO_SITE_TO_PRINCIPALS
    ADD PRIMARY KEY (SITE_ID,PRINCIPAL_ID);







-----------------------------------------------------------------------------
-- SSO_PRINCIPAL_TO_REMOTE
-----------------------------------------------------------------------------

CREATE TABLE SSO_PRINCIPAL_TO_REMOTE
(
                    PRINCIPAL_ID INTEGER NOT NULL,
                    REMOTE_PRINCIPAL_ID INTEGER NOT NULL
);

ALTER TABLE SSO_PRINCIPAL_TO_REMOTE
    ADD PRIMARY KEY (PRINCIPAL_ID,REMOTE_PRINCIPAL_ID);







-----------------------------------------------------------------------------
-- SSO_SITE_TO_REMOTE
-----------------------------------------------------------------------------

CREATE TABLE SSO_SITE_TO_REMOTE
(
                    SITE_ID INTEGER NOT NULL,
                    PRINCIPAL_ID INTEGER NOT NULL
);

ALTER TABLE SSO_SITE_TO_REMOTE
    ADD PRIMARY KEY (SITE_ID,PRINCIPAL_ID);







-----------------------------------------------------------------------------
-- SSO_COOKIE_TO_REMOTE
-----------------------------------------------------------------------------

CREATE TABLE SSO_COOKIE_TO_REMOTE
(
                    COOKIE_ID INTEGER NOT NULL,
                    REMOTE_PRINCIPAL_ID INTEGER NOT NULL
);

ALTER TABLE SSO_COOKIE_TO_REMOTE
    ADD PRIMARY KEY (COOKIE_ID,REMOTE_PRINCIPAL_ID);







-----------------------------------------------------------------------------
-- SECURITY_USER_ROLE
-----------------------------------------------------------------------------

CREATE TABLE SECURITY_USER_ROLE
(
                    USER_ID INTEGER NOT NULL,
                    ROLE_ID INTEGER NOT NULL
);

ALTER TABLE SECURITY_USER_ROLE
    ADD PRIMARY KEY (USER_ID,ROLE_ID);







-----------------------------------------------------------------------------
-- SECURITY_USER_GROUP
-----------------------------------------------------------------------------

CREATE TABLE SECURITY_USER_GROUP
(
                    USER_ID INTEGER NOT NULL,
                    GROUP_ID INTEGER NOT NULL
);

ALTER TABLE SECURITY_USER_GROUP
    ADD PRIMARY KEY (USER_ID,GROUP_ID);







-----------------------------------------------------------------------------
-- SECURITY_GROUP_ROLE
-----------------------------------------------------------------------------

CREATE TABLE SECURITY_GROUP_ROLE
(
                    GROUP_ID INTEGER NOT NULL,
                    ROLE_ID INTEGER NOT NULL
);

ALTER TABLE SECURITY_GROUP_ROLE
    ADD PRIMARY KEY (GROUP_ID,ROLE_ID);







-----------------------------------------------------------------------------
-- SECURITY_PRINCIPAL: FOREIGN KEYS
-----------------------------------------------------------------------------


-----------------------------------------------------------------------------
-- SECURITY_PERMISSION: FOREIGN KEYS
-----------------------------------------------------------------------------


-----------------------------------------------------------------------------
-- PRINCIPAL_PERMISSION: FOREIGN KEYS
-----------------------------------------------------------------------------
ALTER TABLE PRINCIPAL_PERMISSION
    ADD CONSTRAINT FK_PRINCIPAL_PERMISSION_1 FOREIGN KEY (PERMISSION_ID)
    REFERENCES SECURITY_PERMISSION (PERMISSION_ID)
    ON DELETE CASCADE 
;
ALTER TABLE PRINCIPAL_PERMISSION
    ADD CONSTRAINT FK_PRINCIPAL_PERMISSION_2 FOREIGN KEY (PRINCIPAL_ID)
    REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID)
    ON DELETE CASCADE 
;


-----------------------------------------------------------------------------
-- SECURITY_CREDENTIAL: FOREIGN KEYS
-----------------------------------------------------------------------------
ALTER TABLE SECURITY_CREDENTIAL
    ADD CONSTRAINT FK_SECURITY_CREDENTIAL_1 FOREIGN KEY (PRINCIPAL_ID)
    REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID)
    ON DELETE CASCADE 
;


-----------------------------------------------------------------------------
-- SSO_SITE: FOREIGN KEYS
-----------------------------------------------------------------------------


-----------------------------------------------------------------------------
-- SSO_COOKIE: FOREIGN KEYS
-----------------------------------------------------------------------------


-----------------------------------------------------------------------------
-- SSO_SITE_TO_PRINCIPALS: FOREIGN KEYS
-----------------------------------------------------------------------------
ALTER TABLE SSO_SITE_TO_PRINCIPALS
    ADD CONSTRAINT SSO_SITE_TO_PRINC_FK1 FOREIGN KEY (SITE_ID)
    REFERENCES SSO_SITE (SITE_ID)
    ON DELETE CASCADE 
;
ALTER TABLE SSO_SITE_TO_PRINCIPALS
    ADD CONSTRAINT SSO_SITE_TO_PRINC_FK2 FOREIGN KEY (PRINCIPAL_ID)
    REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID)
    ON DELETE CASCADE 
;


-----------------------------------------------------------------------------
-- SSO_PRINCIPAL_TO_REMOTE: FOREIGN KEYS
-----------------------------------------------------------------------------
ALTER TABLE SSO_PRINCIPAL_TO_REMOTE
    ADD CONSTRAINT FK_SSO_PRINCIPAL_TO_REMOTE_1 FOREIGN KEY (PRINCIPAL_ID)
    REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID)
    ON DELETE CASCADE 
;
ALTER TABLE SSO_PRINCIPAL_TO_REMOTE
    ADD CONSTRAINT FK_SSO_PRINCIPAL_TO_REMOTE_2 FOREIGN KEY (REMOTE_PRINCIPAL_ID)
    REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID)
    ON DELETE CASCADE 
;


-----------------------------------------------------------------------------
-- SSO_SITE_TO_REMOTE: FOREIGN KEYS
-----------------------------------------------------------------------------
ALTER TABLE SSO_SITE_TO_REMOTE
    ADD CONSTRAINT FK_SSO_SITE_TO_REMOTE_1 FOREIGN KEY (SITE_ID)
    REFERENCES SSO_SITE (SITE_ID)
    ON DELETE CASCADE 
;
ALTER TABLE SSO_SITE_TO_REMOTE
    ADD CONSTRAINT FK_SSO_SITE_TO_REMOTE_2 FOREIGN KEY (PRINCIPAL_ID)
    REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID)
    ON DELETE CASCADE 
;


-----------------------------------------------------------------------------
-- SSO_COOKIE_TO_REMOTE: FOREIGN KEYS
-----------------------------------------------------------------------------
ALTER TABLE SSO_COOKIE_TO_REMOTE
    ADD CONSTRAINT FK_SSO_COOKIE_TO_REMOTE_1 FOREIGN KEY (COOKIE_ID)
    REFERENCES SSO_COOKIE (COOKIE_ID)
    ON DELETE CASCADE 
;
ALTER TABLE SSO_COOKIE_TO_REMOTE
    ADD CONSTRAINT FK_SSO_COOKIE_TO_REMOTE_2 FOREIGN KEY (REMOTE_PRINCIPAL_ID)
    REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID)
    ON DELETE CASCADE 
;


-----------------------------------------------------------------------------
-- SECURITY_USER_ROLE: FOREIGN KEYS
-----------------------------------------------------------------------------
ALTER TABLE SECURITY_USER_ROLE
    ADD CONSTRAINT FK_SECURITY_USER_ROLE_1 FOREIGN KEY (ROLE_ID)
    REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID)
    ON DELETE CASCADE 
;
ALTER TABLE SECURITY_USER_ROLE
    ADD CONSTRAINT FK_SECURITY_USER_ROLE_2 FOREIGN KEY (USER_ID)
    REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID)
    ON DELETE CASCADE 
;


-----------------------------------------------------------------------------
-- SECURITY_USER_GROUP: FOREIGN KEYS
-----------------------------------------------------------------------------
ALTER TABLE SECURITY_USER_GROUP
    ADD CONSTRAINT FK_SECURITY_USER_GROUP_1 FOREIGN KEY (GROUP_ID)
    REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID)
    ON DELETE CASCADE 
;
ALTER TABLE SECURITY_USER_GROUP
    ADD CONSTRAINT FK_SECURITY_USER_GROUP_2 FOREIGN KEY (USER_ID)
    REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID)
    ON DELETE CASCADE 
;


-----------------------------------------------------------------------------
-- SECURITY_GROUP_ROLE: FOREIGN KEYS
-----------------------------------------------------------------------------
ALTER TABLE SECURITY_GROUP_ROLE
    ADD CONSTRAINT FK_SECURITY_GROUP_ROLE_1 FOREIGN KEY (GROUP_ID)
    REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID)
    ON DELETE CASCADE 
;
ALTER TABLE SECURITY_GROUP_ROLE
    ADD CONSTRAINT FK_SECURITY_GROUP_ROLE_2 FOREIGN KEY (ROLE_ID)
    REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID)
    ON DELETE CASCADE 
;

