For developing our Ruby on Rails based web site, we usually take regular SQL dumps from our production servers (of course, anonymizing sensitive customer data along the way). Always having a fresh dump allows us to be on the safe side when writing database migrations. Having an up to date development database enables us to run our test suite as well; it’s just a matter of rake db:test:prepare to get our test database up-to-date. So far, so good.
Due to our use of some MySQL specific stuff (namely full-text-indices), which are not supported by the database agnostic schema.rb, we’re using config.active_record.schema_format = :sql. This makes rake db:test:prepare use db:test:clone_structure instead of db:test:load. Unfortunately, db:test:clone_structure always calls db:structure:dump, which tries to dump the development database structure to an SQL file. This is ok on a developer’s box but we ran into trouble on our continuous integration server.
We don’t want to setup a development environment on our CI server (which requires loading an anonymized production dump – quite a beast). It’s enough for us to have a test environment there, using a checked out development_structure.sql (created on a development box and checked into version control) to create the test database. Unfortunately, the rake tasks provided by rails do not cover this scenario.
So, we simply came up with the following addition to rails’ set of rake tasks:
namespace :db do
namespace :test do
desc "Recreate the test database from an existing db/development_structure.sql dump file"
task :load_structure => [ "db:test:purge" ] do
ActiveRecord::Base.establish_connection(:test)
ActiveRecord::Base.connection.execute('SET foreign_key_checks = 0')
IO.readlines("#{RAILS_ROOT}/db/#{RAILS_ENV}_structure.sql").join.split("nn").each do |table|
ActiveRecord::Base.connection.execute(table)
end
end
end
end
rake db:test:load_structure reads the checked out development_structure.sql and applies it to the test database (which it recreates first by defining db:test:purge as a pre-requisite). That’s all we need to have a fully up-to-date test database on our CI server! No need to worry about creating a full development environment there just to be able to run migrations and dump the updated schema prior to setting up the test database.
Agile Web Operations 
Thanks for your article! I’m currently testing devver (devver.net) and was stumped by the database schema.db/development_structure.sql.
I assume the .split(“nn”) is intended to split the SQL file at blank lines between table declarations. It should be two “backslash n” (“nn” if it displays correctly in the comment).
Cheers
LikeLike
Christian,
yes, the split must have two backslashes. It splits the table definitions in the dump file. Thanks for pointing it out! I fixed it in the code snippet.
LikeLike
This looks great, though I may be making a dumb mistake. I’m running this command, to ensure the db is created before we run tests:
RAILS_ENV=test rake db:test:load_structure test:units –trace
load_structure works great, but then when test:units runs, it calls db:test:clone_structure which dies with mysql errors, because the tables already exist. Am I doing something wrong? Output looks like this:
** Invoke db:test:load_structure (first_time)
** Invoke db:test:purge (first_time)
** Invoke environment (first_time)
** Execute environment
** Execute db:test:purge
** Execute db:test:load_structure
** Invoke test:units (first_time)
** Invoke db:test:prepare (first_time)
** Invoke db:abort_if_pending_migrations (first_time)
** Invoke environment
** Execute db:abort_if_pending_migrations
** Execute db:test:prepare
** Invoke db:test:clone_structure (first_time)
** Invoke db:structure:dump (first_time)
** Invoke environment
** Execute db:structure:dump
** Invoke db:test:purge
** Execute db:test:clone_structure
rake aborted!
Mysql::Error: Table ‘adsense_stats’ already exists: CREATE TABLE `adsense_stats` (
LikeLike
We do not use
rake test:unitsbut run our tests directly from the command line (spec ./specin our case).You could try to re-define the
test:unitstask, removing thedb:test:preparedependency (just an idea, not tested):namespace :test do Rake::TestTask.new(:units => :environment) do |t| t.libs << "test" t.pattern = 'test/unit/**/*_test.rb' t.verbose = true end endLikeLike