Package org.apache.sis.internal.metadata.sql

Class ScriptRunner

java.lang.Object
org.apache.sis.internal.metadata.sql.ScriptRunner
All Implemented Interfaces:
AutoCloseable
Direct Known Subclasses:
EPSGInstaller, Installer
public class ScriptRunner extends Object implements AutoCloseable
Run SQL scripts. The script is expected to use a standardized syntax, where the '\'' character is used for quoting text, the '\"' character is used for quoting identifier and the ';' character is used at the end for every SQL statement. Those characters will be replaced on-the-fly by the characters actually used by the database engine.

This class is not intended for executing arbitrary SQL scripts. This class is for executing known scripts bundled with Apache SIS or in an extension (for example the scripts for creating the EPSG database). We do not try to support SQL functionalities other than what we need for those scripts.

Since:
0.7
Version:
1.1

  • Field Summary

    Fields
    Modifier and Type
    Field
    Description
    private static final String
    COMMENT
    The sequence for SQL comments.
    private String
    currentFile
    Name of the SQL script under execution, or null if unknown.
    private int
    currentLine
    The line number of the SQL statement being executed.
    private String
    currentSQL
    The SQL statement being executed.
    private final Dialect
    dialect
    The presumed dialect spoken by the database.
    private static final char
    END_OF_STATEMENT
    The character at the end of statements.
    private static final String
    ESCAPE
    The characters for escaping a portion of the SQL script.
    private static final char
    IDENTIFIER_QUOTE
    The quote character for identifiers expected to be found in the SQL script.
    protected final String
    identifierQuote
    The quote character for identifiers actually used in the database, as determined by DatabaseMetaData.getIdentifierQuoteString().
    protected final boolean
    isCatalogSupported
    true if the database supports catalogs.
    protected final boolean
    isCommentSupported
    true if the database supports the COMMENT statement.
    protected final boolean
    isCreateLanguageRequired
    true if the following instruction shall be executed (assuming that the PostgreSQL "plpgsql" language is desired): sql CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql' HANDLER plpgsql_call_handler VALIDATOR plpgsql_validator;
    protected final boolean
    isEnumTypeSupported
    true if the database supports enums.
    protected final boolean
    isGrantOnSchemaSupported
    true if the database supports "GRANT USAGE ON SCHEMA" statements.
    protected final boolean
    isGrantOnTableSupported
    true if the database supports "GRANT SELECT ON TABLE" statements.
    protected final boolean
    isSchemaSupported
    true if the database supports schemas.
    private final int
    maxRowsPerInsert
    The maximum number of rows allowed per "INSERT" statement.
    protected static final String
    PUBLIC
    The database user having read (not write) permissions.
    private static final char
    QUOTE
    The quote character expected to be found in the SQL script.
    private StringBuilder
    regexOfStmtToSkip
    The regular expression to use for building statementsToSkip.
    private final Map<String,String>
    replacements
    A mapping of words to replace.
    private final Statement
    statement
    The statement created from a connection to the database.
    private Matcher
    statementsToSkip
    If non-null, the SQL statements to skip (typically because not supported by the database).

  • Constructor Summary

    Constructors
    Constructor
    Description
    ScriptRunner(Connection connection, int maxRowsPerInsert)
    Creates a new runner which will execute the statements using the given connection.

  • Method Summary

    Modifier and Type
    Method
    Description
    protected final void
    addReplacement(String inScript, String replacement)
    Declares that a word in the SQL script needs to be replaced by the given word.
    protected final void
    addStatementToSkip(String regex)
    Adds a statement to skip.
    void
    close()
    Closes the statement used by this runner.
    protected void
    editText(StringBuilder sql, int lower, int upper)
    Invoked for each text found in a SQL statement.
    protected int
    execute(StringBuilder sql)
    Executes the given SQL statement.
    protected final Connection
    getConnection()
    Returns the connection to the database.
    protected final String
    getReplacement(String inScript)
    Returns the word to use instead of the given one.
    private static boolean
    isOutsideQuotes(CharSequence sql, int from, int to)
    Returns true if the given fragment seems outside identifier quotes or text quotes.
    protected boolean
    isSupported(CharSequence sql)
    Returns true if the given SQL statements is supported by the database engine, or false if this statement should be ignored.
    protected final void
    modifyReplacements(BiFunction<String,String,String> function)
    For every entries in the replacements map, replaces the entry value by the value returned by function(key, value).
    final int
    run(Class<?> loader, String filename)
    Runs the SQL script of the given name in the same package than the given class.
    final int
    run(String statement)
    Runs the given SQL script.
    final int
    run(String filename, BufferedReader in)
    Runs the script from the given reader.
    String
    status(Locale locale)
    Returns the current position (current file and current line in that file).
    String
    toString()
    Returns a string representation of this runner for debugging purpose.

    Methods inherited from class java.lang.Object

    clone, equals, finalize, getClass, hashCode, notify, notifyAll, wait, wait, wait

  • Field Details

    • PUBLIC

      protected static final String PUBLIC
      The database user having read (not write) permissions.
      See Also:

    • COMMENT

      private static final String COMMENT
      The sequence for SQL comments. Leading lines starting by those characters will be ignored.
      See Also:

    • QUOTE

      private static final char QUOTE
      The quote character expected to be found in the SQL script. This character shall not be a whitespace or a Unicode identifier part.
      See Also:

    • IDENTIFIER_QUOTE

      private static final char IDENTIFIER_QUOTE
      The quote character for identifiers expected to be found in the SQL script. This character shall not be a whitespace or a Unicode identifier part.
      See Also:

    • END_OF_STATEMENT

      private static final char END_OF_STATEMENT
      The character at the end of statements. This character shall not be a whitespace or a Unicode identifier part.
      See Also:

    • ESCAPE

      private static final String ESCAPE
      The characters for escaping a portion of the SQL script. This is used by PostgreSQL for the definition of triggers. Those characters should appear at the beginning of a line (ignoring whitespaces), because the text before it will not be parsed.

      This string shall not begin with a whitespace or Unicode identifier part.

      See Also:

    • dialect

      private final Dialect dialect
      The presumed dialect spoken by the database.

    • replacements

      private final Map<String,String> replacements
      A mapping of words to replace. The replacements are performed only for occurrences outside identifiers or texts. See addReplacement(String, String) for more explanation.
      See Also:

    • identifierQuote

      protected final String identifierQuote
      The quote character for identifiers actually used in the database, as determined by DatabaseMetaData.getIdentifierQuoteString().

    • isEnumTypeSupported

      protected final boolean isEnumTypeSupported
      true if the database supports enums. Example:

      Notes per database product:

      • PostgreSQL: while enumeration were introduced in PostgreSQL 8.3, we require PostgreSQL 8.4 because we need the CAST … WITH INOUT feature.
      • Other databases: assumed not supported.
      See Also:

    • isCatalogSupported

      protected final boolean isCatalogSupported
      true if the database supports catalogs.

    • isSchemaSupported

      protected final boolean isSchemaSupported
      true if the database supports schemas.

    • isGrantOnSchemaSupported

      protected final boolean isGrantOnSchemaSupported
      true if the database supports "GRANT USAGE ON SCHEMA" statements. Read-only permissions are typically granted to PUBLIC. Example:
      See Also:

    • isGrantOnTableSupported

      protected final boolean isGrantOnTableSupported
      true if the database supports "GRANT SELECT ON TABLE" statements. Read-only permissions are typically granted to PUBLIC. Example:
      See Also:

    • isCommentSupported

      protected final boolean isCommentSupported
      true if the database supports the COMMENT statement. Example:
      See Also:

    • isCreateLanguageRequired

      protected final boolean isCreateLanguageRequired
      true if the following instruction shall be executed (assuming that the PostgreSQL "plpgsql" language is desired): sql CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql' HANDLER plpgsql_call_handler VALIDATOR plpgsql_validator;

      Notes per database product:

      • PostgreSQL: true only for database prior to version 9. Starting at version 9, the language is installed by default.
      • Other databases: false because not supported.

    • maxRowsPerInsert

      private final int maxRowsPerInsert
      The maximum number of rows allowed per "INSERT" statement. This is 1 if the database does not support multi-rows insertion. For other database, this is set to an arbitrary "reasonable" value since attempts to insert too many rows with a single statement on Derby database cause a StackOverflowError.

    • statement

      private final Statement statement
      The statement created from a connection to the database.

    • statementsToSkip

      private Matcher statementsToSkip
      If non-null, the SQL statements to skip (typically because not supported by the database). The matcher is built as an alternation of many regular expressions separated by the pipe symbol. The list of statements to skip depends on which is*Supported fields are set to true:

    • regexOfStmtToSkip

      private StringBuilder regexOfStmtToSkip
      The regular expression to use for building statementsToSkip. At most one of regexOfStmtToSkip and statementsToSkip shall be non-null. Both fields may be null if there are no statements to skip.

    • currentFile

      @Debug private String currentFile
      Name of the SQL script under execution, or null if unknown. This is used only for error reporting.

    • currentLine

      @Debug private int currentLine
      The line number of the SQL statement being executed. The first line in a file is numbered 1. This is used only for error reporting.

    • currentSQL

      @Debug private String currentSQL
      The SQL statement being executed. This is used only for error reporting.

  • Constructor Details

    • ScriptRunner

      public ScriptRunner(Connection connection, int maxRowsPerInsert) throws SQLException
      Creates a new runner which will execute the statements using the given connection.

      Some maxRowsPerInsert parameter values of interest:

      • A value of 0 means to create only the schemas without inserting any data in them.
      • A value of 1 means to use one separated INSERT INTO statement for each row, which may be slow.
      • A value of 100 is a value which have been found empirically as giving good results.
      • A value of Integer.MAX_VALUE means to not perform any attempt to limit the number of rows in an INSERT INTO statement. Note that this causes StackOverflowError in some JDBC driver.
      Parameters:
      connection - the connection to the database.
      maxRowsPerInsert - maximum number of rows per "INSERT INTO" statement.
      Throws:
      SQLException - if an error occurred while creating a SQL statement.

  • Method Details

    • getConnection

      protected final Connection getConnection() throws SQLException
      Returns the connection to the database.
      Returns:
      the connection.
      Throws:
      SQLException - if the connection cannot be obtained.

    • addStatementToSkip

      protected final void addStatementToSkip(String regex)
      Adds a statement to skip. By default ScriptRunner ignores the following statements: This method can be invoked for ignoring some additional statements.
      Parameters:
      regex - regular expression of the statement to ignore.

    • addReplacement

      protected final void addReplacement(String inScript, String replacement)
      Declares that a word in the SQL script needs to be replaced by the given word. The replacement is performed only for occurrences outside identifiers or texts.
      Example this is used for mapping the table names in the EPSG scripts to table names as they were in the MS-Access flavor of EPSG database. It may also contains the mapping between SQL keywords used in the SQL scripts to SQL keywords understood by the database. For example if a database does not support the "TEXT" data type, it may be replaced by "LONG VARCHAR".
      Limitation: the inScript word to replace must be a single word with no space. If the text to replace contains two words (for example "CREATE TABLE"), then revert commit bceb569558bfb7e3cf1a14aaf9261e786db06856 for bringing back this functionality.
      Parameters:
      inScript - the single word in the script which need to be replaced.
      replacement - the word(s) to use instead of inScript word.

    • getReplacement

      protected final String getReplacement(String inScript)
      Returns the word to use instead of the given one. If there is no replacement, then inScript is returned.
      Parameters:
      inScript - the word in the script which need to be replaced.
      Returns:
      the word to use instead.

    • modifyReplacements

      protected final void modifyReplacements(BiFunction<String,String,String> function)
      For every entries in the replacements map, replaces the entry value by the value returned by function(key, value).
      Parameters:
      function - the function that modify the replacement mapping.

    • run

      public final int run(String statement) throws IOException, SQLException
      Runs the given SQL script. Lines are read and grouped up to the terminal ';' character, then sent to the database.
      Parameters:
      statement - the SQL statements to execute.
      Returns:
      the number of rows added or modified as a result of the statement execution.
      Throws:
      IOException - if an error occurred while reading the input (should never happen).
      SQLException - if an error occurred while executing a SQL statement.

    • run

      public final int run(Class<?> loader, String filename) throws IOException, SQLException
      Runs the SQL script of the given name in the same package than the given class. The script is presumed encoded in UTF-8.
      Parameters:
      loader - the class to use for loading the SQL script.
      filename - the SQL script filename, relative to the loader package.
      Returns:
      the number of rows added or modified as a result of the statement execution.
      Throws:
      IOException - if an error occurred while reading the input.
      SQLException - if an error occurred while executing a SQL statement.

    • run

      public final int run(String filename, BufferedReader in) throws IOException, SQLException
      Runs the script from the given reader. Lines are read and grouped up to the terminal ';' character, then sent to the database.
      Parameters:
      filename - name of the SQL script being executed. This is used only for error reporting.
      in - the stream to read. It is caller's responsibility to close this reader.
      Returns:
      the number of rows added or modified as a result of the script execution.
      Throws:
      IOException - if an error occurred while reading the input.
      SQLException - if an error occurred while executing a SQL statement.

    • editText

      protected void editText(StringBuilder sql, int lower, int upper)
      Invoked for each text found in a SQL statement. The text, including its quote characters, is the sql substring from index lower inclusive to upper exclusive. Subclasses can override this method if they wish to modify the text content. Modifications are applied directly in the given sql buffer.
      Parameters:
      sql - the whole SQL statement.
      lower - index of the opening quote character (') of the text in sql.
      upper - index after the closing quote character (') of the text in sql.

    • isOutsideQuotes

      private static boolean isOutsideQuotes(CharSequence sql, int from, int to)
      Returns true if the given fragment seems outside identifier quotes or text quotes. The given fragment must be the beginning or the end of an SQL statement, or be bounded by indices that are known to be outside quotes. The implementation counts the occurrences of '\"' and '\'' and verifies that both of them are even.
      Parameters:
      sql - the SQL statement for which to test if a fragment is outside quotes.
      from - index of the first character of the fragment.
      to - index after the last character of the fragment.
      Returns:
      whether the given fragment seems outside quotes.

    • isSupported

      protected boolean isSupported(CharSequence sql)
      Returns true if the given SQL statements is supported by the database engine, or false if this statement should be ignored. The default implementation checks if the given query matches the regular expressions given to addStatementToSkip(String).

      This method is only a hint; a value of true is not a guaranteed that the given SQL statement is valid.

      Parameters:
      sql - the SQL statement to verify.
      Returns:
      whether the given SQL statement is supported by the database engine.

    • execute

      protected int execute(StringBuilder sql) throws SQLException, IOException
      Executes the given SQL statement. This method performs the following choices:
      • If isSupported(CharSequence) returns false, then this method does nothing.
      • If the statement is CREATE TABLE ... INHERITS ... but the database does not support table inheritance, then this method drops the INHERITS ... part.
      • If the maxRowsPerInsert argument given at construction time was zero, then this method skips "INSERT INTO" statements but executes all other.
      • Otherwise this method executes the given statement with the following modification: if the statement is an "INSERT INTO" with many values, then this method may break that statement into many "INSERT INTO" where each statements does not have move than maxRowsPerInsert rows.
      Subclasses that override this method can freely edit the StringBuilder content before to invoke this method.
      Parameters:
      sql - the SQL statement to execute.
      Returns:
      the number of rows added or modified as a result of the statement execution.
      Throws:
      SQLException - if an error occurred while executing the SQL statement.
      IOException - if an I/O operation was required and failed.

    • close

      public void close() throws SQLException
      Closes the statement used by this runner. Note that this method does not close the connection given to the constructor; this connection still needs to be closed explicitly by the caller.
      Specified by:
      close in interface AutoCloseable
      Throws:
      SQLException - if an error occurred while closing the statement.

    • status

      public String status(Locale locale)
      Returns the current position (current file and current line in that file). The returned string may also contain the SQL statement under execution. The main purpose of this method is to provide information about the position where an exception occurred.
      Parameters:
      locale - the locale for the message to return.
      Returns:
      a string representation of the current position, or null if unknown.

    • toString

      @Debug public String toString()
      Returns a string representation of this runner for debugging purpose. Current implementation returns the current position in the script being executed, and the SQL statement. This method may be invoked after a SQLException occurred in order to determine the line in the SQL script that caused the error.
      Overrides:
      toString in class Object
      Returns:
      the current position in the script being executed.