[Pljava-dev] conditional SQL in DDR, and a testing idea

This started out as a simple idea about building a jar with
SQL code in the DDR that would be specific to PG version or
other conditions on the backend.

_If_targeting 9.0+ there is the DO statement and PL/pgsql is
there by default, so you _could_ just write stuff in the DDR
in the form:

DO LANGUAGE 'plpgsql' $$
IF whatever_condition_is_needed THEN
stuff
END IF;
$$

but I had been thinking of older versions, or not wanting to care
whether plpgsql is there, and I thought of this:

The DDR syntax already allows you to mark some SQL as implementation-
specific, using

BEGIN some-impl
... SQL ...
END some-impl

Right now, SQLDeploymentDescriptor doesn't do anything with that except
throw away any commands where some-impl isn't PostgreSQL (they're not
even read into the array), and execute the ones with no impl, or impl of
PostgreSQL.

That satisfies the standard, but how about if it read _all_ of them in,
and we had a new GUC variable like pljava.implementortags. Some code in
an untagged part of the DDR could do:

SELECT CASE WHEN check_for_93()
THEN SET LOCAL pljava.implementortags TO ...append PostgreSQL-9.3...
END

adding PostgreSQL-9.3 to the list, and if SQLDeploymentDescriptor.install
rechecks the variable after each command, then from that point it would
include all commands tagged

BEGIN PostgreSQL-9.3
... stuff ...
END PostgreSQL-9.3

and as you could make up any arbitrary tags, you could test for and
enable BEGIN PostgreSQL-with-feature-X ...
or bug-Y or whatever made sense in your case.

next installment: turning the same simple idea into part of a better
integration-testing story ....

-Chap

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

Pull request for (the first part of) this, the conditional
execution in a deployment descriptor, has been made at github.

-Chap

Chapman Flack wrote:
> Some code in an untagged part of the DDR could do:
>
> SELECT CASE WHEN check_for_93()
> THEN SET LOCAL pljava.implementortags TO ...append PostgreSQL-9.3...
> END
>
> adding PostgreSQL-9.3 to the list, and if SQLDeploymentDescriptor.install
> rechecks the variable after each command, then from that point it would
> include all commands tagged
>
> BEGIN PostgreSQL-9.3
> ... stuff ...
> END PostgreSQL-9.3
>
> and as you could make up any arbitrary tags, you could test for and
> enable BEGIN PostgreSQL-with-feature-X ...
> or bug-Y or whatever made sense in your case.

This first installment (i.e. just conditional execution, no pgTap
integration yet) is now merged to the master branch. There's a new
example that demonstrates it:

https://github.com/tada/pljava/blob/master/pljava-examples/src/main/java/org/postgresql/pljava/example/annotation/UnicodeRoundTripTest.java

It wraps a test query up in BEGIN postgresql9_0plus...END postgresql9_0plus
(and enables that tag in the way you'd expect) to avoid running the query on
pre-9.0 systems that don't accept the syntax.

-Chap
_______________________________________________
Pljava-dev mailing list
Pljava-dev(at)lists(dot)pgfoundry(dot)org
http://lists.pgfoundry.org/mailman/listinfo/pljava-dev