pgTAP Integration

So you've caught the database testing bug and now you want to integrate pgTAP tests into your project's test suite. Excellent. You've come to the right place. Just find your favorite programming language below and follow along. Don't see your language here yet? If you figure out how to do it, please send us the instructions!

Perl
PHP
Python
PostgreSQL

Perl

Chances are, if you're writing Perl applications, you're already using Test::More and Test::Harness to write and run your tests. If you use Module::Build to build your application, here's how to hook in pgTAP tests so that they run right along with your Perl tests.

Requirements

Module::Build 0.2808_02 or later
TAP::Harness 3.12 or later

Instructions

Put your tests in the t/ directory of your project, just like the Perl tests. Give them a suffix of “.s”, leaving your Perl tests with a suffix of “.t”. You don't need to set any special variables in the test scripts. They can be as simple as this:
```
BEGIN;
SELECT plan( 1 );
SELECT pass('W00t!');
SELECT * FROM finish();
ROLLBACK;
```

In Build.PL, subclass Module::Build like so:

my $class = Module::Build->subclass(code => <<'EOF');
sub tap_harness_args {
    return {
        exec => sub {
            my ( $harness, $test_file ) = @_;
            # Let Perl tests run.
            return undef if $test_file =~ /[.]t$/;
            # Run pgTAP tests through psql.
            return [qw(
                psql
                --username postgres
                --database try
                --quiet
                --no-psqlrc
                --no-align
                --tuples-only
                --set ON_ERROR_ROLLBACK=1
                --set ON_ERROR_STOP=1
                --file
            ), $test_file ] if $test_file =~ /[.]s$/;
        },
    };
}
EOF
$class->new(
   # ...
)->create_build_script;

What this does is tell Module::Build to use TAP::Harness to run tests, rather than Test::Harness, and it tells it to use psql to run tests with file names ending in “.s”.

Tweak this as necessary to match your configuration. For example, the database you connect to may not be “try”, and the user may not be “postgres”. You might also need to specify a host name and port; consult the psql documentation to decide what other options you might need to specify. And of course, you should fill in the proper parameters to new() (you probably already have them).
Now you can run your tests as usual:
```
 ./Build test                                            
t/perl.t......ok   
t/pgtap.s.....ok   
All tests successful.
Files=2, Tests=6,  0 wallclock secs ( 0.03 usr  0.01 sys +  0.03 cusr  0.02 csys =  0.09 CPU)
Result: PASS
```
If you see a bunch of “NOTICE” messages output from PostgreSQL, likely because your test is creating tables, you can either add client_min_messages = warning to your postgresql.conf file and restart PostgreSQL (recommended), or just add SET client_min_messages = warning; to the top of your pgTAP test scripts.
Profit. Have fun!

Support for running pgTAP tests with ExtUtils::MakeMaker is planned, as well. Just bug Schwern to commit the patch and release a new version!

PHP

We're sure there's a way to integrate pgTAP tests with PHPUnit or SnapTest. If you figure it out, please send the instructions to the pgtap-users mail list and we'll add them to this page!

Python

We're sure there's a way to integrate pgTAP tests with PyUnit. Certainly with PyTAP. If you figure it out, please send the instructions to the pgtap-users mail list and we'll add them to this page!

PostgreSQL

If you're developing an add-on module for PostgreSQL, it can be very useful to do test-driven development with pgTAP and pg_prove. But once things are working, it's also handy to allow such tests to be run via make installcheck, which uses pg_regress to run tests and to diff their output to that found in expected output files. So here's how to do that with pgTAP while keeping everything TAPish.

Instructions

Copy pgtap.sql to the root of your project, so that it can be loaded by the regression tests.
Comment-out the declaration of the pg_typeof() function in this bundled copy pgtap.sql. Unfortunately, this function is not (yet) included with PostgreSQL, and since it is defined for pgTAP in C, you can't use it in an uninstalled copy of pgTAP distributed with your module. Regrettably, this means that you have to avoid cmp_ok() in your tests as well, as this otherwise very useful test function relies on pg_typeof(). This issue hass been addressed in PostgreSQL 8.4.
Add these lines to the Makefile:
```
TESTS = $(wildcard sql/*.sql)
REGRESS = $(patsubst sql/%.sql,%,$(TESTS))
REGRESS_OPTS = --load-language plpgsql
```
The first two lines are an easy way to find all of your test files, which should all be *.sql files in the sql/ directory. The REGRESS_OPTS variable ensures that PL/pgSQL will be loaded in the test database, which is of course required by pgTAP.

If you want to allow a make test target that uses pg_prove to run the tests, you can do so by adding the following to the Makefile:
```
test:
	./bin/pg_prove --pset tuples_only=1 $(TESTS)
```
If you're not familiar with how PostgreSQL module Makefiles are constructed, it's actually quite simple to create a portable build system using pg_config and pg_xs. See the extension building documentation for details and the simple Makefile in the parser example in the PostgreSQL documentation.
In the root directory of your project, create a new file, named test_setup.sql. Its contents should look like this:
```
\set QUIET 1

-- Format the output for nice TAP.
\pset format unaligned
\pset tuples_only true
\pset pager

-- Revert all changes on failure.
\set ON_ERROR_ROLLBACK 1
\set ON_ERROR_STOP true

-- Load the TAP functions.
BEGIN;
\i pgtap.sql
```
These are all of the settings that are normally handled by pg_prove, but not by pg_regress, so we have to add them ourselves.
Each of your test files, sql/*.sql should look like so:
```
\unset ECHO
\i test_setup.sql

SELECT plan(1);
SELECT pass('W00t!');
SELECT * FROM finish();
ROLLBACK;
```
The \unset ECHO bit turns off the echoing of all queries, which pg_regress enables by default. This is annoying for TAP tests. Otherwise, all the tests do is load the test_setup.sql file, which makes everything nice and quiet and pretty for TAP, starts the transaction, and loads pgTAP, and the do the actual testing. Be sure to finish() and ROLLBACK at the end of the test!
For each test file in the sql/ directory, create a corresponding file in the expected directory with the same name as the test file, but with the extension “.out”. These files should just have the line \unset ECHO at the top, and then the TAP output from the test. For example, if the above test had been named sql/base.sql, you'd create the file expected/base.out looking like this:
```
\unset ECHO
1..1
ok 1 - W00t!
```
Alas, there seems to be no way to get rid of the \unset ECHO line, but otherwise, you're golden with pure TAP.
And now you should be ready to go! You should be able to either make installcheck as with any other PostgreSQL module, as well as make test. Enjoy!