2

How do I instruct Nhibernate to generate sequential one step primary keys, like the sql generated ones?

The current HiLo algorithm generates keys like 4001 then 5010, 6089 etc. I understand that this is to manage multiple app servers etc. But I don’t have that problem.
I need nhibernate to pick up the highest record set value during startup (say 15) and then generate the next record with primary key 16(very much like generated id’s from sql’s side).

Quintin Par
  • 15,862
  • 27
  • 93
  • 146
  • Why not use db-generated id's? – Mattias Jakobsson May 20 '10 at 12:48
  • @Mattias http://fabiomaulo.blogspot.com/2009/02/nh210-generators-behavior-explained.html – Quintin Par May 20 '10 at 13:05
  • I didn't read the whole article, so I'm sorry if I missed the point of it. But the common reason to use hilo is so that you reduce network traffic. If you do want your id's in order you will have to go to the db each time anyway, so no real point in using it instead of db-generated id's. It could be done with hilo (just set the maxlo to 0). But it doesn't make any sense as you will get a lot of traffic. The issue isn't multiple apps using the same db. It is multiple users using the same app. If both you and I tries to save something at the same time then we will have a conflict. – Mattias Jakobsson May 20 '10 at 13:21
  • @Mattias isn't there a generator that pulls up the max once during startup and then maintains the max in memory, incrementing it every time an assignment happens. – Quintin Par May 20 '10 at 16:13
  • Yes, but it only reserves as many id's as the maxlo you have set. So if you set it to 0 it will only reserve 1 id and go to the db as soon as you need more. That is why you get the non sequential id's when you use hilo, as you reserve a number of id's but you only use a few of them. It can't be incresed in memory and not saved to the db as that would mean trouble when there are more then one user. – Mattias Jakobsson May 20 '10 at 19:24

2 Answers2

1

Why do you need/expect NHibernate to do this for you?

It's hard for NHibernate to provide a generic solution for scenarios like this as the requirements can vary ever so slightly, but since you exactly know your particular constraints, it should be relatively straight-forward for you to provide your own solution (using manually assigned ids).

On application startup, query the database and get the current max id value. Increment that value every time you do an insert.

Michael Maddox
  • 12,331
  • 5
  • 38
  • 40
  • @Quintin - That blog post doesn't really tell me anything I didn't already know. I'm not sure why you chose that as a comment to my answer? – Michael Maddox May 20 '10 at 19:39
1

Create table:

CREATE TABLE `seq` (
  `ID` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
  `HI` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `seq` VALUES ('COMMENT', '0');
INSERT INTO `seq` VALUES ('POST', '0');
INSERT INTO `seq` VALUES ('USER', '0');

Add mappings like this with FluentNHbiernate:

    public class Comment_Map : ClassMap<Comment>
    {
        public Comment_Map()
        {
            Table("COMMENT");

            Id(x => x.ID, "ID").GeneratedBy.HiLo("SEQ", "HI", "0", o => o.AddParam("where", "ID = 'COMMENT'"));
        }
     }
dmonlord
  • 1,370
  • 9
  • 16
  • @dmonlord, Having maxLo set to 0 pretty much removes the advantage of using hilo in the first place. You will end up with a lot of network traffic this way and you are probably a lot better of using db-generated id's. – Mattias Jakobsson May 20 '10 at 13:56
  • I know, but that's what he wants. There probably isn't any good alternative to this if you don't want to implement your own generator. – dmonlord May 20 '10 at 19:13
  • @dmonlord, I know there isn't a alternative. I just pointed that out as the reason you use hilo (or any client generated id) is because you want less network traffic. With your solution you will end up with more network traffic. – Mattias Jakobsson May 20 '10 at 19:26