FLPR

Testing with database constraints

Posted by Pat Mon, 12 Dec 2005 12:38:00 GMT

PostgreSQL calls itself “the world’s most advanced open source database.” I want to use some of the advanced features, dammit! The first, not-so-spectacular-but-still-nice feature that I want to use is foreign key constraints. If you don’t know what they are (read the links!), the gist of it is that you can specify that a field in one table refers to a field in another table. That’s the foreign key part. The constraint is that the record in the other table must exist. Something like:

Table Companies
id integer
name varchar(80)

Table Products
id integer
name varchar(80)
company_id integer  # This is a foreign key

company_id is a foreign key to the id field in the companies table. We haven’t specified it yet, and I’ll show how to do that later, but for now just understand that concept. Any value insert into products(company_id) must exist in companies(id). So for example, if you make products(company_id) = 2982475, and there’s no record in ‘companies’ with id = 2982475, postgresql will give you an error and no insert is made.

Similarly, if you try to delete a record in ‘companies’, and there are records in ‘products’ that reference it, postgresql will again give you an error. <anecdote style=”useless”>This would have been really nice the time I deleted a couple records I “didn’t need” anymore, and then my whole site stopped working. Turns out that there were still a few records somewhere else that referenced these. I should have used console to make the changes…but I didn’t know about it.</anecdote>

This is a good thing because it ensures you maintain referential integrity. Rails is nice and all, but a simple fact of business apps is that the data is far more valuable and has a longer life than the actual app you use to access it. You want to make sure that the database has no chance of having broken references. Bad data at best causes you lots of headaches, and at worst is absolutely worthless.

TDD with fkey constraints

I’m a huge fan of test-driven development. It’s just fun for some reason, and I quickly make lots of progress. Anyway, I wanted to be able to get foreign key constraints into my apps, so I needed to fit them into my process. Basically here’s what I came up with:

1. Write tests 2. Write code to make tests pass 3. Add foreign key constraints 4. Make sure tests still pass

I didn’t know the best way to add foreign key constraints, but then found out about migrations and thought I could give that a shot. I had a tough time getting started, so I made a post to the Rails list and then Robby Russell showed me how. Go read that article to learn how to use constraints in your migrations.

At first my development process went very smoothly. Then it blew up when I created two tables that referenced a single table. The problem is that when you run tests, rake doesn’t actually wipe the db clean like you might expect. Instead it just rolls it back to its initial state, which is to have the fixtures loaded. This means you end up with dirty tables, and then postgresql complains about you violating foreign key constraints. I haven’t explained it well, honestly, but the entire issue is covered here. There are a couple solutions listed there, and I’ll explain what I’ve managed to get working so far.

First solution – delete records in teardown()

def teardown
  self.class.fixture_table_names.reverse.each do |table_name|
    klass_name = Inflector.classify(table_name.to_s)
    if Object.const_defined?(klass_name)
      klass = Object.const_get(klass_name)
      klass.connection.delete("DELETE FROM #{table_name}", 'Fixture Delete')
    else
      flunk("Cannot find class for table '#{table_name}' to delete fixtures")
    end
  end
end

This overrides the teardown method so that when a test is done completes, all the fixtures will be deleted from the db. The only problem with this solution is that I couldn’t use transactional fixtures with it. Testing worked, it was just a bit slow.

Second solution – Make your models paranoid

# neuter fixture deletions and multiple fixture insertions to work with database constraints
class Fixture
  attr_reader :class_name
end 

class Fixtures
  @@inserted_fixture_list ||= {}
  alias :original_insert_fixtures :insert_fixtures

  def insert_fixtures
    return if @@inserted_fixture_list[values[0].class_name]
    @@inserted_fixture_list[values[0].class_name] = true
    unless ActiveRecord::Base.connection.select_one("select 1 from #{fixture_class_to_table_name(values[0].class_name)}")
      original_insert_fixtures
    end
  end

  def delete_existing_fixtures() end

  # compute a fixture's table name from its (known) class name
  def fixture_class_to_table_name(class_name)
    class_name.gsub(/([a-z])([A-Z])/) { |match| "#{$1}_#{$2}" }.downcase.pluralize
  end
end

I did run into a problem here because it turns out that this code doesn’t delete any fixtures, which becomes obvious once you see that delete_existing_fixtures is empty. If I commented out the redefinition, I got a bunch of warnings about a transaction not being in progress. At this point I realized that I wouldn’t really want to delete any objects that are referenced anyway…I might not want them accessible to the user under normal circumstances, but I definitely don’t want them deleted from the database. So I installed Rick Olson’s acts_as_paranoid module and added that to the models that I wanted to be paranoid in the db. Paranoid models don’t actually get deleted when you call destroy(), they just get a deleted_at field set to the timestamp. Pretty slick.

Go with either of these solutions and you should be able to add foreign key constraints and test away to your heart’s content.