Russian version

Add to Del.icio.us

English version

Digg It!

 dkLab | Constructor | PGUnit: stored procedures unit-test framework for PostgreSQL 8.3 

Site map :: Project «Orphus» :: Constructor

Discuss article in the forum Download source archive On the page: What is PGUnit? How to install Quick usage Test case sample Result sample Changelog

Download PGUnit distribution for PostgreSQL 8.3:
dklab_pgunit_2008-11-09.sql

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.

Quick usage

If you have not used unit-tests yet, I recommend you to learn the PHPUnit documentation.

--
-- 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[]
AS
$body$
SELECT pgunit.testcase(
    $setUp$
        -- 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);
    $setUp$,
    ARRAY[
        -- 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));
        $sql$,

        -- 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));
        $sql$,

        -- This is a third test function code. Illustrate that we may use DECLARE.
        'first test: you may use DECLARE in tests', $sql$
            DECLARE
                i INTEGER;
            BEGIN
                FOR i IN 1 .. 10 LOOP
                    INSERT INTO tst VALUES(i);
                    PERFORM pgunit.assert_same(i, (SELECT * FROM tst WHERE id = i));
                END LOOP;
            END;
        $sql$
    ]
);
$body$
    LANGUAGE sql;

Result sample

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:  
NOTICE:  pgunit.test_assert_same
NOTICE:    - OK matchedValues
NOTICE:    - OK mismatchedValues
NOTICE:  pgunit.test_exec
NOTICE:    ! FAIL executed
NOTICE:  pgunit.test_runtest
NOTICE:    - OK executed
NOTICE:    - OK exception hook
NOTICE:  pgunit.test_sample
NOTICE:    - OK first test: insert is okay
NOTICE:    - OK second test: effect of previous function is not visible here
NOTICE:  pgunit.test_wildcard_match
NOTICE:    - OK one value
NOTICE:    - OK two values
NOTICE:    - OK no matches
NOTICE:  
NOTICE:  Time: 00:00:00
NOTICE:  
NOTICE:  There was 1 failure
NOTICE:  
NOTICE:  1) executed(pgunit.test_exec)
NOTICE:  Failed asserting that two things are same.
NOTICE:  expected string <2>
NOTICE:  got string      <1>
NOTICE:  
NOTICE:  FAILURES!
NOTICE:  Tests: 10, Failures: 1.

Changelog

2008-11-09
  - A little code simplification (undocumented "WHEN others THEN ..." 
    instead of exception class enumeration).

2008-11-07
  - 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.

2008-11-03
  - First release.