[pal-cvs 3263] [997] apply patch 'r656971: Part fix for JS2-812: Better support for MSSQL .'

svnno****@sourc***** svnno****@sourc*****
2008年 6月 25日 (水) 17:18:35 JST


Revision: 997
          http://svn.sourceforge.jp/cgi-bin/viewcvs.cgi?root=pal&view=rev&rev=997
Author:   sone
Date:     2008-06-25 17:18:35 +0900 (Wed, 25 Jun 2008)

Log Message:
-----------
apply patch 'r656971: Part fix for JS2-812: Better support for MSSQL.'

Modified Paths:
--------------
    pal-portal/branches/pal-portal-1.x/portal/jetspeed-2/etc/sql/mssql/schema/drop-triggers.sql

Added Paths:
-----------
    pal-portal/branches/pal-portal-1.x/portal/jetspeed-2/etc/sql/mssql/schema/prefs-schema.sql
    pal-portal/branches/pal-portal-1.x/portal/jetspeed-2/etc/sql/mssql/schema/tg_prefs_node.sql


-------------- next part --------------
Modified: pal-portal/branches/pal-portal-1.x/portal/jetspeed-2/etc/sql/mssql/schema/drop-triggers.sql
===================================================================
--- pal-portal/branches/pal-portal-1.x/portal/jetspeed-2/etc/sql/mssql/schema/drop-triggers.sql	2008-06-25 07:20:27 UTC (rev 996)
+++ pal-portal/branches/pal-portal-1.x/portal/jetspeed-2/etc/sql/mssql/schema/drop-triggers.sql	2008-06-25 08:18:35 UTC (rev 997)
@@ -9,6 +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: pal-portal/branches/pal-portal-1.x/portal/jetspeed-2/etc/sql/mssql/schema/prefs-schema.sql
===================================================================
--- pal-portal/branches/pal-portal-1.x/portal/jetspeed-2/etc/sql/mssql/schema/prefs-schema.sql	                        (rev 0)
+++ pal-portal/branches/pal-portal-1.x/portal/jetspeed-2/etc/sql/mssql/schema/prefs-schema.sql	2008-06-25 08:18:35 UTC (rev 997)
@@ -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    
+;
+
+
+

Added: pal-portal/branches/pal-portal-1.x/portal/jetspeed-2/etc/sql/mssql/schema/tg_prefs_node.sql
===================================================================
--- pal-portal/branches/pal-portal-1.x/portal/jetspeed-2/etc/sql/mssql/schema/tg_prefs_node.sql	                        (rev 0)
+++ pal-portal/branches/pal-portal-1.x/portal/jetspeed-2/etc/sql/mssql/schema/tg_prefs_node.sql	2008-06-25 08:18:35 UTC (rev 997)
@@ -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)
+;


pal-cvs メーリングリストの案内