14 Dec 2005Dumping database to YAML fixtures

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

Posted in code and rails

About Me