LabelRegionSchemaHelper.cs
4.12 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
using SqlSugar;
namespace FoodLabeling.Application.Helpers;
/// <summary>
/// 探测 <c>fl_label.AppliedRegionType</c> / <c>fl_label_region</c> 是否已落库，兼容未执行 DDL 的环境。
/// </summary>
public static class LabelRegionSchemaHelper
{
private static LabelRegionSchemaStatus? _cached;
public sealed class LabelRegionSchemaStatus
{
public bool HasAppliedRegionTypeColumn { get; init; }
public bool HasLabelRegionTable { get; init; }
public bool IsFullSchema => HasAppliedRegionTypeColumn && HasLabelRegionTable;
}
public static async Task<LabelRegionSchemaStatus> GetStatusAsync(ISqlSugarClient db)
{
if (_cached is not null)
{
return _cached;
}
var colCount = await db.Ado.GetIntAsync(
"""
SELECT COUNT(*)
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
AND TABLE_NAME = 'fl_label'
AND COLUMN_NAME = 'AppliedRegionType'
""");
var tableCount = await db.Ado.GetIntAsync(
"""
SELECT COUNT(*)
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = DATABASE()
AND TABLE_NAME = 'fl_label_region'
""");
_cached = new LabelRegionSchemaStatus
{
HasAppliedRegionTypeColumn = colCount > 0,
HasLabelRegionTable = tableCount > 0
};
return _cached;
}
/// <summary>单元测试或切换库后调用。</summary>
public static void ResetCacheForTests() => _cached = null;
public static async Task<string> GetAppliedRegionTypeForLabelAsync(
ISqlSugarClient db,
string labelId,
string? fallback = null)
{
var map = await GetAppliedRegionTypesForLabelsAsync(db, new List<string> { labelId });
if (map.TryGetValue(labelId, out var type) && !string.IsNullOrWhiteSpace(type))
{
return type.Trim();
}
return string.IsNullOrWhiteSpace(fallback)
? LabelRegionScopeHelper.AppliedRegionSpecified
: fallback.Trim();
}
public static async Task<Dictionary<string, string>> GetAppliedRegionTypesForLabelsAsync(
ISqlSugarClient db,
IReadOnlyList<string> labelIds)
{
var result = new Dictionary<string, string>(StringComparer.Ordinal);
if (labelIds.Count == 0)
{
return result;
}
foreach (var id in labelIds)
{
result[id] = LabelRegionScopeHelper.AppliedRegionSpecified;
}
var status = await GetStatusAsync(db);
if (!status.HasAppliedRegionTypeColumn)
{
return result;
}
var ids = labelIds.Where(x => !string.IsNullOrWhiteSpace(x)).Select(x => x.Trim()).Distinct().ToList();
if (ids.Count == 0)
{
return result;
}
var rows = await db.Ado.SqlQueryAsync<LabelAppliedRegionRow>(
"SELECT Id, AppliedRegionType FROM fl_label WHERE Id IN (@ids)",
new { ids });
foreach (var row in rows)
{
if (string.IsNullOrWhiteSpace(row.Id))
{
continue;
}
result[row.Id] = string.IsNullOrWhiteSpace(row.AppliedRegionType)
? LabelRegionScopeHelper.AppliedRegionSpecified
: row.AppliedRegionType.Trim();
}
return result;
}
public static async Task SetAppliedRegionTypeAsync(
ISqlSugarClient db,
string labelId,
string appliedRegionType)
{
var status = await GetStatusAsync(db);
if (!status.HasAppliedRegionTypeColumn || string.IsNullOrWhiteSpace(labelId))
{
return;
}
await db.Ado.ExecuteCommandAsync(
"UPDATE fl_label SET AppliedRegionType = @type WHERE Id = @id",
new { type = appliedRegionType.Trim(), id = labelId.Trim() });
}
private sealed class LabelAppliedRegionRow
{
public string Id { get; set; } = string.Empty;
public string? AppliedRegionType { get; set; }
}
}