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)
+;