OK, thanks for the feedback Andrus! mrg
On Tue, May 11, 2010 at 2:06 AM, Andrus Adamchik <[email protected]> wrote: >> >> Is there a reason why INSERTs occur before DELETEs? > > Yes, the current operation ordering is designed for presumably the most > common scenario. But it is definitely not working for all cases. > Specifically INSERT goes in before DELETE, as deleted objects may have FK > references from other objects replaced by inserted object. So DELETE can > only be done after INSERT and UPDATE. > >> Say the "key" column needs to be unique and you delete >> the record with key="timeout" and insert another new record with >> key="timeout". > > Some edge cases of this scenario (e.g. delete/insert of the same join record > of a many-to-many) are handled by Cayenne as an UPDATE. But others where PK > is different for two records but some other UNIQUE column is same can't be > handled as UPDATE. > > I'd go as far as saying that without deferred constraint checking at the DB > level it is not even possible to always satisfy an arbitrary set of DB > constraints just by reordering operations. So a user solution to that would > be doing multiple commits (I know this sucks)... > > On Cayenne end we can investigate the same multi-commit approach, but done > in the same internal transaction, so that (a) it is transparent to the user > and (b) an atomic rollback is possible. Not sure if that approach would > satisfy constraint checkers on all DB's though. > > Andrus > > > > On May 10, 2010, at 6:50 PM, Michael Gentry wrote: > >> Is there a reason why INSERTs occur before DELETEs? > >> It looks like >> this is the bit of code that orders it (at least in 3.0): >> >> DataDomainFlushAction.java / preprocess >> >> insertBucket.appendQueries(queries); >> flattenedBucket.appendInserts(queries); >> updateBucket.appendQueries(queries); >> flattenedBucket.appendDeletes(queries); >> deleteBucket.appendQueries(queries); >> >> >> There seems to be an issue if you have a DB constraint (unique column, >> for example). Say the "key" column needs to be unique and you delete >> the record with key="timeout" and insert another new record with >> key="timeout". The commit will fail because the insert occurs before >> the delete. >> >> Thanks, >> >> mrg >> > >
