Platinum Partner

Using 64 Bit Primary Keys With Migrations

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

{{ tag }}, {{tag}},

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}
{{ parent.authors[0].realName || parent.author}}

{{ parent.authors[0].tagline || parent.tagline }}

{{ parent.views }} ViewsClicks
Tweet

{{parent.nComments}}