email:

i am trying to do some testing with sequence identifier generators, so i was going to use HSQLDB, since it now supports sequences (and since oracle on my laptop eats up 300M of my memory just by cranking it up). so i notice that the hsqldb dialect does not support sequences. so my question is can you give me a 10-line idiot guide on what i'd do if i want to 'upgrade' the dialect for sequences?

while i have your eyes, i'll pose the underlying question. i am mapping an oracle database that is being replicated. to guarantee uniqueness, the dba has a scheme where the PK for every table is a composite of a surrogate key )from a sequence) and a "site_id" (which is the name of the site where the record was first created). from what i can tell, from what you taught me (so if i am wrong some of the burden is obviously on you), i have a couple of choices (and trying to convince him to partition the id space has been tried and failed):

1) use "assigned" for the id generation. for this i'd have to query the oracle db for the next sequence and build my PK from that - i don't like this b/c it puts some JDBC code in my nice hibernate ORM
2) roll my own IdentifierGenerator, which would basically extend the hibernate SequenceGenerator and add my site thingy.

so what i am really trying to do is to implement #2, which is why i need to have HSQLDB support sequences (i don't think derby does sequences).

Regarding "extending" the dialect, simply take a look at some existing dialects which do support sequences (i.e., Oracle). Essentially override supportsSequences(), getSequenceNextValueString(), getCreateSequenceString(), etc...


If the site_id and the surrogate key are seperate columns, then this is a composite key, which in Hibernate must be "assigned" (because composite keys are natural keys be definition). IdentifierGenerators are not available for composite keys.

Now another option, if you know that the surrogate portion is unique (and can place a unisue constraint on it), is to simply map the surrogate key portion as the PK in Hibernate (essentially the site_id would just be a non-null column).

when you say that IdentifierGenerators don't support composite keys do you mean the IdentifierGenerators hibernate supplies? because the interface requires the generate method to just return a serializable, which could easily be a composite PK object. and that's what i (as the original question poser) was planning on doing. are you saying i cannot write an IdentifierGenerator that returns an arbitrary serializable object (which will happen to be a composite key object)?

Sure, IdentifierGenerator can return whatever you want it to return. But...

<composite-id/>, which you would use to map this beast, does not accept a IdentifierGenerator.


vs.

i see. but i could use an id class that is a composite, but not tell hibernate that it was a composite, yes? so for hibernate it's just my id class...

okay, i see why that won't work (i need to specify a single column for a regular ID mapping).

so i guess i am generating my own keys (the id itself will not be guaranteed to be unique, since each server probably has the same sequence starting point).

how hard and useful would it be to have composite IDs accept IdentifierGenerators? is that something i could put on a wish list or something? or is it a bad idea and i just don't see it?

Try and develop a patch.
It'll solve your concerns
and give concrete feedbacks to the community :-)

well, from what i can tell, the patch is 11 keystrokes (counting the space). i just added "generator" to the composite-id ELEMENT in the mapping dtd and my composite id generator worked fine and dandy. kind of exciting, in a geeky sort of way. obviously, i will test this more and if happy submit it back to you.

thanks!

This is a semi-related post. Excuse me if it does not belong here. It is to do with generated SQL for sequences in HSQLDB. Hibernate generates:

Hibernate: select next value for ARSQPROC from dual_ARSQPROC

which fails. I would have expected the following: (which would work)

select next value for ARSQPROC from SYSTEM_SEQUENCES

but seeing as there are no posts regarding this topic I have setup something incorrectly? (I am using sequences so that my implementation can use either HSQLDB or Oracle).

FYI, I am using: org.hibernate.dialect.HSQLDialect. Hibernate v3.0.4. HSQLDB v1.7.3. I created the sequence with: CREATE SEQUENCE ARSQPROC START WITH 0;