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
{
: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:
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
{
:primary_key => "bigint DEFAULT NULL auto_increment PRIMARY KEY",
.
.
.
