Oracle Schema - triggers and the auto increment

["Kuster, Egon" <Eg...@dsto.defence.gov.au>]

When using Jetspeed's default test application the default sql files to
create the schema within an Oracle database do not fully work as oracle does
not understand columns that are auto incremented. Therefore if any code is
written with the thought that the database being used has auto increment
support will not work when the back end database is Oracle. Therefore you
must create a database trigger on the tables that need the automatic
sequence update.

Attached to this email is an updated turbine-oracle.sql file that creates
the relevant triggers on the tables so that oracle acts like it has a auto
increment column on any id columns and therefore all problems disappear. The
user which runs this file will have to have permissions to create triggers.

Could someone please include this updated sql file in both the jetspeed
distribution and turbine distribution for the creation of turbine user's
schema.

 <<turbine-oracle.sql>> 

Egon Kuster
Defence Science & Technology Organisation
Information Technology Division
Information Exploitation Group
Phone: +61 8 8259 5175
Fax: +61 8 8259 5619
Mobile: +61 401 676 479

RE: Oracle Schema - triggers and the auto increment

[David Sean Taylor <da...@bluesunrise.com>]

I checked in a much simpler patch (for now) into
src/sql/external/default_roles_permissions.sql

insert into TURBINE_ROLE (ROLE_ID, ROLE_NAME) values
(TURBINE_ROLE_SEQ.NEXTVAL, 'authenticated_user');

I like the trigger approach that you used.
Try suggesting it to the Turbine list, as a better way to generate unique
ids for Oracle with Torque.
We will soon be switching over to a new TDK, and eventually to
Torque-generated databases

btw -- Please send only one email at a time.
I received 16 copies of the same email from you all at once. It was like
"Incoming!!!" :)

- david



> -----Original Message-----
> From: Kuster, Egon [mailto:Egon.Kuster@dsto.defence.gov.au]
> Sent: Tuesday, March 27, 2001 10:19 PM
> To: Jakarta Jetspeed-Dev (E-mail); Jakarta Jetspeed-User (E-mail);
> Jakarta Turbine-Dev (E-mail); Jakarta Turbine-User (E-mail)
> Subject: Oracle Schema - triggers and the auto increment
>
>
> When using Jetspeed's default test application the default
> sql files to
> create the schema within an Oracle database do not fully work
> as oracle does
> not understand columns that are auto incremented. Therefore
> if any code is
> written with the thought that the database being used has
> auto increment
> support will not work when the back end database is Oracle.
> Therefore you
> must create a database trigger on the tables that need the automatic
> sequence update.
>
> Attached to this email is an updated turbine-oracle.sql file
> that creates
> the relevant triggers on the tables so that oracle acts like
> it has a auto
> increment column on any id columns and therefore all problems
> disappear. The
> user which runs this file will have to have permissions to
> create triggers.
>
> Could someone please include this updated sql file in both
> the jetspeed
> distribution and turbine distribution for the creation of
> turbine user's
> schema.
>
>  <<turbine-oracle.sql>>
>
> Egon Kuster
> Defence Science & Technology Organisation
> Information Technology Division
> Information Exploitation Group
> Phone: +61 8 8259 5175
> Fax: +61 8 8259 5619
> Mobile: +61 401 676 479
>
>



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