com.tacitknowledge.util.migration.jdbc.SqlScriptMigrationTask Maven / Gradle / Ivy
/* Copyright 2004 Tacit Knowledge
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
package com.tacitknowledge.util.migration.jdbc;
import com.tacitknowledge.util.migration.MigrationContext;
import com.tacitknowledge.util.migration.MigrationException;
import com.tacitknowledge.util.migration.MigrationTaskSupport;
import com.tacitknowledge.util.migration.jdbc.util.SqlUtil;
import com.tacitknowledge.util.migration.jdbc.util.SybaseUtil;
import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.ListIterator;
import java.util.regex.Pattern;
/**
* Adaptss a SQL or DDL database patch for use with the AutoPatch framework.
*
* @author Scott Askew ([email protected])
*/
public class SqlScriptMigrationTask extends MigrationTaskSupport
{
/**
* Class logger
*/
private static Log log = LogFactory.getLog(SqlScriptMigrationTask.class);
/**
* The SQL to execute
*/
private String sql = null;
/**
* SQL to migrate down a patch level
*/
private String downSql = null;
/**
* Creates a new SqlScriptMigrationTask.
*/
public SqlScriptMigrationTask()
{
// do nothing
}
/**
* Creates a new SqlScriptMigrationTask
*
* @param name the name of the SQL script to execute; this is just an
* identifier and does not have to correspond to a file name
* @param level the patch level of the migration task
* @param upSql the SQL to execute to migrate the patch level up one
* @param downSql the SQL to execute to rollback to this patch
*/
public SqlScriptMigrationTask(String name, int level, String upSql, String downSql)
{
setName(name);
setLevel(new Integer(level));
this.sql = upSql;
this.downSql = downSql;
setRollbackSupported(!"".equals(downSql));
}
/**
* Creates a new SqlScriptMigrationTask
*
* @param name the name of the SQL script to execute; this is just an
* identifier and does not have to correspond to a file name
* @param level the patch level of the migration task
* @param sql the SQL to execute
*/
public SqlScriptMigrationTask(String name, int level, String sql)
{
setName(name);
setLevel(new Integer(level));
this.sql = sql;
this.downSql = "";
}
/**
* Creates a new SqlScriptMigrationTask containing the SQL
* contained in the given InputStream.
*
* @param name the name of the SQL script to execute; this is just an
* identifier and does not have to correspond to a file name
* @param level the patch level of the migration task
* @param is the source of the SQL to execute
* @throws IOException if there was problem reading the input stream
*/
public SqlScriptMigrationTask(String name, int level, InputStream is) throws IOException
{
setName(name);
setLevel(new Integer(level));
StringBuffer sqlBuffer = new StringBuffer();
BufferedReader buf = new BufferedReader(new InputStreamReader(is));
String line = buf.readLine();
while (line != null)
{
sqlBuffer.append(line).append("\n");
line = buf.readLine();
}
sql = sqlBuffer.toString();
}
/**
* {@inheritDoc}
*/
public void up(MigrationContext context) throws MigrationException
{
executeSql(context, sql);
}
/**
* {@inheritDoc}
*/
public void down(MigrationContext context) throws MigrationException
{
executeSql(context, downSql);
}
/**
* Executes the passed sql in the passed context.
*
* @param ctx the MigrationContext> to execute the SQL in
* @param sqlToExec the SQL to execute
* @throws MigrationException thrown if there is an error when executing the SQL
*/
private void executeSql(MigrationContext ctx, String sqlToExec)
throws MigrationException
{
JdbcMigrationContext context = (JdbcMigrationContext) ctx;
Connection conn = null;
Statement stmt = null;
String sqlStatement = "";
ListIterator listIterator = null;
try
{
conn = context.getConnection();
// cleaning the slate before we execute the patch.
// This was inspired by a Sybase ASE server that did not allow
// ALTER TABLE statements in multi-statement transactions. Instead of putting
// a if(sybase) conditional, we decided to clean the slate for everyone.
context.commit();
List sqlStatements = getSqlStatements(context, sqlToExec);
for (listIterator = sqlStatements.listIterator(); listIterator.hasNext();)
{
sqlStatement = (String) listIterator.next();
log.debug(getName() + ": Attempting to execute: " + sqlStatement);
stmt = conn.createStatement();
// handle sybase special case with illegal commands in multi
// command transactions
if (isSybase(context)
&& SybaseUtil.containsIllegalMultiStatementTransactionCommand(sqlStatement))
{
log.warn("Committing current transaction since patch " + getName()
+ " contains commands that are not allowed in multi statement"
+ " transactions. If the patch contains errors, this patch may"
+ " not be rolled back cleanly.");
context.commit();
stmt.execute(sqlStatement);
context.commit();
}
else // regular case
{
stmt.execute(sqlStatement);
}
SqlUtil.close(null, stmt, null);
}
context.commit();
}
catch (Exception e)
{
String message = getName() + ": Error running SQL at statement number "
+ listIterator.previousIndex() + " \"" + sqlStatement + "\"";
log.error(message, e);
if (e instanceof SQLException)
{
if (((SQLException) e).getNextException() != null)
{
log.error("Chained SQL Exception", ((SQLException) e).getNextException());
}
}
context.rollback();
throw new MigrationException(message, e);
}
finally
{
SqlUtil.close(null, stmt, null);
}
}
public List getSqlStatements(JdbcMigrationContext context)
{
return getSqlStatements(context, sql);
}
/**
* Parses the SQL/DDL to execute and returns a list of individual statements. For database
* types that support mulitple statements in a single Statement.execute call,
* this method will return a one-element List containing the entire SQL
* file.
*
* @param context the MigrationContext, to figure out db type and if it
* can handle multiple statements at once
* @return a list of SQL and DDL statements to execute
*/
public List getSqlStatements(JdbcMigrationContext context, String sqlStatements)
{
List statements = new ArrayList();
if (context.getDatabaseType().isMultipleStatementsSupported())
{
statements.add(sqlStatements);
return statements;
}
StringBuffer currentStatement = new StringBuffer();
boolean inQuotedString = false;
boolean inComment = false;
char[] sqlChars = sqlStatements.toCharArray();
for (int i = 0; i < sqlChars.length; i++)
{
if (sqlChars[i] == '\n')
{
inComment = false;
}
if (!inComment)
{
switch (sqlChars[i])
{
case '-':
case '/':
if (!inQuotedString && i + 1 < sqlChars.length
&& sqlChars[i + 1] == sqlChars[i])
{
inComment = true;
}
else
{
currentStatement.append(sqlChars[i]);
}
break;
case '\'':
inQuotedString = !inQuotedString;
currentStatement.append(sqlChars[i]);
break;
case ';':
if (!inQuotedString)
{
// If we're in a stored procedure, just keep rolling
if (isStoredProcedure(context.getDatabaseType().getDatabaseType(),
currentStatement.toString()))
{
currentStatement.append(sqlChars[i]);
}
else
{
statements.add(currentStatement.toString().trim());
currentStatement = new StringBuffer();
}
}
else
{
currentStatement.append(sqlChars[i]);
}
break;
/* sybase uses 'GO' as it's statement delimiter */
case 'g':
case 'G':
/*
* Build up a string, reading backwards from the current index to
* the previous newline (or beginning of sequence) and from the
* current index up to the next newline. If it matches the regex
* for the GO delimiter, then add the statement otherwise
* just append the current index's character to currentStatement
*/
if (context.getDatabaseType().getDatabaseType().equals("sybase"))
{
// read from current index to previous line terminator
// or start of sequence
StringBuffer previous = new StringBuffer();
for (int j = i - 1; j >= 0; j--)
{
char c = sqlChars[j];
previous.append(c);
if (isLineTerminator(c))
{
break;
}
}
// reverse previous, since we've been walking backwards, but appending
previous = previous.reverse();
// read from current index to upcoming line terminator
// or end of sequence. If it is the GO delimiter,
// we skip up to line terminator
StringBuffer after = new StringBuffer();
int newIndex = 0;
for (int k = i + 1; k < sqlChars.length; k++)
{
char c = sqlChars[k];
after.append(c);
newIndex = k;
if (isLineTerminator(c))
{
break;
}
}
// check against the pattern if its a GO delimiter
String possibleDelimiter = previous
.append(sqlChars[i]).append(after).toString();
final String delimiterPattern = "^\\s*[Gg][Oo]\\s*$";
if (Pattern.matches(delimiterPattern, possibleDelimiter))
{
// if it's blank, don't bother adding it since Sybase
// will complain about empty queries.
// This happens if there are two GO's with no
// actual SQL to run between them.
if (!StringUtils.isBlank(currentStatement.toString().trim()))
{
statements.add(currentStatement.toString().trim());
}
currentStatement = new StringBuffer();
// skip up to next line terminator
i = newIndex;
}
else // not a delimiter, so just append
{
currentStatement.append(sqlChars[i]);
}
}
else // not a sybase db, so just append
{
currentStatement.append(sqlChars[i]);
}
break;
default:
currentStatement.append(sqlChars[i]);
break;
}
}
}
if (currentStatement.toString().trim().length() > 0)
{
statements.add(currentStatement.toString().trim());
}
return statements;
}
/**
* Return true if the string represents a stored procedure
*
* @param databaseType the type of the database
* @param statement the statement that may be a stored procedure
* @return true if the statement is a stored procedure for the given db type
*/
protected boolean isStoredProcedure(String databaseType, String statement)
{
String currentStatement = statement.trim().toLowerCase();
if ("oracle".equals(databaseType)
&& (currentStatement.startsWith("begin")
|| currentStatement.startsWith("create or replace method")
|| currentStatement.startsWith("create or replace function")
|| currentStatement.startsWith("create or replace procedure")
|| currentStatement.startsWith("create or replace package")))
{
return true;
}
if ("mysql".equals(databaseType)
&& (currentStatement.startsWith("create procedure")
|| currentStatement.startsWith("create function")))
{
return true;
}
return false;
}
/**
* return true if c is a line terminator as detailed in
* http://java.sun.com/j2se/1.5.0/docs/api/java/util/regex/Pattern.html
*
* @param c the char to test
* @return true if it is a line terminator
*/
protected boolean isLineTerminator(char c)
{
return (c == '\n') // newline
|| (c == '\r') // carriage return
|| (c == '\u0085') // next-line
|| (c == '\u2028') // line-separator
|| (c == '\u2029'); // paragraph separator
}
/**
* Check if the current migration context is against a sybase database.
*
* @param context the context to check.
* @return true if context is in a sybase database.
*/
protected boolean isSybase(JdbcMigrationContext context)
{
return context.getDatabaseType().getDatabaseType().equalsIgnoreCase("sybase");
}
/**
* {@inheritDoc}
*/
public String toString()
{
return getName();
}
}
© 2015 - 2025 Weber Informatics LLC | Privacy Policy