Tests for (PL/pg)SQL modules

Last modified
Saturday, May 20, 2017 - 12:28

Introduction

In order to assess correct operation of its stored procedures (which mostly encapsulate queries), LedgerSMB uses pgTAP as the testing framework. pgTAP is a PostgreSQL extension which provides test primitives much like Test::More does for the Perl tests, e.g.:

  • ok()
  • is()
  • isnt()

In addition to these well-known primitives, it also provides primitives specific for database testing, e.g.:

  • results_eq()
  • results_ne()
  • is_empty()
  • isnt_empty()

By using pgTAP, LedgerSMB leverages the same test driver application ("prove") for running the database tests as for running the Perl tests.

Test structure

The database test scripts are located in the xt/ folder. This folder stores the test cases which should not be executed when installing a new LedgerSMB server. In case of the database tests this condition is indeed true: the database tests serve to validate changes during development, but don't serve a purpose for end-users.

The database test script files all match the pattern '42-*.pg'. The "pg" extension is used by the "prove" test driver to identify which extension to delegate the execution of the script to.

Each database script executes a series of tests inside a transaction which is rolled back after execution. This way, tests can be executed multiple times against the same database without the need to clean the database after each test run. The basic skeleton for the tests looks like:

BEGIN;
 
  -- Load the TAP functions.
  CREATE EXTENSION pgtap;
 
  -- Plan the tests.
  SELECT plan(51);
 
  -- Add data
  \i SQL/modules/test/Base.SQL
 
  -- Insert testscript specific data here
 
  -- TESTS GO BELOW HERE
 
ROLLBACK;

As shown in the code block, test data is loaded from a file located in sql/modules/test/Base.sql.

Testscript naming

Testscripts are named 42-<something>.pg. The <something> is the name of the module that's being tested, as located in sql/modules/.

Test execution

Tests can be executed using the "prove" test driver:

$ prove --pgtap-option dbname=<your-ledgersmb-test-database> --pgtap-option username=postgres xt/42-<your-testscript>.pg