Oninit® ifxTAP — Generating Tests from Your SPL

Status: pre-release. The tool is tools/spltest.py in the ifxTAP source tree. It needs Python 3 (tried with 3.14) and no other package, and the checks behind it run on Informix 14.10 and 15.0.

Testing Your Own SPL shows how to write tests for a routine by hand. This page is about the other way in: point a tool at a file of routines and let it write the first tests for you. It reads the SPL, finds every SQL statement and every call to another routine, gives each parameter and variable a value of the right type, and writes SQL that runs each statement once, and then the whole routine once, inside a transaction that is rolled back.

What that proves is that every statement is valid against your database and that the routine runs from top to bottom with typical values. It does not check what the routine returns or changes; that is what the tap_* assertions are for. Treat the output as a smoke test and a starting point.

1. Run it on a routine

Take this procedure, saved as transfer.sql:

CREATE PROCEDURE transfer_funds(p_from INTEGER, p_to INTEGER, p_amount DECIMAL(10,2), p_note VARCHAR(40))
    DEFINE v_balance DECIMAL(10,2);
    DEFINE v_when    DATETIME YEAR TO SECOND;

    LET v_when = CURRENT;
    SELECT balance INTO v_balance FROM account WHERE id = p_from;
    IF v_balance < p_amount THEN
        RAISE EXCEPTION -746, 0, 'insufficient funds';
    END IF;
    UPDATE account SET balance = balance - p_amount WHERE id = p_from;
    UPDATE account SET balance = balance + p_amount WHERE id = p_to;
    INSERT INTO transfer_log (from_id, to_id, amount, note, at)
        VALUES (p_from, p_to, p_amount, p_note, v_when);
    COMMIT WORK;
END PROCEDURE;

python3 tools/spltest.py transfer.sql --output out

Routine             : transfer_funds (procedure)
Parameters          : 4   Variables: 2
SQL statements      : 4   (not run on their own: 0)
Routine calls       : 0
Txn SQL neutralized : 1
Output              : out

It found four SQL statements (the SELECT, the two UPDATEs and the INSERT) and one COMMIT WORK, which it will switch off in the copy of the routine it tests (section 4). Nothing was sent to the database yet.

2. The values it guessed, and how to change them

Each parameter and each DEFINEd variable gets a value from its type, or from its DEFAULT when that is a plain number or string. Every guess is written to out/controller.json, with "source": "guess".

A value of 1 may well not exist in your account table. To use values that do, put them in a small JSON file and pass it with --controller. The names are the parameter and variable names:

{ "p_from": 1, "p_to": 2, "p_amount": 500, "p_note": "rent" }

python3 tools/spltest.py transfer.sql --output out --controller values.json

The overrides are used in every statement test and in the routine test, and controller.json records "source": "controller" for them. Change the values and run again to see another path through the routine: with 500 in an account that holds 100, the routine takes the RAISE EXCEPTION branch.

3. What it wrote

Here is statement_tests.sql for the example (the first two of the four):

-- sql_001 SELECT source lines 6-6
BEGIN WORK;
SELECT balance FROM account WHERE id = 1;
ROLLBACK WORK;

-- sql_002 UPDATE source lines 10-10
BEGIN WORK;
UPDATE account SET balance = balance - 1.0 WHERE id = 1;
ROLLBACK WORK;

The INTO list of the SELECT is gone (it is SPL, not SQL), and p_from and p_amount are now 1 and 1.0. Only names that are parameters or variables of the routine are replaced; a column called balance stays a column, and so does the column list of an INSERT or the left side of a SET.

4. Run the output

The generated files are plain SQL for dbaccess. They are not ifxTAP test files: they contain their own BEGIN WORK and ROLLBACK WORK, which the ifxtap runner refuses, because it owns the transaction. Run them against a test database that has your tables:

dbaccess mytestdb out/statement_tests.sql
dbaccess mytestdb out/routine_test.sql

A clean run prints "Started transaction", the row counts and "Transaction rolled back" for each statement, and for the routine "Routine created", "Routine executed", "Routine dropped". An error number means that statement, or the routine, does not run with these values: an unknown column (-217), an unknown table (-206), a constraint (-268), or the routine's own RAISE EXCEPTION (-746 in the example, when the amount is more than the balance). The comment above each statement gives its source line in your file.

The statements really run, so a missing row or a constraint stops them, and a rolled-back INSERT still uses up a SERIAL number. Use a database you can afford to touch.

The test copy differs from your routine in these ways only:

• It is called spltest_<name>, so your own routine is not replaced.
• Every COMMIT, ROLLBACK and BEGIN WORK statement, wherever it is on its line, becomes a { SPLTEST: ... } comment, so the routine cannot end the transaction the test is wrapped in. In the example, line 14 is reported. ROLLBACK TO SAVEPOINT and a bare BEGIN block are left alone.
• A routine that calls itself calls the copy.
• A routine with OUT or INOUT parameters is called through a small generated wrapper procedure that gives them values.
• A trigger routine is created but not called, because it only runs when its trigger fires; fire it with an INSERT, UPDATE or DELETE on the table.

5. Statements it will not run on their own

These are listed in statement_tests.sql as comments, and in controller.json with "executable_in_isolation": false and the reason:

• The statement uses a table, index or view that the routine creates earlier (a temporary table, for example). It does not exist until the routine runs; the routine test covers it.
• WHERE CURRENT OF, which needs the cursor of a FOREACH.
• UNLOCK TABLE, which cannot be issued inside a transaction.
• A variable whose type has no literal (ROW, collections, BYTE, TEXT).
• The names o and n (or whatever the trigger's REFERENCING clause calls them) in a trigger routine.

Dynamic SQL, the text you pass to EXECUTE IMMEDIATE, is listed under "dynamic" in controller.json and is not run on its own: the tool cannot know what text the routine will build.

6. Dynamic SQL and --allow-dynamic-sql

The routine test runs your routine for real, and the safety of the whole approach is the ROLLBACK WORK around it. A routine that ends the transaction defeats that and keeps its changes. The tool switches off every COMMIT it can see, but it cannot see one that is built at run time, as in LET v_s = 'COM' || 'MIT WORK'; EXECUTE IMMEDIATE v_s;. So:

• If the routine runs EXECUTE IMMEDIATE on anything but a single string literal, no routine test is generated. routine_test.sql says why and names the line, and controller.json has "generated": false. The statement tests and the copy are still written.
• If the literal itself holds a COMMIT or ROLLBACK, the test is never generated.
• --allow-dynamic-sql generates the routine test anyway. It means: I have read the dynamic SQL in this routine and it does not end the transaction. It does not make the tool check anything more. Use it for routines whose dynamic SQL is a DELETE or CREATE you can see, and read the routine first.

7. A file of several routines, and the other options

It extracts every SQL statement in the routine, not only queries and updates: CREATE, DROP and ALTER, TRUNCATE, RENAME, GRANT and REVOKE, UPDATE STATISTICS, SET LOCK MODE and the other SET statements, LOCK TABLE, and EXECUTE PROCEDURE and EXECUTE FUNCTION. The type of each is in controller.json. A DDL statement that runs on its own is rolled back too, because Informix DDL is transactional in a logged database.

From here to a real test

Once the generated statements and the routine run, write the assertions that matter in an ordinary ifxTAP file, as in Testing Your Own SPL: call the routine, check what it returned and what it changed, and check the failure you expect with tap_throws_ok. The values in controller.json are a good start for the arguments.