Day 2 Abp框架下，MySQL数据迁移时，添加表和字段注释
后端采用Abp框架，当前最新版本是7.4.0。
数据库使用MySQL，在执行数据库迁移时，写在Domain层的Entity类上的注释通通都没有，这样查看数据库字段的含义时，就需要对照代码来看，有些不方便。今天专门来解决这个问题。
还是一顿搜索，发现了两个方案：
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即可。