Re: Database Related Question

Wed, 02 Feb 2005 12:25:59 -0800 

Hi David,

Please pardon my stupid questions. I am pretty new with JAAS so ...

By what you suggested, you mean to say that I should have my own implementation for almost all the interfaces in org.apache.jetspeed.security.spi .. ?? and then change the jetspeed-spring.xml file to point to my own interfaces ...

Would that mean I would be changing the source of jetspeed and building again ?? If not, how can I get the classfiles that I woudl write on jetspeed's classpath ....

Appreciate the help ...

Thanks,
Amit


----Original Message Follows----
From: David Sean Taylor <[EMAIL PROTECTED]>
Reply-To: "Jetspeed Users List" <jetspeed-user@jakarta.apache.org>
To: Jetspeed Users List <jetspeed-user@jakarta.apache.org>
Subject: Re: Database Related Question
Date: Tue, 01 Feb 2005 23:11:42 -0800

Shah Amit wrote: 
Thanks for the reply.

Now I already have an existing schema where I have a users table with some users and I check logins against that table in my existing schema.

In that case, how should I configure my authentication mechanism ? Should I translate my current "user" and related tables to "Jetspeed2 Security" schema ?? Like, I just want to know what would be a good design ?? My feeling is I should translate my user and related tables to the jetspeed security model, but would like to get some feedback ...

I don't think that is necessary, although you could do that if its close. Or perhaps a view that combines the two...

A better solutoin may be to provide your own security handlers in the spring configuration. For ex, for credentials, user security, and roles, assemble your components something like:

<!-- Security SPI: CredentialHandler -->
<bean id="org.apache.jetspeed.security.spi.CredentialHandler" class="com.amit.portal.security.spi.impl.AmitCredentialHandler"
>
</bean>

<!-- Security SPI: UserSecurityHandler -->
<bean id="org.apache.jetspeed.security.spi.UserSecurityHandlerImpl" class="com.amit.portal.security.spi.impl.AmitUserSecurityHandler"
>
<constructor-arg ><ref bean="org.apache.jetspeed.security.spi.SecurityAccess"/></constructor-arg>

  </bean>

<!-- Security SPI: RoleSecurityHandler -->
<bean id="org.apache.jetspeed.security.spi.RoleSecurityHandler"
class="com.amit.portal.security.spi.impl.AmitRoleSecurityHandler"
>
<constructor-arg ><ref bean="org.apache.jetspeed.security.spi.SecurityAccess"/></constructor-arg>
</bean>



Thanks for your helps ...
Amit

----Original Message Follows----
From: David Sean Taylor <[EMAIL PROTECTED]>
Reply-To: "Jetspeed Users List" <jetspeed-user@jakarta.apache.org>
To: Jetspeed Users List <jetspeed-user@jakarta.apache.org>
Subject: Re: Database Related Question
Date: Mon, 31 Jan 2005 12:17:16 -0800

Shah Amit wrote:

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]



---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]





--
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]



---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Reply via email to