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