Package org.ametys.core.script

Class SQLScriptHelper

java.lang.Object

org.ametys.core.script.SQLScriptHelper

public final class SQLScriptHelper
extends Object

Example of simple use: SQLScriptHelper.createTableIfNotExists(dataSourceId, "QRTZ_JOB_DETAILS", "plugin:core://scripts/%s/quartz.sql", _sourceResolver); Will test if table QRTZ_JOB_DETAILS exits in database from datasource dataSourceId. If not, the script plugin:core://scripts/%s/quartz.sql will be resolved and executed (where %s is replaced by the database type 'mysql', 'derby'...) Tools to run SQL scripts.

Default separator for isolating statements is the semi colon character: ;.
It can be changed by using a comment like the following snippet for using the string ---:
-- _separator_=---
begin
execute immediate 'DROP TABLE MYTABLE';
Exception when others then null;
end;
---
-- _separator_=;
CREATE TABLE MYTABLE;
...
Note that the command must be placed at the end of the comment.

The runner can be configured to ignore SQLExceptions. This can be useful to execute DROP statements when it's unknown if the tables exist:
--_ignore_exceptions_=on
DROP TABLE MYTABLE;
--_ignore_exceptions_=off

Nested Class Summary

Nested Classes

Modifier and Type	Class	Description
protected static class	SQLScriptHelper.ScriptContext	Script execution context.

Field Summary

Fields

Modifier and Type	Field	Description
protected static final Logger	__LOGGER	Logger available to subclasses.
static final String	CHANGE_SEPARATOR_COMMAND	Command to change the separator.
static final String	DEFAULT_SEPARATOR	Default separator used for isolating statements.
static final String	IGNORE_EXCEPTIONS_COMMAND	Command to ignore sql exceptions.

Method Summary

Modifier and Type	Method	Description
static boolean	createTableIfNotExists(String datasourceId, String tableNameToCheck, String location, SourceResolver sourceResolver)	This method will test if a table exists, and if not will execute a script to create it
static boolean	createTableIfNotExists(String datasourceId, String tableNameToCheck, String location, SourceResolver sourceResolver, Map<String,String> replace)	This method will test if a table exists, and if not will execute a script to create it
static boolean	createTableIfNotExists(String datasourceId, String tableNameToCheck, String location, SourceResolver sourceResolver, Map<String,String> replace, String componentId, String versionId, MigrationExtensionPoint migrationEP, SqlVersionStorage sqlVersionStorage)	This method will test if a table exists, and if not will execute a script to create it
static boolean	createTableIfNotExists(Connection connection, String tableNameToCheck, String location, SourceResolver sourceResolver)	This method will test if a table exists, and if not will execute a script to create it
static boolean	createTableIfNotExists(Connection connection, String tableNameToCheck, String location, SourceResolver sourceResolver, Map<String,String> replace)	This method will test if a table exists, and if not will execute a script to create it
protected static boolean	processScriptLine(String line, StringBuilder commandBuffer, SQLScriptHelper.ScriptContext scriptContext)	Process a script line.
static void	runScript(Connection connection, InputStream is)	Run a SQL script using the connection passed in.
static void	runScript(Connection connection, String script)	Run a SQL script using the connection passed in.
static boolean	tableExists(Connection connection, String tableName)	Checks whether the given table exists in the database.

Methods inherited from class java.lang.Object

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

Field Details

DEFAULT_SEPARATOR

public static final String DEFAULT_SEPARATOR

Default separator used for isolating statements.

See Also:

• Constant Field Values

IGNORE_EXCEPTIONS_COMMAND

public static final String IGNORE_EXCEPTIONS_COMMAND

Command to ignore sql exceptions.

See Also:

• Constant Field Values

CHANGE_SEPARATOR_COMMAND

public static final String CHANGE_SEPARATOR_COMMAND

Command to change the separator.

See Also:

• Constant Field Values

__LOGGER

protected static final Logger __LOGGER

Logger available to subclasses.

Method Details

createTableIfNotExists

public static boolean createTableIfNotExists(String datasourceId,
 String tableNameToCheck,
 String location,
 SourceResolver sourceResolver)
                                      throws SQLException,
IOException

This method will test if a table exists, and if not will execute a script to create it

Parameters:

datasourceId - The data source id to open a connection to the database

tableNameToCheck - The name of the table that will be checked

location - The source location where to find the script to execute to create the table. This string will be format with String.format with the dbType as argument.

sourceResolver - The source resolver

Returns:

true if the table was created, false otherwise

Throws:

SQLException - If an error occurred while executing SQL script, or while testing table existence

IOException - If an error occurred while getting the script file, or if the url is malformed

createTableIfNotExists

public static boolean createTableIfNotExists(String datasourceId,
 String tableNameToCheck,
 String location,
 SourceResolver sourceResolver,
 Map<String,String> replace)
                                      throws SQLException,
IOException

This method will test if a table exists, and if not will execute a script to create it

Parameters:

datasourceId - The data source id to open a connection to the database

tableNameToCheck - The name of the table that will be checked

location - The source location where to find the script to execute to create the table. This string will be format with String.format with the dbType as argument.

sourceResolver - The source resolver

replace - The map of string to replace. Key is the regexp to seek, value is the replacing string.

Returns:

true if the table was created, false otherwise

Throws:

SQLException - If an error occurred while executing SQL script, or while testing table existence

IOException - If an error occurred while getting the script file, or if the url is malformed

createTableIfNotExists

public static boolean createTableIfNotExists(Connection connection,
 String tableNameToCheck,
 String location,
 SourceResolver sourceResolver)
                                      throws SQLException,
IOException

This method will test if a table exists, and if not will execute a script to create it

Parameters:

connection - The database connection to use

tableNameToCheck - The name of the table that will be checked

location - The source location where to find the script to execute to create the table. This string will be format with String.format with the dbType as argument.

sourceResolver - The source resolver

Returns:

true if the table was created, false otherwise

Throws:

SQLException - If an error occurred while executing SQL script, or while testing table existence

IOException - If an error occurred while getting the script file, or if the url is malformed

createTableIfNotExists

public static boolean createTableIfNotExists(Connection connection,
 String tableNameToCheck,
 String location,
 SourceResolver sourceResolver,
 Map<String,String> replace)
                                      throws SQLException,
IOException

This method will test if a table exists, and if not will execute a script to create it

Parameters:

connection - The database connection to use

tableNameToCheck - The name of the table that will be checked

location - The source location where to find the script to execute to create the table. This string will be format with String.format with the dbType as argument.

sourceResolver - The source resolver

replace - The map of string to replace. Key is the regexp to seek, value is the replacing string.

Returns:

true if the table was created, false otherwise

Throws:

SQLException - If an error occurred while executing SQL script, or while testing table existence

IOException - If an error occurred while getting the script file, or if the url is malformed

createTableIfNotExists

public static boolean createTableIfNotExists(String datasourceId,
 String tableNameToCheck,
 String location,
 SourceResolver sourceResolver,
 Map<String,String> replace,
 String componentId,
 String versionId,
 MigrationExtensionPoint migrationEP,
 SqlVersionStorage sqlVersionStorage)
                                      throws SQLException,
IOException

This method will test if a table exists, and if not will execute a script to create it

Parameters:

datasourceId - The data source id to open a connection to the database

tableNameToCheck - The name of the table that will be checked

location - The source location where to find the script to execute to create the table. This string will be format with String.format with the dbType as argument.

sourceResolver - The source resolver

replace - The map of string to replace. Key is the regexp to seek, value is the replacing string.

componentId - component used to get the most recent version number

versionId - version id that will be stored in database (can be the same as componentId)

migrationEP - Migration Extension Point used to read the version

sqlVersionStorage - SQJ Version Storage used to store the version

Returns:

true if the table was created, false otherwise

Throws:

SQLException - If an error occurred while executing SQL script, or while testing table existence

IOException - If an error occurred while getting the script file, or if the url is malformed

tableExists

public static boolean tableExists(Connection connection,
 String tableName)
                           throws SQLException

Checks whether the given table exists in the database.

Parameters:

connection - The database connection

tableName - the name of the table

Returns:

true is the table exists

Throws:

SQLException - In an SQL exception occurs

runScript

public static void runScript(Connection connection,
 String script)
                      throws IOException,
SQLException

Run a SQL script using the connection passed in.

Parameters:

connection - the connection to use for the script

script - the script data.

Throws:

IOException - if an error occurs while reading the script.

SQLException - if an error occurs while executing the script.

runScript

public static void runScript(Connection connection,
 InputStream is)
                      throws IOException,
SQLException

Run a SQL script using the connection passed in.

Parameters:

connection - the connection to use for the script

is - the input stream containing the script data.

Throws:

IOException - if an error occurs while reading the script.

SQLException - if an error occurs while executing the script.

processScriptLine

protected static boolean processScriptLine(String line,
 StringBuilder commandBuffer,
 SQLScriptHelper.ScriptContext scriptContext)

Process a script line.

Parameters:

line - the line to process.

commandBuffer - the command buffer.

scriptContext - the script execution context.

Returns:

true to immediately process the command (a separator was found), false to process it later.