public void CheckAndCreateMigrationTable(Instance instance, Execution currentExecution)
{
currentExecution.AppendLog("Checking migration tables on {0}...", instance.FullName);
using (SqlConnection connection = new SqlConnection(connectionStringProvider.GetConnectionString(instance)))
{
connection.Open();
using (SqlCommand cmd = new SqlCommand(@"SELECT COUNT(*)
FROM sys.objects
WHERE name='DBAppliedMigrations'
AND type_desc = 'USER_TABLE'", connection))
{
int count = (int)cmd.ExecuteScalar();
if (count == 0)
{
currentExecution.AppendLog("No migrations tables detected. Installing...", instance.FullName);
cmd.CommandText = @"CREATE TABLE [DBAppliedMigrations](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Executed_at] [datetime] NULL,
[Executed_by] [nvarchar](255) NULL,
[MigrateScript] [ntext] NULL,
[RollbackScript] [ntext] NULL,
[Migration_id] [int] NULL,
PRIMARY KEY CLUSTERED
(
[Id] ASC
)
)";
cmd.ExecuteNonQuery();
currentExecution.AppendLog("Migrations tables installed.", instance.FullName);
}
else
{
currentExecution.AppendLog("Migration tables already installed on {0}.", instance.FullName);
}
}
}
}
public Execution AsyncBeginApply(Migration migration, Instance instance)
{
ProjectHistoryStep historyStep = new ProjectHistoryStep();
historyStep.Description = string.Format("Applying migration {0} ({1}) on instance {2}.", migration.Id, migration.Description, instance.FullName);
historyStep.At = clock.Now;
historyStep.By = appContext.FullUserName;
historyStep.Project = migration.AuditProject;
historyStep.Create();
Execution execution = new Execution();
execution.At = clock.Now;
execution.By = appContext.FullUserName;
execution.Migration = migration;
execution.Instance = instance;
execution.ExecutionState = ExecutionState.Pending;
execution.AppendLog("Starting migration {0}({1}) on {2}...", migration.Id, migration.Description, instance.FullName);
execution.CreateAndFlush();
try
{
migrationTablesManager.CheckAndCreateMigrationTable(instance, execution);
execution.UpdateAndFlush();
if (!migrationTablesManager.IsMigrationAlreadyApplied(migration, instance, execution))
{
ThreadPool.QueueUserWorkItem(new WaitCallback(delegate(object arg)
{
SyncApplyMigration(migration, instance, execution);
}));
}
else
{
execution.ExecutionState = ExecutionState.Completed;
execution.UpdateAndFlush();
}
}
catch (Exception ex)
{
execution.AppendLogException(ex);
execution.UpdateAndFlush();
}
return execution;
}
private void SyncRollbackMigration(Migration migration, Instance instance, Execution execution)
{
try
{
string rollbackScript = migrationTablesManager.GetRollbackstring(migration, instance, execution);
if (string.IsNullOrEmpty(rollbackScript))
{
execution.AppendLog("Impossible to rollback! Aborting...");
execution.ExecutionState = ExecutionState.OnError;
return;
}
execution.AppendLog(string.Concat(Environment.NewLine, rollbackScript, Environment.NewLine));
execution.UpdateAndFlush();
execution.AppendLog("Rollbacking...");
execution.UpdateAndFlush();
using (SqlConnection conn = new SqlConnection(connectionStringProvider.GetConnectionString(instance)))
{
conn.Open();
conn.InfoMessage += new SqlInfoMessageEventHandler(delegate(object sender, SqlInfoMessageEventArgs e)
{
execution.AppendLog(e.Message);
execution.UpdateAndFlush();
});
using (System.Transactions.TransactionScope transaction = new System.Transactions.TransactionScope())
{
List<string> sqlStatements = GetSqlStatements(rollbackScript);
foreach (string statement in sqlStatements)
{
using (SqlCommand cmd = new SqlCommand(statement, conn))
{
cmd.ExecuteNonQuery();
}
}
execution.AppendLog("Scripts completed. Deleting migration entry...");
execution.UpdateAndFlush();
migrationTablesManager.DeleteSuccessfullRollback(migration, conn);
transaction.Complete();
}
}
execution.AppendLog("Rollback ended with success!");
execution.ExecutionState = ExecutionState.Completed;
execution.UpdateAndFlush();
}
catch (Exception ex)
{
execution.AppendLogException(ex);
execution.UpdateAndFlush();
}
}
private void SyncApplyMigration(Migration migration, Instance instance, Execution execution)
{
try
{
execution.AppendLog("Generating rollback scripts...");
execution.UpdateAndFlush();
string rollbackScript = scriptsGenerator.GenerateRollbackScript(migration, instance);
execution.AppendLog(string.Concat(Environment.NewLine, rollbackScript, Environment.NewLine));
execution.UpdateAndFlush();
execution.AppendLog("Generating migrate scripts...");
execution.UpdateAndFlush();
string migrateScript = scriptsGenerator.GenerateMigrateScript(migration, instance);
execution.AppendLog(string.Concat(Environment.NewLine, migrateScript, Environment.NewLine));
execution.UpdateAndFlush();
execution.AppendLog("Migrating...");
execution.UpdateAndFlush();
using (SqlConnection conn = new SqlConnection(connectionStringProvider.GetConnectionString(instance)))
{
conn.Open();
conn.InfoMessage += new SqlInfoMessageEventHandler(delegate(object sender, SqlInfoMessageEventArgs e)
{
execution.AppendLog(e.Message);
execution.UpdateAndFlush();
});
using (System.Transactions.TransactionScope transaction = new System.Transactions.TransactionScope())
{
List<string> sqlStatements = GetSqlStatements(migrateScript);
foreach (string statement in sqlStatements)
{
using (SqlCommand cmd = new SqlCommand(statement, conn))
{
cmd.ExecuteNonQuery();
}
}
execution.AppendLog("Scripts completed. Writing migration audit...");
execution.UpdateAndFlush();
DBAppliedMigration appliedMigration = new DBAppliedMigration(execution);
appliedMigration.MigrateScript = migrateScript;
appliedMigration.RollbackScript = rollbackScript;
migrationTablesManager.WriteSuccessfullMigration(appliedMigration, conn);
transaction.Complete();
}
}
execution.AppendLog("Migration ended with success!");
execution.ExecutionState = ExecutionState.Completed;
execution.UpdateAndFlush();
}
catch (Exception ex)
{
execution.AppendLogException(ex);
execution.UpdateAndFlush();
}
}
public bool IsMigrationAlreadyApplied(Migration migration, Instance instance, Execution currentExecution)
{
currentExecution.AppendLog("Is migration {0} already applied on {1} ? ...", migration.Id, instance.FullName);
bool returnValue = true;
using (SqlConnection connection = new SqlConnection(connectionStringProvider.GetConnectionString(instance)))
{
connection.Open();
using (SqlCommand cmd = new SqlCommand(@"SELECT COUNT(*)
FROM [DBAppliedMigrations]
WHERE [Migration_id]=@migrationId", connection))
{
cmd.Parameters.Add(new SqlParameter("migrationId", migration.Id));
int applied = (int)cmd.ExecuteScalar();
if (applied == 0)
{
currentExecution.AppendLog("Migration {0} has never been applied on {1}.", migration.Id, instance.FullName);
returnValue = false;
}
else
{
currentExecution.AppendLog("Migration {0} has already been applied on {1}.", migration.Id, instance.FullName);
}
}
}
return returnValue;
}
public string GetRollbackstring(Migration migration, Instance instance, Execution currentExecution)
{
currentExecution.AppendLog("Retreiving rollback script ...");
string returnValue = null;
using (SqlConnection connection = new SqlConnection(connectionStringProvider.GetConnectionString(instance)))
{
connection.Open();
using (SqlCommand cmd = new SqlCommand(@"SELECT [RollbackScript]
FROM [DBAppliedMigrations]
WHERE [Migration_id]=@migrationId", connection))
{
cmd.Parameters.Add(new SqlParameter("migrationId", migration.Id));
returnValue = (string)cmd.ExecuteScalar();
}
}
return returnValue;
}