/// <summary>
/// Executes the SQL statements in a single batch.
/// </summary>
/// <param name="sqlHelper"></param>
private void ExecuteStatementsInBatch(SqlHelper sqlHelper)
{
SqlStatementList list = CreateStatementList();
SqlStatementAdapter adapter = new SqlStatementAdapter(list);
string sql = null;
if (Source == null)
{
sql = adapter.AdaptSql(_embeddedSqlStatements);
}
else
{
sql = adapter.AdaptSqlFile(Source);
}
// only write messages to the build log if the OutputWriter is not
// writing to the build log too
if (Output != null)
{
Log(Level.Verbose, "SQL Statement:");
Log(Level.Verbose, sql);
}
if (this.Verbose)
{
OutputWriter.WriteLine();
OutputWriter.WriteLine("SQL Statement:");
OutputWriter.WriteLine(sql.Trim());
}
IDataReader results = sqlHelper.Execute(sql, CommandTimeout);
ProcessResults(results, OutputWriter);
}
public void Visit(StringBuilder builder, AlterColumnCommand command)
{
if (ExecuteCustomInterpreter(command))
{
return;
}
builder.AppendFormat("alter table {0} alter column {1} ",
_dialect.QuoteForTableName(command.TableName),
_dialect.QuoteForColumnName(command.ColumnName));
// type
if (command.DbType != DbType.Object)
{
builder.Append(GetTypeName(command.DbType, command.Length, command.Precision, command.Scale));
}
else
{
if (command.Length > 0 || command.Precision > 0 || command.Scale > 0)
{
throw new MonahrqCoreException("Error while executing data migration: you need to specify the field's type in order to change its properties");
}
}
// [default value]
if (command.Default != null)
{
builder.Append(" set default ").Append(ConvertToSqlValue(command.Default)).Append(Space);
}
SqlStatementList.Add(builder.ToString());
}
public void TestDifferentGoDelimiters()
{
string goDelimiter1 = "go";
string goDelimiter2 = "gO";
string statements =
STATEMENT_1 + Environment.NewLine
+ STATEMENT_2 + Environment.NewLine
+ goDelimiter1 + Environment.NewLine
+ STATEMENT_3 + Environment.NewLine
+ goDelimiter1 + Environment.NewLine
+ STATEMENT_3 + Environment.NewLine
+ goDelimiter2 + Environment.NewLine
+ "-- " + STATEMENT_3;
SqlStatementList list = new SqlStatementList(goDelimiter1, DelimiterStyle.Line);
list.ParseSql(statements);
Assert.AreEqual(4, list.Count);
Assert.AreEqual(STATEMENT_1 + Environment.NewLine + STATEMENT_2 + Environment.NewLine, list[0], "Statement 1");
Assert.AreEqual(STATEMENT_3 + Environment.NewLine, list[1], "Statement 3.1");
Assert.AreEqual(STATEMENT_3 + Environment.NewLine, list[2], "Statement 3.2");
Assert.AreEqual("-- " + STATEMENT_3 + Environment.NewLine, list[3], "Comment");
}
private void RunPendingStatements()
{
try
{
var session = Shell.SessionFactoryHolder.GetSessionFactory().OpenSession();
var connection = session.Connection;
foreach (var sqlStatement in SqlStatements.Distinct())
{
Logger.Debug(sqlStatement);
using (var command = connection.CreateCommand())
{
command.CommandTimeout =
(int)
Math.Round(MonahrqConfiguration.SettingsGroup.MonahrqSettings().LongTimeout.TotalSeconds);
command.CommandText = sqlStatement;
command.ExecuteNonQuery();
}
Logger.Information("Data Migration{0}\tExecuting SQL Query: {1}", System.Environment.NewLine,
String.Format("{0}", sqlStatement));
}
}
finally
{
SqlStatementList.Clear();
}
}
private SqlStatementList CreateStatementList()
{
SqlStatementList list = new SqlStatementList(Delimiter, DelimiterStyle);
if (ExpandProperties)
{
list.Properties = Project.Properties;
}
return(list);
}
private SqlStatementList CreateStatementList()
{
SqlStatementList list = new SqlStatementList(Delimiter, DelimiterStyle);
if (ExpandProperties)
{
list.Properties = this.PropertyAccessor;
}
return(list);
}
public void TestIgnoreEmptyLines()
{
string statements = STATEMENT_1 + DELIMITER + "\n \n";
SqlStatementList list = new SqlStatementList(DELIMITER, DelimiterStyle.Normal);
list.ParseSql(statements);
Assert.AreEqual(1, list.Count);
Assert.AreEqual(STATEMENT_1 + Environment.NewLine, list[0]);
}
public void Visit(StringBuilder builder, AddColumnCommand command)
{
if (ExecuteCustomInterpreter(command))
{
return;
}
builder.AppendFormat("alter table {0} add ", _dialect.QuoteForTableName(command.TableName));
Visit(builder, (CreateColumnCommand)command);
SqlStatementList.Add(builder.ToString());
}
public void Visit(StringBuilder builder, DropIndexCommand command)
{
if (ExecuteCustomInterpreter(command))
{
return;
}
builder.AppendFormat("drop index {0} ON {1}",
_dialect.QuoteForColumnName(command.IndexName),
_dialect.QuoteForTableName(command.TableName));
SqlStatementList.Add(builder.ToString());
}
public void TestGoSeparatorMustNotSplitGoto()
{
string goDelimiter = "go";
string statements = STATEMENT_1 + Environment.NewLine + STATEMENT_GOTO + Environment.NewLine;
SqlStatementList list = new SqlStatementList(goDelimiter, DelimiterStyle.Line);
list.ParseSql(statements);
Assert.AreEqual(1, list.Count);
Assert.AreEqual(statements, list[0]);
}
public void TestLoadFromString()
{
string statements = STATEMENT_1 + "\n " + DELIMITER + STATEMENT_2
+ DELIMITER + " \n " + STATEMENT_3;
SqlStatementList list = new SqlStatementList(DELIMITER, DelimiterStyle.Normal);
list.ParseSql(statements);
Assert.AreEqual(3, list.Count);
Assert.AreEqual(STATEMENT_1 + Environment.NewLine, list[0]);
Assert.AreEqual(STATEMENT_2 + Environment.NewLine, list[1]);
Assert.AreEqual(STATEMENT_3 + Environment.NewLine, list[2]);
}
public override void Visit(DropForeignKeyCommand command)
{
if (ExecuteCustomInterpreter(command))
{
return;
}
var builder = new StringBuilder();
builder.AppendFormat("alter table {0} drop constraint {1}", _dialect.QuoteForTableName(command.SrcTable), command.Name);
SqlStatementList.Add(builder.ToString());
RunPendingStatements();
}
public void Visit(StringBuilder builder, AddIndexCommand command)
{
if (ExecuteCustomInterpreter(command))
{
return;
}
builder.AppendFormat("create index {1} on {0} ({2}) ",
_dialect.QuoteForTableName(command.TableName),
_dialect.QuoteForColumnName(command.IndexName),
String.Join(", ", command.ColumnNames));
SqlStatementList.Add(builder.ToString());
}
public override void Visit(DropTableCommand command)
{
if (ExecuteCustomInterpreter(command))
{
return;
}
var builder = new StringBuilder();
builder.Append(_dialect.GetDropTableString(command.Name));
SqlStatementList.Add(builder.ToString());
RunPendingStatements();
}
public void TestCommentStripping()
{
string statements = STATEMENT_1 + DELIMITER + "\n //" + STATEMENT_2
+ DELIMITER + " \n --" + STATEMENT_3
+ DELIMITER + "\n" + STATEMENT_1;
SqlStatementList list = new SqlStatementList(DELIMITER, DelimiterStyle.Normal);
list.ParseSql(statements);
Assert.AreEqual(2, list.Count);
Assert.AreEqual(STATEMENT_1 + Environment.NewLine, list[0]);
Assert.AreEqual(STATEMENT_1 + Environment.NewLine, list[1]);
}
public void TestKeepLineFormatting()
{
string goDelimiter = "go";
string statements = "\t" +
STATEMENT_1 + Environment.NewLine
+ "\t" + STATEMENT_2 + Environment.NewLine;
SqlStatementList list = new SqlStatementList(goDelimiter, DelimiterStyle.Line);
list.ParseSql(statements);
Assert.AreEqual(1, list.Count);
Assert.AreEqual(statements, list[0]);
}
public override void Visit(SqlStatementCommand command)
{
if (command.Providers.Count != 0 && !command.Providers.Contains(ConfigurationService.ConnectionSettings.ProviderName))
{
return;
}
if (ExecuteCustomInterpreter(command))
{
return;
}
SqlStatementList.Add(command.Sql);
RunPendingStatements();
}
private bool ExecuteCustomInterpreter <T>(T command) where T : ISchemaBuilderCommand
{
var interpreter = Shell.CommandInterpreters
.Where(ici => ici.DataProvider == ConfigurationService.ConnectionSettings.ProviderName)
.OfType <ICommandInterpreter <T> >()
.FirstOrDefault();
if (interpreter != null)
{
SqlStatementList.AddRange(interpreter.CreateStatements(command));
RunPendingStatements();
return(true);
}
return(false);
}
public override void Visit(CreateTableCommand command)
{
if (ExecuteCustomInterpreter(command))
{
return;
}
var builder = new StringBuilder();
builder.Append(_dialect.CreateMultisetTableString)
.Append(' ')
.Append(_dialect.QuoteForTableName(command.Name))
.Append(" (");
var appendComma = false;
foreach (var createColumn in command.TableCommands.OfType <CreateColumnCommand>())
{
if (appendComma)
{
builder.Append(", ");
}
appendComma = true;
Visit(builder, createColumn);
}
var primaryKeys = command.TableCommands.OfType <CreateColumnCommand>().Where(ccc => ccc.IsPrimaryKey).Select(ccc => ccc.ColumnName);
if (primaryKeys.Any())
{
if (appendComma)
{
builder.Append(", ");
}
builder.Append(_dialect.PrimaryKeyString)
.Append(" ( ")
.Append(String.Join(", ", primaryKeys.ToArray()))
.Append(" )");
}
builder.Append(" )");
SqlStatementList.Add(builder.ToString());
RunPendingStatements();
}
public void TestPropertyReplacement()
{
string sqlWithPropertyTags = @"use ${dbName}";
string expectedSqlStatement = @"use master";
string goDelimiter = "go";
string inputStatements = "\t" +
sqlWithPropertyTags + Environment.NewLine
+ "\t" + sqlWithPropertyTags + Environment.NewLine;
string expectedStatements = "\t" +
expectedSqlStatement + Environment.NewLine
+ "\t" + expectedSqlStatement + Environment.NewLine;
SqlStatementList list = new SqlStatementList(goDelimiter, DelimiterStyle.Line);
string buildFile = null;
try {
// persist buildfile
buildFile = CreateFileWithContents(ProjectXml);
// create project for buildfile
Project project = new Project(buildFile, Level.Info, 0);
var callStack = typeof(Project).GetProperty("RootTargetCallStack", BindingFlags.NonPublic | BindingFlags.Instance).GetValue(project) as TargetCallStack;
list.Properties = new PropertyAccessor(project, callStack);
list.Properties.Set("dbName", "master");
list.ParseSql(inputStatements);
Assert.AreEqual(1, list.Count);
Assert.AreEqual(expectedStatements, list[0]);
} finally {
// make sure temp buildfile is deleted
if (buildFile != null)
{
File.Delete(buildFile);
}
}
}
/// <summary>
/// Executes the SQL Statements one by one.
/// </summary>
/// <param name="sqlHelper"></param>
private void ExecuteStatements(SqlHelper sqlHelper)
{
SqlStatementList list = CreateStatementList();
if (Source == null)
{
list.ParseSql(_embeddedSqlStatements);
}
else
{
list.ParseSqlFromFile(Source);
}
foreach (string statement in list)
{
// only write messages to the build log if the OutputWriter is not
// writing to the build log too
if (Output != null)
{
Log(Level.Verbose, "SQL Statement:");
Log(Level.Verbose, statement);
}
if (this.Verbose)
{
OutputWriter.WriteLine();
OutputWriter.WriteLine("SQL Statement:");
OutputWriter.WriteLine(statement);
}
IDataReader results = null;
try {
results = sqlHelper.Execute(statement, CommandTimeout);
} catch (Exception ex) {
Log(Level.Error, "SQL Error: " + ex.Message);
Log(Level.Error, "Statement: " + statement);
} finally {
ProcessResults(results, OutputWriter);
}
}
}
public void TestLineSpawningDelimiter()
{
string delimiter = "#";
string statements =
STATEMENT_1 + Environment.NewLine
+ STATEMENT_2 + delimiter + STATEMENT_3 + Environment.NewLine
+ delimiter + "ABC";
SqlStatementList list = new SqlStatementList(delimiter, DelimiterStyle.Normal);
list.ParseSql(statements);
Assert.AreEqual(3, list.Count);
Assert.AreEqual(STATEMENT_1 + Environment.NewLine + STATEMENT_2,
list[0], "Statement 1");
Assert.AreEqual(STATEMENT_3 + Environment.NewLine, list[1],
"Statement 2");
Assert.AreEqual("ABC" + Environment.NewLine, list[2], "Statement 3");
}
public void TestPropertyReplacement()
{
string sqlWithPropertyTags = @"use ${dbName}";
string expectedSqlStatement = @"use master";
string goDelimiter = "go";
string inputStatements = "\t" +
sqlWithPropertyTags + Environment.NewLine
+ "\t" + sqlWithPropertyTags + Environment.NewLine;
string expectedStatements = "\t" +
expectedSqlStatement + Environment.NewLine
+ "\t" + expectedSqlStatement + Environment.NewLine;
SqlStatementList list = new SqlStatementList(goDelimiter, DelimiterStyle.Line);
string buildFile = null;
try {
// persist buildfile
buildFile = CreateFileWithContents(ProjectXml);
// create project for buildfile
Project project = new Project(buildFile, Level.Info, 0);
list.Properties = new PropertyDictionary(project);
list.Properties.Add("dbName", "master");
list.ParseSql(inputStatements);
Assert.AreEqual(1, list.Count);
Assert.AreEqual(expectedStatements, list[0]);
} finally {
// make sure temp buildfile is deleted
if (buildFile != null)
{
File.Delete(buildFile);
}
}
}
public override void Visit(CreateForeignKeyCommand command)
{
if (ExecuteCustomInterpreter(command))
{
return;
}
var builder = new StringBuilder();
builder.Append("alter table ")
.Append(_dialect.QuoteForTableName(command.SrcTable));
builder.Append(_dialect.GetAddForeignKeyConstraintString(command.Name,
command.SrcColumns,
_dialect.QuoteForTableName(command.DestTable),
command.DestColumns,
false));
SqlStatementList.Add(builder.ToString());
RunPendingStatements();
}
