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
