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

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

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

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

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

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


 

上述两篇文章，

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

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

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

首先是SqlGenerator：

/// <summary>
/// 拓展迁移操作：增加数据表和列备注
/// </summary>
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);}/// <summary>/// 创建表注释/// </summary>/// <param name="operation"></param>/// <param name="builder"></param>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();}/// <summary>/// 创建列注释/// </summary>/// <param name="operation"></param>/// <param name="builder"></param>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中，就是直接做替换，我考虑还是做了一个静态字典对象，把表名和类名做了一个映射，具体如下：

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

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

 builder.Entity<Company>(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<XiuYuanDbContext>().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<xxxDbContext>().UseMySql(connection, ServerVersion.AutoDetect(connection), o => o.SchemaBehavior(MySqlSchemaBehavior.Ignore))// 注意这里的ReplaceService.ReplaceService<IMigrationsSqlGenerator, MyMigrationsSqlGenerator>();return new xxxDbContext(builder.Options);
}private static IConfigurationRoot BuildConfiguration()
{var builder = new ConfigurationBuilder().AddJsonFile("appsettings.json", optional: false);return builder.Build();
}

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