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 at...@apache.org on 2008/05/16 10:45:44 UTC
svn commit: r656971 - in
/portals/jetspeed-2/portal/branches/JETSPEED-2.1.3-POSTRELEASE/etc/sql/mssql/schema:
drop-triggers.sql prefs-schema.sql tg_prefs_node.sql
Author: ate
Date: Fri May 16 01:45:44 2008
New Revision: 656971
URL: http://svn.apache.org/viewvc?rev=656971&view=rev
Log:
Part fix for JS2-812: Better support for MSSQL.
This change handles the cascading delete needed for the FK_PREFS_NODE_1 constraint on PREFS_NODE.
See: https://issues.apache.org/jira/browse/JS2-812
Added:
portals/jetspeed-2/portal/branches/JETSPEED-2.1.3-POSTRELEASE/etc/sql/mssql/schema/prefs-schema.sql (with props)
portals/jetspeed-2/portal/branches/JETSPEED-2.1.3-POSTRELEASE/etc/sql/mssql/schema/tg_prefs_node.sql (with props)
Modified:
portals/jetspeed-2/portal/branches/JETSPEED-2.1.3-POSTRELEASE/etc/sql/mssql/schema/drop-triggers.sql
Modified: portals/jetspeed-2/portal/branches/JETSPEED-2.1.3-POSTRELEASE/etc/sql/mssql/schema/drop-triggers.sql
URL: http://svn.apache.org/viewvc/portals/jetspeed-2/portal/branches/JETSPEED-2.1.3-POSTRELEASE/etc/sql/mssql/schema/drop-triggers.sql?rev=656971&r1=656970&r2=656971&view=diff
==============================================================================
--- portals/jetspeed-2/portal/branches/JETSPEED-2.1.3-POSTRELEASE/etc/sql/mssql/schema/drop-triggers.sql (original)
+++ portals/jetspeed-2/portal/branches/JETSPEED-2.1.3-POSTRELEASE/etc/sql/mssql/schema/drop-triggers.sql Fri May 16 01:45:44 2008
@@ -9,7 +9,9 @@
IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='TR' AND name='trig_fragment')
DROP TRIGGER trig_fragment;
+
+IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='TR' AND name='trig_prefs_node')
+ DROP TRIGGER trig_prefs_node;
IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='TR' AND name='trig_security_principal')
DROP TRIGGER trig_security_principal;
-
Added: portals/jetspeed-2/portal/branches/JETSPEED-2.1.3-POSTRELEASE/etc/sql/mssql/schema/prefs-schema.sql
URL: http://svn.apache.org/viewvc/portals/jetspeed-2/portal/branches/JETSPEED-2.1.3-POSTRELEASE/etc/sql/mssql/schema/prefs-schema.sql?rev=656971&view=auto
==============================================================================
--- portals/jetspeed-2/portal/branches/JETSPEED-2.1.3-POSTRELEASE/etc/sql/mssql/schema/prefs-schema.sql (added)
+++ portals/jetspeed-2/portal/branches/JETSPEED-2.1.3-POSTRELEASE/etc/sql/mssql/schema/prefs-schema.sql Fri May 16 01:45:44 2008
@@ -0,0 +1,128 @@
+
+/* ---------------------------------------------------------------------- */
+/* 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);
+
+
+
+
+/* ---------------------------------------------------------------------- */
+/* 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 ON UPDATE 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 CASCADE
+END
+;
+
+
+
Propchange: portals/jetspeed-2/portal/branches/JETSPEED-2.1.3-POSTRELEASE/etc/sql/mssql/schema/prefs-schema.sql
------------------------------------------------------------------------------
svn:eol-style = native
Propchange: portals/jetspeed-2/portal/branches/JETSPEED-2.1.3-POSTRELEASE/etc/sql/mssql/schema/prefs-schema.sql
------------------------------------------------------------------------------
svn:keywords = Id
Added: portals/jetspeed-2/portal/branches/JETSPEED-2.1.3-POSTRELEASE/etc/sql/mssql/schema/tg_prefs_node.sql
URL: http://svn.apache.org/viewvc/portals/jetspeed-2/portal/branches/JETSPEED-2.1.3-POSTRELEASE/etc/sql/mssql/schema/tg_prefs_node.sql?rev=656971&view=auto
==============================================================================
--- portals/jetspeed-2/portal/branches/JETSPEED-2.1.3-POSTRELEASE/etc/sql/mssql/schema/tg_prefs_node.sql (added)
+++ portals/jetspeed-2/portal/branches/JETSPEED-2.1.3-POSTRELEASE/etc/sql/mssql/schema/tg_prefs_node.sql Fri May 16 01:45:44 2008
@@ -0,0 +1,19 @@
+CREATE TRIGGER trig_prefs_node
+ON prefs_node
+INSTEAD OF DELETE
+AS
+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
+)
+DELETE a
+FROM prefs_node AS a
+INNER JOIN cte AS b
+ON a.node_id = b.node_id
+OPTION (MAXRECURSION 0)
+;
Propchange: portals/jetspeed-2/portal/branches/JETSPEED-2.1.3-POSTRELEASE/etc/sql/mssql/schema/tg_prefs_node.sql
------------------------------------------------------------------------------
svn:eol-style = native
Propchange: portals/jetspeed-2/portal/branches/JETSPEED-2.1.3-POSTRELEASE/etc/sql/mssql/schema/tg_prefs_node.sql
------------------------------------------------------------------------------
svn:keywords = Id
---------------------------------------------------------------------
To unsubscribe, e-mail: jetspeed-dev-unsubscribe@portals.apache.org
For additional commands, e-mail: jetspeed-dev-help@portals.apache.org