I ported a couple tests from Krang to my latest project today and it occurred to me to share them. The tests go by the ugly names 'aaa_dbcount.t' and 'zzz_dbcount.t'. Their job is to make sure that none of the other tests in our test suite are leaking data in the database.
The first script, 'aaa_dbcount.t' is roughly:
foreach database
open "$dbname.count.txt"
foreach table
print count(*) into "$dbname.count.txt"
And 'zzz_dbcount.t' is roughly:
foreach database
open "$dbname.count.txt"
foreach line
get count(*) for the table
is($old_count, $new_count)
The tests in zzz_dbcount.t fail when a test that runs after aaa_dbcount.t doesn't clean up after itself. This prevents one run from affecting the next one which can be a very difficult bug to catch otherwise.
When a failure happens I can use the tests to narrow it down. I pick a suspect and run:
make test TEST_FILES="t/aaa_dbcount.t t/suspect.t t/zzz_dbcount.t"
I've thought about doing something that looks at the writable areas of the filesystem too. I bet that would have caught a nasty bug in Krang that I noticed by accident recently - deleting a story deletes the published files (good) but leaves behind empty directories (bad).
So why not wrap this up as a Test:: module? I might but it's a very small amount of code and abstracting "foreach database" and "foreach table" so it'll work across DBD drivers seems hard.
-sam
Maybe one of the DBI abstraction modules has something for iterating over databases.
Another simple test might be to make sure that columns and other table metadata aren't altered.