portals-jetspeed-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From tay...@apache.org
Subject cvs commit: jakarta-jetspeed/src/sql/external populate-db2.sql turbine-db2.sql
Date Fri, 18 Oct 2002 22:08:36 GMT
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','raphael@apache.org','CONFIRMED',NULL,NULL,NULL,'F','aced0005737200136a6176612e7574696c2e486173687461626c6513bb0f25214ae4b803000246000a6c6f6164466163746f724900097468726573686f6c6478703f400000000000037708000000050000000274000a4c4153545f4c4f47494e7372000e6a6176612e7574696c2e44617465686a81014b5974190300007870770800000000000000007874000f5f6163636573735f636f756e746572737200116a6176612e6c616e672e496e746567657212e2a0a4f781873802000149000576616c7565787200106a6176612e6c616e672e4e756d62657286ac951d0b94e08b0200007870000000a778');
  -INSERT INTO TURBINE_USER VALUES(2,'turbine','turbine','David','Turbine','taylor@apache.org','CONFIRMED',NULL,NULL,NULL,'F','aced0005737200136a6176612e7574696c2e486173687461626c6513bb0f25214ae4b803000246000a6c6f6164466163746f724900097468726573686f6c6478703f400000000000037708000000050000000274000a4c4153545f4c4f47494e7372000e6a6176612e7574696c2e44617465686a81014b5974190300007870770800000000000000007874000f5f6163636573735f636f756e746572737200116a6176612e6c616e672e496e746567657212e2a0a4f781873802000149000576616c7565787200106a6176612e6c616e672e4e756d62657286ac951d0b94e08b02000078700000007478');
  -INSERT INTO TURBINE_USER VALUES(3,'anon','anon','Anonymous','User','david@bluesunrise.com','CONFIRMED',NULL,NULL,'2001-11-11
18:45:41.671','F',NULL);
  +INSERT INTO TURBINE_USER VALUES(1,'admin','jetspeed','Jetspeed','Admin','admin@jakarta-jetspeed.com','CONFIRMED',NULL,NULL,'2002-10-15
18:45:41.671','F',NULL, NULL);
  +INSERT INTO TURBINE_USER VALUES(2,'turbine','turbine','Tommy','Turbine','tommy@jakarta-jetspeed.com','CONFIRMED',NULL,NULL,'2002-10-15
18:45:41.671','F',NULL, NULL);
  +INSERT INTO TURBINE_USER VALUES(3,'anon','anon','Anonymous','User','anon@jakarta-jetspeed.com','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>


Mime
View raw message