/// <summary>
/// Aplicar o conteúdo do script
/// </summary>
/// <param name="changeScript"></param>
/// <param name="scriptContent"></param>
/// <param name="createChangeLogTable"></param>
public void ApplyScriptContent(ChangeScript changeScript, string scriptContent, bool createChangeLogTable)
{
if (createChangeLogTable)
CriarTabelaChangeLog();
ApplyScript(changeScript, scriptContent, ScriptStatus.SucessRevisedUser);
}
public void ShouldInsertToSchemaVersionTable()
{
ChangeScript changeScript = new ChangeScript(1, "script.sql");
this.applier.InsertToSchemaVersionTable(changeScript);
this.schemaVersionManager.Verify(s => s.RecordScriptApplied(changeScript));
}
/// <summary>
/// Executa um determinado script passando um objeto ChangeScript e seu respectivo conteúdo SQL
/// </summary>
/// <param name="changeScript"></param>
/// <param name="scriptContent"></param>
public void Executar(ChangeScript changeScript, string scriptContent)
{
QueryExecuter.Open();
var criarChangeLog = VerificarSeDeveCriarTabelaChangeLog();
DirectToDbApplier.ApplyScriptContent(changeScript, scriptContent, criarChangeLog);
QueryExecuter.Close();
}
public void ShouldRecordFailureInSchemaVersionTable()
{
var changeScript = new ChangeScript("Scripts", 1, "script.sql");
this.applier.RecordScriptStatus(changeScript, ScriptStatus.Failure, "Script failed");
this.schemaVersionManager.Verify(s => s.RecordScriptStatus(changeScript, ScriptStatus.Failure, "Script failed"));
}
public void ShouldRecordSuccessInSchemaVersionTable()
{
var changeScript = new ChangeScript("Scripts", 1, "script.sql");
this.applier.RecordScriptStatus(changeScript, ScriptStatus.Success, "Script completed");
this.schemaVersionManager.Verify(s => s.RecordScriptStatus(changeScript, ScriptStatus.Success, "Script completed"));
}
/// <summary>
/// Aplicar o conteúdo do script
/// </summary>
/// <param name="changeScript"></param>
/// <param name="scriptContent"></param>
/// <param name="createChangeLogTable"></param>
public void ApplyScriptContent(ChangeScript changeScript, string scriptContent, bool createChangeLogTable)
{
if (createChangeLogTable)
{
CriarTabelaChangeLog();
}
ApplyScript(changeScript, scriptContent, ScriptStatus.SucessRevisedUser);
}
public override void TestCanRetrieveDeltaFragmentHeaderSql()
{
ChangeScript script = new ChangeScript(3, "description");
Assert.AreEqual(@"--------------- Fragment begins: #3 ---------------
INSERT INTO changelog (ChangeNumber, Project, StartDate, AppliedBy, FileName) VALUES (3, 'All', CURRENT_TIMESTAMP, 'none specified', 'description');
COMMIT;",
databaseSchemaVersion.GenerateDoDeltaFragmentHeader(script));
}
public void executar_um_conteudo_script()
{
const string conteudoSql = "Create Table tabelaTeste (id int not null, name varchar(45) not null, primary key (id));";
var changeScript = new ChangeScript("2.0.0.0", 8, new FileInfo(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, @"Mocks\Versioned\2.0.0.0\8.Create Product Table.sql")), Encoding.UTF8);
executorScriptsIndividuais.Executar(changeScript, conteudoSql);
AssertTableExists("tabelaTeste");
AssertTableExists("ChangeLog");
}
public override void TestCanRetrieveDeltaFragmentFooterSql()
{
ChangeScript script = new ChangeScript(3, "description");
Assert.AreEqual(
@";
UPDATE changelog SET CompletedDate = CURRENT_TIMESTAMP WHERE ChangeNumber = 3 AND Project = 'All';
COMMIT;
--------------- Fragment ends: #3 ---------------",
databaseSchemaVersion.GenerateDoDeltaFragmentFooter(script));
}
public virtual void TestCanRetrieveUndoDeltaFragmentFooterSql()
{
ChangeScript script = new ChangeScript(3, "description");
Assert.AreEqual(
@"DELETE FROM changelog WHERE ChangeNumber = 3 AND Project = 'All'
GO
--------------- Fragment ends: #3 ---------------",
databaseSchemaVersion.GenerateUndoDeltaFragmentFooter(script));
}
public void CanFormatAChangeScriptList()
{
ChangeScript change1 = new ChangeScript(1);
ChangeScript change3 = new ChangeScript(3);
List <ChangeScript> list = new List <ChangeScript>();
list.Add(change1);
list.Add(change3);
Assert.AreEqual("1, 3", prettyPrinter.FormatChangeScriptList(list));
}
private void WriteScript(ChangeScript script, Func <object, string> pathTemplate)
{
var filePath = pathTemplate(script);
_logger.LogMessage("- adding/updating file {0}", filePath);
_projectManager.UpdateOrAddFile(new ProjectFileDetails()
{
Content = script.ScriptText,
Name = script.Name,
PathInProject = filePath
});
}
public string GenerateUndoDeltaFragmentFooter(ChangeScript changeScript)
{
StringBuilder builder = new StringBuilder();
builder.AppendLine("DELETE FROM " + TableName
+ " WHERE ChangeNumber = " + changeScript.GetId()
+ " AND Project = '" + _deltaSet + "'"
+ DbmsSyntax.GenerateStatementDelimiter());
builder.AppendLine(DbmsSyntax.GenerateCommit());
builder.Append("--------------- Fragment ends: " + changeScript + " ---------------");
return(builder.ToString());
}
public void executar_script_passando_conteudo()
{
const string conteudoSql = "Create Table tabelaTeste (id int not null, name varchar(45) not null, primary key (id));";
var changeScript = new ChangeScript("1.0.0.0", 1);
splitter.Setup(s => s.Split(It.IsAny <string>())).Returns <string>(s => new[] { s });
var directToDbApplier = new DirectToDbApplier(queryExecuter.Object, schemaVersionManager.Object, splitter.Object, new Mock <IDbmsSyntax>().Object, ChangeLogTableName, System.Console.Out);
directToDbApplier.ApplyScriptContent(changeScript, conteudoSql, true);
queryExecuter.Verify(x => x.Execute(conteudoSql, It.IsAny <StringBuilder>()), Times.Once);
}
public void atualizar_status_do_script_para_sucess_revised_user()
{
const string conteudoSql = "Create Table tabelaTeste (id int not null, name varchar(45) not null, primary key (id));";
var changeScript = new ChangeScript("1.0.0.0", 1);
splitter.Setup(s => s.Split(It.IsAny <string>())).Returns <string>(s => new[] { s });
var directToDbApplier = new DirectToDbApplier(queryExecuter.Object, schemaVersionManager.Object, splitter.Object, new Mock <IDbmsSyntax>().Object, ChangeLogTableName, System.Console.Out);
directToDbApplier.ApplyScriptContent(changeScript, conteudoSql, true);
schemaVersionManager.Verify(x => x.RecordScriptStatus(changeScript, ScriptStatus.SucessRevisedUser, It.IsAny <string>()), Times.Once);
}
public virtual void TestCanRetrieveDeltaFragmentFooterSql()
{
ChangeScript script = new ChangeScript(3, "description");
Assert.AreEqual(
@"
GO
UPDATE changelog SET CompletedDate = getdate() WHERE ChangeNumber = 3 AND Project = 'All'
GO
--------------- Fragment ends: #3 ---------------",
databaseSchemaVersion.GenerateDoDeltaFragmentFooter(script));
}
// Use this for initialization
void Start()
{
testText.SetActive(false);
excWriter = GetComponent <ExcelWriter>();
inputField = inputFieldObject.transform.Find("Text").GetComponent <Text>();
cubePlacer = GetComponent <CubePlacer>();
changeScript = GetComponent <ChangeScript>();
expRand = GetComponent <ExpRandomer>();
place += start;
span += place;
change += span;
Debug.Log(inputField.text);
}
public string GenerateDoDeltaFragmentHeader(ChangeScript changeScript)
{
StringBuilder builder = new StringBuilder();
builder.AppendLine("--------------- Fragment begins: " + changeScript + " ---------------");
builder.AppendLine("INSERT INTO " + TableName +
" (ChangeNumber, Project, StartDate, AppliedBy, FileName)" +
" VALUES (" + changeScript.GetId() + ", '" + _deltaSet + "', " +
DbmsSyntax.GenerateTimestamp() +
", " + DbmsSyntax.GenerateChangeOwner() + ", '" + changeScript.GetDescription() + "')" +
DbmsSyntax.GenerateStatementDelimiter());
builder.Append(DbmsSyntax.GenerateCommit());
return(builder.ToString());
}
public void nao_criar_tabela_changelog()
{
const string conteudoSql = "Create Table tabelaTeste (id int not null, name varchar(45) not null, primary key (id));";
var changeScript = new ChangeScript("1.0.0.0", 1);
splitter.Setup(s => s.Split(It.IsAny <string>())).Returns <string>(s => new[] { s });
dbmsSyntax = new MsSqlDbmsSyntax();
var changeLogTableSqlScript = dbmsSyntax.CreateChangeLogTableSqlScript(ChangeLogTableName);
var directToDbApplier = new DirectToDbApplier(queryExecuter.Object, schemaVersionManager.Object, splitter.Object, dbmsSyntax, ChangeLogTableName, System.Console.Out);
directToDbApplier.ApplyScriptContent(changeScript, conteudoSql, false);
schemaVersionManager.Verify(x => x.RecordScriptStatus(changeScript, ScriptStatus.SucessRevisedUser, It.IsAny <string>()), Times.Once);
queryExecuter.Verify(x => x.Execute(changeLogTableSqlScript), Times.Never());
}
public void ApplyChangeUndoScript(ChangeScript script)
{
output.WriteLine();
output.WriteLine("-- Change script: " + script);
if (_useTransaction)
{
output.WriteLine(_dbmsSyntax.GenerateBeginTransaction());
}
CopyFileUndoContentsToStdOut(script.GetFile());
if (_useTransaction)
{
output.WriteLine(_dbmsSyntax.GenerateCommitTransaction());
}
}
/// <summary>
/// Applies the change changeScript.
/// </summary>
/// <param name="changeScript">The changeScript.</param>
/// <param name="scriptContent"></param>
/// <param name="output">The output from applying the change changeScript.</param>
protected void ApplyChangeScript(ChangeScript changeScript, string scriptContent, StringBuilder output)
{
var statements = splitter.Split(scriptContent);
var i = 0;
foreach (var statement in statements)
{
try
{
queryExecuter.BeginTransaction();
if (statements.Count > 1)
{
infoTextWriter.WriteLine(" -> statement " + (i + 1) + " of " + statements.Count + "...");
}
queryExecuter.Execute(statement, output);
i++;
}
catch (DbException e)
{
throw new ChangeScriptFailedException(e, changeScript, i + 1, statement);
}
finally
{
// Write out SQL execution output.
if (output.Length > 0)
{
infoTextWriter.WriteLine(output.ToString());
}
try
{
// Commit transaction
queryExecuter.CommitTransaction();
}
catch (Exception e)
{
output.Clear();
infoTextWriter.WriteLine(e.ToString());
output.AppendLine(e.Message);
RecordScriptStatus(changeScript, ScriptStatus.Failure, output.ToString());
}
}
}
}
/// <summary>
/// Records the script status.
/// </summary>
/// <param name="script">The script.</param>
/// <param name="status">The status.</param>
/// <param name="output">The output of the script.</param>
/// <exception cref="SchemaVersionTrackingException">Could not update change log because: + e.Message</exception>
public virtual void RecordScriptStatus(ChangeScript script, ScriptStatus status, string output = null)
{
try
{
// Insert or update based on if there is a change ID.
// Update complete date for all but started.
var completeDateValue = status != ScriptStatus.Started ? this.syntax.CurrentTimestamp : "NULL";
if (script.ChangeId == 0)
{
var sql = string.Format(
CultureInfo.InvariantCulture,
@"INSERT INTO {0} (Folder, ScriptNumber, ScriptName, StartDate, CompleteDate, AppliedBy, ScriptStatus, ScriptOutput) VALUES (@1, @2, @3, {1}, {2}, {3}, @4, @5)
SELECT ChangeId FROM {0} WHERE Folder = @1 and ScriptNumber = @2",
this.changeLogTableName,
this.syntax.CurrentTimestamp,
completeDateValue,
this.syntax.CurrentUser);
// Execute insert and set change id so it can be updated.
using (var reader = this.queryExecuter.ExecuteQuery(sql, script.Folder, script.ScriptNumber, script.ScriptName, (int)status, output ?? string.Empty))
{
reader.Read();
script.ChangeId = reader.GetInt32(0);
}
}
else
{
// Update existing entry.
var sql = string.Format(
CultureInfo.InvariantCulture,
"UPDATE {0} SET Folder = @1, ScriptNumber = @2, ScriptName = @3, {1}CompleteDate = {2}, AppliedBy = {3}, ScriptStatus = @4, ScriptOutput = @5 WHERE ChangeId = @6",
this.changeLogTableName,
status == ScriptStatus.Started ? string.Format(CultureInfo.InvariantCulture, "StartDate = {0}, ", this.syntax.CurrentTimestamp) : string.Empty,
completeDateValue,
this.syntax.CurrentUser);
this.queryExecuter.Execute(sql, script.Folder, script.ScriptNumber, script.ScriptName, (int)status, output ?? string.Empty, script.ChangeId);
}
}
catch (DbException e)
{
throw new SchemaVersionTrackingException("Could not update change log because: " + e.Message, e);
}
}
public virtual void RecordScriptApplied(ChangeScript script)
{
try
{
string sql = string.Format(
CultureInfo.InvariantCulture,
"INSERT INTO {0} (change_number, complete_dt, applied_by, description) VALUES (@1, {1}, {2}, @2)",
this.changeLogTableName,
this.syntax.GenerateTimestamp(),
this.syntax.GenerateUser());
this.queryExecuter.Execute(
sql,
script.GetId(),
script.GetDescription());
}
catch (DbException e)
{
throw new SchemaVersionTrackingException("Could not update change log because: " + e.Message, e);
}
}
private void WriteScript(ChangeScript script)
{
var pathTemplate = HandlebarsDotNet.Handlebars.Compile(Configuration.PathFormat);
FileInfo file = new FileInfo(string.Format(@"{0}\{1}", Configuration.Destination.Trim('\\'), pathTemplate(script)));
_logger.LogMessage("- Writing to file {0}", file.FullName);
if (!file.Directory.Exists)
{
file.Directory.Create();
}
if (file.Exists)
{
file.Delete();
}
using (FileStream fileStream = file.OpenWrite())
using (StreamWriter writer = new StreamWriter(fileStream))
{
writer.Write(script.ScriptText);
}
}
private void Apply(ChangeScript changeScript)
{
Exception exception = null;
using (var transaction = _provider.BeginTransaction())
{
try
{
_provider.ApplyChangeScript(changeScript);
_logger.LogInformation($"Database script #{changeScript.Number} of version {changeScript.Version} has been applied.");
transaction.Commit();
}
catch (Exception ex)
{
exception = ex;
}
}
var changeLogRecord = new ChangeLogRecord(changeScript, exception);
using (var transaction = _provider.BeginTransaction())
{
_provider.DeleteChangeLogRecord(changeLogRecord);
_provider.InsertChangeLogRecord(changeLogRecord);
transaction.Commit();
}
if (!changeLogRecord.IsSuccessful)
{
_provider.Disconnect();
throw changeLogRecord.Exception;
}
}
public new void RecordScriptStatus(ChangeScript changeScript, ScriptStatus status, string output)
{
base.RecordScriptStatus(changeScript, status, output);
}
public new void ApplyChangeScript(ChangeScript script, StringBuilder output)
{
base.ApplyChangeScript(script, output);
}
public void ShouldRecordSuccessInSchemaVersionTable()
{
var changeScript = new ChangeScript("Scripts", 1, "script.sql");
this.applier.RecordScriptStatus(changeScript, ScriptStatus.Success, "Script completed");
this.schemaVersionManager.Verify(s => s.RecordScriptStatus(changeScript, ScriptStatus.Success, "Script completed"));
}
public void nao_criar_tabela_changelog()
{
const string conteudoSql = "Create Table tabelaTeste (id int not null, name varchar(45) not null, primary key (id));";
var changeScript = new ChangeScript("1.0.0.0", 1);
splitter.Setup(s => s.Split(It.IsAny<string>())).Returns<string>(s => new[] { s });
dbmsSyntax = new MsSqlDbmsSyntax();
var changeLogTableSqlScript = dbmsSyntax.CreateChangeLogTableSqlScript(ChangeLogTableName);
var directToDbApplier = new DirectToDbApplier(queryExecuter.Object, schemaVersionManager.Object, splitter.Object, dbmsSyntax, ChangeLogTableName, System.Console.Out);
directToDbApplier.ApplyScriptContent(changeScript, conteudoSql, false);
schemaVersionManager.Verify(x => x.RecordScriptStatus(changeScript, ScriptStatus.SucessRevisedUser, It.IsAny<string>()), Times.Once);
queryExecuter.Verify(x => x.Execute(changeLogTableSqlScript), Times.Never());
}
public void atualizar_status_do_script_para_sucess_revised_user()
{
const string conteudoSql = "Create Table tabelaTeste (id int not null, name varchar(45) not null, primary key (id));";
var changeScript = new ChangeScript("1.0.0.0", 1);
splitter.Setup(s => s.Split(It.IsAny<string>())).Returns<string>(s => new[] { s });
var directToDbApplier = new DirectToDbApplier(queryExecuter.Object, schemaVersionManager.Object, splitter.Object, new Mock<IDbmsSyntax>().Object, ChangeLogTableName, System.Console.Out);
directToDbApplier.ApplyScriptContent(changeScript, conteudoSql, true);
schemaVersionManager.Verify(x => x.RecordScriptStatus(changeScript, ScriptStatus.SucessRevisedUser, It.IsAny<string>()), Times.Once);
}
public void executar_script_passando_conteudo()
{
const string conteudoSql = "Create Table tabelaTeste (id int not null, name varchar(45) not null, primary key (id));";
var changeScript = new ChangeScript("1.0.0.0", 1);
splitter.Setup(s => s.Split(It.IsAny<string>())).Returns<string>(s => new[] { s });
var directToDbApplier = new DirectToDbApplier(queryExecuter.Object, schemaVersionManager.Object, splitter.Object, new Mock<IDbmsSyntax>().Object, ChangeLogTableName, System.Console.Out);
directToDbApplier.ApplyScriptContent(changeScript, conteudoSql, true);
queryExecuter.Verify(x => x.Execute(conteudoSql, It.IsAny<StringBuilder>()), Times.Once);
}
private void ApplyScript(ChangeScript script, string scriptContent, ScriptStatus scriptStatusSucess)
{
RecordScriptStatus(script, ScriptStatus.Started);
// Begin transaction
//queryExecuter.BeginTransaction();
infoTextWriter.WriteLine(script);
infoTextWriter.WriteLine("----------------------------------------------------------");
// Apply changes and update ChangeLog table
var output = new StringBuilder();
try
{
ApplyChangeScript(script, scriptContent, output);
RecordScriptStatus(script, scriptStatusSucess, output.ToString());
}
catch (Exception ex)
{
if (ex.InnerException != null)
{
infoTextWriter.WriteLine(ex.ToString());
output.AppendLine(ex.Message);
}
RecordScriptStatus(script, ScriptStatus.Failure, output.ToString());
throw;
}
}
/// <summary>
/// Records details about a change script in the database.
/// </summary>
/// <param name="changeScript">The change script.</param>
/// <param name="status">Status of the script execution.</param>
/// <param name="output">The output from running the script.</param>
protected void RecordScriptStatus(ChangeScript changeScript, ScriptStatus status, string output = null)
{
this.schemaVersionManager.RecordScriptStatus(changeScript, status, output);
}
/// <summary>
/// Applies the change changeScript.
/// </summary>
/// <param name="changeScript">The changeScript.</param>
/// <param name="scriptContent"></param>
/// <param name="output">The output from applying the change changeScript.</param>
protected void ApplyChangeScript(ChangeScript changeScript, string scriptContent, StringBuilder output)
{
var statements = splitter.Split(scriptContent);
var i = 0;
foreach (var statement in statements)
{
try
{
queryExecuter.BeginTransaction();
if (statements.Count > 1)
{
infoTextWriter.WriteLine(" -> statement " + (i + 1) + " of " + statements.Count + "...");
}
queryExecuter.Execute(statement, output);
i++;
}
catch (DbException e)
{
throw new ChangeScriptFailedException(e, changeScript, i + 1, statement);
}
finally
{
// Write out SQL execution output.
if (output.Length > 0)
{
infoTextWriter.WriteLine(output.ToString());
}
try
{
// Commit transaction
queryExecuter.CommitTransaction();
}
catch (Exception e)
{
output.Clear();
infoTextWriter.WriteLine(e.ToString());
output.AppendLine(e.Message);
RecordScriptStatus(changeScript, ScriptStatus.Failure, output.ToString());
}
}
}
}
/// <summary>
/// Records details about a change changeScript in the database.
/// </summary>
/// <param name="changeScript">The change changeScript.</param>
/// <param name="status">Status of the changeScript execution.</param>
/// <param name="output">The output from running the changeScript.</param>
protected void RecordScriptStatus(ChangeScript changeScript, ScriptStatus status, string output = null)
{
schemaVersionManager.RecordScriptStatus(changeScript, status, output);
}
/// <summary>
/// Aplicar um determinado ChangeScript
/// </summary>
/// <param name="changeScript">The changeScript.</param>
/// <param name="createChangeLogTable">Create or not ChangeLog table</param>
public void ApplyChangeScript(ChangeScript changeScript, bool createChangeLogTable)
{
Apply(new List<ChangeScript> { changeScript }, createChangeLogTable);
}
/// <summary>
/// Gets the SQL to delete a change log entry.
/// </summary>
/// <param name="script">The script.</param>
/// <returns>SQL to delete the change log entry.</returns>
public virtual string GetChangelogDeleteSql(ChangeScript script)
{
return(string.Format(CultureInfo.InvariantCulture, "DELETE FROM {0} WHERE Folder = '{1}' AND ScriptNumber = {2}", this.changeLogTableName, script.Folder, script.ScriptNumber));
}
public void ShouldRecordFailureInSchemaVersionTable()
{
var changeScript = new ChangeScript("Scripts", 1, "script.sql");
this.applier.RecordScriptStatus(changeScript, ScriptStatus.Failure, "Script failed");
this.schemaVersionManager.Verify(s => s.RecordScriptStatus(changeScript, ScriptStatus.Failure, "Script failed"));
}
public new void ApplyChangeScript(ChangeScript script, StringBuilder output)
{
base.ApplyChangeScript(script, script.GetContent(), output);
}
public string GenerateUndoDeltaFragmentHeader(ChangeScript changeScript)
{
return("--------------- Fragment begins: " + changeScript + " ---------------");
}
