portals-jetspeed-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Ate Douma (JIRA)" <jetspeed-...@portals.apache.org>
Subject [jira] Reopened: (JS2-812) Better support for MSSQL
Date Fri, 16 May 2008 08:16:55 GMT

     [ 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


Mime
View raw message