taylor 2003/01/22 23:24:28
Modified: src/sql/external populate-sqlserver.sql
turbine-sqlserver.sql
Log:
patches from Eric Pugh for SQL Server population scripts
Revision Changes Path
1.3 +67 -47 jakarta-jetspeed/src/sql/external/populate-sqlserver.sql
Index: populate-sqlserver.sql
===================================================================
RCS file: /home/cvs/jakarta-jetspeed/src/sql/external/populate-sqlserver.sql,v
retrieving revision 1.2
retrieving revision 1.3
diff -u -r1.2 -r1.3
--- populate-sqlserver.sql 18 Oct 2002 19:16:32 -0000 1.2
+++ populate-sqlserver.sql 23 Jan 2003 07:24:28 -0000 1.3
@@ -1,47 +1,67 @@
-INSERT INTO TURBINE_PERMISSION VALUES('view',NULL);
-INSERT INTO TURBINE_PERMISSION VALUES('customize',NULL);
-INSERT INTO TURBINE_PERMISSION VALUES('maximize',NULL);
-INSERT INTO TURBINE_PERMISSION VALUES('minimize',NULL);
-INSERT INTO TURBINE_PERMISSION VALUES('personalize',NULL);
-INSERT INTO TURBINE_PERMISSION VALUES('info',NULL);
-INSERT INTO TURBINE_PERMISSION VALUES('close',NULL);
-INSERT INTO TURBINE_PERMISSION VALUES('detach',NULL);
-INSERT INTO TURBINE_ROLE VALUES('user',NULL);
-INSERT INTO TURBINE_ROLE VALUES('admin',NULL);
-INSERT INTO TURBINE_ROLE VALUES('guest',NULL);
-INSERT INTO TURBINE_GROUP VALUES('Jetspeed',NULL);
-INSERT INTO TURBINE_GROUP VALUES('apache',NULL);
-INSERT INTO TURBINE_USER
VALUES('admin','jetspeed','Jetspeed','Admin','[EMAIL PROTECTED]','CONFIRMED',NULL,NULL,'2002-10-15
18:45:41.671','F',NULL, NULL);
-INSERT INTO TURBINE_USER
VALUES('turbine','turbine','Tommy','Turbine','[EMAIL PROTECTED]','CONFIRMED',NULL,NULL,'2002-10-15
18:45:41.671','F',NULL, NULL);
-INSERT INTO TURBINE_USER
VALUES('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);
-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_ROLE_PERMISSION VALUES(3,1);
-INSERT INTO TURBINE_ROLE_PERMISSION VALUES(3,6);
-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 TURBINE_USER_GROUP_ROLE VALUES(3,1,3);
-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);
-
+SET IDENTITY_INSERT TURBINE_PERMISSION ON
+INSERT INTO TURBINE_PERMISSION (PERMISSION_ID, PERMISSION_NAME, OBJECTDATA)
VALUES(1,'view',NULL);
+INSERT INTO TURBINE_PERMISSION (PERMISSION_ID, PERMISSION_NAME, OBJECTDATA)
VALUES(2,'customize',NULL);
+INSERT INTO TURBINE_PERMISSION (PERMISSION_ID, PERMISSION_NAME, OBJECTDATA)
VALUES(3,'maximize',NULL);
+INSERT INTO TURBINE_PERMISSION (PERMISSION_ID, PERMISSION_NAME, OBJECTDATA)
VALUES(4,'minimize',NULL);
+INSERT INTO TURBINE_PERMISSION (PERMISSION_ID, PERMISSION_NAME, OBJECTDATA)
VALUES(5,'personalize',NULL);
+INSERT INTO TURBINE_PERMISSION (PERMISSION_ID, PERMISSION_NAME, OBJECTDATA)
VALUES(6,'info',NULL);
+INSERT INTO TURBINE_PERMISSION (PERMISSION_ID, PERMISSION_NAME, OBJECTDATA)
VALUES(7,'close',NULL);
+INSERT INTO TURBINE_PERMISSION (PERMISSION_ID, PERMISSION_NAME, OBJECTDATA)
VALUES(8,'detach',NULL);
+SET IDENTITY_INSERT TURBINE_PERMISSION OFF
+
+SET IDENTITY_INSERT TURBINE_ROLE ON
+INSERT INTO TURBINE_ROLE (ROLE_ID, ROLE_NAME, OBJECTDATA) VALUES(1,'user',NULL);
+INSERT INTO TURBINE_ROLE (ROLE_ID, ROLE_NAME, OBJECTDATA) VALUES(2,'admin',NULL);
+INSERT INTO TURBINE_ROLE (ROLE_ID, ROLE_NAME, OBJECTDATA) VALUES(3,'guest',NULL);
+SET IDENTITY_INSERT TURBINE_ROLE OFF
+
+SET IDENTITY_INSERT TURBINE_GROUP ON
+INSERT INTO TURBINE_GROUP (GROUP_ID, GROUP_NAME, OBJECTDATA)
VALUES(1,'Jetspeed',NULL);
+INSERT INTO TURBINE_GROUP (GROUP_ID, GROUP_NAME, OBJECTDATA)
VALUES(2,'apache',NULL);
+SET IDENTITY_INSERT TURBINE_GROUP OFF
+
+SET IDENTITY_INSERT TURBINE_USER ON
+INSERT INTO TURBINE_USER (USER_ID, LOGIN_NAME, PASSWORD_VALUE, FIRST_NAME,
LAST_NAME, EMAIL, CONFIRM_VALUE, MODIFIED, CREATED, LAST_LOGIN, DISABLED, OBJECTDATA)
VALUES(1,'admin','jetspeed','Raphael','Admin','[EMAIL PROTECTED]','CONFIRMED',NULL,NULL,NULL,'F',null);
+INSERT INTO TURBINE_USER (USER_ID, LOGIN_NAME, PASSWORD_VALUE, FIRST_NAME,
LAST_NAME, EMAIL, CONFIRM_VALUE, MODIFIED, CREATED, LAST_LOGIN, DISABLED, OBJECTDATA)
VALUES(2,'turbine','turbine','David','Turbine','[EMAIL PROTECTED]','CONFIRMED',NULL,NULL,NULL,'F',null);
+INSERT INTO TURBINE_USER (USER_ID, LOGIN_NAME, PASSWORD_VALUE, FIRST_NAME,
LAST_NAME, EMAIL, CONFIRM_VALUE, MODIFIED, CREATED, LAST_LOGIN, DISABLED, OBJECTDATA)
VALUES(3,'anon','anon','Anonymous','User','[EMAIL PROTECTED]','CONFIRMED',NULL,NULL,'2001-11-11
18:45:41.671','F',NULL);
+SET IDENTITY_INSERT TURBINE_USER OFF
+
+
+INSERT INTO TURBINE_ROLE_PERMISSION (ROLE_ID, PERMISSION_ID) VALUES(1,1);
+INSERT INTO TURBINE_ROLE_PERMISSION (ROLE_ID, PERMISSION_ID) VALUES(1,2);
+INSERT INTO TURBINE_ROLE_PERMISSION (ROLE_ID, PERMISSION_ID) VALUES(1,3);
+INSERT INTO TURBINE_ROLE_PERMISSION (ROLE_ID, PERMISSION_ID) VALUES(1,4);
+INSERT INTO TURBINE_ROLE_PERMISSION (ROLE_ID, PERMISSION_ID) VALUES(1,5);
+INSERT INTO TURBINE_ROLE_PERMISSION (ROLE_ID, PERMISSION_ID) VALUES(1,6);
+INSERT INTO TURBINE_ROLE_PERMISSION (ROLE_ID, PERMISSION_ID) VALUES(2,1);
+INSERT INTO TURBINE_ROLE_PERMISSION (ROLE_ID, PERMISSION_ID) VALUES(2,2);
+INSERT INTO TURBINE_ROLE_PERMISSION (ROLE_ID, PERMISSION_ID) VALUES(2,3);
+INSERT INTO TURBINE_ROLE_PERMISSION (ROLE_ID, PERMISSION_ID) VALUES(2,4);
+INSERT INTO TURBINE_ROLE_PERMISSION (ROLE_ID, PERMISSION_ID) VALUES(2,5);
+INSERT INTO TURBINE_ROLE_PERMISSION (ROLE_ID, PERMISSION_ID) VALUES(2,6);
+INSERT INTO TURBINE_ROLE_PERMISSION (ROLE_ID, PERMISSION_ID) VALUES(2,7);
+INSERT INTO TURBINE_ROLE_PERMISSION (ROLE_ID, PERMISSION_ID) VALUES(3,1);
+INSERT INTO TURBINE_ROLE_PERMISSION (ROLE_ID, PERMISSION_ID) VALUES(3,6);
+
+
+SET IDENTITY_INSERT TURBINE_USER_GROUP_ROLE ON
+INSERT INTO TURBINE_USER_GROUP_ROLE (USER_ID, GROUP_ID, ROLE_ID) VALUES(2,1,1);
+INSERT INTO TURBINE_USER_GROUP_ROLE (USER_ID, GROUP_ID, ROLE_ID) VALUES(1,1,1);
+INSERT INTO TURBINE_USER_GROUP_ROLE (USER_ID, GROUP_ID, ROLE_ID) VALUES(1,1,2);
+INSERT INTO TURBINE_USER_GROUP_ROLE (USER_ID, GROUP_ID, ROLE_ID) VALUES(3,1,3);
+SET IDENTITY_INSERT TURBINE_USER_GROUP_ROLE OFF
+
+SET IDENTITY_INSERT COFFEES ON
+INSERT INTO COFFEES (COFFEE_ID, COFFEE_NAME, SUPPLIER_ID, PRICE, SALES, TOTAL)
VALUES(1,'ColombianGrade',5,7.99E0,1,2)
+INSERT INTO COFFEES (COFFEE_ID, COFFEE_NAME, SUPPLIER_ID, PRICE, SALES, TOTAL)
VALUES(2,'KonaGrade',6,7.99E0,1,2)
+INSERT INTO COFFEES (COFFEE_ID, COFFEE_NAME, SUPPLIER_ID, PRICE, SALES, TOTAL)
VALUES(3,'FrenchRoastGrade',7,7.99E0,1,2)
+INSERT INTO COFFEES (COFFEE_ID, COFFEE_NAME, SUPPLIER_ID, PRICE, SALES, TOTAL)
VALUES(4,'HazelNutGrade',8,7.99E0,1,2)
+INSERT INTO COFFEES (COFFEE_ID, COFFEE_NAME, SUPPLIER_ID, PRICE, SALES, TOTAL)
VALUES(5,'VanillaGrade',9,7.99E0,1,2)
+INSERT INTO COFFEES (COFFEE_ID, COFFEE_NAME, SUPPLIER_ID, PRICE, SALES, TOTAL)
VALUES(6,'JavaGrade',10,7.99E0,1,2)
+INSERT INTO COFFEES (COFFEE_ID, COFFEE_NAME, SUPPLIER_ID, PRICE, SALES, TOTAL)
VALUES(7,'IndonesianGrade',11,7.99E0,1,2)
+INSERT INTO COFFEES (COFFEE_ID, COFFEE_NAME, SUPPLIER_ID, PRICE, SALES, TOTAL)
VALUES(8,'OotyGrade',1,7.99E0,1,2)
+INSERT INTO COFFEES (COFFEE_ID, COFFEE_NAME, SUPPLIER_ID, PRICE, SALES, TOTAL)
VALUES(9,'KenyanGrade',2,7.99E0,1,2)
+INSERT INTO COFFEES (COFFEE_ID, COFFEE_NAME, SUPPLIER_ID, PRICE, SALES, TOTAL)
VALUES(10,'JoeGrade',3,7.99E0,1,2)
+INSERT INTO COFFEES (COFFEE_ID, COFFEE_NAME, SUPPLIER_ID, PRICE, SALES, TOTAL)
VALUES(11,'CantThinkOfAnymoreGrade',4,7.99E0,1,2)
+SET IDENTITY_INSERT COFFEES OFF
+
1.5 +1 -1 jakarta-jetspeed/src/sql/external/turbine-sqlserver.sql
Index: turbine-sqlserver.sql
===================================================================
RCS file: /home/cvs/jakarta-jetspeed/src/sql/external/turbine-sqlserver.sql,v
retrieving revision 1.4
retrieving revision 1.5
diff -u -r1.4 -r1.5
--- turbine-sqlserver.sql 5 Dec 2002 20:00:07 -0000 1.4
+++ turbine-sqlserver.sql 23 Jan 2003 07:24:28 -0000 1.5
@@ -1 +1 @@
-/* ---------------------------------------------------------------------- */
/*
TURBINE_USER */
/*
---------------------------------------------------------------------- */
IF EXISTS
(SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'TURBINE_USER')
BEGIN
DECLARE @reftable_1 nvarchar(60), @constraintname_1 nvarchar(60)
DECLARE
refcursor CURSOR FOR
select reftables.name tablename, cons.name constraintname
from sysobjects tables,
sysobjects reftables,
sysobjects
cons,
sysreferences ref
where tables.id = ref.rkeyid
and
cons.id = ref.constid
and reftables.id = ref.fkeyid
and tables.name
= 'TURBINE_USER'
OPEN refcursor
FETCH NEXT from refcursor into @reftable_1,
@constraintname_1
while @@FETCH_STATUS = 0
BEGIN
exec ('alter table
'+@reftable_1+' drop constraint '+@constraintname_1)
FETCH NEXT from refcursor
into @reftable_1, @constraintname_1
END
CLOSE refcursor
DEALLOCATE
refcursor
DROP TABLE TURBINE_USER
END
CREATE TABLE TURBINE_USER
(
USER_ID INT NOT NULL IDENTITY (1,1),
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)
NULL,
CONFIRM_VALUE VARCHAR (99) NULL,
MODIFIED DATETIME NULL,
CREATED DATETIME NULL,
LAST_LOGIN DATETIME NULL,
DISABLED CHAR (1) NULL,
OBJECTDATA BINARY(7132) NULL,
PASSWORD_CHANGED DATETIME NULL,
CONSTRAINT TURBINE_USER_PK PRIMARY KEY(USER_ID),
UNIQUE (LOGIN_NAME));
/*
---------------------------------------------------------------------- */
/*
TURBINE_ROLE */
/*
---------------------------------------------------------------------- */
IF EXISTS
(SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'TURBINE_ROLE')
BEGIN
DECLARE @reftable_2 nvarchar(60), @constraintname_2 nvarchar(60)
DECLARE
refcursor CURSOR FOR
select reftables.name tablename, cons.name constraintname
from sysobjects tables,
sysobjects reftables,
sysobjects
cons,
sysreferences ref
where tables.id = ref.rkeyid
and
cons.id = ref.constid
and reftables.id = ref.fkeyid
and tables.name
= 'TURBINE_ROLE'
OPEN refcursor
FETCH NEXT from refcursor into @reftable_2,
@constraintname_2
while @@FETCH_STATUS = 0
BEGIN
exec ('alter table
'+@reftable_2+' drop constraint '+@constraintname_2)
FETCH NEXT from refcursor
into @reftable_2, @constraintname_2
END
CLOSE refcursor
DEALLOCATE
refcursor
DROP TABLE TURBINE_ROLE
END
CREATE TABLE TURBINE_ROLE
(
ROLE_ID INT NOT NULL IDENTITY (1,1),
ROLE_NAME VARCHAR (99) NOT NULL,
OBJECTDATA BINARY(7132) NULL,
CONSTRAINT TURBINE_ROLE_PK PRIMARY
KEY(ROLE_ID),
UNIQUE (ROLE_NAME));
/*
---------------------------------------------------------------------- */
/*
TURBINE_GROUP */
/*
---------------------------------------------------------------------- */
IF EXISTS
(SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'TURBINE_GROUP')
BEGIN
DECLARE @reftable_3 nvarchar(60), @constraintname_3 nvarchar(60)
DECLARE
refcursor CURSOR FOR
select reftables.name tablename, cons.name constraintname
from sysobjects tables,
sysobjects reftables,
sysobjects
cons,
sysreferences ref
where tables.id = ref.rkeyid
and
cons.id = ref.constid
and reftables.id = ref.fkeyid
and tables.name
= 'TURBINE_GROUP'
OPEN refcursor
FETCH NEXT from refcursor into @reftable_3,
@constraintname_3
while @@FETCH_STATUS = 0
BEGIN
exec ('alter table
'+@reftable_3+' drop constraint '+@constraintname_3)
FETCH NEXT from refcursor
into @reftable_3, @constraintname_3
END
CLOSE refcursor
DEALLOCATE
refcursor
DROP TABLE TURBINE_GROUP
END
CREATE TABLE TURBINE_GROUP
(
GROUP_ID INT NOT NULL IDENTITY (1,1),
GROUP_NAME VARCHAR (99) NOT NULL,
OBJECTDATA BINARY(7132) NULL,
CONSTRAINT TURBINE_GROUP_PK PRIMARY
KEY(GROUP_ID),
UNIQUE (GROUP_NAME));
/*
---------------------------------------------------------------------- */
/*
TURBINE_PERMISSION */
/*
---------------------------------------------------------------------- */
IF EXISTS
(SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'TURBINE_PERMISSION')
BEGIN
DECLARE @reftable_4 nvarchar(60), @constraintname_4 nvarchar(60)
DECLARE
refcursor CURSOR FOR
select reftables.name tablename, cons.name constraintname
from sysobjects tables,
sysobjects reftables,
sysobjects
cons,
sysreferences ref
where tables.id = ref.rkeyid
and
cons.id = ref.constid
and reftables.id = ref.fkeyid
and tables.name
= 'TURBINE_PERMISSION'
OPEN refcursor
FETCH NEXT from refcursor into
@reftable_4, @constraintname_4
while @@FETCH_STATUS = 0
BEGIN
exec
('alter table '+@reftable_4+' drop constraint '+@constraintname_4)
FETCH NEXT
from refcursor into @reftable_4, @constraintname_4
END
CLOSE refcursor
DEALLOCATE refcursor
DROP TABLE TURBINE_PERMISSION
END
CREATE TABLE
TURBINE_PERMISSION
(
PERMISSION_ID INT NOT NULL IDENTITY (1,1),
PERMISSION_NAME VARCHAR (99) NOT NULL,
OBJECTDATA BINARY(7132) NULL,
CONSTRAINT TURBINE_PERMISSION_PK PRIMARY KEY(PERMISSION_ID),
UNIQUE
(PERMISSION_NAME));
/*
---------------------------------------------------------------------- */
/*
TURBINE_ROLE_PERMISSION */
/*
---------------------------------------------------------------------- */
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
DECLARE @reftable_5
nvarchar(60), @constraintname_5 nvarchar(60)
DECLARE refcursor CURSOR FOR
select reftables.name tablename, cons.name constraintname
from sysobjects
tables,
sysobjects reftables,
sysobjects cons,
sysreferences ref
where tables.id = ref.rkeyid
and cons.id =
ref.constid
and reftables.id = ref.fkeyid
and tables.name =
'TURBINE_ROLE_PERMISSION'
OPEN refcursor
FETCH NEXT from refcursor into
@reftable_5, @constraintname_5
while @@FETCH_STATUS = 0
BEGIN
exec
('alter table '+@reftable_5+' drop constraint '+@constraintname_5)
FETCH NEXT
from refcursor into @reftable_5, @constraintname_5
END
CLOSE refcursor
DEALLOCATE refcursor
DROP TABLE TURBINE_ROLE_PERMISSION
END
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));
/*
---------------------------------------------------------------------- */
/*
TURBINE_USER_GROUP_ROLE */
/*
---------------------------------------------------------------------- */
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
DECLARE @reftable_6 nvarchar(60), @constraintname_6 nvarchar(60)
DECLARE
refcursor CURSOR FOR
select reftables.name tablename, cons.name constraintname
from sysobjects tables,
sysobjects reftables,
sysobjects
cons,
sysreferences ref
where tables.id = ref.rkeyid
and
cons.id = ref.constid
and reftables.id = ref.fkeyid
and tables.name
= 'TURBINE_USER_GROUP_ROLE'
OPEN refcursor
FETCH NEXT from refcursor into
@reftable_6, @constraintname_6
while @@FETCH_STATUS = 0
BEGIN
exec
('alter table '+@reftable_6+' drop constraint '+@constraintname_6)
FETCH NEXT
from refcursor into @reftable_6, @constraintname_6
END
CLOSE refcursor
DEALLOCATE refcursor
DROP TABLE TURBINE_USER_GROUP_ROLE
END
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));
/*
---------------------------------------------------------------------- */
/*
TURBINE_USER_GROUP_ROLE */
/*
---------------------------------------------------------------------- */
/*
---------------------------------------------------------------------- */
/*
TURBINE_USER */
/*
---------------------------------------------------------------------- */
/*
---------------------------------------------------------------------- */
/*
TURBINE_ROLE */
/*
---------------------------------------------------------------------- */
/*
---------------------------------------------------------------------- */
/*
TURBINE_GROUP */
/*
---------------------------------------------------------------------- */
/*
---------------------------------------------------------------------- */
/*
TURBINE_PERMISSION */
/*
---------------------------------------------------------------------- */
BEGIN
ALTER
TABLE TURBINE_ROLE_PERMISSION
ADD CONSTRAINT TURBINE_ROLE_PERMISSION_FK_1 FOREIGN
KEY (ROLE_ID)
REFERENCES TURBINE_ROLE (ROLE_ID)
ON DELETE CASCADE
END
;
BEGIN
ALTER TABLE TURBINE_ROLE_PERMISSION
ADD CONSTRAINT
TURBINE_ROLE_PERMISSION_FK_2 FOREIGN KEY (PERMISSION_ID)
REFERENCES
TURBINE_PERMISSION (PERMISSION_ID)
ON DELETE CASCADE
END
;
/*
---------------------------------------------------------------------- */
/*
TURBINE_ROLE_PERMISSION */
/*
---------------------------------------------------------------------- */
BEGIN
ALTER
TABLE TURBINE_USER_GROUP_ROLE
ADD CONSTRAINT TURBINE_USER_GROUP_ROLE_FK_1 FOREIGN
KEY (USER_ID)
REFERENCES TURBINE_USER (USER_ID)
ON DELETE CASCADE
END
;
BEGIN
ALTER TABLE TURBINE_USER_GROUP_ROLE
ADD CONSTRAINT
TURBINE_USER_GROUP_ROLE_FK_2 FOREIGN KEY (GROUP_ID)
REFERENCES TURBINE_GROUP
(GROUP_ID)
ON DELETE CASCADE
END
;
BEGIN
ALTER TABLE TURBINE_USER_GROUP_ROLE
ADD CONSTRAINT TURBINE_USER_GROUP_ROLE_FK_3 FOREIGN KEY (ROLE_ID)
REFERENCES
TURBINE_ROLE (ROLE_ID)
ON DELETE CASCADE
END
;
/*
---------------------------------------------------------------------- */
/*
JETSPEED_USER_PROFILE */
/*
---------------------------------------------------------------------- */
IF EXISTS
(SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'JETSPEED_USER_PROFILE')
BEGIN
DECLARE @reftable_11 nvarchar(60), @constraintname_11 nvarchar(60)
DECLARE
refcursor CURSOR FOR
select reftables.name tablename, cons.name constraintname
from sysobjects tables,
sysobjects reftables,
sysobjects
cons,
sysreferences ref
where tables.id = ref.rkeyid
and
cons.id = ref.constid
and reftables.id = ref.fkeyid
and tables.name
= 'JETSPEED_USER_PROFILE'
OPEN refcursor
FETCH NEXT from refcursor into
@reftable_11, @constraintname_11
while @@FETCH_STATUS = 0
BEGIN
exec
('alter table '+@reftable_11+' drop constraint '+@constraintname_11)
FETCH NEXT
from refcursor into @reftable_11, @constraintname_11
END
CLOSE refcursor
DEALLOCATE refcursor
DROP TABLE JETSPEED_USER_PROFILE
END
CREATE TABLE
JETSPEED_USER_PROFILE
(
PSML_ID INT NOT NULL IDENTITY (1,1),
USER_NAME VARCHAR (32) NOT NULL,
MEDIA_TYPE VARCHAR (99) NULL,
LANGUAGE VARCHAR (2) NULL,
COUNTRY VARCHAR (2) NULL,
PAGE
VARCHAR (99) NULL,
PROFILE BINARY NULL,
CONSTRAINT
JETSPEED_USER_PROFILE_PK PRIMARY KEY(PSML_ID),
UNIQUE (USER_NAME, MEDIA_TYPE,
LANGUAGE, COUNTRY, PAGE));
/*
---------------------------------------------------------------------- */
/*
JETSPEED_GROUP_PROFILE */
/*
---------------------------------------------------------------------- */
IF EXISTS
(SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'JETSPEED_GROUP_PROFILE')
BEGIN
DECLARE @reftable_12 nvarchar(60), @constraintname_12 nvarchar(60)
DECLARE
refcursor CURSOR FOR
select reftables.name tablename, cons.name constraintname
from sysobjects tables,
sysobjects reftables,
sysobjects
cons,
sysreferences ref
where tables.id = ref.rkeyid
and
cons.id = ref.constid
and reftables.id = ref.fkeyid
and tables.name
= 'JETSPEED_GROUP_PROFILE'
OPEN refcursor
FETCH NEXT from refcursor into
@reftable_12, @constraintname_12
while @@FETCH_STATUS = 0
BEGIN
exec
('alter table '+@reftable_12+' drop constraint '+@constraintname_12)
FETCH NEXT
from refcursor into @reftable_12, @constraintname_2
END
CLOSE refcursor
DEALLOCATE refcursor
DROP TABLE JETSPEED_GROUP_PROFILE
END
CREATE TABLE
JETSPEED_GROUP_PROFILE
(
PSML_ID INT NOT NULL IDENTITY (1,1),
GROUP_NAME VARCHAR (99) NOT NULL,
MEDIA_TYPE VARCHAR (99) NULL,
LANGUAGE VARCHAR (2) NULL,
COUNTRY VARCHAR (2) NULL,
PAGE
VARCHAR (99) NULL,
PROFILE BINARY NULL,
CONSTRAINT
JETSPEED_GROUP_PROFILE_PK PRIMARY KEY(PSML_ID),
UNIQUE (GROUP_NAME, MEDIA_TYPE,
LANGUAGE, COUNTRY, PAGE));
/*
---------------------------------------------------------------------- */
/*
JETSPEED_ROLE_PROFILE */
/*
---------------------------------------------------------------------- */
IF EXISTS
(SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'JETSPEED_ROLE_PROFILE')
BEGIN
DECLARE @reftable_13 nvarchar(60), @constraintname_13 nvarchar(60)
DECLARE
refcursor CURSOR FOR
select reftables.name tablename, cons.name constraintname
from sysobjects tables,
sysobjects reftables,
sysobjects
cons,
sysreferences ref
where tables.id = ref.rkeyid
and
cons.id = ref.constid
and reftables.id = ref.fkeyid
and tables.name
= 'JETSPEED_ROLE_PROFILE'
OPEN refcursor
FETCH NEXT from refcursor into
@reftable_13, @constraintname_13
while @@FETCH_STATUS = 0
BEGIN
exec
('alter table '+@reftable_13+' drop constraint '+@constraintname_13)
FETCH NEXT
from refcursor into @reftable_13, @constraintname_13
END
CLOSE refcursor
DEALLOCATE refcursor
DROP TABLE JETSPEED_ROLE_PROFILE
END
CREATE TABLE
JETSPEED_ROLE_PROFILE
(
PSML_ID INT NOT NULL IDENTITY (1,1),
ROLE_NAME VARCHAR (99) NOT NULL,
MEDIA_TYPE VARCHAR (99) NULL,
LANGUAGE VARCHAR (2) NULL,
COUNTRY VARCHAR (2) NULL,
PAGE
VARCHAR (99) NULL,
PROFILE BINARY NULL,
CONSTRAINT
JETSPEED_ROLE_PROFILE_PK PRIMARY KEY(PSML_ID),
UNIQUE (ROLE_NAME, MEDIA_TYPE,
LANGUAGE, COUNTRY, PAGE));
/*
---------------------------------------------------------------------- */
/* COFFEES
*/
/*
---------------------------------------------------------------------- */
IF EXISTS
(SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'COFFEES')
BEGIN
DECLARE
@reftable_1 nvarchar(60), @constraintname_1 nvarchar(60)
DECLARE refcursor CURSOR
FOR
select reftables.name tablename, cons.name constraintname
from
sysobjects tables,
sysobjects reftables,
sysobjects cons,
sysreferences ref
where tables.id = ref.rkeyid
and cons.id =
ref.constid
and reftables.id = ref.fkeyid
and tables.name =
'COFFEES'
OPEN refcursor
FETCH NEXT from refcursor into @reftable_1,
@constraintname_1
while @@FETCH_STATUS = 0
BEGIN
exec ('alter table
'+@reftable_1+' drop constraint '+@constraintname_1)
FETCH NEXT from refcursor
into @reftable_1, @constraintname_1
END
CLOSE refcursor
DEALLOCATE
refcursor
DROP TABLE COFFEES
END
CREATE TABLE COFFEES
(
COFFEE_ID INT NOT
NULL IDENTITY (1,1),
COFFEE_NAME VARCHAR (50) NULL,
SUPPLIER_ID INT NULL,
PRICE FLOAT NULL,
SALES INT NULL,
TOTAL INT NULL,
CONSTRAINT COFFEES_PK
PRIMARY KEY(COFFEE_ID));
\ No newline at end of file
+/* ---------------------------------------------------------------------- */
/*
TURBINE_USER */
/*
---------------------------------------------------------------------- */
IF EXISTS
(SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'TURBINE_USER')
BEGIN
DECLARE @reftable_1 nvarchar(60), @constraintname_1 nvarchar(60)
DECLARE
refcursor CURSOR FOR
select reftables.name tablename, cons.name constraintname
from sysobjects tables,
sysobjects reftables,
sysobjects
cons,
sysreferences ref
where tables.id = ref.rkeyid
and
cons.id = ref.constid
and reftables.id = ref.fkeyid
and tables.name
= 'TURBINE_USER'
OPEN refcursor
FETCH NEXT from refcursor into @reftable_1,
@constraintname_1
while @@FETCH_STATUS = 0
BEGIN
exec ('alter table
'+@reftable_1+' drop constraint '+@constraintname_1)
FETCH NEXT from refcursor
into @reftable_1, @constraintname_1
END
CLOSE refcursor
DEALLOCATE
refcursor
DROP TABLE TURBINE_USER
END
CREATE TABLE TURBINE_USER
(
USER_ID INT NOT NULL IDENTITY (1,1),
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)
NULL,
CONFIRM_VALUE VARCHAR (99) NULL,
MODIFIED DATETIME NULL,
CREATED DATETIME NULL,
LAST_LOGIN DATETIME NULL,
DISABLED CHAR (1) NULL,
OBJECTDATA BINARY(7132) NULL,
PASSWORD_CHANGED DATETIME NULL,
CONSTRAINT TURBINE_USER_PK PRIMARY KEY(USER_ID),
UNIQUE (LOGIN_NAME));
/*
---------------------------------------------------------------------- */
/*
TURBINE_ROLE */
/*
---------------------------------------------------------------------- */
IF EXISTS
(SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'TURBINE_ROLE')
BEGIN
DECLARE @reftable_2 nvarchar(60), @constraintname_2 nvarchar(60)
DECLARE
refcursor CURSOR FOR
select reftables.name tablename, cons.name constraintname
from sysobjects tables,
sysobjects reftables,
sysobjects
cons,
sysreferences ref
where tables.id = ref.rkeyid
and
cons.id = ref.constid
and reftables.id = ref.fkeyid
and tables.name
= 'TURBINE_ROLE'
OPEN refcursor
FETCH NEXT from refcursor into @reftable_2,
@constraintname_2
while @@FETCH_STATUS = 0
BEGIN
exec ('alter table
'+@reftable_2+' drop constraint '+@constraintname_2)
FETCH NEXT from refcursor
into @reftable_2, @constraintname_2
END
CLOSE refcursor
DEALLOCATE
refcursor
DROP TABLE TURBINE_ROLE
END
CREATE TABLE TURBINE_ROLE
(
ROLE_ID INT NOT NULL IDENTITY (1,1),
ROLE_NAME VARCHAR (99) NOT NULL,
OBJECTDATA BINARY(7132) NULL,
CONSTRAINT TURBINE_ROLE_PK PRIMARY
KEY(ROLE_ID),
UNIQUE (ROLE_NAME));
/*
---------------------------------------------------------------------- */
/*
TURBINE_GROUP */
/*
---------------------------------------------------------------------- */
IF EXISTS
(SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'TURBINE_GROUP')
BEGIN
DECLARE @reftable_3 nvarchar(60), @constraintname_3 nvarchar(60)
DECLARE
refcursor CURSOR FOR
select reftables.name tablename, cons.name constraintname
from sysobjects tables,
sysobjects reftables,
sysobjects
cons,
sysreferences ref
where tables.id = ref.rkeyid
and
cons.id = ref.constid
and reftables.id = ref.fkeyid
and tables.name
= 'TURBINE_GROUP'
OPEN refcursor
FETCH NEXT from refcursor into @reftable_3,
@constraintname_3
while @@FETCH_STATUS = 0
BEGIN
exec ('alter table
'+@reftable_3+' drop constraint '+@constraintname_3)
FETCH NEXT from refcursor
into @reftable_3, @constraintname_3
END
CLOSE refcursor
DEALLOCATE
refcursor
DROP TABLE TURBINE_GROUP
END
CREATE TABLE TURBINE_GROUP
(
GROUP_ID INT NOT NULL IDENTITY (1,1),
GROUP_NAME VARCHAR (99) NOT NULL,
OBJECTDATA BINARY(7132) NULL,
CONSTRAINT TURBINE_GROUP_PK PRIMARY
KEY(GROUP_ID),
UNIQUE (GROUP_NAME));
/*
---------------------------------------------------------------------- */
/*
TURBINE_PERMISSION */
/*
---------------------------------------------------------------------- */
IF EXISTS
(SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'TURBINE_PERMISSION')
BEGIN
DECLARE @reftable_4 nvarchar(60), @constraintname_4 nvarchar(60)
DECLARE
refcursor CURSOR FOR
select reftables.name tablename, cons.name constraintname
from sysobjects tables,
sysobjects reftables,
sysobjects
cons,
sysreferences ref
where tables.id = ref.rkeyid
and
cons.id = ref.constid
and reftables.id = ref.fkeyid
and tables.name
= 'TURBINE_PERMISSION'
OPEN refcursor
FETCH NEXT from refcursor into
@reftable_4, @constraintname_4
while @@FETCH_STATUS = 0
BEGIN
exec
('alter table '+@reftable_4+' drop constraint '+@constraintname_4)
FETCH NEXT
from refcursor into @reftable_4, @constraintname_4
END
CLOSE refcursor
DEALLOCATE refcursor
DROP TABLE TURBINE_PERMISSION
END
CREATE TABLE
TURBINE_PERMISSION
(
PERMISSION_ID INT NOT NULL IDENTITY (1,1),
PERMISSION_NAME VARCHAR (99) NOT NULL,
OBJECTDATA BINARY(7132) NULL,
CONSTRAINT TURBINE_PERMISSION_PK PRIMARY KEY(PERMISSION_ID),
UNIQUE
(PERMISSION_NAME));
/*
---------------------------------------------------------------------- */
/*
TURBINE_ROLE_PERMISSION */
/*
---------------------------------------------------------------------- */
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
DECLARE @reftable_5
nvarchar(60), @constraintname_5 nvarchar(60)
DECLARE refcursor CURSOR FOR
select reftables.name tablename, cons.name constraintname
from sysobjects
tables,
sysobjects reftables,
sysobjects cons,
sysreferences ref
where tables.id = ref.rkeyid
and cons.id =
ref.constid
and reftables.id = ref.fkeyid
and tables.name =
'TURBINE_ROLE_PERMISSION'
OPEN refcursor
FETCH NEXT from refcursor into
@reftable_5, @constraintname_5
while @@FETCH_STATUS = 0
BEGIN
exec
('alter table '+@reftable_5+' drop constraint '+@constraintname_5)
FETCH NEXT
from refcursor into @reftable_5, @constraintname_5
END
CLOSE refcursor
DEALLOCATE refcursor
DROP TABLE TURBINE_ROLE_PERMISSION
END
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));
/*
---------------------------------------------------------------------- */
/*
TURBINE_USER_GROUP_ROLE */
/*
---------------------------------------------------------------------- */
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
DECLARE @reftable_6 nvarchar(60), @constraintname_6 nvarchar(60)
DECLARE
refcursor CURSOR FOR
select reftables.name tablename, cons.name constraintname
from sysobjects tables,
sysobjects reftables,
sysobjects
cons,
sysreferences ref
where tables.id = ref.rkeyid
and
cons.id = ref.constid
and reftables.id = ref.fkeyid
and tables.name
= 'TURBINE_USER_GROUP_ROLE'
OPEN refcursor
FETCH NEXT from refcursor into
@reftable_6, @constraintname_6
while @@FETCH_STATUS = 0
BEGIN
exec
('alter table '+@reftable_6+' drop constraint '+@constraintname_6)
FETCH NEXT
from refcursor into @reftable_6, @constraintname_6
END
CLOSE refcursor
DEALLOCATE refcursor
DROP TABLE TURBINE_USER_GROUP_ROLE
END
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));
/*
---------------------------------------------------------------------- */
/*
TURBINE_USER_GROUP_ROLE */
/*
---------------------------------------------------------------------- */
/*
---------------------------------------------------------------------- */
/*
TURBINE_USER */
/*
---------------------------------------------------------------------- */
/*
---------------------------------------------------------------------- */
/*
TURBINE_ROLE */
/*
---------------------------------------------------------------------- */
/*
---------------------------------------------------------------------- */
/*
TURBINE_GROUP */
/*
---------------------------------------------------------------------- */
/*
---------------------------------------------------------------------- */
/*
TURBINE_PERMISSION */
/*
---------------------------------------------------------------------- */
BEGIN
ALTER
TABLE TURBINE_ROLE_PERMISSION
ADD CONSTRAINT TURBINE_ROLE_PERMISSION_FK_1 FOREIGN
KEY (ROLE_ID)
REFERENCES TURBINE_ROLE (ROLE_ID)
ON DELETE CASCADE
END
;
BEGIN
ALTER TABLE TURBINE_ROLE_PERMISSION
ADD CONSTRAINT
TURBINE_ROLE_PERMISSION_FK_2 FOREIGN KEY (PERMISSION_ID)
REFERENCES
TURBINE_PERMISSION (PERMISSION_ID)
ON DELETE CASCADE
END
;
/*
---------------------------------------------------------------------- */
/*
TURBINE_ROLE_PERMISSION */
/*
---------------------------------------------------------------------- */
BEGIN
ALTER
TABLE TURBINE_USER_GROUP_ROLE
ADD CONSTRAINT TURBINE_USER_GROUP_ROLE_FK_1 FOREIGN
KEY (USER_ID)
REFERENCES TURBINE_USER (USER_ID)
ON DELETE CASCADE
END
;
BEGIN
ALTER TABLE TURBINE_USER_GROUP_ROLE
ADD CONSTRAINT
TURBINE_USER_GROUP_ROLE_FK_2 FOREIGN KEY (GROUP_ID)
REFERENCES TURBINE_GROUP
(GROUP_ID)
ON DELETE CASCADE
END
;
BEGIN
ALTER TABLE TURBINE_USER_GROUP_ROLE
ADD CONSTRAINT TURBINE_USER_GROUP_ROLE_FK_3 FOREIGN KEY (ROLE_ID)
REFERENCES
TURBINE_ROLE (ROLE_ID)
ON DELETE CASCADE
END
;
/*
---------------------------------------------------------------------- */
/*
JETSPEED_USER_PROFILE */
/*
---------------------------------------------------------------------- */
IF EXISTS
(SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'JETSPEED_USER_PROFILE')
BEGIN
DECLARE @reftable_11 nvarchar(60), @constraintname_11 nvarchar(60)
DECLARE
refcursor CURSOR FOR
select reftables.name tablename, cons.name constraintname
from sysobjects tables,
sysobjects reftables,
sysobjects
cons,
sysreferences ref
where tables.id = ref.rkeyid
and
cons.id = ref.constid
and reftables.id = ref.fkeyid
and tables.name
= 'JETSPEED_USER_PROFILE'
OPEN refcursor
FETCH NEXT from refcursor into
@reftable_11, @constraintname_11
while @@FETCH_STATUS = 0
BEGIN
exec
('alter table '+@reftable_11+' drop constraint '+@constraintname_11)
FETCH NEXT
from refcursor into @reftable_11, @constraintname_11
END
CLOSE refcursor
DEALLOCATE refcursor
DROP TABLE JETSPEED_USER_PROFILE
END
CREATE TABLE
JETSPEED_USER_PROFILE
(
PSML_ID INT NOT NULL IDENTITY (1,1),
USER_NAME VARCHAR (32) NOT NULL,
MEDIA_TYPE VARCHAR (99) NULL,
LANGUAGE VARCHAR (2) NULL,
COUNTRY VARCHAR (2) NULL,
PAGE
VARCHAR (99) NULL,
PROFILE BINARY NULL,
CONSTRAINT
JETSPEED_USER_PROFILE_PK PRIMARY KEY(PSML_ID),
UNIQUE (USER_NAME, MEDIA_TYPE,
LANGUAGE, COUNTRY, PAGE));
/*
---------------------------------------------------------------------- */
/*
JETSPEED_GROUP_PROFILE */
/*
---------------------------------------------------------------------- */
IF EXISTS
(SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'JETSPEED_GROUP_PROFILE')
BEGIN
DECLARE @reftable_12 nvarchar(60), @constraintname_12 nvarchar(60)
DECLARE
refcursor CURSOR FOR
select reftables.name tablename, cons.name constraintname
from sysobjects tables,
sysobjects reftables,
sysobjects
cons,
sysreferences ref
where tables.id = ref.rkeyid
and
cons.id = ref.constid
and reftables.id = ref.fkeyid
and tables.name
= 'JETSPEED_GROUP_PROFILE'
OPEN refcursor
FETCH NEXT from refcursor into
@reftable_12, @constraintname_12
while @@FETCH_STATUS = 0
BEGIN
exec
('alter table '+@reftable_12+' drop constraint '+@constraintname_12)
FETCH NEXT
from refcursor into @reftable_12, @constraintname_2
END
CLOSE refcursor
DEALLOCATE refcursor
DROP TABLE JETSPEED_GROUP_PROFILE
END
CREATE TABLE
JETSPEED_GROUP_PROFILE
(
PSML_ID INT NOT NULL IDENTITY (1,1),
GROUP_NAME VARCHAR (99) NOT NULL,
MEDIA_TYPE VARCHAR (99) NULL,
LANGUAGE VARCHAR (2) NULL,
COUNTRY VARCHAR (2) NULL,
PAGE
VARCHAR (99) NULL,
PROFILE BINARY NULL,
CONSTRAINT
JETSPEED_GROUP_PROFILE_PK PRIMARY KEY(PSML_ID),
UNIQUE (GROUP_NAME, MEDIA_TYPE,
LANGUAGE, COUNTRY, PAGE));
/*
---------------------------------------------------------------------- */
/*
JETSPEED_ROLE_PROFILE */
/*
---------------------------------------------------------------------- */
IF EXISTS
(SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'JETSPEED_ROLE_PROFILE')
BEGIN
DECLARE @reftable_13 nvarchar(60), @constraintname_13 nvarchar(60)
DECLARE
refcursor CURSOR FOR
select reftables.name tablename, cons.name constraintname
from sysobjects tables,
sysobjects reftables,
sysobjects
cons,
sysreferences ref
where tables.id = ref.rkeyid
and
cons.id = ref.constid
and reftables.id = ref.fkeyid
and tables.name
= 'JETSPEED_ROLE_PROFILE'
OPEN refcursor
FETCH NEXT from refcursor into
@reftable_13, @constraintname_13
while @@FETCH_STATUS = 0
BEGIN
exec
('alter table '+@reftable_13+' drop constraint '+@constraintname_13)
FETCH NEXT
from refcursor into @reftable_13, @constraintname_13
END
CLOSE refcursor
DEALLOCATE refcursor
DROP TABLE JETSPEED_ROLE_PROFILE
END
CREATE TABLE
JETSPEED_ROLE_PROFILE
(
PSML_ID INT NOT NULL IDENTITY (1,1),
ROLE_NAME VARCHAR (99) NOT NULL,
MEDIA_TYPE VARCHAR (99) NULL,
LANGUAGE VARCHAR (2) NULL,
COUNTRY VARCHAR (2) NULL,
PAGE
VARCHAR (99) NULL,
PROFILE BINARY NULL,
CONSTRAINT
JETSPEED_ROLE_PROFILE_PK PRIMARY KEY(PSML_ID),
UNIQUE (ROLE_NAME, MEDIA_TYPE,
LANGUAGE, COUNTRY, PAGE));
/*
---------------------------------------------------------------------- */
/* COFFEES
*/
/*
---------------------------------------------------------------------- */
IF EXISTS
(SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'COFFEES')
BEGIN
DECLARE
@reftable_21 nvarchar(60), @constraintname_21 nvarchar(60)
DECLARE refcursor
CURSOR FOR
select reftables.name tablename, cons.name constraintname
from
sysobjects tables,
sysobjects reftables,
sysobjects cons,
sysreferences ref
where tables.id = ref.rkeyid
and cons.id =
ref.constid
and reftables.id = ref.fkeyid
and tables.name =
'COFFEES'
OPEN refcursor
FETCH NEXT from refcursor into @reftable_21,
@constraintname_21
while @@FETCH_STATUS = 0
BEGIN
exec ('alter table
'+@reftable_21+' drop constraint '+@constraintname_21)
FETCH NEXT from
refcursor into @reftable_21, @constraintname_21
END
CLOSE refcursor
DEALLOCATE refcursor
DROP TABLE COFFEES
END
CREATE TABLE COFFEES
(
COFFEE_ID INT NOT NULL IDENTITY (1,1),
COFFEE_NAME VARCHAR (50) NULL,
SUPPLIER_ID INT NULL,
PRICE FLOAT NULL,
SALES INT NULL,
TOTAL INT NULL,
CONSTRAINT COFFEES_PK PRIMARY KEY(COFFEE_ID));
\ No newline at end of file
--
To unsubscribe, e-mail: <mailto:[EMAIL PROTECTED]>
For additional commands, e-mail: <mailto:[EMAIL PROTECTED]>
