Rails: Exporting data from specific tables into fixtures

Posted: - Modified: | geek, rails

Rails is pretty darn amazing. There are plenty of gems (Ruby packages) that provide additional functionality. They’re like Drupal modules, except with more customizability (not just hooks) and fewer pre-built administrative interfaces (you win some, you lose some).

For example, the client asked me, “Can we edit the static content?” Now if I had asked about this as a requirement at the beginning of the project, we might have gone with Drupal instead–although the Rails Surveyor still feels cleaner than a CCK-based survey type, so we might’ve stayed with Rails.

Anyway, we were well into Rails now, so I looked for a content management system that I could integrate into the Rails 3-based website. After some experimenting with Refinery CMS (looks slick, but couldn’t get it to do what I wanted) and Comfortable Mexican Sofa (looked pretty geeky), I settled on Rich CMS. I nearly gave up on Rich CMS, actually, because I’d gotten stuck, but the web demo helped me figure out what I needed to do in order to enable it.

We’re still emptying and reloading the database a lot, though, so I wanted to make sure that I could save the CmsContent items and reload them. I didn’t want to back up the entire database, just a table or two. There were some gems that promised the ability to back up specific models, but I couldn’t figure it out. Eventually I decided to use the table-focused Rake code I saw in order to export the data to fixtures (seems to be based on code from the Rails Recipes book).

task :extract_fixtures => :environment do
  sql  = "SELECT * FROM %s"
  skip_tables = ["schema_info"]
  ActiveRecord::Base.establish_connection
  if (not ENV['TABLES'])
    tables = ActiveRecord::Base.connection.tables - skip_tables
  else
    tables = ENV['TABLES'].split(/, */)
  end
  if (not ENV['OUTPUT_DIR'])
    output_dir="#{RAILS_ROOT}/test/fixtures"
  else
    output_dir = ENV['OUTPUT_DIR'].sub(/\/$/, '')
  end
  (tables).each do |table_name|
    i = "000"
    File.open("#{output_dir}/#{table_name}.yml", 'w') do |file|
      data = ActiveRecord::Base.connection.select_all(sql % table_name)
      file.write data.inject({}) { |hash, record|
        hash["#{table_name}_#{i.succ!}"] = record
        hash
      }.to_yaml
      puts "wrote #{table_name} to #{output_dir}/"
    end
  end
end

Being a lazy programmer who doesn’t want to remember table names, I also defined the following Rake tasks:

task :save_content => :environment do
  ENV["TABLES"] = "cms_contents"
  Rake.application.invoke_task("myproj:extract_fixtures")
end
task :load_content do
  Rake.application.invoke_task("db:fixtures:load")
end

Then I can call rake myproj:save_content and rake myproj:load_content to do the right thing. Or rather, my co-developer (a new IBMer – hello, Vijay!) can do so, and then check his work into our git repository. =)

Now we can re-create the development database as often as we’d like without losing our page content!

2011-04-24 Sun 16:29

You can comment with Disqus or you can e-mail me at sacha@sachachua.com.