appveyor.yml

# These only_commits and branches settings ought to pretty much suppress
# Appveyor, whose runs have all been failing lately because of Maven repository
# connection resets that don't seem reproducible locally. This can be revisited
# later to see if things might be working again.
only_commits:
  message: /appveyor/
branches:
  only:
    - appveyor
image: Visual Studio 2019
environment:
  APPVEYOR_RDP_PASSWORD: MrRobot@2020
  VCVARSALL: C:\Program Files (x86)\Microsoft Visual Studio 14.0\VC\vcvarsall.bat
  matrix:
#   - SYS: MINGW
#     JDK: 9
#     PG: 12
#   - SYS: MINGW
#     JDK: 10
#     PG: 12
    - SYS: MINGW
      JDK: 11
      PG: 12
    - SYS: MINGW
      JDK: 12
      PG: 12
    - SYS: MINGW
      JDK: 13
      PG: 12
    - SYS: MINGW
      JDK: 14
      PG: 12
    - SYS: MINGW
      JDK: 15
      PG: 12
    - SYS: MSVC
      JDK: 15
      PG: 12
    - SYS: MSVC
      JDK: 14
      PG: 12
    - SYS: MSVC
      JDK: 13
      PG: 12
    - SYS: MSVC
      JDK: 12
      PG: 12
    - SYS: MSVC
      JDK: 11
      PG: 12
#   - SYS: MSVC
#     JDK: 10
#     PG: 12
#   - SYS: MSVC
#     JDK: 9
#     PG: 12
    - SYS: MSVC
      JDK: 14
      PG: 11
    - SYS: MSVC
      JDK: 14
      PG: 10
    - SYS: MSVC
      JDK: 14
      PG: 9.6
before_build:
  - ps: .appveyor/appveyor_download_java.ps1
  - set JAVA_HOME=%ProgramFiles%\Java\jdk%JDK%
  - path %JAVA_HOME%\bin;%PATH%
  - '"%VCVARSALL%" x86'
  - '"%VCVARSALL%" amd64'
  - ps: $Env:JAVA_HOME = "C:\Program Files\Java\jdk$Env:JDK"
  - ps: $Env:Path = "$Env:JAVA_HOME\bin;" + $Env:Path
build_script:
  - .appveyor\appveyor_mingw_or_msvc.bat
#  - ps: $blockRdp = $true; iex ((new-object net.webclient).DownloadString('https://raw.githubusercontent.com/appveyor/ci/master/scripts/enable-rdp.ps1'))
test_script:
  - ps: |
      $pgConfig = $Env:pgConfig

      $packageJar = ('pljava-packaging' |
        Get-ChildItem -Recurse -Filter pljava-pg*.jar
      ).FullName

      $mavenRepo = "$env:UserProfile\.m2\repository"

      $saxonVer = (Join-Path $mavenRepo "net\sf\saxon\Saxon-HE" |
        Get-ChildItem -Recurse -Filter Saxon-HE-*.jar |
        Select-Object -Last 1
      ).Directory.Name

      $jdbcJar = (Join-Path $mavenRepo "com\impossibl\pgjdbc-ng\pgjdbc-ng-all" |
        Get-ChildItem -Recurse -Filter pgjdbc-ng-all-*.jar |
        Select-Object -Last 1
      ).FullName

      @'
      boolean succeeding = false; // begin pessimistic

      import static java.nio.file.Files.createTempFile;
      import static java.nio.file.Files.write;
      import java.nio.file.Path;
      import static java.nio.file.Paths.get;
      import java.sql.Connection;
      import java.sql.PreparedStatement;
      import java.sql.ResultSet;
      import org.postgresql.pljava.packaging.Node;
      import static org.postgresql.pljava.packaging.Node.q;
      import static org.postgresql.pljava.packaging.Node.stateMachine;
      import static org.postgresql.pljava.packaging.Node.isVoidResultSet;
      /*
       * Imports that will be needed to serve a jar file over http
       * when the time comes for testing that.
       */
      import static java.nio.charset.StandardCharsets.UTF_8;
      import java.util.jar.Attributes;
      import java.util.jar.Manifest;
      import java.util.jar.JarOutputStream;
      import java.util.zip.ZipEntry;
      import com.sun.net.httpserver.BasicAuthenticator;
      import com.sun.net.httpserver.HttpContext;
      import com.sun.net.httpserver.HttpExchange;
      import com.sun.net.httpserver.HttpHandler;
      import com.sun.net.httpserver.HttpServer;

      System.setErr(System.out); // PowerShell makes a mess of stderr output

      Node.main(new String[0]); // Extract the files (with output to stdout)

      String vmopts = "-enableassertions:org.postgresql.pljava... -Xcheck:jni";

      if ( 17 < Runtime.version().feature() )
        vmopts += " -Djava.security.manager=allow";

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

      n1.use_pg_ctl(true);

      /*
       * Keep a tally of the three types of diagnostic notices that may be
       * received, and, independently, how many represent no-good test results
       * (error always, but also warning if seen from the tests in the
       * examples.jar deployment descriptor).
       */
      Map<String,Integer> results =
        Stream.of("info", "warning", "error", "ng").collect(
          LinkedHashMap<String,Integer>::new,
          (m,k) -> m.put(k, 0), (r,s) -> {});

      boolean isDiagnostic(Object o, Set<String> whatIsNG)
      {
        if ( ! ( o instanceof Throwable ) )
          return false;
        String[] parts = Node.classify((Throwable)o);
        String type = parts[0];
        String message = parts[2];
        results.compute(type, (k,v) -> 1 + v);
        if ( whatIsNG.contains(type) )
          if ( ! "warning".equals(type)  ||  ! message.startsWith("[JEP 411]") )
            results.compute("ng", (k,v) -> 1 + v);
        return true;
      }

      /*
       * Write a trial policy into a temporary file in n's data_dir,
       * and set pljava.vmoptions accordingly over connection c.
       * Returns the 'succeeding' flag from the state machine looking
       * at the command results.
       */
      boolean useTrialPolicy(Node n, Connection c, List<String> contents)
      throws Exception
      {
        Path trialPolicy =
          createTempFile(n.data_dir().getParent(), "trial", "policy");

        write(trialPolicy, contents);

        PreparedStatement setVmOpts = c.prepareStatement(
          "SELECT null::pg_catalog.void" +
          " FROM pg_catalog.set_config('pljava.vmoptions', ?, false)"
        );

        setVmOpts.setString(1, vmopts +
          " -Dorg.postgresql.pljava.policy.trial=" + trialPolicy.toUri());

        return stateMachine(
          "change pljava.vmoptions",
          null,

          q(setVmOpts, setVmOpts::execute)
          .flatMap(Node::semiFlattenDiagnostics)
          .peek(Node::peek),

          (o,p,q) -> isDiagnostic(o, Set.of("error")) ? 1 : -2,
          (o,p,q) -> isVoidResultSet(o, 1, 1) ? 3 : false,
          (o,p,q) -> null == o
        );
      }

      try (
        AutoCloseable t1 = n1.initialized_cluster(
          p->p.redirectErrorStream(true));
        AutoCloseable t2 = n1.started_server(Map.of(
          "client_min_messages", "info",
          "pljava.vmoptions", vmopts
        ), p->p.redirectErrorStream(true));
      )
      {
        try ( Connection c = n1.connect() )
        {
          succeeding = true; // become optimistic, will be using &= below

          succeeding &= stateMachine(
            "create extension no result",
            null,

            q(c, "CREATE EXTENSION pljava")
            .flatMap(Node::semiFlattenDiagnostics)
            .peek(Node::peek),

            // state 1: consume any diagnostics, or show same item to state 2
            (o,p,q) -> isDiagnostic(o, Set.of("error")) ? 1 : -2,

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

        /*
         * 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() )
        {
          succeeding &= stateMachine(
            "saxon path examples path",
            null,

            Node.installSaxonAndExamplesAndPath(c,
              System.getProperty("mavenRepo"),
              System.getProperty("saxonVer"),
              true)
            .flatMap(Node::semiFlattenDiagnostics)
            .peek(Node::peek),

            // states 1,2: 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: 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: 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: 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
          );

          /*
           * Exercise TrialPolicy some. Need another connection to change
           * vmoptions. Uses some example functions, so insert here before the
           * test of undeploying the examples.
           */
          try ( Connection c2 = n1.connect() )
          {
            succeeding &= useTrialPolicy(n1, c2, List.of(
              "grant {",
              "  permission",
              "    org.postgresql.pljava.policy.TrialPolicy$Permission;",
              "};"
            ));

            PreparedStatement tryForbiddenRead = c2.prepareStatement(
              "SELECT" +
              "  CASE WHEN javatest.java_getsystemproperty('java.home')" +
              "    OPERATOR(pg_catalog.=) ?" +
              "  THEN javatest.logmessage('INFO', 'trial policy test ok')" +
              "  ELSE javatest.logmessage('WARNING', 'trial policy test ng')" +
              "  END"
            );

            tryForbiddenRead.setString(1, System.getProperty("java.home"));

            succeeding &= stateMachine(
              "try to read a forbidden property",
              null,

              q(tryForbiddenRead, tryForbiddenRead::execute)
              .flatMap(Node::semiFlattenDiagnostics)
              .peek(Node::peek),

              (o,p,q) -> isDiagnostic(o, Set.of("error", "warning")) ? 1 : -2,
              (o,p,q) -> isVoidResultSet(o, 1, 1) ? 3 : false,
              (o,p,q) -> null == o
            );
            // done with connection c2
          }

          /*
           * Spin up an http server with a little jar file to serve, and test
           * that install_jar works with an http: url.
           *
           * First make a little jar empty but for a deployment descriptor.
           */
          String ddrName = "foo.ddr";
          Attributes a = new Attributes();
          a.putValue("SQLJDeploymentDescriptor", "TRUE");
          Manifest m = new Manifest();
          m.getEntries().put(ddrName, a);
          ByteArrayOutputStream baos = new ByteArrayOutputStream();
          JarOutputStream jos = new JarOutputStream(baos, m);
          jos.putNextEntry(new ZipEntry(ddrName));
          jos.write(
            (
              "SQLActions[]={\n\"BEGIN INSTALL\n" +
              "SELECT javatest.logmessage('INFO'," +
              " 'jar installed from http');\n" +
              "END INSTALL\",\n\"BEGIN REMOVE\n" +
              "BEGIN dummy\n" +
              "END dummy;\n" +
              "END REMOVE\"\n}\n"
            ).getBytes(UTF_8)
          );
          jos.closeEntry();
          jos.close();
          byte[] jar = baos.toByteArray();

          /*
           * Now an http server.
           */
          HttpServer hs =
            HttpServer.create(new InetSocketAddress("localhost", 0), 0);

          try (
            Connection c2 = n1.connect();
            AutoCloseable t = ((Supplier<AutoCloseable>)() ->
              {
                hs.start();
                return () -> hs.stop(0);
              }
            ).get()
          )
          {
            InetSocketAddress addr = hs.getAddress();

            String id = "bar", pw = "baz";

            URL u = new URI(
              "http", id+':'+pw, addr.getHostString(), addr.getPort(),
              "/foo.jar", null, null
            ).toURL();

            HttpContext hc = hs.createContext(
              u.getPath(),
              new HttpHandler()
              {
                @Override
                public void handle(HttpExchange t) throws IOException
                {
                  try ( InputStream is = t.getRequestBody() ) {
                    is.readAllBytes();
                  }
                  t.getResponseHeaders().add(
                    "Content-Type", "application/java-archive");
                  t.sendResponseHeaders(200, jar.length);
                  try ( OutputStream os = t.getResponseBody() ) {
                    os.write(jar);
                  }
                }
              }
            );

            hc.setAuthenticator(
              new BasicAuthenticator("CI realm")
              // ("CI realm", UTF_8) only available in Java 14 or later
              {
                @Override
                public boolean checkCredentials(String c_id, String c_pw)
                {
                    return id.equals(c_id) && pw.equals(c_pw);
                }
              }
            );

            succeeding &= useTrialPolicy(n1, c2, List.of(
              "grant codebase \"${org.postgresql.pljava.codesource}\" {",
              "  permission",
              "    java.net.URLPermission \"http:*\", \"GET:Accept\";",
              "};"
            ));

            succeeding &= stateMachine(
              "install a jar over http",
              null,

              Node.installJar(c2, u.toString(), "foo", true)
              .flatMap(Node::semiFlattenDiagnostics)
              .peek(Node::peek),

              (o,p,q) -> isDiagnostic(o, Set.of("error", "warning")) ? 1 : -2,
              (o,p,q) -> isVoidResultSet(o, 1, 1) ? 3 : false,
              (o,p,q) -> null == o
            );

            // done with connection c2 again, and the http server
          }

          /*
           * Also confirm that the generated undeploy actions work.
           */
          succeeding &= stateMachine(
            "remove jar void result",
            null,

            q(c, "SELECT sqlj.remove_jar('examples', true)")
            .flatMap(Node::semiFlattenDiagnostics)
            .peek(Node::peek),

            (o,p,q) -> isDiagnostic(o, Set.of("error")) ? 1 : -2,
            (o,p,q) -> isVoidResultSet(o, 1, 1) ? 3 : false,
            (o,p,q) -> null == o
          );

          /*
           * Get another new connection and make sure the extension can be
           * loaded in a non-superuser session.
           */
          try ( Connection c2 = n1.connect() )
          {
            succeeding &= stateMachine(
              "become non-superuser",
              null,

              q(c2,
                "CREATE ROLE alice;" +
                "GRANT USAGE ON SCHEMA sqlj TO alice;" +
                "SET SESSION AUTHORIZATION alice")
              .flatMap(Node::semiFlattenDiagnostics)
              .peek(Node::peek),

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

            succeeding &= stateMachine(
              "load as non-superuser",
              null,

              q(c2, "SELECT null::pg_catalog.void" +
                    " FROM sqlj.get_classpath('public')")
              .flatMap(Node::semiFlattenDiagnostics)
              .peek(Node::peek),

              (o,p,q) -> isDiagnostic(o, Set.of("error")) ? 1 : -2,
              (o,p,q) -> isVoidResultSet(o, 1, 1) ? 3 : false,
              (o,p,q) -> null == o
            );
            // done with connection c2 again
          }

          /*
           * Make sure the extension drops cleanly and nothing
           * is left in sqlj.
           */
          succeeding &= stateMachine(
            "drop extension and schema no result",
            null,

            q(c, "DROP EXTENSION pljava;DROP SCHEMA sqlj")
            .flatMap(Node::semiFlattenDiagnostics)
            .peek(Node::peek),

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

        /*
         * Get another new connection and confirm that the old, pre-extension,
         * LOAD method of installing PL/Java works. It is largely obsolete in
         * the era of extensions, but still covers the use case of installing
         * PL/Java without admin access on the server filesystem to where
         * CREATE EXTENSION requires the files to be; they can still be
         * installed in some other writable location the server can read, and
         * pljava.module_path set to the right locations of the jars, and the
         * correct shared-object path given to LOAD.
         *
         * Also test the after-the-fact packaging up with CREATE EXTENSION
         * FROM unpackaged. That officially goes away in PG 13, where the
         * equivalent sequence
         *  CREATE EXTENSION pljava VERSION unpackaged
         *  \c
         *  ALTER EXTENSION pljava UPDATE
         * should be tested instead.
         */
        try ( Connection c = n1.connect() )
        {
          int majorVersion = c.getMetaData().getDatabaseMajorVersion();

          succeeding &= stateMachine(
            "load as non-extension",
            null,

            Node.loadPLJava(c)
            .flatMap(Node::semiFlattenDiagnostics)
            .peek(Node::peek),

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

          if ( 13 <= majorVersion )
          {
            succeeding &= stateMachine(
              "create unpackaged (PG >= 13)",
              null,

              q(c, "CREATE EXTENSION pljava VERSION unpackaged")
              .flatMap(Node::semiFlattenDiagnostics)
              .peek(Node::peek),

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

        /*
         * CREATE EXTENSION FROM unpackaged (or the second half of the
         * PG >= 13 CREATE EXTENSION VERSION unpackaged;ALTER EXTENSION UPDATE
         * sequence) has to happen over a new connection.
         */
        try ( Connection c = n1.connect() )
        {
          int majorVersion = c.getMetaData().getDatabaseMajorVersion();

          succeeding &= stateMachine(
            "package after loading",
            null,

            q(c, 13 > majorVersion
              ? "CREATE EXTENSION pljava FROM unpackaged"
              :  "ALTER EXTENSION pljava UPDATE")
            .flatMap(Node::semiFlattenDiagnostics)
            .peek(Node::peek),

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

          /*
           * Again make sure extension drops cleanly with nothing left behind.
           */
          succeeding &= stateMachine(
            "drop extension and schema no result",
            null,

            q(c, "DROP EXTENSION pljava;DROP SCHEMA sqlj")
            .flatMap(Node::semiFlattenDiagnostics)
            .peek(Node::peek),

            (o,p,q) -> isDiagnostic(o, Set.of("error")) ? 1 : -2,
            (o,p,q) -> null == o
          );
        }
      } catch ( Throwable t )
      {
        succeeding = false;
        throw t;
      }

      System.out.println(results);
      succeeding &= (0 == results.get("ng"));
      System.exit(succeeding ? 0 : 1);
      '@ |
      jshell `
        -execution local `
        "-J--class-path=$packageJar;$jdbcJar" `
        "--class-path=$packageJar" `
        "-J--add-modules=java.sql.rowset,jdk.httpserver" `
        "-J-Dcom.impossibl.shadow.io.netty.noUnsafe=true" `
        "-J-Dpgconfig=$pgConfig" `
        "-J-DmavenRepo=$mavenRepo" `
        "-J-DsaxonVer=$saxonVer" -