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 rw...@apache.org on 2005/12/07 07:22:16 UTC
svn commit: r354736 -
/portals/jetspeed-2/trunk/etc/sql/mssql/schema/security-schema.sql
Author: rwatler
Date: Tue Dec 6 22:22:13 2005
New Revision: 354736
URL: http://svn.apache.org/viewcvs?rev=354736&view=rev
Log:
correct cyclic cascade deletes for MSSQL
Modified:
portals/jetspeed-2/trunk/etc/sql/mssql/schema/security-schema.sql
Modified: portals/jetspeed-2/trunk/etc/sql/mssql/schema/security-schema.sql
URL: http://svn.apache.org/viewcvs/portals/jetspeed-2/trunk/etc/sql/mssql/schema/security-schema.sql?rev=354736&r1=354735&r2=354736&view=diff
==============================================================================
--- portals/jetspeed-2/trunk/etc/sql/mssql/schema/security-schema.sql (original)
+++ portals/jetspeed-2/trunk/etc/sql/mssql/schema/security-schema.sql Tue Dec 6 22:22:13 2005
@@ -1,698 +1,827 @@
-
-/* ---------------------------------------------------------------------- */
-/* SECURITY_PRINCIPAL */
-/* ---------------------------------------------------------------------- */
-
-IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'SECURITY_PRINCIPAL')
-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 = 'SECURITY_PRINCIPAL'
- 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 SECURITY_PRINCIPAL
-END
-;
-
-CREATE TABLE SECURITY_PRINCIPAL
-(
- PRINCIPAL_ID INT NOT NULL,
- CLASSNAME VARCHAR (254) NOT NULL,
- IS_MAPPING_ONLY INT NOT NULL,
- IS_ENABLED INT NOT NULL,
- FULL_PATH VARCHAR (254) NOT NULL,
- CREATION_DATE DATETIME NOT NULL,
- MODIFIED_DATE DATETIME NOT NULL,
-
- CONSTRAINT SECURITY_PRINCIPAL_PK PRIMARY KEY(PRINCIPAL_ID),
- UNIQUE (FULL_PATH));
-
-
-
-
-
-/* ---------------------------------------------------------------------- */
-/* SECURITY_PERMISSION */
-/* ---------------------------------------------------------------------- */
-
-IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'SECURITY_PERMISSION')
-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 = 'SECURITY_PERMISSION'
- 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 SECURITY_PERMISSION
-END
-;
-
-CREATE TABLE SECURITY_PERMISSION
-(
- PERMISSION_ID INT NOT NULL,
- CLASSNAME VARCHAR (254) NOT NULL,
- NAME VARCHAR (254) NOT NULL,
- ACTIONS VARCHAR (254) NOT NULL,
- CREATION_DATE DATETIME NOT NULL,
- MODIFIED_DATE DATETIME NOT NULL,
-
- CONSTRAINT SECURITY_PERMISSION_PK PRIMARY KEY(PERMISSION_ID));
-
-
-
-
-
-/* ---------------------------------------------------------------------- */
-/* PRINCIPAL_PERMISSION */
-/* ---------------------------------------------------------------------- */
-
-IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='PRINCIPAL_PERMISSION_FK_1')
- ALTER TABLE PRINCIPAL_PERMISSION DROP CONSTRAINT PRINCIPAL_PERMISSION_FK_1;
-IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='PRINCIPAL_PERMISSION_FK_2')
- ALTER TABLE PRINCIPAL_PERMISSION DROP CONSTRAINT PRINCIPAL_PERMISSION_FK_2;
-IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'PRINCIPAL_PERMISSION')
-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 = 'PRINCIPAL_PERMISSION'
- 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 PRINCIPAL_PERMISSION
-END
-;
-
-CREATE TABLE PRINCIPAL_PERMISSION
-(
- PRINCIPAL_ID INT NOT NULL,
- PERMISSION_ID INT NOT NULL,
-
- CONSTRAINT PRINCIPAL_PERMISSION_PK PRIMARY KEY(PRINCIPAL_ID,PERMISSION_ID));
-
-
-
-
-
-/* ---------------------------------------------------------------------- */
-/* SECURITY_CREDENTIAL */
-/* ---------------------------------------------------------------------- */
-
-IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='SECURITY_CREDENTIAL_FK_1')
- ALTER TABLE SECURITY_CREDENTIAL DROP CONSTRAINT SECURITY_CREDENTIAL_FK_1;
-IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'SECURITY_CREDENTIAL')
-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 = 'SECURITY_CREDENTIAL'
- 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 SECURITY_CREDENTIAL
-END
-;
-
-CREATE TABLE SECURITY_CREDENTIAL
-(
- CREDENTIAL_ID INT NOT NULL,
- PRINCIPAL_ID INT NOT NULL,
- COLUMN_VALUE VARCHAR (254) NOT NULL,
- TYPE SMALLINT NOT NULL,
- CLASSNAME VARCHAR (254) NULL,
- UPDATE_REQUIRED INT NOT NULL,
- IS_ENCODED INT NOT NULL,
- IS_ENABLED INT NOT NULL,
- AUTH_FAILURES SMALLINT NOT NULL,
- IS_EXPIRED INT NOT NULL,
- CREATION_DATE DATETIME NOT NULL,
- MODIFIED_DATE DATETIME NOT NULL,
- PREV_AUTH_DATE DATETIME NULL,
- LAST_AUTH_DATE DATETIME NULL,
- EXPIRATION_DATE DATETIME NULL,
-
- CONSTRAINT SECURITY_CREDENTIAL_PK PRIMARY KEY(CREDENTIAL_ID));
-
-
-
-
-
-/* ---------------------------------------------------------------------- */
-/* SSO_SITE */
-/* ---------------------------------------------------------------------- */
-
-IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'SSO_SITE')
-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 = 'SSO_SITE'
- 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 SSO_SITE
-END
-;
-
-CREATE TABLE SSO_SITE
-(
- SITE_ID INT NOT NULL,
- NAME VARCHAR (254) NOT NULL,
- URL VARCHAR (254) NOT NULL,
- ALLOW_USER_SET INT default 0 NULL,
- REQUIRES_CERTIFICATE INT default 0 NULL,
-
- CONSTRAINT SSO_SITE_PK PRIMARY KEY(SITE_ID),
- UNIQUE (URL));
-
-
-
-
-
-/* ---------------------------------------------------------------------- */
-/* SSO_SITE_TO_PRINCIPALS */
-/* ---------------------------------------------------------------------- */
-
-IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='SSO_SITE_TO_PRINCIPALS_FK_1')
- ALTER TABLE SSO_SITE_TO_PRINCIPALS DROP CONSTRAINT SSO_SITE_TO_PRINCIPALS_FK_1;
-IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='SSO_SITE_TO_PRINCIPALS_FK_2')
- ALTER TABLE SSO_SITE_TO_PRINCIPALS DROP CONSTRAINT SSO_SITE_TO_PRINCIPALS_FK_2;
-IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'SSO_SITE_TO_PRINCIPALS')
-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 = 'SSO_SITE_TO_PRINCIPALS'
- 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 SSO_SITE_TO_PRINCIPALS
-END
-;
-
-CREATE TABLE SSO_SITE_TO_PRINCIPALS
-(
- SITE_ID INT NOT NULL,
- PRINCIPAL_ID INT NOT NULL,
-
- CONSTRAINT SSO_SITE_TO_PRINCIPALS_PK PRIMARY KEY(SITE_ID,PRINCIPAL_ID));
-
-
-
-
-
-/* ---------------------------------------------------------------------- */
-/* SSO_PRINCIPAL_TO_REMOTE */
-/* ---------------------------------------------------------------------- */
-
-IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='SSO_PRINCIPAL_TO_REMOTE_FK_1')
- ALTER TABLE SSO_PRINCIPAL_TO_REMOTE DROP CONSTRAINT SSO_PRINCIPAL_TO_REMOTE_FK_1;
-IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='SSO_PRINCIPAL_TO_REMOTE_FK_2')
- ALTER TABLE SSO_PRINCIPAL_TO_REMOTE DROP CONSTRAINT SSO_PRINCIPAL_TO_REMOTE_FK_2;
-IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'SSO_PRINCIPAL_TO_REMOTE')
-BEGIN
- DECLARE @reftable_7 nvarchar(60), @constraintname_7 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 = 'SSO_PRINCIPAL_TO_REMOTE'
- OPEN refcursor
- FETCH NEXT from refcursor into @reftable_7, @constraintname_7
- while @@FETCH_STATUS = 0
- BEGIN
- exec ('alter table '+@reftable_7+' drop constraint '+@constraintname_7)
- FETCH NEXT from refcursor into @reftable_7, @constraintname_7
- END
- CLOSE refcursor
- DEALLOCATE refcursor
- DROP TABLE SSO_PRINCIPAL_TO_REMOTE
-END
-;
-
-CREATE TABLE SSO_PRINCIPAL_TO_REMOTE
-(
- PRINCIPAL_ID INT NOT NULL,
- REMOTE_PRINCIPAL_ID INT NOT NULL,
-
- CONSTRAINT SSO_PRINCIPAL_TO_REMOTE_PK PRIMARY KEY(PRINCIPAL_ID,REMOTE_PRINCIPAL_ID));
-
-
-
-
-
-/* ---------------------------------------------------------------------- */
-/* SSO_SITE_TO_REMOTE */
-/* ---------------------------------------------------------------------- */
-
-IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='SSO_SITE_TO_REMOTE_FK_1')
- ALTER TABLE SSO_SITE_TO_REMOTE DROP CONSTRAINT SSO_SITE_TO_REMOTE_FK_1;
-IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='SSO_SITE_TO_REMOTE_FK_2')
- ALTER TABLE SSO_SITE_TO_REMOTE DROP CONSTRAINT SSO_SITE_TO_REMOTE_FK_2;
-IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'SSO_SITE_TO_REMOTE')
-BEGIN
- DECLARE @reftable_8 nvarchar(60), @constraintname_8 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 = 'SSO_SITE_TO_REMOTE'
- OPEN refcursor
- FETCH NEXT from refcursor into @reftable_8, @constraintname_8
- while @@FETCH_STATUS = 0
- BEGIN
- exec ('alter table '+@reftable_8+' drop constraint '+@constraintname_8)
- FETCH NEXT from refcursor into @reftable_8, @constraintname_8
- END
- CLOSE refcursor
- DEALLOCATE refcursor
- DROP TABLE SSO_SITE_TO_REMOTE
-END
-;
-
-CREATE TABLE SSO_SITE_TO_REMOTE
-(
- SITE_ID INT NOT NULL,
- PRINCIPAL_ID INT NOT NULL,
-
- CONSTRAINT SSO_SITE_TO_REMOTE_PK PRIMARY KEY(SITE_ID,PRINCIPAL_ID));
-
-
-
-
-
-/* ---------------------------------------------------------------------- */
-/* SECURITY_USER_ROLE */
-/* ---------------------------------------------------------------------- */
-
-IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='SECURITY_USER_ROLE_FK_1')
- ALTER TABLE SECURITY_USER_ROLE DROP CONSTRAINT SECURITY_USER_ROLE_FK_1;
-IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='SECURITY_USER_ROLE_FK_2')
- ALTER TABLE SECURITY_USER_ROLE DROP CONSTRAINT SECURITY_USER_ROLE_FK_2;
-IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'SECURITY_USER_ROLE')
-BEGIN
- DECLARE @reftable_9 nvarchar(60), @constraintname_9 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 = 'SECURITY_USER_ROLE'
- OPEN refcursor
- FETCH NEXT from refcursor into @reftable_9, @constraintname_9
- while @@FETCH_STATUS = 0
- BEGIN
- exec ('alter table '+@reftable_9+' drop constraint '+@constraintname_9)
- FETCH NEXT from refcursor into @reftable_9, @constraintname_9
- END
- CLOSE refcursor
- DEALLOCATE refcursor
- DROP TABLE SECURITY_USER_ROLE
-END
-;
-
-CREATE TABLE SECURITY_USER_ROLE
-(
- USER_ID INT NOT NULL,
- ROLE_ID INT NOT NULL,
-
- CONSTRAINT SECURITY_USER_ROLE_PK PRIMARY KEY(USER_ID,ROLE_ID));
-
-
-
-
-
-/* ---------------------------------------------------------------------- */
-/* SECURITY_USER_GROUP */
-/* ---------------------------------------------------------------------- */
-
-IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='SECURITY_USER_GROUP_FK_1')
- ALTER TABLE SECURITY_USER_GROUP DROP CONSTRAINT SECURITY_USER_GROUP_FK_1;
-IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='SECURITY_USER_GROUP_FK_2')
- ALTER TABLE SECURITY_USER_GROUP DROP CONSTRAINT SECURITY_USER_GROUP_FK_2;
-IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'SECURITY_USER_GROUP')
-BEGIN
- DECLARE @reftable_10 nvarchar(60), @constraintname_10 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 = 'SECURITY_USER_GROUP'
- OPEN refcursor
- FETCH NEXT from refcursor into @reftable_10, @constraintname_10
- while @@FETCH_STATUS = 0
- BEGIN
- exec ('alter table '+@reftable_10+' drop constraint '+@constraintname_10)
- FETCH NEXT from refcursor into @reftable_10, @constraintname_10
- END
- CLOSE refcursor
- DEALLOCATE refcursor
- DROP TABLE SECURITY_USER_GROUP
-END
-;
-
-CREATE TABLE SECURITY_USER_GROUP
-(
- USER_ID INT NOT NULL,
- GROUP_ID INT NOT NULL,
-
- CONSTRAINT SECURITY_USER_GROUP_PK PRIMARY KEY(USER_ID,GROUP_ID));
-
-
-
-
-
-/* ---------------------------------------------------------------------- */
-/* SECURITY_GROUP_ROLE */
-/* ---------------------------------------------------------------------- */
-
-IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='SECURITY_GROUP_ROLE_FK_1')
- ALTER TABLE SECURITY_GROUP_ROLE DROP CONSTRAINT SECURITY_GROUP_ROLE_FK_1;
-IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='SECURITY_GROUP_ROLE_FK_2')
- ALTER TABLE SECURITY_GROUP_ROLE DROP CONSTRAINT SECURITY_GROUP_ROLE_FK_2;
-IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'SECURITY_GROUP_ROLE')
-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 = 'SECURITY_GROUP_ROLE'
- 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 SECURITY_GROUP_ROLE
-END
-;
-
-CREATE TABLE SECURITY_GROUP_ROLE
-(
- GROUP_ID INT NOT NULL,
- ROLE_ID INT NOT NULL,
-
- CONSTRAINT SECURITY_GROUP_ROLE_PK PRIMARY KEY(GROUP_ID,ROLE_ID));
-
-
-
-
-
-/* ---------------------------------------------------------------------- */
-/* SECURITY_GROUP_ROLE */
-/* ---------------------------------------------------------------------- */
-
-
-
-
-/* ---------------------------------------------------------------------- */
-/* SECURITY_PRINCIPAL */
-/* ---------------------------------------------------------------------- */
-
-
-
-
-/* ---------------------------------------------------------------------- */
-/* SECURITY_PERMISSION */
-/* ---------------------------------------------------------------------- */
-
-BEGIN
-ALTER TABLE PRINCIPAL_PERMISSION
- ADD CONSTRAINT PRINCIPAL_PERMISSION_FK_1 FOREIGN KEY (PERMISSION_ID)
- REFERENCES SECURITY_PERMISSION (PERMISSION_ID)
- ON DELETE CASCADE
-END
-;
-
-BEGIN
-ALTER TABLE PRINCIPAL_PERMISSION
- ADD CONSTRAINT PRINCIPAL_PERMISSION_FK_2 FOREIGN KEY (PRINCIPAL_ID)
- REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID)
- ON DELETE CASCADE
-END
-;
-
-
-
-
-/* ---------------------------------------------------------------------- */
-/* PRINCIPAL_PERMISSION */
-/* ---------------------------------------------------------------------- */
-
-BEGIN
-ALTER TABLE SECURITY_CREDENTIAL
- ADD CONSTRAINT SECURITY_CREDENTIAL_FK_1 FOREIGN KEY (PRINCIPAL_ID)
- REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID)
- ON DELETE CASCADE
-END
-;
-
-
-
-
-/* ---------------------------------------------------------------------- */
-/* SECURITY_CREDENTIAL */
-/* ---------------------------------------------------------------------- */
-
-
-
-
-/* ---------------------------------------------------------------------- */
-/* SSO_SITE */
-/* ---------------------------------------------------------------------- */
-
-BEGIN
-ALTER TABLE SSO_SITE_TO_PRINCIPALS
- ADD CONSTRAINT SSO_SITE_TO_PRINCIPALS_FK_1 FOREIGN KEY (SITE_ID)
- REFERENCES SSO_SITE (SITE_ID)
- ON DELETE CASCADE
-END
-;
-
-BEGIN
-ALTER TABLE SSO_SITE_TO_PRINCIPALS
- ADD CONSTRAINT SSO_SITE_TO_PRINCIPALS_FK_2 FOREIGN KEY (PRINCIPAL_ID)
- REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID)
- ON DELETE CASCADE
-END
-;
-
-
-
-
-/* ---------------------------------------------------------------------- */
-/* SSO_SITE_TO_PRINCIPALS */
-/* ---------------------------------------------------------------------- */
-
-BEGIN
-ALTER TABLE SSO_PRINCIPAL_TO_REMOTE
- ADD CONSTRAINT SSO_PRINCIPAL_TO_REMOTE_FK_1 FOREIGN KEY (PRINCIPAL_ID)
- REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID)
- ON DELETE CASCADE
-END
-;
-
-BEGIN
-ALTER TABLE SSO_PRINCIPAL_TO_REMOTE
- ADD CONSTRAINT SSO_PRINCIPAL_TO_REMOTE_FK_2 FOREIGN KEY (REMOTE_PRINCIPAL_ID)
- REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID)
-END
-;
-
-
-
-
-/* ---------------------------------------------------------------------- */
-/* SSO_PRINCIPAL_TO_REMOTE */
-/* ---------------------------------------------------------------------- */
-
-BEGIN
-ALTER TABLE SSO_SITE_TO_REMOTE
- ADD CONSTRAINT SSO_SITE_TO_REMOTE_FK_1 FOREIGN KEY (SITE_ID)
- REFERENCES SSO_SITE (SITE_ID)
- ON DELETE CASCADE
-END
-;
-
-BEGIN
-ALTER TABLE SSO_SITE_TO_REMOTE
- ADD CONSTRAINT SSO_SITE_TO_REMOTE_FK_2 FOREIGN KEY (PRINCIPAL_ID)
- REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID)
- ON DELETE CASCADE
-END
-;
-
-
-
-
-/* ---------------------------------------------------------------------- */
-/* SSO_SITE_TO_REMOTE */
-/* ---------------------------------------------------------------------- */
-
-BEGIN
-ALTER TABLE SECURITY_USER_ROLE
- ADD CONSTRAINT SECURITY_USER_ROLE_FK_1 FOREIGN KEY (ROLE_ID)
- REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID)
- ON DELETE CASCADE
-END
-;
-
-BEGIN
-ALTER TABLE SECURITY_USER_ROLE
- ADD CONSTRAINT SECURITY_USER_ROLE_FK_2 FOREIGN KEY (USER_ID)
- REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID)
-END
-;
-
-
-
-
-/* ---------------------------------------------------------------------- */
-/* SECURITY_USER_ROLE */
-/* ---------------------------------------------------------------------- */
-
-BEGIN
-ALTER TABLE SECURITY_USER_GROUP
- ADD CONSTRAINT SECURITY_USER_GROUP_FK_1 FOREIGN KEY (GROUP_ID)
- REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID)
- ON DELETE CASCADE
-END
-;
-
-BEGIN
-ALTER TABLE SECURITY_USER_GROUP
- ADD CONSTRAINT SECURITY_USER_GROUP_FK_2 FOREIGN KEY (USER_ID)
- REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID)
-END
-;
-
-
-
-
-/* ---------------------------------------------------------------------- */
-/* SECURITY_USER_GROUP */
-/* ---------------------------------------------------------------------- */
-
-BEGIN
-ALTER TABLE SECURITY_GROUP_ROLE
- ADD CONSTRAINT SECURITY_GROUP_ROLE_FK_1 FOREIGN KEY (GROUP_ID)
- REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID)
- ON DELETE CASCADE
-END
-;
-
-BEGIN
-ALTER TABLE SECURITY_GROUP_ROLE
- ADD CONSTRAINT SECURITY_GROUP_ROLE_FK_2 FOREIGN KEY (ROLE_ID)
- REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID)
-END
-;
+
+/* ---------------------------------------------------------------------- */
+/* SECURITY_PRINCIPAL */
+/* ---------------------------------------------------------------------- */
+
+IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'SECURITY_PRINCIPAL')
+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 = 'SECURITY_PRINCIPAL'
+ 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 SECURITY_PRINCIPAL
+END
+;
+
+CREATE TABLE SECURITY_PRINCIPAL
+(
+ PRINCIPAL_ID INT NOT NULL,
+ CLASSNAME VARCHAR (254) NOT NULL,
+ IS_MAPPING_ONLY INT NOT NULL,
+ IS_ENABLED INT NOT NULL,
+ FULL_PATH VARCHAR (254) NOT NULL,
+ CREATION_DATE DATETIME NOT NULL,
+ MODIFIED_DATE DATETIME NOT NULL,
+
+ CONSTRAINT SECURITY_PRINCIPAL_PK PRIMARY KEY(PRINCIPAL_ID),
+ UNIQUE (FULL_PATH));
+
+
+
+
+
+/* ---------------------------------------------------------------------- */
+/* SECURITY_PERMISSION */
+/* ---------------------------------------------------------------------- */
+
+IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'SECURITY_PERMISSION')
+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 = 'SECURITY_PERMISSION'
+ 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 SECURITY_PERMISSION
+END
+;
+
+CREATE TABLE SECURITY_PERMISSION
+(
+ PERMISSION_ID INT NOT NULL,
+ CLASSNAME VARCHAR (254) NOT NULL,
+ NAME VARCHAR (254) NOT NULL,
+ ACTIONS VARCHAR (254) NOT NULL,
+ CREATION_DATE DATETIME NOT NULL,
+ MODIFIED_DATE DATETIME NOT NULL,
+
+ CONSTRAINT SECURITY_PERMISSION_PK PRIMARY KEY(PERMISSION_ID));
+
+
+
+
+
+/* ---------------------------------------------------------------------- */
+/* PRINCIPAL_PERMISSION */
+/* ---------------------------------------------------------------------- */
+
+IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='PRINCIPAL_PERMISSION_FK_1')
+ ALTER TABLE PRINCIPAL_PERMISSION DROP CONSTRAINT PRINCIPAL_PERMISSION_FK_1;
+IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='PRINCIPAL_PERMISSION_FK_2')
+ ALTER TABLE PRINCIPAL_PERMISSION DROP CONSTRAINT PRINCIPAL_PERMISSION_FK_2;
+IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'PRINCIPAL_PERMISSION')
+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 = 'PRINCIPAL_PERMISSION'
+ 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 PRINCIPAL_PERMISSION
+END
+;
+
+CREATE TABLE PRINCIPAL_PERMISSION
+(
+ PRINCIPAL_ID INT NOT NULL,
+ PERMISSION_ID INT NOT NULL,
+
+ CONSTRAINT PRINCIPAL_PERMISSION_PK PRIMARY KEY(PRINCIPAL_ID,PERMISSION_ID));
+
+
+
+
+
+/* ---------------------------------------------------------------------- */
+/* SECURITY_CREDENTIAL */
+/* ---------------------------------------------------------------------- */
+
+IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='SECURITY_CREDENTIAL_FK_1')
+ ALTER TABLE SECURITY_CREDENTIAL DROP CONSTRAINT SECURITY_CREDENTIAL_FK_1;
+IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'SECURITY_CREDENTIAL')
+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 = 'SECURITY_CREDENTIAL'
+ 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 SECURITY_CREDENTIAL
+END
+;
+
+CREATE TABLE SECURITY_CREDENTIAL
+(
+ CREDENTIAL_ID INT NOT NULL,
+ PRINCIPAL_ID INT NOT NULL,
+ COLUMN_VALUE VARCHAR (254) NOT NULL,
+ TYPE SMALLINT NOT NULL,
+ CLASSNAME VARCHAR (254) NULL,
+ UPDATE_REQUIRED INT NOT NULL,
+ IS_ENCODED INT NOT NULL,
+ IS_ENABLED INT NOT NULL,
+ AUTH_FAILURES SMALLINT NOT NULL,
+ IS_EXPIRED INT NOT NULL,
+ CREATION_DATE DATETIME NOT NULL,
+ MODIFIED_DATE DATETIME NOT NULL,
+ PREV_AUTH_DATE DATETIME NULL,
+ LAST_AUTH_DATE DATETIME NULL,
+ EXPIRATION_DATE DATETIME NULL,
+
+ CONSTRAINT SECURITY_CREDENTIAL_PK PRIMARY KEY(CREDENTIAL_ID));
+
+
+
+
+
+/* ---------------------------------------------------------------------- */
+/* SSO_SITE */
+/* ---------------------------------------------------------------------- */
+
+IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'SSO_SITE')
+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 = 'SSO_SITE'
+ 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 SSO_SITE
+END
+;
+
+CREATE TABLE SSO_SITE
+(
+ SITE_ID INT NOT NULL,
+ NAME VARCHAR (254) NOT NULL,
+ URL VARCHAR (254) NOT NULL,
+ ALLOW_USER_SET INT default 0 NULL,
+ REQUIRES_CERTIFICATE INT default 0 NULL,
+ CHALLENGE_RESPONSE_AUTH INT default 0 NULL,
+ FORM_AUTH INT default 0 NULL,
+ FORM_USER_FIELD VARCHAR (128) NULL,
+ FORM_PWD_FIELD VARCHAR (128) NULL,
+ REALM VARCHAR (128) NULL,
+
+ CONSTRAINT SSO_SITE_PK PRIMARY KEY(SITE_ID),
+ UNIQUE (URL));
+
+
+
+
+
+/* ---------------------------------------------------------------------- */
+/* SSO_COOKIE */
+/* ---------------------------------------------------------------------- */
+
+IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'SSO_COOKIE')
+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 = 'SSO_COOKIE'
+ 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 SSO_COOKIE
+END
+;
+
+CREATE TABLE SSO_COOKIE
+(
+ COOKIE_ID INT NOT NULL,
+ COOKIE VARCHAR (1024) NOT NULL,
+ CREATE_DATE DATETIME NOT NULL,
+
+ CONSTRAINT SSO_COOKIE_PK PRIMARY KEY(COOKIE_ID));
+
+
+
+
+
+/* ---------------------------------------------------------------------- */
+/* SSO_SITE_TO_PRINCIPALS */
+/* ---------------------------------------------------------------------- */
+
+IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='SSO_SITE_TO_PRINCIPALS_FK_1')
+ ALTER TABLE SSO_SITE_TO_PRINCIPALS DROP CONSTRAINT SSO_SITE_TO_PRINCIPALS_FK_1;
+IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='SSO_SITE_TO_PRINCIPALS_FK_2')
+ ALTER TABLE SSO_SITE_TO_PRINCIPALS DROP CONSTRAINT SSO_SITE_TO_PRINCIPALS_FK_2;
+IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'SSO_SITE_TO_PRINCIPALS')
+BEGIN
+ DECLARE @reftable_7 nvarchar(60), @constraintname_7 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 = 'SSO_SITE_TO_PRINCIPALS'
+ OPEN refcursor
+ FETCH NEXT from refcursor into @reftable_7, @constraintname_7
+ while @@FETCH_STATUS = 0
+ BEGIN
+ exec ('alter table '+@reftable_7+' drop constraint '+@constraintname_7)
+ FETCH NEXT from refcursor into @reftable_7, @constraintname_7
+ END
+ CLOSE refcursor
+ DEALLOCATE refcursor
+ DROP TABLE SSO_SITE_TO_PRINCIPALS
+END
+;
+
+CREATE TABLE SSO_SITE_TO_PRINCIPALS
+(
+ SITE_ID INT NOT NULL,
+ PRINCIPAL_ID INT NOT NULL,
+
+ CONSTRAINT SSO_SITE_TO_PRINCIPALS_PK PRIMARY KEY(SITE_ID,PRINCIPAL_ID));
+
+
+
+
+
+/* ---------------------------------------------------------------------- */
+/* SSO_PRINCIPAL_TO_REMOTE */
+/* ---------------------------------------------------------------------- */
+
+IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='SSO_PRINCIPAL_TO_REMOTE_FK_1')
+ ALTER TABLE SSO_PRINCIPAL_TO_REMOTE DROP CONSTRAINT SSO_PRINCIPAL_TO_REMOTE_FK_1;
+IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='SSO_PRINCIPAL_TO_REMOTE_FK_2')
+ ALTER TABLE SSO_PRINCIPAL_TO_REMOTE DROP CONSTRAINT SSO_PRINCIPAL_TO_REMOTE_FK_2;
+IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'SSO_PRINCIPAL_TO_REMOTE')
+BEGIN
+ DECLARE @reftable_8 nvarchar(60), @constraintname_8 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 = 'SSO_PRINCIPAL_TO_REMOTE'
+ OPEN refcursor
+ FETCH NEXT from refcursor into @reftable_8, @constraintname_8
+ while @@FETCH_STATUS = 0
+ BEGIN
+ exec ('alter table '+@reftable_8+' drop constraint '+@constraintname_8)
+ FETCH NEXT from refcursor into @reftable_8, @constraintname_8
+ END
+ CLOSE refcursor
+ DEALLOCATE refcursor
+ DROP TABLE SSO_PRINCIPAL_TO_REMOTE
+END
+;
+
+CREATE TABLE SSO_PRINCIPAL_TO_REMOTE
+(
+ PRINCIPAL_ID INT NOT NULL,
+ REMOTE_PRINCIPAL_ID INT NOT NULL,
+
+ CONSTRAINT SSO_PRINCIPAL_TO_REMOTE_PK PRIMARY KEY(PRINCIPAL_ID,REMOTE_PRINCIPAL_ID));
+
+
+
+
+
+/* ---------------------------------------------------------------------- */
+/* SSO_SITE_TO_REMOTE */
+/* ---------------------------------------------------------------------- */
+
+IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='SSO_SITE_TO_REMOTE_FK_1')
+ ALTER TABLE SSO_SITE_TO_REMOTE DROP CONSTRAINT SSO_SITE_TO_REMOTE_FK_1;
+IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='SSO_SITE_TO_REMOTE_FK_2')
+ ALTER TABLE SSO_SITE_TO_REMOTE DROP CONSTRAINT SSO_SITE_TO_REMOTE_FK_2;
+IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'SSO_SITE_TO_REMOTE')
+BEGIN
+ DECLARE @reftable_9 nvarchar(60), @constraintname_9 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 = 'SSO_SITE_TO_REMOTE'
+ OPEN refcursor
+ FETCH NEXT from refcursor into @reftable_9, @constraintname_9
+ while @@FETCH_STATUS = 0
+ BEGIN
+ exec ('alter table '+@reftable_9+' drop constraint '+@constraintname_9)
+ FETCH NEXT from refcursor into @reftable_9, @constraintname_9
+ END
+ CLOSE refcursor
+ DEALLOCATE refcursor
+ DROP TABLE SSO_SITE_TO_REMOTE
+END
+;
+
+CREATE TABLE SSO_SITE_TO_REMOTE
+(
+ SITE_ID INT NOT NULL,
+ PRINCIPAL_ID INT NOT NULL,
+
+ CONSTRAINT SSO_SITE_TO_REMOTE_PK PRIMARY KEY(SITE_ID,PRINCIPAL_ID));
+
+
+
+
+
+/* ---------------------------------------------------------------------- */
+/* SSO_COOKIE_TO_REMOTE */
+/* ---------------------------------------------------------------------- */
+
+IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='SSO_COOKIE_TO_REMOTE_FK_1')
+ ALTER TABLE SSO_COOKIE_TO_REMOTE DROP CONSTRAINT SSO_COOKIE_TO_REMOTE_FK_1;
+IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='SSO_COOKIE_TO_REMOTE_FK_2')
+ ALTER TABLE SSO_COOKIE_TO_REMOTE DROP CONSTRAINT SSO_COOKIE_TO_REMOTE_FK_2;
+IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'SSO_COOKIE_TO_REMOTE')
+BEGIN
+ DECLARE @reftable_10 nvarchar(60), @constraintname_10 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 = 'SSO_COOKIE_TO_REMOTE'
+ OPEN refcursor
+ FETCH NEXT from refcursor into @reftable_10, @constraintname_10
+ while @@FETCH_STATUS = 0
+ BEGIN
+ exec ('alter table '+@reftable_10+' drop constraint '+@constraintname_10)
+ FETCH NEXT from refcursor into @reftable_10, @constraintname_10
+ END
+ CLOSE refcursor
+ DEALLOCATE refcursor
+ DROP TABLE SSO_COOKIE_TO_REMOTE
+END
+;
+
+CREATE TABLE SSO_COOKIE_TO_REMOTE
+(
+ COOKIE_ID INT NOT NULL,
+ REMOTE_PRINCIPAL_ID INT NOT NULL,
+
+ CONSTRAINT SSO_COOKIE_TO_REMOTE_PK PRIMARY KEY(COOKIE_ID,REMOTE_PRINCIPAL_ID));
+
+
+
+
+
+/* ---------------------------------------------------------------------- */
+/* SECURITY_USER_ROLE */
+/* ---------------------------------------------------------------------- */
+
+IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='SECURITY_USER_ROLE_FK_1')
+ ALTER TABLE SECURITY_USER_ROLE DROP CONSTRAINT SECURITY_USER_ROLE_FK_1;
+IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='SECURITY_USER_ROLE_FK_2')
+ ALTER TABLE SECURITY_USER_ROLE DROP CONSTRAINT SECURITY_USER_ROLE_FK_2;
+IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'SECURITY_USER_ROLE')
+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 = 'SECURITY_USER_ROLE'
+ 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 SECURITY_USER_ROLE
+END
+;
+
+CREATE TABLE SECURITY_USER_ROLE
+(
+ USER_ID INT NOT NULL,
+ ROLE_ID INT NOT NULL,
+
+ CONSTRAINT SECURITY_USER_ROLE_PK PRIMARY KEY(USER_ID,ROLE_ID));
+
+
+
+
+
+/* ---------------------------------------------------------------------- */
+/* SECURITY_USER_GROUP */
+/* ---------------------------------------------------------------------- */
+
+IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='SECURITY_USER_GROUP_FK_1')
+ ALTER TABLE SECURITY_USER_GROUP DROP CONSTRAINT SECURITY_USER_GROUP_FK_1;
+IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='SECURITY_USER_GROUP_FK_2')
+ ALTER TABLE SECURITY_USER_GROUP DROP CONSTRAINT SECURITY_USER_GROUP_FK_2;
+IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'SECURITY_USER_GROUP')
+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 = 'SECURITY_USER_GROUP'
+ 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_12
+ END
+ CLOSE refcursor
+ DEALLOCATE refcursor
+ DROP TABLE SECURITY_USER_GROUP
+END
+;
+
+CREATE TABLE SECURITY_USER_GROUP
+(
+ USER_ID INT NOT NULL,
+ GROUP_ID INT NOT NULL,
+
+ CONSTRAINT SECURITY_USER_GROUP_PK PRIMARY KEY(USER_ID,GROUP_ID));
+
+
+
+
+
+/* ---------------------------------------------------------------------- */
+/* SECURITY_GROUP_ROLE */
+/* ---------------------------------------------------------------------- */
+
+IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='SECURITY_GROUP_ROLE_FK_1')
+ ALTER TABLE SECURITY_GROUP_ROLE DROP CONSTRAINT SECURITY_GROUP_ROLE_FK_1;
+IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='SECURITY_GROUP_ROLE_FK_2')
+ ALTER TABLE SECURITY_GROUP_ROLE DROP CONSTRAINT SECURITY_GROUP_ROLE_FK_2;
+IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'SECURITY_GROUP_ROLE')
+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 = 'SECURITY_GROUP_ROLE'
+ 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 SECURITY_GROUP_ROLE
+END
+;
+
+CREATE TABLE SECURITY_GROUP_ROLE
+(
+ GROUP_ID INT NOT NULL,
+ ROLE_ID INT NOT NULL,
+
+ CONSTRAINT SECURITY_GROUP_ROLE_PK PRIMARY KEY(GROUP_ID,ROLE_ID));
+
+
+
+
+
+/* ---------------------------------------------------------------------- */
+/* SECURITY_GROUP_ROLE */
+/* ---------------------------------------------------------------------- */
+
+
+
+
+/* ---------------------------------------------------------------------- */
+/* SECURITY_PRINCIPAL */
+/* ---------------------------------------------------------------------- */
+
+
+
+
+/* ---------------------------------------------------------------------- */
+/* SECURITY_PERMISSION */
+/* ---------------------------------------------------------------------- */
+
+BEGIN
+ALTER TABLE PRINCIPAL_PERMISSION
+ ADD CONSTRAINT PRINCIPAL_PERMISSION_FK_1 FOREIGN KEY (PERMISSION_ID)
+ REFERENCES SECURITY_PERMISSION (PERMISSION_ID)
+ ON DELETE CASCADE
+END
+;
+
+BEGIN
+ALTER TABLE PRINCIPAL_PERMISSION
+ ADD CONSTRAINT PRINCIPAL_PERMISSION_FK_2 FOREIGN KEY (PRINCIPAL_ID)
+ REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID)
+ ON DELETE CASCADE
+END
+;
+
+
+
+
+/* ---------------------------------------------------------------------- */
+/* PRINCIPAL_PERMISSION */
+/* ---------------------------------------------------------------------- */
+
+BEGIN
+ALTER TABLE SECURITY_CREDENTIAL
+ ADD CONSTRAINT SECURITY_CREDENTIAL_FK_1 FOREIGN KEY (PRINCIPAL_ID)
+ REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID)
+ ON DELETE CASCADE
+END
+;
+
+
+
+
+/* ---------------------------------------------------------------------- */
+/* SECURITY_CREDENTIAL */
+/* ---------------------------------------------------------------------- */
+
+
+
+
+/* ---------------------------------------------------------------------- */
+/* SSO_SITE */
+/* ---------------------------------------------------------------------- */
+
+
+
+
+/* ---------------------------------------------------------------------- */
+/* SSO_COOKIE */
+/* ---------------------------------------------------------------------- */
+
+BEGIN
+ALTER TABLE SSO_SITE_TO_PRINCIPALS
+ ADD CONSTRAINT SSO_SITE_TO_PRINCIPALS_FK_1 FOREIGN KEY (SITE_ID)
+ REFERENCES SSO_SITE (SITE_ID)
+ ON DELETE CASCADE
+END
+;
+
+BEGIN
+ALTER TABLE SSO_SITE_TO_PRINCIPALS
+ ADD CONSTRAINT SSO_SITE_TO_PRINCIPALS_FK_2 FOREIGN KEY (PRINCIPAL_ID)
+ REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID)
+ ON DELETE CASCADE
+END
+;
+
+
+
+
+/* ---------------------------------------------------------------------- */
+/* SSO_SITE_TO_PRINCIPALS */
+/* ---------------------------------------------------------------------- */
+
+BEGIN
+ALTER TABLE SSO_PRINCIPAL_TO_REMOTE
+ ADD CONSTRAINT SSO_PRINCIPAL_TO_REMOTE_FK_1 FOREIGN KEY (PRINCIPAL_ID)
+ REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID)
+ ON DELETE CASCADE
+END
+;
+
+BEGIN
+ALTER TABLE SSO_PRINCIPAL_TO_REMOTE
+ ADD CONSTRAINT SSO_PRINCIPAL_TO_REMOTE_FK_2 FOREIGN KEY (REMOTE_PRINCIPAL_ID)
+ REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID)
+ /* ON DELETE CASCADE - cannot be used: Introducing FOREIGN KEY constraint 'X_FK_N' on table 'X' may cause cycles or multiple cascade paths. */
+END
+;
+
+
+
+
+/* ---------------------------------------------------------------------- */
+/* SSO_PRINCIPAL_TO_REMOTE */
+/* ---------------------------------------------------------------------- */
+
+BEGIN
+ALTER TABLE SSO_SITE_TO_REMOTE
+ ADD CONSTRAINT SSO_SITE_TO_REMOTE_FK_1 FOREIGN KEY (SITE_ID)
+ REFERENCES SSO_SITE (SITE_ID)
+ ON DELETE CASCADE
+END
+;
+
+BEGIN
+ALTER TABLE SSO_SITE_TO_REMOTE
+ ADD CONSTRAINT SSO_SITE_TO_REMOTE_FK_2 FOREIGN KEY (PRINCIPAL_ID)
+ REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID)
+ ON DELETE CASCADE
+END
+;
+
+
+
+
+/* ---------------------------------------------------------------------- */
+/* SSO_SITE_TO_REMOTE */
+/* ---------------------------------------------------------------------- */
+
+BEGIN
+ALTER TABLE SSO_COOKIE_TO_REMOTE
+ ADD CONSTRAINT SSO_COOKIE_TO_REMOTE_FK_1 FOREIGN KEY (COOKIE_ID)
+ REFERENCES SSO_COOKIE (COOKIE_ID)
+ ON DELETE CASCADE
+END
+;
+
+BEGIN
+ALTER TABLE SSO_COOKIE_TO_REMOTE
+ ADD CONSTRAINT SSO_COOKIE_TO_REMOTE_FK_2 FOREIGN KEY (REMOTE_PRINCIPAL_ID)
+ REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID)
+ ON DELETE CASCADE
+END
+;
+
+
+
+
+/* ---------------------------------------------------------------------- */
+/* SSO_COOKIE_TO_REMOTE */
+/* ---------------------------------------------------------------------- */
+
+BEGIN
+ALTER TABLE SECURITY_USER_ROLE
+ ADD CONSTRAINT SECURITY_USER_ROLE_FK_1 FOREIGN KEY (ROLE_ID)
+ REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID)
+ ON DELETE CASCADE
+END
+;
+
+BEGIN
+ALTER TABLE SECURITY_USER_ROLE
+ ADD CONSTRAINT SECURITY_USER_ROLE_FK_2 FOREIGN KEY (USER_ID)
+ REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID)
+ /* ON DELETE CASCADE - cannot be used: Introducing FOREIGN KEY constraint 'X_FK_N' on table 'X' may cause cycles or multiple cascade paths. */
+END
+;
+
+
+
+
+/* ---------------------------------------------------------------------- */
+/* SECURITY_USER_ROLE */
+/* ---------------------------------------------------------------------- */
+
+BEGIN
+ALTER TABLE SECURITY_USER_GROUP
+ ADD CONSTRAINT SECURITY_USER_GROUP_FK_1 FOREIGN KEY (GROUP_ID)
+ REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID)
+ ON DELETE CASCADE
+END
+;
+
+BEGIN
+ALTER TABLE SECURITY_USER_GROUP
+ ADD CONSTRAINT SECURITY_USER_GROUP_FK_2 FOREIGN KEY (USER_ID)
+ REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID)
+ /* ON DELETE CASCADE - cannot be used: Introducing FOREIGN KEY constraint 'X_FK_N' on table 'X' may cause cycles or multiple cascade paths. */
+END
+;
+
+
+
+
+/* ---------------------------------------------------------------------- */
+/* SECURITY_USER_GROUP */
+/* ---------------------------------------------------------------------- */
+
+BEGIN
+ALTER TABLE SECURITY_GROUP_ROLE
+ ADD CONSTRAINT SECURITY_GROUP_ROLE_FK_1 FOREIGN KEY (GROUP_ID)
+ REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID)
+ ON DELETE CASCADE
+END
+;
+
+BEGIN
+ALTER TABLE SECURITY_GROUP_ROLE
+ ADD CONSTRAINT SECURITY_GROUP_ROLE_FK_2 FOREIGN KEY (ROLE_ID)
+ REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID)
+ /* ON DELETE CASCADE - cannot be used: Introducing FOREIGN KEY constraint 'X_FK_N' on table 'X' may cause cycles or multiple cascade paths. */
+END
+;
+
+
+
---------------------------------------------------------------------
To unsubscribe, e-mail: jetspeed-dev-unsubscribe@portals.apache.org
For additional commands, e-mail: jetspeed-dev-help@portals.apache.org