Package org.dellroad.stuff.schema

Class SQLSchemaUpdater

java.lang.Object

org.dellroad.stuff.schema.AbstractSchemaUpdater<DataSource,Connection>

org.dellroad.stuff.schema.SQLSchemaUpdater

Direct Known Subclasses:

SpringSQLSchemaUpdater

public class SQLSchemaUpdater
extends AbstractSchemaUpdater<DataSource,Connection>

Concrete extension of AbstractSchemaUpdater for SQL databases.

Required properties are the database initialization, update table initialization, and the updates themselves.

Applied updates are recorded in a special update table, which contains two columns: one for the unique update name and one for a timestamp. The update table and column names are configurable via setUpdateTableName(), setUpdateTableNameColumn(), and setUpdateTableTimeColumn().

By default, this class detects a completely uninitialized database by the absence of the update table itself in the schema (see databaseNeedsInitialization()). When an uninitialized database is encountered, the configured database initialization and update table initialization actions are applied first to initialize the database schema.

Field Summary

Fields

Modifier and Type	Field	Description
static final String	DEFAULT_UPDATE_TABLE_NAME	Default name of the table that tracks schema updates, "SchemaUpdate".
static final String	DEFAULT_UPDATE_TABLE_NAME_COLUMN	Default name of the column in the updates table holding the unique update name, "updateName".
static final String	DEFAULT_UPDATE_TABLE_TIME_COLUMN	Default name of the column in the updates table holding the update's time applied, "updateTime".

Fields inherited from class org.dellroad.stuff.schema.AbstractSchemaUpdater

log

Constructor Summary

Constructors

Constructor	Description
SQLSchemaUpdater()

Method Summary

Modifier and Type	Method	Description
protected void	apply(Connection c, DatabaseAction<Connection> action)	Execute a database action within an existing transaction.
protected void	commitTransaction(Connection c)	Commit a previously opened transaction.
protected boolean	databaseNeedsInitialization(Connection c)	Determine if the database needs initialization.
protected Set<String>	getAppliedUpdateNames(Connection c)	Determine which updates have already been applied.
SQLCommandList	getDatabaseInitialization()	Get the empty database initialization.
int	getTransactionIsolation()	Get transaction isolation level for the schema check/migration transaction.
SQLCommandList	getUpdateTableInitialization()	Get the update table initialization.
String	getUpdateTableName()	Get the name of the table that keeps track of applied updates.
String	getUpdateTableNameColumn()	Get the name of the update name column in the table that keeps track of applied updates.
String	getUpdateTableTimeColumn()	Get the name of the update timestamp column in the table that keeps track of applied updates.
protected boolean	indicatesUninitializedDatabase(Connection c, SQLException e)	Determine if an exception thrown during databaseNeedsInitialization(java.sql.Connection) is consistent with an uninitialized database.
void	initializeAndUpdateDatabase(DataSource dataSource)	Perform database schema initialization and updates.
protected void	initializeDatabase(Connection c)	Initialize an uninitialized database.
protected Connection	openTransaction(DataSource dataSource)	Begin a transaction on the given connection.
protected void	recordUpdateApplied(Connection c, String updateName)	Record an update as having been applied.
protected void	rollbackTransaction(Connection c)	Roll back a previously opened transaction.
void	setDatabaseInitialization(SQLCommandList databaseInitialization)	Configure how an empty database gets initialized.
void	setTransactionIsolation(int transactionIsolation)	Set transaction isolation level for the schema check/migration transaction.
void	setUpdateTableInitialization(SQLCommandList updateTableInitialization)	Configure how the update table itself gets initialized.
void	setUpdateTableName(String updateTableName)	Set the name of the table that keeps track of applied updates.
void	setUpdateTableNameColumn(String updateTableNameColumn)	Set the name of the update name column in the table that keeps track of applied updates.
void	setUpdateTableTimeColumn(String updateTableTimeColumn)	Set the name of the update timestamp column in the table that keeps track of applied updates.

Methods inherited from class org.dellroad.stuff.schema.AbstractSchemaUpdater

applyInTransaction, generateMultiUpdateName, getAllUpdateNames, getOrderingTieBreaker, getUpdates, isIgnoreUnrecognizedUpdates, isValidUpdateName, setIgnoreUnrecognizedUpdates, setUpdates

Methods inherited from class java.lang.Object

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

Field Details

DEFAULT_UPDATE_TABLE_NAME

public static final String DEFAULT_UPDATE_TABLE_NAME

Default name of the table that tracks schema updates, "SchemaUpdate".

See Also:

• Constant Field Values

DEFAULT_UPDATE_TABLE_NAME_COLUMN

public static final String DEFAULT_UPDATE_TABLE_NAME_COLUMN

Default name of the column in the updates table holding the unique update name, "updateName".

See Also:

• Constant Field Values

DEFAULT_UPDATE_TABLE_TIME_COLUMN

public static final String DEFAULT_UPDATE_TABLE_TIME_COLUMN

Default name of the column in the updates table holding the update's time applied, "updateTime".

See Also:

• Constant Field Values

Constructor Details

SQLSchemaUpdater

public SQLSchemaUpdater()

Method Details

getUpdateTableName

public String getUpdateTableName()

Get the name of the table that keeps track of applied updates.

Returns:

the name of the update table

See Also:

• setUpdateTableName()

setUpdateTableName

public void setUpdateTableName(String updateTableName)

Set the name of the table that keeps track of applied updates. Default value is DEFAULT_UPDATE_TABLE_NAME.

This name must be consistent with the update table initialization.

Parameters:

updateTableName - the name of the update table

getUpdateTableNameColumn

public String getUpdateTableNameColumn()

Get the name of the update name column in the table that keeps track of applied updates.

Returns:

the name of the name column in the update table

See Also:

• setUpdateTableNameColumn()

setUpdateTableNameColumn

public void setUpdateTableNameColumn(String updateTableNameColumn)

Set the name of the update name column in the table that keeps track of applied updates. Default value is DEFAULT_UPDATE_TABLE_NAME_COLUMN.

This name must be consistent with the update table initialization.

Parameters:

updateTableNameColumn - the name of the name column in the update table

getUpdateTableTimeColumn

public String getUpdateTableTimeColumn()

Get the name of the update timestamp column in the table that keeps track of applied updates.

Returns:

the name of the timestamp column in the update table

See Also:

• setUpdateTableTimeColumn()

setUpdateTableTimeColumn

public void setUpdateTableTimeColumn(String updateTableTimeColumn)

Set the name of the update timestamp column in the table that keeps track of applied updates. Default value is DEFAULT_UPDATE_TABLE_TIME_COLUMN.

This name must be consistent with the update table initialization.

Parameters:

updateTableTimeColumn - the name of the timestamp column in the update table

getUpdateTableInitialization

public SQLCommandList getUpdateTableInitialization()

Get the update table initialization.

Returns:

SQL commands to create the update table

See Also:

• setUpdateTableInitialization()

setUpdateTableInitialization

public void setUpdateTableInitialization(SQLCommandList updateTableInitialization)

Configure how the update table itself gets initialized. This update is run when no update table found, which (we assume) implies an empty database with no tables or content. This is a required property.

This initialization should create the update table where the name column is the primary key. The name column must have a length limit greater than or equal to the longest schema update name.

The table and column names must be consistent with the values configured via setUpdateTableName(), setUpdateTableNameColumn(), and setUpdateTableTimeColumn().

For convenience, pre-defined initialization scripts using the default table and column names are available at the following resource locations. These can be used to configure a SQLCommandList:

Database	Resource
MySQL (InnoDB)	classpath:org/dellroad/stuff/schema/updateTable-mysql.sql
Oracle	classpath:org/dellroad/stuff/schema/updateTable-oracle.sql
Postgres	classpath:org/dellroad/stuff/schema/updateTable-postgres.sql
HSQLDB	classpath:org/dellroad/stuff/schema/updateTable-hsqldb.sql

Parameters:

updateTableInitialization - update table schema initialization

See Also:

• setUpdateTableName()
• setUpdateTableNameColumn()
• setUpdateTableTimeColumn()

getDatabaseInitialization

public SQLCommandList getDatabaseInitialization()

Get the empty database initialization.

Returns:

SQL commands to initialize an empty database

See Also:

• setDatabaseInitialization()

setDatabaseInitialization

public void setDatabaseInitialization(SQLCommandList databaseInitialization)

Configure how an empty database gets initialized. This is a required property.

This update is run when no update table found, which (we assume) implies an empty database with no tables or content. Typically this contains the SQL script that gets automatically generated by your favorite schema generation tool.

This script is expected to initialize the database schema (i.e., creating all the tables) so that when completed the database is "up to date" with respect to the configured schema updates. That is, when this action completes, we assume all updates have already been (implicitly) applied (and they will be recorded as such).

Note this script is not expected to create the update table that tracks schema updates; that function is handled by the update table initialization.

Parameters:

databaseInitialization - application database schema initialization

getTransactionIsolation

public int getTransactionIsolation()

Get transaction isolation level for the schema check/migration transaction.

Default is Connection.TRANSACTION_SERIALIZABLE.

Returns:

transaction isolation level, or -1 to leave it alone

setTransactionIsolation

public void setTransactionIsolation(int transactionIsolation)

Set transaction isolation level for the schema check/migration transaction.

Default is Connection.TRANSACTION_SERIALIZABLE.

Parameters:

transactionIsolation - transaction isolation level, or -1 to leave it alone

apply

protected void apply(Connection c,
 DatabaseAction<Connection> action)
              throws SQLException

Description copied from class: AbstractSchemaUpdater

Execute a database action within an existing transaction.

All database operations in AbstractSchemaUpdater are performed via this method; subclasses are encouraged to follow this pattern.

The implementation in AbstractSchemaUpdater simply invokes action.apply(); subclasses may override if desired.

Overrides:

apply in class AbstractSchemaUpdater<DataSource,Connection>

Parameters:

c - transaction within which to apply action

action - operation to perform

Throws:

SQLException

initializeAndUpdateDatabase

public void initializeAndUpdateDatabase(DataSource dataSource)
                                 throws SQLException

Description copied from class: AbstractSchemaUpdater

Perform database schema initialization and updates.

This method applies the following logic: if the database needs initialization, then initialize the database and record each update as having been applied; otherwise, apply any unapplied updates as needed.

Note this implies the database initialization must initialize the database to its current, up-to-date state (with respect to the set of all available updates), not its original, pre-update state.

The database initialization step, and each of the update steps, is performed within its own transaction.

Overrides:

initializeAndUpdateDatabase in class AbstractSchemaUpdater<DataSource,Connection>

Parameters:

dataSource - the database to initialize (if necessary) and update

Throws:

SQLException - if an update fails

IllegalStateException - if the database needs initialization and either the database initialization or the update table initialization has not been configured

IllegalStateException

IllegalArgumentException - if any configured update has a required predecessor which is not also a configured update (i.e., if the updates are not transitively closed under predecessors)

openTransaction

protected Connection openTransaction(DataSource dataSource)
                              throws SQLException

Begin a transaction on the given connection.

The implementation in SQLSchemaUpdater creates a serializable-level transaction.

Specified by:

openTransaction in class AbstractSchemaUpdater<DataSource,Connection>

Parameters:

dataSource - the database on which to open the transaction

Returns:

new Connection with an open transaction

Throws:

SQLException - if an error occurs while accessing the database

commitTransaction

protected void commitTransaction(Connection c)
                          throws SQLException

Commit a previously opened transaction.

The implementation in SQLSchemaUpdater just invokes Connection.commit().

Specified by:

commitTransaction in class AbstractSchemaUpdater<DataSource,Connection>

Parameters:

c - the connection on which to commit the transaction

Throws:

SQLException - if an error occurs while accessing the database

rollbackTransaction

protected void rollbackTransaction(Connection c)
                            throws SQLException

Roll back a previously opened transaction. This method will also be invoked if commitTransaction() throws an exception.

The implementation in SQLSchemaUpdater just invokes Connection.rollback().

Specified by:

rollbackTransaction in class AbstractSchemaUpdater<DataSource,Connection>

Parameters:

c - the connection on which to roll back the transaction

Throws:

SQLException - if an error occurs while accessing the database

databaseNeedsInitialization

protected boolean databaseNeedsInitialization(Connection c)
                                       throws SQLException

Determine if the database needs initialization.

The implementation in SQLSchemaUpdater simply invokes SELECT COUNT(*) FROM UPDATETABLE and checks for success or failure. If an exception is thrown, indicatesUninitializedDatabase(java.sql.Connection, java.sql.SQLException) is used to distinguish between an exception caused by an uninitialized database and a truly unexpected one.

Specified by:

databaseNeedsInitialization in class AbstractSchemaUpdater<DataSource,Connection>

Parameters:

c - connection to the database

Returns:

true if the database needs initialization, otherwise false

Throws:

SQLException - if an unexpected error occurs while accessing the database

indicatesUninitializedDatabase

protected boolean indicatesUninitializedDatabase(Connection c,
 SQLException e)
                                          throws SQLException

Determine if an exception thrown during databaseNeedsInitialization(java.sql.Connection) is consistent with an uninitialized database.

This should return true if the exception would be thrown by an SQL query that attempts to access a non-existent table. For exceptions thrown by other causes, this should return false.

The implementation in SQLSchemaUpdater always returns true. Subclasses are encouraged to override with a more precise implementation.

Parameters:

c - connection on which the exception occurred

e - exception thrown during database access in databaseNeedsInitialization(java.sql.Connection)

Returns:

true if e indicates an uninitialized database

Throws:

SQLException - if an error occurs

See Also:

• databaseNeedsInitialization(java.sql.Connection)

recordUpdateApplied

protected void recordUpdateApplied(Connection c,
 String updateName)
                            throws SQLException

Record an update as having been applied.

The implementation in SQLSchemaUpdater does the standard JDBC thing using an INSERT statement into the update table.

Specified by:

recordUpdateApplied in class AbstractSchemaUpdater<DataSource,Connection>

Parameters:

c - SQL connection

updateName - update name

Throws:

IllegalStateException - if the update has already been recorded in the database

SQLException - if an error occurs while accessing the database

getAppliedUpdateNames

protected Set<String> getAppliedUpdateNames(Connection c)
                                     throws SQLException

Determine which updates have already been applied.

The implementation in SQLSchemaUpdater does the standard JDBC thing using a SELECT statement from the update table.

Specified by:

getAppliedUpdateNames in class AbstractSchemaUpdater<DataSource,Connection>

Parameters:

c - open transaction

Returns:

set of already-applied updates

Throws:

SQLException - if an error occurs while accessing the database

initializeDatabase

protected void initializeDatabase(Connection c)
                           throws SQLException

Description copied from class: AbstractSchemaUpdater

Initialize an uninitialized database. This should create and initialize the database schema and content, including whatever portion of that is used to track schema updates.

Specified by:

initializeDatabase in class AbstractSchemaUpdater<DataSource,Connection>

Parameters:

c - open transaction

Throws:

SQLException