Overview:
- Generate the script
- Edit the script
- Test the Script
- Apply the Script
- Roll back a script
- Apply the Script… again
- 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.
database: mydb_development
Details:
Generate the scripts…
For a new Table/Model… ”ruby script/generate model order”
This command also creates the following files:
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!
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)
__________________________________________________________________________________________________
__________________________________________________________________________________________________
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 
rdCustPK, :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 => wner_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
add_column :accounts, :ssl_enabled, :boolean, :default => 1
remove_column :accounts, :ssl_enabled
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 ptions => ‘ENGINE=MyISAM’) do |t|
t.column rdPK, :integer, :null => false, :auto_increment => true
t.column rdCustPK, :integer, :null => false
t.column rdCustPONum, :string, :limit => 20, :default => ”
t.column rdTotal, :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 rdPK, :integer, :default => 0, :null => false
t.column rdCustPONum, :string, :limit => 20, :null => false
t.column rdFinalDisposition, :string, :limit => 30, :null => false
t.column rDateOrderTaken, :date, :null => false
t.column rdItemWeight, :decimal, :precision => 10, :scale => 0, :default => 0
t.column rdShippingWeight, :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”
