Rails, Fixtures, the Test DB, and Test::Unit

From what I’ve seen, Rails’ weakest features lie in the way it prepares the test database and test data, and Ruby’s Test::Unit isn’t much better than the awful but ubuiquitous JUnit that Java developers are accustomed to. I set out this week to impose my preferences on Rails in this area, and that took some effort. Here’s what I did.

When I’ve implemented (in Java) what Rails does for database preparation, I did it like this:

1. Create the test database exactly the same way that the developers’ databases are created: by running the exact same code, pointed at a different database.
2. Load the appropriate sets of data for the test database. “Sets” is plural on purpose; most non-trivial databases include code tables, which constitute base data which are essentially part of the database design itself. Then, test code will want a fixed set of known test data to act upon, so that tests can measure whether the code did the right thing given the test data (the right inputs yield the right outputs).
3. Run the individual tests, providing some way of assuring that changes to the test data are undone before the next test.

At first (11 years ago) I used a hand-maintained SQL DDL file to create the databases. Later I split that up into one file per table, and made a list of the proper ordering of tables during creation (reversible for deletion). Later still, with Hibernate, I ditched the DDL and let a higher-level ORM description of the table do the schema generation (which was painful in Hibernate since it wasn’t made to do that except from the command line, but it was possible to hack it into a state of relative beauty). The test data was always loaded from a bunch of text files that were easy to hand-edit (as opposed to a bunch of SQL INSERT statements).

Running the test with assurance of pristine test data was more or less horrific in a J2EE+Hibernate 2.x environment. The design of Hibernate and JUnit made it difficult to wrap tests in transactions, and the version of MySQL that we were using had no transactional storage engines available at all (MyISAM? Thanks, Red Hat!), so I ended up falling back on an intrusive but relatively high-performance design that required tests to declare if they were going to alter the test data, so that the test teardown method knew it had to reload the test data. Since we were waiting for Hibernate 3.0, MySQL 5.x, and a few other things to become part of our architecture, I left that solution in place and ended up moving on to a new job before fixing it.

Rails initially seemed to nail this problem: the test database is automatically made based on the development database; the data is loaded from YAML files called Fixtures, which feature a very simple and straightforward API, and tests run inside individual transactions. Nice!

Except not. Fixtures are loaded by specifying the tables for which you need test data loaded, and this is done in each Test::Unit::TestCase class, of which I have several hundred. They are stupidly reloaded each time you say a given TestCase is going to use them. Worse, the tables you’re using for this TestCase are emptied out using SQL DELETE statements, but if there is test data in other tables that has foreign key dependencies on the data being deleted, fixture loading will fail. (Rails was not designed for FKs to be enabled in the database, so encountering this this bug is a side effect of enabling them via the plugin.) This deletion behavior is pointless in light of transactions wrapping each test, but if you’re using MySQL MyISAM you can’t use transactions, so it needs to be there for people using MyISAM, which is to say, crazy people who care not for their data.

Since Test::Unit, like Java’s JUnit, lacks a hook for the beginning or end of a given TestCase class’s set of tests, there’s no way to accumulate a list of fixtures created and then delete them and/or reload them at the end. That would at least allow you to undo the creation of the fixtures so that the tables were all empty before the next set of fixtures were loaded. Sadly, Test::Unit is not that clever.

I initially fixed this problem a couple of months ago, using a hack that simply refuses to delete and re-create (test data) fixtures if they’re already loaded. That works since the fixture data progressively accumulates and is always clean since changes within tests are rolled back at the end of those tests.

Upon adding a trigger to a Rails migration and then writing a test case that checked to see if it was working, I found the true ugliness. Rails has Migrations, which in my opinion are an excellent feature that works well, and is a more useful generalization of my ordered-list-o-tables and set of table-definition text files. But… when creating the test database, Rails uses the SchemaDumper‘s schema.rb output to create it, instead of using migrations. Talk about principle of least astonishment… I was pretty astonished. We have migrations, which is how we create databases! Great! So let’s use this other thing instead.

Also, SchemaDumper does not in fact dump the schema; it dumps tables and indices only. The RedHillOnRails foreign keys core plugin adds foreign key dumping to this output, but forget about check constraints, triggers, and stored procedures. Those schema objects are ignored, so your test database is not the same as your development (or production) database. Whoops.

I thought of about a dozen ways to deal with this:

1. Abandon triggers and do it all in Rails, make a TODO to fix this later, and get on with feature implementation
2. Add code to the tests to check for the missing schema objects and add them if missing (eww)
3. Replace the db:test:prepare Rake task with one that tells PostgreSQL to copy the database as-is
4. Replace the db:test:prepare Rake task with one that tells PostgreSQL to use pg_dump instead of ActiveRecord::SchemaDumper
5. Hack the PostgreSQL-specific code that SchemaDumper uses to look at the pg_proc and pg_trigger system catalogs and use code similar to the RedHillOnRails Core plugin to dump stored procs and triggers into schema.rb also
6. Just dump using pg_dump into a temp file and parse the output and add that to schema.rb (ewwwwwww)

etc. etc.

I finally found the Migrate Test DB Rake Plugin which simply uses your Rails Migrations to create the test database. Lovely. Except I now had some new problems.

1. rake db:schema:purge for PostgreSQL does dropdb/createdb on the test database to empty it out. That creates a database with no built in procedural langauges, so stored procs won’t work. Adding the language to that database is a DB superuser task, so it couldn’t be done inside of Rake. Fortunately I found that I could solve this via “createlang plpgsql template1” which puts plpgsql in the template database used for creating new databases. Easy.
2. My never-delete-fixtures code got into a fight with my base-data-loader code. They both used Fixtures to load data, and so the base data fixtures made the never-delete-fixtures code think that the test data was already in. So the tests failed due to lacking test data.

I fixed this initially by modifying my BaseDataLoader class to not load base data if RAILS_ENV is ‘test’, and added code to the Migrate Test DB Plugin to set RAILS_ENV to ‘test’ right before running the migrations on the test database. This is a workaround, really, because it still leaves the base data either missing entirely, or duplicated.

Then I switched to the Preload Fixtures plugin which is nice but still leads to FK related errors. It grab the fixture names from your test/fixtures directory and loads all the files it finds, in the order it found them. That fails since alphabetical order and the required table creation order are different in my case.

Fortunately since I’m using the Migrate Test DB Plugin I can just observe the order in which tables were created and tell the Preload Fixtures plugin to do its work in the same order. This is in my environment.rb because that’s where all my project-wide monkeypatching currently lives. (Cleaning that up and maybe plugin-izing it is a TODO for the future.)

Sadly if you run “rake test” it runs ruby as a subprocess in order to do “rake test:units”, “rake test:functionals”, and “rake test:integration”. That means that the migrations are run once (before the tests), but that the preloading is done three times. The second and third times through, though, the preloading fails since it’s trying to delete-then-create each table’s fixtures in table-creation order. So, a patch to preload_fixtures.rb is needed, to ensure that deletes are done first, in the reverse order of table creation. Here’s what the new preload! method looks like:

I’m not sure, but I think there’s an assumption in there that the table name is the same as the fixture name. My patch also makes that assumption, which is true in the case of my project. But in your project you might not have done that, so further hackery might be needed.

So, it all seems to work correctly now, and I’m back to working on my trigger code. If this seems like it took a lot of effort, it did, but I think it’ll be worth it once I start using stored procs and triggers more. That phase begins now.