You are viewing a plain text version of this content. The canonical link for it is here.
Posted to jetspeed-dev@portals.apache.org by ki...@apache.org on 2002/06/10 17:23:56 UTC
cvs commit: jakarta-jetspeed/src/sql/external populate-sqlserver.sql turbine-sqlserver.sql
kimptoc 2002/06/10 08:23:56
Added: src/sql/external populate-sqlserver.sql
turbine-sqlserver.sql
Log:
added scripts for sqlserver - based on the sybase scripts
Revision Changes Path
1.1 jakarta-jetspeed/src/sql/external/populate-sqlserver.sql
Index: populate-sqlserver.sql
===================================================================
INSERT INTO TURBINE_PERMISSION VALUES(1,'view',NULL);
INSERT INTO TURBINE_PERMISSION VALUES(2,'customize',NULL);
INSERT INTO TURBINE_PERMISSION VALUES(3,'maximize',NULL);
INSERT INTO TURBINE_PERMISSION VALUES(4,'minimize',NULL);
INSERT INTO TURBINE_PERMISSION VALUES(5,'personalize',NULL);
INSERT INTO TURBINE_PERMISSION VALUES(6,'info',NULL);
INSERT INTO TURBINE_PERMISSION VALUES(7,'close',NULL);
INSERT INTO TURBINE_PERMISSION VALUES(8,'detach',NULL);
INSERT INTO TURBINE_ROLE VALUES(1,'user',NULL);
INSERT INTO TURBINE_ROLE VALUES(2,'admin',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_ROLE_PERMISSION VALUES(1,1);
INSERT INTO TURBINE_ROLE_PERMISSION VALUES(1,2);
INSERT INTO TURBINE_ROLE_PERMISSION VALUES(1,3);
INSERT INTO TURBINE_ROLE_PERMISSION VALUES(1,4);
INSERT INTO TURBINE_ROLE_PERMISSION VALUES(1,5);
INSERT INTO TURBINE_ROLE_PERMISSION VALUES(1,6);
INSERT INTO TURBINE_ROLE_PERMISSION VALUES(2,1);
INSERT INTO TURBINE_ROLE_PERMISSION VALUES(2,2);
INSERT INTO TURBINE_ROLE_PERMISSION VALUES(2,3);
INSERT INTO TURBINE_ROLE_PERMISSION VALUES(2,4);
INSERT INTO TURBINE_ROLE_PERMISSION VALUES(2,5);
INSERT INTO TURBINE_ROLE_PERMISSION VALUES(2,6);
INSERT INTO TURBINE_ROLE_PERMISSION VALUES(2,7);
INSERT INTO TURBINE_USER_GROUP_ROLE VALUES(2,1,1);
INSERT INTO TURBINE_USER_GROUP_ROLE VALUES(1,1,1);
INSERT INTO TURBINE_USER_GROUP_ROLE VALUES(1,1,2);
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);
1.1 jakarta-jetspeed/src/sql/external/turbine-sqlserver.sql
Index: turbine-sqlserver.sql
===================================================================
IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'TURBINE_SCHEDULED_JOB')
BEGIN
DROP TABLE TURBINE_SCHEDULED_JOB
END
;
IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='TURBINE_USER_GROUP_ROLE_FK_1')
ALTER TABLE TURBINE_USER_GROUP_ROLE DROP CONSTRAINT TURBINE_USER_GROUP_ROLE_FK_1
;
IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='TURBINE_USER_GROUP_ROLE_FK_2')
ALTER TABLE TURBINE_USER_GROUP_ROLE DROP CONSTRAINT TURBINE_USER_GROUP_ROLE_FK_2
;
IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='TURBINE_USER_GROUP_ROLE_FK_3')
ALTER TABLE TURBINE_USER_GROUP_ROLE DROP CONSTRAINT TURBINE_USER_GROUP_ROLE_FK_3
;
IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'TURBINE_USER_GROUP_ROLE')
BEGIN
DROP TABLE TURBINE_USER_GROUP_ROLE
END
;
IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'TURBINE_USER')
BEGIN
DROP TABLE TURBINE_USER
END
;
IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='TURBINE_ROLE_PERMISSION_FK_1')
ALTER TABLE TURBINE_ROLE_PERMISSION DROP CONSTRAINT TURBINE_ROLE_PERMISSION_FK_1
;
IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='TURBINE_ROLE_PERMISSION_FK_2')
ALTER TABLE TURBINE_ROLE_PERMISSION DROP CONSTRAINT TURBINE_ROLE_PERMISSION_FK_2
;
IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'TURBINE_ROLE_PERMISSION')
BEGIN
DROP TABLE TURBINE_ROLE_PERMISSION
END
;
IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'TURBINE_GROUP')
BEGIN
DROP TABLE TURBINE_GROUP
END
;
IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'TURBINE_ROLE')
BEGIN
DROP TABLE TURBINE_ROLE
END
;
/* ---------------------------------------------------------------------- */
/* TURBINE_PERMISSION */
/* ---------------------------------------------------------------------- */
IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'TURBINE_PERMISSION')
BEGIN
DROP TABLE TURBINE_PERMISSION
END
;
CREATE TABLE TURBINE_PERMISSION
(
PERMISSION_ID INT NOT NULL,
PERMISSION_NAME VARCHAR (99) NOT NULL,
OBJECTDATA CHAR(10),
CONSTRAINT TURBINE_PERMISSION_PK PRIMARY KEY(PERMISSION_ID),
UNIQUE (PERMISSION_NAME)
);
/* ---------------------------------------------------------------------- */
/* TURBINE_ROLE */
/* ---------------------------------------------------------------------- */
CREATE TABLE TURBINE_ROLE
(
ROLE_ID INT NOT NULL,
ROLE_NAME VARCHAR (99) NOT NULL,
OBJECTDATA CHAR(10),
CONSTRAINT TURBINE_ROLE_PK PRIMARY KEY(ROLE_ID),
UNIQUE (ROLE_NAME)
);
/* ---------------------------------------------------------------------- */
/* TURBINE_GROUP */
/* ---------------------------------------------------------------------- */
CREATE TABLE TURBINE_GROUP
(
GROUP_ID INT NOT NULL,
GROUP_NAME VARCHAR (99) NOT NULL,
OBJECTDATA CHAR(10),
CONSTRAINT TURBINE_GROUP_PK PRIMARY KEY(GROUP_ID),
UNIQUE (GROUP_NAME)
);
/* ---------------------------------------------------------------------- */
/* TURBINE_ROLE_PERMISSION */
/* ---------------------------------------------------------------------- */
CREATE TABLE TURBINE_ROLE_PERMISSION
(
ROLE_ID INT NOT NULL,
PERMISSION_ID INT NOT NULL,
CONSTRAINT TURBINE_ROLE_PERMISSION_PK PRIMARY KEY(ROLE_ID,PERMISSION_ID)
);
ALTER TABLE TURBINE_ROLE_PERMISSION
ADD CONSTRAINT TURBINE_ROLE_PERMISSION_FK_1 FOREIGN KEY (ROLE_ID)
REFERENCES TURBINE_ROLE (ROLE_ID)
;
ALTER TABLE TURBINE_ROLE_PERMISSION
ADD CONSTRAINT TURBINE_ROLE_PERMISSION_FK_2 FOREIGN KEY (PERMISSION_ID)
REFERENCES TURBINE_PERMISSION (PERMISSION_ID)
;
CREATE INDEX ROLE_PERMISSION_INDEX ON TURBINE_ROLE_PERMISSION (ROLE_ID, PERMISSION_ID);
/* ---------------------------------------------------------------------- */
/* TURBINE_USER */
/* ---------------------------------------------------------------------- */
CREATE TABLE TURBINE_USER
(
USER_ID INT NOT NULL,
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 DATETIME,
CREATED DATETIME,
LAST_LOGIN DATETIME,
DISABLED VARCHAR(1),
OBJECTDATA CHAR(10),
CONSTRAINT TURBINE_USER_PK PRIMARY KEY(USER_ID),
UNIQUE (LOGIN_NAME)
);
/* ---------------------------------------------------------------------- */
/* 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,
CONSTRAINT TURBINE_USER_GROUP_ROLE_PK PRIMARY KEY(USER_ID,GROUP_ID,ROLE_ID)
);
ALTER TABLE TURBINE_USER_GROUP_ROLE
ADD CONSTRAINT TURBINE_USER_GROUP_ROLE_FK_1 FOREIGN KEY (USER_ID)
REFERENCES TURBINE_USER (USER_ID)
;
ALTER TABLE TURBINE_USER_GROUP_ROLE
ADD CONSTRAINT TURBINE_USER_GROUP_ROLE_FK_2 FOREIGN KEY (GROUP_ID)
REFERENCES TURBINE_GROUP (GROUP_ID)
;
ALTER TABLE TURBINE_USER_GROUP_ROLE
ADD CONSTRAINT TURBINE_USER_GROUP_ROLE_FK_3 FOREIGN KEY (ROLE_ID)
REFERENCES TURBINE_ROLE (ROLE_ID)
;
CREATE INDEX USER_GROUP_ROLE_INDEX ON TURBINE_USER_GROUP_ROLE (USER_ID, GROUP_ID, ROLE_ID);
/* ---------------------------------------------------------------------- */
/* TURBINE_SCHEDULED_JOB */
/* ---------------------------------------------------------------------- */
CREATE TABLE TURBINE_SCHEDULED_JOB
(
JOB_ID INT NOT NULL,
SECOND INT default -1 NOT NULL,
MINUTE INT default -1 NOT NULL,
HOUR INT default -1 NOT NULL,
WEEK_DAY INT default -1 NOT NULL,
DAY_OF_MONTH INT default -1 NOT NULL,
TASK VARCHAR (99) NOT NULL,
EMAIL VARCHAR (99),
CONSTRAINT TURBINE_SCHEDULED_JOB_PK PRIMARY KEY(JOB_ID)
);
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>