Posted by Peter Donald Wed, 14 Dec 2005 02:28:00 GMT

I finally got fed up with the bugs in Rails 1.0 handling of mysql connections and have decided to move to postgres. I have talked about the move and even migrated my DDL to the database agnostic schema language. The one thing I had not yet thought about was how to move my data.

After doing a little bit of asking and searching I decided to just dump my data out the databse to fixtures and then reload these fixtures. This is surprisingly simple using the rake task.

desc 'Dump a database to yaml fixtures. '
task :dump_fixtures => :environment do
  path = ENV['FIXTURE_DIR'] || "#{RAILS_ROOT}/data"

  ActiveRecord::Base.establish_connection(RAILS_ENV.to_sym)
  ActiveRecord::Base.connection.
             select_values('show tables').each do |table_name|
    i = 0
    File.open("#{path}/#{table_name}.yml", 'wb') do |file|
      file.write ActiveRecord::Base.connection.
          select_all("SELECT * FROM #{table_name}").inject({}) { |hash, record|
      hash["#{table_name}_#{i += 1}"] = record
        hash
      }.to_yaml
    end
  end
end

desc "Reset Database data to that in fixtures that were dumped"
task :load_dumped_fixtures => :environment do
  require 'active_record/fixtures'
  ActiveRecord::Base.establish_connection(RAILS_ENV.to_sym)
  path = ENV['FIXTURE_DIR'] || "#{RAILS_ROOT}/data"
  Dir.glob("#{path}/*.{yml}").each do |fixture_file|
    Fixtures.create_fixtures(path, File.basename(fixture_file, '.*'))
  end
end
This is Mysql specific due to the use of select_values('show tables') but apparently sqlite usues select_values('.table') and postgres uses the following. 
 select_values(<<-end_sql
SELECT c.relname
 FROM pg_class c
   LEFT JOIN pg_roles r     ON r.oid = c.relowner
   LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
 WHERE c.relkind IN ('r','')
   AND n.nspname IN ('myappschema', 'public')
   AND pg_table_is_visible(c.oid)
end_sql

This worked like a charm except when my data contained embedded ERB directives because when rails loads the fixtures it attempts to evaluate the fixture as an ERB script. In this scenario I just needed to nip into the read_fixture_files method in $RUBY_HOME\activerecord-1.13.2\lib\active_record\fixtures.rb and comment out the erb rendering while I imported my data.

Update: The code for this can be found is available in dump_fixtures.rake