Currently, the way we do this on LedgerSMB is to have SQL scripts which insert records into a test results table. This table is created in a transaction which runs the tests, and the tests always rollback. A wrapper program in Perl runs these scripts, checks the results of the test_result table, and reports errors back up to the test harness. There are some advantages here, including the fact that tests can be run on a live, production DB without contaminating it (and thus also move between QA and support as a tool), and we don't require any additional components to run the tests. In fact, you can run the tests passably using just psql.

One possible direction we are considering going is to move the test scripts to pgTAP and also centralize test data logic. This would seem to give us more options and might allow us to do exception testing and expand the reach of the test scripts, but it would mean additional dependencies for testing, and would likely rule out as a general support tool or one that could be run as part of the installation.

Which approach is best? The current one or pgTap? Or would it be better to adopt a hybrid approach?

  • I found this to be a useful, though high level article about data validation. It's more about how to approach the testing than specific tests, but it does contain some specific tests and has a lot of useful information: msdn.microsoft.com/en-us/library/gg261774.aspx
    – Sam Woods
    Commented Oct 8, 2012 at 18:19
  • that's an interesting link and we will definitely have to include it in our testing strategy long-term. However, the larger question right now is how best to unit test db components. Do people think being dependent on a third party TAP harness in the db is worth it? Commented Oct 9, 2012 at 2:11
  • 1
    I've never used pgTAP, so I can't speak directly to that, but here's walkthroughs of two sql unit testing frameworks. Visual Studio's built in database unit tests: databasejournal.com/features/mssql/article.php/3921471/… and red-gate's sql unit: troyhunt.com/2012/02/making-unit-tests-in-sql-server-first.html
    – Sam Woods
    Commented Oct 16, 2012 at 20:13


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.