portals-jetspeed-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Vitaly Baranovsky (JIRA)" <jetspeed-...@portals.apache.org>
Subject [jira] Updated: (JS2-812) Better support for MSSQL
Date Thu, 22 May 2008 15:23:56 GMT

     [ 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: jetspeed-dev-unsubscribe@portals.apache.org
For additional commands, e-mail: jetspeed-dev-help@portals.apache.org


Mime
View raw message