From: chap at anastigmatix.net (Chapman Flack)
Date: Sun, 20 Sep 2015 14:21:03 -0400
Subject: [Pljava-dev] conditional SQL in DDR, and a testing idea
In-Reply-To: <55FEBCFA.8060502@anastigmatix.net>
References: <55FEBCFA.8060502@anastigmatix.net>
Message-ID: <55FEF90F.5080108@anastigmatix.net>

By the end of the last installment, we had a deployment descriptor file
that could contain things like

    always-executed sql stuff;

    BEGIN PostgreSQL
      vendor-specific sql stuff
    END PostgreSQL;

and even

    BEGIN PostgreSQL-withWidgets
      sql stuff to do with widgets
    END PostgreSQL-withWidgets;

where some bit of SQL in the always-executed part could test
conditions and add 'PostgreSQL-withWidgets' into a GUC variable
listing which 'BEGIN foo' tags should be honored.

But we didn't have:

BEGIN PostgreSQL-myTests
 SELECT plan(3)

 SELECT is(findAnswer(), 42, 'wrong answer found')

 SELECT throws_ok('SELECT ''9780393040028''::isbn13', '22P02',
  'bad checkdigit not caught')

 SELECT cmp_ok('+60 38921234'::telno, '>', '+30 2244041234'::telno,
   'telnos should be ordered as if left-aligned')

 SELECT finish()
END PostgreSQL-myTests;


[Ahem. For clarity I've abused the notation slightly. DDR syntax
would require BEGIN ... / END ... wrapping each statement individually.
That would be annoying to write if we didn't have a DDR generator,
but happily, we do.]


If you have used pgTap (http://pgtap.org/) then you recognized what
those tests were doing (out of the huge number of useful tests that
are very easy to write using pgTap). They were probably clear even if
you haven't used it before.

TAP (the Test Anything Protocol) defines a simple, more or less human
readable but machine parsable, text format for reporting tests and
results, so all those testing functions just return SETOF text and,
if a normal client were running them, it could just collect and parse
the output with any of the TAP reporting libraries out there. For
example, in Java, there's tap4j (http://tap4j.org/tap4j/). Both pgTap
and tap4j have copacetic licenses (PostgreSQL/BSD and MIT, respectively).

The only magic to make them automatic within a deployment descriptor:
I've already proposed a GUC variable, pljava.implementortags, that
can be set to the list of tags the DDR installer will honor, and treat
any SQL code wrapped in those tags as additional, ordinary installation
commands to be executed.

One *more* GUC variable, say pljava.implementortags.pgtap, could
be set to a list of tags around test code that should be run. When
the installer finds a tag that's in _this_ list, it treats the
tagged code specially. It can make sure pgTap has been installed, or
make it so if it isn't, and manage savepoints and rollbacks the same
way a client like pg_prove would. The wrapped SQL, instead of being
executed as commands, should be executed as SETOF-text-returning
queries. The DDR installer code itself should read the returned text
and use tap4j to parse it all into one or more TestSets.

TestSets can be nested. The Java code could be maintaining a global
TestSet and add each of these to it as another subtest ... along with
other subtests that might come from tap4j tests done in the Java code
itself.

To this accumulated test data, two things could happen by the end of
the containing transaction.

1. We could provide a retrieveTestResults() RETURNS SETOF text;
   function, and the connected client could call that function to get
   all the accumulated results sent up the wire in TAP format, so any
   TAP-consuming code on the client (tap4j again in a JDBC client,
   pg_prove in Perl, etc.) could analyze and report on it in detail.
   Calling the function would reset the global TestSet to empty, much
   as retrieving SQLWarnings in JDBC resets that list.

2. If retrieveTestResults() hasn't been called by the end of the
   containing transaction, a TransactionListener can raise NOTICE with
   summary counts of tests run and passed, or WARNING with counts of
   run, passed, and failed.

Reactions?

-Chap