portals-jetspeed-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "David Sean Taylor" <da...@bluesunrise.com>
Subject RE: Putting .psml/markup info into database
Date Wed, 11 Jul 2001 22:36:43 GMT
> > > PRIMARY KEY (USER_ID,MEDIA_TYPE,LANGUAGE,COUNTRY,PAGE);
> >Are you sure you want this to be the primary key?
> >Turbine always uses a generated 'object id' as the primary
> key, and leave
> >business values to secondary keys.
>
>
> I am not quiet clear about 'object id' as the primary key. My
> understanding

There can be multiple profilers per user, so you can't use the USER_ID by
itself.
I was thinking more along the lines of PROFILE_ID
Anyway, thats just the way TURBINE (and lots of other OR impls) does it.
Its up to you ....

> was, the USER_ID in the following table, is the sequence_id
> obtained from
> TURBINE_USER_SEQ and is the primary key:
> ########## SQLs ###########
> CREATE TABLE TURBINE_USER
> (
>      USER_ID INT NOT NULL,
>      LOGIN_NAME VARCHAR2 (32) NOT NULL,
>      PASSWORD_VALUE VARCHAR2 (32) NOT NULL,
>      FIRST_NAME VARCHAR2 (99) NOT NULL,
>      LAST_NAME VARCHAR2 (99) NOT NULL,
>      EMAIL VARCHAR2 (99),
>      CONFIRM_VALUE VARCHAR2 (99),
>      MODIFIED DATE,
>      CREATED DATE,
>      LASTLOGIN DATE,
>      OBJECTDATA LONG RAW,
>      UNIQUE (LOGIN_NAME)
> );
>
> ALTER TABLE TURBINE_USER
>      ADD CONSTRAINT TURBINE_USER_PK
> PRIMARY KEY (USER_ID);
>
> CREATE SEQUENCE TURBINE_USER_SEQ;
> ############## SQLs #####################
>
> >The USER_ID is dependent on the TURBINE_USER table, if the
> row gets deleted
> >from the TURBINE_USER table, Im not sure how you plan to keep the
> >referential integrity, since I don't see a foreign key.
> Likewise for Role.
>
>
> We will have to provide the remove logic in the
> PSMLManagerService for
> removing the record from these tables too,
> when the admin removes the user from the system, so it will
> be a cascaded
> delete from the user table upto these tables.
>

If you do something like

alter table JETSPEED_USER_PROFILE add ( constraint fk_user foreign key
(user_id) references TURBINE_USER ON DELETE CASCADE);

This will give you declarative referential integrity.
Make sure this syntax works on all supported Jetspeed databases.
I wonder if Torque supports cascading deletes...


>
>
> > > -----Original Message-----
> > > From: Anil Shinde [mailto:ashinde@cisco.com]
> > > Sent: Tuesday, July 10, 2001 3:18 PM
> > > To: jetspeed-dev@jakarta.apache.org
> > > Subject: RE: Putting .psml/markup info into database
> > >
> > >
> > > Hi David/Raphael,
> > >
> > > Let me first introduce myself, I am Anil Shinde, working with
> > > Atul on the
> > > "PSML info .... database" effort.
> > > Me, atul and narendra (one more team member) discussed today
> > > morning about
> > > the schema for storing PSML info.
> > > We are looking at a feedback on the schema, before we proceed
> > > with the code
> > > development.
> > > We have already thought about the code logic to some extent,
> > > developing
> > > that shouldnt take a long.
> > > Also we thought about creating this tables along with other
> > > turbine tables,
> > > so that everything will reside in the same database.
> > >
> > > Here is the final sql script that was written to generate the
> > > required table,
> > >
> > > ------------------------------------------------
> > > -- JETSPEED_USER_PROFILE
> > > ------------------------------------------------
> > > drop table JETSPEED_USER_PROFILE cascade constraints;
> > >
> > > CREATE TABLE JETSPEED_USER_PROFILE
> > > (
> > >          USER_ID INT NOT NULL,
> > >          MEDIA_TYPE VARCHAR2(99),
> > >          LANGUAGE VARCHAR2(99),
> > >          COUNTRY  VARCHAR2(99),
> > >          PAGE VARCHAR2(99),
> > >          PROFILE LONG RAW
> > > );
> > >
> > > ALTER TABLE JETSPEED_USER_PROFILE
> > >          ADD CONSTRAINT JETSPEED_USER_PROFILE_PK
> > > PRIMARY KEY (USER_ID,MEDIA_TYPE,LANGUAGE,COUNTRY,PAGE);
> > >
> > > ------------------------------------------------
> > > -- JETSPEED_GROUP_PROFILE
> > > ------------------------------------------------
> > > drop table JETSPEED_GROUP_PROFILE cascade constraints;
> > >
> > > CREATE TABLE JETSPEED_GROUP_PROFILE
> > > (
> > >          GROUP_ID INT NOT NULL,
> > >          MEDIA_TYPE VARCHAR2(99),
> > >          LANGUAGE VARCHAR2(99),
> > >          COUNTRY  VARCHAR2(99),
> > >          PAGE VARCHAR2(99),
> > >          PROFILE LONG RAW
> > > );
> > >
> > > ALTER TABLE JETSPEED_GROUP_PROFILE
> > >          ADD CONSTRAINT JETSPEED_GROUP_PROFILE_PK
> > > PRIMARY KEY (GROUP_ID,MEDIA_TYPE,LANGUAGE,COUNTRY,PAGE);
> > >
> > > ------------------------------------------------
> > > -- JETSPEED_ROLE_PROFILE
> > > ------------------------------------------------
> > > drop table JETSPEED_ROLE_PROFILE cascade constraints;
> > >
> > > CREATE TABLE JETSPEED_ROLE_PROFILE
> > > (
> > >          ROLE_ID INT NOT NULL,
> > >          MEDIA_TYPE VARCHAR2(99),
> > >          LANGUAGE VARCHAR2(99),
> > >          COUNTRY  VARCHAR2(99),
> > >          PAGE VARCHAR2(99),
> > >          PROFILE LONG RAW
> > > );
> > >
> > > ALTER TABLE JETSPEED_ROLE_PROFILE
> > >          ADD CONSTRAINT JETSPEED_ROLE_PROFILE_PK
> > > PRIMARY KEY (ROLE_ID,MEDIA_TYPE,LANGUAGE,COUNTRY,PAGE);
> > >
> > >
> > >
> >
> >
> >
> >---------------------------------------------------------------------
> >To unsubscribe, e-mail: jetspeed-dev-unsubscribe@jakarta.apache.org
> >For additional commands, e-mail: jetspeed-dev-help@jakarta.apache.org
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: jetspeed-dev-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: jetspeed-dev-help@jakarta.apache.org
>
>



---------------------------------------------------------------------
To unsubscribe, e-mail: jetspeed-dev-unsubscribe@jakarta.apache.org
For additional commands, e-mail: jetspeed-dev-help@jakarta.apache.org


Mime
View raw message