Wednesday, September 17, 2008

Composite Primary Keys (everyone's got an opinion)

Tonight I was playing around with some of the "cooler" things that Rails can do, and I thought I would try them out on our code.

Namely, there are two features that I'd like to use: nested_has_many_through, and named_scope.

This would allow us to write code like "taxon_concept.data_objects.images.visible" without having to go through a hairy find_by_sql, and it would allow us to more easily make changes to the DB (like, say, how we define "visible" for data_objects).  It would have made one of today's changes painless, too (long story).

It would also reduce the amount of code we have by a significant amount.  ...I like less code.

...The problem, though, is that DHH doesn't understand composite primary keys (CPKs) *at all*, and his framework reflects that.  Even with the CPK plugin, things don't work right.  ...That is, when you declare a CPK on a model, it seems that it expects every reference to it to contain all of the PKs.  I tried doing something like (pseudo-code): "DataObject.has_many :data_objects_taxa, :foreign_key => :data_object_id ; DataObjectsTaxon.has_many :data_objects, :foreign_key => :id" (DataObjectsTaxon has a CPK)... but this doesn't work: it STILL tries to pass in two key values.  The resulting queries are actually rather asinine (notice the 1,4 at the end), and fails miserably:

    SELECT * FROM `data_objects` WHERE ( = 1,4)

...I could delve into the CPK plugin code and try to resolve this, but I might be gone for days.  ; )

You can kind of get around this by using a habtm relationship.  ...But that only works for that one hop.  So I can get data_object.taxa, but I can't get data_object.resources, using this code:

    has_and_belongs_to_many :taxa
    has_many :resources, :through => :taxa

This throws "Unknown column 'taxa.data_object_id'"... and the docs even warn about this ("You can only use a :through query through a belongs_to or has_many association on the join model").


What I would like to do--knowing full well that this is the wrong thing to do, but bending to the will of Rails--is change our CPKs to Unique Indexes, and add autoincrements to those tables.  I could write the migrations to do such things without too much trouble, but I worry about the affect it would have on the related PHP code (I hope none, since the autoinc should just merrily do its thang).

Again: from a database level of understanding, this irks me.  Where we use CPKs is where they are uniquely identifying entries, and that's how its sputta be.  ...but Rails thinks otherwise.

...Unless someone knows a way to keep things as-is and get the has_many relationships working in both directions.  There my be some trick to it that I'm unaware of. That would be cool, too.

If we could get this to work, I think our code would be much, *much* more elegant (and thus, maintainable/extensible).  I'd be happy, assuming there isn't a noticeable performance hit (but from what people are saying, it's actually quite efficient).


And: ...apologies to California Pizza Kitchen (CPK).

No comments: