Create A New Migration Script

Overview:

1. Generate the script
2. Edit the script
3. Test the Script
4. Apply the Script
5. Roll back a script
6. Apply the Script… again
7. Check the “schema.rb” that gets generated

NewbieNote*  The Database.YML file dictates the database upon which your code (and rake commands) operate.  For example, this snippet from a database.yml file will execute all commands/code on the “mybd_development” database. 

development:

  adapter: mysql

  database: mydb_development

  username: root

  password:

  host: localhost 
 

Details:

Generate the scripts…

For a new Table/Model…      ”ruby script/generate model order”

      This command also creates the following files:

         app/models/ order.rb

         test/unit/ order_test.rb

         test/fixtures/ orders.yml 

For a new Table/Model (just the migration script)…   ”ruby script/generate migration create_orders_table”

For making modifications…      ”ruby script/generate migration this_is_the_name_of_my_migration”

For existing Tables (legacy data)…      …see the related topic ‘Create Migration For Existing Tables’ 
 

(you should use explicit names, describing what the migration will do, since the generator will create a file with this same name and overwrite any previously existing one) 

Scripts are Stored In:  \db\migrate\ 

Scripts are Sequentially Numbered…  001_create_orders_table.rb, 002_create_second_table.rb, etc. 

Next Number that needs to be applied, stored in table “schema_info” field named “version” 

“db:migrate” will apply the scripts in sequential order, starting with the next number after the one stored in “schema_info.version”. 
 

Edit the script file (see below) 

You probably want to include the “:force => true” parameter when creating a new table. 

Make sure you have properly coded the “self.down” method in case you need to roll back to a prior version! 

See my “Naming Conventions” Post for more info:  http://oomoo.wordpress.com/2008/03/11/some-rails-naming-conventions/ 
 

Test the Migration Scrips…   “rake db:migrate –dry-run –trace” 

If “nothing” happens, then your migration was not applied.  The most obvious reason is that the “schema_info.version” field already

contains a number equal to, or higher than, the migration you are trying to apply. 
 

Apply the Migration Scripts…  “rake db:migrate”

Did an error occur?  See the “troubleshooting” section below. 

If “nothing” happens, then your migration was not applied.  The most obvious reason is that the “schema_info.version” field already

contains a number equal to, or higher than, the migration you are trying to apply. 
 

Roll back a script…  “rake db:migrate VERSION=004″    (substitute the desired “version #” for the number “4″) (Use the number “0″ to drop the tables) 

If you manually “dropped” the table yourself, you need to add the table back (just add one field), so the rollback will work.

OR, you could manually change the number stored in “schema_info.version” to a number prior to when the table existed. 

It is a good idea to “roll back” EVERY script, just to make sure your “self.down” works properly (before the next \db\migrate\ script gets created). 

Apply the Migration Script…Again  “rake db:migrate”   (After it rolls back successfully, just “rake db:migrate” again to put your migration back into effect.) 

Check the “schema.rb” file to check your new model.  Can be run manually by…  “rake db:schema:dump” 

_______________________________________________________________________________________________

_______________________________________________________________________________________________ 
 
 

Table/Model Naming Conventions: 

Model / Class - ( singular, first letter Capitalized, CamelCase for models like SteeringWheel)

Table / schema (in database) - ( plural, with underscores instead of spaces between words, like steering_wheels)  

Model/Class       Table/Schema

Order                 orders

LineItem             line_items

Person                people

Address             addresses

Legacy               legacies

Mouse               mice 
 

__________________________________________________________________________________________________

__________________________________________________________________________________________________ 
 

Migration Data Types

      string

      text

      integer

      float

      float :limit => 25   (yields a double)

      date

      time

      datetime

      timestamp

      binary

      boolean

      decimal, :precision => 15, :scale => 10   
               (precision = total digits, scale = digits right of decimal point)  

      t.column :, :string, :limit => 0, :default => ”, :null => false

      t.column :, :integer, :default => ‘0′ :null => false

      t.column :, :decimal, :precision => 10, :scale => 0 :default => ‘0′ :null => false

      t.column :, :date, :default => ‘0000-00-00′, :null => false 
 

      *Force the ordCustPK field to be a Foreign Key (FK) referencing Customers.CustPK:

      t.column :ordCustPK, :integer, :default => ‘0′ :null => false,  
               :references => [:customers, :CustPK] 

      *Force the Primary Key (PK) of the customers table to be the CustPK field:

      create_table(:customers, :force => true :primary_key => ‘CustPK’) 
 

______________________________________________________________________________________________

______________________________________________________________________________________________ 
 

Some Additional Migration Syntax

      add_index “activitylog”, ["logcreatedate"], :name => “logcreatedate”

      add_constraint :projects, :unique => :name

      add_constraint :projects, :foreign_key => :owner_id, :references => :users

            OR… execute ALTER TABLE posts ADD CONSTRAINT fk_posts FOREIGN KEY(discussion_id) REFERENCES discussions(id) 

      say_with_time “Updating salaries…”

            …Inserts your own messages/benchmarks by using the #say_with_time method 
 

Available transformations

• create_table(name, options) Creates a table called name and makes the table object available to a block that can then add columns to it, following the same format as add_column. See example above. The options hash is for fragments like “DEFAULT CHARSET=UTF-8″ that are appended to the create table definition.
• drop_table(name): Drops the table called name.
• rename_table(old_name, new_name): Renames the table called old_name to new_name.
• add_column(table_name, column_name, type, options): Adds a new column to the table called table_name named column_name specified to be one of the following types: :string, :text, :integer, :float, :decimal, :datetime, :timestamp, :time, :date, :binary, :boolean. A default value can be specified by passing an options hash like { :default => 11 }. Other options include :limit and :null (e.g. { :limit => 50, :null => false }) — see ActiveRecord::ConnectionAdapters::TableDefinition#column for details.
• rename_column(table_name, column_name, new_column_name): Renames a column but keeps the type and content.
• change_column(table_name, column_name, type, options): Changes the column to a different type using the same parameters as add_column.
• remove_column(table_name, column_name): Removes the column named column_name from the table called table_name.
• add_index(table_name, column_names, index_type, index_name): Add a new index with the name of the column, or index_name (if specified) on the column(s). Specify an optional index_type (e.g. UNIQUE).
• remove_index(table_name, index_name): Remove the index specified by index_name.

____________________________________________________________________________________________________

____________________________________________________________________________________________________ 
 

Primary Key:

The Rails convention is for every table to have a field named “id” which is an auto-incrementing integer primary key.  
 
 
 

_____________________________________________________________________________________________________

_____________________________________________________________________________________________________ 
 
 

Specify Your Own Primary Key:

The Rails convention is for every table to have a field named “id” which is an auto-incrementing integer primary key

(that the Rails migration code will automatically insert into your tables). 

If you are working with an existing database, you probably don’t want to redefine all the primary key fields to suit Rails. 
 

In your Migration code, just use the “:primary_key =>” parameter in your create_table method call, like this: 
 

class CreateAppTables < ActiveRecord::Migration

  def self.up

    create_table “employees”, :force => true, :primary_key => “empPK” do |t|

      t.column “empFirstName”,    :string,    :limit => 100, :default => “”,   :null => false

      t.column “empLastName”,     :string,    :limit => 100, :default => “”,   :null => false

      t.column “empActive”,       :boolean,                  :default => true, :null => false

      t.column “empDateCreate”,   :timestamp,                                  :null => false

      t.column “empLastLogon”,    :datetime,                                   :null => false

      t.column “empCustomerMgr”,  :integer,   :limit => 5,   :default => 0,    :null => false

    end

end 

Note - Since you are specifying the primary key, DO NOT also list the primary key as a field !!!

Notice above that since “empPK” is the primary key field, no “t.column” line exists for the field “empPK”. 
 
 

In your Model code, you must also tell Rails not to use the standard “id” primary key. 

Include a statement like this in your model code: 

class Employee ActiveRecord::Base 

  set_primary_key “empPK” 

end 
 
 
 

________________________________________________________________________________________________

________________________________________________________________________________________________ 
 
 

(see also) http://redhillconsulting.com.au/rails_plugins.html 
If you use any engines, etc. that have their own migrations,  

You will need to edit them to keep the “Row Version Migration” plugin from inserting the extra columns. 

In the create table statement, include  :row_version => false 
 

You will also need to edit the field definitions for any field that ends with “id” to keep the “Foreign Key Migration” plugin

from trying to force foreign key constraints on the table. 

Add the following to the end of the each “id” field definition  :references => nil 
 

________________________________________________________________________________________________

________________________________________________________________________________________________ 
 
 

Examples

This migration will add a boolean flag to the accounts table and remove it again:

class AddSsl < ActiveRecord::Migration

    def self.up

      add_column :accounts, :ssl_enabled, :boolean, :default => 1

    end 

    def self.down

      remove_column :accounts, :ssl_enabled

    end

end 

Example of a more complex migration that also needs to initialize data:

  class AddSystemSettings < ActiveRecord::Migration

    def self.up

      create_table :system_settings do |t|

        t.column :name,     :string

        t.column :label,    :string

        t.column :value,    :text

        t.column :type,     :string

        t.column :position, :integer

      end 

      SystemSetting.create :name => “notice”, :label => “Use notice?”, :value => 1

    end 

    def self.down

      drop_table :system_settings

    end

  end 
 
 
 

EXAMPLE1:

class CreateRepairs < ActiveRecord::Migration

  def self.up

    execute “DROP TABLE IF EXISTS `repairs`” 

      create_table(:repairs, :force => true :options => ‘ENGINE=MyISAM’) do |t|

            t.column :ordPK, :integer, :null => false, :auto_increment => true

            t.column :ordCustPK, :integer, :null => false

            t.column :ordCustPONum, :string, :limit => 20, :default => ”

            t.column :ordTotal, :decimal, :precision => 10, :scale => 2 :default => ‘0.00′

      end

  end 

  def self.down

    drop_table :repairs

  end

end 
 

EXAMPLE2:

class CreateRepairs < ActiveRecord::Migration

  def self.up

      create_table(:repairs, :force => true) do |t|

            t.column :ordPK, :integer, :default => 0, :null => false

            t.column :ordCustPONum, :string, :limit => 20, :null => false

            t.column :ordFinalDisposition, :string, :limit => 30, :null => false

            t.column :orDateOrderTaken, :date, :null => false  

            t.column :ordItemWeight, :decimal, :precision => 10, :scale => 0, :default => 0

            t.column :ordShippingWeight, :float, :limit => 25, :default => 0, :null => false

    end

  end 

  def self.down

    drop_table :repairs

  end

end 
 
 

Not all migrations change the schema. Some just fix the data:

  class RemoveEmptyTags < ActiveRecord::Migration

    def self.up

      Tag.find(:all).each { |tag| tag.destroy if tag.pages.empty? }

    end 

    def self.down

      # not much we can do to restore deleted data

      raise IrreversibleMigration

    end

  end 

Others remove columns when they migrate up instead of down:

  class RemoveUnnecessaryItemAttributes < ActiveRecord::Migration

    def self.up

      remove_column :items, :incomplete_items_count

      remove_column :items, :completed_items_count

    end 

    def self.down

      add_column :items, :incomplete_items_count

      add_column :items, :completed_items_count

    end

  end 

And sometimes you need to do something in SQL not abstracted directly by migrations:

  class MakeJoinUnique < ActiveRecord::Migration

    def self.up

      execute “ALTER TABLE `pages_linked_pages` ADD  
               UNIQUE `page_id_linked_page_id` (`page_id`,`linked_page_id`)”

    end 

    def self.down

      execute “ALTER TABLE `pages_linked_pages` DROP INDEX `page_id_linked_page_id`”

    end

  end 
 
 

_______________________________________________________________________________________________

_______________________________________________________________________________________________ 
 

The Next “Migration Number” that needs to be applied, is stored in table “schema_info” field named “version”