public void Can_add_and_retrieve_migrations()
{
var updateDatabaseOperation
= new UpdateDatabaseOperation(new List <DbQueryCommandTree>());
updateDatabaseOperation.AddMigration("M1", new List <MigrationOperation>());
updateDatabaseOperation.AddMigration("M2", new List <MigrationOperation>());
Assert.Equal(2, updateDatabaseOperation.Migrations.Count);
}
public void Can_add_and_retrieve_migrations()
{
var updateDatabaseOperation
= new UpdateDatabaseOperation(new List<DbQueryCommandTree>());
updateDatabaseOperation.AddMigration("M1", new List<MigrationOperation>());
updateDatabaseOperation.AddMigration("M2", new List<MigrationOperation>());
Assert.Equal(2, updateDatabaseOperation.Migrations.Count);
}
/// <summary>
/// Produces a script to update the database.
/// </summary>
/// <param name="sourceMigration">
/// The migration to update from. If null is supplied, a script to update the
/// current database will be produced.
/// </param>
/// <param name="targetMigration">
/// The migration to update to. If null is supplied,
/// a script to update to the latest migration will be produced.
/// </param>
/// <returns> The generated SQL script. </returns>
public string ScriptUpdate(string sourceMigration, string targetMigration)
{
_sqlBuilder.Clear();
if (string.IsNullOrWhiteSpace(sourceMigration))
{
Update(targetMigration);
}
else
{
if (sourceMigration.IsAutomaticMigration())
{
throw Error.AutoNotValidForScriptWindows(sourceMigration);
}
var sourceMigrationId = GetMigrationId(sourceMigration);
var pendingMigrations = GetLocalMigrations().Where(m => string.CompareOrdinal(m, sourceMigrationId) > 0);
string targetMigrationId = null;
if (!string.IsNullOrWhiteSpace(targetMigration))
{
if (targetMigration.IsAutomaticMigration())
{
throw Error.AutoNotValidForScriptWindows(targetMigration);
}
targetMigrationId = GetMigrationId(targetMigration);
if (string.CompareOrdinal(sourceMigrationId, targetMigrationId) > 0)
{
throw Error.DownScriptWindowsNotSupported();
}
pendingMigrations = pendingMigrations.Where(m => string.CompareOrdinal(m, targetMigrationId) <= 0);
}
_updateDatabaseOperation
= sourceMigration == DbMigrator.InitialDatabase
? new UpdateDatabaseOperation(base.CreateDiscoveryQueryTrees().ToList())
: null;
Upgrade(pendingMigrations, targetMigrationId, sourceMigrationId);
if (_updateDatabaseOperation != null)
{
ExecuteStatements(base.GenerateStatements(new[] { _updateDatabaseOperation }, null));
}
}
return _sqlBuilder.ToString();
}
public void AddMigration_should_validate_preconditions()
{
var updateDatabaseOperation
= new UpdateDatabaseOperation(new List <DbQueryCommandTree>());
Assert.Equal(
new ArgumentException(Strings.ArgumentIsNullOrWhitespace("migrationId")).Message,
Assert.Throws <ArgumentException>(() => updateDatabaseOperation.AddMigration(null, null)).Message);
Assert.Equal(
"operations",
Assert.Throws <ArgumentNullException>(() => updateDatabaseOperation.AddMigration("M", null)).ParamName);
}
public void AddMigration_should_validate_preconditions()
{
var updateDatabaseOperation
= new UpdateDatabaseOperation(new List<DbQueryCommandTree>());
Assert.Equal(
new ArgumentException(Strings.ArgumentIsNullOrWhitespace("migrationId")).Message,
Assert.Throws<ArgumentException>(() => updateDatabaseOperation.AddMigration(null, null)).Message);
Assert.Equal(
"operations",
Assert.Throws<ArgumentNullException>(() => updateDatabaseOperation.AddMigration("M", null)).ParamName);
}
public void Generate_can_handle_update_database_operations()
{
var migrationSqlGenerator = new SqlCeMigrationSqlGenerator();
var providerInvariantName = ProviderRegistry.SqlCe4_ProviderInfo.ProviderInvariantName;
var historyRepository
= new HistoryRepository(
new SqlCeConnectionFactory(providerInvariantName)
.CreateConnection("Foo").ConnectionString,
DbProviderFactories.GetFactory(providerInvariantName),
"MyKey",
null);
var updateDatabaseOperation
= new UpdateDatabaseOperation(historyRepository.CreateDiscoveryQueryTrees().ToList());
updateDatabaseOperation.AddMigration("M1", new []{ new DropColumnOperation("Customers", "Foo") });
var sql = migrationSqlGenerator.Generate(new[] { updateDatabaseOperation }, "2008").Join(s => s.Sql, Environment.NewLine);
Assert.Equal(@"ALTER TABLE [Customers] DROP COLUMN [Foo]", sql);
}
protected virtual IEnumerable<MigrationStatement> Generate(UpdateDatabaseOperation operation)
{
return GenerateStatements(operation.Migrations.SelectMany(x => x.Operations));
}
/// <summary>
/// Generates the specified update database operation which represents applying a series of migrations.
/// The generated script is idempotent, meaning it contains conditional logic to check if individual migrations
/// have already been applied and only apply the pending ones.
/// </summary>
/// <param name="updateDatabaseOperation">The update database operation.</param>
protected virtual void Generate(UpdateDatabaseOperation updateDatabaseOperation)
{
Check.NotNull(updateDatabaseOperation, "updateDatabaseOperation");
if (!updateDatabaseOperation.Migrations.Any())
{
return;
}
using (var writer = Writer())
{
writer.WriteLine("DECLARE @CurrentMigration [nvarchar](max)");
writer.WriteLine();
foreach (var historyQueryTree in updateDatabaseOperation.HistoryQueryTrees)
{
HashSet<string> _;
var historyQuery
= _sqlGenerator.GenerateSql(historyQueryTree, out _);
writer.Write("IF object_id('");
writer.Write(Escape(_sqlGenerator.Targets.Single()));
writer.WriteLine("') IS NOT NULL");
writer.Indent++;
writer.WriteLine("SELECT @CurrentMigration =");
writer.Indent++;
writer.Write("(");
writer.Write(Indent(historyQuery, writer.CurrentIndentation()));
writer.WriteLine(")");
writer.Indent -= 2;
writer.WriteLine();
}
writer.WriteLine("IF @CurrentMigration IS NULL");
writer.Indent++;
writer.WriteLine("SET @CurrentMigration = '0'");
Statement(writer);
}
var existingStatements = _statements;
foreach (var migration in updateDatabaseOperation.Migrations)
{
using (var writer = Writer())
{
_statements = new List<MigrationStatement>();
GenerateStatements(migration.Operations);
if (_statements.Count > 0)
{
writer.Write("IF @CurrentMigration < '");
writer.Write(Escape(migration.MigrationId));
writer.WriteLine("'");
writer.Write("BEGIN");
using (var blockWriter = Writer())
{
blockWriter.WriteLine();
blockWriter.Indent++;
foreach (var migrationStatement in _statements)
{
if (string.IsNullOrWhiteSpace(migrationStatement.BatchTerminator))
{
migrationStatement.Sql.EachLine(blockWriter.WriteLine);
}
else
{
blockWriter.WriteLine("EXECUTE('");
blockWriter.Indent++;
migrationStatement.Sql.EachLine(l => blockWriter.WriteLine(Escape(l)));
blockWriter.Indent--;
blockWriter.WriteLine("')");
}
}
writer.WriteLine(blockWriter.InnerWriter.ToString().TrimEnd());
}
writer.WriteLine("END");
existingStatements.Add(
new MigrationStatement
{
Sql
= writer.InnerWriter.ToString()
});
}
}
}
_statements = existingStatements;
}
public void Generate_can_handle_update_database_operations()
{
var migrationSqlGenerator = new SqlServerMigrationSqlGenerator();
var historyRepository
= new HistoryRepository(
Mock.Of<InternalContextForMock>(),
new SqlConnectionFactory().CreateConnection("Foo").ConnectionString,
DbProviderFactories.GetFactory(ProviderRegistry.Sql2008_ProviderInfo.ProviderInvariantName),
"MyKey",
null,
HistoryContext.DefaultFactory,
schemas: new[] { "dbo", "foo", "bar" });
var updateDatabaseOperation
= new UpdateDatabaseOperation(historyRepository.CreateDiscoveryQueryTrees().ToList());
updateDatabaseOperation.AddMigration(
"V1",
new MigrationOperation[]
{
new DropColumnOperation("Customers", "Foo"),
new CreateProcedureOperation("Foo", "Bar")
});
updateDatabaseOperation.AddMigration(
"V2",
new MigrationOperation[]
{
new AddColumnOperation("Customers", new ColumnModel(PrimitiveTypeKind.String) { Name = "C" }),
new CreateProcedureOperation("bar", "baz")
});
var sql = migrationSqlGenerator.Generate(new[] { updateDatabaseOperation }, "2008").Join(s => s.Sql, Environment.NewLine);
Assert.Equal(@"DECLARE @CurrentMigration [nvarchar](max)
IF object_id('[dbo].[__MigrationHistory]') IS NOT NULL
SELECT @CurrentMigration =
(SELECT TOP (1)
[Project1].[MigrationId] AS [MigrationId]
FROM ( SELECT
[Extent1].[MigrationId] AS [MigrationId]
FROM [dbo].[__MigrationHistory] AS [Extent1]
WHERE [Extent1].[ContextKey] = N'MyKey'
) AS [Project1]
ORDER BY [Project1].[MigrationId] DESC)
IF object_id('[foo].[__MigrationHistory]') IS NOT NULL
SELECT @CurrentMigration =
(SELECT TOP (1)
[Project1].[MigrationId] AS [MigrationId]
FROM ( SELECT
[Extent1].[MigrationId] AS [MigrationId]
FROM [foo].[__MigrationHistory] AS [Extent1]
WHERE [Extent1].[ContextKey] = N'MyKey'
) AS [Project1]
ORDER BY [Project1].[MigrationId] DESC)
IF object_id('[bar].[__MigrationHistory]') IS NOT NULL
SELECT @CurrentMigration =
(SELECT TOP (1)
[Project1].[MigrationId] AS [MigrationId]
FROM ( SELECT
[Extent1].[MigrationId] AS [MigrationId]
FROM [bar].[__MigrationHistory] AS [Extent1]
WHERE [Extent1].[ContextKey] = N'MyKey'
) AS [Project1]
ORDER BY [Project1].[MigrationId] DESC)
IF @CurrentMigration IS NULL
SET @CurrentMigration = '0'
IF @CurrentMigration < 'V1'
BEGIN
DECLARE @var0 nvarchar(128)
SELECT @var0 = name
FROM sys.default_constraints
WHERE parent_object_id = object_id(N'Customers')
AND col_name(parent_object_id, parent_column_id) = 'Foo';
IF @var0 IS NOT NULL
EXECUTE('ALTER TABLE [Customers] DROP CONSTRAINT [' + @var0 + ']')
ALTER TABLE [Customers] DROP COLUMN [Foo]
EXECUTE('
CREATE PROCEDURE [Foo]
AS
BEGIN
Bar
END
')
END
IF @CurrentMigration < 'V2'
BEGIN
ALTER TABLE [Customers] ADD [C] [nvarchar](max)
EXECUTE('
CREATE PROCEDURE [bar]
AS
BEGIN
baz
END
')
END
", sql);
}
private MigrationStatement Generate(UpdateDatabaseOperation updateDatabaseOperation)
{
if (updateDatabaseOperation == null)
throw new ArgumentNullException("UpdateDatabaseOperation");
MigrationStatement statement = new MigrationStatement();
StringBuilder sql = new StringBuilder();
const string idempotentScriptName = "_idempotent_script";
SelectGenerator generator = new SelectGenerator();
if (!updateDatabaseOperation.Migrations.Any())
return statement;
sql.AppendFormat("DROP PROCEDURE IF EXISTS `{0}`;", idempotentScriptName);
sql.AppendLine();
sql.AppendLine();
sql.AppendLine("DELIMITER //");
sql.AppendLine();
sql.AppendFormat("CREATE PROCEDURE `{0}`()", idempotentScriptName);
sql.AppendLine();
sql.AppendLine("BEGIN");
sql.AppendLine(" DECLARE CurrentMigration TEXT;");
sql.AppendLine();
sql.AppendLine(" IF EXISTS(SELECT 1 FROM information_schema.tables ");
sql.AppendLine(" WHERE table_name = '__MigrationHistory' ");
sql.AppendLine(" AND table_schema = DATABASE()) THEN ");
foreach (var historyQueryTree in updateDatabaseOperation.HistoryQueryTrees)
{
string historyQuery = generator.GenerateSQL(historyQueryTree);
ReplaceParemeters(ref historyQuery, generator.Parameters);
sql.AppendLine(@" SET CurrentMigration = (" + historyQuery + ");");
sql.AppendLine(" END IF;");
sql.AppendLine();
}
sql.AppendLine(" IF CurrentMigration IS NULL THEN");
sql.AppendLine(" SET CurrentMigration = '0';");
sql.AppendLine(" END IF;");
sql.AppendLine();
// Migrations
foreach (var migration in updateDatabaseOperation.Migrations)
{
if (migration.Operations.Count == 0)
continue;
sql.AppendLine(" IF CurrentMigration < '" + migration.MigrationId + "' THEN ");
var statements = Generate(migration.Operations, _providerManifestToken);
foreach (var migrationStatement in statements)
{
string sqlStatement = migrationStatement.Sql;
if (!sqlStatement.EndsWith(";"))
sqlStatement += ";";
sql.AppendLine(sqlStatement);
}
sql.AppendLine(" END IF;");
sql.AppendLine();
}
sql.AppendLine("END //");
sql.AppendLine();
sql.AppendLine("DELIMITER ;");
sql.AppendLine();
sql.AppendFormat("CALL `{0}`();", idempotentScriptName);
sql.AppendLine();
sql.AppendLine();
sql.AppendFormat("DROP PROCEDURE IF EXISTS `{0}`;", idempotentScriptName);
sql.AppendLine();
statement.Sql = sql.ToString();
return statement;
}
/// <summary>
/// Generates the specified update database operation which represents applying a series of migrations.
/// The generated script is idempotent, meaning it contains conditional logic to check if individual migrations
/// have already been applied and only apply the pending ones.
/// </summary>
/// <param name="updateDatabaseOperation">The update database operation.</param>
protected virtual void Generate(UpdateDatabaseOperation updateDatabaseOperation)
{
Check.NotNull(updateDatabaseOperation, "updateDatabaseOperation");
GenerateStatements(updateDatabaseOperation.Migrations.SelectMany(m => m.Operations));
}
