sync-skills-to-sqlite.js
6.87 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
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
#!/usr/bin/env node
/**
* sync-skills-to-sqlite.js
*
* 以 skills/skill_list.json 为权威数据源，全量同步到 data/skills.sqlite
*
* 策略：
* 1. JSON 中有、SQLite 中没有 → INSERT
* 2. JSON 中有、SQLite 中也有 → UPDATE 所有字段（以 skill_id 匹配，fallback skill_name）
* 3. SQLite 中有、JSON 中没有 → DELETE（可选，默认开启）
* 4. Arena 数据也一并同步
*
* 用法: node server/scripts/sync-skills-to-sqlite.js [--dry-run] [--keep-orphans]
* --dry-run 只打印变更，不实际写入
* --keep-orphans 不删除 SQLite 中多余的 skill
*/
const path = require('path');
const fs = require('fs');
const SKILLS_DIR = path.resolve(__dirname, '../../skills');
const JSON_PATH = path.join(SKILLS_DIR, 'skill_list.json');
const skillDb = require('../services/skill-db');
function main() {
const args = process.argv.slice(2);
const dryRun = args.includes('--dry-run');
const keepOrphans = args.includes('--keep-orphans');
console.log('═══════════════════════════════════════════');
console.log(' Skills JSON → SQLite Full Sync');
console.log(` Mode: ${dryRun ? '🔍 DRY-RUN' : '🚀 LIVE'}`);
console.log(` Orphans: ${keepOrphans ? '保留' : '删除'}`);
console.log('═══════════════════════════════════════════');
console.log('');
// 1. 读取 JSON
if (!fs.existsSync(JSON_PATH)) {
console.error(`❌ skill_list.json not found at ${JSON_PATH}`);
process.exit(1);
}
const raw = JSON.parse(fs.readFileSync(JSON_PATH, 'utf-8'));
const jsonSkills = Array.isArray(raw) ? raw : (raw.skills || []);
console.log(`📄 JSON 中共 ${jsonSkills.length} 个 skills`);
// 2. 获取 SQLite 现有数据
const db = skillDb.getDb();
const dbRows = db.prepare('SELECT skill_id, skill_name FROM skills').all();
const dbSkillIdSet = new Set(dbRows.map(r => r.skill_id));
const dbSkillNameSet = new Set(dbRows.map(r => r.skill_name));
console.log(`📊 SQLite 中共 ${dbRows.length} 个 skills`);
console.log('');
let inserted = 0;
let updated = 0;
let deleted = 0;
let arenasynced = 0;
let errors = 0;
// 构建 JSON 中 skill_id 和 skill_name 集合
const jsonSkillIdSet = new Set(jsonSkills.map(s => s.skillId));
const jsonSkillNameSet = new Set(jsonSkills.map(s => s.skillName));
// 3. 遍历 JSON，INSERT 或 UPDATE
for (const skill of jsonSkills) {
try {
const existsById = dbSkillIdSet.has(skill.skillId);
const existsByName = dbSkillNameSet.has(skill.skillName);
if (existsById || existsByName) {
// ─── UPDATE ───
if (dryRun) {
console.log(` 🔄 UPDATE: ${skill.skillName} (${skill.skillId})`);
} else {
const row = skillDb.skillToRow(skill);
// 构建 SET 子句（排除 skill_id，它是主键）
const updateCols = Object.keys(row).filter(k => k !== 'skill_id');
const sets = updateCols.map(k => `${k} = ?`).join(', ');
const params = updateCols.map(k => row[k]);
if (existsById) {
params.push(skill.skillId);
db.prepare(`UPDATE skills SET ${sets} WHERE skill_id = ?`).run(...params);
} else {
// skill_name 匹配但 skill_id 不同，更新 skill_id 并更新所有字段
params.push(skill.skillName);
db.prepare(`UPDATE skills SET skill_id = ?, ${sets} WHERE skill_name = ?`)
.run(skill.skillId, ...params);
}
}
updated++;
} else {
// ─── INSERT ───
if (dryRun) {
console.log(` ➕ INSERT: ${skill.skillName} (${skill.skillId})`);
} else {
const row = skillDb.skillToRow(skill);
const cols = Object.keys(row);
const placeholders = cols.map(() => '?').join(', ');
db.prepare(`INSERT INTO skills (${cols.join(', ')}) VALUES (${placeholders})`)
.run(...Object.values(row));
// 记录初始版本
db.prepare(`INSERT OR IGNORE INTO skill_versions (skill_id, version, bump_type, change_summary) VALUES (?, ?, ?, ?)`)
.run(skill.skillId, skill.version || '1.0.0', 'sync', `Synced from skill_list.json`);
}
inserted++;
}
// ─── Arena 同步 ───
if (skill.arena && !dryRun) {
// 需要先确保 skill_id 在 skills 表中存在
skillDb.upsertArena(skill.skillId, skill.arena);
arenasynced++;
} else if (skill.arena && dryRun) {
arenasynced++;
}
} catch (err) {
console.error(` ❌ ${skill.skillName}: ${err.message}`);
errors++;
}
}
// 4. 删除 SQLite 中有但 JSON 中没有的 skill（孤儿）
if (!keepOrphans) {
for (const dbRow of dbRows) {
if (!jsonSkillIdSet.has(dbRow.skill_id) && !jsonSkillNameSet.has(dbRow.skill_name)) {
if (dryRun) {
console.log(` 🗑️ DELETE: ${dbRow.skill_name} (${dbRow.skill_id})`);
} else {
// 先删 arena（外键 CASCADE 应该自动处理，但显式删除更安全）
db.prepare('DELETE FROM skill_arena WHERE skill_id = ?').run(dbRow.skill_id);
db.prepare('DELETE FROM skill_versions WHERE skill_id = ?').run(dbRow.skill_id);
db.prepare('DELETE FROM skill_downloads WHERE skill_id = ?').run(dbRow.skill_id);
db.prepare('DELETE FROM skill_update_history WHERE skill_id = ?').run(dbRow.skill_id);
db.prepare('DELETE FROM skills WHERE skill_id = ?').run(dbRow.skill_id);
}
deleted++;
}
}
}
// 5. 结果报告
console.log('');
console.log('─── Results ───');
console.log(` ➕ Inserted: ${inserted}`);
console.log(` 🔄 Updated: ${updated}`);
console.log(` 🗑️ Deleted: ${deleted}`);
console.log(` 🏟️ Arena synced: ${arenasynced}`);
console.log(` ❌ Errors: ${errors}`);
console.log('');
// 6. 验证
if (!dryRun) {
const finalCount = db.prepare('SELECT COUNT(*) as c FROM skills').get().c;
const arenaCount = db.prepare('SELECT COUNT(*) as c FROM skill_arena').get().c;
const versionCount = db.prepare('SELECT COUNT(*) as c FROM skill_versions').get().c;
console.log('─── Verification ───');
console.log(` Skills: ${finalCount} (JSON: ${jsonSkills.length})`);
console.log(` Arena: ${arenaCount}`);
console.log(` Versions: ${versionCount}`);
console.log('');
if (finalCount === jsonSkills.length) {
console.log('✅ Sync complete! SQLite is now in sync with skill_list.json.');
} else {
console.log(`⚠️ Mismatch: expected ${jsonSkills.length}, got ${finalCount} in DB.`);
}
} else {
console.log('🔍 Dry-run complete. No changes were made.');
}
}
main();