C# SqlServer.SqlServerMigrationSqlGenerator类代码示例

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

        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);
        }