MySql Lovin' Part 2: Add New Data Types To Migrations

This is a follow up to last week's MySql Lovin Part 1.

You will eventually want to use MySql specific data types. While arbitrary SQL statements can be executed in migrations, an alternative is to extend the MySql adapter to support new types of columns.

Two examples of columns I use are:

• BIGINT. Rails 2.1 attempts to use the :limit option to predict you want a BIGINT, but there seems to be confusion in Rails source about what the N means when you write BIGINT(N). Nevertheless, BIGINT always has the same maximum and minimum limit, and if I want to use one, I like to be explicit.


• Primary Key without auto increment. Sometimes I have enums defined in Ruby code, and want to explicitly define their value when inserting into the database, rather than having their id value automatically incremented by the database.

Let's add support for these columns, so that they can be used with create_table. First, have a look at native_database_types, defined in ActiveRecord::ConnectionAdapters::MysqlAdapter. Rails provides no facility to register more column types with an adapter, but that does not prevent merging them into the column type hash:

 1 module ActiveRecord
 2   module ConnectionAdapters
 3     class MysqlAdapter < AbstractAdapter
 4       alias :original_native_database_types :native_database_types
 5 
 6       def native_database_types
 7         original_native_database_types.update(
 8             :big_integer  => { :name => "bigint", :limit => 21 },
 9             :manual_pk    => "int(11) NOT NULL PRIMARY KEY")
10       end
11     end
12   end
13 end

With the new types added, they can be used in migrations:

1 create_table :foo, :id => false do |t|
2   t.column :id, :manual_pk
3   t.column :bytes, :big_integer
4 end

Shorthand Columns
To make these migrations a bit more appealing, extend ActiveRecord::ConnectionAdapters::TableDefinition to support shorthanded column names:

 1 module ActiveRecord
 2   module ConnectionAdapters
 3     class TableDefinition
 4       %w(big_integer manual_pk).each do |column_type|
 5         class_eval <<-EOV
 6           def #{column_type}(*args)
 7             options = args.extract_options!
 8             column_names = args
 9 
10             column_names.each { |name| column(name, '#{column_type}', options) }
11           end
12         EOV
13       end
14     end
15   end
16 end

Refactor the migration:

1 create_table :foo, :id => false do |t|
2   t.manual_pk :id
3   t.big_integer :bytes
4 end

Adding Native Support to Rails
It would be nifty if Rails allowed you to perform the above in an initializer (very similar to how you register mime_types). For example:

1 ActiveRecord::ConnectionAdapters::MysqlAdapter.register :big_integer, { :name => "bigint", :limit => 21 }
2 ActiveRecord::ConnectionAdapters::MysqlAdapter.register :manual_pk, "int(11) NOT NULL PRIMARY KEY"

I might follow this up with a Rails patch, but need to think about it more. Also, the weather is too nice for me to continue blogging.