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]
