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