Oninit
Talk to an expert →

← All articles

Technical Articles

Getting Started with ifxTAP: Unit Testing Informix Routines, Triggers and Constraints in Plain SQL

The logic that matters most in an Informix® application often lives in the database: constraints that reject bad rows, triggers that write audit records, and SPL routines that calculate money. It is also the logic least likely to have a test, because testing it has usually meant a hand-built test database and a clean-up script that someone has to keep correct. ifxTAP removes the clean-up. A test is a plain SQL file, and the runner executes each file inside a transaction that it rolls back, so the database is left exactly as it was found.

This article takes you from an empty test database to a passing test, a shared fixture and a CI step. It uses only commands and routines that ship with ifxTAP. ifxTAP is pre-release; the product pages have the full reference.

What you need

  • Informix 14.10 or later, with a logged database. Unlogged databases are not supported, and the runner refuses them.
  • Java 11 or later on the machine that runs the tests.
  • The IBM Informix JDBC driver, supplied separately.

How ifxTAP works

ifxTAP has two parts. The first is a library of SPL routines named tap_*, installed in your test database. They compare values, inspect the catalog and record each result in a session-local temporary table. The second is a Java command-line runner, ifxtap, that connects over JDBC, runs each test file inside BEGIN WORK, collects the recorded results, issues ROLLBACK WORK and prints them as Test Anything Protocol (TAP) version 13. Informix DDL is transactional in a logged database, so a test can create tables, routines and triggers and have them vanish when the file ends. How It Works has the detail.

One test file, start to finish The runner begins a transaction, runs the test file, reads the recorded results, then rolls the transaction back, so nothing the test did remains. 1. BEGIN WORK The runner opens a transaction 2. Run the test Fixtures, then tap_* assertions record results 3. Read results From the session-local temporary table 4. ROLLBACK WORK Rows and objects the test made are gone
One test file, start to finish. The results are printed as TAP 13 after step 3.

Step 1: install the library

Tell the runner where the database is, then install the routines: 

./build.sh                        # once: builds the runner
export PATH="$PWD/bin:$PATH"
export IFXTAP_JDBC=/path/to/jdbc.jar
export IFXTAP_URL='jdbc:informix-sqli://dbhost:9088/mydb:INFORMIXSERVER=myserver'
export IFXTAP_USER=...  IFXTAP_PASSWORD=...
ifxtap --install sql/install.sql
ifxtap --check

The export lines are set once; every ifxtap command after them uses them. ifxTAP is built from source for now, as the Install page describes.

The install runs in one transaction. If a routine with the same name and signature as one ifxTAP creates already exists, whether ifxTAP itself or one of yours, everything is rolled back and the runner says what clashed. A routine of yours with the same name but different parameters does not block the install: it is left alone and the runner warns. A later release upgrades in place with ifxtap --upgrade sql, and sql/uninstall.sql removes the routines.

Step 2: write a test

A test file declares how many assertions it expects, does its work, and ends with tap_finish(). This one tests a stored function. It creates the function and a result table, calls the function and checks what came back: 

EXECUTE PROCEDURE tap_plan(2);

CREATE FUNCTION add_interest(p_balance DECIMAL(10,2), p_pct INTEGER) RETURNING DECIMAL(10,2)
    RETURN p_balance + (p_balance * p_pct / 100);
END FUNCTION;

CREATE TABLE result (v DECIMAL(10,2));
INSERT INTO result SELECT add_interest(200, 10) FROM sysmaster:sysdual;

EXECUTE PROCEDURE tap_has_function('add_interest');
EXECUTE PROCEDURE tap_is((SELECT v FROM result), 220.00, '10% on 200 is 220');
EXECUTE PROCEDURE tap_finish();

The plan, tap_plan(2), is a safety net: if the file stops early or runs an assertion more than once, the count no longer matches and the file fails. The function and the table exist only for this run.

Step 3: run it

ifxtap tests/
1..2
ok 1 - function add_interest should exist
ok 2 - 10% on 200 is 220

Each ok or not ok line is one assertion. A failing assertion adds a diagnostic with the expected and actual values, so a CI log shows what differed without a re-run. The exit status is non-zero if an assertion fails, the count differs from the plan, or the run does not complete. Runner and TAP Output describes the format.

Step 4: choose your assertions

The library covers three kinds of check:

  • Values — tap_ok, tap_is, tap_isnt, tap_is_null and pattern matches. tap_is fails if either side is NULL, so use tap_is_null to test for NULL.
  • Schema — tap_has_table, tap_has_function, tap_function_returns, and checks for columns, types, defaults, indexes, triggers and constraints.
  • Errors — tap_throws_ok expects a given SQLCODE, and tap_lives_ok expects a statement to succeed.

The error assertions are how you test a constraint. This checks that a unique index rejects a duplicate key with SQLCODE −268: 

EXECUTE PROCEDURE tap_throws_ok('INSERT INTO product VALUES (''A-100'', 5)', -268, 'a duplicate SKU is rejected');

Because the statement runs under the assertion, the error is caught and reported instead of ending the file. The Assertion Reference lists every routine.

Step 5: share a fixture between tests

Statements at the top of a file run once and form a fixture for the whole file. When each test needs a clean start, divide the file into sections with marker comments. Each -- @test section runs in its own savepoint, and -- @setup runs before every test: 

EXECUTE PROCEDURE tap_plan(2);
CREATE TABLE account (acct_id INTEGER, balance DECIMAL(10,2));

-- @setup
INSERT INTO account VALUES (1, 100);

-- @test a deposit raises the balance
UPDATE account SET balance = balance + 50 WHERE acct_id = 1;
EXECUTE PROCEDURE tap_is((SELECT balance FROM account WHERE acct_id = 1), 150.00, 'balance is 150');

-- @test the next test starts from the fixture again
EXECUTE PROCEDURE tap_is((SELECT balance FROM account WHERE acct_id = 1), 100.00, 'balance is back to 100');
EXECUTE PROCEDURE tap_finish();

The second test sees a balance of 100, not 150: the savepoint of the first test was rolled back before the second began. A -- @teardown section is also available. The Examples page has complete files for schema checks, functions, triggers, constraints, hooks and your own SPL, with their output.

Step 6: put it in CI

Run the runner as a build step and let the exit status decide:

ifxtap tests/

with the same four variables set in the job's environment. A failed assertion fails the build. The exit status is the signal; the TAP output of a run of several files is one stream with a single plan, so a TAP tool such as prove reads it as one run.

Limitations and what is not rolled back

  • Leave transactions to the runner. The runner refuses COMMIT, ROLLBACK and BEGIN WORK in a test file (it does not run them), because a commit would keep your fixtures.
  • Some effects are not rolled back: sequence values, writes to RAW tables, routines that commit (the runner fails the file when one does, but cannot undo it), and anything outside the database such as SYSTEM calls. Clean those up in a -- @teardown section; How It Works lists them.
  • Write each file to stand alone. Every file gets its own transaction, so a file should create the fixtures it needs rather than rely on an earlier one.

Where next

Writing Tests covers plans, NULL handling and expected errors in depth. Install has the privileges and upgrade details. If you are testing your own SPL, the sixth example on the Examples page shows a raised exception, an iterator function and a function with a side effect.