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

CREATE TABLE SECURITY_PRINCIPAL
(
    PRINCIPAL_ID INTEGER NOT NULL,
    CLASSNAME VARCHAR(254) NOT NULL,
    IS_MAPPING_ONLY SMALLINT NOT NULL,
    IS_ENABLED SMALLINT 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 SMALLINT NOT NULL,
    IS_ENCODED SMALLINT NOT NULL,
    IS_ENABLED SMALLINT NOT NULL,
    AUTH_FAILURES SMALLINT NOT NULL,
    IS_EXPIRED SMALLINT 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 SMALLINT default 0,
    REQUIRES_CERTIFICATE SMALLINT default 0,
    CHALLENGE_RESPONSE_AUTH SMALLINT default 0,
    FORM_AUTH SMALLINT 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) ;




ALTER TABLE PRINCIPAL_PERMISSION
    FOREIGN KEY FK_PRINCIPAL_PERMISSION_1 (PERMISSION_ID)
    REFERENCES SECURITY_PERMISSION (PERMISSION_ID)
;

ALTER TABLE PRINCIPAL_PERMISSION
    FOREIGN KEY FK_PRINCIPAL_PERMISSION_2 (PRINCIPAL_ID)
    REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID)
;


ALTER TABLE SECURITY_CREDENTIAL
    FOREIGN KEY FK_SECURITY_CREDENTIAL_1 (PRINCIPAL_ID)
    REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID)
;




ALTER TABLE SSO_SITE_TO_PRINCIPALS
    FOREIGN KEY SSO_SITE_TO_PRINC_FK1 (SITE_ID)
    REFERENCES SSO_SITE (SITE_ID)
;

ALTER TABLE SSO_SITE_TO_PRINCIPALS
    FOREIGN KEY SSO_SITE_TO_PRINC_FK2 (PRINCIPAL_ID)
    REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID)
;


ALTER TABLE SSO_PRINCIPAL_TO_REMOTE
    FOREIGN KEY FK_SSO_PRINCIPAL_TO_REMOTE_1 (PRINCIPAL_ID)
    REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID)
;

ALTER TABLE SSO_PRINCIPAL_TO_REMOTE
    FOREIGN KEY FK_SSO_PRINCIPAL_TO_REMOTE_2 (REMOTE_PRINCIPAL_ID)
    REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID)
;


ALTER TABLE SSO_SITE_TO_REMOTE
    FOREIGN KEY FK_SSO_SITE_TO_REMOTE_1 (SITE_ID)
    REFERENCES SSO_SITE (SITE_ID)
;

ALTER TABLE SSO_SITE_TO_REMOTE
    FOREIGN KEY FK_SSO_SITE_TO_REMOTE_2 (PRINCIPAL_ID)
    REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID)
;


ALTER TABLE SSO_COOKIE_TO_REMOTE
    FOREIGN KEY FK_SSO_COOKIE_TO_REMOTE_1 (COOKIE_ID)
    REFERENCES SSO_COOKIE (COOKIE_ID)
;

ALTER TABLE SSO_COOKIE_TO_REMOTE
    FOREIGN KEY FK_SSO_COOKIE_TO_REMOTE_2 (REMOTE_PRINCIPAL_ID)
    REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID)
;


ALTER TABLE SECURITY_USER_ROLE
    FOREIGN KEY FK_SECURITY_USER_ROLE_1 (ROLE_ID)
    REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID)
;

ALTER TABLE SECURITY_USER_ROLE
    FOREIGN KEY FK_SECURITY_USER_ROLE_2 (USER_ID)
    REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID)
;


ALTER TABLE SECURITY_USER_GROUP
    FOREIGN KEY FK_SECURITY_USER_GROUP_1 (GROUP_ID)
    REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID)
;

ALTER TABLE SECURITY_USER_GROUP
    FOREIGN KEY FK_SECURITY_USER_GROUP_2 (USER_ID)
    REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID)
;


ALTER TABLE SECURITY_GROUP_ROLE
    FOREIGN KEY FK_SECURITY_GROUP_ROLE_1 (GROUP_ID)
    REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID)
;

ALTER TABLE SECURITY_GROUP_ROLE
    FOREIGN KEY FK_SECURITY_GROUP_ROLE_2 (ROLE_ID)
    REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID)
;


