I have an existing database, and a website built on it. We are in a process of portalizing the website with Jetspeed2.
Now I downloaded jetspeed2 and the DB that it creates has quite a lot of tables (atleast 30 - 40). Out of all of these tables, I do understand that security_XXX tables would probably be used by the JAAS Security module of jetspeed. But apart from those, which are the tables that I would have to preserve on my production database for jetspeed to work ??
Like, I know that there are lot of sample applications shipped with Jetspeed2, and lot of those applications might need their own tables. But if I were to remove all the sample applications shipped with J2, and only have the minimal skeleton of J2, what are the tables that I need to preserve ??
Ive gone thru something similar here. Minimized, its not that much smaller (see below)
summary:
phase 1 schema: need this for the capability map component which only runs against a relational database store
phase 2 schema: need the profiler schema
phase3 ojb: ojb internals
prefs schema: to handle preferences - required for proper operation using prefs
registry schema: all Portlet app and portlet info from portlet.xml stored here
security-schema: you could minimize this by removing SSO tables or providing your own security components
# ----------------------------------------------------------------------- # MEDIA_TYPE # ----------------------------------------------------------------------- drop table if exists MEDIA_TYPE;
CREATE TABLE MEDIA_TYPE ( MEDIATYPE_ID INTEGER NOT NULL, NAME VARCHAR (80) NOT NULL, CHARACTER_SET VARCHAR (40), TITLE VARCHAR (80), DESCRIPTION MEDIUMTEXT, PRIMARY KEY(MEDIATYPE_ID) );
# ----------------------------------------------------------------------- # CLIENT # ----------------------------------------------------------------------- drop table if exists CLIENT;
CREATE TABLE CLIENT ( CLIENT_ID INTEGER NOT NULL, NAME VARCHAR (80) NOT NULL, USER_AGENT_PATTERN VARCHAR (128), MANUFACTURER VARCHAR (80), MODEL VARCHAR (80), VERSION VARCHAR (40), PREFERRED_MIMETYPE_ID INTEGER NOT NULL, PRIMARY KEY(CLIENT_ID) );
# ----------------------------------------------------------------------- # MIMETYPE # ----------------------------------------------------------------------- drop table if exists MIMETYPE;
CREATE TABLE MIMETYPE ( MIMETYPE_ID INTEGER NOT NULL, NAME VARCHAR (80) NOT NULL, PRIMARY KEY(MIMETYPE_ID) );
# ----------------------------------------------------------------------- # CAPABILITY # ----------------------------------------------------------------------- drop table if exists CAPABILITY;
CREATE TABLE CAPABILITY ( CAPABILITY_ID INTEGER NOT NULL, CAPABILITY VARCHAR (80) NOT NULL, PRIMARY KEY(CAPABILITY_ID) );
# ----------------------------------------------------------------------- # CLIENT_TO_CAPABILITY # ----------------------------------------------------------------------- drop table if exists CLIENT_TO_CAPABILITY;
CREATE TABLE CLIENT_TO_CAPABILITY ( CLIENT_ID INTEGER NOT NULL, CAPABILITY_ID INTEGER NOT NULL );
# ----------------------------------------------------------------------- # CLIENT_TO_MIMETYPE # ----------------------------------------------------------------------- drop table if exists CLIENT_TO_MIMETYPE;
CREATE TABLE CLIENT_TO_MIMETYPE ( CLIENT_ID INTEGER NOT NULL, MIMETYPE_ID INTEGER NOT NULL );
# ----------------------------------------------------------------------- # MEDIATYPE_TO_CAPABILITY # ----------------------------------------------------------------------- drop table if exists MEDIATYPE_TO_CAPABILITY;
CREATE TABLE MEDIATYPE_TO_CAPABILITY ( MEDIATYPE_ID INTEGER NOT NULL, CAPABILITY_ID INTEGER NOT NULL );
# ----------------------------------------------------------------------- # MEDIATYPE_TO_MIMETYPE # ----------------------------------------------------------------------- drop table if exists MEDIATYPE_TO_MIMETYPE;
CREATE TABLE MEDIATYPE_TO_MIMETYPE ( MEDIATYPE_ID INTEGER NOT NULL, MIMETYPE_ID INTEGER NOT NULL );
# ----------------------------------------------------------------------- # PROFILING_RULE # ----------------------------------------------------------------------- drop table if exists PROFILING_RULE;
CREATE TABLE PROFILING_RULE ( RULE_ID VARCHAR (80) NOT NULL, CLASS_NAME VARCHAR (100) NOT NULL, TITLE VARCHAR (100), PRIMARY KEY(RULE_ID) );
# ----------------------------------------------------------------------- # RULE_CRITERION # ----------------------------------------------------------------------- drop table if exists RULE_CRITERION;
CREATE TABLE RULE_CRITERION ( CRITERION_ID VARCHAR (80) NOT NULL, RULE_ID VARCHAR (80) NOT NULL, FALLBACK_ORDER INTEGER NOT NULL, REQUEST_TYPE VARCHAR (40) NOT NULL, NAME VARCHAR (80) NOT NULL, VALUE VARCHAR (128), FALLBACK_TYPE INTEGER default 1, PRIMARY KEY(CRITERION_ID), FOREIGN KEY (RULE_ID) REFERENCES PROFILING_RULE (RULE_ID) ON DELETE CASCADE , INDEX IX_RULE_CRITERION_1 (RULE_ID, FALLBACK_ORDER) );
# ----------------------------------------------------------------------- # PRINCIPAL_RULE_ASSOC # ----------------------------------------------------------------------- drop table if exists PRINCIPAL_RULE_ASSOC;
CREATE TABLE PRINCIPAL_RULE_ASSOC ( PRINCIPAL_NAME VARCHAR (80) NOT NULL, LOCATOR_NAME VARCHAR (80) NOT NULL, RULE_ID VARCHAR (80) NOT NULL, PRIMARY KEY(PRINCIPAL_NAME,LOCATOR_NAME) );
# ----------------------------------------------------------------------- # PROFILE_PAGE_ASSOC # ----------------------------------------------------------------------- drop table if exists PROFILE_PAGE_ASSOC;
CREATE TABLE PROFILE_PAGE_ASSOC ( LOCATOR_HASH VARCHAR (40) NOT NULL, PAGE_ID VARCHAR (80) NOT NULL, UNIQUE (LOCATOR_HASH, PAGE_ID) );
# ----------------------------------------------------------------------- # OJB_HL_SEQ # ----------------------------------------------------------------------- drop table if exists OJB_HL_SEQ;
CREATE TABLE OJB_HL_SEQ ( TABLENAME VARCHAR (175) NOT NULL, FIELDNAME VARCHAR (70) NOT NULL, MAX_KEY INTEGER, GRAB_SIZE INTEGER, VERSION INTEGER, PRIMARY KEY(TABLENAME,FIELDNAME) );
# ----------------------------------------------------------------------- # OJB_LOCKENTRY # ----------------------------------------------------------------------- drop table if exists OJB_LOCKENTRY;
CREATE TABLE OJB_LOCKENTRY ( OID_ VARCHAR (250) NOT NULL, TX_ID VARCHAR (50) NOT NULL, TIMESTAMP_ TIMESTAMP, ISOLATIONLEVEL INTEGER, LOCKTYPE INTEGER, PRIMARY KEY(OID_,TX_ID) );
# ----------------------------------------------------------------------- # OJB_NRM # ----------------------------------------------------------------------- drop table if exists OJB_NRM;
CREATE TABLE OJB_NRM ( NAME VARCHAR (250) NOT NULL, OID_ LONGBLOB, PRIMARY KEY(NAME) );
# ----------------------------------------------------------------------- # OJB_DLIST # ----------------------------------------------------------------------- drop table if exists OJB_DLIST;
CREATE TABLE OJB_DLIST ( ID INTEGER NOT NULL, SIZE_ INTEGER, PRIMARY KEY(ID) );
# ----------------------------------------------------------------------- # OJB_DLIST_ENTRIES # ----------------------------------------------------------------------- drop table if exists OJB_DLIST_ENTRIES;
CREATE TABLE OJB_DLIST_ENTRIES ( ID INTEGER NOT NULL, DLIST_ID INTEGER, POSITION_ INTEGER, OID_ LONGBLOB, PRIMARY KEY(ID) );
# ----------------------------------------------------------------------- # OJB_DSET # ----------------------------------------------------------------------- drop table if exists OJB_DSET;
CREATE TABLE OJB_DSET ( ID INTEGER NOT NULL, SIZE_ INTEGER, PRIMARY KEY(ID) );
# ----------------------------------------------------------------------- # OJB_DSET_ENTRIES # ----------------------------------------------------------------------- drop table if exists OJB_DSET_ENTRIES;
CREATE TABLE OJB_DSET_ENTRIES ( ID INTEGER NOT NULL, DLIST_ID INTEGER, POSITION_ INTEGER, OID_ LONGBLOB, PRIMARY KEY(ID) );
# ----------------------------------------------------------------------- # OJB_DMAP # ----------------------------------------------------------------------- drop table if exists OJB_DMAP;
CREATE TABLE OJB_DMAP ( ID INTEGER NOT NULL, SIZE_ INTEGER, PRIMARY KEY(ID) );
# ----------------------------------------------------------------------- # PREFS_PROPERTY_VALUE # ----------------------------------------------------------------------- drop table if exists PREFS_PROPERTY_VALUE;
CREATE TABLE PREFS_PROPERTY_VALUE ( PROPERTY_VALUE_ID INTEGER NOT NULL, NODE_ID INTEGER, PROPERTY_KEY_ID INTEGER, BOOLEAN_VALUE BIT, DATETIME_VALUE TIMESTAMP, LONG_VALUE INTEGER, DOUBLE_VALUE DOUBLE, TEXT_VALUE VARCHAR (254), CREATION_DATE TIMESTAMP, MODIFIED_DATE TIMESTAMP, PRIMARY KEY(PROPERTY_VALUE_ID) );
# ----------------------------------------------------------------------- # PREFS_NODE_PROPERTY_KEY # ----------------------------------------------------------------------- drop table if exists PREFS_NODE_PROPERTY_KEY;
CREATE TABLE PREFS_NODE_PROPERTY_KEY ( NODE_ID INTEGER NOT NULL, PROPERTY_KEY_ID INTEGER NOT NULL, PRIMARY KEY(NODE_ID,PROPERTY_KEY_ID) );
# ----------------------------------------------------------------------- # PREFS_PROPERTY_KEY # ----------------------------------------------------------------------- drop table if exists PREFS_PROPERTY_KEY;
CREATE TABLE PREFS_PROPERTY_KEY ( PROPERTY_KEY_ID INTEGER NOT NULL, PROPERTY_NAME VARCHAR (100), PROPERTY_TYPE SMALLINT, CREATION_DATE TIMESTAMP, MODIFIED_DATE TIMESTAMP, PRIMARY KEY(PROPERTY_KEY_ID) );
# ----------------------------------------------------------------------- # PREFS_NODE # ----------------------------------------------------------------------- drop table if exists PREFS_NODE;
CREATE TABLE PREFS_NODE ( NODE_ID INTEGER NOT NULL, PARENT_NODE_ID INTEGER, NODE_NAME VARCHAR (100), NODE_TYPE SMALLINT, FULL_PATH VARCHAR (254), CREATION_DATE TIMESTAMP, MODIFIED_DATE TIMESTAMP, PRIMARY KEY(NODE_ID), FOREIGN KEY (PARENT_NODE_ID) REFERENCES PREFS_NODE (NODE_ID)
);
# ----------------------------------------------------------------------- # PORTLET_DEFINITION # ----------------------------------------------------------------------- drop table if exists PORTLET_DEFINITION;
CREATE TABLE PORTLET_DEFINITION ( ID INTEGER NOT NULL, NAME VARCHAR (80), CLASS_NAME VARCHAR (255), APPLICATION_ID INTEGER NOT NULL, PORTLET_IDENTIFIER VARCHAR (80), EXPIRATION_CACHE VARCHAR (30), RESOURCE_BUNDLE VARCHAR (255), PREFERENCE_VALIDATOR VARCHAR (255), PRIMARY KEY(ID) );
# ----------------------------------------------------------------------- # PORTLET_APPLICATION # ----------------------------------------------------------------------- drop table if exists PORTLET_APPLICATION;
CREATE TABLE PORTLET_APPLICATION ( APPLICATION_ID INTEGER NOT NULL, APP_NAME VARCHAR (80) NOT NULL, APP_IDENTIFIER VARCHAR (80), VERSION VARCHAR (80), APP_TYPE INTEGER, CHECKSUM VARCHAR (80), DESCRIPTION VARCHAR (80), WEB_APP_ID INTEGER NOT NULL, PRIMARY KEY(APPLICATION_ID), UNIQUE (APP_NAME) );
# ----------------------------------------------------------------------- # WEB_APPLICATION # ----------------------------------------------------------------------- drop table if exists WEB_APPLICATION;
CREATE TABLE WEB_APPLICATION ( ID INTEGER NOT NULL, CONTEXT_ROOT VARCHAR (255) NOT NULL, PRIMARY KEY(ID) );
# ----------------------------------------------------------------------- # PA_METADATA_FIELDS # ----------------------------------------------------------------------- drop table if exists PA_METADATA_FIELDS;
CREATE TABLE PA_METADATA_FIELDS ( ID INTEGER NOT NULL, OBJECT_ID INTEGER NOT NULL, VALUE MEDIUMTEXT NOT NULL, NAME VARCHAR (100) NOT NULL, LOCALE_STRING VARCHAR (50) NOT NULL, PRIMARY KEY(ID), FOREIGN KEY (OBJECT_ID) REFERENCES PORTLET_APPLICATION (APPLICATION_ID) ON DELETE CASCADE
);
# ----------------------------------------------------------------------- # PD_METADATA_FIELDS # ----------------------------------------------------------------------- drop table if exists PD_METADATA_FIELDS;
CREATE TABLE PD_METADATA_FIELDS ( ID INTEGER NOT NULL, OBJECT_ID INTEGER NOT NULL, VALUE MEDIUMTEXT NOT NULL, NAME VARCHAR (100) NOT NULL, LOCALE_STRING VARCHAR (50) NOT NULL, PRIMARY KEY(ID), FOREIGN KEY (OBJECT_ID) REFERENCES PORTLET_DEFINITION (ID) ON DELETE CASCADE
);
# ----------------------------------------------------------------------- # LANGUAGE # ----------------------------------------------------------------------- drop table if exists LANGUAGE;
CREATE TABLE LANGUAGE ( ID INTEGER NOT NULL, PORTLET_ID INTEGER NOT NULL, TITLE VARCHAR (100), SHORT_TITLE VARCHAR (50), LOCALE_STRING VARCHAR (50) NOT NULL, KEYWORDS MEDIUMTEXT, PRIMARY KEY(ID) );
# ----------------------------------------------------------------------- # PORTLET_CONTENT_TYPE # ----------------------------------------------------------------------- drop table if exists PORTLET_CONTENT_TYPE;
CREATE TABLE PORTLET_CONTENT_TYPE ( CONTENT_TYPE_ID INTEGER NOT NULL, PORTLET_ID INTEGER NOT NULL, CONTENT_TYPE VARCHAR (30) NOT NULL, MODES MEDIUMTEXT, PRIMARY KEY(CONTENT_TYPE_ID) );
# ----------------------------------------------------------------------- # PARAMETER # ----------------------------------------------------------------------- drop table if exists PARAMETER;
CREATE TABLE PARAMETER ( PARAMETER_ID INTEGER NOT NULL, PARENT_ID INTEGER NOT NULL, CLASS_NAME VARCHAR (255) NOT NULL, NAME VARCHAR (80) NOT NULL, PARAMETER_VALUE MEDIUMTEXT NOT NULL, PRIMARY KEY(PARAMETER_ID) );
# ----------------------------------------------------------------------- # PREFERENCE_VALUE # ----------------------------------------------------------------------- drop table if exists PREFERENCE_VALUE;
CREATE TABLE PREFERENCE_VALUE ( ID INTEGER NOT NULL, PREFERENCE_ID INTEGER, VALUE MEDIUMTEXT, PRIMARY KEY(ID) );
# ----------------------------------------------------------------------- # PORTLET_PREFERENCE # ----------------------------------------------------------------------- drop table if exists PORTLET_PREFERENCE;
CREATE TABLE PORTLET_PREFERENCE ( ID INTEGER NOT NULL, PARENT_ID INTEGER NOT NULL, NAME VARCHAR (80) NOT NULL, CLASS_NAME VARCHAR (255) NOT NULL, READ_ONLY CHAR (1) default '1', PRIMARY KEY(ID) );
# ----------------------------------------------------------------------- # PORTLET_ENTITY # ----------------------------------------------------------------------- drop table if exists PORTLET_ENTITY;
CREATE TABLE PORTLET_ENTITY ( PEID INTEGER NOT NULL, ID VARCHAR (255) NOT NULL, APP_NAME VARCHAR (255) NOT NULL, PORTLET_NAME VARCHAR (255) NOT NULL, PRIMARY KEY(PEID), UNIQUE (ID) );
# ----------------------------------------------------------------------- # SECURITY_ROLE_REFERENCE # ----------------------------------------------------------------------- drop table if exists SECURITY_ROLE_REFERENCE;
CREATE TABLE SECURITY_ROLE_REFERENCE ( ID INTEGER NOT NULL, PORTLET_DEFINITION_ID INTEGER NOT NULL, ROLE_NAME VARCHAR (150) NOT NULL, ROLE_LINK VARCHAR (150), PRIMARY KEY(ID) );
# ----------------------------------------------------------------------- # SECURITY_ROLE # ----------------------------------------------------------------------- drop table if exists SECURITY_ROLE;
CREATE TABLE SECURITY_ROLE ( ID INTEGER NOT NULL, WEB_APPLICATION_ID INTEGER NOT NULL, ROLE_NAME VARCHAR (150) NOT NULL, DESCRIPTION VARCHAR (150), PRIMARY KEY(ID) );
# ----------------------------------------------------------------------- # USER_ATTRIBUTE_REF # ----------------------------------------------------------------------- drop table if exists USER_ATTRIBUTE_REF;
CREATE TABLE USER_ATTRIBUTE_REF
(
ID INTEGER NOT NULL,
APPLICATION_ID INTEGER NOT NULL,
NAME VARCHAR (150),
NAME_LINK VARCHAR (150),
PRIMARY KEY(ID),
FOREIGN KEY (APPLICATION_ID) REFERENCES PORTLET_APPLICATION (APPLICATION_ID)
ON DELETE CASCADE
);
# ----------------------------------------------------------------------- # USER_ATTRIBUTE # ----------------------------------------------------------------------- drop table if exists USER_ATTRIBUTE;
CREATE TABLE USER_ATTRIBUTE
(
ID INTEGER NOT NULL,
APPLICATION_ID INTEGER NOT NULL,
NAME VARCHAR (150),
DESCRIPTION VARCHAR (150),
PRIMARY KEY(ID),
FOREIGN KEY (APPLICATION_ID) REFERENCES PORTLET_APPLICATION (APPLICATION_ID)
ON DELETE CASCADE
);
# ----------------------------------------------------------------------- # JETSPEED_SERVICE # ----------------------------------------------------------------------- drop table if exists JETSPEED_SERVICE;
CREATE TABLE JETSPEED_SERVICE ( ID INTEGER NOT NULL, APPLICATION_ID INTEGER NOT NULL, NAME VARCHAR (150), PRIMARY KEY(ID) );
# ----------------------------------------------------------------------- # LOCALIZED_DESCRIPTION # ----------------------------------------------------------------------- drop table if exists LOCALIZED_DESCRIPTION;
CREATE TABLE LOCALIZED_DESCRIPTION ( ID INTEGER NOT NULL, OBJECT_ID INTEGER NOT NULL, CLASS_NAME VARCHAR (255) NOT NULL, DESCRIPTION MEDIUMTEXT NOT NULL, LOCALE_STRING VARCHAR (50) NOT NULL, PRIMARY KEY(ID) );
# ----------------------------------------------------------------------- # LOCALIZED_DISPLAY_NAME # ----------------------------------------------------------------------- drop table if exists LOCALIZED_DISPLAY_NAME;
CREATE TABLE LOCALIZED_DISPLAY_NAME ( ID INTEGER NOT NULL, OBJECT_ID INTEGER NOT NULL, CLASS_NAME VARCHAR (255), DISPLAY_NAME MEDIUMTEXT NOT NULL, LOCALE_STRING VARCHAR (50) NOT NULL, PRIMARY KEY(ID) );
# ----------------------------------------------------------------------- # SECURITY_PRINCIPAL # ----------------------------------------------------------------------- drop table if exists SECURITY_PRINCIPAL;
CREATE TABLE SECURITY_PRINCIPAL ( PRINCIPAL_ID INTEGER NOT NULL, CLASSNAME VARCHAR (254) NOT NULL, IS_MAPPING_ONLY BIT NOT NULL, IS_ENABLED BIT NOT NULL, FULL_PATH VARCHAR (254) NOT NULL, CREATION_DATE TIMESTAMP NOT NULL, MODIFIED_DATE TIMESTAMP NOT NULL, PRIMARY KEY(PRINCIPAL_ID), UNIQUE (FULL_PATH) );
# ----------------------------------------------------------------------- # SECURITY_PERMISSION # ----------------------------------------------------------------------- drop table if exists 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, PRIMARY KEY(PERMISSION_ID) );
# ----------------------------------------------------------------------- # PRINCIPAL_PERMISSION # ----------------------------------------------------------------------- drop table if exists PRINCIPAL_PERMISSION;
CREATE TABLE PRINCIPAL_PERMISSION
(
PRINCIPAL_ID INTEGER NOT NULL,
PERMISSION_ID INTEGER NOT NULL,
PRIMARY KEY(PRINCIPAL_ID,PERMISSION_ID),
FOREIGN KEY (PERMISSION_ID) REFERENCES SECURITY_PERMISSION (PERMISSION_ID)
ON DELETE CASCADE
,
FOREIGN KEY (PRINCIPAL_ID) REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID)
ON DELETE CASCADE
);
# ----------------------------------------------------------------------- # SECURITY_CREDENTIAL # ----------------------------------------------------------------------- drop table if exists SECURITY_CREDENTIAL;
CREATE TABLE SECURITY_CREDENTIAL ( CREDENTIAL_ID INTEGER NOT NULL, PRINCIPAL_ID INTEGER NOT NULL, VALUE VARCHAR (254) NOT NULL, TYPE SMALLINT NOT NULL, CLASSNAME VARCHAR (254), UPDATE_REQUIRED BIT NOT NULL, IS_ENCODED BIT NOT NULL, IS_ENABLED BIT NOT NULL, AUTH_FAILURES SMALLINT NOT NULL, IS_EXPIRED BIT NOT NULL, CREATION_DATE TIMESTAMP NOT NULL, MODIFIED_DATE TIMESTAMP NOT NULL, PREV_AUTH_DATE TIMESTAMP, LAST_AUTH_DATE TIMESTAMP, EXPIRATION_DATE DATETIME, PRIMARY KEY(CREDENTIAL_ID), FOREIGN KEY (PRINCIPAL_ID) REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID) ON DELETE CASCADE
);
# ----------------------------------------------------------------------- # SSO_SITE # ----------------------------------------------------------------------- drop table if exists SSO_SITE;
CREATE TABLE SSO_SITE ( SITE_ID INTEGER NOT NULL, NAME VARCHAR (254) NOT NULL, URL VARCHAR (254) NOT NULL, ALLOW_USER_SET BIT default 0, REQUIRES_CERTIFICATE BIT default 0, PRIMARY KEY(SITE_ID), UNIQUE (URL) );
# ----------------------------------------------------------------------- # SSO_SITE_TO_PRINCIPALS # ----------------------------------------------------------------------- drop table if exists SSO_SITE_TO_PRINCIPALS;
CREATE TABLE SSO_SITE_TO_PRINCIPALS ( SITE_ID INTEGER NOT NULL, PRINCIPAL_ID INTEGER NOT NULL, PRIMARY KEY(SITE_ID,PRINCIPAL_ID), FOREIGN KEY (SITE_ID) REFERENCES SSO_SITE (SITE_ID) ON DELETE CASCADE , FOREIGN KEY (PRINCIPAL_ID) REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID) ON DELETE CASCADE
);
# ----------------------------------------------------------------------- # SSO_PRINCIPAL_TO_REMOTE # ----------------------------------------------------------------------- drop table if exists SSO_PRINCIPAL_TO_REMOTE;
CREATE TABLE SSO_PRINCIPAL_TO_REMOTE
(
PRINCIPAL_ID INTEGER NOT NULL,
REMOTE_PRINCIPAL_ID INTEGER NOT NULL,
PRIMARY KEY(PRINCIPAL_ID,REMOTE_PRINCIPAL_ID),
FOREIGN KEY (PRINCIPAL_ID) REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID)
ON DELETE CASCADE
,
FOREIGN KEY (REMOTE_PRINCIPAL_ID) REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID)
ON DELETE CASCADE
);
# ----------------------------------------------------------------------- # SSO_SITE_TO_REMOTE # ----------------------------------------------------------------------- drop table if exists SSO_SITE_TO_REMOTE;
CREATE TABLE SSO_SITE_TO_REMOTE ( SITE_ID INTEGER NOT NULL, PRINCIPAL_ID INTEGER NOT NULL, PRIMARY KEY(SITE_ID,PRINCIPAL_ID), FOREIGN KEY (SITE_ID) REFERENCES SSO_SITE (SITE_ID) ON DELETE CASCADE , FOREIGN KEY (PRINCIPAL_ID) REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID) ON DELETE CASCADE
);
# ----------------------------------------------------------------------- # SECURITY_USER_ROLE # ----------------------------------------------------------------------- drop table if exists SECURITY_USER_ROLE;
CREATE TABLE SECURITY_USER_ROLE ( USER_ID INTEGER NOT NULL, ROLE_ID INTEGER NOT NULL, PRIMARY KEY(USER_ID,ROLE_ID), FOREIGN KEY (ROLE_ID) REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID) ON DELETE CASCADE , FOREIGN KEY (USER_ID) REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID) ON DELETE CASCADE
);
# ----------------------------------------------------------------------- # SECURITY_USER_GROUP # ----------------------------------------------------------------------- drop table if exists SECURITY_USER_GROUP;
CREATE TABLE SECURITY_USER_GROUP ( USER_ID INTEGER NOT NULL, GROUP_ID INTEGER NOT NULL, PRIMARY KEY(USER_ID,GROUP_ID), FOREIGN KEY (GROUP_ID) REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID) ON DELETE CASCADE , FOREIGN KEY (USER_ID) REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID) ON DELETE CASCADE
);
# ----------------------------------------------------------------------- # SECURITY_GROUP_ROLE # ----------------------------------------------------------------------- drop table if exists SECURITY_GROUP_ROLE;
CREATE TABLE SECURITY_GROUP_ROLE ( GROUP_ID INTEGER NOT NULL, ROLE_ID INTEGER NOT NULL, PRIMARY KEY(GROUP_ID,ROLE_ID), FOREIGN KEY (GROUP_ID) REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID) ON DELETE CASCADE , FOREIGN KEY (ROLE_ID) REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID) ON DELETE CASCADE
);
-- David Sean Taylor Bluesunrise Software [EMAIL PROTECTED] [office] +01 707 773-4646 [mobile] +01 707 529 9194
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]