Just a Theory

Trans rights are human rights

Posts about Test Anything Protocol

Sqitch Homebrew Tap

If Sqitch is to succeed, it needs to get into the hands of as many people as possible. That means making it easy to install for people who are not Perl hackers and don’t want to deal with CPAN. The Sqitch Homebrew Tap is my first public stab at that. It provides a series of “Formulas” for Homebrew users to easily download, build, and install Sqitch and all of its dependencies.

If you are one of these lucky people, here’s how to configure the Sqitch tap:

brew tap theory/sqitch

Now you can install the core Sqitch application:

brew install sqitch

That’s it. Make sure it works:

> sqitch --version
sqitch (App::Sqitch) 0.953

It won’t do you much good without support for your database, though. Currently, there is a build for PostgreSQL. Note that this requires the Homebrew core PostgreSQL server:

brew install sqitch_pg

Sqitch hasn’t been ported to other database engines yet, but once it is, expect other formulas to follow. But if you use PostgreSQL (or just want to experiment with it), you’re ready to rock! I suggest following along the tutorial, downloading, or taking in the latest iteration of the introductory presentation (video of an older version on Vimeo).

My thanks to IRC user “mistym” for the help and suggestions in getting this going. My Ruby is pretty much rusted through, soI could not have done it without the incredibly responsive help!

Looking for the comments? Try the old layout.

Unit Test Your Database!

Gave my talk at PGCon today. I felt that it went well, and was well-received. So here it is for everyone else, for posterity, via download or Slideshare. Enjoy!

Looking for the comments? Try the old layout.

pgTAP 0.14 Released

I’ve just released pgTAP 0.14. This release focuses on getting more schema functions into your hands, as well as fixing a few issues. Changes:

  • Added SET search_path statements to uninstall_pgtap.sql.in so that it will work properly when TAP is installed in its own schema. Thanks to Ben for the catch!
  • Added commands to drop pg_version() and pg_version_num() touninstall_pgtap.sql.in.
  • Added has_index(), index_is_unique(), index_is_primary(), is_clustered(), and index_is_type().
  • Added os_name(). This is somewhat experimental. If you have uname, it’s probably correct, but assistance in improving OS detection in the Makefile would be greatly appreciated. Notably, it does not detect Windows.
  • Made ok() smarter when the test result is passed as NULL. It was dying, but now it simply fails and attaches a diagnostic message reporting that the test result was NULL. Reported by Jason Gordon.
  • Fixed an issue in check_test() where an extra character was removed from the beginning of the diagnostic output before testing it.
  • Fixed a bug comparing name[]s on PostgreSQL 8.2, previously hacked around.
  • Added has_trigger() and trigger_is().
  • Switched to pure SQL implementations of the pg_version() and pg_version_num() functions, to simplify including pgTAP in module distributions.
  • Added a note to README.pgtap about the need to avoid pg_typeof() and cmp_ok() in tests run as part of a distribution.


Looking for the comments? Try the old layout.

pgTAP 0.12 Released

In anticipation of my PostgreSQL Conference West 2008 talk on Sunday, I’ve just released pgTAP 0.12. This is a minor release with just a few tweaks:

  • Updated plan() to disable warnings while it creates its tables. This means that plan() no longer send NOTICE messages when they run, although tests still might, depending on the setting of client_min_messages.
  • Added hasnt_table(), hasnt_view(), and hasnt_column().
  • Added hasnt_pk(), hasnt_fk(), col_isnt_pk(), and col_isnt_fk().
  • Added missing DROP statements to uninstall_pgtap.sql.in.

I also have an idea to add functions that return the server version number (and each of the version number parts) and an OS string, to make testing things on various versions of PostgreSQL and on various operating systems a lot simpler.

I think I’ll also spend some time in the next few weeks on an article explaining exactly what pgTAP is and why you’d want to use it. Provided, of course, I can find the tuits for that.

Looking for the comments? Try the old layout.

pgTAP 0.11 Released

So I’ve just released pgTAP 0.11. I know I said I wasn’t going to work on it for a while, but I changed my mind. Here’s what’s changed:

  • Simplified the tests so that they now load test_setup.sql instead of setting a bunch of stuff themselves. Now only test_setup.sql needs to be created from test_setup.sql.in, and the other .sql files depend on it, meaning that one no longer has to specify TAPSCHEMA for any make target other than the default.
  • Eliminated all uses of E'' in the tests, so that we don’t have to process them for testing on 8.0.
  • Fixed the spelling of ON_ROLLBACK in the test setup. Can’t believe I had it with one L in all of the test files before! Thanks to Curtis “Ovid” Poe for the spot.
  • Added a couple of variants of todo() and skip(), since I can never remember whether the numeric argument comes first or second. Thanks to PostgreSQL’s functional polymorphism, I don’t have to. Also, there are variants where the numeric value, if not passed, defaults to 1.
  • Updated the link to the pgTAP home page in pgtap.sql.in.
  • TODO tests can now nest.
  • Added todo_start(), todo_end(), and in_todo().
  • Added variants of throws_ok() that test error messages as well as error codes.
  • Converted some more tests to use check_test().
  • Added can() and can_ok().
  • Fixed a bug in check_test() where the leading whitespace for diagnostic messages could be off by 1 or more characters.
  • Fixed the installcheck target so that it properly installs PL/pgSQL into the target database before the tests run.

Now I really am going to do some other stuff for a bit, although I do want to see what I can poach from Epic Test. And I do have that talk on pgTAP next month. So I’ll be back with more soon enough.

Looking for the comments? Try the old layout.

pgTAP 0.10 Released, Web Site Launched

Two major announcements this week with regard to pgTAP:

First, I’ve release pgTAP 0.10. The two major categories of changes are compatibility as far back as PostgreSQL 8.0 and new functions for testing database schemas. Here’s a quick example:

SELECT plan(7);

SELECT has_table( 'users' );
SELECT has_pk('users');
SELECT col_is_fk( 'users', ARRAY[ 'family_name', 'given_name' ]);

SELECT has_table( 'widgets' );
SELECT has_pk( 'widgets' );
SLEECT col_is_pk( 'widgets', 'id' );
SELECT fk_ok(
    ARRAY[ 'user_family_name', 'user_given_name' ],
    ARRAY[ 'family_name', 'given_name' ],

SELECT * FROM finish();

Pretty cool, right? Check the documentation for all the details.

Speaking of the documentation, that link goes to the new pgTAP Web site. Not only does it include the complete documentation for pgTAP, but also instructions for integrating pgTAP into your application’s preferred test environment. Right now it includes detailed instructions for Perl + Module::Build and for PostgreSQL, but has only placeholders for PHP and Python. Send me the details on those languages or any others into which you integrate pgTAP tests and I’ll update the page.

Oh, and it has a beer. Enjoy.

I think I’ll take a little time off from pgTAP next week to give Bricolage some much-needed love. But as I’ll be given another talk on pgTAP at PostgreSQL Conference West next month, worry not! I’ll be doing a lot more with pgTAP in the coming weeks.

Oh, and one more thing: I’m looking for consulting work. Give me a shout (david

  • at - justatheory.com) if you have some PostgreSQL, Perl, Ruby, MySQL, or JavaScript hacking you’d like me to do. I’m free through November.

That is all.

Looking for the comments? Try the old layout.

pgTAP pgFoundry Project and Lightning Talk

A couple of quick announcements:

pgTAP on pgFoundry

First, the PostgreSQL community approved my project, so now there is a pgTAP project page, including a couple of mail lists, a bug tracker, and downloads. I uploaded a new version shortly after the project was approved, and 0.03 should be there soon, as well.

pgTAP YAPC::NA Lightning Talk

I gave a Lightning talk at YAPC::NA 2008 in Chicago this afternoon. I’ve now posted the slides for your enjoyment.

Care to help me with development? Want to add your own test functions or make it all integrate better with standard PostgreSQL regression tests? Want to help me get Module::Build or Module::Install to run Perl and PostgreSQL and whatever tests side-by-side, all at once? Join the pgtap-users mail list and join the fun!

Looking for the comments? Try the old layout.

Introducing pgTAP

So I started working on a new PostgreSQL data type this week. More on that soon; in the meantime, I wanted to create a test suite for it, and wasn’t sure where to go. The only PostgreSQL tests I’ve seen are those distributed with Elein Mustain’s tests for the email data type she created in a PostgreSQL General Bits posting from a couple of years ago. I used the same approach myself for my GTIN data type, but it was rather hard to use: I had to pay very close attention to what was output in order to tell the description output from the test output. It was quite a PITA, actually.

This time, I started down the same path, then then started thinking about Perl testing, where each unit test, or “assertion,” in the xUnit parlance, triggers output of a single line of information indicating whether or not a test succeeded. It occurred to me that I could just run a bunch of queries that returned booleans to do my testing. So my first stab looked something like this:

\pset format unaligned
\pset tuples_only
\pset pager
\pset null '[NULL]'

SELECT foo() = 'bar';
SELECT foo(1) = 'baz';
SELECT foo(2) = 'foo';

The output looked like this:

% psql try -f ~/Desktop/try.sql

Once I started down that path, and had written ten or so tests, It suddenly dawned on me that the Perl Test::More module and its core ok() subroutine worked just like that. It essentially just depends on a boolean value and outputs text based on that value. A couple minutes of hacking and I had this:

    SELECT (CASE $1 WHEN TRUE THEN '' ELSE 'not ' END) || 'ok'
        || ' ' || NEXTVAL('__tc__')
        || CASE $2 WHEN '' THEN '' ELSE COALESCE( ' - ' || $2, '' ) END;

I then rewrote my test queries like so:

\echo 1..3
SELECT ok( foo() = 'bar'   'foo() should return "bar"' );
SELECT ok( foo(1) = 'baz', 'foo(1) should return "baz"' );
SELECT ok( foo(2) = 'foo', 'foo(2) should return "foo"' );

Running these tests, I now got:

% psql try -f ~/Desktop/try.sql
ok 1 - foo() should return "bar"
ok 2 - foo(1) should return "baz"
ok 3 - foo(2) should return "foo"

And, BAM! I had the beginning of a test framework that emits pure TAP output.

Well, I was so excited about this that I put aside my data type for a few hours and banged out the rest of the framework. Why was this exciting to me? Because now I can use a standard test harness to run the tests, even mix them in with other TAP tests on any project I might work on. Just now, I quickly hacked together a quick script to run the tests:

use TAP::Harness;

my $harness = TAP::Harness->new({
    timer   => $opts->{timer},
    exec    => [qw( psql try -f )],

$harness->runtests( @ARGV );

Now I’m able to run the tests like so:

% try ~/Desktop/try.sql        
All tests successful.
Files=1, Tests=3,  0 wallclock secs ( 0.00 usr  0.00 sys +  0.01 cusr  0.00 csys =  0.01 CPU)
Result: PASS

Pretty damn cool! And lest you wonder whether such a suite of TAP-emitting test functions is suitable for testing SQL, here are a few examples of tests I’ve written:

-- Plan the tests.
SELECT plan(4);

-- Emit a diagnostic message for users of different locales.
SELECT diag(
    E'These tests expect LC_COLLATE to be en_US.UTF-8,\n'
    || 'but yours is set to ' || setting || E'.\n'
    || 'As a result, some tests may fail. YMMV.'
    FROM pg_settings
    WHERE name = 'lc_collate'
    AND setting <> 'en_US.UTF-8';

SELECT is( 'a', 'a', '"a" should = "a"' );
SELECT is( 'B', 'B', '"B" should = "B"' );

    name lctext PRIMARY KEY

INSERT INTO try (name)
VALUES ('a'), ('ab'), ('â'), ('aba'), ('b'), ('ba'), ('bab'), ('AZ');

SELECT ok( 'a' = name, 'We should be able to select the value' )
    FROM try
    WHERE name = 'a';

SELECT throws_ok(
    'INSERT INTO try (name) VALUES (''a'')',
    'We should get an error inserting a lowercase letter'

-- Finish the tests and clean up.
SELECT * FROM finish();

As you can see, it’s just SQL. And yes, I have ported most of the test functions from Test::More, as well as a couple from Test::Exception.

So, without further ado, I’d like to introduce pgTAP, a lightweight test framework for PostgreSQL implemented in PL/pgSQL and PL/SQL. I’ll be hacking on it more in the coming days, mostly to get a proper client for running tests hacked together. Then I think I’ll see if pgFoundry is interested in it.

Whaddya think? Is this something you could use? I can see many uses, myself, not only for testing a custom data type as I develop it, but also custom functions in PL/pgSQL or PL/Perl, and, heck, just regular schema stuff. I’ve had to write a lot of Perl tests to test my database schema (triggers, rules, functions, etc.), all using the DBI and being very verbose. Being able to do it all in a single psql script seems so much cleaner. And if I can end up mixing the output of those scripts in with the rest of my unit tests, so much the better!

Anyway, feedback welcome. Leave your comments, suggestions, complaints, patches, etc., below. Thanks!

Looking for the comments? Try the old layout.

Test.Simple 0.20 Released

It gives me great pleasure, not to mention a significant amount of pride, to announce the release of Test.Simple 0.20. There are quite a few changes in this release, including a few that break backwards compatibility—but only you’re writing your own Test.Builder-based test libraries (and I don’t think anyone has done so yet) or if you’re subclassing Test.Harness (and there’s only one of those, that I know of).

The biggest change is that Test.Harness.Browser now supports pure .js script files in addition to the original .html files. This works best in Firefox, of course, but with a lot of help from Pawel Chmielowski (“prefiks” on #jsan), it also works in Safari, Opera, and IE 6 (though not in XP Service Pack 2; I’ll work on that after I get my new PC in the next few days). The trick with Firefox (and hopefully other browsers in the future, since it feels lest hackish to me), is that it uses the DOM to create a new HTML document in a hidden iframe, and that document loads the .js. Essentially, it just uses the DOM to mimic the structure of a typical .html test file. For the other browsers, the hidden iframe uses XMLHttpRequest to load and eval the .js test file. Check it out (verbosely)!

I think that this will greatly enhance the benefits of Test.Simple, as it makes writing tests really simple. All you have to do is create a single .html file that looks something like this:

    <script type="text/javascript" src="./lib/JSAN.js"></script>
<script type="text/javascript">
    new JSAN("../lib").use("Test.Harness.Browser");
    new Test.Harness.Browser('./lib/JSAN.js').encoding('utf-8').runTests(

In fact, that’s pretty much exactly what Test.Simple’s new harness looks like, now that I’ve moved all of the old tests into .js files (although there is still a simpl.html test file to ensure that .html test files still work!). Here I’m using JSAN to dynamically load the libraries I need. I use it to load Test.Harness.Browser (which then uses it to load Test.Harness), and then I tell the Test.Harness.Browser object where it is so that it can load it for each .js script. The test script itself can then look something like this:

new JSAN('../lib').use('Test.Simple');
plan({tests: 3});
ok(1, 'compile');
ok(1, 'foo');

And that’s it! Just use JSAN to load the appropriate test library or libraries and go! I know that JSAN is already loaded because Test.Harness.Browser loads it for me before it loads and runs my .js test script. Nice, eh?

Of course, you don’t have to use JSAN to run pure .js tests, although it can be convenient. Instead, you can just pass a list of files to the harness to have it load them for each test:

    <script type="text/javascript" src="./lib/Test/Harness.js"></script>
    <script type="text/javascript" src="./lib/Test/Harness/Browser.js"></script>
<script type="text/javascript">
    new Test.Harness.Browser(

This example tells Test.Harness.Browser to load Test.Builder and Test.More, and then to run the tests in foo.js and bar.js. No need for JSAN if you don’t want it. The test script is exactly the same as the above, only without the line with JSAN loading your test library.

Now, as I’ve said, this is imperfect. It’s surprisingly difficult to get browsers to do this properly, and it’s likely that it won’t work at all in many browsers. I’m sure that I broke the Directory harness, too. Nevertheless, I’m pleased that I got as many to work as I did (again, with great thanks to Pawel Chmielowski for all the great hacks), but at this point, I’ll probably only focus on adding support for Windows XP Service Pack 2. But as you might imagine, I’d welcome patches from anyone who wants to add support for other browsers.

There are a lot of other changes in this release. Here’s the complete list:

  • Fixed verbose test output to be complete in the harness in Safari and IE.
  • Fixed plan() so that it doesn’t die if the object is passed with an unknown attribute. This can happen when JS code has altered Object.prototype (shame on it!). Reported by Rob Kinyon.
  • Fixed some errors in the POD documentation.
  • Updated JSAN to 0.10.
  • Added documentation for Test.Harness.Director, complements of Gordon McCreight.
  • Fixed line endings in Konqueror and Opera and any other browser other than MSIE that supports document.all. Reported by Rob Kinyon.
  • Added support to Test.Harness.Browser for .js test files in addition to .html test files. Thanks to Pawel Chmielowski for helping me to overcome the final obstacles to actually getting this feature to work.
  • Added missing variable declarations. Patch from Pawel Chmielowski.
  • More portable fetching of the body element in Test.Builder. Based on patch from Pawel Chmielowski.
  • Added an encoding attribute to Test.Harness. This is largely to support pure JS tests, so that the browser harness can set up the proper encoding for the script elements it creates.
  • Added support for Opera, with thanks to Pawel Chmielowski.
  • Fixed the output from skipAll in the test harness.
  • Fixed display of summary of failed tests after all tests have been run by the browser harness. They are now displayed in a nicely formatted table without a NaN stuck where it doesn’t belong.
  • COMPATIBILITY CHANGE: The browser harness now outputs failure information bold-faced and red. This required changing the output argument to the outputResults() method to an object with two methods, pass() and fail(). Anyone using Test.Harness.outputResults() will want to make any changes accordingly.
  • COMPATIBILITY CHANGE: new Test.Builder() now always returns a new Test.Builder object instead of a singleton. If you want the singleton, call Test.Builder.instance(). Test.Builder.create() has been deprecated and will be removed in a future release. This is different from how Perl’s Test::Builder works, but is more JavaScript-like and sensible, so we felt it was best to break things early on rather than later. Suggested by Bob Ippolito.
  • Added beginAsync() and endAsync() functions to Test.More. Suggested by Bob Ippolito.

As always, feedback/comments/suggestions/winges welcome. Enjoy!

Looking for the comments? Try the old layout.

Test.Simple 0.10 Released

I’m pleased to announce the first beta release of Test.Simple, the port of Test::Builder, Test::Simple, Test::More, and Test::Harness to JavaScript. You can download it here. See the harness in action here (or verbosely!). This release has the following changes:

  • Changed the signature of functions passed to output() and friends to accept a single argument rather than a list of arguments. This allows custom functions to be much simpler.
  • Added support for Macromedia Director. Patch from Gordon McCreight.
  • Backwards Incompatibility change: moved all “modules” into Test “namespace” by using an object for the Test namespace and assigning the Build() constructor to it. See http://xrl.us/fy4h for a description of this approach.
  • Fixed the typeOf() class method in Test.Builder to just return the value returned by the typeof operator if the class constructor is an anonymous function.
  • Changed for (var in in someArray) to for (var i = 0; i < someArray.length; i++) for iterating through arrays, since the former method will break if someone has changed the prototype for arrays. Thanks to Bob Ippolito for the spot!
  • The default output in browsers is now to append to an element with the ID “test” or, failing that, to use document.write. The use of the “test” element allows output to continue to be written to the browser window even after the document has been closed. Reported by Adam Kennedy.
  • Changed the default endOutput() method to be the same as the other outputs.
  • Backwards incompatibility change: Changed semantics of plan() so that it takes an object for an argument. This allows multiple commands to be passed, where the object attribute keys are the command and their values are the arguments.
  • Backwards incompatibility change: Changed the “no_plan”, “skip_all”, and “no_diag” (in Test.More only) options to plan() to their studlyCap alternatives, “noPlan”, “skipAll”, and “noDiag”. This makes them consistent with JavaScript attribute naming convention.
  • Added beginAsync() and endAsync() methods to Test.Builder to allow users to put off the ending of a script until after asynchronous tests have been run. Suggested by Adam Kennedy.
  • Backwards incompatibility change: Changed the signature for the output() method and friends to take only a single anonymous function as its argument. If you still need to call a method, pass an anonymous function that calls it appropriately.
  • Changed handling of line-endings to be browser-specific. That is, if the current environment is Internet Explorer, we use “\r” for line endings. Otherwise we use “\n”. Although IE properly interprets \n as a line ending when it’s passed to document.write(), it doesn’t when passed to a DOM text node. No idea why not.
  • Added a browser harness. Now you can run all of your tests in a single browser window and get a summary at the end, including a list of failed tests and the time spent running the tests.
  • Fixed calls to warn() in Test.More.
  • Output to the browser now causes the window to scroll when the length of the output is greater than the height of the window.
  • Backwards incompatibility change: Changed all instances of “Ok” to “OK”. So this means that the new Test.More function names are canOK(), isaOK(), and cmpOK(). Sorry ‘bout that, won’t happen again.
  • Ported to Safari (though there are issues–see the “Bugs” section of the Test.Harness.Browser docs for details).

Obviously this is a big release. I bumped up the version number because there are a fair number of backwards incompatibilities. But I’m reasonably confident that they wont’ change so much in the future. And with the addition of the harness, it’s getting ready for prime time!

Next up, I’ll finish porting the test from Test::Harness (really!) and add support for JSAN (look for a JSAN announcement soon). But in the meantime, feedback, bug reports, kudos, complaints, etc.warmly welcomed!

Looking for the comments? Try the old layout.