public void Can_get_and_set_sql_generator()
{
var migrationsConfiguration = new TestMigrationsConfiguration();
var migrationSqlGenerator = new SqlServerMigrationSqlGenerator();
migrationsConfiguration.SetSqlGenerator(DbProviders.Sql, migrationSqlGenerator);
Assert.Same(migrationSqlGenerator, migrationsConfiguration.GetSqlGenerator(DbProviders.Sql));
}
public void Generate_for_AlterTableAnnotationsOperation_checks_its_arguments()
{
var generator = new SqlServerMigrationSqlGenerator();
Assert.Equal(
"alterTableOperation",
Assert.Throws<ArgumentNullException>(() => generator.Generate((AlterTableOperation)null)).ParamName);
}
public void Generate_can_output_add_column_statement_with_custom_store_type_and_maxLength()
{
var migrationSqlGenerator = new SqlServerMigrationSqlGenerator();
var column = new ColumnModel(PrimitiveTypeKind.String)
{
Name = "Bar",
StoreType = "varchar",
MaxLength = 15
};
var addColumnOperation = new AddColumnOperation("Foo", column);
var sql = migrationSqlGenerator.Generate(new[] { addColumnOperation }, "2008").Join(s => s.Sql, Environment.NewLine);
Assert.Contains("ALTER TABLE [Foo] ADD [Bar] [varchar](15)", sql);
}
public void Generate_can_output_delete_history_statement()
{
var migrationSqlGenerator = new SqlServerMigrationSqlGenerator();
using (var historyContext = new HistoryContext())
{
var historyRow
= new HistoryRow
{
MigrationId = "House Lannister",
ContextKey = "The pointy end"
};
historyContext.History.Attach(historyRow);
historyContext.History.Remove(historyRow);
using (var commandTracer = new CommandTracer(historyContext))
{
historyContext.SaveChanges();
var deleteHistoryOperation
= new HistoryOperation(commandTracer.CommandTrees.OfType<DbModificationCommandTree>().ToList());
var sql
= migrationSqlGenerator
.Generate(new[] { deleteHistoryOperation }, "2008")
.Single();
Assert.Equal(@"DELETE [dbo].[__MigrationHistory]
WHERE (([MigrationId] = N'House Lannister') AND ([ContextKey] = N'The pointy end'))", sql.Sql.Trim());
}
}
}
public void Generate_can_output_drop_table_statement()
{
var migrationSqlGenerator = new SqlServerMigrationSqlGenerator();
var sql = migrationSqlGenerator.Generate(new[] { new DropTableOperation("Customers") }, "2008").Join(
s => s.Sql, Environment.NewLine);
Assert.Contains("DROP TABLE [Customers]", sql);
}
public void Generate_can_output_create_index_statement_clustered()
{
var createTableOperation = new CreateTableOperation("Customers");
var idColumn = new ColumnModel(PrimitiveTypeKind.Int32)
{
Name = "Id",
IsNullable = true,
IsIdentity = true
};
createTableOperation.Columns.Add(idColumn);
createTableOperation.Columns.Add(
new ColumnModel(PrimitiveTypeKind.String)
{
Name = "Name",
IsNullable = false
});
createTableOperation.PrimaryKey = new AddPrimaryKeyOperation();
createTableOperation.PrimaryKey.Columns.Add(idColumn.Name);
var migrationSqlGenerator = new SqlServerMigrationSqlGenerator();
var createIndexOperation = new CreateIndexOperation
{
Table = createTableOperation.Name,
IsUnique = true,
IsClustered = true
};
createIndexOperation.Columns.Add(idColumn.Name);
var sql
= migrationSqlGenerator.Generate(
new[]
{
createIndexOperation
},
"2008").Join(s => s.Sql, Environment.NewLine);
Assert.Contains(
@"CREATE UNIQUE CLUSTERED INDEX [IX_Id] ON [Customers]([Id])", sql);
}
public void Generate_can_output_move_procedure_statement()
{
var moveProcedureOperation
= new MoveProcedureOperation("dbo.History", "foo");
var migrationSqlGenerator = new SqlServerMigrationSqlGenerator();
var sql = migrationSqlGenerator.Generate(new[] { moveProcedureOperation }, "2008").Join(s => s.Sql, Environment.NewLine);
Assert.Contains(
@"IF schema_id('foo') IS NULL
EXECUTE('CREATE SCHEMA [foo]')
ALTER SCHEMA [foo] TRANSFER [dbo].[History]", sql);
}
public void Generate_can_output_create_table_statement_with_non_clustered_pk()
{
var createTableOperation = new CreateTableOperation("foo.Customers");
var idColumn = new ColumnModel(PrimitiveTypeKind.Int32)
{
Name = "Id",
IsNullable = true,
IsIdentity = true
};
createTableOperation.Columns.Add(idColumn);
createTableOperation.Columns.Add(
new ColumnModel(PrimitiveTypeKind.String)
{
Name = "Name",
IsNullable = false
});
createTableOperation.PrimaryKey
= new AddPrimaryKeyOperation
{
IsClustered = false
};
createTableOperation.PrimaryKey.Columns.Add(idColumn.Name);
var migrationSqlGenerator = new SqlServerMigrationSqlGenerator();
var sql = migrationSqlGenerator.Generate(new[] { createTableOperation }, "2008").Join(s => s.Sql, Environment.NewLine);
Assert.Contains(
@"IF schema_id('foo') IS NULL
EXECUTE('CREATE SCHEMA [foo]')
CREATE TABLE [foo].[Customers] (
[Id] [int] IDENTITY,
[Name] [nvarchar](max) NOT NULL,
CONSTRAINT [PK_foo.Customers] PRIMARY KEY NONCLUSTERED ([Id])
)", sql);
}
public void Generate_should_output_column_nullability_for_altered_non_nullable_columns()
{
var migrationSqlGenerator = new SqlServerMigrationSqlGenerator();
var alterColumnOperation =
new AlterColumnOperation("Customers", new ColumnModel(PrimitiveTypeKind.Int32) { Name = "Baz", IsNullable = false }, false);
var sql = migrationSqlGenerator.Generate(new[] { alterColumnOperation }, "2012").Join(s => s.Sql, Environment.NewLine);
Assert.Contains("ALTER TABLE [Customers] ALTER COLUMN [Baz] [int] NOT NULL", sql);
}
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);
}
public void Generate_can_output_non_clustered_add_primary_key_operation()
{
var migrationSqlGenerator = new SqlServerMigrationSqlGenerator();
var addPrimaryKeyOperation = new AddPrimaryKeyOperation
{
Table = "T",
IsClustered = false
};
addPrimaryKeyOperation.Columns.Add("c1");
addPrimaryKeyOperation.Columns.Add("c2");
var sql = migrationSqlGenerator.Generate(new[] { addPrimaryKeyOperation }, "2008").Join(s => s.Sql, Environment.NewLine);
Assert.Contains("ALTER TABLE [T] ADD CONSTRAINT [PK_T] PRIMARY KEY NONCLUSTERED ([c1], [c2])", sql);
}
public void Generate_can_output_drop_primary_key_operation()
{
var migrationSqlGenerator = new SqlServerMigrationSqlGenerator();
var dropPrimaryKeyOperation = new DropPrimaryKeyOperation
{
Table = "T"
};
var sql = migrationSqlGenerator.Generate(new[] { dropPrimaryKeyOperation }, "2008").Join(s => s.Sql, Environment.NewLine);
Assert.Contains("ALTER TABLE [T] DROP CONSTRAINT [PK_T]", sql);
}
public void Generate_can_output_add_timestamp_store_type_column_operation()
{
var migrationSqlGenerator = new SqlServerMigrationSqlGenerator();
var addColumnOperation
= new AddColumnOperation(
"T",
new ColumnModel(PrimitiveTypeKind.Binary)
{
IsNullable = false,
Name = "C",
StoreType = "timestamp"
});
var sql = migrationSqlGenerator.Generate(new[] { addColumnOperation }, "2008").Join(s => s.Sql, Environment.NewLine);
Assert.Contains("ALTER TABLE [T] ADD [C] [timestamp] NOT NULL", sql);
}
public void Generate_for_ColumnModel_checks_its_arguments()
{
var generator = new SqlServerMigrationSqlGenerator();
var writer = new IndentedTextWriter(new Mock<TextWriter>().Object);
var columnModel = new ColumnModel(PrimitiveTypeKind.Int32);
Assert.Equal(
"column",
Assert.Throws<ArgumentNullException>(() => generator.Generate(null, writer)).ParamName);
Assert.Equal(
"writer",
Assert.Throws<ArgumentNullException>(() => generator.Generate(columnModel, null)).ParamName);
}
public void DropDefaultConstraint_checks_its_arguments()
{
var generator = new SqlServerMigrationSqlGenerator();
var writer = new IndentedTextWriter(new Mock<TextWriter>().Object);
Assert.Equal(
Strings.ArgumentIsNullOrWhitespace("table"),
Assert.Throws<ArgumentException>(
() => generator.DropDefaultConstraint(null, "Spektor", writer)).Message);
Assert.Equal(
Strings.ArgumentIsNullOrWhitespace("column"),
Assert.Throws<ArgumentException>(
() => generator.DropDefaultConstraint("Regina", null, writer)).Message);
Assert.Equal(
"writer",
Assert.Throws<ArgumentNullException>(() => generator.DropDefaultConstraint("Regina", "Spektor", null)).ParamName);
}
public void Generate_can_output_rename_procedure_statements()
{
var model1 = new TestContext();
var model2 = new TestContext_v2();
var commandTreeGenerator
= new ModificationCommandTreeGenerator(TestContext.CreateDynamicUpdateModel());
var renameProcedureOperation
= new EdmModelDiffer()
.Diff(
model1.GetModel(),
model2.GetModel(),
new Lazy<ModificationCommandTreeGenerator>(() => commandTreeGenerator),
new SqlServerMigrationSqlGenerator())
.OfType<RenameProcedureOperation>()
.Single();
var migrationSqlGenerator = new SqlServerMigrationSqlGenerator();
var sql = migrationSqlGenerator.Generate(new[] { renameProcedureOperation }, "2008").Join(s => s.Sql, Environment.NewLine);
Assert.Equal(@"EXECUTE sp_rename @objname = N'dbo.Order_Insert', @newname = N'sproc_A', @objtype = N'OBJECT'", sql);
}
public void Generate_can_output_drop_procedure_statement()
{
var dropModificationFunctionsOperation
= new DropProcedureOperation("Customer_Insert");
var migrationSqlGenerator = new SqlServerMigrationSqlGenerator();
var sql
= migrationSqlGenerator
.Generate(new[] { dropModificationFunctionsOperation }, "2008")
.Join(s => s.Sql, Environment.NewLine);
Assert.Contains(
@"DROP PROCEDURE [Customer_Insert]", sql);
}
public void Generate_can_output_drop_column()
{
var migrationSqlGenerator = new SqlServerMigrationSqlGenerator();
var dropColumnOperation = new DropColumnOperation("Customers", "Foo");
var sql = migrationSqlGenerator.Generate(new[] { dropColumnOperation }, "2008").Join(s => s.Sql, Environment.NewLine);
Assert.Contains("ALTER TABLE [Customers] DROP COLUMN [Foo]", sql);
}
public void Generate_can_output_rename_table_statements()
{
var renameTableOperation = new RenameTableOperation("dbo.Foo", "Bar");
var migrationSqlGenerator = new SqlServerMigrationSqlGenerator();
var sql = migrationSqlGenerator.Generate(new[] { renameTableOperation }, "2008").Join(s => s.Sql, Environment.NewLine);
Assert.Contains(
@"EXECUTE sp_rename @objname = N'dbo.Foo', @newname = N'Bar', @objtype = N'OBJECT'
IF object_id('[PK_dbo.Foo]') IS NOT NULL BEGIN
EXECUTE sp_rename @objname = N'[PK_dbo.Foo]', @newname = N'PK_dbo.Bar', @objtype = N'OBJECT'
END", sql);
}
public void Generate_can_output_timestamp_column()
{
var migrationSqlGenerator = new SqlServerMigrationSqlGenerator();
var createTableOperation = new CreateTableOperation("Customers");
var column = new ColumnModel(PrimitiveTypeKind.Binary)
{
Name = "Version",
IsTimestamp = true
};
createTableOperation.Columns.Add(column);
var sql = migrationSqlGenerator.Generate(new[] { createTableOperation }, "2008").Join(s => s.Sql, Environment.NewLine);
Assert.Contains(@"[Version] rowversion", sql);
}
public void Generate_can_output_move_table_as_system_object_statement()
{
var createTableOperation
= new CreateTableOperation("dbo.History");
createTableOperation.Columns.Add(
new ColumnModel(PrimitiveTypeKind.Int32)
{
Name = "Id",
IsNullable = false
});
createTableOperation.Columns.Add(
new ColumnModel(PrimitiveTypeKind.String)
{
Name = "Name",
IsNullable = false
});
var moveTableOperation
= new MoveTableOperation("dbo.History", "foo")
{
IsSystem = true,
ContextKey = "MyKey",
CreateTableOperation = createTableOperation
};
var migrationSqlGenerator = new SqlServerMigrationSqlGenerator();
var sql = migrationSqlGenerator.Generate(new[] { moveTableOperation }, "2008").Join(s => s.Sql, Environment.NewLine);
Assert.Contains(
@"IF schema_id('foo') IS NULL
EXECUTE('CREATE SCHEMA [foo]')
IF object_id('dbo.History') IS NULL BEGIN
CREATE TABLE [dbo].[History] (
[Id] [int] NOT NULL,
[Name] [nvarchar](max) NOT NULL
)
END
INSERT INTO [dbo].[History]
SELECT * FROM [dbo].[History]
WHERE [ContextKey] = 'MyKey'
DELETE [dbo].[History]
WHERE [ContextKey] = 'MyKey'
IF NOT EXISTS(SELECT * FROM [dbo].[History])
DROP TABLE [dbo].[History]", sql);
}
public void Generate_can_output_custom_sql_operation()
{
var migrationSqlGenerator = new SqlServerMigrationSqlGenerator();
var sql = migrationSqlGenerator.Generate(new[] { new SqlOperation("insert into foo") }, "2008").Join(
s => s.Sql, Environment.NewLine);
Assert.Contains(@"insert into foo", sql);
}
public void Generate_can_output_add_fk_statement()
{
var addForeignKeyOperation = new AddForeignKeyOperation
{
PrincipalTable = "Customers",
DependentTable = "Orders",
CascadeDelete = true
};
addForeignKeyOperation.PrincipalColumns.Add("CustomerId");
addForeignKeyOperation.DependentColumns.Add("CustomerId");
var migrationSqlGenerator = new SqlServerMigrationSqlGenerator();
var sql = migrationSqlGenerator.Generate(new[] { addForeignKeyOperation }, "2008").Join(s => s.Sql, Environment.NewLine);
Assert.Contains(
@"ALTER TABLE [Orders] ADD CONSTRAINT [FK_Orders_Customers_CustomerId] FOREIGN KEY ([CustomerId]) REFERENCES [Customers] ([CustomerId]) ON DELETE CASCADE",
sql);
}
public void Generate_should_output_batched_custom_sql_operation()
{
var migrationSqlGenerator = new SqlServerMigrationSqlGenerator();
var statementBatch = File.ReadAllText("TestDataFiles/SqlOperation_Batch.sql");
var statements = migrationSqlGenerator
.Generate(new[] { new SqlOperation(statementBatch) }, "2008")
.ToList();
Assert.Equal(3, statements.Count);
Assert.Equal("insert into foo", statements[0].Sql.Trim());
Assert.Equal("insert into bar VALUES ('ab')", statements[1].Sql.Trim());
Assert.Equal("insert into bar VALUES ('ab')", statements[2].Sql.Trim());
}
public void Generate_can_output_insert_history_statement()
{
var migrationSqlGenerator = new SqlServerMigrationSqlGenerator();
using (var historyContext = new HistoryContext())
{
historyContext.History.Add(
new HistoryRow
{
MigrationId = "House Lannister",
ContextKey = "The pointy end",
Model = new byte[0],
ProductVersion = "Awesomeness"
});
using (var commandTracer = new CommandTracer(historyContext))
{
historyContext.SaveChanges();
var insertHistoryOperation
= new HistoryOperation(commandTracer.CommandTrees.OfType<DbModificationCommandTree>().ToList());
var sql
= migrationSqlGenerator
.Generate(new[] { insertHistoryOperation }, "2008")
.Single();
Assert.Equal(@"INSERT [dbo].[__MigrationHistory]([MigrationId], [ContextKey], [Model], [ProductVersion])
VALUES (N'House Lannister', N'The pointy end', 0x , N'Awesomeness')", sql.Sql.Trim());
}
}
}
public void Generate_can_output_create_procedure_statements()
{
var modelBuilder = new DbModelBuilder();
var model1 = modelBuilder.Build(ProviderRegistry.Sql2008_ProviderInfo);
var model2 = new TestContext();
var commandTreeGenerator
= new ModificationCommandTreeGenerator(TestContext.CreateDynamicUpdateModel());
var createProcedureOperation
= new EdmModelDiffer()
.Diff(
model1.GetModel(),
model2.GetModel(),
new Lazy<ModificationCommandTreeGenerator>(() => commandTreeGenerator),
new SqlServerMigrationSqlGenerator())
.OfType<CreateProcedureOperation>()
.Single(c => c.Name == "dbo.ExtraSpecialOrder_Update");
var migrationSqlGenerator = new SqlServerMigrationSqlGenerator();
var sql = migrationSqlGenerator.Generate(new[] { createProcedureOperation }, "2008").Join(s => s.Sql, Environment.NewLine);
Assert.Equal(
@"CREATE PROCEDURE [dbo].[ExtraSpecialOrder_Update]
@xid [int],
@key_for_update [uniqueidentifier],
@Code [nvarchar](128),
@Signature [varbinary](128),
@Name [nvarchar](max),
@Name_Original [nvarchar](max),
@Address_Street [nvarchar](max),
@Address_City [nvarchar](max),
@Address_CountryOrRegion_Name [nvarchar](max),
@OrderGroupId [int],
@RowVersion_Original [rowversion],
@OtherAddress_Street [nvarchar](max),
@OtherAddress_City [nvarchar](max),
@OtherAddress_CountryOrRegion_Name [nvarchar](max),
@TheSpecialist [int],
@Customer_CustomerId [int],
@OtherCustomer_CustomerId [int]
AS
BEGIN
UPDATE [dbo].[Orders]
SET [Name] = @Name, [Address_Street] = @Address_Street, [Address_City] = @Address_City, [Address_CountryOrRegion_Name] = @Address_CountryOrRegion_Name, [OrderGroupId] = @OrderGroupId, [Customer_CustomerId] = @Customer_CustomerId
WHERE (((((([order_id] = @xid) AND ([Key] = @key_for_update)) AND ([Code] = @Code)) AND ([Signature] = @Signature)) AND (([Name] = @Name_Original) OR ([Name] IS NULL AND @Name_Original IS NULL))) AND (([RowVersion] = @RowVersion_Original) OR ([RowVersion] IS NULL AND @RowVersion_Original IS NULL)))
UPDATE [dbo].[special_orders]
SET [OtherCustomer_CustomerId] = @OtherCustomer_CustomerId, [OtherAddress_Street] = @OtherAddress_Street, [OtherAddress_City] = @OtherAddress_City, [OtherAddress_CountryOrRegion_Name] = @OtherAddress_CountryOrRegion_Name
WHERE (((([order_id] = @xid) AND ([so_key] = @key_for_update)) AND ([Code] = @Code)) AND ([Signature] = @Signature))
AND @@ROWCOUNT > 0
UPDATE [dbo].[xspecial_orders]
SET [TheSpecialist] = @TheSpecialist
WHERE (((([xid] = @xid) AND ([so_key] = @key_for_update)) AND ([Code] = @Code)) AND ([Signature] = @Signature))
AND @@ROWCOUNT > 0
SELECT t0.[OrderNo] AS order_fu, t0.[RowVersion], t1.[MagicOrderToken], t2.[FairyDust]
FROM [dbo].[Orders] AS t0
JOIN [dbo].[special_orders] AS t1 ON t1.[order_id] = t0.[order_id] AND t1.[so_key] = t0.[Key] AND t1.[Code] = t0.[Code] AND t1.[Signature] = t0.[Signature]
JOIN [dbo].[xspecial_orders] AS t2 ON t2.[xid] = t0.[order_id] AND t2.[so_key] = t0.[Key] AND t2.[Code] = t0.[Code] AND t2.[Signature] = t0.[Signature]
WHERE @@ROWCOUNT > 0 AND t0.[order_id] = @xid AND t0.[Key] = @key_for_update AND t0.[Code] = @Code AND t0.[Signature] = @Signature
END", sql);
}
public void Generate_can_output_add_column_statement_for_GUID(string providerManifestToken, string expectedGuidDefault)
{
var migrationSqlGenerator = new SqlServerMigrationSqlGenerator();
var column = new ColumnModel(PrimitiveTypeKind.Guid)
{
Name = "Bar",
IsIdentity = true
};
var addColumnOperation = new AddColumnOperation("Foo", column);
var sql = migrationSqlGenerator.Generate(new[] { addColumnOperation }, providerManifestToken)
.Join(s => s.Sql, Environment.NewLine);
Assert.Contains(string.Format("ALTER TABLE [Foo] ADD [Bar] [uniqueidentifier] DEFAULT {0}", expectedGuidDefault), sql);
}
public void Generate_can_output_alter_procedure_statements()
{
var model1 = new TestContext();
var model2 = new TestContext_v2();
var commandTreeGenerator
= new ModificationCommandTreeGenerator(TestContext.CreateDynamicUpdateModel());
var alterProcedureOperation
= new EdmModelDiffer()
.Diff(
model1.GetModel(),
model2.GetModel(),
new Lazy<ModificationCommandTreeGenerator>(() => commandTreeGenerator),
new SqlServerMigrationSqlGenerator())
.OfType<AlterProcedureOperation>()
.Single(c => c.Name == "dbo.Order_Update");
var migrationSqlGenerator = new SqlServerMigrationSqlGenerator();
var sql = migrationSqlGenerator.Generate(new[] { alterProcedureOperation }, "2008").Join(s => s.Sql, Environment.NewLine);
Assert.Equal(
@"ALTER PROCEDURE [dbo].[Order_Update]
@order_id [int],
@key_for_update2 [uniqueidentifier],
@Code [nvarchar](128),
@Signature [varbinary](128),
@Name [nvarchar](max),
@Name_Original [nvarchar](max),
@Address_Street [nvarchar](max),
@Address_City [nvarchar](max),
@Address_CountryOrRegion_Name [nvarchar](max),
@OrderGroupId [int],
@RowVersion_Original [rowversion],
@Customer_CustomerId [int]
AS
BEGIN
UPDATE [dbo].[Orders]
SET [Name] = @Name, [Address_Street] = @Address_Street, [Address_City] = @Address_City, [Address_CountryOrRegion_Name] = @Address_CountryOrRegion_Name, [OrderGroupId] = @OrderGroupId, [Customer_CustomerId] = @Customer_CustomerId
WHERE (((((([order_id] = @order_id) AND ([Key] = @key_for_update2)) AND ([Code] = @Code)) AND ([Signature] = @Signature)) AND (([Name] = @Name_Original) OR ([Name] IS NULL AND @Name_Original IS NULL))) AND (([RowVersion] = @RowVersion_Original) OR ([RowVersion] IS NULL AND @RowVersion_Original IS NULL)))
SELECT t0.[OrderNo], t0.[RowVersion]
FROM [dbo].[Orders] AS t0
WHERE @@ROWCOUNT > 0 AND t0.[order_id] = @order_id AND t0.[Key] = @key_for_update2 AND t0.[Code] = @Code AND t0.[Signature] = @Signature
END", sql);
}
public void Generate_can_output_add_geography_column_operation_with_implicit_default_value()
{
var operation
= new AddColumnOperation(
"T",
new ColumnModel(PrimitiveTypeKind.Geography)
{
IsNullable = false,
Name = "C"
});
var sql = new SqlServerMigrationSqlGenerator().Generate(new[] { operation }, "2008").Join(s => s.Sql, Environment.NewLine);
Assert.Equal("ALTER TABLE [T] ADD [C] [geography] NOT NULL DEFAULT 'SRID=4326;POINT (0 0)'", sql);
}
public void Generate_can_output_rename_index_statements()
{
var renameIndexOperation = new RenameIndexOperation("dbo.Foo", "Bar", "Baz");
var migrationSqlGenerator = new SqlServerMigrationSqlGenerator();
var sql = migrationSqlGenerator.Generate(new[] { renameIndexOperation }, "2008").Join(s => s.Sql, Environment.NewLine);
Assert.Contains(
@"EXECUTE sp_rename @objname = N'dbo.Foo.Bar', @newname = N'Baz', @objtype = N'INDEX'", sql);
}