portals-jetspeed-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From a..@apache.org
Subject 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
Date Fri, 16 May 2008 08:45:44 GMT
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


Mime
View raw message