/// <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(); }