# ScriptRunner.java

    /*
    ** Module   : ScriptRunner.java
    ** Abstract : Run DDL SQL script against the database
    **
    ** Copyright (c) 2022-2024, Golden Code Development Corporation.
    **
    ** -#- -I- --Date-- ---------------------------------------Description---------------------------------------
    ** 001 IAS 20220110 Created initial version.
    **     IAS 20220601 Use scripts' list as optional argument, apply SQL 'words' UDFs by default.
    **     IAS 20220608 Add option for setting database search_path.
    **     IAS 20220610 Create 'scale' UDF if PostgreSQL major version is < 10.
    **     OM  20220614 Dropped unused import. Formatting javadoc and typo fixes.
    **     IAS 20220712 Create missing UDFs
    **     IAS 20220816 Moved dialect-specific code to the corresponding subclass.
    ** 002 GBB 20230512 Logging methods replaced by CentralLogger/ConversionStatus.
    ** 003 IAS 20230908 Add support for more SQL script types.
    ** 004 SP  20240618 Added SQL 'word-tables' UDFs by default.
    **     SP  20240627 Added applyPlaceholders() that substitutes variables as placeholders
    **                  within a SQL string by values.
    ** 005 OM  20240816 Being more resilient by allowing scripts to be loaded from local file system.
    */

    /*
    ** 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.nio.file.*;
    import java.sql.*;
    import java.util.*;
    import java.util.logging.*;
    import java.util.regex.*;
    import java.util.stream.*;

    import com.goldencode.p2j.persist.*;
    import com.goldencode.p2j.persist.deploy.ScriptSplitter.*;
    import com.goldencode.p2j.persist.dialect.*;
    import com.goldencode.p2j.persist.orm.*;
    import com.goldencode.p2j.util.I18nOps;
    import com.goldencode.p2j.util.logging.*;
    import com.goldencode.util.StringHelper;

    /**
     *  Run SQL script against the database
     *
     */
    public abstract class ScriptRunner
    {
       /** Logger */
       protected static final CentralLogger LOG = CentralLogger.get(ScriptRunner.class);

       /** Pattern for the JDBC URL parsing */
       private static final Pattern JDBC = Pattern.compile("^jdbc:([^:]*).*");

       /** Map for placeholder values  */
       protected Map<String, String> placeholders = null;

       /** If placeholders should be used */
       protected boolean usePlaceholders = false;

       /** The target database dialect */
       protected final Dialect dialect;

       /** Scripts to be run */
       protected List<String> scripts = new ArrayList<>();

       /** Script type */
       protected ScriptType scriptType;

       /**
        * Runner main method.
        *
        * @param   args
        *          Command line arguments, as follows: {@code [<db url>, <user>, <password>, <command>]}.
        *
        * @throws  Exception
        *          On error.
        */
       public static void main(String... args)
       throws Exception
       {
          Matcher matcher = JDBC.matcher(args[0]);
          if (!matcher.matches())
          {
             throw new IllegalArgumentException("Unknown JDBC URL: [" + args[0] + "]");
          }
          String dbType = matcher.group(1);
          Dialect dbDialect = Dialect.getDialect(dbType);
          if (dbDialect == null)
          {
             throw new IllegalArgumentException("Unknown JDBC database type: [" + dbType + "]");
          }
          ScriptRunner scriptRunner = dbDialect.getScriptRunner();
          scriptRunner.parseCmdLine(args);
          try(Connection conn = DriverManager.getConnection(args[0], args[1], args[2]))
          {
             scriptRunner.applyDDLScripts(conn);
          }
       }

       /**
        * Constructor.
        *
        * @param dialect
        *        the target database dialect
        */
       public ScriptRunner(Dialect dialect)
       {
          this.dialect = dialect;
       }

       /**
        * Parse command line arguments.
        *
        * @param args
        *        command line arguments;
        */
       protected void parseCmdLine(String[] args)
       {
          List<String> scriptNames;
          this.scriptType = Arrays.stream(ScriptType.values()).filter(st -> st.cmd.equalsIgnoreCase(args[3])).
                                                              findFirst().orElse(null);
          if (scriptType == null)
          {
             throw new IllegalArgumentException("Unknown command: [" + args[3] + "]");
          }

          switch (scriptType)
          {
             case UDF_INSTALL_SET_SEARCH_PATH:
             case UDF_INSTALL:
                if (!supportsUdfScripts())
                {
                   throw new IllegalArgumentException(
                            "UDF scripts are not supported for the "+ dialect + "dialect");
                }
                if (args.length > 4)
                {
                   scriptNames = Arrays.asList(args[4].split("\\s+"));
                }
                else
                {
                   scriptNames = defaultUdfScripts();
                }
                scripts = addScriptPath(scriptNames);
                break;
             case CREATE_TABLES:
                if (args.length > 4)
                {
                   scriptNames = Arrays.asList(args[4].split("\\s+"));
                }
                else
                {
                   throw new IllegalArgumentException(
                            "Tables' DDL script is not provided for"+ dialect + "dialect");
                }
                scripts =  makeScripts(scriptNames);
                break;
             case CREATE_INDEXES:
                if (args.length > 4)
                {
                   scriptNames = Arrays.asList(args[4].split("\\s+"));
                }
                else
                {
                   throw new IllegalArgumentException(
                            "Indexes' DDL script is not provided for the "+ dialect + "dialect");
                }
                scripts =  makeScripts(scriptNames);
                break;
             case CREATE_WORD_OBJECTS:
                if (!dialect.useWordTables())
                {
                   throw new IllegalArgumentException(
                            "Word DDL scripts is not provided for for the "+ dialect + "dialect");

                }
                if (args.length > 4)
                {
                   scriptNames = Arrays.asList(args[4].split("\\s+"));
                }
                else
                {
                   throw new IllegalArgumentException(
                            "Word tables are not supported for the "+ dialect + "dialect");
                }
                scripts =  makeScripts(scriptNames);
                break;
             default:
                throw new IllegalArgumentException("Unknown command: [" + scriptType + "]");
          }
       }

       /**
        * Check if UDFs are created with script
        *
        * @return <code>true</code> if UDFs are created with script
        */
       public boolean supportsUdfScripts()
       {
          return true;
       }

       /**
        * Provide a list of default UDF scripts
        *
        * @return the list of default UDF scripts;
        */
       protected List<String> defaultUdfScripts()
       {
          return Arrays.asList("udfs.sql", "words-udfs-sql.sql", "word-tables.sql");
       }

       protected void applyDDLScripts(Connection conn)
                throws SQLException, IOException
       {
          switch(scriptType)
          {
             case UDF_INSTALL_SET_SEARCH_PATH:
             case UDF_INSTALL:
                applyUdfScripts(conn);
                break;
             case CREATE_TABLES:
             case CREATE_INDEXES:
             case CREATE_WORD_OBJECTS:
                applySchema(conn);
                break;
          }
       }

       /**
        * Substitutes variables as placeholders within a SQL string by values.
        *
        * @param conn
        *        Database connection.
        * @param source
        *        The SQL statement to be executed.
        *
        * @return The SQL statement with the placeholders substituted by values.
        */
       protected String applyPlaceholders(Connection conn, String source)
       {
          if (!usePlaceholders)
          {
             return source;
          }
          if (placeholders == null)
          {
             placeholders = new HashMap<>();
             String codePage = dialect.getCodepage(conn);
             placeholders.put("delimiters", I18nOps.getWordDelimiters(codePage));
          }

          return StringHelper.sweep(source, placeholders);
       }

       /**
        * Apply SQL scripts.
        *
        * @param conn
        *        Database connection.
        *
        * @throws SQLException
        *         on SQL error.
        * @throws IOException
        *         on script reading error.
        */
       protected abstract void applyUdfScripts(Connection conn)
       throws SQLException, IOException;

       /**
        * 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.
        */
       protected abstract void createMissingUdfs(String dbname, String url, String adm,  String pwd)
       throws SQLException,
              PersistenceException;

       /**
        * Check for missing UDFs and create them.
        *
        * @param db
        *        Database.
        *
        * @throws SQLException
        *         on SQL error.
        * @throws PersistenceException
        *         on script reading error.
        */
       public static void createMissingUdfs(Database db)
       throws SQLException,
              PersistenceException
       {
          String dbname = db.getName();
          Dialect dialect = DatabaseManager.getDialect(db);
          Settings orm = DatabaseManager.getConfiguration(db).getOrmSettings();
          String url = orm.getString(Settings.URL, null);
          String adm = orm.getString(Settings.ADMIN, null);
          String pwd = orm.getString(Settings.ADMIN_PASSWORD, null);
          if (adm == null || pwd == null)
          {
             LOG.info("No admin credentials provided, skipping missing UDFs analysis");
             return;
          }
          dialect.getScriptRunner().createMissingUdfs(dbname, url, adm, pwd);
       }

       /**
        * Add script path to script names
        *
        * @param scriptNames
        *        original script names
        *
        * @return list of full script names
        */
       protected List<String> addScriptPath(List<String> scriptNames)
       {
          return scriptNames.stream().
                   map(s -> "/udf/" + dialect.getJdbcDatabaseType() + "/" + s).
                   collect(Collectors.toList());
       }

       /**
        * Make full script files names from script names
        *
        * @param scriptNames
        *        original script names
        *
        * @return list of full script names
        */
       protected List<String> makeScripts(List<String> scriptNames)
       {
          return scriptNames.stream().
                   map(s -> "/ddl/schema_" + s + "_" + dialect.getJdbcDatabaseType() + ".sql").
                   collect(Collectors.toList());
       }

       /**
        * Apply SQL scripts using connection
        *
        * @param conn
        *        connection
        * @param splitter
        *        script splitter
        *
        * @throws IOException
        *         on I/O error
        * @throws SQLException
        *         on database error
        */
       protected void applyScripts(Connection conn, ScriptSplitter splitter)
       throws IOException,
              SQLException
       {
          for (String scriptName : scripts)
          {
             InputStream is = ScriptRunner.class.getResourceAsStream(scriptName);
             if (is == null)
             {
                try
                {
                   is = Files.newInputStream(Paths.get("p2j/" + scriptName)); // TODO: compute the right path
                }
                catch (IOException e)
                {
                   // silent. [is] remains null
                }
             }
             if (is == null)
             {
                throw new IllegalStateException("Cannot open script [" + scriptName + "]" );
             }

             try (BufferedReader br = new BufferedReader(new InputStreamReader(is)))
             {
                LOG.log(Level.INFO, String.format("Running script: [%s]", scriptName));
                usePlaceholders = true;
                runScript(conn, br, splitter);
             }
          }
       }

       /**
        * Apply SQL scripts using connection
        *
        * @param conn
        *        connection
        *
        * @throws IOException
        *         on I/O error
        * @throws SQLException
        *         on database error
        */
       protected void applySchema(Connection conn)
       throws IOException, SQLException
       {
          for (String scriptName: scripts)
          {
             InputStream is = ScriptRunner.class.getResourceAsStream(scriptName);
             if (is == null)
             {
                throw new IllegalStateException("Cannot open script [" + scriptName + "]" );
             }
             try(BufferedReader br = new BufferedReader(new InputStreamReader(is)))
             {
                LOG.log(Level.INFO, String.format("Running script: [%s]", scriptName));
                runScript(conn, br);
             }
          }
       }

       /**
        * Run UDFs SQL script against the database.
        *
        * @param   conn
        *          The database connection.
        * @param   script
        *          The reader for the script.
        *
        * @throws  IOException
        *          In I/O error.
        * @throws  SQLException
        *          In database error.
        */
       private void runScript(Connection conn, Reader script, ScriptSplitter splitter)
       throws IOException,
              SQLException
       {
          String s;
          StringBuilder sb = new StringBuilder();
          State prevState = State.GAP;
          long nline = -1;
          while ((s = readLine(script)) != null)
          {
             nline++;
             State newState = splitter.state(s);
             switch (newState)
             {
                case GAP:
                   if (prevState == State.DDL && sb.length() > 0)
                   {
                      execute(conn, sb.toString());
                      sb.setLength(0);
                   }
                   break;
                case COMMENT:
                   break;
                case DDL:
                   sb.append(s).append('\n');
                   break;
                case IGNORE:
                   if (prevState == State.DDL && sb.length() > 0)
                   {
                      execute(conn, sb.toString());
                      sb.setLength(0);
                   }
                   newState = State.GAP;
                   splitter.state(State.GAP);
                   break;
                case INVALID_COMMENT:
                   throw new IllegalStateException("Invalid comment in line #" + nline);
             }
             prevState = newState;
          }

          if (prevState == State.DDL && sb.length() > 0)
          {
             execute(conn, sb.toString());
             sb.setLength(0);
          }
       }

       /**
        * Run UDFs SQL script against the database.
        *
        * @param   conn
        *          The database connection.
        * @param   script
        *          The reader for the script.
        *
        * @throws  IOException
        *          In I/O error.
        * @throws  SQLException
        *          In database error.
        */
       private void runScript(Connection conn, Reader script)
       throws IOException,
              SQLException
       {
          boolean ddl = false;
          String s;
          StringBuilder sb = new StringBuilder();
          while ((s = readLine(script)) != null)
          {
             if(s.trim().equals(""))
             {
                if (ddl)
                {
                   execute(conn, sb.toString());
                   sb.setLength(0);
                   ddl = false;
                }
                continue;
             }
             if (ddl)
             {
                sb.append(s).append("\n");
                continue;
             }
             if(s.trim().endsWith(";"))
             {
                execute(conn, s);
                continue;
             }
             sb.append(s).append("\n");
             ddl = true;
          }
       }

       /**
        * Execute an SQL statement.
        *
        * @param   conn
        *          The database connection.
        * @param   sql
        *          The SQL statement to be executed.
        *
        * @throws  SQLException
        *          On error.
        */
       protected void execute(Connection conn, String sql)
       throws SQLException
       {
          sql = applyPlaceholders(conn, sql);
          try (Statement stmt = conn.createStatement())
          {
             stmt.executeUpdate(sql);
          }
       }

       /**
        * Read the next line from the source Reader.
        *
        * @param   r
        *          The reader for the source file.
        *
        * @return  next line of <code>null</code> if EOF.
        *
        * @throws  IOException
        *          In the event of an I/O error.
        */
       private String readLine(Reader r)
       throws IOException
       {
          StringBuilder sb = new StringBuilder();
          int c = r.read();
          if (c == -1)
          {
             return null;
          }

          while ((char)c != '\n' && c != -1)
          {
             if (c == '\r')
             {
                c = r.read();
                continue;
             }
             sb.append((char)c);
             c = r.read();
          }
          return sb.toString();
       }

       /** Script type */
       protected static enum ScriptType
       {
          /** install UDFs */
          UDF_INSTALL("udf.install"),

          /** install UDFs and set search path */
          UDF_INSTALL_SET_SEARCH_PATH("udf.install.search_path"),

          /** create tables */
          CREATE_TABLES("create.tables"),

          /** create indexes */
          CREATE_INDEXES("create.indexes"),

          /** create indexes */
          CREATE_WORD_OBJECTS("create.word.objects");

          /** command string */
          public final String cmd;

          /**
           * C'tor
           * @param cmd
           *        command string.
           */
          private ScriptType(String cmd)
          {
             this.cmd = cmd;
          }
       }
    }