如何覆盖由MigratorScriptingDecorator生成的SQL脚本 [英] How can I override SQL scripts generated by MigratorScriptingDecorator

问题描述

使用实体框架4.3.1代码和数据迁移。

我已经编写了一个实用程序来自动生成Migration脚本一个目标数据库，使用MigratorScriptingDecorator。

但是，有时从头重新生成目标数据库时，生成的脚本无效，因为它声明一个变量，

变量名称为 @ var0 。

当应用多个迁移时，以及至少两个导致默认约束被删除时发生。

生成脚本表单代码时出现问题，当使用Package Manager控制台命令时：

 更新数据库-Script 
  pre> 
 
 
以下是生成的脚本的违规代码片段：
  DECLARE @ var0 nvarchar（128）
 SELECT @ var0 = name 
 FRO M sys.default_constraints 
 WHERE parent_object_id = object_id（N'SomeTableName'）
  
和
 
 
 
  DECLARE @ var0 nvarchar（128）
 SELECT @ var0 = name 
 FROM sys.default_constraints 
 WHERE parent_object_id = object_id（N'SomeOtherTableName'）
  
我希望能够覆盖它为每个迁移生成SQL，然后添加一个GO语句，以便每个迁移都在一个单独的批处理中，这将解决问题。
 
 
 
任何人都有想法如何做，或者如果我正在树上错误的树，那么也许你可以建议一个更好的方法？
解决方案
因此，广泛使用 ILSpy 以及这个问题的答案我找到了一种方式。
 
 
 
下面的详细信息
 
 
 
 问题 
 
 
 
  SqlServerMigrationSqlGenerator 是最终负责创建在目标数据库中执行的SQL语句，或者在使用包管理器中的 -Script 开关时脚本化控制台或使用 MigratorScriptingDecorator 。
 
 
 
 工作 
 
 
 
检查负责$ code> DROP COLUMN 的 SqlServerMigrationSqlGenerator 中的Genearate方法看起来像这样：
  protected virtual void Generate（DropColumnOperation dropColumnOperation）
 {
 RuntimeFailureMethods 
 .Requires（dropColumnOperation！= null，null，dropColumnOperation！= null）; 
使用（IndentedTextWriter indentedTextWriter = 
 SqlServerMigrationSqlGenerator.Writer（））
 {
 string value =@var+ this._variableCounter ++; 
 indentedTextWriter.Write（DECLARE）; 
 indentedTextWriter.Write（value）; 
 indentedTextWriter.WriteLine（nvarchar（128））; 
 indentedTextWriter.Write（SELECT）; 
 indentedTextWriter.Write（value）; 
 indentedTextWriter.WriteLine（= name）; 
 indentedTextWriter.WriteLine（FROM sys.default_constraints）; 
 indentedTextWriter.Write（WHERE parent_object_id = object_id（N'）; 
 indentedTextWriter.Write（dropColumnOperation.Table）; 
 indentedTextWriter.WriteLine（'））; 
 indentedTextWriter.Write（AND col_name（parent_object_id，
 parent_column_id）='）; 
 indentedTextWriter.Write（dropColumnOperation.Name）; 
 indentedTextWriter.WriteLine（';）; 
 indentedTextWriter.Write（IF）; 
 indentedTextWriter.Write（value）; 
 indentedTextWriter.WriteLine（IS NOT NULL）; 
 indentedTextWriter.Indent ++; 
 indentedTextWriter.Write（EXECUTE（'ALTER TABLE）; 
 indentedTextWriter.Write（this.Name（dropColumnOperation.Table））; 
 indentedTextWriter.Write（DROP CONSTRAINT'+） ; 
 indentedTextWriter.Write（value）; 
 indentedTextWriter.WriteLine（））; 
 indentedTextWriter.Indent--; 
 indentedTextWriter.Write（ALTER TABLE）; 
 indentedTextWriter.Write（this.Name（dropColumnOperation.Table））; 
 indentedTextWriter.Write（DROP COLUMN）; 
 indentedTextWriter.Write（this.Quote（dropColumnOperation.Name））; 
 this.Statement（indentedTextWriter）; 
} 
} 
  
您可以看到它跟踪使用的变量名称，但这仅仅是在一个批次中保持跟踪，即单次迁移。因此，如果migratin包含多个 DROP COLUM ，以上工作正常，但如果有两个迁移导致 DROP COLUMN 被生成，然后重新设置 _variableCounter 变量。
 
 
 
在生成脚本时没有问题，因为每个语句都是立即对数据库执行的（我使用SQL Profiler检查）。
 
 
 
如果生成一个SQL脚本，并且要按原样运行， 
 
 
 
 解决方案 
 
 
 
我创建了一个新的继承自 SqlServerMigrationSqlGenerator 的BatchSqlServerMigrationSqlGenerator 如下（请注意，您需要使用System.Data.Entity.Migrations.Sql;  
 
  public class BatchSqlServerMigrationSqlGenerator：SqlServerMigrationSqlGenerator 
 {
 protected override void Generate 
（System.Data.Entity.Migrations.Model.DropColumnOperation dropColumnOperation）
 {
 base.Generate（dropColumnOperation）; 
 
声明（GO）; 
} 
} 
  
现在强制迁移使用您的自定义生成器有两个选项：
 
 
 
如果您希望将其集成到包管理器控制台中，请将以下行添加到配置 class：
  SetSqlGenerator（System.Data.SqlClient，
 new BatchSqlServerMigrationSqlGenerator（））; 
  
 
 
 
 
如果您从代码生成脚本（像我一样），添加一个类似的代码行代码到您的配置程序集中：
  migrationsConfiguration.SetSqlGenerator（DataProviderInvariantName，
 new BatchSqlServerMigrationSqlGenerator ））; 
  
 
 
 
 
Using Entity Framework 4.3.1 Code first, and Data Migrations.

I have written a utility to automatically generate the Migration scripts for a target database, using the MigratorScriptingDecorator.

However, sometimes when re-generating the target database from scratch, the generated script is invalid, in that it declares a variable with the same name twice.

The variable name is @var0.

This appears to happen when there are multiple migrations being applied, and when at least two result in a default constraint being dropped.

The problem occurs both when generating the script form code, and when using the Package Manager console command:
Update-Database -Script
Here are the offending snippets form the generated script:
DECLARE @var0 nvarchar(128)
SELECT @var0 = name
FROM sys.default_constraints
WHERE parent_object_id = object_id(N'SomeTableName')
and
DECLARE @var0 nvarchar(128)
SELECT @var0 = name
FROM sys.default_constraints
WHERE parent_object_id = object_id(N'SomeOtherTableName')
I would like to be able to override the point where it generates the SQL for each migration, and then add a "GO" statement so that each migration is in a separate batch, which would solve the problem.

Anyone have any ideas how to do this, or if I'm barking up the wrong tree then maybe you could suggest a better approach?
解决方案
So with extensive use of ILSpy and some pointers in the answer to this question I found a way.

Details below fo those interested.

Problem

The SqlServerMigrationSqlGenerator is the class ultimately responsible for creating the SQL statements that get executed against the target database or scripted out when using the -Script switch in the Package Manager console or when using the MigratorScriptingDecorator.

Workings

Examining the Genearate method in the SqlServerMigrationSqlGenerator which is responsible for a DROP COLUMN, it looks like this:
protected virtual void Generate(DropColumnOperation dropColumnOperation)
{
    RuntimeFailureMethods
        .Requires(dropColumnOperation != null, null, "dropColumnOperation != null");
    using (IndentedTextWriter indentedTextWriter = 
        SqlServerMigrationSqlGenerator.Writer())
    {
        string value = "@var" + this._variableCounter++;
        indentedTextWriter.Write("DECLARE ");
        indentedTextWriter.Write(value);
        indentedTextWriter.WriteLine(" nvarchar(128)");
        indentedTextWriter.Write("SELECT ");
        indentedTextWriter.Write(value);
        indentedTextWriter.WriteLine(" = name");
        indentedTextWriter.WriteLine("FROM sys.default_constraints");
        indentedTextWriter.Write("WHERE parent_object_id = object_id(N'");
        indentedTextWriter.Write(dropColumnOperation.Table);
        indentedTextWriter.WriteLine("')");
        indentedTextWriter.Write("AND col_name(parent_object_id, 
                                                       parent_column_id) = '");
        indentedTextWriter.Write(dropColumnOperation.Name);
        indentedTextWriter.WriteLine("';");
        indentedTextWriter.Write("IF ");
        indentedTextWriter.Write(value);
        indentedTextWriter.WriteLine(" IS NOT NULL");
        indentedTextWriter.Indent++;
        indentedTextWriter.Write("EXECUTE('ALTER TABLE ");
        indentedTextWriter.Write(this.Name(dropColumnOperation.Table));
        indentedTextWriter.Write(" DROP CONSTRAINT ' + ");
        indentedTextWriter.Write(value);
        indentedTextWriter.WriteLine(")");
        indentedTextWriter.Indent--;
        indentedTextWriter.Write("ALTER TABLE ");
        indentedTextWriter.Write(this.Name(dropColumnOperation.Table));
        indentedTextWriter.Write(" DROP COLUMN ");
        indentedTextWriter.Write(this.Quote(dropColumnOperation.Name));
        this.Statement(indentedTextWriter);
    }
}
You can see it keeps track of the variables names used, but this only appears to keep track within a batch, i.e. a single migration. So if a migratin contains more than one DROP COLUM the above works fine, but if there are two migrations which result in a DROP COLUMN being generated then the _variableCounter variable is reset.

No problems are experienced when not generating a script, as each statement is executed immediately against the database (I checked using SQL Profiler).

If you generate a SQL script and want to run it as-is though you have a problem.

Solution

I created a new BatchSqlServerMigrationSqlGenerator inheriting from SqlServerMigrationSqlGenerator as follows (note you need using System.Data.Entity.Migrations.Sql;):
public class BatchSqlServerMigrationSqlGenerator : SqlServerMigrationSqlGenerator
{
    protected override void Generate
       (System.Data.Entity.Migrations.Model.DropColumnOperation dropColumnOperation)
    {
        base.Generate(dropColumnOperation);

        Statement("GO");
    }
}
Now to force the migrations to use your custom generator you have two options:

If you want it to be integrated into the Package Manager console, add the below line to your Configuration class:
   SetSqlGenerator("System.Data.SqlClient", 
                   new BatchSqlServerMigrationSqlGenerator());

If you're generating the script from code (like I was), add a similar line of code to where you have your Configuration assembly in code:
migrationsConfiguration.SetSqlGenerator(DataProviderInvariantName, 
                   new BatchSqlServerMigrationSqlGenerator());



                        
这篇关于如何覆盖由MigratorScriptingDecorator生成的SQL脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！