PGtools, an open source project hosted on SourceForge, is a collection of shell functions and Unix command-line tools to help build, test and release changes to PostgreSQL database schemas. For the current version, the focus is on Bourne shell tools to aid both unit and functional testing, but the project certainly isn’t limited to this.
- Download the latest release
- Read a tutorial on how to run the example application unit tests.
- See recent news about the project
- Visit the SourceForge project summary page
There’s a sample application in the release under the “example-app” directory. For a tutorial on how to run the example application’s tests, see the unit test demo. This sample application contains a single schema, with one table, one function, and a couple of unit tests. This example also functions as the test for the test scripts. It’s quite limited compared to a full application as described below, but serves as a simple demonstration of the schema testing portions of PGTools.
In a full application, this is our typical usage pattern at Starling Software.
First, the master test script will start by loading a schema into a namespace to ensure that there are no errors in the schema DDL itself. It then runs the database unit tests for the schema to test database functions, views, constraints, and anything else the DBMS is programmed to do. Unit testing is aided by the rollback facilities provided by the PGTools test framework: an individual test will set up the test data, run the tests, verify the results and exit, and the framework will automatically roll back all of the changes to leave a clean schema for the next test. With careful programming behavior between transactions can also be tested.
Next, we load a test schema for application unit tests and run those. Again, the application’s unit test framework (not included in this package, though we’re considering adding some sample Ruby code) takes care of rolling back transactions after tests are complete to avoid test failures cascading into other tests.
After that, we move on to functional tests for command-line and batch programs. For smaller applications, we load a fresh schema for each functional test. If this becomes too slow, we switch to loading one schema before all the tests, and make each test responsible for backing out any changes it made so that the test schema is restored to its pristine state. This is more work to write, and less reliable, but takes less time to run if you’ve got a number of different programs to test or functional test suites to run.
Finally, we load up the full set of schemas afresh, start up some web servers, and do the web testing. For each test, our testing program sets up test data in the schemas and sends requests to the web servers. The web servers read those data, update them, and generate replies. The test suite checks the replies and updated data and then cleans up.
Create a higher-level schema specification language to avoid the large amounts of very-similar SQL syntax one uses for many common database use cases. You can see a start on this in
pgtools/bin/pg-make-get-function(which is written in Ruby, the likely future direction of things such as this) and its use in
Create a pgdiff tool to show the schema differences between the schemas loaded in two namespaces or the schema loaded into a namespace and the DDL for a schema. Eventually, this might be extended to help automate upgrades of existing schemas while preserving the data.
If you have questions or suggestions or, better yet, would like to help out, please contact us.