SQL ordering and unique constraints

[Reece Garrett <RG...@co.pierce.wa.us>]

Hello,

I previously submitted a patch which reorders the SQL statmements generated by OpenJPA to avoid violating foreign key constraints. In addition to making improvements on that patch ( more efficient and I caught a few edge cases that I did not initially think of ) I am trying to deal with unique key constraint violations. 

This quote from the docs illustrates what I am talking about: "...suppose there is a unique constraint on the columns of field F. In the same transaction, you remove an object A and persist a new object B, both with the same F value. The JPA runtime must ensure that the SQL deleting A is sent to the database before the SQL inserting B to avoid a unique constraint violation." Unfortunately, OpenJPA does not ensure the SQL is executed in such a manner that the unique key is not violated. The insert of object B is depends on the removal of object A but in order to calculated that dependency I need to know what the initial values of the columns of field F were at the beginning of the transaction. 

I am aware that some initial values are stored in the statemanagers to facilitate rollback, however, not all fields are saved (in the case of deletes none are saved) and I have no way to efficiently map from columns (the columns involved in a unique constraint) back to the saved fields. Also, deferred constraints are not an option due to the DBMS I am forced to use.

In a nutshell, for updates and deletes I need to have access to the initial values (at the beginning of the transaction) of any columns involved in a unique constraint in order to calculated SQL statement dependencies. Before I go any further I would like suggestions as to how to proceed. 

-Reece