[
https://issues.apache.org/jira/browse/OPENJPA-235?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Reece Garrett updated OPENJPA-235:
----------------------------------
Attachment: sqlReorder2.patch
I've posted my updated patch (sqlReorder2.patch). It is considerably different
from the first patch. Statements are ordered based on dependency to other
statements. The logic is as follows:
INSERTS: an insert statement depends on other insert statements involving rows
with a non-nullable foreign key to it. If the foreign key is nullable and it is
necessary to avoid a constraint violation then the foreign key columns are
initially inserted as null and later updated after the foreign key has been
inserted.
UPDATES: an update statement depends on insert statements involving rows with a
non-nullable foreign key to it. If the foreign key is nullable and it is
necessary to avoid a constraint violation then the foreign key columns are
initially updated to null and later updated again after the foreign key has
been inserted.
DELETES: A delete statement depends on other delete statements involving rows
with a non-nullable foreign key to it. If the foreign key is nullable and it is
necessary to avoid a constraint violation then an update is generated to null
the offending foreign key before this delete executes. A delete statement may
be generated because another delete or update dereferenced it's state. If this
is the case then said delete depends on the update or delete that caused the
dereferenced state.
To calculate dependencies for dereferenced states it is necessary to store
those states in the state that dereferenced them. This involved adding a
collection (set) to each state, methods to add and remove states to the
collection, and a method to retrieve the states from the collection. Of the
three methods mentioned only the method that retrieves the dereferenced states
was added to the OpenJPAStateManager interface. I had to add the method to the
interface because OperationOrderUpdateManager (where I calculate dependencies)
programs to that interface, however, SingleFieldManager, where the calls to
dereference and un-dereference states initiate) does not. SingleFieldManager
programs to StateManagerImpl which is an implementation of OpenJPAStatManager.
Of course that did not prevent me from adding the add and remove methods to the
OpenJPAStateManager interface but there was no reason to.
Adding the one method to the OpenJPAStateManager interface forced me to change
all implementing classes including DetatchedStateManager,
DetachedValueStateManager, ObjectIdStateManager, NullEmbeddedStateManager (an
inner class of EmbededFieldStrategy), and of course StateManagerImpl.
StateManagerImpl is the only implementation that actually uses the method so
the rest of classes throw either an UnsupportedOperationException or an
InternalException.
I should also mention that I consider updating a persistence capable field to
null to be dereferencing it and added the state for that persistence capable
object to the collection of dereferenced states.
I have provided a simple test case to exercise my code and have verified that
all other OpenJPA test cases pass (with the exception of the TCK tests which I
do not have access to). Without being able to run the TCK tests I cannot
guarantee that I have addressed the issues that caused the previous failures,
but I have caught several cases that I did not catch the first time so I am
confident.
Markus,
Again, thank you for the test cases. They all pass and I have addressed all of
your issues you mentioned in your last post except for your comment about
deletes from join tables. Join tables are secondary tables and are handled up
front by OpenJPA so I don't mess with any of that.
> 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: openjpa-235-test.jar, openjpa-235-test1.jar,
> sqlreorder.patch, sqlReorder2.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.
