You are viewing a plain text version of this content. The canonical link for it is here.
Posted to jetspeed-dev@portals.apache.org by "Scott T Weaver (JIRA)" <je...@portals.apache.org> on 2007/11/08 17:09:50 UTC

[jira] Created: (JS2-812) Better support for MSSQL

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
            Reporter: Scott T Weaver
            Assignee: Scott T Weaver
            Priority: Minor
             Fix For: 2.1.3


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


[jira] Resolved: (JS2-812) Better support for MSSQL

Posted by "Scott T Weaver (JIRA)" <je...@portals.apache.org>.
     [ https://issues.apache.org/jira/browse/JS2-812?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Scott T Weaver resolved JS2-812.
--------------------------------

    Resolution: Fixed

> 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
>            Reporter: Scott T Weaver
>            Assignee: Scott T Weaver
>            Priority: Minor
>             Fix For: 2.1.3
>
>
> 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


[jira] Assigned: (JS2-812) Better support for MSSQL

Posted by "Ate Douma (JIRA)" <je...@portals.apache.org>.
     [ https://issues.apache.org/jira/browse/JS2-812?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Ate Douma reassigned JS2-812:
-----------------------------

    Assignee: Vivek Kumar  (was: Ate Douma)

> 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: Vivek Kumar
>            Priority: Critical
>             Fix For: 2.2
>
>         Attachments: 213patched.zip, mssqlschema.patch
>
>
> 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


[jira] Updated: (JS2-812) Better support for MSSQL

Posted by "Vitaly Baranovsky (JIRA)" <je...@portals.apache.org>.
     [ 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


[jira] Resolved: (JS2-812) Better support for MSSQL

Posted by "Vivek Kumar (JIRA)" <je...@portals.apache.org>.
     [ https://issues.apache.org/jira/browse/JS2-812?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Vivek Kumar resolved JS2-812.
-----------------------------

    Resolution: Fixed

Adding hand-coded scripts for 2. 2
I have removed it from generation list of DDLutils.


> 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: Vivek Kumar
>            Priority: Critical
>             Fix For: 2.2
>
>         Attachments: 213patched.zip, mssqlschema.patch
>
>
> 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


[jira] Reopened: (JS2-812) Better support for MSSQL

Posted by "Ate Douma (JIRA)" <je...@portals.apache.org>.
     [ https://issues.apache.org/jira/browse/JS2-812?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Ate Douma reopened JS2-812:
---------------------------

      Assignee: Ate Douma  (was: Scott T Weaver)

The current handling of recursive constraints still isn't complete nor correct.

The FK_PREFS_NODE_1 constraint on PREFS_NODE still has ON DELETE CASCADE which is not allowed on MSSQL (2005).
So, this needs to be changed to ON CHANGE NO ACTION ON DELETE NO ACTION.
Additionally, it also needs a trigger to handle the delete cascading.

But besides that, the already provided triggers for the other recursive constrains are not correct either.
After doing a long search and diving deep into the MSSQL Transact-SQL manuals, I found a solution which works for the above FK_PREFS_NODE_1 constraint:

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

But, the same solution cannot be 1-to-1 reused for the even more *nested* cascading constraints on dbpsml tables (FOLDER/PAGE/FOLDER_MENU/PAGE_MENU etc)
because INSTEAD OF triggers are not allowed when another cascading FK is referencing its table, sigh.

So, for the dbpsml cascading constraints, the INSTEAD OF triggers on master tables also have to do the hard work of cleaning up children tables as well!
Right now, don't have the time nor endurance to write those triggers too, so this issue will be kept open until we find the time (or someone else) to do so...
Until then, DBPSML on MSSQL isn't supported yet!

Additionally, for the new Jetspeed 2.2 with a complete new maven plugin for initializing the database, integrating custom/overriding sql scripts also needs to be added.

At least for Jetspeed 2.2, the ordering of sql script execution is now defined in the plugin configuration. 
For the Jetspeed 2.1.3 maven-1 build, this currently isn't guaranteed which I found out while trying to create the MSSQL database...

I found a way to get the jetspeed maven plugin (maven-1) to at least execute the scripts in alphabetic order which luckily matches the needed order :)
I'll commit that "fix" to the jetspeed-2.1.3-postrelease branch, together with the added/adjusted custom MSSQL scripts for handling the  FK_PREFS_NODE_1 constraint.

> 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
>            Reporter: Scott T Weaver
>            Assignee: Ate Douma
>            Priority: Minor
>             Fix For: 2.1.3
>
>
> 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


[jira] Updated: (JS2-812) Better support for MSSQL

Posted by "Ate Douma (JIRA)" <je...@portals.apache.org>.
     [ https://issues.apache.org/jira/browse/JS2-812?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Ate Douma updated JS2-812:
--------------------------

        Fix Version/s: 2.2
    Affects Version/s: 2.1.3

> 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
>
>
> 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


[jira] Updated: (JS2-812) Better support for MSSQL

Posted by "Ate Douma (JIRA)" <je...@portals.apache.org>.
     [ https://issues.apache.org/jira/browse/JS2-812?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Ate Douma updated JS2-812:
--------------------------

    Fix Version/s:     (was: 2.1.3)
         Priority: Critical  (was: Minor)

> 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: Critical
>             Fix For: 2.2
>
>         Attachments: 213patched.zip, mssqlschema.patch
>
>
> 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


[jira] Commented: (JS2-812) Better support for MSSQL

Posted by "David Sean Taylor (JIRA)" <je...@portals.apache.org>.
    [ https://issues.apache.org/jira/browse/JS2-812?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12610422#action_12610422 ] 

David Sean Taylor commented on JS2-812:
---------------------------------------

see http://svn.apache.org/viewvc?view=rev&revision=673895

I committed schema for SQL Server that works from a custom Jetspeed installer (Ant)
Its not clear to me how the jetspeed-db maven plugin handles schema that should not be generated, like for Derby
These scripts require two different kinds of delimiters, for the 2 trigger sql files (phase2-trig-schema.sql, prefs-trig-schema.sql), 
we use '/' as the delimiter and 'row' as the delimeterType. For the other scripts, we use defaults (;)

Additionally, the order of SQL file execution will be important


> 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, mssqlschema.patch
>
>
> 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


[jira] Updated: (JS2-812) Better support for MSSQL

Posted by "Vitaly Baranovsky (JIRA)" <je...@portals.apache.org>.
     [ https://issues.apache.org/jira/browse/JS2-812?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Vitaly Baranovsky updated JS2-812:
----------------------------------

    Attachment: mssqlschema.patch

I've attached a patch.
This scirpts are generated and not exists in svn repository. So, I've created the patch using linux diff command.
Original scripts was in folder 213, patched is in folder 213patched

> 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, mssqlschema.patch
>
>
> 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


[jira] Commented: (JS2-812) Better support for MSSQL

Posted by "Vitaly Baranovsky (JIRA)" <je...@portals.apache.org>.
    [ https://issues.apache.org/jira/browse/JS2-812?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12599055#action_12599055 ] 

Vitaly Baranovsky commented on JS2-812:
---------------------------------------

And I forgot one more tip! Inside sql script you have to place GO after each trigger code!

> 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, mssqlschema.patch
>
>
> 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