Tuesday, April 24, 2007

DBUnit + Sequence numbers + Foreign keys = Nightmare!

Setting up my unit tests with reliable fixtures is turning out to be quite a headache.

Sparing you arcane application-speak let me use the classic simple EMPLOYEE-DEPARTMENT example to illustrate my problem.

  • We have the typical EMPLOYEE table and the DEPARTMENT table
  • Both use database-generated ids (sequence numbers) as primary keys.
  • An EMPLOYEE belongs to a DEPARMENT.
  • The DEPT_ID field in the EMPLOYEE table is a foreign key into the ID field of the DEPARTMENT table.
So far, so good.

Now, consider a method in the EmploymentDao class that returns all known employments. In SQL speak, it executes

Select e.emp_name, d.dept_name
From employee e, department d
Where e.dept_id=d.id

In order to test this method I need to set up the database with necessary data. I would like to be able to use something like DBUnit to set up my fixtures. However, I have to populate the DEPARTMENT table with at least one department record. Then, I need to get the ID field of that record to insert a record into the EMPLOYEE table. How does one do that ? This is crucial for a test such as

public void testGettingAllEmployments() {
List employments = employmentDao.getAllEmployments();
assertNotNull("Empty list of employments", employments);

Even with a simplification of suppressing foreign key constraints, I will have to do something like:
  • Insert a record into the DEPARTMENT table with a DBUnit fixture.
  • Insert a record in the EMPLOYEE table (with a bogus dept_id)
  • Then read the record, from the DEPARTMENT table to get at its id
  • Update the record in theEMPLOYEE table to set the dept_id
Wow! That's a lot of preamble -- not to mention using of the very DAO methods I want to test!!

Tweety thoughts

    follow me on Twitter