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;
}
}
}