We build Web & Mobile Applications.
Ok, so I’m starting a new Rails application, I’ve spent some time refining my database, I’ve considered storage requirements and performance when choosing the data types for my fields and now I’m ready to create some migrations to implement my design.
And then I’m reminded that, lovely though Rails migrations are, there are two things that really bug me about them: they create signed primary keys and they don’t allow me to easily create unsigned integer columns.
It’s been discussed before, dismissed as an ‘uncommon requirement’ (probably because MySQL is, as far as I know, the only database that uses unsigned integers) and, as far as I can tell, not a lot more has happened. So, while sat out in the garden, enjoying a sunny Saturday afternoon, I grabbed edge Rails from GitHub and decided I’d see if I could do something about it (how’s that for geekyness?!)
Thanks to a snippet on DZone this was the easy bit:
module ActiveRecord
module ConnectionAdapters
class MysqlAdapter
def native_database_types_with_unsigned_primary_key
returning native_database_types_without_unsigned_primary_key do |types|
types[:primary_key] = 'int(11) UNSIGNED DEFAULT NULL auto_increment PRIMARY KEY'
end
end
alias_method_chain :native_database_types, :unsigned_primary_key
end
end
As you can see this extends the native_database_types method of the MysqlAdapter class which means that all other database adapters will be unaffected by this change.
If you use the ‘sexy’ references or belongs_to syntax in your migration then changing the primary key to unsigned can cause problems. For example if you try and create a foreign key constraint you might see an error like this:
ERROR 1005 (HY000): Can't create table 'test_development.#sql-160_31' (errno: 150)
This unhelpful error message occurs because the primary key is unsigned but the foreign key field created by references is signed. To fix this I subclassed the Table and TableDefinition classes to provide MySQL specific behaviour that defaults to creating unsigned foreign key columns. This also involved overriding the create_table and change_table methods of the MysqlAdapter.
Of course if you really want an signed reference you can simply pass :unsigned => false in your call to references.
module ActiveRecord
module ConnectionAdapters
class MysqlTable < Table
def references(*args)
options = args.extract_options!
polymorphic = options.delete(:polymorphic)
args.each do |col|
@base.add_column(@table_name, "#{col}_id", :integer, options.reverse_merge(:unsigned => true))
@base.add_column(@table_name, "#{col}_type", :string, polymorphic.is_a?(Hash) ? polymorphic : options) unless polymorphic.nil?
end
end
alias :belongs_to :references
end
class MysqlTableDefinition < TableDefinition
def references(*args)
options = args.extract_options!
polymorphic = options.delete(:polymorphic)
args.each do |col|
column("#{col}_id", :integer, options.reverse_merge(:unsigned => true))
column("#{col}_type", :string, polymorphic.is_a?(Hash) ? polymorphic : options) unless polymorphic.nil?
end
end
alias :belongs_to :references
end
class MysqlAdapter
def create_table(table_name, options = {}) #:nodoc:
options = options.reverse_merge(:options => "ENGINE=InnoDB")
table_definition = MysqlTableDefinition.new(self)
table_definition.primary_key(options[:primary_key] || Base.get_primary_key(table_name)) unless options[:id] == false
yield table_definition
if options[:force] && table_exists?(table_name)
drop_table(table_name, options)
end
create_sql = "CREATE#{' TEMPORARY' if options[:temporary]} TABLE "
create_sql << "#{quote_table_name(table_name)} ("
create_sql << table_definition.to_sql
create_sql << ") #{options[:options]}"
execute create_sql
end
def change_table(table_name)
yield MysqlTable.new(table_name, self)
end
end
end
end
This was the tricky bit, here’s the code:
module ActiveRecord
module ConnectionAdapters
class MysqlAdapter
def type_to_sql_with_unsigned(type, limit = nil, precision = nil, scale = nil, unsigned = false)
returning type_to_sql_without_unsigned(type, limit, precision, scale) do |sql|
sql << ' UNSIGNED' if unsigned && type.to_s == 'integer'
end
end
alias_method_chain :type_to_sql, :unsigned
def add_column(table_name, column_name, type, options = {})
add_column_sql = "ALTER TABLE #{quote_table_name(table_name)} ADD #{quote_column_name(column_name)} #{type_to_sql(type, options[:limit], options[:precision], options[:scale], options[:unsigned])}"
add_column_options!(add_column_sql, options)
execute(add_column_sql)
end
def change_column(table_name, column_name, type, options = {})
unless options_include_default?(options)
if column = columns(table_name).find { |c| c.name == column_name.to_s }
options[:default] = column.default
else
raise "No such column: #{table_name}.#{column_name}"
end
end
change_column_sql = "ALTER TABLE #{quote_table_name(table_name)} CHANGE #{quote_column_name(column_name)} #{quote_column_name(column_name)} #{type_to_sql(type, options[:limit], options[:precision], options[:scale], options[:unsigned])}"
add_column_options!(change_column_sql, options)
execute(change_column_sql)
end
end
class Column
attr_reader :unsigned
def initialize_with_unsigned(name, default, sql_type = nil, null = true)
initialize_without_unsigned(name, default, sql_type, null)
@unsigned = extract_unsigned(sql_type)
end
alias_method_chain :initialize, :unsigned
def extract_unsigned(sql_type)
false
end
end
class MysqlColumn
def extract_unsigned(sql_type)
sql_type =~ /unsigned/ ? true : false
end
end
class ColumnDefinition
attr_accessor :unsigned
def sql_type
base.type_to_sql(type.to_sym, limit, precision, scale, unsigned) rescue type
end
end
class Table
def integer(*args)
options = args.extract_options!
column_names = args
column_names.each do |name|
column = ColumnDefinition.new(@base, name, 'integer')
if options[:limit]
column.limit = options[:limit]
elsif native[:integer].is_a?(Hash)
column.limit = native[:integer][:limit]
end
column.precision = options[:precision]
column.scale = options[:scale]
column.default = options[:default]
column.null = options[:null]
column.unsigned = options[:unsigned]
@base.add_column(@table_name, name, column.sql_type, options)
end
end
end
class TableDefinition
def column_with_unsigned(name, type, options = {})
column_without_unsigned(name, type, options)[name].unsigned = options[:unsigned]
end
alias_method_chain :column, :unsigned
end
end
end
This adds the :unsigned option (which defaults to false) to migration files so that you can do things like this:
create_table :suppliers do |t|
t.integer :category_id, :null => true, :unsigned => true
t.column :region_id, :integer, :unsigned => true, :limit => 2
end
It works with ‘sexy’ syntax as well as the new :limit handling (where Rails now selects a more appropriate integer type, such as MEDIUMINT or BIGINT, depending on the :limit option) and you can also use it in add_column and change_column like this:
add_column :suppliers, :category_id, :integer, :unsigned => true
change_column :suppliers, :category_id, :integer, :unsigned => false
The good news is that by using the unsigned option your migrations are still database agnostic: the option is only used by the MySQL adapter and will be ignored by the others.
The final piece of the puzzle is the SchemaDumper class which is responsible for generating the schema.rb file - it also needs to know about unsigned fields so that it can generate the correct output. Some of the code is already implemented above, for example the Column and MysqlColumn classes have been extended to understand unsigned integers). It is also necessary to update the SchemaDumper class:
module ActiveRecord
class SchemaDumper
def table(table, stream)
columns = @connection.columns(table)
begin
tbl = StringIO.new
if @connection.respond_to?(:pk_and_sequence_for)
pk, pk_seq = @connection.pk_and_sequence_for(table)
end
pk ||= 'id'
tbl.print " create_table #{table.inspect}"
if columns.detect { |c| c.name == pk }
if pk != 'id'
tbl.print %Q(, :primary_key => "#{pk}")
end
else
tbl.print ", :id => false"
end
tbl.print ", :force => true"
tbl.puts " do |t|"
column_specs = columns.map do |column|
raise StandardError, "Unknown type '#{column.sql_type}' for column '#{column.name}'" if @types[column.type].nil?
next if column.name == pk
spec = {}
spec[:name] = column.name.inspect
spec[:type] = column.type.to_s
spec[:limit] = column.limit.inspect if column.limit != @types[column.type][:limit] && column.type != :decimal
spec[:precision] = column.precision.inspect if !column.precision.nil?
spec[:scale] = column.scale.inspect if !column.scale.nil?
spec[:null] = 'false' if !column.null
spec[:default] = default_string(column.default) if !column.default.nil?
spec[:unsigned] = 'true' if column.unsigned == true
(spec.keys - [:name, :type]).each{ |k| spec[k].insert(0, "#{k.inspect} => ")}
spec
end.compact
# find all migration keys used in this table
keys = [:name, :limit, :precision, :scale, :default, :null, :unsigned] & column_specs.map(&:keys).flatten
# figure out the lengths for each column based on above keys
lengths = keys.map{ |key| column_specs.map{ |spec| spec[key] ? spec[key].length + 2 : 0 }.max }
# the string we're going to sprintf our values against, with standardized column widths
format_string = lengths.map{ |len| "%-#{len}s" }
# find the max length for the 'type' column, which is special
type_length = column_specs.map{ |column| column[:type].length }.max
# add column type definition to our format string
format_string.unshift " t.%-#{type_length}s "
format_string *= ''
column_specs.each do |colspec|
values = keys.zip(lengths).map{ |key, len| colspec.key?(key) ? colspec[key] + ", " : " " * len }
values.unshift colspec[:type]
tbl.print((format_string % values).gsub(/,\s*$/, ''))
tbl.puts
end
tbl.puts " end"
tbl.puts
indexes(table, tbl)
tbl.rewind
stream.print tbl.read
rescue => e
stream.puts "# Could not dump table #{table.inspect} because of following #{e.class}"
stream.puts "# #{e.message}"
stream.puts
end
stream
end
end
end
Unfortunately I couldn’t see a tidier way of doing this when monkey-patching other than copying, pasting and editing the original method. There are actually only two changes:
spec[:unsigned] = 'true' if column.unsigned == true
and
keys = [:name, :limit, :precision, :scale, :default, :null, :unsigned] & column_specs.map(&:keys).flatten
I’ve also submitted a patch that I’ve tested with MySQL and SQLite3 so please take a look, let me know if you’ve got any suggestions on how these changes could be made better and maybe then they’ll be good enough to make it into Rails 2.2!
You can find the Rails-core discussion of the patch here.