Thursday, 24 January 2008

Not very clever.

I've just been formally adding the notion of fixtures to my CLSQL-backed application using Postgres SQL. Fixtures are fixed scenarios for your application that you can run tests against. Atleast that is what I call them!

One of the tasks when dealing with testing database applications is that you have to populate the database for part of your tests. The problem is that you have to also empty the tables before running the same set of tests again otherwise your results are either not repeatable or you will accidentally violate some uniqueness constraints. For example, you might have some users table and one of the uniqueness constraints applies to email addresses.

I came up with a really clever way of keeping the database clean during testing by abusing transactions (hint: whenever someone says they did something clever, it usually isn't!) The idea was to begin and always rollback a transaction for each fixture. This way, the database never actually got populated. Score one for me. The code looks something like this:

(defmacro with-no-db-side-effects (&body body)
(progn ,@body)

(defmacro def-fixture (name args &body body)
`(defun ,name ,args

(defmacro with-fixtures ((&rest fixtures) &body body)
(let (
,@(loop for fixture in fixtures
collect `(,fixture (,fixture))))
(declare (ignorable ,@fixtures))
(def-fixture fixture-a ()
(test test-something-specific-about-scenario-a
(with-fixtures (fixture-a)

Pretty clever (remember the above hint!) It worked as I expected.

At some point during the testing, you must also test the integrity of the relational model. I think you must do this because the integrity of your database should not solely be protected by the application. The IT department will always figure out a way to futz with your data!

One way in which you might try to test the integrity of your database is by entering some data that should be unique. You might try and add the same user twice, for example. So I did:

(test test-something-specific-about-scenario-a
(with-fixtures (fixture-a)
(signals duplicate-key-error (insert-duplicate-user))))

Which immediately results in:

Error POSTGRESQL-ERROR / ERROR:  current transaction is aborted, commands ignored until end of transaction block

Duh! If a query fails within a transaction, you are supposed to roll it back. It especially doesn't make any sense for PGSQL to execute any more of my commands until I rollback the transaction.

Oh well, it was almost clever. I wonder how people solve this problem with testing? I guess another way around it might be to force your fixtures to tell you which tables they populate:

(def-fixture fixture-a (:view-classes '(user foo bar))

Then the with-fixtures macro collects all the view classes and does a clause-less delete:

(defmacro with-fixtures ((&rest fixtures) &body body)
(let ((tables (collect-all-tables-from-view-classes-of-fixtures fixtures)))
`(unwind-protect (progn ,@body)
(progn ,@(loop for table in tables collect `(clsql:delete-records :from ,table))))))


Update: Still not very clever as the success of the above depends on the order of deletion. Dang it.


Anonymous said...


I find your attempt to work in CommonLisp very interesting. Do you do this as part of your work or hobby? Regardless, please continue blogging more about your experience.

TH team I work with Uses Java and Spring framework. Spring has a uni test base class that automatically rolls back the transaction after the tests have run. And, many people see this as a good way of keeping the database clean. I recently characterized this as a hacky way of unit testing but others don't see it quite that way. Glad to see you have issue with such clever ideas ;)

Anyway, you might want to look into a mock database object for unit tests. If your tests are really unit tests, that would work just fine.

Sohail Somani said...

I am using it for a project which I hope will draw a nice profit for me :-)

I think the approach outlined here actually works OK for unit tests. What I ended up doing about the problem earlier was using savepoints. So for better or worse, I had:
(with-savepoint ()
  (signals duplicate-key-error (insert-duplicate-user)))

And that worked fine.

Of course, the real trouble is when you actually start testing your server (i.e., using different connections to the database.) Then this approach is totally useless as no one else will see the transaction until it has been committed. Since the majority of my tests are here right now, all that fighting to get the unit tests to run nicely was a waste of time! What do you do in this case?

I did consider looking at mock objects but found that there is a lot less work involved in using an actual database :-)