Have you ever been stuck in a multiple table migration that you can't roll forward or backwards because your database and your migration are not in sync. This very problem has come up for me numerous times and one day I finally decided to fix it.
Here is a quick example of what I am talking about. Lets take a simple 2 table migration file:
class EmployeeJobs < ActiveRecord::Migration
def self.up
create_table employees do |t|
t.column :age, :integer
t.column :name, :string, :length=>255
end
create_table :jobs, :id => false do |t|
t.column :employee_id, :integer
t.column :status, :integer
t.column :name, :string, :length=>255
end
end
def self.down
drop_table :employees
drop_table :jobs
end
end
This migration file will create 2 tables and seems pretty innocent. But lets say later down the line you decide to change the jobs table to add a category field. But since you are a newbie to Ruby on Rails like myself, you have a typo and the migration fails 1/2 way through execution. Now in the database you have an employees table but no jobs table. When you try to rollback you get an error saying the employees table could not be found. When you try to roll forward you get an error saying the jobs table already exists. If you are like me, most of the time you drop the database and re-create it to get around the problem... I mean this is only development.
Solution:
Here is a simple solution that will help prevent this situation from happening. Add a simple function that will check to see if the table exists before trying to drop the table. This will stop migration failing if it tries to drop a table that does not exist in the database.
def self.check_table(name)
begin
User.connection.execute("select 1 from #{name}")
say "Checking For Table: #{name} => Table Found"
return false;
rescue
say "Checking For Table: #{name} => Table Not Found"
return true;
end
end
Now in your down block add a simple unless clause:
def self.down
drop_table :employees unless self.check_table('employees')
drop_table :jobs unless self.check_table('jobs')
end
Conclusion:
While this method does work, it does have some flaws. For one, you need to have the "check_table" function in each of your migration files. I am sure there is a way to make this a global function, but in my get it down now world I have no had time to figure that out. Being a DBA in a former life whose job it was to create migration scripts, simple checks to see if the table exists or if there is data in the table would be nice to have as options on the drop_table commands. But in the meantime hopefully the above function will help you out.
A sample migration file with the download check can be downloaded here: sample_migration.rb (0.8K)
