The testing harness Node.class in PL/Java's self-installer jar

qp dissected

qp is for interactive, exploratory use, generating printed output. For scripting purposes, q gives direct access to result objects; qp is nothing but a wrapper that calls q with the same arguments, and what q returns is passed directly to another method (in fact, another of several overloads of qp) to be printed.

What q returns is a Stream<Object>. Although declared with element type Object, the stream will only deliver instances of: ResultSet, Long (an update count), or throwables (caught exceptions, or SQLWarning instances). The JDBC Statement is polled for new SQLWarnings before checking for each next result (ResultSet or update count). An error or exception that is thrown and caught will be placed on the stream when caught (and will be the last thing on the stream, though it may carry chains of cause, suppressed, or next exceptions that may follow it if flattenDiagnostics is used on the stream).

All ‘notices’ from PostgreSQL (severity below ERROR but at or above client_min_messages) are turned into SQLWarning instances by pgjdbc-ng, which does not provide any API to get the original PostgreSQL severity, or any of the details other than the message and SQLState code. Node classifies them as info if the SQLState ‘class’ (leftmost two positions) is 00, otherwise as warning. Exceptions of any other kind are classified as error.

PGJDBC also turns notices below ERROR into SQLWarning instances, but provides access to the severity tag from the server, so Node uses that to classify them as warning or info, instead of the class 00 rule. If the severity is WARNING (or happens to be null for some reason), Node will classify the notice as warning; any other severity will be classified as info. (Note, however, that this scheme will not work if the server is configured for a language that uses a different word for WARNING. To make it work in that case, you can call Node.set_WARNING_localized in advance, passing the word that PostgreSQL uses for WARNING in your language.)

As it happens, there is also an overload of qp with just one Stream<Object> parameter. If you have already run a query with q and have the result stream, and decide you just want to print that, just pass it to qp. There are other overloads of qp for the individual objects you might encounter in a result stream. One static import of the name qp will allow printing many things.

More specialized convenience methods

Node also supplies several more specialized methods: setConfig for PostgreSQL configuration variables (qp is fine for a literal string “set foo to bar” command, but for computed values, setConfig uses a prepared statement and binding), and wrappers for PL/Java install_jar, remove_jar, and set_classpath.

The installExamplesJar method supplies the correct as-installed path to the jar file (which we know, because this is the self-installer code, remember?). The boolean method examplesNeedSaxon introspects in the examples jar to see if it includes the Saxon examples, and therefore needs the Saxon jar in place before it can be deployed.

As the Saxon jar is probably already in a local Maven repository, installSaxon will install it from there, given a path to the repository root and the desired version of Saxon-HE. Not to be outdone, installSaxonAndExamplesAndPath combines the steps in correct order to install the Saxon jar, place it on the classpath, install and deploy the examples jar, and set a final classpath that includes both.

import static java.nio.file.Paths.get;
import java.sql.Connection;
import org.postgresql.pljava.packaging.Node;
import static org.postgresql.pljava.packaging.Node.qp;

Node n1 = Node.get_new_node("TestNode1");

try (
  AutoCloseable t1 = n1.initialized_cluster();
  AutoCloseable t2 = n1.started_server(Map.of(
    "client_min_messages", "info",
    "pljava.vmoptions",
      "-Xcheck:jni -enableassertions:org.postgresql.pljava..."
  ));
)
{
  try ( Connection c = n1.connect() )
  {
    qp(c, "CREATE EXTENSION pljava");
  }

  /*
   * Get a new connection; 'create extension' always sets a near-silent logging
   * level, and PL/Java only checks once at VM start time, so in the same
   * session where 'create extension' was done, logging is somewhat suppressed.
   */
  try ( Connection c = n1.connect() )
  {
    qp(Node.installSaxonAndExamplesAndPath(c,
      get(System.getProperty("user.home"), ".m2", "repository").toString(),
      "10.9",
      true));
  }
}
/exit

The above example puts together most of the ideas covered here. While it does demonstrate installing and deploying the examples jar (which runs all of the tests contained in its deployment code), this example merely prints the output, rather than examining it programmatically to evaluate success, and it does not use its exit status to communicate success or failure to its invoker, as one would expect of a test.

Worked-out examples that do the rest of that can be seen in the project repository in the configuration files for the CI testing services.

stateMachine for checking results

One last Node method most useful for checking returned results programmatically is stateMachine (full description in the javadocs). For example, the installSaxonAndExamplesAndPath call above returns a concatenation of four object streams: one from sqlj.install_jar loading the Saxon jar, one from sqlj.set_classpath adding it to the path, one from sqlj.install_jar loading the PL/Java examples jar (which runs the tests in its deployment descriptor), and finally the sqlj.set_classpath placing both jars on the path.

Each of those streams should end with a void result set, preceded by zero or more info or warning (any error should be counted as test failure). In the third stream, from installing the examples jar, any warning should also be counted as a test failure: the tests in the deployment descriptor report failures that way to avoid aborting the query, so more results can be reported.

Using stateMachine, that can be expressed as a small set of states and transitions that match that expected sequence. A state returns a positive number n to consume the input object it is looking at and transition to state n for the next item of input, or a negative number -n to go to state n still with the same input item. The final accepting state returns true; false from any state reports a mismatch. A null is supplied by stateMachine after the last item on the input Stream (which therefore must not contain nulls):

succeeding &= stateMachine(
  "descriptive string for this state machine",
  null,

  Node.installSaxonAndExamplesAndPath(c,
    System.getProperty("mavenRepo"),
    System.getProperty("saxonVer"),
    true)
  .flatMap(Node::semiFlattenDiagnostics)
  .peek(Node::peek), // so they also appear in the log

  // states 1,2: maybe diagnostics, then a void result set (saxon install)
  (o,p,q) -> isDiagnostic(o, Set.of("error")) ? 1 : -2,
  (o,p,q) -> isVoidResultSet(o, 1, 1) ? 3 : false,

  // states 3,4: maybe diagnostics, then a void result set (set classpath)
  (o,p,q) -> isDiagnostic(o, Set.of("error")) ? 3 : -4,
  (o,p,q) -> isVoidResultSet(o, 1, 1) ? 5 : false,

  // states 5,6: maybe diagnostics, then void result set (example install)
  (o,p,q) -> isDiagnostic(o, Set.of("error", "warning")) ? 5 : -6,
  (o,p,q) -> isVoidResultSet(o, 1, 1) ? 7 : false,

  // states 7,8: maybe diagnostics, then a void result set (set classpath)
  (o,p,q) -> isDiagnostic(o, Set.of("error")) ? 7 : -8,
  (o,p,q) -> isVoidResultSet(o, 1, 1) ? 9 : false,

  // state 9: must be end of input
  (o,p,q) -> null == o
);

The isDiagnostic method shown above isn't part of the Node class; in the actual test configurations in the repository, it is trivially defined in jshell a few lines earlier. Not everything needs to be built in.

The difference in treatment of no-result DDL statements between drivers (where pgjdbc-ng really has no result, and PGJDBC has a zero-row update count as it would for a DML statement) can complicate writing a state machine that works with both drivers. Node predefines a state function NOTHING_OR_PGJDBC_ZERO_COUNT that consults the s_urlForm static field to determine which driver is in use, and then moves to the numerically next state, after consuming

• nothing, if the driver is pgjdbc-ng, or
• a single zero row count (rejecting any other input), if the driver is PGJDBC.

succeeding &= stateMachine(
  "descriptive string for this state machine",
  null,

  q(c, "CREATE TABLE foo (bar int, baz text)")
  .flatMap(Node::semiFlattenDiagnostics)
  .peek(Node::peek), // so they also appear in the log

  (o,p,q) -> isDiagnostic(o, Set.of("error")) ? 1 : -2,

  Node.NOTHING_OR_PGJDBC_ZERO_COUNT,

  (o,p,q) -> null == o
);