[
https://issues.apache.org/jira/browse/OPENJPA-235?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12502535
]
Reece Garrett commented on OPENJPA-235:
---------------------------------------
Hello Patrick,
Your patch works great for foreign keys but I wrote my patch in anticipation of
adding uniqueness constraint dependencies. Markus is correct, when you consider
uniqueness constraints an insert can depend on an update or a delete and an
update can depend on another update or a delete.
Say you have an insert which depends on a delete because of a uniqueness
constraint. The delete should get ordered first. However, the delete was
originally generated because there is an update either nulling or replacing a
foreign key reference to it . If the delete runs before the update a foreign
key violation will occur because the row being deleted has not been
dereferenced by the update yet. Upon seeing that the delete precedes the update
which it depends on the code must update the offending foreign key to null
before the delete executes (this assumes the foreign key is nullable, if not
then you're out of luck).
The code you committed does not know about the dependency between the delete
and the update and thus can not handle the above situation. In fact, without
having my list of dereferenced states for each state manager I do not see how
the dependency can be calculated.
Other than that, the code looks great. My test cases pass and I will attach
them as a patch ("sqlReorderTests.patch").
Any input you have on the uniqueness constraint issue would be much appreciated
because that's the last piece to this puzzle. Secretly I'm hoping you guys will
tackle this one because it doesn't look too hard and will give you guys
(another) advantage over the competition. I know I can do it but it will
require going a bit deeper into the code than I feel totally comfortable with.
For all updates and deletes I'd have to store the original values (as they
stood at the beginning of the transaction) for columns involved in uniqueness
constraints. Then, when calculating dependencies for inserts and updates where
uniqueness constraints are involved I'd check all updates and deletes going to
the same table and see if they need to be run first to prevent a unique key
violation. Like I said, it shouldn't be rocket science as all the data needed
to do this is pulled from the database; just not stored for later access.
Updates have some of these original values stored for rollback but deletes
don't store any of the original values except the primary key and version.
Basically, if there was a method "RowImpl.getOriginal(Column col)" that works
just like the existing method "RowImpl.getSet(Column col)" but returns the
original value for the specified column then it would be smooth sailing from
there.
-Reece
> 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-multigen-collection-testcase.zip, openjpa-235-test.jar,
> openjpa-235-test1.jar, openjpa-235-test2.zip, 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.
