[
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: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
