Details

    • Type: Improvement Improvement
    • Status: Closed
    • Priority: Critical Critical
    • Resolution: Fixed
    • Affects Version/s: 2.1.2, 2.1.3
    • Fix Version/s: 2.2.0
    • Component/s: Project Build
    • Labels:
      None

      Description

      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"

      1. 213patched.zip
        14 kB
        Vitaly Baranovsky
      2. mssqlschema.patch
        12 kB
        Vitaly Baranovsky

        Issue Links

          Activity

          Scott T Weaver created issue -
          Scott T Weaver made changes -
          Field Original Value New Value
          Status Open [ 1 ] Resolved [ 5 ]
          Resolution Fixed [ 1 ]
          Hide
          Ate Douma added a comment -

          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.

          Show
          Ate Douma added a comment - 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.
          Ate Douma made changes -
          Resolution Fixed [ 1 ]
          Status Resolved [ 5 ] Reopened [ 4 ]
          Assignee Scott T Weaver [ weaver ] Ate Douma [ adouma ]
          Ate Douma made changes -
          Affects Version/s 2.1.3 [ 12312573 ]
          Fix Version/s 2.2 [ 12312318 ]
          Ate Douma made changes -
          Link This issue is duplicated by JS2-838 [ JS2-838 ]
          Hide
          Vitaly Baranovsky added a comment -

          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.

          Show
          Vitaly Baranovsky added a comment - 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.
          Vitaly Baranovsky made changes -
          Attachment 213patched.zip [ 12382573 ]
          Hide
          Vitaly Baranovsky added a comment -

          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

          Show
          Vitaly Baranovsky added a comment - 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
          Vitaly Baranovsky made changes -
          Attachment mssqlschema.patch [ 12382574 ]
          Hide
          Vitaly Baranovsky added a comment -

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

          Show
          Vitaly Baranovsky added a comment - And I forgot one more tip! Inside sql script you have to place GO after each trigger code!
          Hide
          David Sean Taylor added a comment -

          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

          Show
          David Sean Taylor added a comment - 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
          Ate Douma made changes -
          Priority Minor [ 4 ] Critical [ 2 ]
          Fix Version/s 2.1.3 [ 12312573 ]
          Ate Douma made changes -
          Assignee Ate Douma [ adouma ] Vivek Kumar [ firevelocity ]
          Hide
          Vivek Kumar added a comment -

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

          Show
          Vivek Kumar added a comment - Adding hand-coded scripts for 2. 2 I have removed it from generation list of DDLutils.
          Vivek Kumar made changes -
          Resolution Fixed [ 1 ]
          Status Reopened [ 4 ] Resolved [ 5 ]
          Ate Douma made changes -
          Status Resolved [ 5 ] Closed [ 6 ]

            People

            • Assignee:
              Vivek Kumar
              Reporter:
              Scott T Weaver
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development