I’ll show you a short introduction to pgTAP, a testing framework for postgres, including basic theory and short examples. I’ll just show the “simple test script” way, not the xUnit way of testing. My test db is postgres version 9.0.2
The framework around your tests is built on putting your tests in one transaction and rollback it at the end. Additionally statements for starting and showing the results are mandatory:
BEGIN;
SELECT plan();
…your tests are here…
SELECT * FROM finish();
ROLLBACK;
download source package from http://pgtap.org download section, or go directly to http://pgfoundry.org/frs/?group_id=1000389
install it like the common linux way, e.g. the .gz (or just run the pgtap.sql from the extracted sourcedir/sql in your target database):
tar -xzf pgtap-.gz
cd pgtap-
make
make install
make installcheck
This will work if the binary pg_control is in your PATH and you are a db superuser. Most likely you’ll need additional settings like:
env PG_CONFIG=/path/to/pg_config
make installcheck PGUSER=postgres
Detailed instructions are here: http://pgtap.org/documentation.html#Installation, probably you need additional perl modules..
Now let’s test some stuff….
If you need the pgTAP-functions in a different database, just run the sql-script called pgtap.sql (you’ll find it in the subdirectory sql under the sourcedir pgtap-) or include the call to pgtap.sql in your script with \i /pgtap.sql
HelloWorld:
-- 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
\set QUIET 1
BEGIN;
SELECT plan(1);
SELECT pass( 'Hello World !' );
SELECT * FROM finish();
ROLLBACK;
save this as helloworld.txt and call it: psql -U postgres -f helloworld.txt
The snipped output will look like:
1..1
ok 1 - Hello World !
What does this mean ?
1..1 => you ran tests from #1 to #1 (exactly one 😉 )
ok 1 – Hello World ! => your test returned “ok”, it was test number “1” and the string “Hello World!” is the description (==output) of the pass() function.
Yes, this test doesn’t make any sense at all, but it shows the basic format.
Now you’re free to check any detail of your database structure. Let’s start checking some tables and columns. Create a table first with
CREATE TABLE tab_test
(
datetime timestamp without time zone NOT NULL DEFAULT now(),
"number" integer NOT NULL,
price real NOT NULL DEFAULT 0.000000000000000,
currency_id character varying(3) DEFAULT 'eur'::character varying
);
CREATE INDEX idx_number ON tab_test USING btree (number);
CREATE INDEX idx_price ON tab_test USING btree (price);
tables_and_columns.txt:
-- 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
\set QUIET 1
BEGIN;
SELECT plan(3);
SELECT has_table( 'tab_test', 'table tab_test exists' );
SELECT has_column( 'tab_test', 'number' );
SELECT col_type_is( 'tab_test', 'number', 'integer' );
SELECT * FROM finish();
ROLLBACK;
Run the tests: psql -U postgres -f tables_and_columns.txt
The output will be:
1..3
ok 1 - table tab_test exists
ok 2 - Column tab_test.number should exist
ok 3 - Column tab_test.number should be type integer
The call of has_table got the additional parameter “description”, which will be printed out instead of the default message you see for tests no. 2 and 3. It’s possible to put a schemaname as the first parameter to this function call, if your table is not in the default search schema.
To receive a “not ok” result, just modify e.g. the call of has_column to SELECT has_column(‘tab_test’,’id’);
Now the output tells you that something failed:
1..3
ok 1 - table tab_test exists
not ok 2 - Column tab_test.id should exist
# Failed test 2: "Column tab_test.id should exist"
ok 3 - Column tab_test.number should be type integer
# Looks like you failed 1 test of 3
extend tables_and_columns.txt by:
...
SELECT plan(5);
...
SELECT has_index('tab_test','idx_number', 'Index idx_number on tab_test');
SELECT has_index('tab_test','idx_price','currency_id', 'Index idx_price on tab_test');
...
Now you get a helpful hint in the output, which tells you that the table has an index called idx_price, but it is on a different column:
1..5
ok 1 - table tab_test exists
not ok 2 - Column tab_test.id should exist
# Failed test 2: "Column tab_test.id should exist"
ok 3 - Column tab_test.number should be type integer
ok 4 - Index idx_number on tab_test
not ok 5 - Index idx_price on tab_test
# Failed test 5: "Index idx_price on tab_test"
# have: idx_price ON tab_test(price)
# want: idx_price ON tab_test(currency_id)
# Looks like you failed 2 tests of 5
There are pgTAP functions for almost everything in your postgres db, like Triggers, Functions, Schemas, Tablespaces, …. just check the documentation.
It is also possible to create relationships of, or better conditional, tests. This means calling a testfunction only if certain conditions are met.
Again an example will illustrate this best:
The following code fragment will check the table tab_test in schema1 if there exists a primary key on the table. If yes the next test will check if the primary key contains the two columns an_id and another_id. If there’s no primary key found the function fail() will be called. You can call several test functions within collect_tap(…) to build something like a subfunction
...
SELECT CASE WHEN has_pk('schema1','tab_test',NULL) ~* '^ok'
THEN
collect_tap(
col_is_pk ('schema1','tab_test', ARRAY['an_id','another_id'],'table "schema1.tab_test" should have a combined primary key over two columns'),
pass('passed')
)
ELSE
fail('did not find any primary key in table "schema1.tab_test".')
END;
...
Now you should have a basic understanding of how to create a test scenario for testing a db structure, there are a lot more functions available than described in this post, so feel free to play with it.
In my next post I’ll show how to check data….
any hints are highly welcome
Gkoenig's Blog 