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 ta...@apache.org on 2008/07/04 04:19:51 UTC
svn commit: r673895 [3/3] - in
/portals/jetspeed-2/portal/trunk/etc/sql/mssql: ./ schema/
Added: portals/jetspeed-2/portal/trunk/etc/sql/mssql/schema/prefs-schema.sql
URL: http://svn.apache.org/viewvc/portals/jetspeed-2/portal/trunk/etc/sql/mssql/schema/prefs-schema.sql?rev=673895&view=auto
==============================================================================
--- portals/jetspeed-2/portal/trunk/etc/sql/mssql/schema/prefs-schema.sql (added)
+++ portals/jetspeed-2/portal/trunk/etc/sql/mssql/schema/prefs-schema.sql Thu Jul 3 19:19:50 2008
@@ -0,0 +1,135 @@
+
+/* ---------------------------------------------------------------------- */
+/* PREFS_NODE */
+/* ---------------------------------------------------------------------- */
+
+IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='FK_PREFS_NODE_1')
+ ALTER TABLE PREFS_NODE DROP CONSTRAINT FK_PREFS_NODE_1;
+IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'PREFS_NODE')
+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 = 'PREFS_NODE'
+ 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 PREFS_NODE
+END
+;
+
+CREATE TABLE PREFS_NODE
+(
+ NODE_ID INT NOT NULL,
+ PARENT_NODE_ID INT NULL,
+ NODE_NAME VARCHAR (100) NULL,
+ NODE_TYPE SMALLINT NULL,
+ FULL_PATH VARCHAR (254) NULL,
+ CREATION_DATE DATETIME NULL,
+ MODIFIED_DATE DATETIME NULL,
+
+ CONSTRAINT PREFS_NODE_PK PRIMARY KEY(NODE_ID));
+
+CREATE INDEX IX_PREFS_NODE_1 ON PREFS_NODE (PARENT_NODE_ID);
+CREATE INDEX IX_PREFS_NODE_2 ON PREFS_NODE (FULL_PATH);
+
+
+
+
+/* ---------------------------------------------------------------------- */
+/* PREFS_PROPERTY_VALUE */
+/* ---------------------------------------------------------------------- */
+
+IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='PREFS_PROPERTY_VALUE_FK_1')
+ ALTER TABLE PREFS_PROPERTY_VALUE DROP CONSTRAINT PREFS_PROPERTY_VALUE_FK_1;
+IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'PREFS_PROPERTY_VALUE')
+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 = 'PREFS_PROPERTY_VALUE'
+ 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 PREFS_PROPERTY_VALUE
+END
+;
+
+CREATE TABLE PREFS_PROPERTY_VALUE
+(
+ PROPERTY_VALUE_ID INT NOT NULL,
+ NODE_ID INT NULL,
+ PROPERTY_NAME VARCHAR (100) NULL,
+ PROPERTY_VALUE VARCHAR (254) NULL,
+ CREATION_DATE DATETIME NULL,
+ MODIFIED_DATE DATETIME NULL,
+
+ CONSTRAINT PREFS_PROPERTY_VALUE_PK PRIMARY KEY(PROPERTY_VALUE_ID));
+
+CREATE INDEX IX_FKPPV_1 ON PREFS_PROPERTY_VALUE (NODE_ID);
+
+
+
+
+/* ---------------------------------------------------------------------- */
+/* DROP TRIGGERS */
+/* ---------------------------------------------------------------------- */
+
+
+
+/* ---------------------------------------------------------------------- */
+/* PREFS_PROPERTY_VALUE */
+/* ---------------------------------------------------------------------- */
+
+
+
+
+BEGIN
+ALTER TABLE PREFS_NODE
+ ADD CONSTRAINT FK_PREFS_NODE_1 FOREIGN KEY (PARENT_NODE_ID)
+ REFERENCES PREFS_NODE (NODE_ID)
+ ON DELETE NO ACTION
+END
+;
+
+
+/* ---------------------------------------------------------------------- */
+/* PREFS_NODE */
+/* ---------------------------------------------------------------------- */
+
+BEGIN
+ALTER TABLE PREFS_PROPERTY_VALUE
+ ADD CONSTRAINT PREFS_PROPERTY_VALUE_FK_1 FOREIGN KEY (NODE_ID)
+ REFERENCES PREFS_NODE (NODE_ID)
+ ON DELETE NO ACTION
+END
+;
+
+
+
Added: portals/jetspeed-2/portal/trunk/etc/sql/mssql/schema/prefs-trig-schema.sql
URL: http://svn.apache.org/viewvc/portals/jetspeed-2/portal/trunk/etc/sql/mssql/schema/prefs-trig-schema.sql?rev=673895&view=auto
==============================================================================
--- portals/jetspeed-2/portal/trunk/etc/sql/mssql/schema/prefs-trig-schema.sql (added)
+++ portals/jetspeed-2/portal/trunk/etc/sql/mssql/schema/prefs-trig-schema.sql Thu Jul 3 19:19:50 2008
@@ -0,0 +1,35 @@
+
+IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='TR' AND name='trig_PREFS_NODE')
+ DROP TRIGGER trig_PREFS_NODE
+/
+
+CREATE TRIGGER trig_PREFS_NODE
+ ON PREFS_NODE
+ INSTEAD OF DELETE
+ AS
+
+ SET NOCOUNT ON;
+
+ WITH cte AS
+ ( SELECT NODE_ID, PARENT_NODE_ID
+ FROM DELETED
+ UNION ALL
+ SELECT c.NODE_ID, c.PARENT_NODE_ID
+ FROM PREFS_NODE AS c
+ INNER JOIN cte AS p
+ ON c.PARENT_NODE_ID = p.NODE_ID
+ )
+ SELECT *
+ into #tmp
+ FROM cte
+ OPTION (MAXRECURSION 32767)
+
+ DELETE FROM PREFS_PROPERTY_VALUE
+ WHERE NODE_ID IN (SELECT NODE_ID FROM #tmp);
+
+ DELETE FROM PREFS_NODE
+ WHERE NODE_ID IN(
+ SELECT NODE_ID FROM #TMP)
+
+ drop table #tmp
+/
\ No newline at end of file
Added: portals/jetspeed-2/portal/trunk/etc/sql/mssql/schema/registry-schema.sql
URL: http://svn.apache.org/viewvc/portals/jetspeed-2/portal/trunk/etc/sql/mssql/schema/registry-schema.sql?rev=673895&view=auto
==============================================================================
--- portals/jetspeed-2/portal/trunk/etc/sql/mssql/schema/registry-schema.sql (added)
+++ portals/jetspeed-2/portal/trunk/etc/sql/mssql/schema/registry-schema.sql Thu Jul 3 19:19:50 2008
@@ -0,0 +1,978 @@
+
+/* ---------------------------------------------------------------------- */
+/* PORTLET_DEFINITION */
+/* ---------------------------------------------------------------------- */
+
+IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'PORTLET_DEFINITION')
+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 = 'PORTLET_DEFINITION'
+ 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 PORTLET_DEFINITION
+END
+;
+
+CREATE TABLE PORTLET_DEFINITION
+(
+ ID INT NOT NULL,
+ NAME VARCHAR (80) NULL,
+ CLASS_NAME VARCHAR (255) NULL,
+ APPLICATION_ID INT NOT NULL,
+ PORTLET_IDENTIFIER VARCHAR (80) NULL,
+ EXPIRATION_CACHE VARCHAR (30) NULL,
+ RESOURCE_BUNDLE VARCHAR (255) NULL,
+ PREFERENCE_VALIDATOR VARCHAR (255) NULL,
+ SECURITY_REF VARCHAR (40) NULL,
+
+ CONSTRAINT PORTLET_DEFINITION_PK PRIMARY KEY(ID));
+
+
+
+
+
+/* ---------------------------------------------------------------------- */
+/* PORTLET_APPLICATION */
+/* ---------------------------------------------------------------------- */
+
+IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'PORTLET_APPLICATION')
+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 = 'PORTLET_APPLICATION'
+ 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 PORTLET_APPLICATION
+END
+;
+
+CREATE TABLE PORTLET_APPLICATION
+(
+ APPLICATION_ID INT NOT NULL,
+ APP_NAME VARCHAR (80) NOT NULL,
+ APP_IDENTIFIER VARCHAR (80) NULL,
+ VERSION VARCHAR (80) NULL,
+ APP_TYPE INT NULL,
+ CHECKSUM VARCHAR (80) NULL,
+ DESCRIPTION VARCHAR (80) NULL,
+ WEB_APP_ID INT NOT NULL,
+ SECURITY_REF VARCHAR (40) NULL,
+
+ CONSTRAINT PORTLET_APPLICATION_PK PRIMARY KEY(APPLICATION_ID),
+ UNIQUE (APP_NAME));
+
+
+
+
+
+/* ---------------------------------------------------------------------- */
+/* WEB_APPLICATION */
+/* ---------------------------------------------------------------------- */
+
+IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'WEB_APPLICATION')
+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 = 'WEB_APPLICATION'
+ 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 WEB_APPLICATION
+END
+;
+
+CREATE TABLE WEB_APPLICATION
+(
+ ID INT NOT NULL,
+ CONTEXT_ROOT VARCHAR (255) NOT NULL,
+
+ CONSTRAINT WEB_APPLICATION_PK PRIMARY KEY(ID));
+
+
+
+
+
+/* ---------------------------------------------------------------------- */
+/* PA_METADATA_FIELDS */
+/* ---------------------------------------------------------------------- */
+
+IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='FK_PA_METADATA_FIELDS_1')
+ ALTER TABLE PA_METADATA_FIELDS DROP CONSTRAINT FK_PA_METADATA_FIELDS_1;
+IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'PA_METADATA_FIELDS')
+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 = 'PA_METADATA_FIELDS'
+ 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 PA_METADATA_FIELDS
+END
+;
+
+CREATE TABLE PA_METADATA_FIELDS
+(
+ ID INT NOT NULL,
+ OBJECT_ID INT NOT NULL,
+ COLUMN_VALUE TEXT NOT NULL,
+ NAME VARCHAR (100) NOT NULL,
+ LOCALE_STRING VARCHAR (50) NOT NULL,
+
+ CONSTRAINT PA_METADATA_FIELDS_PK PRIMARY KEY(ID));
+
+
+
+
+
+/* ---------------------------------------------------------------------- */
+/* PD_METADATA_FIELDS */
+/* ---------------------------------------------------------------------- */
+
+IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='FK_PD_METADATA_FIELDS_1')
+ ALTER TABLE PD_METADATA_FIELDS DROP CONSTRAINT FK_PD_METADATA_FIELDS_1;
+IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'PD_METADATA_FIELDS')
+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 = 'PD_METADATA_FIELDS'
+ 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 PD_METADATA_FIELDS
+END
+;
+
+CREATE TABLE PD_METADATA_FIELDS
+(
+ ID INT NOT NULL,
+ OBJECT_ID INT NOT NULL,
+ COLUMN_VALUE TEXT NOT NULL,
+ NAME VARCHAR (100) NOT NULL,
+ LOCALE_STRING VARCHAR (50) NOT NULL,
+
+ CONSTRAINT PD_METADATA_FIELDS_PK PRIMARY KEY(ID));
+
+
+
+
+
+/* ---------------------------------------------------------------------- */
+/* LANGUAGE */
+/* ---------------------------------------------------------------------- */
+
+IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'LANGUAGE')
+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 = 'LANGUAGE'
+ 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 LANGUAGE
+END
+;
+
+CREATE TABLE LANGUAGE
+(
+ ID INT NOT NULL,
+ PORTLET_ID INT NOT NULL,
+ TITLE VARCHAR (100) NULL,
+ SHORT_TITLE VARCHAR (100) NULL,
+ LOCALE_STRING VARCHAR (50) NOT NULL,
+ KEYWORDS TEXT NULL,
+
+ CONSTRAINT LANGUAGE_PK PRIMARY KEY(ID));
+
+
+
+
+
+/* ---------------------------------------------------------------------- */
+/* PORTLET_CONTENT_TYPE */
+/* ---------------------------------------------------------------------- */
+
+IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'PORTLET_CONTENT_TYPE')
+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 = 'PORTLET_CONTENT_TYPE'
+ 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 PORTLET_CONTENT_TYPE
+END
+;
+
+CREATE TABLE PORTLET_CONTENT_TYPE
+(
+ CONTENT_TYPE_ID INT NOT NULL,
+ PORTLET_ID INT NOT NULL,
+ CONTENT_TYPE VARCHAR (30) NOT NULL,
+ MODES TEXT NULL,
+
+ CONSTRAINT PORTLET_CONTENT_TYPE_PK PRIMARY KEY(CONTENT_TYPE_ID));
+
+
+
+
+
+/* ---------------------------------------------------------------------- */
+/* PARAMETER */
+/* ---------------------------------------------------------------------- */
+
+IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'PARAMETER')
+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 = 'PARAMETER'
+ 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 PARAMETER
+END
+;
+
+CREATE TABLE PARAMETER
+(
+ PARAMETER_ID INT NOT NULL,
+ PARENT_ID INT NOT NULL,
+ CLASS_NAME VARCHAR (255) NOT NULL,
+ NAME VARCHAR (80) NOT NULL,
+ PARAMETER_VALUE TEXT NOT NULL,
+
+ CONSTRAINT PARAMETER_PK PRIMARY KEY(PARAMETER_ID));
+
+
+
+
+
+/* ---------------------------------------------------------------------- */
+/* PORTLET_ENTITY */
+/* ---------------------------------------------------------------------- */
+
+IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'PORTLET_ENTITY')
+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 = 'PORTLET_ENTITY'
+ 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 PORTLET_ENTITY
+END
+;
+
+CREATE TABLE PORTLET_ENTITY
+(
+ PEID INT NOT NULL,
+ ID VARCHAR (255) NOT NULL,
+ APP_NAME VARCHAR (255) NOT NULL,
+ PORTLET_NAME VARCHAR (255) NOT NULL,
+
+ CONSTRAINT PORTLET_ENTITY_PK PRIMARY KEY(PEID),
+ UNIQUE (ID));
+
+
+
+
+
+/* ---------------------------------------------------------------------- */
+/* SECURITY_ROLE_REFERENCE */
+/* ---------------------------------------------------------------------- */
+
+IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'SECURITY_ROLE_REFERENCE')
+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_ROLE_REFERENCE'
+ 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_ROLE_REFERENCE
+END
+;
+
+CREATE TABLE SECURITY_ROLE_REFERENCE
+(
+ ID INT NOT NULL,
+ PORTLET_DEFINITION_ID INT NOT NULL,
+ ROLE_NAME VARCHAR (150) NOT NULL,
+ ROLE_LINK VARCHAR (150) NULL,
+
+ CONSTRAINT SECURITY_ROLE_REFERENCE_PK PRIMARY KEY(ID));
+
+
+
+
+
+/* ---------------------------------------------------------------------- */
+/* SECURITY_ROLE */
+/* ---------------------------------------------------------------------- */
+
+IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'SECURITY_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_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_ROLE
+END
+;
+
+CREATE TABLE SECURITY_ROLE
+(
+ ID INT NOT NULL,
+ WEB_APPLICATION_ID INT NOT NULL,
+ ROLE_NAME VARCHAR (150) NOT NULL,
+ DESCRIPTION VARCHAR (150) NULL,
+
+ CONSTRAINT SECURITY_ROLE_PK PRIMARY KEY(ID));
+
+
+
+
+
+/* ---------------------------------------------------------------------- */
+/* USER_ATTRIBUTE_REF */
+/* ---------------------------------------------------------------------- */
+
+IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='FK_USER_ATTRIBUTE_REF_1')
+ ALTER TABLE USER_ATTRIBUTE_REF DROP CONSTRAINT FK_USER_ATTRIBUTE_REF_1;
+IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'USER_ATTRIBUTE_REF')
+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 = 'USER_ATTRIBUTE_REF'
+ 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 USER_ATTRIBUTE_REF
+END
+;
+
+CREATE TABLE USER_ATTRIBUTE_REF
+(
+ ID INT NOT NULL,
+ APPLICATION_ID INT NOT NULL,
+ NAME VARCHAR (150) NULL,
+ NAME_LINK VARCHAR (150) NULL,
+
+ CONSTRAINT USER_ATTRIBUTE_REF_PK PRIMARY KEY(ID));
+
+
+
+
+
+/* ---------------------------------------------------------------------- */
+/* USER_ATTRIBUTE */
+/* ---------------------------------------------------------------------- */
+
+IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='FK_USER_ATTRIBUTE_1')
+ ALTER TABLE USER_ATTRIBUTE DROP CONSTRAINT FK_USER_ATTRIBUTE_1;
+IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'USER_ATTRIBUTE')
+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 = 'USER_ATTRIBUTE'
+ 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 USER_ATTRIBUTE
+END
+;
+
+CREATE TABLE USER_ATTRIBUTE
+(
+ ID INT NOT NULL,
+ APPLICATION_ID INT NOT NULL,
+ NAME VARCHAR (150) NULL,
+ DESCRIPTION VARCHAR (150) NULL,
+
+ CONSTRAINT USER_ATTRIBUTE_PK PRIMARY KEY(ID));
+
+
+
+
+
+/* ---------------------------------------------------------------------- */
+/* JETSPEED_SERVICE */
+/* ---------------------------------------------------------------------- */
+
+IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'JETSPEED_SERVICE')
+BEGIN
+ DECLARE @reftable_14 nvarchar(60), @constraintname_14 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_SERVICE'
+ OPEN refcursor
+ FETCH NEXT from refcursor into @reftable_14, @constraintname_14
+ while @@FETCH_STATUS = 0
+ BEGIN
+ exec ('alter table '+@reftable_14+' drop constraint '+@constraintname_14)
+ FETCH NEXT from refcursor into @reftable_14, @constraintname_14
+ END
+ CLOSE refcursor
+ DEALLOCATE refcursor
+ DROP TABLE JETSPEED_SERVICE
+END
+;
+
+CREATE TABLE JETSPEED_SERVICE
+(
+ ID INT NOT NULL,
+ APPLICATION_ID INT NOT NULL,
+ NAME VARCHAR (150) NULL,
+
+ CONSTRAINT JETSPEED_SERVICE_PK PRIMARY KEY(ID));
+
+
+
+
+
+/* ---------------------------------------------------------------------- */
+/* LOCALIZED_DESCRIPTION */
+/* ---------------------------------------------------------------------- */
+
+IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'LOCALIZED_DESCRIPTION')
+BEGIN
+ DECLARE @reftable_15 nvarchar(60), @constraintname_15 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 = 'LOCALIZED_DESCRIPTION'
+ OPEN refcursor
+ FETCH NEXT from refcursor into @reftable_15, @constraintname_15
+ while @@FETCH_STATUS = 0
+ BEGIN
+ exec ('alter table '+@reftable_15+' drop constraint '+@constraintname_15)
+ FETCH NEXT from refcursor into @reftable_15, @constraintname_15
+ END
+ CLOSE refcursor
+ DEALLOCATE refcursor
+ DROP TABLE LOCALIZED_DESCRIPTION
+END
+;
+
+CREATE TABLE LOCALIZED_DESCRIPTION
+(
+ ID INT NOT NULL,
+ OBJECT_ID INT NOT NULL,
+ CLASS_NAME VARCHAR (255) NOT NULL,
+ DESCRIPTION TEXT NOT NULL,
+ LOCALE_STRING VARCHAR (50) NOT NULL,
+
+ CONSTRAINT LOCALIZED_DESCRIPTION_PK PRIMARY KEY(ID));
+
+
+
+
+
+/* ---------------------------------------------------------------------- */
+/* LOCALIZED_DISPLAY_NAME */
+/* ---------------------------------------------------------------------- */
+
+IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'LOCALIZED_DISPLAY_NAME')
+BEGIN
+ DECLARE @reftable_16 nvarchar(60), @constraintname_16 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 = 'LOCALIZED_DISPLAY_NAME'
+ OPEN refcursor
+ FETCH NEXT from refcursor into @reftable_16, @constraintname_16
+ while @@FETCH_STATUS = 0
+ BEGIN
+ exec ('alter table '+@reftable_16+' drop constraint '+@constraintname_16)
+ FETCH NEXT from refcursor into @reftable_16, @constraintname_16
+ END
+ CLOSE refcursor
+ DEALLOCATE refcursor
+ DROP TABLE LOCALIZED_DISPLAY_NAME
+END
+;
+
+CREATE TABLE LOCALIZED_DISPLAY_NAME
+(
+ ID INT NOT NULL,
+ OBJECT_ID INT NOT NULL,
+ CLASS_NAME VARCHAR (255) NULL,
+ DISPLAY_NAME TEXT NOT NULL,
+ LOCALE_STRING VARCHAR (50) NOT NULL,
+
+ CONSTRAINT LOCALIZED_DISPLAY_NAME_PK PRIMARY KEY(ID));
+
+
+
+
+
+/* ---------------------------------------------------------------------- */
+/* CUSTOM_PORTLET_MODE */
+/* ---------------------------------------------------------------------- */
+
+IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='FK_CUSTOM_PORTLET_MODE_1')
+ ALTER TABLE CUSTOM_PORTLET_MODE DROP CONSTRAINT FK_CUSTOM_PORTLET_MODE_1;
+IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'CUSTOM_PORTLET_MODE')
+BEGIN
+ DECLARE @reftable_17 nvarchar(60), @constraintname_17 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 = 'CUSTOM_PORTLET_MODE'
+ OPEN refcursor
+ FETCH NEXT from refcursor into @reftable_17, @constraintname_17
+ while @@FETCH_STATUS = 0
+ BEGIN
+ exec ('alter table '+@reftable_17+' drop constraint '+@constraintname_17)
+ FETCH NEXT from refcursor into @reftable_17, @constraintname_17
+ END
+ CLOSE refcursor
+ DEALLOCATE refcursor
+ DROP TABLE CUSTOM_PORTLET_MODE
+END
+;
+
+CREATE TABLE CUSTOM_PORTLET_MODE
+(
+ ID INT NOT NULL,
+ APPLICATION_ID INT NOT NULL,
+ CUSTOM_NAME VARCHAR (150) NOT NULL,
+ MAPPED_NAME VARCHAR (150) NULL,
+ DESCRIPTION TEXT NULL,
+
+ CONSTRAINT CUSTOM_PORTLET_MODE_PK PRIMARY KEY(ID));
+
+
+
+
+
+/* ---------------------------------------------------------------------- */
+/* CUSTOM_WINDOW_STATE */
+/* ---------------------------------------------------------------------- */
+
+IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='FK_CUSTOM_WINDOW_STATE_1')
+ ALTER TABLE CUSTOM_WINDOW_STATE DROP CONSTRAINT FK_CUSTOM_WINDOW_STATE_1;
+IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'CUSTOM_WINDOW_STATE')
+BEGIN
+ DECLARE @reftable_18 nvarchar(60), @constraintname_18 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 = 'CUSTOM_WINDOW_STATE'
+ OPEN refcursor
+ FETCH NEXT from refcursor into @reftable_18, @constraintname_18
+ while @@FETCH_STATUS = 0
+ BEGIN
+ exec ('alter table '+@reftable_18+' drop constraint '+@constraintname_18)
+ FETCH NEXT from refcursor into @reftable_18, @constraintname_18
+ END
+ CLOSE refcursor
+ DEALLOCATE refcursor
+ DROP TABLE CUSTOM_WINDOW_STATE
+END
+;
+
+CREATE TABLE CUSTOM_WINDOW_STATE
+(
+ ID INT NOT NULL,
+ APPLICATION_ID INT NOT NULL,
+ CUSTOM_NAME VARCHAR (150) NOT NULL,
+ MAPPED_NAME VARCHAR (150) NULL,
+ DESCRIPTION TEXT NULL,
+
+ CONSTRAINT CUSTOM_WINDOW_STATE_PK PRIMARY KEY(ID));
+
+
+
+
+
+/* ---------------------------------------------------------------------- */
+/* CUSTOM_WINDOW_STATE */
+/* ---------------------------------------------------------------------- */
+
+
+
+
+/* ---------------------------------------------------------------------- */
+/* PORTLET_DEFINITION */
+/* ---------------------------------------------------------------------- */
+
+
+
+
+/* ---------------------------------------------------------------------- */
+/* PORTLET_APPLICATION */
+/* ---------------------------------------------------------------------- */
+
+
+
+
+/* ---------------------------------------------------------------------- */
+/* WEB_APPLICATION */
+/* ---------------------------------------------------------------------- */
+
+BEGIN
+ALTER TABLE PA_METADATA_FIELDS
+ ADD CONSTRAINT FK_PA_METADATA_FIELDS_1 FOREIGN KEY (OBJECT_ID)
+ REFERENCES PORTLET_APPLICATION (APPLICATION_ID)
+ ON DELETE CASCADE
+END
+;
+
+
+
+
+/* ---------------------------------------------------------------------- */
+/* PA_METADATA_FIELDS */
+/* ---------------------------------------------------------------------- */
+
+BEGIN
+ALTER TABLE PD_METADATA_FIELDS
+ ADD CONSTRAINT FK_PD_METADATA_FIELDS_1 FOREIGN KEY (OBJECT_ID)
+ REFERENCES PORTLET_DEFINITION (ID)
+ ON DELETE CASCADE
+END
+;
+
+
+
+
+/* ---------------------------------------------------------------------- */
+/* PD_METADATA_FIELDS */
+/* ---------------------------------------------------------------------- */
+
+
+
+
+/* ---------------------------------------------------------------------- */
+/* LANGUAGE */
+/* ---------------------------------------------------------------------- */
+
+
+
+
+/* ---------------------------------------------------------------------- */
+/* PORTLET_CONTENT_TYPE */
+/* ---------------------------------------------------------------------- */
+
+
+
+
+/* ---------------------------------------------------------------------- */
+/* PARAMETER */
+/* ---------------------------------------------------------------------- */
+
+
+
+
+/* ---------------------------------------------------------------------- */
+/* PORTLET_ENTITY */
+/* ---------------------------------------------------------------------- */
+
+
+
+
+/* ---------------------------------------------------------------------- */
+/* SECURITY_ROLE_REFERENCE */
+/* ---------------------------------------------------------------------- */
+
+
+
+
+/* ---------------------------------------------------------------------- */
+/* SECURITY_ROLE */
+/* ---------------------------------------------------------------------- */
+
+BEGIN
+ALTER TABLE USER_ATTRIBUTE_REF
+ ADD CONSTRAINT FK_USER_ATTRIBUTE_REF_1 FOREIGN KEY (APPLICATION_ID)
+ REFERENCES PORTLET_APPLICATION (APPLICATION_ID)
+ ON DELETE CASCADE
+END
+;
+
+
+
+
+/* ---------------------------------------------------------------------- */
+/* USER_ATTRIBUTE_REF */
+/* ---------------------------------------------------------------------- */
+
+BEGIN
+ALTER TABLE USER_ATTRIBUTE
+ ADD CONSTRAINT FK_USER_ATTRIBUTE_1 FOREIGN KEY (APPLICATION_ID)
+ REFERENCES PORTLET_APPLICATION (APPLICATION_ID)
+ ON DELETE CASCADE
+END
+;
+
+
+
+
+/* ---------------------------------------------------------------------- */
+/* USER_ATTRIBUTE */
+/* ---------------------------------------------------------------------- */
+
+
+
+
+/* ---------------------------------------------------------------------- */
+/* JETSPEED_SERVICE */
+/* ---------------------------------------------------------------------- */
+
+
+
+
+/* ---------------------------------------------------------------------- */
+/* LOCALIZED_DESCRIPTION */
+/* ---------------------------------------------------------------------- */
+
+
+
+
+/* ---------------------------------------------------------------------- */
+/* LOCALIZED_DISPLAY_NAME */
+/* ---------------------------------------------------------------------- */
+
+BEGIN
+ALTER TABLE CUSTOM_PORTLET_MODE
+ ADD CONSTRAINT FK_CUSTOM_PORTLET_MODE_1 FOREIGN KEY (APPLICATION_ID)
+ REFERENCES PORTLET_APPLICATION (APPLICATION_ID)
+ ON DELETE CASCADE
+END
+;
+
+
+
+
+/* ---------------------------------------------------------------------- */
+/* CUSTOM_PORTLET_MODE */
+/* ---------------------------------------------------------------------- */
+
+BEGIN
+ALTER TABLE CUSTOM_WINDOW_STATE
+ ADD CONSTRAINT FK_CUSTOM_WINDOW_STATE_1 FOREIGN KEY (APPLICATION_ID)
+ REFERENCES PORTLET_APPLICATION (APPLICATION_ID)
+ ON DELETE CASCADE
+END
+;
+
+
+
Added: portals/jetspeed-2/portal/trunk/etc/sql/mssql/schema/security-schema.sql
URL: http://svn.apache.org/viewvc/portals/jetspeed-2/portal/trunk/etc/sql/mssql/schema/security-schema.sql?rev=673895&view=auto
==============================================================================
--- portals/jetspeed-2/portal/trunk/etc/sql/mssql/schema/security-schema.sql (added)
+++ portals/jetspeed-2/portal/trunk/etc/sql/mssql/schema/security-schema.sql Thu Jul 3 19:19:50 2008
@@ -0,0 +1,831 @@
+
+/* ---------------------------------------------------------------------- */
+/* 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='FK_PRINCIPAL_PERMISSION_1')
+ ALTER TABLE PRINCIPAL_PERMISSION DROP CONSTRAINT FK_PRINCIPAL_PERMISSION_1;
+IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='FK_PRINCIPAL_PERMISSION_2')
+ ALTER TABLE PRINCIPAL_PERMISSION DROP CONSTRAINT FK_PRINCIPAL_PERMISSION_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='FK_SECURITY_CREDENTIAL_1')
+ ALTER TABLE SECURITY_CREDENTIAL DROP CONSTRAINT FK_SECURITY_CREDENTIAL_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_PRINC_FK1')
+ ALTER TABLE SSO_SITE_TO_PRINCIPALS DROP CONSTRAINT SSO_SITE_TO_PRINC_FK1;
+IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='SSO_SITE_TO_PRINC_FK2')
+ ALTER TABLE SSO_SITE_TO_PRINCIPALS DROP CONSTRAINT SSO_SITE_TO_PRINC_FK2;
+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='FK_SSO_PRINCIPAL_TO_REMOTE_1')
+ ALTER TABLE SSO_PRINCIPAL_TO_REMOTE DROP CONSTRAINT FK_SSO_PRINCIPAL_TO_REMOTE_1;
+IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='FK_SSO_PRINCIPAL_TO_REMOTE_2')
+ ALTER TABLE SSO_PRINCIPAL_TO_REMOTE DROP CONSTRAINT FK_SSO_PRINCIPAL_TO_REMOTE_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='FK_SSO_SITE_TO_REMOTE_1')
+ ALTER TABLE SSO_SITE_TO_REMOTE DROP CONSTRAINT FK_SSO_SITE_TO_REMOTE_1;
+IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='FK_SSO_SITE_TO_REMOTE_2')
+ ALTER TABLE SSO_SITE_TO_REMOTE DROP CONSTRAINT FK_SSO_SITE_TO_REMOTE_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='FK_SSO_COOKIE_TO_REMOTE_1')
+ ALTER TABLE SSO_COOKIE_TO_REMOTE DROP CONSTRAINT FK_SSO_COOKIE_TO_REMOTE_1;
+IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='FK_SSO_COOKIE_TO_REMOTE_2')
+ ALTER TABLE SSO_COOKIE_TO_REMOTE DROP CONSTRAINT FK_SSO_COOKIE_TO_REMOTE_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='FK_SECURITY_USER_ROLE_1')
+ ALTER TABLE SECURITY_USER_ROLE DROP CONSTRAINT FK_SECURITY_USER_ROLE_1;
+IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='FK_SECURITY_USER_ROLE_2')
+ ALTER TABLE SECURITY_USER_ROLE DROP CONSTRAINT FK_SECURITY_USER_ROLE_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='FK_SECURITY_USER_GROUP_1')
+ ALTER TABLE SECURITY_USER_GROUP DROP CONSTRAINT FK_SECURITY_USER_GROUP_1;
+IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='FK_SECURITY_USER_GROUP_2')
+ ALTER TABLE SECURITY_USER_GROUP DROP CONSTRAINT FK_SECURITY_USER_GROUP_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='FK_SECURITY_GROUP_ROLE_1')
+ ALTER TABLE SECURITY_GROUP_ROLE DROP CONSTRAINT FK_SECURITY_GROUP_ROLE_1;
+IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='FK_SECURITY_GROUP_ROLE_2')
+ ALTER TABLE SECURITY_GROUP_ROLE DROP CONSTRAINT FK_SECURITY_GROUP_ROLE_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 */
+/* ---------------------------------------------------------------------- */
+
+
+/* ---------------------------------------------------------------------- */
+/* DROP TRIGGERS */
+/* ---------------------------------------------------------------------- */
+
+
+
+
+/* ---------------------------------------------------------------------- */
+/* SECURITY_PRINCIPAL */
+/* ---------------------------------------------------------------------- */
+
+
+
+
+/* ---------------------------------------------------------------------- */
+/* SECURITY_PERMISSION */
+/* ---------------------------------------------------------------------- */
+
+BEGIN
+ALTER TABLE PRINCIPAL_PERMISSION
+ ADD CONSTRAINT FK_PRINCIPAL_PERMISSION_1 FOREIGN KEY (PERMISSION_ID)
+ REFERENCES SECURITY_PERMISSION (PERMISSION_ID)
+ ON DELETE CASCADE
+END
+;
+
+BEGIN
+ALTER TABLE PRINCIPAL_PERMISSION
+ ADD CONSTRAINT FK_PRINCIPAL_PERMISSION_2 FOREIGN KEY (PRINCIPAL_ID)
+ REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID)
+ ON DELETE CASCADE
+END
+;
+
+
+
+
+/* ---------------------------------------------------------------------- */
+/* PRINCIPAL_PERMISSION */
+/* ---------------------------------------------------------------------- */
+
+BEGIN
+ALTER TABLE SECURITY_CREDENTIAL
+ ADD CONSTRAINT FK_SECURITY_CREDENTIAL_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_PRINC_FK1 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_PRINC_FK2 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 FK_SSO_PRINCIPAL_TO_REMOTE_1 FOREIGN KEY (PRINCIPAL_ID)
+ REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID)
+ ON DELETE CASCADE ON UPDATE NO ACTION
+END
+;
+
+
+BEGIN
+ALTER TABLE SSO_PRINCIPAL_TO_REMOTE
+ ADD CONSTRAINT FK_SSO_PRINCIPAL_TO_REMOTE_2 FOREIGN KEY (REMOTE_PRINCIPAL_ID)
+ REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID)
+ ON DELETE NO ACTION ON UPDATE NO ACTION
+END
+;
+
+
+
+/* ---------------------------------------------------------------------- */
+/* SSO_PRINCIPAL_TO_REMOTE */
+/* ---------------------------------------------------------------------- */
+
+BEGIN
+ALTER TABLE SSO_SITE_TO_REMOTE
+ ADD CONSTRAINT FK_SSO_SITE_TO_REMOTE_1 FOREIGN KEY (SITE_ID)
+ REFERENCES SSO_SITE (SITE_ID)
+ ON DELETE CASCADE
+END
+;
+
+BEGIN
+ALTER TABLE SSO_SITE_TO_REMOTE
+ ADD CONSTRAINT FK_SSO_SITE_TO_REMOTE_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 FK_SSO_COOKIE_TO_REMOTE_1 FOREIGN KEY (COOKIE_ID)
+ REFERENCES SSO_COOKIE (COOKIE_ID)
+ ON DELETE CASCADE
+END
+;
+
+BEGIN
+ALTER TABLE SSO_COOKIE_TO_REMOTE
+ ADD CONSTRAINT FK_SSO_COOKIE_TO_REMOTE_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 FK_SECURITY_USER_ROLE_1 FOREIGN KEY (ROLE_ID)
+ REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID)
+ ON DELETE CASCADE ON UPDATE NO ACTION
+END
+;
+
+BEGIN
+ALTER TABLE SECURITY_USER_ROLE
+ ADD CONSTRAINT FK_SECURITY_USER_ROLE_2 FOREIGN KEY (USER_ID)
+ REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID)
+ ON DELETE NO ACTION ON UPDATE NO ACTION
+END
+;
+
+
+
+/* ---------------------------------------------------------------------- */
+/* SECURITY_USER_ROLE */
+/* ---------------------------------------------------------------------- */
+
+BEGIN
+ALTER TABLE SECURITY_USER_GROUP
+ ADD CONSTRAINT FK_SECURITY_USER_GROUP_1 FOREIGN KEY (GROUP_ID)
+ REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID)
+ ON DELETE CASCADE ON UPDATE NO ACTION
+END
+;
+
+
+BEGIN
+ALTER TABLE SECURITY_USER_GROUP
+ ADD CONSTRAINT FK_SECURITY_USER_GROUP_2 FOREIGN KEY (USER_ID)
+ REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID)
+ ON DELETE NO ACTION ON UPDATE NO ACTION
+END
+;
+
+
+
+
+/* ---------------------------------------------------------------------- */
+/* SECURITY_USER_GROUP */
+/* ---------------------------------------------------------------------- */
+
+BEGIN
+ALTER TABLE SECURITY_GROUP_ROLE
+ ADD CONSTRAINT FK_SECURITY_GROUP_ROLE_1 FOREIGN KEY (GROUP_ID)
+ REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID)
+ ON DELETE CASCADE
+END
+;
+
+BEGIN
+ALTER TABLE SECURITY_GROUP_ROLE
+ ADD CONSTRAINT FK_SECURITY_GROUP_ROLE_2 FOREIGN KEY (ROLE_ID)
+ REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID)
+ ON DELETE NO ACTION
+END
+;
+
Added: portals/jetspeed-2/portal/trunk/etc/sql/mssql/schema/security-trig-schema.sql
URL: http://svn.apache.org/viewvc/portals/jetspeed-2/portal/trunk/etc/sql/mssql/schema/security-trig-schema.sql?rev=673895&view=auto
==============================================================================
--- portals/jetspeed-2/portal/trunk/etc/sql/mssql/schema/security-trig-schema.sql (added)
+++ portals/jetspeed-2/portal/trunk/etc/sql/mssql/schema/security-trig-schema.sql Thu Jul 3 19:19:50 2008
@@ -0,0 +1,32 @@
+
+IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='TR' AND name='trig_SECURITY_PRINCIPAL')
+ DROP TRIGGER trig_SECURITY_PRINCIPAL
+/
+
+
+CREATE TRIGGER trig_SECURITY_PRINCIPAL
+ ON SECURITY_PRINCIPAL
+ INSTEAD OF DELETE
+ AS
+
+ SET NOCOUNT ON;
+
+ DELETE FROM SSO_PRINCIPAL_TO_REMOTE
+ WHERE REMOTE_PRINCIPAL_ID IN (SELECT PRINCIPAL_ID FROM DELETED)
+;
+
+ DELETE FROM SECURITY_USER_ROLE
+ WHERE USER_ID IN (SELECT PRINCIPAL_ID FROM DELETED)
+;
+
+ DELETE FROM SECURITY_USER_GROUP
+ WHERE USER_ID IN (SELECT PRINCIPAL_ID FROM DELETED)
+;
+
+ DELETE FROM SECURITY_GROUP_ROLE
+ WHERE ROLE_ID IN (SELECT PRINCIPAL_ID FROM DELETED)
+;
+
+ DELETE FROM SECURITY_PRINCIPAL
+ WHERE PRINCIPAL_ID IN (SELECT PRINCIPAL_ID FROM DELETED)
+/
---------------------------------------------------------------------
To unsubscribe, e-mail: jetspeed-dev-unsubscribe@portals.apache.org
For additional commands, e-mail: jetspeed-dev-help@portals.apache.org