NHibernate HiLo generation and SQL 2005/8 Schemas

Question

I have an issue on my hands that I've spent several days searching for an answer to no avail...

We're using HiLo Id generation, and everything seems to be working fine, as long as the entity table is in the same schema as the hibernate_unique_key table.

The table structure is pretty simple. I have my hi value table in the db as dbo.hibernate_unique_key. Several entity table are also in the dbo schema, and they work without issue. Then we have tables under the "Contact" schema (such as Contact.Person and Contact.Address).

In the Person Mapping file:

<class name="Person" table="Person" schema="Contact">
<id name="Id" unsaved-value="0">
  <generator class="hilo">
    <param name="max_lo">100</param>
  </generator>
</id>
...

When I try to insert a Person entity, I get an error of "Invalid object name 'Contact.hibernate_unique_key'. That error is certainly clear enough. So I add:

<param name="schema">dbo</param>

to my mapping file/generator element. Now, when the SessionFactory is built, I get a "An item with the same key has already been added." error. So now I'm a bit stuck. I can't leave the HiLo generator without a schema, because it picks up the schema from the Class, and I can't specify the schema because it's already been added (presumably because it's my "default_schema" as identified in my XML cfg file).

Am I completely hosed here? Must I either

A) Keep all my tables in the dbo schema or

B) Create a separate HiLo Key table for each unique schema in the DB?

Neither of those scenarios is particularly palatable for my application, so I'm hoping that I can "fix" my mapping files to address this issue.

Answer 1

I don't think there's anything wrong with solution B. Behavior will be pretty much the same.

Answer 2

Only one such table per database should exist. Such data table should imply the following columns (let's call this table Parameters):

HiLoId
TableName
ParamName
HiLoAssigned

In addition to be used as a HiLo assignment data table, this could be used as a parameter table. As such, the ParamName field is required. This could contain data such as:

HiLoId | TableName | ParamName | HiLoAssigned
---------------------------------------------
   1   | Parameters| HiLoId    |       3
   2   | Customers | CustomerId|    9425 
   3   | Invoices  | InvoiceId |  134978

And when you need some other parameters, such as a parameter for a job that would prune your tables for history, then an age parameter for record could be inserted into it.

Well, I'm a little further in the subject than what you actually asked. Just sharing some additional thoughts in database design/architecture.

Take an eye out this question, and see my answer there. This might answer your question as well, and bring further information to this answer.