AiDOPWarehouse/server/Admin.NET.Core/Update/AutoVersionUpdate.cs

// Admin.NET 项目的版权、商标、专利和其他相关权利均受相应法律法规的保护。使用本项目应遵守相关法律法规和许可证的要求。
//
// 本项目主要遵循 MIT 许可证和 Apache 许可证（版本 2.0）进行分发和使用。许可证位于源代码树根目录中的 LICENSE-MIT 和 LICENSE-APACHE 文件。
//
// 不得利用本项目从事危害国家安全、扰乱社会秩序、侵犯他人合法权益等法律法规禁止的活动！任何基于本项目二次开发而产生的一切法律纠纷和责任，我们不承担任何责任！

#if NET10_0_OR_GREATER

using System.Data;
using System.Diagnostics;
using System.Security.Cryptography;
using System.Text;
using System.Text.RegularExpressions;
using Microsoft.AspNetCore.Builder;
using XiHan.Framework.Utils.Logging;
using XiHan.Framework.Utils.Reflections;

namespace Admin.NET.Core.Update;

/// <summary>
/// 自动版本更新中间件拓展
/// </summary>
[SuppressSniffer]
public static class AutoVersionUpdate
{
    private const int MigrationCommandTimeoutSeconds = 600;
    private const string MigrationLogTable = "sys_db_migration_log";
    private const string StatusRunning = "Running";
    private const string StatusSuccess = "Success";
    private const string StatusFailed = "Failed";
    private const string StatusSkipped = "Skipped";
    private const string VerifyNotConfigured = "NotConfigured";
    private const string VerifySuccess = "Success";
    private const string VerifyFailed = "Failed";

    /// <summary>
    /// 使用自动版本更新中间件
    /// </summary>
    public static IApplicationBuilder UseAutoVersionUpdate(this IApplicationBuilder app)
    {
        LogHelper.Info("AutoVersionUpdate 中间件运行");

        var snowIdOpt = App.GetConfig<SnowIdOptions>("SnowId", true);
        if (snowIdOpt.WorkerId != 1)
        {
            LogHelper.Handle("非主节点，不执行脚本");
            return app;
        }

        var stopOnFailure = App.GetConfig<bool?>("AutoVersionUpdate:StopApplicationOnFailure") ?? true;

        try
        {
            RunPendingMigrations(app);
            LogHelper.Success("AutoVersionUpdate 中间件结束");
        }
        catch (Exception ex)
        {
            LogHelper.Error($"AutoVersionUpdate 执行失败：{ex.Message}");
            if (stopOnFailure)
                throw;
        }

        return app;
    }

    private static void RunPendingMigrations(IApplicationBuilder app)
    {
        using var scope = App.GetRequiredService<IServiceScopeFactory>().CreateScope();
        var db = scope.ServiceProvider.GetRequiredService<ISqlSugarClient>();

        EnsureMigrationLogTable(db);

        var currentVersionText = GetEntryAssemblyCurrentVersion();
        var currentVersion = ParseVersion(currentVersionText);
        var historyFromTxt = GetEntryAssemblyHistoryVersionInfo();
        var databaseName = GetDatabaseName(db);

        LogHelper.Handle($"当前版本：{currentVersionText}，目标数据库：{databaseName ?? "(unknown)"}");

        var scripts = LoadMigrationScripts();
        var migrationRows = LoadMigrationLogs(db);
        var successVersions = migrationRows
            .Where(x => string.Equals(x.Status, StatusSuccess, StringComparison.OrdinalIgnoreCase))
            .Select(x => ParseVersion(x.Version))
            .ToHashSet();

        Version? legacyBoundary = null;
        if (migrationRows.Count == 0 &&
            !string.IsNullOrWhiteSpace(historyFromTxt.Version) &&
            historyFromTxt.IsRunScript &&
            Version.TryParse(historyFromTxt.Version, out var legacyVersion))
        {
            legacyBoundary = legacyVersion;
            LogHelper.Handle(
                $"检测到 {MigrationLogTable} 为空但 version.txt 存在，legacy 跳过边界：{legacyBoundary}");
        }

        var pending = scripts
            .Where(s => s.ParsedVersion <= currentVersion)
            .Where(s => !ShouldSkipScript(s, migrationRows, legacyBoundary))
            .OrderBy(s => s.ParsedVersion)
            .ToList();

        LogHelper.Handle($"发现脚本 {scripts.Count} 个，已成功 {successVersions.Count} 个，待执行 {pending.Count} 个");

        if (pending.Count == 0)
        {
            SetEntryAssemblyCurrentVersion(currentVersionText, true);
            return;
        }

        foreach (var script in pending)
        {
            ExecuteOneMigrationScript(db, script, databaseName);
        }

        SetEntryAssemblyCurrentVersion(currentVersionText, true);
    }

    private static bool ShouldSkipScript(
        MigrationScript script,
        List<MigrationLogRow> migrationRows,
        Version? legacyBoundary)
    {
        var row = migrationRows.FirstOrDefault(x =>
            string.Equals(x.Version, script.Version, StringComparison.OrdinalIgnoreCase));

        if (row != null)
        {
            if (string.Equals(row.Status, StatusSuccess, StringComparison.OrdinalIgnoreCase))
            {
                if (!string.Equals(row.FileHash, script.Hash, StringComparison.OrdinalIgnoreCase))
                {
                    throw new InvalidOperationException(
                        $"版本脚本 {script.FileName} 的 SHA256 与已成功记录不一致，禁止静默覆盖。请新建更高版本脚本。");
                }

                LogHelper.Handle($"版本 {script.Version} 已成功且 hash 未变，跳过");
                return true;
            }

            return false;
        }

        if (legacyBoundary != null && script.ParsedVersion <= legacyBoundary)
        {
            LogHelper.Handle($"版本 {script.Version} 处于 legacy 边界内，跳过");
            return true;
        }

        return false;
    }

    private static void ExecuteOneMigrationScript(ISqlSugarClient db, MigrationScript script, string? databaseName)
    {
        var sql = File.ReadAllText(script.FilePath);
        if (string.IsNullOrWhiteSpace(sql))
        {
            LogHelper.Handle($"版本 {script.Version} 脚本为空，记录 Skipped");
            UpsertMigrationLog(db, script, databaseName, StatusSkipped, 0, 0, VerifyNotConfigured, null, 0);
            return;
        }

        if (SqlScriptSplitter.ContainsDelimiterDirective(sql))
        {
            throw new InvalidOperationException(
                $"版本脚本 {script.FileName} 包含 DELIMITER 指令，当前执行器不支持，请改为普通 SQL 或手工执行。");
        }

        var startedAt = DateTime.Now;
        var sw = Stopwatch.StartNew();
        UpsertMigrationLog(db, script, databaseName, StatusRunning, 0, 0, VerifyNotConfigured, null, 0);
        var originalCommandTimeout = db.Ado.CommandTimeOut;
        db.Ado.CommandTimeOut = Math.Max(originalCommandTimeout, MigrationCommandTimeoutSeconds);

        try
        {
            LogHelper.Handle($"执行版本 {script.Version} 脚本 {script.FileName}，SHA256={script.Hash}");

            var executeResult = SqlScriptSplitter.Execute(db, sql);
            var verifyStatus = RunVerifyScriptIfExists(db, script);

            sw.Stop();
            UpsertMigrationLog(
                db,
                script,
                databaseName,
                StatusSuccess,
                executeResult.StatementCount,
                executeResult.AffectedRows,
                verifyStatus,
                null,
                sw.ElapsedMilliseconds);

            LogHelper.Handle(
                $"版本 {script.Version} 成功：语句 {executeResult.StatementCount} 条，影响行 {executeResult.AffectedRows}，校验 {verifyStatus}，耗时 {sw.ElapsedMilliseconds}ms");
        }
        catch (Exception ex)
        {
            sw.Stop();
            var message = BuildExceptionMessage(ex);
            UpsertMigrationLog(
                db,
                script,
                databaseName,
                StatusFailed,
                0,
                0,
                VerifyFailed,
                message,
                sw.ElapsedMilliseconds);

            LogHelper.Error($"AutoVersionUpdate 版本 {script.Version} 失败：{message}");
            throw new InvalidOperationException($"AutoVersionUpdate 版本 {script.Version} 执行失败：{message}", ex);
        }
        finally
        {
            db.Ado.CommandTimeOut = originalCommandTimeout;
        }
    }

    private static string RunVerifyScriptIfExists(ISqlSugarClient db, MigrationScript script)
    {
        var verifyPath = Path.ChangeExtension(script.FilePath, ".verify.sql");
        if (!File.Exists(verifyPath))
            return VerifyNotConfigured;

        var verifySql = File.ReadAllText(verifyPath);
        var statements = SqlScriptSplitter.Split(verifySql);
        if (statements.Count == 0)
            return VerifyNotConfigured;

        for (var i = 0; i < statements.Count; i++)
        {
            var statement = statements[i];
            var result = db.Ado.SqlQuerySingle<object>(statement);
            if (!IsTruthy(result))
            {
                throw new InvalidOperationException(
                    $"校验 SQL 第 {i + 1} 条未通过：{TrimForLog(statement)}");
            }
        }

        return VerifySuccess;
    }

    private static bool IsTruthy(object? value)
    {
        if (value == null || value is DBNull)
            return false;

        return value switch
        {
            bool b => b,
            byte or sbyte or short or ushort or int or uint or long or ulong or float or double or decimal =>
                Convert.ToDecimal(value) != 0,
            string s => !string.IsNullOrWhiteSpace(s) &&
                          !string.Equals(s, "0", StringComparison.OrdinalIgnoreCase) &&
                          !string.Equals(s, "false", StringComparison.OrdinalIgnoreCase),
            _ => true
        };
    }

    private static void EnsureMigrationLogTable(ISqlSugarClient db)
    {
        db.Ado.ExecuteCommand(
            """
            CREATE TABLE IF NOT EXISTS sys_db_migration_log (
                id BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键',
                version VARCHAR(50) NOT NULL COMMENT '脚本版本',
                file_name VARCHAR(255) NOT NULL COMMENT '脚本文件名',
                file_hash VARCHAR(128) NOT NULL COMMENT '脚本 SHA256',
                status VARCHAR(20) NOT NULL COMMENT 'Running/Success/Failed/Skipped',
                started_at DATETIME(3) NOT NULL COMMENT '开始时间',
                finished_at DATETIME(3) NULL COMMENT '结束时间',
                elapsed_ms BIGINT NULL COMMENT '耗时毫秒',
                statement_count INT NOT NULL DEFAULT 0 COMMENT '执行语句数',
                affected_rows BIGINT NULL COMMENT '影响行数合计',
                verify_status VARCHAR(20) NULL COMMENT '校验状态',
                error_message LONGTEXT NULL COMMENT '错误信息',
                database_name VARCHAR(128) NULL COMMENT '执行数据库',
                created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
                updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
                PRIMARY KEY (id),
                UNIQUE KEY uk_sys_db_migration_log_version (version),
                KEY idx_sys_db_migration_log_status (status),
                KEY idx_sys_db_migration_log_started_at (started_at)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='数据库版本脚本执行记录'
            """);
    }

    private static List<MigrationLogRow> LoadMigrationLogs(ISqlSugarClient db)
    {
        try
        {
            return db.Ado.SqlQuery<MigrationLogRow>(
                $"""
                 SELECT version AS Version, file_hash AS FileHash, status AS Status
                 FROM {MigrationLogTable}
                 """);
        }
        catch
        {
            return [];
        }
    }

    private static void UpsertMigrationLog(
        ISqlSugarClient db,
        MigrationScript script,
        string? databaseName,
        string status,
        int statementCount,
        long affectedRows,
        string verifyStatus,
        string? errorMessage,
        long elapsedMs)
    {
        var now = DateTime.Now;
        var existing = db.Ado.SqlQuerySingle<int?>(
            $"SELECT id FROM {MigrationLogTable} WHERE version = @version LIMIT 1",
            new { version = script.Version });

        if (existing is > 0)
        {
            db.Ado.ExecuteCommand(
                $"""
                 UPDATE {MigrationLogTable}
                 SET file_name = @fileName,
                     file_hash = @fileHash,
                     status = @status,
                     started_at = CASE WHEN @status = '{StatusRunning}' THEN @startedAt ELSE started_at END,
                     finished_at = @finishedAt,
                     elapsed_ms = @elapsedMs,
                     statement_count = @statementCount,
                     affected_rows = @affectedRows,
                     verify_status = @verifyStatus,
                     error_message = @errorMessage,
                     database_name = @databaseName,
                     updated_at = @updatedAt
                 WHERE version = @version
                 """,
                new
                {
                    version = script.Version,
                    fileName = script.FileName,
                    fileHash = script.Hash,
                    status,
                    startedAt = now,
                    finishedAt = status is StatusRunning ? (DateTime?)null : now,
                    elapsedMs = status is StatusRunning ? (long?)null : elapsedMs,
                    statementCount,
                    affectedRows,
                    verifyStatus,
                    errorMessage,
                    databaseName,
                    updatedAt = now
                });
            return;
        }

        db.Ado.ExecuteCommand(
            $"""
             INSERT INTO {MigrationLogTable}
             (version, file_name, file_hash, status, started_at, finished_at, elapsed_ms,
              statement_count, affected_rows, verify_status, error_message, database_name)
             VALUES
             (@version, @fileName, @fileHash, @status, @startedAt, @finishedAt, @elapsedMs,
              @statementCount, @affectedRows, @verifyStatus, @errorMessage, @databaseName)
             """,
            new
            {
                version = script.Version,
                fileName = script.FileName,
                fileHash = script.Hash,
                status,
                startedAt = now,
                finishedAt = status is StatusRunning ? (DateTime?)null : now,
                elapsedMs = status is StatusRunning ? (long?)null : elapsedMs,
                statementCount,
                affectedRows,
                verifyStatus,
                errorMessage,
                databaseName
            });
    }

    private static List<MigrationScript> LoadMigrationScripts()
    {
        var path = Path.Combine(AppContext.BaseDirectory, "UpdateScripts");
        if (!Directory.Exists(path))
            return [];

        return Directory.GetFiles(path, "*.sql", SearchOption.TopDirectoryOnly)
            .Where(file =>
            {
                var name = Path.GetFileName(file);
                return !name.EndsWith(".verify.sql", StringComparison.OrdinalIgnoreCase);
            })
            .Select(file =>
            {
                var versionText = Path.GetFileNameWithoutExtension(file);
                if (!Version.TryParse(versionText, out var parsedVersion))
                    return null;

                return new MigrationScript(
                    versionText,
                    parsedVersion,
                    file,
                    Path.GetFileName(file),
                    ComputeSha256(file));
            })
            .Where(x => x != null)
            .Cast<MigrationScript>()
            .OrderBy(x => x.ParsedVersion)
            .ToList();
    }

    private static string GetEntryAssemblyCurrentVersion()
    {
        var entryAssemblyVersion = ReflectionHelper.GetEntryAssemblyVersion();
        return entryAssemblyVersion.ToString(3);
    }

    private static void SetEntryAssemblyCurrentVersion(string version, bool isRunScript)
    {
        var path = Path.Combine(AppContext.BaseDirectory, "version.txt");
        var now = DateTime.Now;
        File.WriteAllText(path, $"{version}^{now:yyyy-MM-dd HH:mm:ss}^{isRunScript}");
    }

    private static HistoryVersionInfo GetEntryAssemblyHistoryVersionInfo()
    {
        var path = Path.Combine(AppContext.BaseDirectory, "version.txt");
        if (!File.Exists(path))
            return new HistoryVersionInfo(string.Empty, string.Empty, false);

        var info = File.ReadAllText(path);
        if (!info.Contains('^'))
            return new HistoryVersionInfo(string.Empty, string.Empty, false);

        var parts = info.Split('^');
        var version = parts.Length > 0 ? parts[0] : string.Empty;
        var date = parts.Length > 1 ? parts[1] : string.Empty;
        var isRunScript = parts.Length > 2 && parts[2].ToBoolean();
        return new HistoryVersionInfo(version, date, isRunScript);
    }

    private static Version ParseVersion(string value)
    {
        if (!Version.TryParse(value, out var version))
            throw new InvalidOperationException($"非法版本号：{value}");
        return version;
    }

    private static string ComputeSha256(string filePath)
    {
        using var sha = SHA256.Create();
        using var stream = File.OpenRead(filePath);
        return Convert.ToHexString(sha.ComputeHash(stream));
    }

    private static string? GetDatabaseName(ISqlSugarClient db)
    {
        try
        {
            return db.Ado.GetString("SELECT DATABASE()");
        }
        catch
        {
            return null;
        }
    }

    private static string BuildExceptionMessage(Exception ex)
    {
        var messages = new List<string>();
        for (var current = ex; current != null; current = current.InnerException)
            messages.Add(current.Message);
        return string.Join(" | ", messages);
    }

    private static string TrimForLog(string sql, int maxLength = 300)
    {
        var normalized = sql.Replace('\r', ' ').Replace('\n', ' ').Trim();
        return normalized.Length <= maxLength ? normalized : normalized[..maxLength] + "...";
    }

    private sealed record MigrationScript(
        string Version,
        Version ParsedVersion,
        string FilePath,
        string FileName,
        string Hash);

    private sealed class MigrationLogRow
    {
        public string Version { get; set; } = string.Empty;
        public string FileHash { get; set; } = string.Empty;
        public string Status { get; set; } = string.Empty;
    }

    private sealed record ScriptExecuteResult(int StatementCount, long AffectedRows);

    private static class SqlScriptSplitter
    {
        public static bool ContainsDelimiterDirective(string sql) =>
            sql.Contains("DELIMITER", StringComparison.OrdinalIgnoreCase);

        public static ScriptExecuteResult Execute(ISqlSugarClient db, string sql)
            // SET @var / PREPARE / EXECUTE 依赖同一连接，但不需要作为一个超长批次提交。
            // 分句执行既保留会话变量，又让超时和失败定位落到单条 DDL。
            => ExecuteSplitWithSharedConnection(db, sql);

        private static ScriptExecuteResult ExecuteSplitWithSharedConnection(ISqlSugarClient db, string sql)
        {
            var statements = Split(sql);
            long affectedRows = 0;
            var wasOpen = EnsureConnectionOpen(db);

            try
            {
                for (var i = 0; i < statements.Count; i++)
                {
                    var statement = statements[i];
                    try
                    {
                        using var command = db.Ado.Connection.CreateCommand();
                        command.CommandText = statement;
                        command.CommandTimeout = db.Ado.CommandTimeOut;
                        affectedRows += Math.Max(0, command.ExecuteNonQuery());
                    }
                    catch (Exception ex)
                    {
                        throw new InvalidOperationException(
                            $"第 {i + 1}/{statements.Count} 条语句失败：{TrimForLog(statement)} | {ex.Message}", ex);
                    }
                }

                return new ScriptExecuteResult(statements.Count, affectedRows);
            }
            finally
            {
                RestoreConnection(db, wasOpen);
            }
        }

        private static bool EnsureConnectionOpen(ISqlSugarClient db)
        {
            var connection = db.Ado.Connection;
            if (connection.State == ConnectionState.Open)
                return true;

            connection.Open();
            return false;
        }

        private static void RestoreConnection(ISqlSugarClient db, bool wasOpen)
        {
            if (wasOpen)
                return;

            var connection = db.Ado.Connection;
            if (connection.State == ConnectionState.Open)
                connection.Close();
        }

        public static List<string> Split(string sql)
        {
            var statements = new List<string>();
            if (string.IsNullOrWhiteSpace(sql))
                return statements;

            var current = new StringBuilder();
            var inSingleQuote = false;
            var inDoubleQuote = false;
            var inBacktick = false;
            var inLineComment = false;
            var inBlockComment = false;

            for (var i = 0; i < sql.Length; i++)
            {
                var ch = sql[i];
                var next = i + 1 < sql.Length ? sql[i + 1] : '\0';

                if (inLineComment)
                {
                    current.Append(ch);
                    if (ch is '\n' or '\r')
                        inLineComment = false;
                    continue;
                }

                if (inBlockComment)
                {
                    current.Append(ch);
                    if (ch == '*' && next == '/')
                    {
                        current.Append(next);
                        i++;
                        inBlockComment = false;
                    }

                    continue;
                }

                if (!inSingleQuote && !inDoubleQuote && !inBacktick)
                {
                    if (ch == '-' && next == '-')
                    {
                        inLineComment = true;
                        current.Append(ch);
                        continue;
                    }

                    if (ch == '#')
                    {
                        inLineComment = true;
                        current.Append(ch);
                        continue;
                    }

                    if (ch == '/' && next == '*')
                    {
                        inBlockComment = true;
                        current.Append(ch);
                        continue;
                    }
                }

                if (!inDoubleQuote && !inBacktick && ch == '\'' && !inSingleQuote)
                {
                    inSingleQuote = true;
                    current.Append(ch);
                    continue;
                }

                if (inSingleQuote)
                {
                    current.Append(ch);
                    if (ch == '\'' && next == '\'')
                    {
                        current.Append(next);
                        i++;
                        continue;
                    }

                    if (ch == '\\' && next != '\0')
                    {
                        current.Append(next);
                        i++;
                        continue;
                    }

                    if (ch == '\'')
                        inSingleQuote = false;
                    continue;
                }

                if (!inSingleQuote && !inBacktick && ch == '"' && !inDoubleQuote)
                {
                    inDoubleQuote = true;
                    current.Append(ch);
                    continue;
                }

                if (inDoubleQuote)
                {
                    current.Append(ch);
                    if (ch == '"' && next == '"')
                    {
                        current.Append(next);
                        i++;
                        continue;
                    }

                    if (ch == '\\' && next != '\0')
                    {
                        current.Append(next);
                        i++;
                        continue;
                    }

                    if (ch == '"')
                        inDoubleQuote = false;
                    continue;
                }

                if (!inSingleQuote && !inDoubleQuote && ch == '`' && !inBacktick)
                {
                    inBacktick = true;
                    current.Append(ch);
                    continue;
                }

                if (inBacktick)
                {
                    current.Append(ch);
                    if (ch == '`')
                        inBacktick = false;
                    continue;
                }

                if (ch == ';')
                {
                    AppendStatement(statements, current);
                    continue;
                }

                current.Append(ch);
            }

            AppendStatement(statements, current);
            return statements;
        }

        private static void AppendStatement(List<string> statements, StringBuilder current)
        {
            var text = current.ToString().Trim();
            current.Clear();
            if (string.IsNullOrWhiteSpace(text))
                return;

            statements.Add(text);
        }
    }
}

public record HistoryVersionInfo(string Version, string Date, bool IsRunScript);

#endif // NET10_0_OR_GREATER