001
002 /*
003 * Copyright (C) 2011 Archie L. Cobbs. All rights reserved.
004 *
005 * $Id: SpringSQLSchemaUpdater.java 211 2012-01-14 18:28:54Z archie.cobbs $
006 */
007
008 package org.dellroad.stuff.schema;
009
010 import java.sql.Connection;
011 import java.sql.SQLException;
012 import java.util.Comparator;
013
014 import org.dellroad.stuff.spring.BeanNameComparator;
015 import org.springframework.beans.factory.BeanFactory;
016 import org.springframework.beans.factory.BeanFactoryAware;
017 import org.springframework.beans.factory.InitializingBean;
018 import org.springframework.beans.factory.ListableBeanFactory;
019 import org.springframework.dao.DataAccessException;
020 import org.springframework.jdbc.BadSqlGrammarException;
021 import org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator;
022
023 /**
024 * {@link SQLSchemaUpdater} optimized for use with Spring.
025 * <ul>
026 * <li>{@link #apply(Connection, DatabaseAction) apply()} is overridden so Spring {@link DataAccessException}s are thrown.</li>
027 * <li>{@link #indicatesUninitializedDatabase indicatesUninitializedDatabase()} is overridden to examine exceptions
028 * and more precisely using Spring's exception translation infrastructure to filter out false positives.</li>
029 * <li>{@link #getOrderingTieBreaker} is overridden to break ties by ordering updates in the same order
030 * as they are defined in the bean factory.</li>
031 * <li>This class implements {@link InitializingBean} and verifies all required properties are set.</li>
032 * <li>If no updates are {@linkplain #setUpdates explicitly configured}, then all {@link SpringSQLSchemaUpdate}s found
033 * in the containing bean factory are automatically configured.
034 * </ul>
035 *
036 * <p>
037 * An example of how this class can be combined with custom XML to define an updater, all its updates,
038 * and a {@link SchemaUpdatingDataSource} that automatically updates the database schema:
039 * <blockquote><pre>
040 * <beans xmlns="http://www.springframework.org/schema/beans"
041 * <b>xmlns:dellroad-stuff="http://dellroad-stuff.googlecode.com/schema/dellroad-stuff"</b>
042 * xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
043 * xmlns:p="http://www.springframework.org/schema/p"
044 * xsi:schemaLocation="
045 * http://www.springframework.org/schema/beans
046 * http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
047 * <b>http://dellroad-stuff.googlecode.com/schema/dellroad-stuff
048 * http://dellroad-stuff.googlecode.com/svn/wiki/schemas/dellroad-stuff-1.0.xsd</b>">
049 *
050 * <!-- DataSource that automatically updates the database schema -->
051 * <b><bean id="dataSource" class="org.dellroad.stuff.schema.SchemaUpdatingDataSource"
052 * p:dataSource-ref="realDataSource" p:schemaUpdater-ref="schemaUpdater"/></b>
053 *
054 * <!--
055 * Database updater bean. This is used on first access to the DataSource above. Notes:
056 * - "databaseInitialization" is used to initialize the schema (first time only)
057 * - "updateTableInitialization" is used to initialize the update table (first time only)
058 * - In this example, we just use dellroad-stuff's update table initialization for MySQL
059 * - The <dellroad-stuff:sql-update> beans below will be auto-detected
060 * -->
061 * <b><bean id="schemaUpdater" class="org.dellroad.stuff.schema.SpringSQLSchemaUpdater">
062 * <property name="databaseInitialization">
063 * <dellroad-stuff:sql resource="classpath:databaseInit.sql"/>
064 * </property>
065 * <property name="updateTableInitialization">
066 * <dellroad-stuff:sql resource="classpath:org/dellroad/stuff/schema/updateTable-mysql.sql"/>
067 * </property>
068 * </bean></b>
069 *
070 * <!-- Schema update to add the 'phone' column to the 'User' table -->
071 * <b><dellroad-stuff:sql-update id="addPhone">ALTER TABLE User ADD phone VARCHAR(64)</dellroad-stuff:sql-update></b>
072 *
073 * <!-- Schema update to run some complicated external SQL script -->
074 * <b><dellroad-stuff:sql-update id="majorChanges" depends-on="addPhone" resource="classpath:majorChanges.sql"/></b>
075 *
076 * <!-- Multiple SQL commands that will be automatically separated into distinct updates -->
077 * <b><dellroad-stuff:sql-update id="renameColumn">
078 * ALTER TABLE User ADD newName VARCHAR(64);
079 * ALTER TABLE User SET newName = oldName;
080 * ALTER TABLE User DROP oldName;
081 * </dellroad-stuff:sql-update></b>
082 *
083 * <!-- Add more schema updates over time as needed and everything just works... -->
084 *
085 * </beans>
086 * </pre></blockquote>
087 *
088 * <p>
089 * In the case no schema updates are explicitly configured, it is required that this updater and all of its
090 * schema updates are defined in the same {@link ListableBeanFactory}.
091 */
092 public class SpringSQLSchemaUpdater extends SQLSchemaUpdater implements BeanFactoryAware, InitializingBean {
093
094 private ListableBeanFactory beanFactory;
095
096 @Override
097 public void afterPropertiesSet() throws Exception {
098 if (this.getDatabaseInitialization() == null)
099 throw new Exception("no database initialization configured");
100 if (this.getUpdateTableInitialization() == null)
101 throw new Exception("no update table initialization configured");
102 if (this.getUpdates() == null) {
103 if (this.beanFactory == null) {
104 throw new IllegalArgumentException("no updates explicitly configured and the containing BeanFactory"
105 + " is not a ListableBeanFactory: " + this.beanFactory);
106 }
107 this.setUpdates(this.beanFactory.getBeansOfType(SpringSQLSchemaUpdate.class).values());
108 }
109 }
110
111 @Override
112 public void setBeanFactory(BeanFactory beanFactory) {
113 if (beanFactory instanceof ListableBeanFactory)
114 this.beanFactory = (ListableBeanFactory)beanFactory;
115 }
116
117 /**
118 * Determine if an exception thrown during {@link #databaseNeedsInitialization} is consistent with
119 * an uninitialized database.
120 *
121 * <p>
122 * The implementation in {@link SpringSQLSchemaUpdater} looks for a {@link BadSqlGrammarException}.
123 */
124 @Override
125 protected boolean indicatesUninitializedDatabase(Connection c, SQLException e) throws SQLException {
126 return this.translate(e, c, null) instanceof BadSqlGrammarException;
127 }
128
129 /**
130 * Apply a {@link DatabaseAction} to a {@link Connection}.
131 *
132 * <p>
133 * The implementation in {@link SQLSchemaUpdater} invokes the action and delegates to
134 * {@link #translate(SQLException, Connection, String) translate()} to convert any {@link SQLException} thrown.
135 *
136 * @throws SQLException if an error occurs attempting to translate a thrown SQLException
137 * @throws DataAccessException if an error occurs accessing the database
138 * @see #translate(SQLException, Connection, String) translate()
139 */
140 @Override
141 protected void apply(Connection c, DatabaseAction<Connection> action) throws SQLException {
142 try {
143 super.apply(c, action);
144 } catch (SQLException e) {
145 String sql = action instanceof SQLCommand ? ((SQLCommand)action).getSQL() : null;
146 throw this.translate(e, c, sql);
147 }
148 }
149
150 /**
151 * Converts {@link SQLException}s into Spring {@link DataAccessException}s.
152 */
153 protected DataAccessException translate(SQLException e, Connection c, String sql) throws SQLException {
154 return new SQLErrorCodeSQLExceptionTranslator(c.getMetaData().getDatabaseProductName())
155 .translate("database access during schema update", sql, e);
156 }
157
158 /**
159 * Get the preferred ordering of two updates that do not have any predecessor constraints
160 * (including implied indirect constraints) between them.
161 *
162 * <p>
163 * In the case no schema updates are explicitly configured, the {@link Comparator} returned by the
164 * implementation in {@link SpringSQLSchemaUpdater} sorts updates in the same order that they appear
165 * in the containing {@link ListableBeanFactory}. Otherwise, the {@linkplain AbstractSchemaUpdater#getOrderingTieBreaker
166 * superclass method} is used.
167 */
168 @Override
169 protected Comparator<SchemaUpdate<Connection>> getOrderingTieBreaker() {
170 if (this.beanFactory == null)
171 return super.getOrderingTieBreaker();
172 final BeanNameComparator beanNameComparator = new BeanNameComparator(this.beanFactory);
173 return new Comparator<SchemaUpdate<Connection>>() {
174 @Override
175 public int compare(SchemaUpdate<Connection> update1, SchemaUpdate<Connection> update2) {
176 return beanNameComparator.compare(update1.getName(), update2.getName());
177 }
178 };
179 }
180 }
181