001
002 /*
003 * Copyright (C) 2011 Archie L. Cobbs. All rights reserved.
004 *
005 * $Id: SQLSchemaUpdater.java 270 2012-02-02 14:49:15Z archie.cobbs $
006 */
007
008 package org.dellroad.stuff.schema;
009
010 import java.sql.Connection;
011 import java.sql.PreparedStatement;
012 import java.sql.ResultSet;
013 import java.sql.SQLException;
014 import java.sql.Statement;
015 import java.util.Date;
016 import java.util.HashSet;
017 import java.util.Set;
018
019 import javax.sql.DataSource;
020
021 /**
022 * Concrete extension of {@link AbstractSchemaUpdater} for SQL databases.
023 *
024 * <p>
025 * Required properties are the {@linkplain #setDatabaseInitialization database initialization},
026 * {@linkplain #setUpdateTableInitialization update table initialization}, and the {@linkplain #setUpdates updates} themselves.
027 * </p>
028 *
029 * <p>
030 * Applied updates are recorded in a special <i>update table</i>, which contains two columns: one for the unique
031 * {@linkplain SchemaUpdate#getName update name} and one for a timestamp. The update table and column names
032 * are configurable via {@link #setUpdateTableName setUpdateTableName()},
033 * {@link #setUpdateTableNameColumn setUpdateTableNameColumn()}, and {@link #setUpdateTableTimeColumn setUpdateTableTimeColumn()}.
034 * </p>
035 *
036 * <p>
037 * By default, this class detects a completely uninitialized database by the absence of the update table itself
038 * in the schema (see {@link #databaseNeedsInitialization databaseNeedsInitialization()}).
039 * When an uninitialized database is encountered, the configured {@linkplain #setDatabaseInitialization database initialization}
040 * and {@linkplain #setUpdateTableInitialization update table initialization} actions are applied first to initialize
041 * the database schema.
042 * </p>
043 */
044 public class SQLSchemaUpdater extends AbstractSchemaUpdater<DataSource, Connection> {
045
046 /**
047 * Default nefault name of the table that tracks schema updates, <code>{@value}</code>.
048 */
049 public static final String DEFAULT_UPDATE_TABLE_NAME = "SchemaUpdate";
050
051 /**
052 * Default name of the column in the updates table holding the unique update name, <code>{@value}</code>.
053 */
054 public static final String DEFAULT_UPDATE_TABLE_NAME_COLUMN = "updateName";
055
056 /**
057 * Default name of the column in the updates table holding the update's time applied, <code>{@value}</code>.
058 */
059 public static final String DEFAULT_UPDATE_TABLE_TIME_COLUMN = "updateTime";
060
061 private String updateTableName = DEFAULT_UPDATE_TABLE_NAME;
062 private String updateTableNameColumn = DEFAULT_UPDATE_TABLE_NAME_COLUMN;
063 private String updateTableTimeColumn = DEFAULT_UPDATE_TABLE_TIME_COLUMN;
064
065 private SQLCommandList databaseInitialization;
066 private SQLCommandList updateTableInitialization;
067
068 /**
069 * Get the name of the table that keeps track of applied updates.
070 *
071 * @see #setUpdateTableName setUpdateTableName()
072 */
073 public String getUpdateTableName() {
074 return this.updateTableName;
075 }
076
077 /**
078 * Set the name of the table that keeps track of applied updates.
079 * Default value is {@link #DEFAULT_UPDATE_TABLE_NAME}.
080 *
081 * <p>
082 * This name must be consistent with the {@linkplain #setUpdateTableInitialization update table initialization}.
083 */
084 public void setUpdateTableName(String updateTableName) {
085 this.updateTableName = updateTableName;
086 }
087
088 /**
089 * Get the name of the update name column in the table that keeps track of applied updates.
090 *
091 * @see #setUpdateTableNameColumn setUpdateTableNameColumn()
092 */
093 public String getUpdateTableNameColumn() {
094 return this.updateTableNameColumn;
095 }
096
097 /**
098 * Set the name of the update name column in the table that keeps track of applied updates.
099 * Default value is {@link #DEFAULT_UPDATE_TABLE_NAME_COLUMN}.
100 *
101 * <p>
102 * This name must be consistent with the {@linkplain #setUpdateTableInitialization update table initialization}.
103 */
104 public void setUpdateTableNameColumn(String updateTableNameColumn) {
105 this.updateTableNameColumn = updateTableNameColumn;
106 }
107
108 /**
109 * Get the name of the update timestamp column in the table that keeps track of applied updates.
110 *
111 * @see #setUpdateTableTimeColumn setUpdateTableTimeColumn()
112 */
113 public String getUpdateTableTimeColumn() {
114 return this.updateTableTimeColumn;
115 }
116
117 /**
118 * Set the name of the update timestamp column in the table that keeps track of applied updates.
119 * Default value is {@link #DEFAULT_UPDATE_TABLE_TIME_COLUMN}.
120 *
121 * <p>
122 * This name must be consistent with the {@linkplain #setUpdateTableInitialization update table initialization}.
123 */
124 public void setUpdateTableTimeColumn(String updateTableTimeColumn) {
125 this.updateTableTimeColumn = updateTableTimeColumn;
126 }
127
128 /**
129 * Get the update table initialization.
130 *
131 * @see #setUpdateTableInitialization setUpdateTableInitialization()
132 */
133 public SQLCommandList getUpdateTableInitialization() {
134 return this.updateTableInitialization;
135 }
136
137 /**
138 * Configure how the update table itself gets initialized. This update is run when no update table found,
139 * which (we assume) implies an empty database with no tables or content. This is a required property.
140 *
141 * <p>
142 * This initialization should create the update table where the name column is the primary key.
143 * The name column must have a length limit greater than or equal to the longest schema update name.
144 *
145 * <p>
146 * The table and column names must be consistent with the values configured via
147 * {@link #setUpdateTableName setUpdateTableName()}, {@link #setUpdateTableNameColumn setUpdateTableNameColumn()},
148 * and {@link #setUpdateTableTimeColumn setUpdateTableTimeColumn()}.
149 *
150 * <p>
151 * For convenience, pre-defined initialization scripts using the default table and column names are available
152 * at the following resource locations. These can be used to configure a {@link SQLCommandList}:
153 * <table border="1" cellspacing="0" cellpadding="4">
154 * <tr>
155 * <th>Database</th>
156 * <th>Resource</th>
157 * </tr>
158 * <tr>
159 * </tr>
160 * <td>MySQL (InnoDB)</td>
161 * <td><code>classpath:org/dellroad/stuff/schema/updateTable-mysql.sql</code></td>
162 * </tr>
163 * </table>
164 *
165 * @param updateTableInitialization update table schema initialization
166 * @see #setUpdateTableName setUpdateTableName()
167 * @see #setUpdateTableNameColumn setUpdateTableNameColumn()
168 * @see #setUpdateTableTimeColumn setUpdateTableTimeColumn()
169 */
170 public void setUpdateTableInitialization(SQLCommandList updateTableInitialization) {
171 this.updateTableInitialization = updateTableInitialization;
172 }
173
174 /**
175 * Get the empty database initialization.
176 *
177 * @see #setDatabaseInitialization setDatabaseInitialization()
178 */
179 public SQLCommandList getDatabaseInitialization() {
180 return this.databaseInitialization;
181 }
182
183 /**
184 * Configure how an empty database gets initialized. This is a required property.
185 *
186 * <p>
187 * This update is run when no update table found, which (we assume) implies an empty database with no tables or content.
188 * Typically this contains the SQL script that gets automatically generated by your favorite schema generation tool.
189 *
190 * <p>
191 * This script is expected to initialize the database schema (i.e., creating all the tables) so that
192 * when completed the database is "up to date" with respect to the configured schema updates.
193 * That is, when this action completes, we assume all updates have already been (implicitly) applied
194 * (and they will be recorded as such).
195 *
196 * <p>
197 * Note this script is <i>not</i> expected to create the update table that tracks schema updates;
198 * that function is handled by the {@linkplain #setUpdateTableInitialization update table initialization}.
199 *
200 * @param databaseInitialization application database schema initialization
201 */
202 public void setDatabaseInitialization(SQLCommandList databaseInitialization) {
203 this.databaseInitialization = databaseInitialization;
204 }
205
206 @Override
207 protected void apply(Connection c, DatabaseAction<Connection> action) throws SQLException {
208 try {
209 super.apply(c, action);
210 } catch (SQLException e) {
211 throw e;
212 } catch (RuntimeException e) {
213 throw e;
214 } catch (Error e) {
215 throw e;
216 } catch (Exception e) {
217 throw new RuntimeException(e);
218 }
219 }
220
221
222 /**
223 * @throws Exception {@inheritDoc}
224 * @throws IllegalStateException if the database needs initialization and either the
225 * {@linkplain #setDatabaseInitialization database initialization} or
226 * the {@linkplain #setUpdateTableInitialization update table initialization} has not been configured
227 * @throws IllegalStateException {@inheritDoc}
228 * @throws IllegalArgumentException {@inheritDoc}
229 */
230 @Override
231 public synchronized void initializeAndUpdateDatabase(DataSource dataSource) throws SQLException {
232 try {
233 super.initializeAndUpdateDatabase(dataSource);
234 } catch (SQLException e) {
235 throw e;
236 } catch (RuntimeException e) {
237 throw e;
238 } catch (Error e) {
239 throw e;
240 } catch (Exception e) {
241 throw new RuntimeException(e);
242 }
243 }
244
245 /**
246 * Begin a transaction on the given connection.
247 *
248 * <p>
249 * The implementation in {@link SQLSchemaUpdater} creates a serializable-level transaction.
250 *
251 * @param dataSource the database on which to open the transaction
252 * @return new {@link Connection} with an open transaction
253 * @throws SQLException if an error occurs while accessing the database
254 */
255 @Override
256 protected Connection openTransaction(DataSource dataSource) throws SQLException {
257 Connection c = dataSource.getConnection();
258 c.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
259 c.setAutoCommit(false);
260 return c;
261 }
262
263 /**
264 * Commit a previously opened transaction.
265 *
266 * <p>
267 * The implementation in {@link SQLSchemaUpdater} just invokes {@link Connection#commit}.
268 *
269 * @param c the connection on which to commit the transaction
270 * @throws SQLException if an error occurs while accessing the database
271 */
272 @Override
273 protected void commitTransaction(Connection c) throws SQLException {
274 c.commit();
275 c.close();
276 }
277
278 /**
279 * Roll back a previously opened transaction.
280 * This method will also be invoked if {@link #commitTransaction commitTransaction()} throws an exception.
281 *
282 * <p>
283 * The implementation in {@link SQLSchemaUpdater} just invokes {@link Connection#rollback}.
284 *
285 * @param c the connection on which to roll back the transaction
286 * @throws SQLException if an error occurs while accessing the database
287 */
288 @Override
289 protected void rollbackTransaction(Connection c) throws SQLException {
290 c.rollback();
291 c.close();
292 }
293
294 /**
295 * Determine if the database needs initialization.
296 *
297 * <p>
298 * The implementation in {@link SQLSchemaUpdater} simply invokes <code>SELECT COUNT(*) FROM <i>UPDATETABLE</i></code>
299 * and checks for success or failure. If an exception is thrown, {@link #indicatesUninitializedDatabase} is used
300 * to distinguish between an exception caused by an uninitialized database and a truly unexpected one.
301 *
302 * @param c connection to the database
303 * @throws SQLException if an unexpected error occurs while accessing the database
304 */
305 @Override
306 protected boolean databaseNeedsInitialization(Connection c) throws SQLException {
307 final boolean[] result = new boolean[1];
308 this.apply(c, new SQLCommand("SELECT COUNT(*) FROM " + this.getUpdateTableName()) {
309 @Override
310 public void apply(Connection c) throws SQLException {
311 Statement s = c.createStatement();
312 try {
313 ResultSet resultSet;
314 try {
315 resultSet = s.executeQuery(this.getSQL());
316 } catch (SQLException e) {
317 if (SQLSchemaUpdater.this.indicatesUninitializedDatabase(c, e)) {
318 SQLSchemaUpdater.this.log.warn("detected an uninitialized database");
319 result[0] = true;
320 return;
321 }
322 throw e;
323 }
324 if (!resultSet.next())
325 throw new IllegalStateException("zero rows returned by `" + this.getSQL() + "'");
326 SQLSchemaUpdater.this.log.info("detected initialized database, with "
327 + resultSet.getLong(1) + " update(s) already applied");
328 } finally {
329 s.close();
330 }
331 }
332 });
333 return result[0];
334 }
335
336 /**
337 * Determine if an exception thrown during {@link #databaseNeedsInitialization} is consistent with
338 * an uninitialized database.
339 *
340 * <p>
341 * This should return true if the exception would be thrown by an SQL query that attempts to access a non-existent table.
342 * For exceptions thrown by other causes, this should return false.
343 *
344 * <p>
345 * The implementation in {@link SQLSchemaUpdater} always returns true. Subclasses are encouraged to override
346 * with a more precise implementation.
347 *
348 * @param c connection on which the exception occurred
349 * @param e exception thrown during database access in {@link #databaseNeedsInitialization}
350 * @see #databaseNeedsInitialization
351 * @throws SQLException if an error occurs
352 */
353 protected boolean indicatesUninitializedDatabase(Connection c, SQLException e) throws SQLException {
354 return true;
355 }
356
357 /**
358 * Record an update as having been applied.
359 *
360 * <p>
361 * The implementation in {@link SQLSchemaUpdater} does the standard JDBC thing using an INSERT statement
362 * into the update table.
363 * </p>
364 *
365 * @param c SQL connection
366 * @param updateName update name
367 * @throws IllegalStateException if the update has already been recorded in the database
368 * @throws SQLException if an error occurs while accessing the database
369 */
370 @Override
371 protected void recordUpdateApplied(Connection c, final String updateName) throws SQLException {
372 this.apply(c, new SQLCommand("INSERT INTO " + this.getUpdateTableName()
373 + " (" + this.getUpdateTableNameColumn() + ", " + this.getUpdateTableTimeColumn() + ") VALUES (?, ?)") {
374 @Override
375 public void apply(Connection c) throws SQLException {
376 PreparedStatement s = c.prepareStatement(this.getSQL());
377 try {
378 s.setString(1, updateName);
379 s.setDate(2, new java.sql.Date(new Date().getTime()));
380 int rows = s.executeUpdate();
381 if (rows != 1)
382 throw new IllegalStateException("got " + rows + " != 1 rows for `" + this.getSQL() + "'");
383 } finally {
384 s.close();
385 }
386 }
387 });
388 }
389
390 /**
391 * Determine which updates have already been applied.
392 *
393 * <p>
394 * The implementation in {@link SQLSchemaUpdater} does the standard JDBC thing using a SELECT statement
395 * from the update table.
396 *
397 * @throws SQLException if an error occurs while accessing the database
398 */
399 @Override
400 protected Set<String> getAppliedUpdateNames(Connection c) throws SQLException {
401 final HashSet<String> updateNames = new HashSet<String>();
402 this.apply(c, new SQLCommand("SELECT " + this.getUpdateTableNameColumn() + " FROM " + this.getUpdateTableName()) {
403 @Override
404 public void apply(Connection c) throws SQLException {
405 Statement s = c.createStatement();
406 try {
407 for (ResultSet resultSet = s.executeQuery(this.getSQL()); resultSet.next(); )
408 updateNames.add(resultSet.getString(1));
409 } finally {
410 s.close();
411 }
412 }
413 });
414 return updateNames;
415 }
416
417 // Initialize the database
418 @Override
419 protected void initializeDatabase(Connection c) throws SQLException {
420
421 // Sanity check
422 if (this.getDatabaseInitialization() == null)
423 throw new IllegalArgumentException("database needs initialization but no database initialization is configured");
424 if (this.getUpdateTableInitialization() == null)
425 throw new IllegalArgumentException("database needs initialization but no update table initialization is configured");
426
427 // Initialize application schema
428 this.log.info("intializing database schema");
429 this.apply(c, this.getDatabaseInitialization());
430
431 // Initialize update table
432 this.log.info("intializing update table");
433 this.apply(c, this.getUpdateTableInitialization());
434 }
435 }
436