Composite Migrations — err.the

“Composite Migrations”

– PJ on August 16, 2006
Dr. Nic started a fire with the composite_primary_keys gem and he doesn’t even know it.

It was created for maintaining legacy database schemas that use composite primary keys, but not only do we encourage their use, we actually made more work for ourselves on Chow and Chowhound so we could use them.

But, arguing their use isn’t the point of this article. If you’d like to use composite primary keys, you don’t have to go out of your way anymore.

The gem gets you most of the way, but I was getting tired of putting execute statements in my migrations to create tables that use composite primary keys.

Wouldn’t it be great if you were able to do this?
```
create_table :index_queue, :primary_key => [:content_id, :content_type] do |t|
  t.column :content_id,   :integer
  t.column :content_type, :string
  t.column :action,       :string
end
```
Turns out you can with the following code:
```
ActiveRecord::ConnectionAdapters::ColumnDefinition.class_eval <<-'EOF'
  def to_sql
    if name.is_a? Array
      column_sql = "PRIMARY KEY (#{name.join(',')})"
    else
      column_sql = "#{base.quote_column_name(name)} #{type_to_sql(type.to_sym, limit)}"
      add_column_options!(column_sql, :null => null, :default => default)
    end
    column_sql
  end
EOF

ActiveRecord::ConnectionAdapters::ColumnDefinition.send(:alias_method, :to_s, :to_sql)
```
Piece of cake. Use it however you wish or install the composite_migrations plugin with the following:

$ ./script/plugin install \ svn://errtheblog.com/svn/plugins/composite_migrations

So, without opening up a flame war (I’m just genuinely curious), do you say you use composite keys on greenfield development? Why?

The short answer is yes. The explanation will be on my blog in order to keep this blog code-driven.

Cool. I did promise the CPK users migrations support, but then the Magic Models turned up, and now the Magic Announcements is absorbing my mind… :)

Could I integrated this into the gem and call it the “PJ Hyett & Co” release? Sounds very Wild Western!

They’re a match made in heaven, so by all means integrate away! I’ll update this post when you’re ready so people won’t unnecessarily install this plugin.

Here’s my longer explanation on the usage of composite primary keys

Thanks for the plugin. I got Rails to work on the DB2 for iSeries last week, and just now got Composite_Primary_Keys (CPK) to work (with 0.7.0 release). Now I want to create some new test databases, and since I love migrations, I will enthusiastically use your plugin.

Sorry, I’m not getting it; I’m new to this Ruby/Rails voodoo. Could you explain how to get this onto my system? I ran “gem install composite_primary_keys” and 0.7.1 was magically installed. But how do you actually make an application aware of it?

The codes snippet above (starting with ActiveRecord::ConnectionAdapters::ColumnDefinition.class_eval) doesn’t really tell me anything. Where would I put that?

And what is the plugin thing that you mention after that? Where would I find ./script/plugin, and how would I use it after installing it.

The composite keys syntax looks easy to add to a migration, once it’s actually installed.

Any chance we can get this to work with SQLite3? It appears that the order of the statements in the Create Table SQL IS important… you must define the columns BEFORE declaring a primary key.

Will, try adding the following to composite_migrations.rb:

 if ActiveRecord::Base.connection.adapter_name == 'SQLite'
   ActiveRecord::ConnectionAdapters::TableDefinition.class_eval <<-'EOF'
   def to_sql
     if @columns.first.to_s =~ /PRIMARY KEY/ && @columns.first.name.is_a?(Array)
       @columns.push(@columns.shift)
     end
     @columns * ', '
   end
 EOF
 end

There may be a better way to get this to work, but it gets SQLite3 to work correctly with composite primary keys.

Doesn’t seem to work with the latest rails, what a shame :(

Does it work with Rails 1.2 or not?

I got it to work. See my blog for details:

http://blog.dragonsharp.com/index.php/archive/making-composite-migrations-work-on-rails-12/

Where do you insert def to_sql method?

I think this is integrated into Dr Nic’s composite_primary_keys now