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

Class ScriptRunner

java.lang.Object
org.apache.sis.internal.metadata.sql.ScriptRunner
All Implemented Interfaces:
AutoCloseable
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
Author:
Martin Desruisseaux (Geomatys), Johann Sorel (Geomatys)

  • Field Summary

    Fields
    Modifier and Type
    Field
    Description
    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.
    protected static final String
    PUBLIC
    The database user having read (not write) permissions.

  • 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.
    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:

    • 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:
      • statementsToSkip

    • 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:
      • statementsToSkip

    • 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:
      • statementsToSkip

    • isCommentSupported

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

    • 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.

  • 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.

    • 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.