Day 2 Abp框架下，MySQL数据迁移时，添加表和字段注释

后端采用Abp框架，当前最新版本是7.4.0。

数据库使用MySQL，在执行数据库迁移时，写在Domain层的Entity类上的注释通通都没有，这样查看数据库字段的含义时，就需要对照代码来看，有些不方便。今天专门来解决这个问题。

还是一顿搜索，发现了两个方案：

abp 框架拓展mysql 迁移：增加数据库表和列备注

EFcore+MySql 数据迁移的时候，怎么给表结构加注释？


 

上述两篇文章，

第一篇重载 MySqlMigrationsSqlGenerator 来实现加注释，但是字段注释是通过Description属性来获取的，这样字段上就要注释和Description重复写两遍。

第二篇直接通过工具，读取xml文档，生成 HasComment相关代码，每次都需要手动修改DbContext代码。

都不是特别完美，所以结合一下看看。具体方案还是重载MySqlMigrationsSqlGenerator，但是通过读取xml来获取信息。

首先是SqlGenerator：

/// 
/// 拓展迁移操作：增加数据表和列备注
/// 
public class MyMigrationsSqlGenerator : MySqlMigrationsSqlGenerator
{
 
    public MyMigrationsSqlGenerator(
        MigrationsSqlGeneratorDependencies dependencies,
        IMigrationsAnnotationProvider migrationsAnnotations,
        ICommandBatchPreparer commandBatchPreparer,
        IMySqlOptions mySqlOptions)
        : base(dependencies, commandBatchPreparer, mySqlOptions)
    {
    }
 
    protected override void Generate(MigrationOperation operation, IModel model, MigrationCommandListBuilder builder)
    {
        base.Generate(operation, model, builder);
 
        if (operation is CreateTableOperation || operation is AlterTableOperation)
            CreateTableComment(operation, model, builder);
 
        if (operation is AddColumnOperation || operation is AlterColumnOperation)
            CreateColumnComment(operation, model, builder);
    }
 
    /// 
    /// 创建表注释
    /// 
    /// 
    /// 
    private void CreateTableComment(MigrationOperation operation, IModel model, MigrationCommandListBuilder builder)
    {
        string tableName = string.Empty;
        string description = string.Empty;
        if (operation is AlterTableOperation)
        {
            var t = operation as AlterColumnOperation;
            tableName = (operation as AlterTableOperation).Name;
        }
 
        if (operation is CreateTableOperation)
        {
            var t = operation as CreateTableOperation;
            var addColumnsOperation = t.Columns;
            tableName = t.Name;
 
            foreach (var item in addColumnsOperation)
            {
                CreateColumnComment(item, model, builder);
            }
        }
 
        //description = DbDescriptionHelper.GetDescription(tableName.Replace(jingdianConsts.DbTablePrefix, ""));
        description = GetDescription(tableName, null);
 
        if (tableName.IsNullOrWhiteSpace())
            throw new Exception("表名为空引起添加表注释异常.");
 
        var sqlHelper = Dependencies.SqlGenerationHelper;
        builder
        .Append("ALTER TABLE ")
        .Append(sqlHelper.DelimitIdentifier(tableName))
        .Append(" COMMENT ")
        .Append("'")
        .Append(description)
        .Append("'")
        .AppendLine(sqlHelper.StatementTerminator)
        .EndCommand();
    }
 
    /// 
    /// 创建列注释
    /// 
    /// 
    /// 
    private void CreateColumnComment(MigrationOperation operation, IModel model, MigrationCommandListBuilder builder)
    {
        //alter table a1log modify column UUID VARCHAR(26) comment '修改后的字段注释';
        string tableName = string.Empty;
        string columnName = string.Empty;
        string columnType = string.Empty;
        string description = string.Empty;
        if (operation is AlterColumnOperation)
        {
            var t = (operation as AlterColumnOperation);
            columnType = t.ColumnType;
        }
 
        if (operation is AddColumnOperation)
        {
            var t = (operation as AddColumnOperation);
            columnType = t.ColumnType;
            description = GetDescription(tableName, columnName);
        }
 
        if (columnName.IsNullOrWhiteSpace() || tableName.IsNullOrWhiteSpace() || columnType.IsNullOrWhiteSpace())
            throw new Exception("列名为空或表名为空或列类型为空引起添加列注释异常." + columnName + "/" + tableName + "/" + columnType);
 
        var sqlHelper = Dependencies.SqlGenerationHelper;
        builder
        .Append("ALTER TABLE ")
        .Append(sqlHelper.DelimitIdentifier(tableName))
        .Append(" MODIFY COLUMN ")
        .Append(columnName)
        .Append(" ")
        .Append(columnType)
        .Append(" COMMENT ")
        .Append("'")
        .Append(description)
        .Append("'")
        .AppendLine(sqlHelper.StatementTerminator)
        .EndCommand();
    }
 
    private string GetDescription(string tableName, string? columnName)
    {
        var type = TableCommentRegister.Types[tableName];
        if (type == null)
        {
            return string.Empty;
        }
        
        string xmlPath = type.Module.Name.Replace("dll","xml");
        XmlDocument xml = new XmlDocument();
        xml.Load(xmlPath);
 
        var classNode = xml.SelectSingleNode(($"//member[@name='T:{type.FullName}']"));
        if (classNode == null)
        {
            return string.Empty;
        }
 
        if (columnName == null)
        {
            return classNode.InnerText.Trim();
        }
        else
        {
            var propertyNode = xml.SelectSingleNode(($"//member[@name='P:{type.FullName}.{columnName}']"));
            if (propertyNode == null)
            {
                return string.Empty;
            }
 
            return propertyNode.InnerText.Trim();
        }
    }
}

这里面有一个点，生成Sql时，只知道table name和column name，一般情况下列名就是属性名，可以不考虑，但是表名和类名可能会有差异，比如前后缀之类的。参考1中，就是直接做替换，我考虑还是做了一个静态字典对象，把表名和类名做了一个映射，具体如下：

/// 
/// 数据表注册器
/// 
public static class TableCommentRegister
{
    public static Dictionary<string, Type> Types { get; set; } = new Dictionary<string, Type>();
    /// 
    /// 配置实体对应的数据表，同时注册类型，用于后续生成备注
    /// 
    /// 实体类型
    /// 
    /// 数据表名
    public static void ToTableWithComment<T>(this EntityTypeBuilder builder, string tableName) where T : class
    {
        builder.ToTable(tableName);
        Types.TryAdd(tableName, typeof(T));
    }
}

然后在DbContext类中，针对表的处理代码如下：

 builder.Entity(b =>
 {
     string tableName = Consts.DbTablePrefix + "Company";
     b.ToTableWithComment(tableName);
     b.ConfigureByConvention(); //auto configure for the base class props
 });

就是把之前的 ToTable 改成 ToTableWithComment 就可以了。

最后，需要修改DbSchemaMigrator类，把SqlGenerator注册进去。这里我就简单粗暴的复制了一下DbContextFactory类。因为DbContextFactory代码注释则表明了其只是用于EF Core console commands，在Abp的DbMigrator程序中不起作用。

DbSchemaMigrator类中，Abp 脚手架代码应该是这样的：

public async Task MigrateAsync()
{
    /* We intentionally resolving the XiuYuanDbContext
     * from IServiceProvider (instead of directly injecting it)
     * to properly get the connection string of the current tenant in the
     * current scope.
     */
 
    await _serviceProvider
        .GetRequiredService()
        .Database
        .MigrateAsync();
}

修改如下：

public async Task MigrateAsync()
{    
    await CreateDbContext()            
        .Database
        .MigrateAsync();
}
 
public xxxDbContext CreateDbContext()
{
    xxxEfCoreEntityExtensionMappings.Configure();
 
    var configuration = BuildConfiguration();
    var connection = configuration.GetConnectionString(xxxConsts.DbSchema);
    var builder = new DbContextOptionsBuilder()
        .UseMySql(connection, ServerVersion.AutoDetect(connection), o => o.SchemaBehavior(MySqlSchemaBehavior.Ignore))
        // 注意这里的ReplaceService
        .ReplaceService();
 
    return new xxxDbContext(builder.Options);
}
 
 
private static IConfigurationRoot BuildConfiguration()
{
    var builder = new ConfigurationBuilder()
        .AddJsonFile("appsettings.json", optional: false);
 
    return builder.Build();
}

至此，所有基础性工作都完成了，后面再添加领域模型时，记得把ToTable改成ToTableWithComment即可。