Oninit ifxtap / Informix unit testing
Talk to an expert →

Oninit® ifxTAP — Examples

Status: pre-release. Every example below is a real test file from the ifxTAP source tree (tests/examples/), run against Informix 14.10 and passing as shown. Each file runs in its own transaction that is rolled back, so the tables and routines the examples create are gone when it ends.

1. Check a schema

Build the schema, then assert the shape the application depends on: columns, types, keys and indexes.

-- Check that a schema has the shape the application expects.
EXECUTE PROCEDURE tap_plan(9);

CREATE TABLE customer (
    cust_id  SERIAL PRIMARY KEY,
    email    VARCHAR(80) NOT NULL,
    balance  DECIMAL(10,2) DEFAULT 0
);
CREATE UNIQUE INDEX ux_customer_email ON customer (email);

EXECUTE PROCEDURE tap_has_table('customer');
EXECUTE PROCEDURE tap_has_column('customer', 'email');
EXECUTE PROCEDURE tap_col_type_is('customer', 'email', 'VARCHAR');
EXECUTE PROCEDURE tap_col_not_null('customer', 'email');
EXECUTE PROCEDURE tap_col_default_is('customer', 'balance', '0');
EXECUTE PROCEDURE tap_has_pk('customer');
EXECUTE PROCEDURE tap_col_is_pk('customer', 'cust_id');
EXECUTE PROCEDURE tap_index_is_unique('customer', 'ux_customer_email');
EXECUTE PROCEDURE tap_is_indexed('customer', 'email');

EXECUTE PROCEDURE tap_finish();

2. Test a stored function

tap_is compares typed values. A function result is read into a table first, then compared, including the edge case of a zero balance. 

-- Test a stored function: call it and assert on what it returns.
EXECUTE PROCEDURE tap_plan(4);

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_function_returns('add_interest', 'DECIMAL');
EXECUTE PROCEDURE tap_is((SELECT v FROM result), 220.00, '10% on 200 is 220');

DELETE FROM result;
INSERT INTO result SELECT add_interest(0, 10) FROM sysmaster:sysdual;
EXECUTE PROCEDURE tap_is((SELECT v FROM result), 0, 'interest on zero is zero');

EXECUTE PROCEDURE tap_finish();

3. Test a trigger

Fire the trigger with ordinary DML, then assert on its side effects. The last assertion checks the trigger does not fire on an update.

-- Test a trigger by firing it with DML and checking its side effects.
EXECUTE PROCEDURE tap_plan(5);

CREATE TABLE account (acct_id INTEGER, balance DECIMAL(10,2));
CREATE TABLE audit_log (acct_id INTEGER, note VARCHAR(40));
CREATE TRIGGER trg_account_ins INSERT ON account REFERENCING NEW AS n
    FOR EACH ROW (INSERT INTO audit_log VALUES (n.acct_id, 'opened'));

EXECUTE PROCEDURE tap_has_trigger('account', 'trg_account_ins');

INSERT INTO account VALUES (1, 100);
EXECUTE PROCEDURE tap_is((SELECT COUNT(*) FROM audit_log WHERE acct_id = 1), 1, 'insert wrote one audit row');
EXECUTE PROCEDURE tap_is((SELECT note FROM audit_log WHERE acct_id = 1), 'opened', 'audit note is "opened"');

UPDATE account SET balance = 150 WHERE acct_id = 1;
EXECUTE PROCEDURE tap_is((SELECT COUNT(*) FROM audit_log), 1, 'update does not write an audit row');
EXECUTE PROCEDURE tap_isnt_empty('SELECT * FROM audit_log', 'audit_log has rows');

EXECUTE PROCEDURE tap_finish();

4. Expect errors, and mark known gaps

tap_throws_ok passes when the statement fails with the SQLCODE (or ISAM code) you name. tap_todo marks the next test as a known gap: it is reported, but does not fail the run. 

-- Check that constraints reject bad data, and that good data is accepted.
EXECUTE PROCEDURE tap_plan(6);

CREATE TABLE product (
    sku    CHAR(8) PRIMARY KEY,
    price  DECIMAL(8,2) NOT NULL,
    CHECK (price >= 0)
);

EXECUTE PROCEDURE tap_lives_ok('INSERT INTO product VALUES (''A-100'', 9.99)', 'a valid product is accepted');
EXECUTE PROCEDURE tap_throws_ok('INSERT INTO product VALUES (''A-100'', 5)', -268, 'a duplicate SKU is rejected');
EXECUTE PROCEDURE tap_throws_ok('INSERT INTO product VALUES (''B-200'', NULL)', -391, 'a NULL price is rejected');
EXECUTE PROCEDURE tap_throws_ok('INSERT INTO product VALUES (''C-300'', -1)', -530, 'a negative price violates the CHECK');
EXECUTE PROCEDURE tap_is_empty('SELECT * FROM product WHERE price < 0', 'no negative prices');

EXECUTE PROCEDURE tap_todo('discount rules are not built yet', 1);
EXECUTE PROCEDURE tap_throws_ok('INSERT INTO product VALUES (''D-400'', 0.001)', -530, 'sub-cent prices are rejected');

EXECUTE PROCEDURE tap_finish();

5. Share a fixture with setup and savepoints

Marker comments split the file into tests. -- @setup runs before each one, and the runner rolls back to a savepoint after each, so every test starts from the same fixture and leaves nothing behind. 

-- Share a fixture across tests with setup and teardown; each test gets its own savepoint.
EXECUTE PROCEDURE tap_plan(5);

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 after a deposit');

-- @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_is((SELECT COUNT(*) FROM account), 1, 'setup ran once for this test, not twice');

-- @test objects a test creates do not outlive it
CREATE TABLE scratch (x INTEGER);
EXECUTE PROCEDURE tap_has_table('scratch');

-- @test the scratch table from the previous test is gone
EXECUTE PROCEDURE tap_hasnt_table('scratch');

EXECUTE PROCEDURE tap_finish();

6. Test your own SPL

SPL routines can be tested with the same assertions. This file covers four things that come up often, each in its own -- @test section so it starts from the same two accounts:

  • A routine that raises its own exception. tap_throws_ok matches the code you gave to RAISE EXCEPTION, and tap_throws_like matches its message.
  • An iterator function (RETURN … WITH RESUME). Wrap the call in SELECT * FROM TABLE(f(…)) and use tap_isnt_empty, tap_is_empty or a COUNT(*).
  • A function with a side effect. It cannot be called inside a SELECT, so write a small procedure that calls it, keeps the result in a variable and passes it to tap_is. Assertions work inside your own procedures.
  • A routine with its own exception handler. Check both the normal path and the path that triggers the handler.
-- Test your own SPL: a raised exception, an iterator function, a function with a side effect
-- and a routine with its own exception handler. Each scenario is a "-- @test" section, so each
-- runs in its own savepoint and starts from the same two accounts.
EXECUTE PROCEDURE tap_plan(10);

CREATE TABLE acct (id INTEGER, bal INTEGER);
INSERT INTO acct VALUES (1, 100);
INSERT INTO acct VALUES (2, 200);

-- the code under test
CREATE PROCEDURE withdraw(p_id INTEGER, p_amt INTEGER)
    DEFINE v_bal INTEGER;
    SELECT bal INTO v_bal FROM acct WHERE id = p_id;
    IF v_bal < p_amt THEN
        RAISE EXCEPTION -746, 0, 'insufficient funds';
    END IF;
    UPDATE acct SET bal = bal - p_amt WHERE id = p_id;
END PROCEDURE;

CREATE FUNCTION rich(p_min INTEGER) RETURNING INTEGER, INTEGER;
    DEFINE v_id, v_bal INTEGER;
    FOREACH SELECT id, bal INTO v_id, v_bal FROM acct WHERE bal >= p_min
        RETURN v_id, v_bal WITH RESUME;
    END FOREACH;
END FUNCTION;

CREATE FUNCTION bump(p_id INTEGER) RETURNING INTEGER;
    UPDATE acct SET bal = bal + 1 WHERE id = p_id;
    RETURN 1;
END FUNCTION;

CREATE PROCEDURE safe_div(p_a INTEGER, p_b INTEGER) RETURNING INTEGER;
    DEFINE v_r INTEGER;
    ON EXCEPTION IN (-1202)
        RETURN -1;
    END EXCEPTION;
    LET v_r = p_a / p_b;
    RETURN v_r;
END PROCEDURE;

-- @test a procedure that raises its own exception
EXECUTE PROCEDURE tap_throws_ok('EXECUTE PROCEDURE withdraw(1, 500)', -746, 'withdraw raises -746 when funds are short');
EXECUTE PROCEDURE tap_throws_like('EXECUTE PROCEDURE withdraw(1, 500)', 'insufficient%', 'the message from RAISE EXCEPTION can be matched');

-- @test a procedure that succeeds
EXECUTE PROCEDURE tap_lives_ok('EXECUTE PROCEDURE withdraw(1, 50)', 'a covered withdrawal succeeds');
EXECUTE PROCEDURE tap_is((SELECT bal FROM acct WHERE id = 1), 50, 'the balance dropped by 50');

-- @test an iterator function: use TABLE() to treat its rows as a table
EXECUTE PROCEDURE tap_isnt_empty('SELECT * FROM TABLE(rich(150))', 'rich(150) returns a row');
EXECUTE PROCEDURE tap_is_empty('SELECT * FROM TABLE(rich(9999))', 'rich(9999) returns none');

-- @test a function with a side effect cannot be called inside a SELECT: wrap it in a procedure
CREATE PROCEDURE t_bump()
    DEFINE v_r INTEGER;
    LET v_r = bump(2);
    EXECUTE PROCEDURE tap_is(v_r, 1, 'bump returns 1');
    EXECUTE PROCEDURE tap_is((SELECT bal FROM acct WHERE id = 2), 201, 'bump added 1 to the balance');
END PROCEDURE;
EXECUTE PROCEDURE t_bump();

-- @test a routine that handles its own error: check the normal and the error path
CREATE PROCEDURE t_safe_div()
    DEFINE v_r INTEGER;
    LET v_r = safe_div(10, 2);
    EXECUTE PROCEDURE tap_is(v_r, 5, 'safe_div(10, 2) is 5');
    LET v_r = safe_div(1, 0);
    EXECUTE PROCEDURE tap_is(v_r, -1, 'safe_div(1, 0) returns -1 instead of failing');
END PROCEDURE;
EXECUTE PROCEDURE t_safe_div();

EXECUTE PROCEDURE tap_finish();

Running them

export IFXTAP_JDBC=/path/to/jdbc.jar
export IFXTAP_URL='jdbc:informix-sqli://dbhost:9088/mydb:INFORMIXSERVER=myserver'
export IFXTAP_USER=tester IFXTAP_PASSWORD=...
ifxtap tests/examples

Output (the server and driver version lines are left out here). The six files are one TAP stream: the numbers continue from file to file and the single plan comes last:

TAP version 13
# file: tests/examples/01_schema.sql
ok 1 - table customer should exist
ok 2 - column customer.email should exist
ok 3 - column customer.email should be VARCHAR
ok 4 - column customer.email should be NOT NULL
ok 5 - column customer.balance default should be 0
ok 6 - table customer should have a primary key
ok 7 - column customer.cust_id should be the primary key
ok 8 - index ux_customer_email should be unique
ok 9 - column customer.email should be indexed
# file: tests/examples/02_routine.sql
ok 10 - function add_interest should exist
ok 11 - function add_interest should return DECIMAL
ok 12 - 10% on 200 is 220
ok 13 - interest on zero is zero
# file: tests/examples/03_trigger.sql
ok 14 - trigger trg_account_ins on account should exist
ok 15 - insert wrote one audit row
ok 16 - audit note is "opened"
ok 17 - update does not write an audit row
ok 18 - audit_log has rows
# file: tests/examples/04_errors.sql
ok 19 - a valid product is accepted
ok 20 - a duplicate SKU is rejected
ok 21 - a NULL price is rejected
ok 22 - a negative price violates the CHECK
ok 23 - no negative prices
not ok 24 - sub-cent prices are rejected # TODO discount rules are not built yet
  ---
  message: no error raised; wanted -530
  ...
# file: tests/examples/05_hooks.sql
# test: a deposit raises the balance
ok 25 - balance is 150 after a deposit
# test: the next test starts from the fixture again
ok 26 - balance is back to 100
ok 27 - setup ran once for this test, not twice
# test: objects a test creates do not outlive it
ok 28 - table scratch should exist
# test: the scratch table from the previous test is gone
ok 29 - table scratch should not exist
# file: tests/examples/06_spl.sql
# test: a procedure that raises its own exception
ok 30 - withdraw raises -746 when funds are short
ok 31 - the message from RAISE EXCEPTION can be matched
# test: a procedure that succeeds
ok 32 - a covered withdrawal succeeds
ok 33 - the balance dropped by 50
# test: an iterator function: use TABLE() to treat its rows as a table
ok 34 - rich(150) returns a row
ok 35 - rich(9999) returns none
# test: a function with a side effect cannot be called inside a SELECT: wrap it in a procedure
ok 36 - bump returns 1
ok 37 - bump added 1 to the balance
# test: a routine that handles its own error: check the normal and the error path
ok 38 - safe_div(10, 2) is 5
ok 39 - safe_div(1, 0) returns -1 instead of failing
1..39
# 6 file(s), 39 test(s), 0 failed, 0 file(s) failing

The exit status is 0 here. A failing test, a plan that does not match the number of tests run, or a file that stops on an unexpected error makes it 1, which is what a CI job should check. The TODO test above is reported as not ok but is not counted as a failure.

Tips

  • Call tap_plan(n) first, or tap_no_plan() and end with tap_finish().
  • A statement that fails inside a -- @test section fails that test, and the next test still runs, unless the error ended the transaction (then the rest of the file is abandoned). A statement that fails in the fixture, before the first section, stops the file. Put statements that are expected to fail inside tap_throws_ok or tap_lives_ok.
  • EXECUTE IMMEDIATE, which these two use, cannot run a bare SELECT. Use INSERT, UPDATE, DELETE, DDL or EXECUTE PROCEDURE.
  • Test databases must be logged. The runner refuses an unlogged database.