2

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.

Will Marcouiller
  • 23,773
  • 22
  • 96
  • 162

3 Answers3

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.

Community
  • 1
  • 1
Will Marcouiller
  • 23,773
  • 22
  • 96
  • 162
  • A bit more than I need, but nice info none the less. The HiLo stuff in our app is all being managed transparently by NHibernate, so expanding it to this level isn't something we'll do, at least until we're ready to write our own IIdentifierGenerator implementation. –  Jun 11 '10 at 18:58
2

Have you tried specifying the schema with the table name on all generators (including the ones already in the dbo schema? I.e.

<param name="table">dbo.hibernate_unique_key</param>

The hilo generator looks for a '.' in the table name, and qualifies it (with schema) only if one isn't there.

Keith Robertson
  • 791
  • 7
  • 13
  • This project is rolled out now, so I won't go changing it but the next project that we need Id generation on, I'll give this suggestion a shot. – Kirk Clawson Mar 04 '11 at 14:13
  • Kirk, Did you have a chance to verify this suggestion? If it worked, please vote it up; I could use the reputation! :-) – Keith Robertson Jun 24 '11 at 13:44
1

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

Diego Mijelshon
  • 52,548
  • 16
  • 116
  • 154
  • This is what we're doing at the moment just to get the app working, but the "preferred" NH pattern (and the one I'd *like* to use) is a single key table, with multiple rows (one per primary entity), using a filter in the hilo generator mapping. –  Jun 11 '10 at 18:54
  • Who told you that's the *preferred* NH pattern? I'd say it's not very important, but the most performing solution (in databases without sequences) is a table per generator (entity) – Diego Mijelshon Jun 12 '10 at 00:02
  • hm. I guess it was just an assumption on my part that one table is > multiple tables. Kind of a DB DRY principal if you will. Fair enough, since this is how we already have it set up, we'll just move forward with this method. –  Jun 14 '10 at 13:01