Ovid

I had written about Testing With PostgreSQL and today discovered some failing tests. It turns out this is because I had migrated a database down two levels, altered a table, and migrated it back up (I can do this because no one is relying on a production copy). Unfortunately, when I migrated it back up, the new tables didn’t have their correct triggers assigned because the _test_changed_table check was in place. As a result, my test module assumed the testing system was in place.

Each table is assigned three triggers. For example, the table “users” might have:

tr_insert_users
tr_update_users
tr_delete_users

What I now do is count the number of those triggers per table. I check that each table has the correct number of triggers. The SQL for PostgreSQL looks like this:

SELECT relname, COUNT(relname) AS total
FROM   (pg_trigger JOIN pg_class ON tgrelid=pg_class.oid)
JOIN   pg_proc ON (tgfoid=pg_proc.oid)
WHERE  prosrc LIKE '%BEGIN%'
  AND (
       tgname LIKE 'tr_insert%'
    OR tgname LIKE 'tr_update%'
    OR tgname LIKE 'tr_delete%'
  )
GROUP BY relname

If a table is missing triggers, I bail out with a useful error message explaining what the problem is and how to correct it. If a table has the wrong number of triggers, I bail out with a “PANIC” statement, explaining what’s going on and telling the user to investigate. There should either be three triggers or none per table.

After running a test, if you get a failure, you can check to see what’s left in the database. That’s useful for debugging. A quick check to see what’s changed looks like this:

Veure  $ psql veure_test
psql (8.4.2)
Type "help" for help.

veure_test=# select * from _test_changed_table;
 id | table_name  | is_static | inserts | updates | deletes 
----+-------------+-----------+---------+---------+---------
  4 | location    |         1 |       1 |       0 |       0
  8 | email       |         0 |       0 |       0 |       0
  1 | roles       |         1 |       0 |       0 |       0
  3 | star        |         1 |       0 |       0 |       0
  7 | characters  |         1 |       1 |       0 |       0
  2 | wormhole    |         1 |       0 |       0 |       0
  5 | users       |         1 |       0 |       0 |       0
  6 | user_2_role |         1 |       0 |       0 |       0
(8 rows)

You can see that we’ve had one insert on location and one insert on characters. Then you can just select the data from those tables to see what’s in there.

So why am I going to all this trouble? On this personal project, I want it to be easy to use. One fundamental aspect of testing that I’ve found is that many test suites are hard to use or buggy. I am striving very hard to ensure that everything is ridiculously easy to use, with little to no boilerplate (hence, Test::Class::Most) and if it fails, it fails early with complete error messages. If I really screw up, I can rebuild the entire project with one command.

Spending so much time on infrastructure means that I slowed initial development down, but it’s already paying off by being incredibly explicit and helpful every step of the way.