C# SqlServerMigrationSqlGenerator.Generate方法代码示例

        public void Generate_can_output_statement_to_drop_foreign_key_operation()
        {
            var migrationSqlGenerator = new SqlServerMigrationSqlGenerator();

            var operation =
                new DropForeignKeyOperation
                {
                    Name = "FK_na'me",
                    DependentTable = "sch'ema.DependentTable"
                };

            var sql = migrationSqlGenerator.Generate(new [] { operation }, SqlProviderManifest.TokenAzure11)
                .Join(s => s.Sql, Environment.NewLine);

            const string expectedSql =
@"IF object_id(N'[sch''ema].[FK_na''me]', N'F') IS NOT NULL
    ALTER TABLE [sch'ema].[DependentTable] DROP CONSTRAINT [FK_na'me]";

            Assert.Equal(expectedSql, sql);
        }

        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_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_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_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_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_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_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_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
//.........这里部分代码省略.........

        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_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_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 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_statement_to_drop_index_operation()
        {
            var migrationSqlGenerator = new SqlServerMigrationSqlGenerator();

            var operation =
                new DropIndexOperation
                {
                    Name = "IX_na'me",
                    Table = "sch'ema.Ta'ble"
                };

            var sql = migrationSqlGenerator.Generate(new[] { operation }, SqlProviderManifest.TokenAzure11)
                .Join(s => s.Sql, Environment.NewLine);

            const string expectedSql =
@"IF EXISTS (SELECT name FROM sys.indexes WHERE name = N'IX_na''me' AND object_id = object_id(N'[sch''ema].[Ta''ble]', N'U'))
    DROP INDEX [IX_na'me] ON [sch'ema].[Ta'ble]";

            Assert.Equal(expectedSql, 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);
        }