Re: Database Related Question

Wed, 02 Feb 2005 12:45:20 -0800

I think David is saying that you should create implementations of the interfaces he references below. I am doing that to allow Jetspeed-2 to use an LDAP directory server instead of a relational database. My strategy has been to check out the Jetspeed-2 code from CVS and then write my own implementations of these classes and wire them in using the jetspeed-spring.xml, security.xml, and a couple other configuration files. A really good set of unit tests exists for the security components already that will tell you if your implementation of those interfaces is correct. You will have good assurance that your implementation is correct when all the component/security tests work. The tests should run out of the box hooked up to your custom implementations.

Your work will be easier than mine since you are only mapping the Jetspeed-2 security tables to your own. Since LDAP is not generally a transactional resource like a relational database, I am having difficulty because the existing suite of security tests is hardwired to use SQL persistence. That said, the work for you is still considerable. I would suggest reading up on Maven, all the tutorials on JAAS, and then the Spring reference manual. The later will show you how to wire the application together using your own security implementations.

Regards,
Mike Long





Shah Amit wrote:

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]


I agree with David. I am following his recommendation for
I am doing this for LDAP, implementing LDAP authentication authorization and

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

Reply via email to