114 lines
7.8 KiB
C#
114 lines
7.8 KiB
C#
using System.Text.Json;
|
|
using Store.Modules.CloudProduct.Entities;
|
|
|
|
// 使用固定时间验证过期边界，无需等待或连接数据库。
|
|
var now = DateTime.UtcNow;
|
|
var cache = new ImageAnalysisCache { UpdatedAt = now, RecognitionResult = "[]" };
|
|
foreach (var days in new[] { 1, 2, 4, 8, 16, 16 })
|
|
{
|
|
cache.RecordExpiration(true, now);
|
|
if (cache.ExpiresAt != days * 24
|
|
|| cache.ReadFreshResult(now.AddDays(days).AddTicks(-1)) is not { Length: 0 }
|
|
|| cache.ReadFreshResult(now.AddDays(days)) != null)
|
|
throw new Exception("连续空识别结果的缓存期限错误。");
|
|
now = now.AddDays(days);
|
|
}
|
|
cache.RecognitionResult = """["文字"]""";
|
|
cache.RecordExpiration(false, now);
|
|
if (cache.ExpiresAt != 720
|
|
|| cache.ReadFreshResult(now.AddDays(30).AddTicks(-1)) is not ["文字"]
|
|
|| cache.ReadFreshResult(now.AddDays(30)) != null)
|
|
throw new Exception("非空识别结果应重置级别并缓存三十天。");
|
|
cache.RecognitionResult = "{}";
|
|
if (cache.ReadFreshResult(now) != null)
|
|
throw new Exception("旧对象格式应立即重新识别。");
|
|
cache.RecordExpiration(true, now.AddDays(30));
|
|
if (cache.ExpiresAt != 24)
|
|
throw new Exception("非空结果后的首次空结果应重新从一天开始。");
|
|
if (args.Contains("--cache-policy"))
|
|
{
|
|
Console.WriteLine("PASS cache expiration checks");
|
|
return;
|
|
}
|
|
|
|
using var config = JsonDocument.Parse(File.ReadAllText("Plugins/CloudProduct/appsettings.json"), new JsonDocumentOptions { CommentHandling = JsonCommentHandling.Skip, AllowTrailingCommas = true });
|
|
const string tableQuery = "SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE() ORDER BY TABLE_NAME";
|
|
const string columnsQuery = "SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'image_analysis_cache' ORDER BY ORDINAL_POSITION";
|
|
if (args.Contains("--migrate-cache-expiration"))
|
|
{
|
|
// 运行时缓存使用 CloudProductConnection；历史初始化命令仍针对原专用库。
|
|
var cacheConnection = Environment.GetEnvironmentVariable("Plugins__CloudProduct__CloudProductConnection")
|
|
?? config.RootElement.GetProperty("CloudProductConnection").GetString();
|
|
using var cacheDb = new FreeSql.FreeSqlBuilder().UseConnectionString(FreeSql.DataType.MySql, cacheConnection).Build();
|
|
const string expirationTypeQuery = """
|
|
SELECT DATA_TYPE FROM information_schema.COLUMNS
|
|
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'image_analysis_cache' AND COLUMN_NAME = 'ExpiresAt'
|
|
""";
|
|
var columns = await cacheDb.Ado.QueryAsync<string>(columnsQuery);
|
|
var expirationType = (await cacheDb.Ado.QueryAsync<string>(expirationTypeQuery)).SingleOrDefault();
|
|
Console.WriteLine($"Cache columns before migration: {string.Join(", ", columns)}; ExpiresAt: {expirationType ?? "absent"}");
|
|
if (!columns.Contains("RecognitionResult") || !columns.Contains("UpdatedAt"))
|
|
throw new InvalidOperationException("当前运行库缺少缓存表或基础列。");
|
|
if (expirationType is not (null or "int" or "datetime" or "timestamp"))
|
|
throw new InvalidOperationException("不支持的 ExpiresAt 字段类型。");
|
|
// 用临时列完成日期到时长的转换，回填完成后才一次性替换原列。
|
|
var hasDateColumn = expirationType is "datetime" or "timestamp";
|
|
var hoursColumn = hasDateColumn ? "ExpiresAtHours" : "ExpiresAt";
|
|
if (!columns.Contains(hoursColumn))
|
|
await cacheDb.Ado.ExecuteNonQueryAsync($"ALTER TABLE image_analysis_cache ADD {hoursColumn} INT NULL");
|
|
// 已有整数时长原样保留；旧日期列按原到期时间与更新时间的差值换算。
|
|
var initialHours = "IF(TRIM(RecognitionResult) = '[]', 24, 720)";
|
|
if (hasDateColumn)
|
|
initialHours = $"COALESCE(TIMESTAMPDIFF(HOUR, UpdatedAt, ExpiresAt), {initialHours})";
|
|
await cacheDb.Ado.ExecuteNonQueryAsync($"UPDATE image_analysis_cache SET {hoursColumn} = {initialHours} WHERE {hoursColumn} IS NULL");
|
|
if (hasDateColumn)
|
|
await cacheDb.Ado.ExecuteNonQueryAsync("ALTER TABLE image_analysis_cache DROP COLUMN ExpiresAt, CHANGE ExpiresAtHours ExpiresAt INT NOT NULL");
|
|
else if ((await cacheDb.Ado.QueryAsync<string>("SELECT IS_NULLABLE FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'image_analysis_cache' AND COLUMN_NAME = 'ExpiresAt'")).Single() == "YES")
|
|
await cacheDb.Ado.ExecuteNonQueryAsync("ALTER TABLE image_analysis_cache MODIFY ExpiresAt INT NOT NULL");
|
|
|
|
var finalType = (await cacheDb.Ado.QueryAsync<string>(expirationTypeQuery)).Single();
|
|
var invalidRows = Convert.ToInt64(await cacheDb.Ado.ExecuteScalarAsync("""
|
|
SELECT COUNT(*) FROM image_analysis_cache
|
|
WHERE ExpiresAt IS NULL OR ExpiresAt NOT IN (24, 48, 96, 192, 384, 720)
|
|
"""));
|
|
if (finalType != "int" || invalidRows != 0)
|
|
throw new InvalidOperationException($"迁移校验失败，ExpiresAt 类型 {finalType}，异常行数 {invalidRows}。");
|
|
if (columns.Contains("EmptyResultStreak"))
|
|
await cacheDb.Ado.ExecuteNonQueryAsync("ALTER TABLE image_analysis_cache DROP COLUMN EmptyResultStreak");
|
|
Console.WriteLine("PASS database migration: cache lifetime stored in ExpiresAt hours");
|
|
return;
|
|
}
|
|
var connection = Environment.GetEnvironmentVariable("Plugins__CloudProduct__ProductInspection__ConnectionString")
|
|
?? config.RootElement.GetProperty("ProductInspection").GetProperty("ConnectionString").GetString();
|
|
using var db = new FreeSql.FreeSqlBuilder().UseConnectionString(FreeSql.DataType.MySql, connection).Build();
|
|
if (args.Contains("--initialize"))
|
|
{
|
|
// 旧表只移除两个派生字段，保留缓存键、响应原文及更新时间。
|
|
var existing = await db.Ado.QueryAsync<string>(tableQuery);
|
|
if (!existing.Contains("image_analysis_cache")) db.CodeFirst.SyncStructure(typeof(ImageAnalysisCache));
|
|
var columns = await db.Ado.QueryAsync<string>(columnsQuery);
|
|
var oldResult = new[] { "FormattedData", "识别结果" }.SingleOrDefault(columns.Contains);
|
|
if (oldResult != null)
|
|
await db.Ado.ExecuteNonQueryAsync($"ALTER TABLE image_analysis_cache CHANGE COLUMN `{oldResult}` RecognitionResult LONGTEXT NOT NULL");
|
|
var drops = new[] { "DetectionText", "LogoBrands" }.Where(columns.Contains).Select(name => $"DROP COLUMN `{name}`").ToArray();
|
|
if (drops.Length > 0) await db.Ado.ExecuteNonQueryAsync("ALTER TABLE image_analysis_cache " + string.Join(", ", drops));
|
|
// 旧键不能还原为图片 MD5，禁止在有旧缓存的环境中直接缩短主键。
|
|
if (await db.Select<ImageAnalysisCache>().Where(x => x.CacheKey.Length > 32).AnyAsync())
|
|
throw new InvalidOperationException("请先归档或清理旧的 SHA-256 图片缓存，再执行升级。");
|
|
db.CodeFirst.SyncStructure(typeof(ImageAnalysisCache));
|
|
await db.Ado.ExecuteNonQueryAsync("DROP TABLE IF EXISTS inspection_task_item, inspection_task, inspection_result, inspection_user_rule");
|
|
}
|
|
var tables = await db.Ado.QueryAsync<string>(tableQuery);
|
|
Console.WriteLine(string.Join(", ", tables));
|
|
var cacheColumns = await db.Ado.QueryAsync<string>(columnsQuery);
|
|
Console.WriteLine("Cache columns: " + string.Join(", ", cacheColumns));
|
|
Console.WriteLine($"Cache rows: {await db.Select<ImageAnalysisCache>().CountAsync()}");
|
|
if (args.Contains("--schema")) return;
|
|
if (!cacheColumns.ToHashSet().SetEquals(new[] { "CacheKey", "RecognitionResult", "UpdatedAt", "ExpiresAt" }))
|
|
throw new Exception("图片识别缓存结构不匹配。");
|
|
if (!tables.Contains("image_analysis_cache"))
|
|
throw new Exception("商品检测专用库缺少图片缓存表。");
|
|
var keyLength = await db.Ado.ExecuteScalarAsync("SELECT CHARACTER_MAXIMUM_LENGTH FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'image_analysis_cache' AND COLUMN_NAME = 'CacheKey'");
|
|
if (Convert.ToInt32(keyLength) != 32) throw new Exception("CacheKey 应为 32 位 MD5。");
|
|
await HttpChecks.RunAsync(db);
|