How to swap unique, ordering column entries with NHibernate and Envers?

Question

I am working with NHibernate for a while now and finally hit a problem, I can neither find an answer, nor anyone else how seemed to have this problem. The environment is NHibernate 3.3.1.4000 and NHibernate.Envers 1.4 for history keeping.

I've got a database table/entity class of the following kind:

public class ProcedureStep
{
    public virtual int Id { get; protected internal set; }
    public virtual Procedure ParentObject { get; set; }
    public virtual int StepNo { get; set; }
    public virtual string Name { get; set; }
}

with the mapping:

public class ProcedureStepMap : ClassMap<ProcedureStep>
{
    public ProcedureStepMap()
    {
        Table("ProcedureStep");
        Id(x => x.Id);

        References(x => x.ParentObject).Not.Nullable().UniqueKey("UK_PO_SN");
        Map(x => x.StepNo).Not.Nullable().UniqueKey("UK_PO_SN");
        Map(x => x.Name).Not.Nullable();
    }
}

We have a parent object with a list of procedure steps. The user can now add, delete or modify the different steps. One "modification" is the change of the ordering of the procedure steps using a button to move a selected item up or down in the list of steps.

Let's make a concrete example:

The scenario is a list of 4 procedure steps 1-4:

(1) Aaaa
(2) Bbbb <--
(3) Cccc
(4) Dddd

The users selectes the second item "Bbbb" and presses the "Up" button, to get:

(1) Bbbb <--
(2) Aaaa
(3) Cccc
(4) Dddd

In the code I now thought of doing something like

Begin Transaction
1) Set "Aaaa" to StepNo = 0
2) Set "Bbbb" to StepNo = 1
3) Set "Aaaa" to StepNo = 2
End Transaction

But now NHibernates get's into play. NHibernate does not know anything about step 1 when committing the transaction, and is therefore trying to work with only 2 update commands to the database (steps 2 and 3), which is not working, due to the unique character of the StepNo column. Adding a session.flush() after step 1 is not changing anything to that behaviour (quite understandable to me, as we are within a transaction).

The next idea was just using session.CreateQuery("update...").ExecuteUpdate() to do the changes on the database, which is working fine, but is working around Envers and leaving me without a history record for this change (quite understandable once again, as the sql is just passed on).

But what would be the correct way to do this change, with having everything in one transaction and having the history of Envers? Could it be, that not making the "StepNo" column unique is the only way to get it working? Loosing the unique character of the column would not be wanted, as it is important to have a propper ordering and having the database ensuring this, would be a great help.

Thanks upfront for your ideas!

[Solution]:

jbl and cremor seem to have the only answers. Together with some other points of trouble, we now decided to skip Envers completly and use a more specialized way of doing a history management, less general, more adapted to our needs. With no Envers, the problem could be solved with a simple 3-step update using sql commands.

how is the collection mapped? As a set, bag or list? If you're using list - do you need a "stepno" at all on the child object? — Roger, Feb 07 '13 at 16:36
You are right @JokoFacile , seems like it is not possible without using specific SQL statement http://stackoverflow.com/q/1766057/1236044 — jbl, Feb 07 '13 at 22:25
@Roger: It's mapped as a list, sorry forgot posting that part. But I do not see what makes the difference according to having a database column for StepNo, it would just not need it in the object model. But the exception occours from the database, not the object model. But I will take a closer look into that. — JokoFacile, Feb 08 '13 at 07:34
@jbl: ... but the specific SQL using CreateQuery will work around Envers, therefore that's not working either, that has been one part of the problem. — JokoFacile, Feb 08 '13 at 07:35
If you are using sqlserver, I'm afraid it does not support deferrable constraints. One thing could be to that in two transactions : first transaction increments all item index with the size of the collection. Second transaction putting the items back and in the right place. But that seems quite artificial, performance greedy, will generate noise in your versionning system... — jbl, Feb 08 '13 at 08:35

score 0 · Accepted Answer · answered Feb 08 '13 at 07:06

0

If your database supports deferrable constraints I would use them.

answered Feb 08 '13 at 07:06

cremor

6,669
1
29
72

Thanks very much, this really seems to be the way to go to get everything working and maintain the checking on the database. Is there a possibility to make NHibernate generate the contraint as a deferrable constraint? – JokoFacile Feb 08 '13 at 07:36
@JokoFacile I don't think so. But it should be possible by using a ``: http://nhforge.org/doc/nh/en/index.html#mapping-database-object – cremor Feb 08 '13 at 07:40
For a quick test, this seems to work, but only with Oracle, SQLite is used for development purpose upto now and is not supporting deferrable unique key, only deferrable foreign key constraints. But it would at least be a solution for the final production system. I will leave the question open a little bit more, maybe an other idea comes up ... – JokoFacile Feb 08 '13 at 08:35

How to swap unique, ordering column entries with NHibernate and Envers?

1 Answers1