Oninit® ifxTAP — Writing Tests

Status: pre-release.

What we are trying to do

Application code gets unit tests: small, automatic checks that say what should be true and fail loudly when it stops being true. Database code rarely does. Schemas, constraints, stored procedures and triggers are changed by hand and checked by eye, and a mistake often shows up only when an application breaks. ifxTAP gives database code the same safety net, so that a change to a table, a procedure or a trigger can be checked in seconds, by anyone, on every change.

A test answers a plain question about the database:

Does the schema still have the shape the application depends on: the columns, types, keys and indexes?
Does this stored procedure or function return the right result, including the awkward cases?
Does this trigger fire when it should, and stay quiet when it should not?
Does the database refuse bad data, and accept good data?

How a test is built

Every test follows the same three steps:

The three steps of a test
Step	What you do	In ifxTAP
Arrange	Set up the situation: create the tables and insert the rows the test needs	Ordinary SQL, or a `-- @setup` section
Act	Do the thing being tested: call the procedure, run the statement, fire the trigger	Ordinary SQL
Assert	State what should now be true	The `tap_` routines, such as `tap_is` and `tap_throws_ok`

Each assertion records a pass or a fail with a description you wrote, so a failing run says which expectation was not met and what the actual value was.

Why it is safe to run

The runner wraps each test file in a transaction and rolls it back at the end. Tables, rows and routines a test creates are undone, so the database is left as it was found, a test can be run again and again, and there is no cleanup to write. The results are kept aside and printed as Test Anything Protocol (TAP) output, and the runner exits with a non-zero status when something fails, so the same tests can run by hand or in a CI pipeline.

Run tests against a test database, not production. They execute real statements, and a rollback cannot undo everything: sequences, files and SYSTEM calls fall outside a transaction (see teardown below).

A first test

A test is an .sql file. This one checks an existing customer table, so it needs no setup and no cleanup of its own.

EXECUTE PROCEDURE tap_plan(4);

EXECUTE PROCEDURE tap_has_table('customer');
EXECUTE PROCEDURE tap_has_column('customer', 'cust_id');
EXECUTE PROCEDURE tap_col_type_is('customer', 'cust_id', 'SERIAL');

EXECUTE PROCEDURE tap_is(
    (SELECT COUNT(*) FROM customer), 0, 'customer starts empty');

EXECUTE PROCEDURE tap_finish();

See Examples for complete tests that build their own tables, routines and triggers.

Plan

tap_plan(n) declares how many tests the file will run, and the runner checks the count. Use tap_no_plan() when the count is not known up front, and tap_finish() to close the file.

NULL handling

tap_is fails when either value is NULL, with a diagnostic. Two NULLs are not considered equal. Use tap_is_null and tap_isnt_null to test for NULL.

Fixtures, setup and teardown

Statements at the top of a file run once, as the fixture for the whole file. To give each test a clean start, divide the file into sections with marker comments:

Section markers
Marker	Meaning
`-- @setup`	Statements that run before every test
`-- @teardown`	Statements that run after every test, even when the test failed
`-- @test name`	Starts a test; its statements run until the next marker

The runner sets a savepoint before each test, runs setup, the test and teardown, then rolls back to the savepoint. Whatever the test did, including tables and routines it created, is undone before the next test starts, and a TODO set in one test does not carry into the next. Each test name is printed as a # comment in the TAP output.

A file with no markers behaves as before: it is all fixture, in one transaction.
A statement that fails inside a test fails that test only. The runner rolls back to the savepoint and carries on with the next test.
If an error ends the whole transaction, the rollback to the savepoint fails and the rest of the file is abandoned.
Use teardown for state a rollback cannot undo, such as sequences, files or SYSTEM calls.
Markers must be a whole line in exactly this form. Setup and teardown sections may appear more than once and are combined.

See Examples for a complete file.

Skipped and to-do tests

tap_skip covers tests that depend on a particular server release. tap_todo, or the pair tap_todo_start and tap_todo_end, marks tests expected to fail for now. tap_diag adds a diagnostic line to the output.

Expected errors

tap_throws_ok runs a statement through EXECUTE IMMEDIATE inside an ON EXCEPTION block and passes when it fails with the SQLCODE or ISAM code you name. tap_throws_like and tap_throws_matching match the error text instead. Informix does not use PostgreSQL SQLSTATE codes, so tests match on SQLCODE and ISAM code. Some errors end the enclosing transaction; the runner checks after every test and, if that has happened, abandons the rest of the file.

To test your own stored procedures and functions, see Testing Your Own SPL.