Composite Primary Keys
+→ Ruby on Rails
+→ ActiveRecords
+What
+Ruby on Rails does not support composite primary keys. This free software is an extension
+to the database layer of Rails – ActiveRecords – to support composite primary keys as transparently as possible.
Any Ruby script using ActiveRecords can use Composite Primary Keys with this library.
+Installing
+sudo gem install composite_primary_keys+
Rails: Add the following to the bottom of your environment.rb
file
require 'composite_primary_keys'+
Ruby scripts: Add the following to the top of your script
+require 'rubygems' +require 'composite_primary_keys'+
The basics
+A model with composite primary keys would look like…
+class Membership < ActiveRecord::Base + # set_primary_keys *keys - turns on composite key functionality + set_primary_keys :user_id, :group_id + belongs_to :user + belongs_to :group + has_many :statuses, :class_name => 'MembershipStatus', :foreign_key => [:user_id, :group_id] +end+
A model associated with a composite key model would be defined like…
+class MembershipStatus < ActiveRecord::Base + belongs_to :membership, :foreign_key => [:user_id, :group_id] +end+
That is, associations can include composite keys too. Nice.
+Demonstration of usage
+Once you’ve created your models to specify composite primary keys (such as the Membership class) and associations (such as MembershipStatus#membership), you can uses them like any normal model with associations.
+But first, lets check out our primary keys.
+MembershipStatus.primary_key # => "id" # normal single key +Membership.primary_key # => [:user_id, :group_id] # composite keys +Membership.primary_key.to_s # => "user_id,group_id"+
Now we want to be able to find instances using the same syntax we always use for ActiveRecords…
+MembershipStatus.find(1) # single id returns single instance +=> <MembershipStatus:0x392a8c8 @attributes={"id"=>"1", "status"=>"Active"}> +Membership.find(1,1) # composite ids returns single instance +=> <Membership:0x39218b0 @attributes={"user_id"=>"1", "group_id"=>"1"}>+
Using Ruby on Rails? You’ll want to your url_for helpers
+to convert composite keys into strings and back again…
Membership.find(:first).to_param # => "1,1"+
And then use the string id within your controller to find the object again
+params[:id] # => '1,1' +Membership.find(params[:id]) +=> <Membership:0x3904288 @attributes={"user_id"=>"1", "group_id"=>"1"}>+
That is, an ActiveRecord supporting composite keys behaves transparently
+throughout your application. Just like a normal ActiveRecord.
Other tricks
+Pass a list of composite ids to the #find
method
+Membership.find [1,1], [2,1] +=> [ + <Membership:0x394ade8 @attributes={"user_id"=>"1", "group_id"=>"1"}>, + <Membership:0x394ada0 @attributes={"user_id"=>"2", "group_id"=>"1"}> +]+
Perform #count
operations
MembershipStatus.find(:first).memberships.count # => 1+
Routes with Rails
+From Pete Sumskas:
+++ +I ran into one problem that I didn’t see mentioned on this list –
+
+ and I didn’t see any information about what I should do to address it in the
+ documentation (might have missed it).The problem was that the urls being generated for a ‘show’ action (for
+ example) had a syntax like:
+
+/controller/show/123000,Bu70+for a two-field composite PK. The default routing would not match that,
+ so after working out how to do the routing I added:
+
+map.connect ':controller/:action/:id', :id => /\w+(,\w+)*/
+
+ to myroute.rb
file. +
Which databases?
+A suite of unit tests have been run on the following databases supported by ActiveRecord:
+Database | +Test Success | +User feedback | +
---|---|---|
mysql | +YES | +YES (Yes! or No…) | +
sqlite3 | +YES | +YES (Yes! or No…) | +
postgresql | +YES | +YES (Yes! or No…) | +
oracle | +YES | +YES (Yes! or No…) | +
sqlserver | +??? (I can help) | +??? (Yes! or No…) | +
db2 | +??? (I can help) | +??? (Yes! or No…) | +
firebird | +??? (I can help) | +??? (Yes! or No…) | +
sybase | +??? (I can help) | +??? (Yes! or No…) | +
openbase | +??? (I can help) | +??? (Yes! or No…) | +
frontbase | +??? (I can help) | +??? (Yes! or No…) | +
Dr Nic’s Blog
+http://www.drnicwilliams.com – for future announcements and
+other stories and things.
Forum
+http://groups.google.com/group/compositekeys
+How to submit patches
+Read the 8 steps for fixing other people’s code and for section 8b: Submit patch to Google Groups, use the Google Group above.
+The source for this project is available via git. You can browse and/or fork the source, or to clone the project locally:
+
+
git clone git://github.com/drnic/composite_primary_keys.git+
Licence
+This code is free to use under the terms of the MIT licence.
+Contact
+Comments are welcome. Send an email to Dr Nic Williams.
+
+ Dr Nic, 25th October 2008
+ Theme extended from Paul Battley
+