PL/pgSQL doesn't have much in the way of developer tools, and writing automated tests for stored procedures can be much easier when you know what you haven't tested. Code coverage allows you to see which parts of your code haven't been executed.
Piggly is a tool (written in Ruby, but you can write your tests in any language) to track code coverage of PostgreSQL PL/pgSQL stored procedures. It reports on code coverage to help you identify untested parts of your code.
How Does It Work?
Piggly tracks the execution of PostgreSQL's PL/pgSQL stored procedures by recompiling the stored procedure with instrumentation code. Basically, RAISE WARNING statements notify the client of an execution event (e.g., a branch condition evaluating to true or false). It records these events and generates prettified source code that is annotated with coverage details.
- Readable and easily-navigable reports (see example)
- Language agnostic - write your tests in Ruby, Python, Java, SQL scripts etc
- Branch, block, and loop coverage analysis
- Instrumenting source-to-source compiler
- Low test execution overhead
- Reduced compilation times by use of disk caching
- Possible to aggregate coverage across multiple runs
- Not all PL/pgSQL grammar is currently supported, but the grammar is easy to modify
- Cannot parse nested dollar-quoted strings, eg $A$ ... $B$ ... $B$ ... $A$
- SQL statements are not instrumented, so their branches (COALESCE, WHERE-clauses, etc) aren't tracked
gem install treetop
- The ruby-pg driver:
gem install pg
- The examples require ActiveRecord:
gem install activerecord
How to Install
To install the latest from github:
$ git clone git://github.com/kputnam/piggly.git $ cd piggly $ bundle install $ bundle exec rake spec $ rake gem $ gem install pkg/*.gem --no-rdoc --no-ri
To install the latest release:
$ gem install piggly
Your stored procedures must already be loaded in the database. Configure your database connection in
a file named
config/database.yml relative to where you want to run piggly. You can also specify the
-d PATH to an existing configuration file. The contents of the file follow ActiveRecord conventions:
piggly: adapter: postgresql database: cookbook username: kputnam password: secret host: localhost
Here we'll add some stored procedures to the database we described above:
$ cat example/proc/*.sql | psql -U kputnam -h localhost cookbook
Note the connection is expected to be named
piggly but you may specify the
-k DATABASE option to
use a different connection name (eg
-k development in Rails). See also
Now you are ready to recompile and install your stored procedures. This reads the configuration from
./config/database.yml relative to the current working directory.
$ piggly trace compiling 5 procedures Compiling scramble Compiling scramble Compiling numberedargs Compiling snippets Compiling iterate tracing 5 procedures
This caches the original version (without instrumentation) in
piggly/cache so you can restore them
later. Piggly will only recompile procedures that have changed in the database since it last
made a copy in
WARNING: piggly fetches your code from the database and replaces it (in the database) with the
instrumented code. If you run
piggly trace twice consecutively, the second time will cause an error
because you are trying to re-instrument code that has already been instrumented. You need to run
piggly untrace or restore your original stored procedures manually before you can trace them again.
Now you're ready to execute your tests. Make sure your connection is configured to log
messages to a file -- or you can log them to
STDERR and redirect that to a file. For instance you
$ ant test 2> messages.txt $ make test 2> messages.txt etc.
To build the coverage report, have piggly read that file in by executing
piggly report < messages.txt,
piggly report -f messages.txt. You don't actually need the intermediate file, you can pipe your
test suite directly in like
ant test 2>&1 | piggly report.
Once the report is built you can open it in
Running the Examples
$ cd piggly $ bundle install $ cat example/README ... $ ./example/run-tests compiling 5 procedures Compiling scramble Compiling scramble Compiling numberedargs Compiling snippets Compiling iterate tracing 5 procedures Loaded suite /home/kputnam/wd/piggly/example/test/iterate_test Started ...... Finished in 0.199236 seconds. 6 tests, 6 assertions, 0 failures, 0 errors, 0 skips Test run options: --seed 25290 clearing previous coverage storing coverage profile creating index creating reports reporting coverage for scramble reporting coverage for scramble reporting coverage for numberedargs reporting coverage for snippets reporting coverage for iterate: +0.0% block, +0.0% branch, +0.0% loop restoring 5 procedures OK, view /home/kputnam/wd/piggly/example/piggly/reports/index.html $ ls -alh example/reports/index.html -rw-r--r-- 1 kputnam kputnam 1.4K 2010-04-28 11:21 example/reports/index.html
Bugs & Issues
Please report any issues or feature requests on the github tracker.