EntityFrameworkCore 模型自動更新（下）

話題

上一篇我們討論到獲取將要執行的遷移操作，到這一步為止，針對所有資料庫都通用，在此之後需要生成SQL指令碼對於不同資料庫將有不同差異，我們一起來瞅一瞅

SQLite指令碼生成差異

在上一篇拿到的遷移操作類即MigrationOperation為執行所有其他操作類的父類別，比如新增列操作（AddColumnOperation），修改列操作（AlterColumnOperation）、建立表操作（CreateTableOperation）等等，我們知道SQLite不支援修改列，所以我們需要去除列修改操作，程式碼如下：

// Sqlite不支援修改操作，所以需過濾修改遷移操作
var operations = migrationOperations.Except(migrationOperations.Where(o => o is AlterColumnOperation)).ToList();

if (!operations.Any())
{
    return;
}

然後獲取生成SQL指令碼介面，拿到執行操作命令類裡面的指令碼文字即可

var migrationsSqlGenerator = context.GetService<IMigrationsSqlGenerator>();

var commandList = migrationsSqlGenerator.Generate(operations);

if (!commandList.Any())
{
    return;
}

var sqlScript = string.Concat(commandList.Select(c => c.CommandText));

if (string.IsNullOrEmpty(sqlScript))
{
    return;
}

MySQL指令碼生成差異

因為我們可能會修改主鍵，此時Pomelo.EntityFrameworkCore.MySql使用的方式則是建立一個臨時儲存過程，先刪除主鍵，然後則執行完相關指令碼後，最後重建主鍵，然後刪除臨時儲存過程，臨時儲存過程如下：

#region Custom Sql
        #region BeforeDropPrimaryKey

        private const string BeforeDropPrimaryKeyMigrationBegin = @"DROP PROCEDURE IF EXISTS `POMELO_BEFORE_DROP_PRIMARY_KEY`;
CREATE PROCEDURE `POMELO_BEFORE_DROP_PRIMARY_KEY`(IN `SCHEMA_NAME_ARGUMENT` VARCHAR(255), IN `TABLE_NAME_ARGUMENT` VARCHAR(255))
BEGIN
    DECLARE HAS_AUTO_INCREMENT_ID TINYINT(1);
    DECLARE PRIMARY_KEY_COLUMN_NAME VARCHAR(255);
    DECLARE PRIMARY_KEY_TYPE VARCHAR(255);
    DECLARE SQL_EXP VARCHAR(1000);
    SELECT COUNT(*)
        INTO HAS_AUTO_INCREMENT_ID
        FROM `information_schema`.`COLUMNS`
        WHERE `TABLE_SCHEMA` = (SELECT IFNULL(SCHEMA_NAME_ARGUMENT, SCHEMA()))
            AND `TABLE_NAME` = TABLE_NAME_ARGUMENT
            AND `Extra` = 'auto_increment'
            AND `COLUMN_KEY` = 'PRI'
            LIMIT 1;
    IF HAS_AUTO_INCREMENT_ID THEN
        SELECT `COLUMN_TYPE`
            INTO PRIMARY_KEY_TYPE
            FROM `information_schema`.`COLUMNS`
            WHERE `TABLE_SCHEMA` = (SELECT IFNULL(SCHEMA_NAME_ARGUMENT, SCHEMA()))
                AND `TABLE_NAME` = TABLE_NAME_ARGUMENT
                AND `COLUMN_KEY` = 'PRI'
            LIMIT 1;
        SELECT `COLUMN_NAME`
            INTO PRIMARY_KEY_COLUMN_NAME
            FROM `information_schema`.`COLUMNS`
            WHERE `TABLE_SCHEMA` = (SELECT IFNULL(SCHEMA_NAME_ARGUMENT, SCHEMA()))
                AND `TABLE_NAME` = TABLE_NAME_ARGUMENT
                AND `COLUMN_KEY` = 'PRI'
            LIMIT 1;
        SET SQL_EXP = CONCAT('ALTER TABLE `', (SELECT IFNULL(SCHEMA_NAME_ARGUMENT, SCHEMA())), '`.`', TABLE_NAME_ARGUMENT, '` MODIFY COLUMN `', PRIMARY_KEY_COLUMN_NAME, '` ', PRIMARY_KEY_TYPE, ' NOT NULL;');
        SET @SQL_EXP = SQL_EXP;
        PREPARE SQL_EXP_EXECUTE FROM @SQL_EXP;
        EXECUTE SQL_EXP_EXECUTE;
        DEALLOCATE PREPARE SQL_EXP_EXECUTE;
    END IF;
END;";

        private const string BeforeDropPrimaryKeyMigrationEnd = @"DROP PROCEDURE `POMELO_BEFORE_DROP_PRIMARY_KEY`;";

        #endregion BeforeDropPrimaryKey

        #region AfterAddPrimaryKey

        private const string AfterAddPrimaryKeyMigrationBegin = @"DROP PROCEDURE IF EXISTS `POMELO_AFTER_ADD_PRIMARY_KEY`;
CREATE PROCEDURE `POMELO_AFTER_ADD_PRIMARY_KEY`(IN `SCHEMA_NAME_ARGUMENT` VARCHAR(255), IN `TABLE_NAME_ARGUMENT` VARCHAR(255), IN `COLUMN_NAME_ARGUMENT` VARCHAR(255))
BEGIN
    DECLARE HAS_AUTO_INCREMENT_ID INT(11);
    DECLARE PRIMARY_KEY_COLUMN_NAME VARCHAR(255);
    DECLARE PRIMARY_KEY_TYPE VARCHAR(255);
    DECLARE SQL_EXP VARCHAR(1000);
    SELECT COUNT(*)
        INTO HAS_AUTO_INCREMENT_ID
        FROM `information_schema`.`COLUMNS`
        WHERE `TABLE_SCHEMA` = (SELECT IFNULL(SCHEMA_NAME_ARGUMENT, SCHEMA()))
            AND `TABLE_NAME` = TABLE_NAME_ARGUMENT
            AND `COLUMN_NAME` = COLUMN_NAME_ARGUMENT
            AND `COLUMN_TYPE` LIKE '%int%'
            AND `COLUMN_KEY` = 'PRI';
    IF HAS_AUTO_INCREMENT_ID THEN
        SELECT `COLUMN_TYPE`
            INTO PRIMARY_KEY_TYPE
            FROM `information_schema`.`COLUMNS`
            WHERE `TABLE_SCHEMA` = (SELECT IFNULL(SCHEMA_NAME_ARGUMENT, SCHEMA()))
                AND `TABLE_NAME` = TABLE_NAME_ARGUMENT
                AND `COLUMN_NAME` = COLUMN_NAME_ARGUMENT
                AND `COLUMN_TYPE` LIKE '%int%'
                AND `COLUMN_KEY` = 'PRI';
        SELECT `COLUMN_NAME`
            INTO PRIMARY_KEY_COLUMN_NAME
            FROM `information_schema`.`COLUMNS`
            WHERE `TABLE_SCHEMA` = (SELECT IFNULL(SCHEMA_NAME_ARGUMENT, SCHEMA()))
                AND `TABLE_NAME` = TABLE_NAME_ARGUMENT
                AND `COLUMN_NAME` = COLUMN_NAME_ARGUMENT
                AND `COLUMN_TYPE` LIKE '%int%'
                AND `COLUMN_KEY` = 'PRI';
        SET SQL_EXP = CONCAT('ALTER TABLE `', (SELECT IFNULL(SCHEMA_NAME_ARGUMENT, SCHEMA())), '`.`', TABLE_NAME_ARGUMENT, '` MODIFY COLUMN `', PRIMARY_KEY_COLUMN_NAME, '` ', PRIMARY_KEY_TYPE, ' NOT NULL AUTO_INCREMENT;');
        SET @SQL_EXP = SQL_EXP;
        PREPARE SQL_EXP_EXECUTE FROM @SQL_EXP;
        EXECUTE SQL_EXP_EXECUTE;
        DEALLOCATE PREPARE SQL_EXP_EXECUTE;
    END IF;
END;";

        private const string AfterAddPrimaryKeyMigrationEnd = @"DROP PROCEDURE `POMELO_AFTER_ADD_PRIMARY_KEY`;";

        #endregion AfterAddPrimaryKey 
#endregion

我想大部分童鞋使用MySQL時，基本沒遷移過，在實際遷移時會可能會丟擲如下異常

Incorrect table definition; there can be only one auto column and it must be defined as a key

此問題一直遺留至今並未得到很好的解決，見連結《https://github.com/PomeloFoundation/Pomelo.EntityFrameworkCore.MySql/issues/711》，根本問題在於主鍵唯一約束問題，所以我們在得到指令碼文字後，進行如下操作變換即可

var migrationsSqlGenerator = context.GetService<IMigrationsSqlGenerator>();

var commandList = migrationsSqlGenerator.Generate(migrationOperations);

if (!commandList.Any())
{
    return;
}

var sqlScript = string.Concat(commandList.Select(c => c.CommandText));

if (string.IsNullOrEmpty(sqlScript))
{
    return;
}

var builder = new StringBuilder();

builder.AppendJoin(string.Empty, GetMigrationCommandTexts(migrationOperations, true));
builder.Append(sqlScript);
builder.AppendJoin(string.Empty, GetMigrationCommandTexts(migrationOperations, false));

var sql = builder.ToString();
sql = sql.Replace("AUTO_INCREMENT", "AUTO_INCREMENT UNIQUE");

PostgreSQL指令碼生成差異

要操作PG資料庫，我們基本都使用Npgsql.EntityFrameworkCore.PostgreSQL來進行，在查詢獲取資料庫模型時基本也會丟擲如下異常

Cannot parse collation name from annotation: pg_catalog.C.UTF-8

PG資料庫基於架構(schema)和排序規則(collation)，但在Npg中還不能很好支援，比如PG資料庫存在如下架構和排序規則

直到Npg EF Core 7預覽版該問題仍未得到解決，作為遺留問題一直存在，當由第一列（schema）和第二列（collation）查詢以點組合，在校驗時以點分隔，陣列長度超過3位，必定丟擲異常，所以目前排序規則僅支援default和ci_x_icu，原始碼如下：

// TODO: This would be a safer operation if we stored schema and name in the annotation value (see Sequence.cs).
// Yes, this doesn't support dots in the schema/collation name, let somebody complain first.
var schemaAndName = annotation.Name.Substring(KdbndpAnnotationNames.CollationDefinitionPrefix.Length).Split('.');
switch (schemaAndName.Length)
{
case 1:
    return (null, schemaAndName[0], elements[0], elements[1], elements[2], isDeterministic);
case 2:
    return (schemaAndName[0], schemaAndName[1], elements[0], elements[1], elements[2], isDeterministic);
default:
    throw new ArgumentException($"Cannot parse collation name from annotation: {annotation.Name}");
}

其他細節考慮

我們知道不同資料庫肯定各有差異，差異性主要體現在兩點上，其一有大小寫區分，比如SQL Server並不區分，而MySQL雖區分但可以在組態檔中設定，人大金倉也好，高斯資料庫也好，底層都是基於PG，所以都區分大小寫，同時二者在部署時就需明確是否區分大小寫，而且對於日期型別還存在時區問題，其二，不同資料庫列型別不一樣，比如SQLite僅有INTEGER和TEXT等型別，而SQL Server有NVARCHAR和VARCHAR，但PG資料庫僅有VARCHAR，若我們對模型列型別以及長度等等不能有統一規範，那麼完全通過程式碼遷移勢必會帶來一個問題，那就是每次都可能會得出遷移差異。比如我們使用SQL Server資料庫，模型如下：

[Table("test1")]
public class Test
{
    [Column("id")]
    public int Id { get; set; }
    [Column("name")]
    public string Name { get; set; }
}

我們對屬性Name型別和長度並未做任何處理，若我們在實際開發過程中，在資料庫中將該列型別修改為VARCHAR(30)，我們知道EF Core通過命令遷移生成資料庫模型時，該列將使用預設約定即對映為NVARCHAR(MAX)，通過程式碼生成的遷移指令碼文字則為如下

DECLARE @var0 sysname;
SELECT @var0 = [d].[name]
FROM [sys].[default_constraints] [d]
INNER JOIN [sys].[columns] [c] ON [d].[parent_column_id] = [c].[column_id] AND [d].[parent_object_id] = [c].[object_id]
WHERE ([d].[parent_object_id] = OBJECT_ID(N'[test1]') AND [c].[name] = N'name');
IF @var0 IS NOT NULL EXEC(N'ALTER TABLE [test1] DROP CONSTRAINT [' + @var0 + '];');
ALTER TABLE [test1] ALTER COLUMN [name] nvarchar(max) NULL;
DECLARE @var1 sysname;
SELECT @var1 = [d].[name]
FROM [sys].[default_constraints] [d]
INNER JOIN [sys].[columns] [c] ON [d].[parent_column_id] = [c].[column_id] AND [d].[parent_object_id] = [c].[object_id]
WHERE ([d].[parent_object_id] = OBJECT_ID(N'[test1]') AND [c].[name] = N'id');
IF @var1 IS NOT NULL EXEC(N'ALTER TABLE [test1] DROP CONSTRAINT [' + @var1 + '];');
ALTER TABLE [test1] ALTER COLUMN [id] int NOT NULL;

所以基於完全通過程式碼而非命令遷移，前提應該是針對不同資料庫定義屬於對應資料庫支援的列型別格式，如此這般才能避免每次都可能會生成差異性遷移指令碼文字從而執行，比如字串型別可能為中文，此時對於SQL Server就定義為NVARCHAR或其他，而SQLite為TEXT，PG資料庫則是VARCHAR或其他，最後再來一下

if (context.Database.IsSqlite())
{
    ioTMigrationFactory = new IoTSqlliteMigrationFactory();
}
else if (context.Database.IsSqlServer())
{
    ioTMigrationFactory = new IoTSqlServerMigrationFactory();
}
else if (context.Database.IsMySql())
{
    ioTMigrationFactory = new IoTMySqlMigrationFactory();
}
else if (context.Database.IsNpgsql())
{
    ioTMigrationFactory = new IoTPostgreSQLMigrationFactory();
}
else if (context.Database.IsKdbndp())
{
    ioTMigrationFactory = new IoTKdbndpMigrationFactory();
}

if (ioTMigrationFactory == default(IIoTMigrationFactory))
{
    return;
}

總結

本文我們重點介紹如何生成指令碼文字以及對於不同資料庫需要進行對應邏輯處理存在的差異性，以及想完全通過程式碼而非命令執行遷移所需要遵循對於不同資料庫設定不同列型別規範，避免每次都會進行差異性指令碼執行，尤其是涉及開發人員手動更改列型別，帶來腳文字執行自動覆蓋的問題