Never been to DZone Snippets before?

Snippets is a public source code repository. Easily build up your personal collection of code snippets, categorize them with tags / keywords, and share them with the world

Using 64 bit primary keys with migrations (See related posts)

I've got an application which may need more IDs than the standard 32 bit ones typically provide. MySQL has a BIGINT variable type, but it is a little tricky to convince Rails and Migrations to use it. The instructions are for MySQL, but should be easily transferable to other DBs.

1. Monkeypatch ActiveRecord::ConnectionAdapters::MysqlAdapter#native_database_types by appending it to config/environment.rb and add two new types. I've named them int64 and int64_pk

class ActiveRecord::ConnectionAdapters::MysqlAdapter
  def native_database_types #:nodoc:
    {
      :primary_key => "int(11) DEFAULT NULL auto_increment PRIMARY KEY",
      :int64_pk    => "bigint DEFAULT NULL auto_increment PRIMARY KEY",
      :int64       => { :name => "bigint" },
      :string      => { :name => "varchar", :limit => 255 },
      :text        => { :name => "text" },
      :integer     => { :name => "int", :limit => 11 },
      :float       => { :name => "float" },
      :decimal     => { :name => "decimal" },
      :datetime    => { :name => "datetime" },
      :timestamp   => { :name => "datetime" },
      :time        => { :name => "time" },
      :date        => { :name => "date" },
      :binary      => { :name => "blob" },
      :boolean     => { :name => "tinyint", :limit => 1 },
    }
  end
end


2. In the table creation migration, create the table WITHOUT a primary key column, and then add the column manually, referencing int64_pk above:

create_table :slices, :id => false do |t|
       t.column :cheese_id, :integer
       t.column :plant, :string
       t.column :date, :datetime
     end
    add_column :events, :id, :int64_pk
end



3. In tables referencing this one, mark the foreign_id fields as int64:

create_table :fondues do |t|
      t.column :cheese_id, :int64
      t.column :party_id, :integer
      t.column :kirsch_id, :integer
end



4. It turns out that the c-based mysql bindings do NOT do let you set primary keys via the sequence because it coerces the
insert_id into 32 bits at mysql.c:532. There are two way to fix this:

a. Use the pure-ruby gem (ie, remove the c-gem)

b. Patch and rebuild the c-gem by changing line 352 (in version 2.7):

   return INT2NUM(mysql_insert_id(GetHandler(obj)));


Extending this to use LL2NUM solves the issue:

   return LL2NUM(mysql_insert_id(GetHandler(obj)));



5. One last thing, in environment.rb:

  # Use SQL instead of Active Record's schema dumper when creating the test database.
  # This is necessary if your schema can't be completely dumped by the schema dumper,
  # like if you have constraints or database-specific column types
  config.active_record.schema_format = :sql


We need to deal with sql, rather than ruby, schemas.

----------------------

Note that I've decided that ONLY the cheese table needs 64bit IDs -- other references are still integers. If you wanted ALL primary keys to be 64 bits, you could have the monkeypatch look like:

class ActiveRecord::ConnectionAdapters::MysqlAdapter
  def native_database_types #:nodoc:
    {
      :primary_key    => "bigint DEFAULT NULL auto_increment PRIMARY KEY",
.
.
.


Comments on this post

dinsley posts on Sep 11, 2007 at 19:56
You can also pass native column types into the column call of your migrations, like the following:

  create_table 'list', :force => true do |t|
    t.column :position, 'integer unsigned' 
  end


I had a similar problem awhile ago where we had to set the id's of a lot of our tables to use unsigned integers, including the primary keys. I wrote a plugin and threw it on my blog, more technical details are available here:

http://www.danielinsley.com/articles/2007/06/10/rails-plugin-primary_key_fix

It also fixes the schema dumper to report the correct column type as well.
smackjer posts on Sep 19, 2007 at 14:54
There's a plugin called mysql_bigint that takes care of this. See http://svn.northpub.com/plugins/mysql_bigint/. The plugin also allows use of MySQL's smaller integer types.

You need to create an account or log in to post comments to this site.


Click here to browse all 6088 code snippets

Related Posts