Using 64 bit primary keys with migrations

sanj (Sanjay Vakil) — Wed, 15 Aug 2007 13:23:00 GMT

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",

.

.

.

DZone Snippets: Sanj's Code Snippets

Using 64 bit primary keys with migrations