Download PGUnit distribution for PostgreSQL 8.3:
What is PGUnit?
PGUnit is a xUnit-style framework for stored procedures in PostgreSQL 8.3+. It allows
database developers to write automated tests for existed stored procedures or develop
procedures using concepts of Test Driven Development (TDD). All test cases are stored
in the database, so you don't need any external resources (like files, version control,
command-line utilities etc.) to save tests.
As in traditional xUnit, tests may be grouped in test case; each test-case may have
its own environment initialization code ("fixture preparation code", or setUp block).
The main benefit of PGUnit is that setUp block (usually quite CPU intensive) is
executed only once, and its effect is stored in a savepoint. Then, all tests
are executed from that savepoint, so the fixture initialization overheat is
minimal. All tests are still executed independently, because their effects are
automatically rolled back after the execution.
How to install
It's simple: just download and execute as usual SQL script. The new schema "pgunit"
will be created in you database; it contains all needed functions.
Why not pgTAP
? Why yet another test framework?
Because pgTAP is not so simple and obvious as we need. But, possibly, you
should use pgTAP, not PGUnit - just learn it.
-- dkLab PGUnit: stored procedure unit-test framework for PostgreSQL 8.3
-- Version: (see definition of version() function below).
-- (C) Dmitry Koterov, dkLab (http://dklab.ru)
-- Licensed under BSD license (compatible with PostgreSQL).
-- How to use:
-- 1) Run this SQL in your database. The schema "pgunit" will be created.
-- 2) See samples in pgunit.test_*() routines (PGUnit self-test code).
-- 3) To run all the tests, call:
-- SELECT pgunit.testrunner(NULL);
-- 4) To run a specified test by its name, call:
-- SELECT pgunit.testrunner('first%testname second%testname ...');
-- (use SQL LIKE wildcards; alternatives are separated by spaces).
-- Tests progress is displayed using PostgreSQL NOTICE which allows
-- to watch testing progress in realtime.
-- Changes, improvements and your patches are welcome!
-- Please contact the author: http://forum.dklab.ru/lib/
Test case sample
CREATE FUNCTION pgunit.test_sample () RETURNS testfunc
-- setUp code is executed before ANY test function code (see below).
-- Effect of this execution is persistent only during the code
-- block execution and rolled back after the test is finished.
CREATE TABLE tst(id INTEGER);
-- This is a first test function code. Just check if we can insert
-- into the table created in setUp.
'first test: insert is okay', $sql$
INSERT INTO tst VALUES(1);
PERFORM pgunit.assert_same(1, (SELECT * FROM tst));
-- This is a second test function code.
-- Illustrates that the effect of the first function is not visible.
'second test: effect of previous function is not visible here', $sql$
PERFORM pgunit.assert_same(NULL, (SELECT * FROM tst));
-- This is a third test function code. Illustrate that we may use DECLARE.
'first test: you may use DECLARE in tests', $sql$
FOR i IN 1 .. 10 LOOP
INSERT INTO tst VALUES(i);
PERFORM pgunit.assert_same(i, (SELECT * FROM tst WHERE id = i));
Here is a sample of test output produced by SELECT pgunit.testrunner(NULL) call.
I advisedly broke one test to illustrate how it looks. The output format is PHPUnit-compatible.
NOTICE: PGUnit 2008-11-03 by Dmitry Koterov.
NOTICE: - OK matchedValues
NOTICE: - OK mismatchedValues
NOTICE: ! FAIL executed
NOTICE: - OK executed
NOTICE: - OK exception hook
NOTICE: - OK first test: insert is okay
NOTICE: - OK second test: effect of previous function is not visible here
NOTICE: - OK one value
NOTICE: - OK two values
NOTICE: - OK no matches
NOTICE: Time: 00:00:00
NOTICE: There was 1 failure
NOTICE: 1) executed(pgunit.test_exec)
NOTICE: Failed asserting that two things are same.
NOTICE: expected string <2>
NOTICE: got string <1>
NOTICE: Tests: 10, Failures: 1.
- A little code simplification (undocumented "WHEN others THEN ..."
instead of exception class enumeration).
- Great speed-up while running a bunch of test with a single setUp block.
In this case setUp is executed ONLY ONCE, and the effect of its
execution is stored in a savepoint which is used for all
individually-rollbacked tests. (All tests are still running
independently and are not intersected with each other.)
- Time measurnment of each test execution (thanks to Garrynja).
- Supress NOTICEs generated by a setUp or test code to keep the
execution log clean.
- First release.