taylor 2002/10/18 15:08:36
Modified: src/sql/external populate-db2.sql turbine-db2.sql
Log:
Generated DDL for DB2 with Torque
Updated populate script
WARNING: these scripts are not tested.
Sorry but I couldn't get the trial version of Personal DB2 to install, it kept
crashing.
Revision Changes Path
1.8 +14 -16 jakarta-jetspeed/src/sql/external/populate-db2.sql
Index: populate-db2.sql
===================================================================
RCS file: /home/cvs/jakarta-jetspeed/src/sql/external/populate-db2.sql,v
retrieving revision 1.7
retrieving revision 1.8
diff -u -r1.7 -r1.8
--- populate-db2.sql 28 Jun 2002 05:37:36 -0000 1.7
+++ populate-db2.sql 18 Oct 2002 22:08:36 -0000 1.8
@@ -11,9 +11,9 @@
INSERT INTO TURBINE_ROLE VALUES(3,'guest',NULL);
INSERT INTO TURBINE_GROUP VALUES(1,'Jetspeed',NULL);
INSERT INTO TURBINE_GROUP VALUES(2,'apache',NULL);
-INSERT INTO TURBINE_USER
VALUES(1,'admin','jetspeed','Raphael','Admin','[EMAIL PROTECTED]','CONFIRMED',NULL,NULL,NULL,'F','aced0005737200136a6176612e7574696c2e486173687461626c6513bb0f25214ae4b803000246000a6c6f6164466163746f724900097468726573686f6c6478703f400000000000037708000000050000000274000a4c4153545f4c4f47494e7372000e6a6176612e7574696c2e44617465686a81014b5974190300007870770800000000000000007874000f5f6163636573735f636f756e746572737200116a6176612e6c616e672e496e746567657212e2a0a4f781873802000149000576616c7565787200106a6176612e6c616e672e4e756d62657286ac951d0b94e08b0200007870000000a778');
-INSERT INTO TURBINE_USER
VALUES(2,'turbine','turbine','David','Turbine','[EMAIL PROTECTED]','CONFIRMED',NULL,NULL,NULL,'F','aced0005737200136a6176612e7574696c2e486173687461626c6513bb0f25214ae4b803000246000a6c6f6164466163746f724900097468726573686f6c6478703f400000000000037708000000050000000274000a4c4153545f4c4f47494e7372000e6a6176612e7574696c2e44617465686a81014b5974190300007870770800000000000000007874000f5f6163636573735f636f756e746572737200116a6176612e6c616e672e496e746567657212e2a0a4f781873802000149000576616c7565787200106a6176612e6c616e672e4e756d62657286ac951d0b94e08b02000078700000007478');
-INSERT INTO TURBINE_USER
VALUES(3,'anon','anon','Anonymous','User','[EMAIL PROTECTED]','CONFIRMED',NULL,NULL,'2001-11-11
18:45:41.671','F',NULL);
+INSERT INTO TURBINE_USER
VALUES(1,'admin','jetspeed','Jetspeed','Admin','[EMAIL PROTECTED]','CONFIRMED',NULL,NULL,'2002-10-15
18:45:41.671','F',NULL, NULL);
+INSERT INTO TURBINE_USER
VALUES(2,'turbine','turbine','Tommy','Turbine','[EMAIL PROTECTED]','CONFIRMED',NULL,NULL,'2002-10-15
18:45:41.671','F',NULL, NULL);
+INSERT INTO TURBINE_USER
VALUES(3,'anon','anon','Anonymous','User','[EMAIL PROTECTED]','CONFIRMED',NULL,NULL,'2002-10-15
18:45:41.671','F',NULL, NULL);
INSERT INTO TURBINE_ROLE_PERMISSION VALUES(1,1);
INSERT INTO TURBINE_ROLE_PERMISSION VALUES(1,2);
INSERT INTO TURBINE_ROLE_PERMISSION VALUES(1,3);
@@ -33,17 +33,15 @@
INSERT INTO TURBINE_USER_GROUP_ROLE VALUES(1,1,1);
INSERT INTO TURBINE_USER_GROUP_ROLE VALUES(1,1,2);
INSERT INTO TURBINE_USER_GROUP_ROLE VALUES(3,1,3);
-INSERT INTO ID_TABLE VALUES(1,'TURBINE_PERMISSION',100,10);
-INSERT INTO ID_TABLE VALUES(2,'TURBINE_ROLE',120,10);
-INSERT INTO ID_TABLE VALUES(3,'TURBINE_GROUP',100,10);
-INSERT INTO ID_TABLE VALUES(4,'TURBINE_USER',120,10);
-INSERT INTO ID_TABLE VALUES(5,'TURBINE_SCHEDULED_JOB',100,10);
-INSERT INTO ID_TABLE VALUES(6,'TURBINE_ROLE_PERMISSION',100,10);
-INSERT INTO ID_TABLE VALUES(7,'TURBINE_USER_GROUP_ROLE',100,10);
-INSERT INTO ID_TABLE VALUES(8,'JETSPEED_USER_PROFILE',120,10);
-INSERT INTO ID_TABLE VALUES(9,'JETSPEED_GROUP_PROFILE',120,10);
-INSERT INTO ID_TABLE VALUES(10,'JETSPEED_ROLE_PROFILE',120,10);
-INSERT INTO ID_TABLE VALUES(11,'JETSPEED_ANON_PROFILE',120,10);
-
-
+INSERT INTO COFFEES VALUES('ColombianGrade',5,7.99,1,2);
+INSERT INTO COFFEES VALUES('KonaGrade',6,7.99,1,2);
+INSERT INTO COFFEES VALUES('FrenchRoastGrade',7,7.99,1,2);
+INSERT INTO COFFEES VALUES('HazelNutGrade',8,7.99,1,2);
+INSERT INTO COFFEES VALUES('VanillaGrade',9,7.99,1,2);
+INSERT INTO COFFEES VALUES('JavaGrade',10,7.99,1,2);
+INSERT INTO COFFEES VALUES('IndonesianGrade',11,7.99,1,2);
+INSERT INTO COFFEES VALUES('OotyGrade',1,7.99,1,2);
+INSERT INTO COFFEES VALUES('KenyanGrade',2,7.99,1,2);
+INSERT INTO COFFEES VALUES('JoeGrade',3,7.99,1,2);
+INSERT INTO COFFEES VALUES('CantThinkOfAnymoreGrade',4,7.99,1,2);
1.4 +136 -102 jakarta-jetspeed/src/sql/external/turbine-db2.sql
Index: turbine-db2.sql
===================================================================
RCS file: /home/cvs/jakarta-jetspeed/src/sql/external/turbine-db2.sql,v
retrieving revision 1.3
retrieving revision 1.4
diff -u -r1.3 -r1.4
--- turbine-db2.sql 25 Feb 2002 04:15:43 -0000 1.3
+++ turbine-db2.sql 18 Oct 2002 22:08:36 -0000 1.4
@@ -1,191 +1,225 @@
-----------------------------------------------------------------------------
--- Please note: early versions of DB2 don't support auto-increment fields
--- (i.e. "GENERATED ALWAYS AS IDENTITY"). With those older versions, an
--- additional ID table has to be used.
------------------------------------------------------------------------------
-
-
-
------------------------------------------------------------------------------
--- TURBINE_PERMISSION
+-- TURBINE_USER
-----------------------------------------------------------------------------
-drop table TURBINE_PERMISSION;
-
+drop table TURBINE_USER;
-CREATE TABLE TURBINE_PERMISSION
+CREATE TABLE TURBINE_USER
(
- PERMISSION_ID INT GENERATED ALWAYS AS IDENTITY,
- PERMISSION_NAME VARCHAR (99) NOT NULL,
- OBJECTDATA BLOB (16777215),
- UNIQUE (PERMISSION_NAME)
+ USER_ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
+ LOGIN_NAME VARCHAR (32) NOT NULL,
+ PASSWORD_VALUE VARCHAR (32) NOT NULL,
+ FIRST_NAME VARCHAR (99) NOT NULL,
+ LAST_NAME VARCHAR (99) NOT NULL,
+ EMAIL VARCHAR (99),
+ CONFIRM_VALUE VARCHAR (99),
+ MODIFIED TIMESTAMP,
+ CREATED TIMESTAMP,
+ LAST_LOGIN TIMESTAMP,
+ DISABLED VARCHAR (1),
+ OBJECTDATA CHAR(1) FOR BIT DATA CHAR(n) FOR BIT DATA,
+ PASSWORD_CHANGED TIMESTAMP,
+ UNIQUE (LOGIN_NAME)
);
-ALTER TABLE TURBINE_PERMISSION
- ADD CONSTRAINT TRB_PERM_PK
-PRIMARY KEY (PERMISSION_ID);
+ALTER TABLE TURBINE_USER
+ ADD PRIMARY KEY (USER_ID);
-
-----------------------------------------------------------------------------
-- TURBINE_ROLE
-----------------------------------------------------------------------------
drop table TURBINE_ROLE;
-
CREATE TABLE TURBINE_ROLE
(
- ROLE_ID INT GENERATED ALWAYS AS IDENTITY,
- ROLE_NAME VARCHAR (99) NOT NULL,
- OBJECTDATA BLOB (16777215),
+ ROLE_ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
+ ROLE_NAME VARCHAR (99) NOT NULL,
+ OBJECTDATA CHAR(99) FOR BIT DATA CHAR(n) FOR BIT DATA,
UNIQUE (ROLE_NAME)
);
ALTER TABLE TURBINE_ROLE
- ADD CONSTRAINT TRB_RL_PK
-PRIMARY KEY (ROLE_ID);
+ ADD PRIMARY KEY (ROLE_ID);
-
-----------------------------------------------------------------------------
-- TURBINE_GROUP
-----------------------------------------------------------------------------
drop table TURBINE_GROUP;
-
CREATE TABLE TURBINE_GROUP
(
- GROUP_ID INT GENERATED ALWAYS AS IDENTITY,
- GROUP_NAME VARCHAR (99) NOT NULL,
- OBJECTDATA BLOB (16777215),
+ GROUP_ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
+ GROUP_NAME VARCHAR (99) NOT NULL,
+ OBJECTDATA CHAR(99) FOR BIT DATA CHAR(n) FOR BIT DATA,
UNIQUE (GROUP_NAME)
);
ALTER TABLE TURBINE_GROUP
- ADD CONSTRAINT TRB_GRP_PK
-PRIMARY KEY (GROUP_ID);
+ ADD PRIMARY KEY (GROUP_ID);
-
-----------------------------------------------------------------------------
--- TURBINE_ROLE_PERMISSION
+-- TURBINE_PERMISSION
-----------------------------------------------------------------------------
-drop table TURBINE_ROLE_PERMISSION;
-
+drop table TURBINE_PERMISSION;
-CREATE TABLE TURBINE_ROLE_PERMISSION
+CREATE TABLE TURBINE_PERMISSION
(
- ROLE_ID INT NOT NULL,
- PERMISSION_ID INT NOT NULL
+ PERMISSION_ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
+ PERMISSION_NAME VARCHAR (99) NOT NULL,
+ OBJECTDATA CHAR(99) FOR BIT DATA CHAR(n) FOR BIT DATA,
+ UNIQUE (PERMISSION_NAME)
);
-ALTER TABLE TURBINE_ROLE_PERMISSION
- ADD CONSTRAINT TRB_RL_PERM_PK
-PRIMARY KEY (ROLE_ID,PERMISSION_ID);
-
-ALTER TABLE TURBINE_ROLE_PERMISSION
- ADD CONSTRAINT TRB_RL_PERM_FK1 FOREIGN KEY (ROLE_ID)
- REFERENCES TURBINE_ROLE (ROLE_ID);
+ALTER TABLE TURBINE_PERMISSION
+ ADD PRIMARY KEY (PERMISSION_ID);
-ALTER TABLE TURBINE_ROLE_PERMISSION
- ADD CONSTRAINT TRB_RL_PERM_FK2 FOREIGN KEY (PERMISSION_ID)
- REFERENCES TURBINE_PERMISSION (PERMISSION_ID);
-
-----------------------------------------------------------------------------
--- TURBINE_USER
+-- TURBINE_ROLE_PERMISSION
-----------------------------------------------------------------------------
-drop table TURBINE_USER;
-
+drop table TURBINE_ROLE_PERMISSION;
-CREATE TABLE TURBINE_USER
+CREATE TABLE TURBINE_ROLE_PERMISSION
(
- USER_ID INT GENERATED ALWAYS AS IDENTITY,
- LOGIN_NAME VARCHAR (32) NOT NULL,
- PASSWORD_VALUE VARCHAR (32) NOT NULL,
- FIRST_NAME VARCHAR (99) NOT NULL,
- LAST_NAME VARCHAR (99) NOT NULL,
- EMAIL VARCHAR (99),
- CONFIRM_VALUE VARCHAR (99),
- MODIFIED DATE,
- CREATED DATE,
- LAST_LOGIN DATE,
- DISABLED VARCHAR(1),
- OBJECTDATA BLOB (16777215),
- UNIQUE (LOGIN_NAME)
+ ROLE_ID INTEGER NOT NULL,
+ PERMISSION_ID INTEGER NOT NULL
);
-ALTER TABLE TURBINE_USER
- ADD CONSTRAINT TRB_USR_PK
-PRIMARY KEY (USER_ID);
-
+ALTER TABLE TURBINE_ROLE_PERMISSION
+ ADD PRIMARY KEY (ROLE_ID,PERMISSION_ID);
+ALTER TABLE TURBINE_ROLE_PERMISSION
+ ADD CONSTRAINT TURBINE_ROLE__FK_1 FOREIGN KEY (ROLE_ID)
+ REFERENCES TURBINE_ROLE (ROLE_ID)
+;
+ALTER TABLE TURBINE_ROLE_PERMISSION
+ ADD CONSTRAINT TURBINE_ROLE__FK_2 FOREIGN KEY (PERMISSION_ID)
+ REFERENCES TURBINE_PERMISSION (PERMISSION_ID)
+;
-
-----------------------------------------------------------------------------
-- TURBINE_USER_GROUP_ROLE
-----------------------------------------------------------------------------
drop table TURBINE_USER_GROUP_ROLE;
-
CREATE TABLE TURBINE_USER_GROUP_ROLE
(
- USER_ID INT NOT NULL,
- GROUP_ID INT NOT NULL,
- ROLE_ID INT NOT NULL
+ USER_ID INTEGER NOT NULL,
+ GROUP_ID INTEGER NOT NULL,
+ ROLE_ID INTEGER NOT NULL
);
ALTER TABLE TURBINE_USER_GROUP_ROLE
- ADD CONSTRAINT TRB_USR_GRP_RL_PK
-PRIMARY KEY (USER_ID,GROUP_ID,ROLE_ID);
+ ADD PRIMARY KEY (USER_ID,GROUP_ID,ROLE_ID);
ALTER TABLE TURBINE_USER_GROUP_ROLE
- ADD CONSTRAINT TRB_USR_GRP_RL_FK1 FOREIGN KEY (USER_ID)
- REFERENCES TURBINE_USER (USER_ID);
-
+ ADD CONSTRAINT TURBINE_USER__FK_1 FOREIGN KEY (USER_ID)
+ REFERENCES TURBINE_USER (USER_ID)
+;
ALTER TABLE TURBINE_USER_GROUP_ROLE
- ADD CONSTRAINT TRB_USR_GRP_RL_FK2 FOREIGN KEY (GROUP_ID)
- REFERENCES TURBINE_GROUP (GROUP_ID);
-
+ ADD CONSTRAINT TURBINE_USER__FK_2 FOREIGN KEY (GROUP_ID)
+ REFERENCES TURBINE_GROUP (GROUP_ID)
+;
ALTER TABLE TURBINE_USER_GROUP_ROLE
- ADD CONSTRAINT TRB_USR_GRP_RL_FK3 FOREIGN KEY (ROLE_ID)
- REFERENCES TURBINE_ROLE (ROLE_ID);
+ ADD CONSTRAINT TURBINE_USER__FK_3 FOREIGN KEY (ROLE_ID)
+ REFERENCES TURBINE_ROLE (ROLE_ID)
+;
+
+-----------------------------------------------------------------------------
+-- JETSPEED_USER_PROFILE
+-----------------------------------------------------------------------------
+drop table JETSPEED_USER_PROFILE;
+
+CREATE TABLE JETSPEED_USER_PROFILE
+(
+ PSML_ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
+ USER_NAME VARCHAR (32) NOT NULL,
+ MEDIA_TYPE VARCHAR (99),
+ LANGUAGE VARCHAR (2),
+ COUNTRY VARCHAR (2),
+ PAGE VARCHAR (99),
+ PROFILE CHAR(99) FOR BIT DATA CHAR(n) FOR BIT DATA,
+ UNIQUE (USER_NAME, MEDIA_TYPE, LANGUAGE, COUNTRY, PAGE)
+);
+
+ALTER TABLE JETSPEED_USER_PROFILE
+ ADD PRIMARY KEY (PSML_ID);
-
-----------------------------------------------------------------------------
--- TURBINE_JOBENTRY
+-- JETSPEED_GROUP_PROFILE
-----------------------------------------------------------------------------
-drop table TURBINE_JOBENTRY;
+drop table JETSPEED_GROUP_PROFILE;
+
+CREATE TABLE JETSPEED_GROUP_PROFILE
+(
+ PSML_ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
+ GROUP_NAME VARCHAR (99) NOT NULL,
+ MEDIA_TYPE VARCHAR (99),
+ LANGUAGE VARCHAR (2),
+ COUNTRY VARCHAR (2),
+ PAGE VARCHAR (99),
+ PROFILE CHAR(99) FOR BIT DATA CHAR(n) FOR BIT DATA,
+ UNIQUE (GROUP_NAME, MEDIA_TYPE, LANGUAGE, COUNTRY, PAGE)
+);
+
+ALTER TABLE JETSPEED_GROUP_PROFILE
+ ADD PRIMARY KEY (PSML_ID);
-CREATE TABLE TURBINE_JOBENTRY
+
+
+-----------------------------------------------------------------------------
+-- JETSPEED_ROLE_PROFILE
+-----------------------------------------------------------------------------
+drop table JETSPEED_ROLE_PROFILE;
+
+CREATE TABLE JETSPEED_ROLE_PROFILE
(
- JOB_ID INT NOT NULL,
- SECOND INT default -1 NOT NULL,
- MINUTE INT default -1 NOT NULL,
- HOUR INT default -1 NOT NULL,
- WEEKDAY INT default -1 NOT NULL,
- DAY_OF_MONTH INT default -1 NOT NULL,
- TASK VARCHAR (99) NOT NULL,
- EMAIL VARCHAR (99)
+ PSML_ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
+ ROLE_NAME VARCHAR (99) NOT NULL,
+ MEDIA_TYPE VARCHAR (99),
+ LANGUAGE VARCHAR (2),
+ COUNTRY VARCHAR (2),
+ PAGE VARCHAR (99),
+ PROFILE CHAR(99) FOR BIT DATA CHAR(n) FOR BIT DATA,
+ UNIQUE (ROLE_NAME, MEDIA_TYPE, LANGUAGE, COUNTRY, PAGE)
);
-ALTER TABLE TURBINE_JOBENTRY
- ADD CONSTRAINT TRB_JOBENTRY_PK
-PRIMARY KEY (JOB_ID);
+ALTER TABLE JETSPEED_ROLE_PROFILE
+ ADD PRIMARY KEY (PSML_ID);
+
+
+-----------------------------------------------------------------------------
+-- COFFEES
+-----------------------------------------------------------------------------
+drop table COFFEES;
+
+CREATE TABLE COFFEES
+(
+ COF_NAME VARCHAR (50),
+ SUP_ID INTEGER,
+ PRICE FLOAT,
+ SALES INTEGER,
+ TOTAL INTEGER
+);
+ALTER TABLE COFFEES
+ ADD PRIMARY KEY ();
--
To unsubscribe, e-mail: <mailto:jetspeed-dev-unsubscribe@;jakarta.apache.org>
For additional commands, e-mail: <mailto:jetspeed-dev-help@;jakarta.apache.org>
