# PostgreSQLScriptRunner.java

    /*
    ** Module   : PostgreSQLScriptRunner.java
    ** Abstract : Run DDL SQL script against the PostgreSQL database
    **
    ** Copyright (c) 2022-2023, Golden Code Development Corporation.
    **
    ** -#- -I- --Date-- ---------------------------------------Description---------------------------------------
    ** 001 IAS 20220816 Created initial version.
    *  002 IAS 20230908 Add support for more SQL script types.
    */

    /*
    ** This program is free software: you can redistribute it and/or modify
    ** it under the terms of the GNU Affero General Public License as
    ** published by the Free Software Foundation, either version 3 of the
    ** License, or (at your option) any later version.
    **
    ** This program is distributed in the hope that it will be useful,
    ** but WITHOUT ANY WARRANTY; without even the implied warranty of
    ** MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
    ** GNU Affero General Public License for more details.
    **
    ** You may find a copy of the GNU Affero GPL version 3 at the following
    ** location: https://www.gnu.org/licenses/agpl-3.0.en.html
    **
    ** Additional terms under GNU Affero GPL version 3 section 7:
    **
    **   Under Section 7 of the GNU Affero GPL version 3, the following additional
    **   terms apply to the works covered under the License.  These additional terms
    **   are non-permissive additional terms allowed under Section 7 of the GNU
    **   Affero GPL version 3 and may not be removed by you.
    **
    **   0. Attribution Requirement.
    **
    **     You must preserve all legal notices or author attributions in the covered
    **     work or Appropriate Legal Notices displayed by works containing the covered
    **     work.  You may not remove from the covered work any author or developer
    **     credit already included within the covered work.
    **
    **   1. No License To Use Trademarks.
    **
    **     This license does not grant any license or rights to use the trademarks
    **     Golden Code, FWD, any Golden Code or FWD logo, or any other trademarks
    **     of Golden Code Development Corporation. You are not authorized to use the
    **     name Golden Code, FWD, or the names of any author or contributor, for
    **     publicity purposes without written authorization.
    **
    **   2. No Misrepresentation of Affiliation.
    **
    **     You may not represent yourself as Golden Code Development Corporation or FWD.
    **
    **     You may not represent yourself for publicity purposes as associated with
    **     Golden Code Development Corporation, FWD, or any author or contributor to
    **     the covered work, without written authorization.
    **
    **   3. No Misrepresentation of Source or Origin.
    **
    **     You may not represent the covered work as solely your work.  All modified
    **     versions of the covered work must be marked in a reasonable way to make it
    **     clear that the modified work is not originating from Golden Code Development
    **     Corporation or FWD.  All modified versions must contain the notices of
    **     attribution required in this license.
    */

    package com.goldencode.p2j.persist.deploy;

    import java.io.*;
    import java.sql.*;
    import java.util.*;
    import java.util.logging.*;
    import java.util.regex.*;
    import java.util.stream.*;

    import com.goldencode.p2j.cfg.*;
    import com.goldencode.p2j.persist.*;
    import com.goldencode.p2j.persist.dialect.*;
    import com.goldencode.p2j.util.*;

    /**
     *  Run SQL script against the PostgreSQL database
     *
     */
    public class PostgreSQLScriptRunner
    extends ScriptRunner
    {
       /** pattern for the PostgreSQL 'SELECT version()' result parsing */
       private static final Pattern PG_VER = Pattern.compile("^PostgreSQL *([0-9]*)\\.([0-9]*).*");

       /**
        *  PostgreSQL query for counting the number of UDFs with a spicefic name.
        */
       private static final List<String> COUNT = Collections.unmodifiableList(
                Arrays.asList(
                   "select count(*)",
                   "from pg_proc p",
                   "left join pg_namespace n on p.pronamespace = n.oid",
                   "left join pg_language l on p.prolang = l.oid",
                   "left join pg_type t on t.oid = p.prorettype",
                   "where n.nspname = ?",
                   "  and l.lanname in ('sql', 'plpgsql')",
                   "  and t.typname != 'trigger'",
                   "  and p.proname = ?"
                )
             );

       /** SCALE UDF (built-in for PostgreSQL 10+) */
       private static final List<String> SCALE = Collections.unmodifiableList(
          Arrays.asList(
             "CREATE OR REPLACE FUNCTION scale(v numeric)",
             "RETURNS integer",
             "LANGUAGE plpgsql",
             "IMMUTABLE STRICT LEAKPROOF",
             "AS $function$",
             "   declare s text;",
             "   declare p integer;",
             "   begin",
             "      s := v::text;",
             "      p := strpos(s, '.');",
             "      if p = 0 then",
             "         return 0;",
             "      else",
             "         return length(left(s, length(s) - p));",
             "      end if;",
             "   end",
             "$function$"
          )
       );

       /**
        * Constructor.
        *
        * @param dialect
        *        the target database dialect
        */
       public PostgreSQLScriptRunner(Dialect dialect)
       {
          super(dialect);
       }

       /**
        * Apply UDF SQL scripts.
        *
        * @param conn
        *        Database connection.
        *
        * @throws SQLException
        *         on SQL error.
        * @throws IOException
        *         on script reading error.
        */
       @Override
       protected void applyUdfScripts(Connection conn)
       throws SQLException, IOException
       {
          ScriptSplitter splitter = dialect.scriptSplitter();

           // Creating 'scale' UDF if PostgreSQL version is less than 10.00
          if (getPgVersion(conn) < 1000)
          {
             createScaleUDF(conn);
          }

          applyScripts(conn, splitter);

          // Setting the database 'search_path'

          if (scriptType == ScriptType.UDF_INSTALL_SET_SEARCH_PATH)
          {
             String dbname = null;
             try(Statement stmt = conn.createStatement();
                 ResultSet rs = stmt.executeQuery("SELECT current_database()"))
             {
                if( rs.next())
                {
                   dbname = rs.getString(1);
                }
             }
             if (dbname != null)
             {
                LOG.log(Level.INFO, String.format("Setting search_path for [%s]", dbname));
                execute(conn, "ALTER DATABASE " + dbname + " SET search_path TO public,udf");
             }
             else
             {
                LOG.log(Level.WARNING, "Failed to retrieve the database name");
             }
          }

       }

       /**
        * Check for missing UDFs and create them.
        *
        * @param dbname
        *        Database name.
        * @param url
        *         Database JDBC URL.
        * @param adm
        *        Admin login.
        * @param pwd
        *        Admin password.
        *
        * @throws SQLException
        *         on SQL error.
        * @throws PersistenceException
        *         on script reading error.
        */
       @Override
       protected void createMissingUdfs(String dbname, String url, String adm, String pwd)
       throws SQLException,
              PersistenceException
       {
          List<String> scriptNames = new ArrayList<>();
          try(Connection conn = DriverManager.getConnection(url, adm, pwd))
          {
             if (countUDFs(conn, "udf", "getfwdversion") == 0)
             {
                LOG.info(String.format("%s UDF not found in %s; %s SQL script will be applied",
                         "'udf.getfwdversion'", dbname, "'udfs.sql'"));
                scriptNames.add("udfs.sql");
             }
             if (countUDFs(conn, "public", "words") < 2)
             {
                LOG.info(String.format("Less than 2 %s UDFs found in %s; %s SQL script will be applied",
                         "'public.words'", dbname, "'words-udfs-sql.sql'"));
                scriptNames.add("words-udfs-sql.sql");
             }
             scripts = addScriptPath(scriptNames);
             try
             {
                applyUdfScripts(conn);
             }
             catch (IOException e)
             {
                throw new PersistenceException(e);
             }
          }
       }

       /**
        * Retrieve PostgreSQL server version.
        *
        * @param   conn
        *          The database connection.
        *
        * @return  PostgreSQL server version as 100+major + minor.
        *
        * @throws  SQLException
        *          On error.
        */
       private static int getPgVersion(Connection conn)
       throws SQLException
       {
          String version = "?";
          try (Statement stmt = conn.createStatement())
          {
             ResultSet rs = stmt.executeQuery("SELECT version()");
             if( rs.next())
             {
                version = rs.getString(1);
             }
          }
          Matcher matcher = PG_VER.matcher(version);
          if (!matcher.matches())
          {
             throw new IllegalStateException("Unexpected 'SELECT version()' result: [" + version + "]");
          }
          return Integer.parseInt(matcher.group(1)) * 100 + Integer.parseInt(matcher.group(2));
       }

       /**
        * Count number of UDFs with a given name.
        *
        * @param   conn
        *          The database connection.
        * @param   schema
        *          schema name.
        * @param   name
        *          UDF name.
        *
        * @return  number of UDFs with a given name.
        *
        * @throws  SQLException
        *          On error.
        */
       private int countUDFs(Connection conn, String schema, String name)
       throws SQLException
       {
          String eoln = EnvironmentOps.OS_WIN.equalsIgnoreCase(Configuration.getParameter("opsys"))
                ? "\r\n" /* express to WINDOWS */ : "\n" /* default to Linux */;
          String sql = COUNT.stream().collect(Collectors.joining(eoln));
          try (PreparedStatement pstmt = conn.prepareStatement(sql))
          {
             int n = 0;
             pstmt.setString(1, schema);
             pstmt.setString(2, name);
             ResultSet rs = pstmt.executeQuery();
             if (rs.next())
             {
                n = rs.getInt(1);
             }
             return n;
          }
       }

       /**
        * Create SCALE UDF.
        *
        * @param   conn
        *          The database connection.
        *
        * @throws  SQLException
        *          On error.
        */
       private void createScaleUDF(Connection conn)
       throws SQLException
       {
          String eoln = EnvironmentOps.OS_WIN.equalsIgnoreCase(Configuration.getParameter("opsys"))
                ? "\r\n" /* express to WINDOWS */ : "\n" /* default to Linux */;
          String sql = SCALE.stream().collect(Collectors.joining(eoln));
          execute(conn, sql);
       }
    }