openjpa-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Reece Garrett (JIRA)" <j...@apache.org>
Subject [jira] Updated: (OPENJPA-235) SQL reordering to avoid non-nullable foreign key constraint violations
Date Wed, 30 May 2007 20:42:15 GMT

     [ 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.


Mime
View raw message