This week's book giveaway is in the Agile and Other Processes forum.
We're giving away four copies of The Little Book of Impediments (e-book only) and have Tom Perry on-line!
See this thread for details.
Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

[Hibernate] composite-id and table-per-hierarchy

 
Lasse Koskela
author
Sheriff
Posts: 11962
5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Regarding this question in the Hibernate forum, does anyone here know whether Hibernate supports the use of a composite-id when one of the composite columns is also a discriminator column?
 
David Harkness
Ranch Hand
Posts: 1646
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'm shooting from the hip here since I haven't used composite keys with Hibernate, but the many-to-one mapping looks suspect.Doesn't the CANDIDATE table have two columns that make up the FK to CMLOOKUPS: LOOKUP and TABLENAME (or some other names)? Or do you have an alternate, single-column key in CMLOOKUPS that you use for joining to CANDIDATE? I don't even know if Hibernate supports that, but I know you could model it that way in SQL. Of course, I'd flip the AK and PK around in that case, but I don't know your setup.

Another project where I work is using composite IDs, but I don't think they're using subclassing with domain objects. I'll check, however, when I get in tomorrow (PST here). Hopefully someone can chime in before then, though.
[ January 28, 2005: Message edited by: David Harkness ]
 
Lasse Koskela
author
Sheriff
Posts: 11962
5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by David Harkness:
Doesn't the CANDIDATE table have two columns that make up the FK to CMLOOKUPS: LOOKUP and TABLENAME (or some other names)? Or do you have an alternate, single-column key in CMLOOKUPS that you use for joining to CANDIDATE? I don't even know if Hibernate supports that, but I know you could model it that way in SQL. Of course, I'd flip the AK and PK around in that case, but I don't know your setup.

Thanks, David.

We only have one column in the CANDIDATE table to make up the FK -- the other part of the two-part composite ID should be the discriminator-value for the specific type of Lookup, i.e. "AppointedRole". The problem is that we can't change the database schema and Hibernate doesn't want to recognize that we're referring to a composite-id where the other half can be deduced from the subclass type itself.
 
David Harkness
Ranch Hand
Posts: 1646
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hmm, so if I understand correctly, the primary key of the CMLOOKUPS table is (LOOKUP, TABLENAME), and the foreign key to it in the CANDIDATE table is (APPOINTEDROLE). Oh I see, logically CANDIDATES only ever point to CMLOOKUPS whose TABLENAME is "AppointedRole", so you've omitted the TABLENAME half of the PK from the FK. Is this correct?

But doesn't this mean that your database doesn't actually have an FK constraint? Or is there a way with the DB you're using to specify a fixed value for part of an FK? I've never seen that, but again I've always had the luxury of designing the database model and using single-column surrogate keys.

The more I read forum posts, the more I realize how truly lucky I have been! Is there a patron saint of relational models watching over me?

Anyway, the other team here doesn't use subclasses, so I'm afraid I'm no help.
 
Lasse Koskela
author
Sheriff
Posts: 11962
5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by David Harkness:
Hmm, so if I understand correctly, the primary key of the CMLOOKUPS table is (LOOKUP, TABLENAME), and the foreign key to it in the CANDIDATE table is (APPOINTEDROLE). Oh I see, logically CANDIDATES only ever point to CMLOOKUPS whose TABLENAME is "AppointedRole", so you've omitted the TABLENAME half of the PK from the FK. Is this correct?

Correct.

Originally posted by David Harkness:
But doesn't this mean that your database doesn't actually have an FK constraint?
Yes, that's exactly what it means. The whole database consists of some 50 tables with not a single foreign key constraint! (it's the schema of a certain very f*ed up product)

Originally posted by David Harkness:
I've always had the luxury of designing the database model and using single-column surrogate keys. ... The more I read forum posts, the more I realize how truly lucky I have been!
I know. I've also been pretty much on the greenfield side when it comes to database schemas. This particular project has been a rather nightmare'ish experience in that you can find practically any classic s/w engineering/design screw-up around any corner you decide to turn...
 
Lasse Koskela
author
Sheriff
Posts: 11962
5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Oh, we got the permission to add a new column into the CANDIDATE table to hold the fixed value of "AppointedRole" so we could make Hibernate understand the schema. Not a pretty solution, but it seems like either Hibernate doesn't support this kind of "smart" composite-id association or they just decided not to tell anyone how to configure such a beast
 
David Harkness
Ranch Hand
Posts: 1646
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Lasse Koskela:
it seems like either Hibernate doesn't support this kind of "smart" composite-id association or they just decided not to tell anyone how to configure such a beast
Looking at the DTD for the <many-to-one> and <column> elements, I'm guessing the former. However, if you're feeling particularly brave and generous you could try wrangling it in there. My guess is that you'd need to add a "value='...'" attribute to the <column> element. How to have Hibernate then use that attribute ... well, probably not as simple as altering the DTD.

Good job on getting the clearance to alter the schema, though. I bet that is a huge relief.
 
Lasse Koskela
author
Sheriff
Posts: 11962
5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by David Harkness:
I bet that is a huge relief.

You can say that again.

I'll try to peek inside Hibernate at some point to see if I can figure out how to add constant values or the real solution, i.e. make Hibernate realize that the other class is part of a table-per-class-hierarchy and that it should use the discriminator-value if that's the column that's missing from the composite. We'll see...
 
Lasse Koskela
author
Sheriff
Posts: 11962
5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
For your information, Hibernate has an undocumented feature for specifying fixed/literal values as follows:

I probably don't need to say that I'm pretty glad Gavin tipped us about this feature... The alternatives would not have been pretty.
 
David Harkness
Ranch Hand
Posts: 1646
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
So do I understand you correctly that this solves your issue without having to add the column? Thanks for the pointer, that just might come in handy!
 
Lasse Koskela
author
Sheriff
Posts: 11962
5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by David Harkness:
So do I understand you correctly that this solves your issue without having to add the column?

Yes, it does!

Although the best alternative would've been to not have the composite-id's in the first place...
 
David Harkness
Ranch Hand
Posts: 1646
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Lasse Koskela:
Although the best alternative would've been to not have the composite-id's in the first place...
I think that should be plastered on every page of hibernate.org.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic