[jira] Updated: (OPENJPA-235) SQL reordering to avoid non-nullable foreign key constraint violations

["Markus Fuchs (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/OPENJPA-235?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Markus Fuchs updated OPENJPA-235:
---------------------------------

    Attachment: openjpa-235-break-nullable.patch

This patch guarantees that circular dependencies are always broken at nullable foreign keys. Its successfully tested by new tests and running the JPA TCK. The changes are:

ConstraintUpdateManager.java:
=============================
- analyzeForeignKeys, analyzeAgainstInserts:
the direction of edges in the dependency graph has been reversed. 
A node has edges to other nodes it depends on
- flushGraph:
Handles circular constraints
  - if deleted row A has a ciricular fk to deleted row B, 
  then use an update statement to null A's fk to B before flushing, 
  and then flush
  - if inserted row A has a circular fk to updated/inserted row B,
  then null the fk in the B row object, then flush,
  and after flushing, use an update to set the fk back to A
Depending on where circular dependencies are broken, the  
topological order of the graph nodes has to be re-calculated;
The Lists deleteUpdates and insertUpdates are always initialized, 
since they are passed to resolceCycles 
- addDeleteUpdate, addInsertUpdate:
refactored the code nulifying foreign keys into separate methods
- findBreakableLink:
finds a nullable foreign key by walking the dependency cycle.
- recalculateDFA:
Re-calculates the DepthFirstSearch analysis of the graph 
after some of the edges have been removed. Assertions ensure
that the graph is cycle free.
- resolveCycles:
Resolves cycles by identifying a nullable foreign key, and 
then calling either addDeleteUpdate or addInsertUpdate depending 
on the operation causing the dependency

ForeignKey.java:
================
- hasNotNullColumns:
checks for non-nullable local columns
 
kernel/localizer.properties:
============================
- added messages used in ConstraintUpdateManager.

DepthFirstAnalysis.java:
========================
- added localizer for messages
- visit:
a) takes a list of graph edges traversed while walking the graph in DFS; 
list is initially empty; 
always contains the edges walked to reach currently visited node
b) the cycle is calculated for back- and forward edges;
The cycle always exists for back edges part: of path + back edge;
The cycle might exist for forward edges: Reasons for forward edges
i) The graph is disconnected or ii) Threre's a cycle. 
- buildCycle:
Constructs the cycle for back edges: part of DFS path + back edge
- cycleForBackEdge:
calculates the cycle for a back edge
- cycleForForwardEdge:
Once a forward edge is found, the graph is walked to find a path from
the end of the forward edge back to it's beginning node
- findNodeInPath:
Finds the position of the edge starting from a node in a continuous 
list of edges.
- NodeInfoComparator.compare
Since the edge direction is reversed, node are sorted by finished order

Edge.java:
==========
- List cycle holds the cycle for back- and forward edges
- cycle field is re-set in clearTraversal

util/localizer.properties:
==========================
- added messages used in DepthFirstAnalysis.

TestDepthFirstAnalysis.java:
============================
- refactored test data set up
- adjusted testNodeSorting for reversed edge direction
- added new graph resembling the commit dependencies in 
TestNoForeignKeyViolation#testComplexCycle to test cycle detection

EntityB.java, EntityC.java:
===========================
- reintroduced "non optional" property removed in last commit

EntityD.java:
=============
- added two optional relationships to EntityA and EntityB. Both
relationships may cause a circular dependency

EntityE.java:
=============
- added class, has a nullable relationship to EntityB. This
relationship doesn't cause a circular dependency

TestNoForeignKeyViolation.java:
===============================
- refactored test data set up
- testSimpleCycle
Dependency graph has exactly one cycle. 
The nodes on the cycle don't have any edges which are not on the cycle
- testComplexCycle
Like above. One node on the cycle has an edge which is not on the cycle
- testComplexTwoCycles
Dependency graph has two cycles. Otherwise like above.


> SQL reordering to avoid non-nullable foreign key constraint violations
> ----------------------------------------------------------------------
>
>                 Key: OPENJPA-235
>                 URL: https://issues.apache.org/jira/browse/OPENJPA-235
>             Project: OpenJPA
>          Issue Type: Improvement
>          Components: kernel
>            Reporter: Reece Garrett
>            Assignee: Patrick Linskey
>             Fix For: 0.9.8
>
>         Attachments: merge-detached.patch, merge-testcases.patch, openjpa-235-break-nullable.patch, openjpa-235-test.jar, openjpa-235-test1.jar, openjpa-235-test2.zip, sqlreorder.patch, sqlReorder2.patch, sqlReorderTests.patch
>
>
> OpenJPA does not do any SQL statement re-ordering in order to resolve foreign key constraints. Instead, objects are always inserted in the order in which the user persists the instances.  When you persist in an order that would violate foreign key constraints, OpenJPA attempts to insert null and then update the foreign key value in a separate statement. If you use non-nullable constraints, though, you must persist your objects in the correct order.
> This improvement re-orders SQL statements as follows:
> 1. First, all insert statements execute. Inserts which have foreign keys with non-nullable constraints execute AFTER the foreign keys which they depend on have been inserted since no deferred update is possible.
> 2. Next, all update statements execute. No reordering is necessary.
> 3.  Finally, all delete statements execute. Like inserts, deletes execute in an order which does not violate non-nullable foreign key constraints.
> If a circular foreign key reference is found during the re-ordering process then re-ordering halts and the remaining unordered statements are left as is. There is nothing that can be done about the circular reference (other than fixing the schema) and the resulting SQL statements will not succeed.
> The net effect is that users do not need to worry about the persistence order of their objects regardless of non-nullable foreign key constraints. The only class modified was org.apache.openjpa.jdbc.kernel.OperationOrderUpdateManager. I have included a patch which includes my modifications to OperationOrderUpdateManager and test cases. The test cases I have provided fail on the current trunk but pass with my modifications. I have also verified that I did not break anything by using maven to run all test cases with my modifications in place.

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.