ORM violating database unique constraint

Hi!

In our database we have two tables (A,B) which are connected using a connection table (A_B) (defined with a ThroughAssociation in the domain model).

Each combination of id's from the two tables A and B has to be unique in the connection table.

Now, if i clear the ThroughAssociation list in A (or B), and then insert an item that was previously contained in the list before it was cleared, I get a database constraint error when I save, since LightSpeed inserts the new items before it deletes the cleared items.

Is this a LightSpeed bug, or can we force the delete to be done before the insert?

Attached is a test-case. It uses MySql as databaseprovider, I hope that is OK.

 Best regards

Björn Andersson

Admeta AB

You cannot force LightSpeed to do the delete before the insert.  LightSpeed always performs inserts first, then updates, then deletes.

A possible workaround, however, is to perform a SaveChanges() after clearing the ThroughAssociation.  This will flush the deletes.  You can then set up the new associations and do another SaveChanges() which will perform the inserts.  In order to avoid other users seeing the temporarily empty through association you would want to perform both operations within the same transaction.

I'll butt in again since Björn and I discussed this issue today.

This is an unfortunate ORM affect I think. On the other hand it seems to be quite a general problem with O/R mappers. Here are three NHibernate links discussing what seem to be a very simular problem:

http://opensource.atlassian.com/projects/hibernate/browse/HHH-2801
http://opensource.atlassian.com/projects/hibernate/browse/HHH-1268
http://opensource.atlassian.com/projects/hibernate/browse/HHH-1296

The main problem seem to be that it is not possible to specify the execution order of your insert/updates. 9 times out of 10, the exceution order is probably correct as the O/R mapper is using it as a standard. But then a special scenario pops up that renders this type of error.

[quote user="ivan"]

A possible workaround, however, is to perform a SaveChanges() after clearing the ThroughAssociation.  This will flush the deletes.  You can then set up the new associations and do another SaveChanges() which will perform the inserts.

[/quote]

The problem with the proposed solution appears if you in a wizard like application would like to make changes to you domain model in several different pages, like for instance a wizard Preferably you would like to use some kind of Repository encapsulation in the end, not caring about the actual save operation in all but the last wizard. If you would have to delete stuff before the last step in the wizard, you'll have a problem since you would like all changes to be within a transaction.

I guess you can transport your transaction object between different steps, but it still feels like a bit of a nasty workaround unfortunatly.

One interesting approach is what the LLBLGen Pro O/R mapper seem to allow. Look at the "Specifying the order in which the actions are executed" in the following page: http://www.llblgen.com/documentation/2.6/Using%20the%20generated%20code/SelfServicing/gencode_unitofwork.htm. Would this be somthing that Lightspeed might be able to support in the future?

I agree that the proffered workaround is not ideal -- to be fair I did describe it as a "workaround" rather than a "solution" *grin*.

I don't see any fundamental issue with allowing application code to override the default order of actions, though it depends on how fine-grained this needs to be.  I think specifying "do deletes before inserts" would be reasonably easy.  Specifying "do these deletes first, then these inserts, then the remaining deletes" would be a lot harder.  It sounds like the first would meet your immediate need though and the second is an edge case which we could look at if it ever came up.

I've logged a feature request to do the first option and we'll see if we can fit that in soonish. 

[quote user="ivan"]

I agree that the proffered workaround is not ideal -- to be fair I did describe it as a "workaround" rather than a "solution" *grin*.

 [/quote] 

Yes. Sorry. My bad typing.

[quote user="ivan"]

I've logged a feature request to do the first option and we'll see if we can fit that in soonish.

 [/quote] 

Ok, great! Thanks. We have implemented your workaround now for the issue we ran into, but for future problems with this we hopefully we'll be able to work with the new fix instead. 

Of course, we are interested to know whenish , but I do have respect for that you don't want to commit to a release date. (hej, I'm a developer too...). But is there anywhere a note of issues displayed on the web with bugs/new features that have been solved for each nightly build? Otherwise we'd have to wait for the release note of an upcoming new release I guess.

 

We don't currently provide release notes alongside nightly builds, though this is something on our wish list too!  We try to post in the relevant forum thread when a nightly includes a feature or bug fix that has come in through the forum -- e.g if/when we implement order control I will post in this thread.