MySql Migration Optimizer
Extends the MySQL connector to provide:
* Support for unsigned integer values through the precision parameter
* Support for column display width through the scale parameter
* Support for customization of the primary key value through primary_column hash specified to create_table
UNSIGNED integers
By default, use unsigned are used for integers (and booleans) unless :precision is set to :signed or globally configured by setting the default_sign in environment.rb
MySqlMigrationOptimizer.default_sign = :signed
Since foreign keys should match, it is a good idea to set the default to :signed if you have already established your app
add_column :giraffe, :beer_count, :integer, :precision => :signed
add_column :giraffe, :beer_count, :integer, :precision => :unsigned
create_table "giraffe" do |t|
t.integer "neck_length", :scale => 8, :precision => :signed
t.boolean "has_spots", :default => true
end
Generated SQL:
CREATE TABLE `giraffe` (
`id` int(11) UNSIGNED NOT NULL auto_increment PRIMARY KEY,
`neck_length` int(8), `has_spots` tinyint(1) UNSIGNED DEFAULT 1
) ENGINE=InnoDB
Integer Display width
To set the integer display width, use :scale => XXX Display width is the max number of digits that MySQL will display and is not indicative of the storage size which is determined by the :limit option
add_column :giraffe, :beer_count, :integer, :scale => 8
SQL: ALTER TABLE `giraffe` ADD `beer_count` int(8) UNSIGNED
Note that the type of integer tinyint, smallint, mediumint, int, and bigint are determined by the :limit from 1, 2, 3, 4, 5-8 respectively Therefore the following are equivalent
add_column :giraffe, :beer_count, :smallint
add_column :giraffe, :beer_count, :integer, :limit => 2
SQL: ALTER TABLE `giraffe` ADD `beer_count` smallint UNSIGNED
In addition to the normal options of create_table, additional option of :primary_column can specify column attributes for the primary column. By default an unsigned int(11) is used
Create Table :primary_column Hash Map Options
create_table allows for an additional map param :primary_column of options to define the primary key column.
* :name - the same as specifying the :primary_key in the parent map
* :scale - the scale of the variable
* :precision - precision of the column. Set to :signed or :unsigned for integers and booleans. Defaults to the value of MySqlMigrationOptimizer.default_sign which is originaly set to :unsigned
* :type - the type of column. Defaults to :integer
* :null - set to false if the column is not nullable
* :null - default value of the column
* :auto_increment - Defaulted to true, set to false to turn off auto increment
Specify a string column as the primary key
create_table "blah", :force => true,
:primary_column => {:type=>:string, :limit=>25, :auto_increment=>false} do |t|#
end
SQL: CREATE TABLE `blah` (`id` varchar(25) NOT NULL PRIMARY KEY) ENGINE=InnoDB
Specify the primary column as an unsigned (default) integer called special key with display width of 3
create_table "animal", :primary_key => "special_key", :force => true,
:primary_column => {:type=>:integer, :limit => 2, :scale=>3} do |t|
end
SQL: CREATE TABLE `animal` (`special_key` smallint(3) UNSIGNED NOT NULL auto_increment PRIMARY KEY) ENGINE=InnoDB
Developers
* Blythe Dunham http://snowgiraffe.com
Homepage
* Homepage: http://www.snowgiraffe.com/tech/?tag=mysql_migration_optimizer
* Rdoc: http://snowgiraffe.com/rdocs/mysql_migration_optimizer/index.html
* GitHub Project: http://github.com/blythedunham/mysql_migration_optimizer/tree/master
* Plugin Install: script/plugin git://github.com/blythedunham/mysql_migration_optimizer.git
Copyright (c) 2009 Blythe Dunham, released under the MIT license