gistfile1.rb

# Generate a new schema from a legacy database from a generated hash map
# code released courtesy of Radio New Zealand under MIT license
# see http://www.abletech.co.nz/2009/06/rails-legacy-database-migration/ for more info
 
desc "generates migration code insert"
task :generate_migrations_sniplet => :environment do
  s = "MIGRATIONS = \n"
  s << " [\n"
  con = ActiveRecord::Base.connection
  con.tables.each do |table|
    s << "  {\n" 
    s << "    :old_table => '#{table}',\n" 
    s << "    :new_table => 'new_#{table}',\n"
    s << "    :cols => [\n"
    cols = con.columns(table).collect {|c| "      {:name => '#{c.name}', :old_name => '#{c.name}', :type => '#{c.type}', :options => {}},"}
    s << cols.join("\n")
    s << "\n    ]\n"
    s << "  },\n" 
  end
  s << " ]\n"
  puts s
end

# Rails migration file contents
# format
# MIGRATIONS = [{
#   :old_table => '',
#   :new_table => '',
#   :cols => [
#     {:name => '', :type => '', :old_col => '', :optons => {}},
#     {:name => '', :type => '', :old_col => '', :optons => {}},
#   ]
# }]


class MigrateBrad < ActiveRecord::Migration
  def self.up
    old_db = 'YOUR_LEGACY_DATABASE_NAME'
    # create new table
    MIGRATIONS.each do |migration|
      new_table = migration[:new_table]
      create_table new_table, :force => true do |t|
        migration[:cols].each do |col|
          t.send(col[:type], col[:name], col[:options]) unless col[:name].blank? || col[:name] == 'id'
        end
        
      end
    end
    # migrate the data
    MIGRATIONS.each do |migration|
      new_cols = migration[:cols].collect {|c| c[:name]}.join(',')
      old_cols = migration[:cols].collect {|c| c[:old_name].blank? ? 'NULL' : c[:old_name]}.join(',')
      old_table = migration[:old_table]
      new_table = migration[:new_table]
      execute "INSERT INTO #{new_table} (#{new_cols}) SELECT #{old_cols} FROM #{old_db}.#{old_table};"
    end
  end

  def self.down
    MIGRATIONS.each do |migration|
      drop_table migration[:new_table]
    end
  end
end