Then there are cases that don't have correct ordering at all. Usually this
involves cyclic relationships. I don't have an example handy, but IIRC we
discussed that some time ago.
Andrus
On Feb 17, 2011, at 3:29 PM, Bryan Lewis wrote:
> This sounds like the same problem we've lived with for years. In a
> complicated model (hundreds of entities for us), Cayenne's graph-following
> algorithm (AshwoodEntitySorter) doesn't have enough information to perform
> all the commits in exactly the right order. (Perhaps this is an argument
> for supporting database constraints in the modeler, so that Cayenne would
> know about them.)
>
> If multiple interrelated entities are inserted or deleted in the same
> commit, they can occasionally get committed out of order. This matters if
> your database is fully constrained. (For us the error usually involves a
> foreign-key constraint. I've wondered why other people weren't asking about
> this problem more often, and assumed that most people didn't have such large
> models, or seldom did multi-entiy commits, or didn't add constraints to the
> database.)
>
> Anyway, the fix is to override the EntitySorter and explicitly tell it the
> order in each case that arises.
>
>
> // We call this to initialize our model... the first time a DataContext
> is created.
> private static synchronized Configuration initConfiguration()
> {
> ...
> DataDomain domain = config.getDomain();
> // We have only one DataNode.
> DataNode node = domain.getDataNodes().iterator().next();
>
> node.setEntitySorter(new CustomEntitySorter());
> ...
> }
>
> public class CustomEntitySorter extends AshwoodEntitySorter
> {
> public CustomEntitySorter()
> {
> super(Collections.EMPTY_LIST);
> }
>
> @Override
> public void sortDbEntities(List<DbEntity> dbEntities, boolean
> deleteOrder)
> {
> // Somewhat ugly code, depending on Cayenne internals.
> _indexSorter();
> Collections.sort(dbEntities, getDbEntityComparator(deleteOrder));
>
> // No need to check the ordering of a single-entity list.
> if (dbEntities.size() <= 1) {
> return;
> }
>
> // Two examples. In our apps we have a few dozen of these lines.
> putParentBeforeChild("contact", "note", dbEntities, deleteOrder);
> putParentBeforeChild("company", "companyphone", dbEntities,
> deleteOrder);
> }
>
> /**
> * If the parent object comes after the child in the list, move it
> before
> * the child. Unless we're deleting, which reverses the order.
> *
> * The naming might be a bit confusing. I'm calling the parent the
> * entity that appears to be the source of the relationship in the
> modeler.
> * The parent record needs to be inserted first.
> *
> * The confusing thing is, the parent table is the one pointed *to* by
> * the FK constraint, and the child is the table that defines the
> constraint.
> */
> private void putParentBeforeChild(String parentDbEntityName,
> String childDbEntityName,
> List<DbEntity> dbEntities,
> boolean deleteOrder)
> {
> int iParent = -1;
> int iChild = -1;
>
> // Protect against a misspelling of an entity name, which I did
> once.
> List<String> allDbEntityNames =
> Model.getInstance().getDbEntityNames();
> if (!allDbEntityNames.contains(parentDbEntityName)) {
> log.error("!! Unrecognized parentDbEntity name " +
> parentDbEntityName);
> }
> if (!allDbEntityNames.contains(childDbEntityName)) {
> log.error("!! Unrecognized childDbEntity name " +
> childDbEntityName);
> }
>
> for (int i = 0; i < dbEntities.size(); i++) {
> DbEntity entity = dbEntities.get(i);
> String name = entity.getName();
> // Use equalsIgnoreCase in case we're switching between Oracle
> and Postgres databases.
> if (name.equalsIgnoreCase(parentDbEntityName) && iParent == -1)
> {
> iParent = i;
> }
> // Check for -1 so that we get the lowest child in case there
> // are more than one. Might not ever matter.
> if (name.equalsIgnoreCase(childDbEntityName) && iChild == -1) {
> iChild = i;
> }
> }
>
> if (iParent != -1 && iChild != -1) {
> StringBuilder buf = new StringBuilder();
> if (deleteOrder) {
> if (iParent < iChild) {
> buf.append("-- need to move
> ").append(childDbEntityName);
> buf.append(" before ").append(parentDbEntityName);
> buf.append(" for deletion");
> log.debug(buf.toString());
>
> // Remove and re-add it just before the parent.
> DbEntity childEntity = dbEntities.remove(iChild);
> dbEntities.add(iParent, childEntity);
> }
> }
> else {
> if (iParent > iChild) {
> buf.append("-- need to move
> ").append(parentDbEntityName);
> buf.append(" before ").append(childDbEntityName);
> buf.append(" for insertion");
> log.debug(buf.toString());
>
> // Remove and re-add it just before the child.
> DbEntity parentEntity = dbEntities.remove(iParent);
> dbEntities.add(iChild, parentEntity);
> }
> }
> }
> }
> }
>
>
>
> On Thu, Feb 17, 2011 at 3:11 AM, Aristedes Maniatis <[email protected]>wrote:
>
>> On 15/02/11 2:04 PM, Marcin Skladaniec wrote:
>>
>>> java.sql.SQLIntegrityConstraintViolationException: The statement was
>>> aborted because it would have caused a duplicate key value in a unique or
>>> primary key constraint or unique index identified by 'TAGRELATION_UNIQUE'
>>> defined on 'TAGRELATION'.
>>>
>>
>>
>> Can I summarise, since I think you've highlighted a problem which is
>> completely different to your email subject.
>>
>> 1. Table A has primary key which is user defined (in this case, it is the
>> join between two other tables, so the PK is the compound of the two foreign
>> keys)
>>
>> 2. Create record in table A. Commit.
>>
>> 3. Delete that record in table A.
>>
>> 4. Create another record in table A with the same PK as the one just
>> deleted (since it joins the same other two tables).
>>
>> 5. Commit -> ERRROR
>>
>>
>> The create in step 4 is being committed to the db before the delete in step
>> 3, failing the key constraint.
>>
>>
>> Use case
>> --------
>> You may wonder why on earth this is happening. Well, in a rich client
>> application a user might tick a checkbox to link two records and then untick
>> it and tick it again while they make up their mind. All those changes result
>> in changes to the context.
>>
>>
>> Solution
>> --------
>>
>> A. Dedupe the overlapping create/delete records in the context before
>> committing.
>> B. Order the SQL better.
>>
>>
>>
>> Any thoughts about how Cayenne could deal with this better? What do other
>> ORMs do in this scenario?
>>
>>
>>
>> Ari
>>
>>
>> --
>> -------------------------->
>> Aristedes Maniatis
>> GPG fingerprint CBFB 84B4 738D 4E87 5E5C 5EFA EF6A 7D2E 3E49 102A
>>
