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

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

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

  • 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

      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

      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

      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

      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:

    • getDatabaseInitialization

      public SQLCommandList getDatabaseInitialization()
      Get the empty database initialization.
      Returns:
      SQL commands to initialize an empty database
      See Also:

    • 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
      IllegalArgumentException - if two configured updates have the same name
      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(Connection, 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(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(Connection)
      Returns:
      true if e indicates an uninitialized database
      Throws:
      SQLException - if an error occurs
      See Also:

    • 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