[
https://issues.apache.org/jira/browse/JS2-812?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Vitaly Baranovsky updated JS2-812:
----------------------------------
Attachment: 213patched.zip
I've created working scripts for ms sql and test them. They work successfully
and deletes all elements with subtrees and rows from child tables without any
problem!
I've attached all the scripts for ms sql. So, it looks like you have to create
different algorithm for ms sql generation from xml-schemas...
Algorithm of generation is:
1) You have to remove all your FOR DELETE triggers that has created in Jetspeed
2.1.3. So, now you how to delete drop-triggers.sql and all tg_*.sql files.
2) You have to look for all tables that stores a tree. Theese are the tables,
that have foreign keys for themself.
2.1) You have to change ON DELETE CASCADE foreign keys for trees to ON DELETE
NO ACTION and you have to create next triggers for all this tables:
IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='TR' AND name='trig_%TABLE%')
DROP TRIGGER trig_%TABLE%;
GO
CREATE TRIGGER [dbo].[trig_%TABLE%]
ON %TABLE%
INSTEAD OF DELETE
AS
SET NOCOUNT ON;
WITH cte AS
( SELECT %PK_ID%, %PARENT_ID%
FROM DELETED
UNION ALL
SELECT c.%PK_ID%, c.%PARENT_ID%
FROM %TABLE% AS c
INNER JOIN cte AS p
ON c.%PARENT_ID% = p.%PK_ID%
)
SELECT *
into #tmp
FROM cte
OPTION (MAXRECURSION 32767)
DELETE FROM %TABLE%
WHERE %PK_ID% IN
(SELECT %PK_ID% FROM #TMP)
drop table #tmp
GO
where:
%TABLE% - name of current table
%PK_ID% - primary key of current table
%PARENT_ID% - foreign key to parent id in same table
Some comments on code:
OPTION (MAXRECURSION 32767) - it allows max nesting lavel of jetspeed objects
of 32767 levels and disallows infinity loops on deletion.
SET NOCOUNT ON; - doesn't allows server to send message "n rows affected" to
client
2.2) For each tree table that has another foreign keys for cascade deletion,
you have to change foreign keys types to ON DELETE NO ACTION and add code to
trigger of master table for deletion of child rows:
2.2.1) if master table is tree table:
You have to add code between rows OPTION (MAXRECURSION 32767) and DELETE FROM
%TABLE%:
DELETE FROM %CHILD_TABLE%
WHERE %FK_ID% IN (SELECT %PK_ID% FROM #tmp);
where:
%CHILD_TABLE% - name of child table when you want to delete rows cascadelly
%FK_ID% - foreign key id of child table
%PK_ID% - primary key of master table (which trigger you are changing)
2.2.2) if master table is not tree table:
You have to add same to code to INSTEAD OF DELETE trigger of master table. If
INSTEAD OF DELETE trigger is not exists yet, you have to create it before with
code:
IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='TR' AND name='trig_%TABLE%')
DROP TRIGGER trig_%TABLE%;
GO
CREATE TRIGGER [dbo].[trig_%TABLE%]
ON %TABLE%
INSTEAD OF DELETE
AS
SET NOCOUNT ON;
2.3) For each trigger that has two foreign keys to same master table (there are
4 such tables in security_schema.sql) you have to change type of second foreign
key to ON DELETE NO ACTION and add code from 1.2.1 to INSTEAD OF DELETE master
table. %FK_ID% in that case is the foreign key field for second foreign key. If
INSTEAD OF DELETE trigger is not exists yet, you have to create it before with
code from 1.2.2.
> Better support for MSSQL
> ------------------------
>
> Key: JS2-812
> URL: https://issues.apache.org/jira/browse/JS2-812
> Project: Jetspeed 2
> Issue Type: Improvement
> Components: Project Build
> Affects Versions: 2.1.2, 2.1.3
> Reporter: Scott T Weaver
> Assignee: Ate Douma
> Priority: Minor
> Fix For: 2.1.3, 2.2
>
> Attachments: 213patched.zip
>
>
> Need to add better support for MS SQL. The real issue is that MS SQL lacks
> support for recursive constraints, where all other databases support it just
> fine. As is always the case with MS, they consider this a "feature"
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
