Class ScriptRunner
java.lang.Object
org.apache.sis.internal.metadata.sql.ScriptRunner
- All Implemented Interfaces:
AutoCloseable
- Direct Known Subclasses:
EPSGInstaller
,Installer
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
FieldsModifier and TypeFieldDescriptionprivate static final String
The sequence for SQL comments.private String
Name of the SQL script under execution, ornull
if unknown.private int
The line number of the SQL statement being executed.private String
The SQL statement being executed.private final Dialect
The presumed dialect spoken by the database.private static final char
The character at the end of statements.private static final String
The characters for escaping a portion of the SQL script.private static final char
The quote character for identifiers expected to be found in the SQL script.protected final String
The quote character for identifiers actually used in the database, as determined byDatabaseMetaData.getIdentifierQuoteString()
.protected final boolean
true
if the database supports catalogs.protected final boolean
true
if the database supports theCOMMENT
statement.protected final boolean
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
true
if the database supports enums.protected final boolean
true
if the database supports"GRANT USAGE ON SCHEMA"
statements.protected final boolean
true
if the database supports"GRANT SELECT ON TABLE"
statements.protected final boolean
true
if the database supports schemas.private final int
The maximum number of rows allowed per"INSERT"
statement.protected static final String
The database user having read (not write) permissions.private static final char
The quote character expected to be found in the SQL script.private StringBuilder
The regular expression to use for buildingstatementsToSkip
.A mapping of words to replace.private final Statement
The statement created from a connection to the database.private Matcher
If non-null, the SQL statements to skip (typically because not supported by the database). -
Constructor Summary
ConstructorsConstructorDescriptionScriptRunner
(Connection connection, int maxRowsPerInsert) Creates a new runner which will execute the statements using the given connection. -
Method Summary
Modifier and TypeMethodDescriptionprotected 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
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) Returnstrue
if the given fragment seems outside identifier quotes or text quotes.protected boolean
isSupported
(CharSequence sql) Returnstrue
if the given SQL statements is supported by the database engine, orfalse
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 byfunction(key, value)
.final int
Runs the SQL script of the given name in the same package than the given class.final int
Runs the given SQL script.final int
run
(String filename, BufferedReader in) Runs the script from the given reader.Returns the current position (current file and current line in that file).toString()
Returns a string representation of this runner for debugging purpose.
-
Field Details
-
PUBLIC
The database user having read (not write) permissions.- See Also:
-
COMMENT
The sequence for SQL comments. Leading lines starting by those characters will be ignored.- See Also:
-
QUOTE
private static final char QUOTEThe 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_QUOTEThe 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_STATEMENTThe character at the end of statements. This character shall not be a whitespace or a Unicode identifier part.- See Also:
-
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
The presumed dialect spoken by the database. -
replacements
A mapping of words to replace. The replacements are performed only for occurrences outside identifiers or texts. SeeaddReplacement(String, String)
for more explanation.- See Also:
-
identifierQuote
The quote character for identifiers actually used in the database, as determined byDatabaseMetaData.getIdentifierQuoteString()
. -
isEnumTypeSupported
protected final boolean isEnumTypeSupportedtrue
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:
- PostgreSQL: while enumeration were introduced in PostgreSQL 8.3,
we require PostgreSQL 8.4 because we need the
-
isCatalogSupported
protected final boolean isCatalogSupportedtrue
if the database supports catalogs. -
isSchemaSupported
protected final boolean isSchemaSupportedtrue
if the database supports schemas. -
isGrantOnSchemaSupported
protected final boolean isGrantOnSchemaSupportedtrue
if the database supports"GRANT USAGE ON SCHEMA"
statements. Read-only permissions are typically granted toPUBLIC
. Example:- See Also:
-
isGrantOnTableSupported
protected final boolean isGrantOnTableSupportedtrue
if the database supports"GRANT SELECT ON TABLE"
statements. Read-only permissions are typically granted toPUBLIC
. Example:- See Also:
-
isCommentSupported
protected final boolean isCommentSupportedtrue
if the database supports theCOMMENT
statement. Example:- See Also:
-
isCreateLanguageRequired
protected final boolean isCreateLanguageRequiredtrue
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.
- PostgreSQL:
-
maxRowsPerInsert
private final int maxRowsPerInsertThe 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 aStackOverflowError
. -
statement
The statement created from a connection to the database. -
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 whichis*Supported
fields are set totrue
:isEnumTypeSupported
for"CREATE TYPE …"
or"CREATE CAST …"
statements.isGrantOnSchemaSupported
for"GRANT USAGE ON SCHEMA …"
statements.isGrantOnTableSupported
for"GRANT SELECT ON TABLE …"
statements.isCommentSupported
for"COMMENT ON …"
statements.
-
regexOfStmtToSkip
The regular expression to use for buildingstatementsToSkip
. At most one ofregexOfStmtToSkip
andstatementsToSkip
shall be non-null. Both fields may be null if there are no statements to skip. -
currentFile
Name of the SQL script under execution, ornull
if unknown. This is used only for error reporting. -
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
The SQL statement being executed. This is used only for error reporting.
-
-
Constructor Details
-
ScriptRunner
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 anINSERT INTO
statement. Note that this causesStackOverflowError
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
Returns the connection to the database.- Returns:
- the connection.
- Throws:
SQLException
- if the connection cannot be obtained.
-
addStatementToSkip
Adds a statement to skip. By defaultScriptRunner
ignores the following statements:"CREATE TYPE …"
or"CREATE CAST …"
ifisEnumTypeSupported
isfalse
."GRANT USAGE ON SCHEMA …"
ifisGrantOnSchemaSupported
isfalse
."GRANT SELECT ON TABLE …"
ifisGrantOnTableSupported
isfalse
."COMMENT ON …"
ifisCommentSupported
isfalse
.
- Parameters:
regex
- regular expression of the statement to ignore.
-
addReplacement
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 theLimitation: the"TEXT"
data type, it may be replaced by"LONG VARCHAR"
.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 commitbceb569558bfb7e3cf1a14aaf9261e786db06856
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 ofinScript
word.
-
getReplacement
Returns the word to use instead of the given one. If there is no replacement, theninScript
is returned.- Parameters:
inScript
- the word in the script which need to be replaced.- Returns:
- the word to use instead.
-
modifyReplacements
For every entries in the replacements map, replaces the entry value by the value returned byfunction(key, value)
.- Parameters:
function
- the function that modify the replacement mapping.
-
run
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
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 theloader
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
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
Invoked for each text found in a SQL statement. The text, including its quote characters, is thesql
substring from indexlower
inclusive toupper
exclusive. Subclasses can override this method if they wish to modify the text content. Modifications are applied directly in the givensql
buffer.- Parameters:
sql
- the whole SQL statement.lower
- index of the opening quote character ('
) of the text insql
.upper
- index after the closing quote character ('
) of the text insql
.
-
isOutsideQuotes
Returnstrue
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
Returnstrue
if the given SQL statements is supported by the database engine, orfalse
if this statement should be ignored. The default implementation checks if the given query matches the regular expressions given toaddStatementToSkip(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
Executes the given SQL statement. This method performs the following choices:- If
isSupported(CharSequence)
returnsfalse
, then this method does nothing. - If the statement is
CREATE TABLE ... INHERITS ...
but the database does not support table inheritance, then this method drops theINHERITS ...
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 thanmaxRowsPerInsert
rows.
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.
- If
-
close
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 interfaceAutoCloseable
- Throws:
SQLException
- if an error occurred while closing the statement.
-
status
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
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 aSQLException
occurred in order to determine the line in the SQL script that caused the error.
-