Bootstrapping your Database with Ordered Fixtures

July 27, 2007

Ruby on Rails provides a convenient way to ensure consistent database state for all unit tests and this is accomplished through Fixtures.

In the Rails Weenie forums technoweenie presented a technique that allows us to ‘bootstrap’ our development or production database using the same underlying mechanisms.

The way it works is first we place YAML files similar to the ones we use for testing under db/bootstrap (instead of test/fixtures). Then it’s a simple matter of executing:

rake db:bootstrap:load

Fixtures and Referential Integrity

The only problem with the above technique is when your tables have foreign key constraints (as they easily could if you use ActiveRecord Migrations with my own Migrations Constraints Plug-in).

When using fixtures for unit tests, we can explicitly specify the order of the fixtures in code. For example:

fixtures :foo, :bar_references_foo

The original technique by technoweenie allows us to specify a fixture order by an environment variable:

FIXTURES="foo bar_references_foo" rake db:bootstrap:load

While it works, it requires us to specify our fixture names and their order every time – not very DRY if you ask me.

Ordered Fixtures

Sandro Paganotti on RailsOnWave.com also realized this and presented his own version of rake db:bootstrap:load that attempts to address this.

The idea is to let us number our fixtures, similar to how we number our migrations, so that they can be loaded/unloaded in proper order according to our foreign key constraints.

Except, when I looked more closely at his code, I had my reservations.

First, and most glaring, is that he sorts the fixtures array, even if it’s passed in through the FIXTURES environment variable. I think this totally defeats the purpose of explicitly specifying the fixtures that way.

Second, and relatively minor, is that he hard-codes the format of the fixture file names so they have to be 2 digit numbers followed by an underscore, then the table name.

My rake db:bootstrap:load

So I set about to write my own, slightly more evolved version of Sandro Paganotti’s rake task.

namespace :db do
  desc "Loads a schema.rb file into the database and then loads the initial database fixtures."
  task :bootstrap => ['db:migrate', 'db:bootstrap:load']

  namespace :bootstrap do
    desc "Load initial database fixtures (in db/bootstrap/*.yml) into the current environment's database.  Load specific fixtures using FIXTURES=x,y"
    task :load => :environment do
      require 'active_record/fixtures'
      if ENV['FIXTURES']
        fixture_files = ENV['FIXTURES'].split(/\s*,\s*/).map {|file| [File.join(RAILS_ROOT, 'db', 'bootstrap', file), file]}
      else
        found_files = Dir.glob(File.join(RAILS_ROOT, 'db', 'bootstrap', '*.{yml,csv}'))
        numbered_files = found_files.select {|path| path =~ /\d+_[^.]+\.yml\Z/i }
        fixture_files = numbered_files.sort {|x,y| x[/(\d+)_.*\Z/,1].to_i <=> y[/(\d+)_.*\Z/,1].to_i }.
          map {|fixture_path| [fixture_path[/(.*)\.(yml|csv)\Z/i,1], fixture_path[/\d+_([^.]+)\.(yml|csv)\Z/i, 1]] }
        fixture_files += (found_files - numbered_files).map {|file| path = file[0, file.rindex('.')]; [path, File.basename(path)]}
        raise "No fixtures found matching \"db/bootstrap/*.{yml,csv}\"! Specify fixtures using \"FIXTURES=\"" if fixture_files.empty?
      end

      ActiveRecord::Base.establish_connection(RAILS_ENV.to_sym)
      connection = ActiveRecord::Base.connection
      fixtures = fixture_files.map do |fixture_path, table_name|
        Fixtures.new(connection, table_name, nil, fixture_path)
      end
      connection.transaction(Thread.current['open_transactions'] == 0) do
        fixtures.reverse.each { |fixture| fixture.delete_existing_fixtures }
        fixtures.each { |fixture| fixture.insert_fixtures }

        # Cap primary key sequences to max(pk).
        if connection.respond_to?(:reset_pk_sequence!)
          fixture_files.each do |fixture_path, table_name|
            connection.reset_pk_sequence!(table_name)
          end
        end
      end
    end
  end
end

While the code looks substantially changed, as far as behavior it really isn’t very different.

The first major change is that fixtures can now be numbered using any number of digits, followed by an underscore, followed by the table name and the file extension (.yml or .csv). Fixtures will be ordered properly, numerically, even if we various combinations of 1, 2 or 3-digit numerical prefixes (though, for consistency’s sake you’ll probably want to stick to 3 digits like migrations).

Note that any other *.yml or *.csv files found under db/bootstrap are loaded after the numbered fixtures – so this essentially remains backward compatible with the first two versions discussed earlier.

Finally, we can still specify an explicit set of fixtures to load via FIXTURES, except this time the code uses an array (instead of a Hash) and processes the fixtures according to the order given. This lets us bootstrap our database with selected tables only, while still letting us work around our foreign key constraints.

Of course, none of this will let you properly bootstrap your database if your tables have any circular foreign key constraints. That’s an entirely different problem (and database schema design ‘smell’) that deserves its own discussion.

Try it out

To use the above code, simply copy it and save it as a .rake file under lib/tasks, say, lib/tasks/boostrap.rake. That’s all there is to it. Then simply create you fixtures under db/bootstrap as you would your unit test fixtures, and number them accordingly when needed!

Advertisements

17 Responses to “Bootstrapping your Database with Ordered Fixtures”

  1. Sasha Says:

    Ok. I reckon my brain just might implode (then explode) with that sort of data. Gah.

  2. Sandro Says:

    Hi Alistar

    I’ve read your post and I must agree, you’ve done a real good job 🙂 When I wrote my bootstrap implementation it was a quick and dirty fix ’cause I had not time to refine my rake.

    Thank you for the work you’ve done.
    Sandro Paganotti.

  3. Alistair Says:

    Hi, Sandro. Thanks for the note of appreciation! Though, I guess we all owe you thanks first for sharing your code (which I just added to). 🙂


  4. […] Alistair Israel Just another WordPress.com weblog « Bootstrapping your Database with Ordered Fixtures […]

  5. Peter Says:

    Hi Alistair,

    I was hoping to use this, but I get the following error when trying to do any rake db task…

    $ rake db:migrate

    /lib/tasks/bootstrap.rake:5: syntax error, unexpected tIDENTIFIER, expecting kEND
    task :bootstrap => [‘db:schema:load’, ‘db:bootstrap:load’] namespace :bootstrap do

    Is there some dependency I am missing? Any other ideas? I did get technoweenie’s version to work.

  6. Alistair Says:

    Hi, Peter. Sorry – it seems WordPress mangled the code a bit (happens sometimes when switching back & forth from visual editing to code editing modes).

    Anyway, the namespace :bootstrap do statement should be on the following line. Fixed the code already. Please do try again and let me know if you’re still having any problems.

  7. Peter Says:

    That seemed to do the trick.

    Thanks!

  8. borni Says:

    Thank you very much for this great solution.
    Copy and paste did not work for me, because the regular expressions did not match correctly. I suppose either wordpress or my editor acted a bit overzealous.
    I have pasted a modified (and thus probably your original) version at:
    http://pastie.caboo.se/pastes/125423

    Thanks again, just what I was looking for.

  9. Alistair Says:

    Hi, borni.

    Yeah, I guess WordPress mangles code more than I thought. I double-checked and re-copied and pasted from the actual source code on my project and edited the text (yet again) to try and get it right this time.

    Thanks for the heads up and sorry for any confusion.

  10. borni Says:

    Hi again!
    There seems to be another minor bug that is triggered when the fixture filename contains an extra underscore (001_foo_bar.yml).
    I believe the reason is a missing comparison operator in line 13 (sort block) in the code above, probably stripped by wp 😉
    Adding <=> between ….to_i and y[… fixes this.
    Thank you again for your work!

  11. Tom Says:

    Hi, in case someone is still looking at this, I added to your method to use the ‘create_fixtures’ function instead…

    In our app the ‘insert_fixtures’ method too 463 seconds, and create_fixtures took 36 seconds… Which makes no sense as it runs pretty much identically to the above code… But that was the result…

    In case it helps anyone the adapted code is here:
    http://pastie.org/269403


  12. Sounds like a very interesting concept! Be nice to my dizzy transition Fresh joke! Did you hear about the guy who ran through the screen door? He strained himself.


  13. […] A bootstrap task (here and to this place) […]

  14. kikito Says:

    The initial code got me the following error:

    undefined method `assert_valid_keys’ for false:FalseClass

    This was on the line “connection.transaction”.

    I don’t think i need transactions while bootstrapping, so I tried Tom’s solution from the comments.

    Tom’s solution doesn’t use transactions, but it is a db:bootstrap task instead of db:bootstrap:load. I didn’t want to loose the standard db:bootstrap so I’ve modified it (also slight changes on the output messages) and uploaded here:

    http://pastie.org/531899

    Pasting it below just in case pastie gets broken or something:

    namespace :db do
    desc “Loads a schema.rb file into the database and then loads the initial database fixtures.”
    task :bootstrap => [‘db:schema:load’, ‘db:bootstrap:load’]

    namespace :bootstrap do
    desc “Load initial database fixtures (in db/bootstrap/*.yml) into the current environment’s database. Uses number_class for ordering by default. Load specific fixtures using FIXTURES=x,y”
    task :load => :environment do
    require ‘active_record/fixtures’

    if ENV[‘FIXTURES’]
    fixture_files = ENV[‘FIXTURES’].split(/\s*,\s*/).map {|file| [File.join(RAILS_ROOT, ‘db’, ‘bootstrap’, file), file]}
    else
    found_files = Dir.glob(File.join(RAILS_ROOT, ‘db’, ‘bootstrap’, ‘*.{yml,csv}’))
    numbered_files = found_files.select {|path| path =~ /\d+_[^.]+\.yml\Z/i }
    fixture_files = numbered_files.sort {|x,y| x[/(\d+)_.*\Z/,1].to_i y[/(\d+)_.*\Z/,1].to_i }.
    map {|fixture_path| [fixture_path[/(.*)\.(yml|csv)\Z/i,1], fixture_path[/\d+_([^.]+)\.(yml|csv)\Z/i, 1]] }
    fixture_files += (found_files – numbered_files).map {|file| path = file[0, file.rindex(‘.’)]; [path, File.basename(path)]}
    raise “No fixtures found matching \”db/bootstrap/*.{yml,csv}\”! Specify fixtures using \”FIXTURES=\”” if fixture_files.empty?
    end

    start = Time.now
    fixture_counter = 0
    fixture_files.each do |fixture_path, table_name|
    fixture_counter += 1
    puts “#{fixture_counter}. Importing – ” + table_name.titleize
    Fixtures.create_fixtures(‘db/bootstrap’, table_name)
    end
    puts “Elapsed time ” + (Time.now – start).seconds.to_s + ” seconds”

    end #task :load => :environment do
    end #namespace :bootstrap do
    end

  15. kikito Says:

    Updated version – csv files not being ordered.

    http://pastie.org/627461

  16. Jaco Says:

    The first example gave me the error “undefined method `assert_valid_keys’ for false:FalseClass”. then I tries the last bootstrap code from pastie and it does also not work for me.

    I use tablenames like 001_languages.yml, 002_xxx etc. Running the bootstrap in trace mode, it says it imports the tables, but all tables are empty after the import. When I rename the sample file to languages.yml, the table does contain the data after the bootstrap has run. Any ideas? Can someone replicate this behaviour?


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: