Wiki: TestDrivenDatabaseDevelopment

Test Driven Database Development tutorial

Monday, July 19
8:30AM
Portland 252

Questions? Please use the comment box on the tutorial description page.

Prerequisites

Attendees should have PostgreSQL 8.4 + contribs installed. Contribs are usually included with binary distributions.

• Downloads.
• Instructions for Ubuntu

Attendees should also have Perl (Windows users grab Strawberry Perl) and Test::Harness 3.0 or higher: To install from CPAN:

cpan Test::Harness

And finally, attendees should download and install pgTAP 0.24. Be sure to install it into a schema named tap>

make TAPSCHEMA=tap
make install
make installcheck

See below for detailed instructions for installing pgTAP on Windows or for building without a compiler.

Creating the Database

Now, assuming that PostgreSQL is running, create the database and add pgTAP to it:

createdb flipr
createlang plpgsql flipr
psql -d flipr -f pgtap.sql

Note that you might have to use -U postgres to connect to PostgreSQL as the super user:

createdb -U postgres flipr
createlang -U postgres plpgsql flipr
psql -U postgres -d flipr -f pgtap.sql

If such is the case for you, you will also need to use -U postgres with pg_prove in the exercises during the presentation.

Installing pgTAP on Windows

PGXS (the PostgreSQL extension build-system) is mainly a Unix build environment. Fortunately, pgTAP requires no C building on 8.4, so installing it should be pretty easy if you’ve already got Perl installed. So if you know where your PostgreSQL install is (probably something like "%ProgramFiles%\PostgreSQL-8.4), you should be able to install pgTAP like this (updated by David Hancock to get the DOS batch syntax working; using Strawberry Perl):

cd c:\wherever\you\put\tap24
copy pgtap.sql.in pgtap.sql
perl.exe -pi.bak -e "s/TAPSCHEMA/tap/g" pgtap.sql
perl.exe -pi.bak -e "s/__OS__/win32/g" pgtap.sql
perl.exe -pi.bak -e "s/__VERSION__/0.24/g" pgtap.sql
perl.exe -pi.bak -e "s/^-- ## //g" pgtap.sql
copy pgtap.sql "%ProgramFiles%\PostgreSQL\8.4\share" 
call pl2bat bin/pg_prove
copy pg_prove.bat "%ProgramFiles%\PostgreSQL\8.4\bin\"

Essentially, we’re just copying pgtap.sql.in to pgtap.sql, and then changing “TAPSCHEMA” to “tap”, __OS__ to “win32”, and __VERSION__ to “0.24”, and then removing the all instances of ”—## ” from the beginning of lines. Then it gets copied to the share directory under you PostgreSQL install.

After that, we use the pl2bat.bat program that should be included with your install of Perl to convert pg_prove to a .bat file, and then install that. If you want, you can put it somewhere else so that it’s in your %PATH%, or else add the PostgreSQL bin directory to your %PATH%—or just use the full path to it to execute it.

Please update this entry with any improvements. It will be used to create permanent permanent instructions to include in the README. Your feedback will really help with that!

Installing pgTAP without a compiler

If you’re installing pgTAP on a box with no compiler (for example, if you you’re on Mac OS X and don’t have Xcode installed), you can build and install pgTAP like so:

cd pgtap-0.24
cp pgtap.sql.in pgtap.sql
perl -pi.bak -e 's/TAPSCHEMA/tap/g' pgtap.sql
perl -pi.bak -e "s/__OS__/`./getos.sh`/g" pgtap.sql
perl -pi.bak -e 's/__VERSION__/0.24/g' pgtap.sql
perl -pi.bak -e 's/^-- ## //g' pgtap.sql
cp pgtap.sql /path/to/pgsql/share/contrib/
cp bin/pg_prove /path/to/pgsql/bin/

Files

The entire presentation, including the Keynote slide deck and all source code examples, are in the presentation GitHub repository. You can clone it with

git clone git://github.com/theory/tddd.git

The files in the can also be fetched from the local network file server:

• files
• pgTAP source
• Test::Harness source

Alternative git repo

A shallow git clone is available at:

git clone git://ec2.dagolden.com/git/oscon-tddd.git