h1. Composite Primary Keys

h1. → Ruby on Rails

h1. → ActiveRecords

h2. What

Ruby on Rails does not support composite primary keys. This free software is an extension 
to the database layer of Rails - "ActiveRecords":http://wiki.rubyonrails.com/rails/pages/ActiveRecord - to support composite primary keys as transparently as possible.

Any Ruby script using ActiveRecords can use Composite Primary Keys with this library.

h2. Installing

<pre syntax="ruby">sudo gem install composite_primary_keys</pre>

Rails: Add the following to the bottom of your <code>environment.rb</code> file

<pre syntax="ruby">require 'composite_primary_keys'</pre>

Ruby scripts: Add the following to the top of your script

<pre syntax="ruby">require 'rubygems'
require 'composite_primary_keys'</pre>

h2. The basics

A model with composite primary keys would look like...

<pre syntax="ruby">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</pre>

A model associated with a composite key model would be defined like...

<pre syntax="ruby">class MembershipStatus < ActiveRecord::Base
  belongs_to :membership, :foreign_key => [:user_id, :group_id]
end</pre>

That is, associations can include composite keys too. Nice.

h2. 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.

<pre syntax="ruby">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"</pre>

Now we want to be able to find instances using the same syntax we always use for ActiveRecords...

<pre syntax="ruby">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"}></pre>

Using "Ruby on Rails":http://www.rubyonrails.org? You'll want to your url_for helpers
to convert composite keys into strings and back again...

<pre syntax="ruby">Membership.find(:first).to_param # => "1,1"</pre>

And then use the string id within your controller to find the object again

<pre syntax="ruby">params[:id] # => '1,1'
Membership.find(params[:id])
=> <Membership:0x3904288 @attributes={"user_id"=>"1", "group_id"=>"1"}></pre>

That is, an ActiveRecord supporting composite keys behaves transparently
throughout your application. Just like a normal ActiveRecord.


h2. Other tricks

h3. Pass a list of composite ids to the <code>#find</code> method

<pre syntax="ruby">Membership.find [1,1], [2,1]
=> [
  <Membership:0x394ade8 @attributes={"user_id"=>"1", "group_id"=>"1"}>, 
  <Membership:0x394ada0 @attributes={"user_id"=>"2", "group_id"=>"1"}>
]</pre>

Perform <code>#count</code> operations

<pre syntax="ruby">MembershipStatus.find(:first).memberships.count # => 1</pre>

h3. Routes with Rails

From Pete Sumskas:

<blockquote>
	I ran into one problem that I didn't see mentioned on "this list":http://groups.google.com/group/compositekeys - 
	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:
	
	<pre>/controller/show/123000,Bu70</pre>

	for a two-field composite PK. The default routing would not match that,
	so after working out how to do the routing I added:
	
	<pre syntax="ruby">map.connect ':controller/:action/:id', :id => /\w+(,\w+)*/</pre>
	
	to my <code>route.rb</code> file.
	
</blockquote>

<a name="dbs"></a>

h2. Which databases?


A suite of unit tests have been run on the following databases supported by ActiveRecord:

|_.Database|_.Test Success|_.User feedback|
|mysql     |<span class=success>YES</span>|<span class=success>YES</span> ("Yes!":mailto:compositekeys@googlegroups.com?subject=Mysql+is+working or "No...":mailto:compositekeys@googlegroups.com?subject=Mysql+is+failing)|
|sqlite3   |<span class=success>YES</span>|<span class=success>YES</span> ("Yes!":mailto:compositekeys@googlegroups.com?subject=Sqlite3+is+working or "No...":mailto:compositekeys@googlegroups.com?subject=Sqlite3+is+failing)|
|postgresql|<span class=success>YES</span>|<span class=success>YES</span> ("Yes!":mailto:compositekeys@googlegroups.com?subject=Postgresql+is+working or "No...":mailto:compositekeys@googlegroups.com?subject=Postgresql+is+failing)|
|oracle    |<span class=success>YES</span>|<span class=success>YES</span> ("Yes!":mailto:compositekeys@googlegroups.com?subject=Oracle+is+working or "No...":mailto:compositekeys@googlegroups.com?subject=Oracle+is+failing)|
|sqlserver |<span class=unknown>???</span> ("I can help":mailto:compositekeys@googlegroups.com?subject=Help+with+SQLServer)|<span class=unknown>???</span> ("Yes!":mailto:compositekeys@googlegroups.com?subject=SQLServer+is+working or "No...":mailto:compositekeys@googlegroups.com?subject=SQLServer+is+failing)|
|db2       |<span class=unknown>???</span> ("I can help":mailto:compositekeys@googlegroups.com?subject=Help+with+DB2)|<span class=unknown>???</span> ("Yes!":mailto:compositekeys@googlegroups.com?subject=DB2+is+working or "No...":mailto:compositekeys@googlegroups.com?subject=DB2+is+failing)|
|firebird  |<span class=unknown>???</span> ("I can help":mailto:compositekeys@googlegroups.com?subject=Help+with+Firebird)|<span class=unknown>???</span> ("Yes!":mailto:compositekeys@googlegroups.com?subject=Firebird+is+working or "No...":mailto:compositekeys@googlegroups.com?subject=Firebird+is+failing)|
|sybase    |<span class=unknown>???</span> ("I can help":mailto:compositekeys@googlegroups.com?subject=Help+with+Sybase)|<span class=unknown>???</span> ("Yes!":mailto:compositekeys@googlegroups.com?subject=Sybase+is+working or "No...":mailto:compositekeys@googlegroups.com?subject=Sybase+is+failing)|
|openbase  |<span class=unknown>???</span> ("I can help":mailto:compositekeys@googlegroups.com?subject=Help+with+Openbase)|<span class=unknown>???</span> ("Yes!":mailto:compositekeys@googlegroups.com?subject=Openbase+is+working or "No...":mailto:compositekeys@googlegroups.com?subject=Openbase+is+failing)|
|frontbase |<span class=unknown>???</span> ("I can help":mailto:compositekeys@googlegroups.com?subject=Help+with+Frontbase)|<span class=unknown>???</span> ("Yes!":mailto:compositekeys@googlegroups.com?subject=Frontbase+is+working or "No...":mailto:compositekeys@googlegroups.com?subject=Frontbase+is+failing)|

h2. Dr Nic's Blog

"http://www.drnicwilliams.com":http://www.drnicwilliams.com - for future announcements and
other stories and things.

h2. Forum

"http://groups.google.com/group/compositekeys":http://groups.google.com/group/compositekeys

h2. How to submit patches

Read the "8 steps for fixing other people's code":http://drnicwilliams.com/2007/06/01/8-steps-for-fixing-other-peoples-code/ and for section "8b: Submit patch to Google Groups":http://drnicwilliams.com/2007/06/01/8-steps-for-fixing-other-peoples-code/#8b-google-groups, use the Google Group above.


The source for this project is available via git. You can "browse and/or fork the source":http://github.com/drnic/composite_primary_keys/tree/master, or to clone the project locally:
  
<pre>git clone git://github.com/drnic/composite_primary_keys.git</pre>

h2. Licence

This code is free to use under the terms of the MIT licence. 

h2. Contact

Comments are welcome. Send an email to "Dr Nic Williams":mailto:drnicwilliams@gmail.com.