You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@openjpa.apache.org by "Reece Garrett (JIRA)" <ji...@apache.org> on 2007/05/04 20:25:15 UTC
[jira] Created: (OPENJPA-235) SQL reordering to avoid non-nullable
foreign key constraint violations
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
Fix For: 0.9.8
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.
Re: [jira] Commented: (OPENJPA-235) SQL reordering to avoid non-nullable
foreign key constraint violations
Posted by Gokhan Ergul <go...@telenity.com>.
Marc Prud'hommeaux wrote:
> Gokhan-
>
>> On a related note, does the build process support/plan to support
>> TestNG tests or should I stick to junit?
>
> I looked into this a little while back: Maven supposedly supports both
> TestNG and JUnit used together in tandem, but I was never able to get
> it working at all. The bugs I found appear to have been reported and
> marked for the next release of the surefire plugin, but I haven't
> tested with more recent releases to see if they really do work.
>
> In short: you should probably stick with junit for the time being for
> OpenJPA tests.
>
Fair enough, thanks Marc.
Gokhan.
Re: [jira] Commented: (OPENJPA-235) SQL reordering to avoid non-nullable foreign key constraint violations
Posted by Marc Prud'hommeaux <mp...@apache.org>.
Gokhan-
> On a related note, does the build process support/plan to support
> TestNG tests or should I stick to junit?
I looked into this a little while back: Maven supposedly supports
both TestNG and JUnit used together in tandem, but I was never able
to get it working at all. The bugs I found appear to have been
reported and marked for the next release of the surefire plugin, but
I haven't tested with more recent releases to see if they really do
work.
In short: you should probably stick with junit for the time being for
OpenJPA tests.
On May 22, 2007, at 5:03 PM, Gokhan Ergul wrote:
> Patrick Linskey wrote:
>>> Gokhan, I am interested in the test cases that this broke so if
>>> you have time please email me.
>>
>> +1 -- if you've got any test cases that we could incorporate into the
>> OpenJPA build environment, it'd be great to get them integrated.
> Existing test cases are part of an application test suite which
> requires a whole set of domain objects and a fairly complex schema
> --not suitable for OpenJPA build environment. I'll try to strip off
> representative and standalone test cases out of them and will post
> when ready.
>
> On a related note, does the build process support/plan to support
> TestNG tests or should I stick to junit?
>
> Gokhan.
Re: [jira] Commented: (OPENJPA-235) SQL reordering to avoid non-nullable
foreign key constraint violations
Posted by Gokhan Ergul <go...@telenity.com>.
Patrick Linskey wrote:
>> Gokhan, I am interested in the test cases that this broke so if
>> you have time please email me.
>
> +1 -- if you've got any test cases that we could incorporate into the
> OpenJPA build environment, it'd be great to get them integrated.
Existing test cases are part of an application test suite which requires
a whole set of domain objects and a fairly complex schema --not suitable
for OpenJPA build environment. I'll try to strip off representative and
standalone test cases out of them and will post when ready.
On a related note, does the build process support/plan to support TestNG
tests or should I stick to junit?
Gokhan.
Re: [jira] Commented: (OPENJPA-235) SQL reordering to avoid non-nullable foreign key constraint violations
Posted by Patrick Linskey <pl...@gmail.com>.
> Gokhan, I am interested in the test cases that this broke so if
> you have time please email me.
+1 -- if you've got any test cases that we could incorporate into the
OpenJPA build environment, it'd be great to get them integrated.
> I apologize for any inconvenience this has caused but I was
> not aware of additional test cases that I needed to run.
No worries. The TCK is a bit tricky since only people who have signed
an NDA are allowed access to it.
-Patrick
--
Patrick Linskey
202 669 5907
[jira] Commented: (OPENJPA-235) SQL reordering to avoid
non-nullable foreign key constraint violations
Posted by "Marc Prud'hommeaux (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-235?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12497641 ]
Marc Prud'hommeaux commented on OPENJPA-235:
--------------------------------------------
I didn't look to closely at the failures. I just saw that the TCK hadn't been passing since May 4th, so I figured it would be best to restore its passing status, and the patch author could work to determine why the failures were occurring. I do recall that the errors I saw had to do with foreign key constraint violations, which was a little surprising (since the patch was designed to avoid precisely that).
The errors are pretty easily reproducable with the command I mention above, provided you have access to the TCK.
> 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
> Assigned To: Patrick Linskey
> Fix For: 0.9.8
>
> Attachments: sqlreorder.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.
[jira] Updated: (OPENJPA-235) SQL reordering to avoid non-nullable
foreign key constraint violations
Posted by "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-test.jar
Hi Reece,
This test case produces a foreign key violation when applying your previous patch.
-- markus.
> 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
> Assigned To: Patrick Linskey
> Fix For: 0.9.8
>
> Attachments: openjpa-235-test.jar, sqlreorder.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.
[jira] Commented: (OPENJPA-235) SQL reordering to avoid
non-nullable foreign key constraint violations
Posted by "Reece Garrett (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-235?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12500768 ]
Reece Garrett commented on OPENJPA-235:
---------------------------------------
Markus,
That is great news! Thanks for running those test and getting back to me.
Dereferenced means that a persistence capable object was either nulled or replaced. I need to capture this information because the dereference may cause a delete. If that is the case, then by the time the code gets to OperationOrderUpdateManager there is no way to calculated the dependency between the delete and the update which caused it without knowing who dereferenced who.
As a concrete example: A has a non-nullable reference to B and no one else refers to B. You replace B with B2 thus dereferencing B. B is now orphaned and will be deleted. You get three rows generated:
Row 1: insert B2
Row 2: update A to point to B2
Row 3: delete B
So, row 2 depends on row 1 and row 3 depends on row 2. But, without knowing that the state manager for row 2 was responsible for dereferencing the state manager for row 3 (thus causing the delete) there is no way to know that row 3 depends on row 2. Without having that dependency row 3 could be run before row 2 and would violate the foreign key constraint.
As for your second question, no, non-nullable foreign keys are not the only reason for constraint
dependencies. Unique key constraints also cause dependencies. With unique keys an insert can depend on an update or delete and an update can depend on another update or delete. I mentioned earlier that I have a plan for dealing with this but decided that it would be best complete my patch for foreign keys first and then tackle uniques. I have a post on the mailing list where I get into a bit more detail. The post was placed on May 22nd and is titled "SQL ordering and unique constraints".
Hope that all made sense. If you have any more questions please ask away.
Gokhan,
I'm sorry but I have not had a chance to apply your patch and try my code with it yet. I'll try my best to get to it but assuming your patch corrects the problem I was running into I see no reason why it won't work. I'm being lazy by not looking but have you posted your issue as a bug and posted the patch for these guys to test?
-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, 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.
[jira] Commented: (OPENJPA-235) SQL reordering to avoid
non-nullable foreign key constraint violations
Posted by "Reece Garrett (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-235?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12497991 ]
Reece Garrett commented on OPENJPA-235:
---------------------------------------
I have been doing further work on this patch over the past week to deal with several edge cases I did not initially think of (which may have caused the failures) and to deal with unique constraint violations. I will reproduce and fix the tests Marc referred to.
Gokhan, I am interested in the test cases that this broke so if you have time please email me.
I apologize for any inconvenience this has caused but I was not aware of additional test cases that I needed to run. This functionality is sorely needed for anyone using a database which does not support deferred constraints so I will continue to work on it.
Also, I just posted a message on the mailing list, "SQL ordering and unique constraints", and would appreciate any suggestions on how to proceed.
> 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
> Assigned To: Patrick Linskey
> Fix For: 0.9.8
>
> Attachments: sqlreorder.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.
[jira] Reopened: (OPENJPA-235) SQL reordering to avoid non-nullable
foreign key constraint violations
Posted by "Marc Prud'hommeaux (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-235?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Marc Prud'hommeaux reopened OPENJPA-235:
----------------------------------------
I need to revert the patch for OperationOrderUpdateManager, since it is causing 19 TCK failures (one of the test failures can be reproduced with: mvn integration-test -Dtest=false -Ptck-profile -Djpatck.pkg.dir=com/sun/ts/tests/ejb30/persistence/annotations/orderby ).
> 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
> Assigned To: Patrick Linskey
> Fix For: 0.9.8
>
> Attachments: sqlreorder.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.
[jira] Updated: (OPENJPA-235) SQL reordering to avoid non-nullable
foreign key constraint violations
Posted by "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-test1.jar
Hi Reece,
Thanks for trying my test case. I've been playing around with commit dependencies, and this is, what I found:
1) One reason, why your previous patch didn't work is, as you already found out, that the rows returned by RowManagerImpl.getInserts returns are not in user order. This might be unexpected and should either be documented or changed (I haven't found any usages of RowManagerImpl.getInserts in the current code, so it might be possible to change that). An alternative would be to add a new method RowManagerImpl.getOrderedInserts.
2) Another situation, that generally doesn't work (both w/ your patch and w/o), is when relationships are nullified before the objects are deleted. Please see my new test openjpa-235-test1. OpenJPA doesn't execute updates for deleted objects. There will be a fk constraint violation if the updates are skipped and the objects aren't modified according to the constraint dependencies inside the user transaction. I couldn't find how to get "old" relationship value before the update, which would be needed for the dependency calculation.
3) It seems, that join table entries are removed from the non-owning side. IMHO, relationship updates should only be done from the owning side.
> 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
> Assigned To: Patrick Linskey
> Fix For: 0.9.8
>
> Attachments: openjpa-235-test.jar, openjpa-235-test1.jar, sqlreorder.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.
[jira] Commented: (OPENJPA-235) SQL reordering to avoid
non-nullable foreign key constraint violations
Posted by "Gokhan Ergul (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-235?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12500481 ]
Gokhan Ergul commented on OPENJPA-235:
--------------------------------------
Reece --thanks for trying them out, good news indeed on the attached-merge front. The issue with detached testcase is as follows: merge A cascades to B, which in turn cascades to C, since the relations between A -> B and B -> C are annotated with Cascade.ALL on OneToMany sides. But as there's no cascade annotation on ManyToOne side (owner side of bidirectional relation), when AttachManager tries to attach those fields (such as C.parent), it encounters a new object (an instance of EntityB), and complains as such. In all fairness, that's correct -- B is a new object and there's no Cascade.MERGE from C.parent to B. The missing piece of information is, that B object has already been attached (evidently since manager._attached map contains it) as part of that "merge session" so to speak --that's the object via which AttachManager got to object C in the first place, and B will be persisted as part of the same transaction. So there's no grounds to reject it. That's what the patch I attached along with the testcase does. If you could go ahead and apply it, you should no longer see that error.
> 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, 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.
[jira] Commented: (OPENJPA-235) SQL reordering to avoid
non-nullable foreign key constraint violations
Posted by "Gokhan Ergul (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-235?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12497650 ]
Gokhan Ergul commented on OPENJPA-235:
--------------------------------------
FWIW, I've tested the patch with the hope that it'd also fix OPENJPA-231, however it broke a good number of my local tests which persist fresh objects (with multiple generations of cascading collections), I couldn't get to the stage where I can test merge behaviour at all --had to rollback locally.
> 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
> Assigned To: Patrick Linskey
> Fix For: 0.9.8
>
> Attachments: sqlreorder.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.
[jira] Commented: (OPENJPA-235) SQL reordering to avoid
non-nullable foreign key constraint violations
Posted by "Reece Garrett (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-235?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12500460 ]
Reece Garrett commented on OPENJPA-235:
---------------------------------------
Gokhan,
Thank you for the test cases. The first test case ( testMergeAttached ) passes but the other case ( testMergeDetached ) fails before it even gets to my code. I see what you're trying to do and my patch should handle it. I'm sorry I don't have time to tell you why the error occurs but I suspect it is a problem with your annotations. Here is the error testMergeDetached generates:
<0.0.0 nonfatal user error> org.apache.openjpa.persistence.ArgumentException: Encountered new object "org.apache.openjpa.persistence.merge.model.EntityB@1450f1f" in persistent field "org.apache.openjpa.persistence.merge.model.EntityC.parent" of managed object "org.apache.openjpa.persistence.merge.model.EntityC@1a722ef" during attach. However, this field does not allow cascade attach. You cannot attach a reference to a new object without cascading.
FailedObject: org.apache.openjpa.persistence.merge.model.EntityB@1450f1f
at org.apache.openjpa.kernel.AttachStrategy.getReference(AttachStrategy.java:276)
at org.apache.openjpa.kernel.AttachStrategy.attachField(AttachStrategy.java:193)
at org.apache.openjpa.kernel.VersionAttachStrategy.attach(VersionAttachStrategy.java:134)
at org.apache.openjpa.kernel.AttachManager.attach(AttachManager.java:239)
at org.apache.openjpa.kernel.AttachStrategy.attachCollection(AttachStrategy.java:330)
at org.apache.openjpa.kernel.AttachStrategy.replaceCollection(AttachStrategy.java:298)
at org.apache.openjpa.kernel.AttachStrategy.attachField(AttachStrategy.java:220)
at org.apache.openjpa.kernel.VersionAttachStrategy.attach(VersionAttachStrategy.java:134)
at org.apache.openjpa.kernel.AttachManager.attach(AttachManager.java:239)
at org.apache.openjpa.kernel.AttachStrategy.attachCollection(AttachStrategy.java:330)
at org.apache.openjpa.kernel.DetachedStateManager.attach(DetachedStateManager.java:250)
at org.apache.openjpa.kernel.AttachManager.attach(AttachManager.java:239)
at org.apache.openjpa.kernel.AttachManager.attach(AttachManager.java:100)
at org.apache.openjpa.kernel.BrokerImpl.attach(BrokerImpl.java:3176)
at org.apache.openjpa.kernel.DelegatingBroker.attach(DelegatingBroker.java:1147)
at org.apache.openjpa.persistence.EntityManagerImpl.merge(EntityManagerImpl.java:665)
at org.piercecountywa.personnel.openjpa.TestMultigenOneToManyMerge.testMergeDetached(TestMultigenOneToManyMerge.java:108)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at junit.framework.TestCase.runTest(TestCase.java:164)
at junit.framework.TestCase.runBare(TestCase.java:130)
at junit.framework.TestResult$1.protect(TestResult.java:110)
at junit.framework.TestResult.runProtected(TestResult.java:128)
at junit.framework.TestResult.run(TestResult.java:113)
at junit.framework.TestCase.run(TestCase.java:120)
at junit.framework.TestSuite.runTest(TestSuite.java:228)
at junit.framework.TestSuite.run(TestSuite.java:223)
at org.junit.internal.runners.OldTestClassRunner.run(OldTestClassRunner.java:35)
at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:38)
at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:460)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:673)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:386)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:196)
Again, the good news is the first test case runs great and what you're trying to do in the second test should work great too. If you can figure out what the issue is on that second test please post it.
> 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, 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.
[jira] Updated: (OPENJPA-235) SQL reordering to avoid non-nullable
foreign key constraint violations
Posted by "Gokhan Ergul (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-235?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Gokhan Ergul updated OPENJPA-235:
---------------------------------
Attachment: merge-detached.patch
merge-multigen-collection-testcase.zip
Reece,
I'm sorry it took so long, somehow I managed to mess up maven plugin repo, wasn't able to run tests at all for the last week. Testcases attached, they're ready to be used as part of standard openjpa tests. TestMultigenOneToManyMerge.updateDetached should succeed with the patch I attached. TestMultigenOneToManyMerge.updateAttached works with auto-generated tables against Derby, fails against mysql/innodb with foreign keys defined between tables, that's the case I'm hoping your patch would solve.
Let me know if you have trouble setting up/running the tests.
Gokhan.
> 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, 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.
[jira] Commented: (OPENJPA-235) SQL reordering to avoid
non-nullable foreign key constraint violations
Posted by "Gokhan Ergul (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-235?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12500765 ]
Gokhan Ergul commented on OPENJPA-235:
--------------------------------------
Marcus,
I'm not familiar with the specifics of Reece's patch (yet), but to answer your question from a theoretical point of view: As your example demonstrates, not-nullable FKs are not the only reason for such dependencies. The real issue with ordering statements is not about nullable/not-nullable constraints, but rather with whether the target database supports deferred constraints or not (constraints enforced at transaction commit time, as opposed constraints enforced after each statement). If the target database supports deferred constraints, we'd have no need to reorder statements --assuming that resulting set of rows (or lack of them) at transaction commit do not violate any constraints. Otherwise the transaction will fail anyway, irrespective of if/how you order the statements.
On a related note, since you have access to TCK tests, is there any chance you can apply the patch I posted to see if it breaks anything? If we could take that detached testcase out of the picture I'll go ahead mark OPENJPA-231 resolved, since the issue with attached testcase is the same with this one.
Thanks,
Gokhan.
> 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, 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.
[jira] Commented: (OPENJPA-235) SQL reordering to avoid
non-nullable foreign key constraint violations
Posted by "Reece Garrett (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-235?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12498842 ]
Reece Garrett commented on OPENJPA-235:
---------------------------------------
I am just about done revising my patch but I do not have access to the TCK and can not sign the NDA because I work for a government agency which will not allow it. All the test cases I can run (mvn test) pass but that's not saying much because they passed the first time. As I said, I have caught a few cases that I did not think of the first time and am hopeful that they were the source of the TCK failures but can not be sure. Also, the algorithm is now iterative instead of recursive, does almost no collection copying, and orders rows based purely on dependencies instead of lumping all inserts first followed by updates and then deletes. I decided not to include support for ordering unique keys until I get foreign key ordering resolved (although I'm fairly certain my idea for unique keys will work well.
I'm at a loss as to how to proceed from here. I'll have a new patch ready soon but since I can not run the TCK tests myself I don't feel comfortable resubmitting it and potentially taking up more of your development time tracking down bugs I've introduced. What should I do with my new patch?
> 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
> Assigned To: Patrick Linskey
> Fix For: 0.9.8
>
> Attachments: sqlreorder.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.
[jira] Commented: (OPENJPA-235) SQL reordering to avoid
non-nullable foreign key constraint violations
Posted by "Patrick Linskey (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-235?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12497632 ]
Patrick Linskey commented on OPENJPA-235:
-----------------------------------------
Any idea about the nature of the failure? Was the patch reordering things incorrectly?
Ideally, we should fix this in a way that passes the TCK; did it seem like a significant enough problem that it's worthwhile to create a separate non-default UpdateManager for use by the TCK (or for use to access the feature)?
> 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
> Assigned To: Patrick Linskey
> Fix For: 0.9.8
>
> Attachments: sqlreorder.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.
[jira] Assigned: (OPENJPA-235) SQL reordering to avoid non-nullable
foreign key constraint violations
Posted by "Patrick Linskey (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-235?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Patrick Linskey reassigned OPENJPA-235:
---------------------------------------
Assignee: Patrick Linskey
> 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
> Assigned To: Patrick Linskey
> Fix For: 0.9.8
>
> Attachments: sqlreorder.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.
[jira] Updated: (OPENJPA-235) SQL reordering to avoid non-nullable
foreign key constraint violations
Posted by "Reece Garrett (JIRA)" <ji...@apache.org>.
[ 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.
[jira] Updated: (OPENJPA-235) SQL reordering to avoid non-nullable
foreign key constraint violations
Posted by "Reece Garrett (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-235?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Reece Garrett updated OPENJPA-235:
----------------------------------
Attachment: sqlreorder.patch
Patch includes code changes and test cases.
> 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
> Fix For: 0.9.8
>
> Attachments: sqlreorder.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.
[jira] Resolved: (OPENJPA-235) SQL reordering to avoid non-nullable
foreign key constraint violations
Posted by "Patrick Linskey (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-235?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Patrick Linskey resolved OPENJPA-235.
-------------------------------------
Resolution: Fixed
> 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
> Assigned To: Patrick Linskey
> Fix For: 0.9.8
>
> Attachments: sqlreorder.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.
[jira] Commented: (OPENJPA-235) SQL reordering to avoid
non-nullable foreign key constraint violations
Posted by "Reece Garrett (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-235?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12499262 ]
Reece Garrett commented on OPENJPA-235:
---------------------------------------
Markus,
Thank you for your test case. It runs just fine with my new code. I will post a new patch on Tuesday. Everyone have a great weekend!
> 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
> Assigned To: Patrick Linskey
> Fix For: 0.9.8
>
> Attachments: openjpa-235-test.jar, sqlreorder.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.
[jira] Commented: (OPENJPA-235) SQL reordering to avoid
non-nullable foreign key constraint violations
Posted by "Markus Fuchs (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-235?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12500542 ]
Markus Fuchs commented on OPENJPA-235:
--------------------------------------
Hi Reece,
I ran the JPA TCK with your changes, and all tests passed!
Congratulations.
But I'd need some more time to really understand your changes. One
question upfront: What does "dereference" mean? Does it mean changing
the reference to a persistence capable object?
Hi Reece & Gokhan:
Are non-nullable foreign keys the only reason for constraint
dependencies? One update can be dependent on another update in case of
a OneToOne relationship, as OneToOne relationships are usually
enforced by unique keys in the database. Please also consider the
following situation:
a -> b;
c -> d;
tx.begin();
c -> b;
a -> null;
tx.commit();
B/c of the unique key, the foreign key in the row corresponding to "a"
has to be nullified, *before* the foreign key in row "c" can be
updated. Does this dependency also need to be addressed?
Is above dependency different from the dependencies imposed by
non-nullable foreign keys? I.e. can non-nullable foreign keys cause
dependencies, that can't be addressed by the user order of operations?
Are non-nullable foreign keys different in this regard?
Thanks and sorry for my late reply!
-- markus.
> 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, 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.
[jira] Commented: (OPENJPA-235) SQL reordering to avoid
non-nullable foreign key constraint violations
Posted by "Patrick Linskey (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-235?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12499200 ]
Patrick Linskey commented on OPENJPA-235:
-----------------------------------------
Hi,
Attach the patch to the JIRA and one of those of us with TCK access will run it against the TCK.
> 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
> Assigned To: Patrick Linskey
> Fix For: 0.9.8
>
> Attachments: openjpa-235-test.jar, sqlreorder.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.