Export MySQL data to sql file in order to import to PostgreSQL task.

convert.rake

#
# Export development database(MySQL) data to sql file in order to
# import to production database(PostgreSQL) task specialized radiant.
#
# WARGNING: It should be configured database.yml also created databases
# both development and production and runned db:migrate before run tasks.
# MySQL database is already imported is needed.
#
# usage:  rake db:convert:execute
#

require "active_record"

SKIP_TABLES = ["schema_info", "schema_migrations"]
SQL_FILE_DIR = "#{RAILS_ROOT}/tmp/files"
ROW_PER_FILE = 1000

namespace :db do
  namespace :convert do
    class DevelopmentModelClass < ActiveRecord::Base; end
    class ProductionModelClass < ActiveRecord::Base; end

    desc "Set up, export, import tasks."
    task :execute => :environment do
      Rake::Task["db:convert:setup"].execute
      Rake::Task["db:convert:export_from_mysql"].execute
      Rake::Task["db:convert:import_to_postgresql"].execute
    end

    desc "Set up for convert data."
    task :setup => :environment do
      FileUtils.mkdir_p(SQL_FILE_DIR)
    end

    desc "Export MySQL data to insert sql files."
    task :export_from_mysql => :environment do
      ActiveRecord::Base.establish_connection(:production)
      (ActiveRecord::Base.connection.tables - SKIP_TABLES).each do |table_name|
        ProductionModelClass.set_table_name(table_name)
        ProductionModelClass.reset_column_information

        ActiveRecord::Base.establish_connection(:development)
        DevelopmentModelClass.set_table_name(table_name)
        DevelopmentModelClass.reset_column_information

        total_rows = DevelopmentModelClass.find(:all).size
        next if total_rows == 0

        print "Exporting #{table_name}...¥n"
        max_page = (total_rows / ROW_PER_FILE).succ
        print "Data devide to #{max_page} file...¥n"

        max_page.times.each do |page|
          file_name = "#{table_name}_#{page}.sql"
          file = "#{SQL_FILE_DIR}/#{file_name}"
          next if File.exists?(file)

          offset = page * ROW_PER_FILE
          limit = ROW_PER_FILE

          f = File.open(file, "a+b")
          f.puts "TRUNCATE #{table_name};¥n" if page == 0
          DevelopmentModelClass.find(:all, :offset => offset, :limit => limit).each do |record|
            cols = []
            values = []
            attrs = record.attributes
            ActiveRecord::Base.establish_connection(:production)
            ProductionModelClass.columns.each do |column|
              cols << ProductionModelClass.connection.quote_column_name(column.name)
              value = attrs[column.name]
              if column.type == :boolean
                value = value == 1 ? "true" : "false"
              else
                value = value.respond_to?("gsub") ? value.gsub(/¥r/, "") : value
                value = ProductionModelClass.connection.quote(value)
              end
              values << value
            end
            f.puts "INSERT INTO #{ProductionModelClass.connection.quote_table_name(table_name)} (#{cols.join(",")}) VALUES (#{values.join(",")});¥n"
          end
          f.close
          print "#{file_name} is published.¥n¥n"
        end
      end
    end

    desc "Import insert sql files to PostgreSQL."
    task :import_to_postgresql => :environment do
      ActiveRecord::Base.establish_connection(:production)
      (ActiveRecord::Base.connection.tables - SKIP_TABLES).each do |table_name|
        ProductionModelClass.set_table_name(table_name)
        ProductionModelClass.reset_column_information

        files = Dir.glob("#{SQL_FILE_DIR}/#{table_name}_[0-9]{0,}.sql")
        next if files.size == 0
        files.sort!

        print "Importing #{table_name}...¥n"
        files.each do |file|
          next unless File.exists?(file)
          file_name = File::basename(file)
          print "Importing from #{file_name}..¥n"
          f = File.open(file, "r+b")
          ProductionModelClass.connection.execute(f.read)
          ProductionModelClass.connection.reset_pk_sequence!(table_name)
          f.close
          print "#{file_name} is imported.¥n¥n"
          FileUtils.rm(file)
        end
      end
    end
  end
end