/** * Skill Database — SQLite 替代 skill_list.json * * 表结构： * skills 主表（所有 Skill 元数据） * skill_versions 版本历史（每次 bumpVersion 记录快照） * skill_downloads 下载记录（关联用户） * skill_arena Arena 评分数据（1:1 关联 skills） * * API 完全兼容 SkillRegistry 类接口，可渐进替换。 */ const Database = require('better-sqlite3'); const path = require('path'); const fs = require('fs'); const crypto = require('crypto'); const config = require('../config'); const { initSkillDir, getTargetDir, resolveSkillDir } = require('./skill-dir-manager'); const DATA_DIR = path.resolve(__dirname, '../../../data'); const DB_PATH = process.env.SKILLS_DB_PATH || path.join(DATA_DIR, 'skills.sqlite'); let db = null; function getDb() { if (db) return db; if (!fs.existsSync(DATA_DIR)) { fs.mkdirSync(DATA_DIR, { recursive: true }); } db = new Database(DB_PATH); db.pragma('journal_mode = WAL'); db.pragma('foreign_keys = ON'); // ─── 主表 ─── db.exec(` CREATE TABLE IF NOT EXISTS skills ( id INTEGER PRIMARY KEY AUTOINCREMENT, skill_id TEXT UNIQUE NOT NULL, skill_name TEXT UNIQUE NOT NULL, directory TEXT, description TEXT DEFAULT '', domain TEXT DEFAULT '', scope TEXT DEFAULT '', status TEXT DEFAULT 'training', version TEXT DEFAULT '1.0.0', benchmark_version TEXT DEFAULT '1.0.0', industry_avg_score REAL, elite_avg_score REAL, current_score REAL, use_count INTEGER DEFAULT 0, download_count INTEGER DEFAULT 0, update_count INTEGER DEFAULT 0, last_used DATETIME, last_updated DATETIME DEFAULT CURRENT_TIMESTAMP, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, keywords TEXT DEFAULT '[]', type TEXT DEFAULT 'executor', agent_name TEXT, layer INTEGER, dependencies TEXT DEFAULT '[]', scene TEXT DEFAULT '', author TEXT DEFAULT 'LemonAI', license TEXT DEFAULT 'MIT', source TEXT DEFAULT 'local', tags TEXT DEFAULT '[]' ); CREATE INDEX IF NOT EXISTS idx_skills_name ON skills(skill_name); CREATE INDEX IF NOT EXISTS idx_skills_domain ON skills(domain); CREATE INDEX IF NOT EXISTS idx_skills_status ON skills(status); `); // ─── 三维标签字段迁移 (roles/scenes/platforms) ─── try { db.exec(`ALTER TABLE skills ADD COLUMN roles TEXT DEFAULT '[]'`); } catch (e) { /* column likely already exists */ } try { db.exec(`ALTER TABLE skills ADD COLUMN scenes TEXT DEFAULT '[]'`); } catch (e) { /* column likely already exists */ } try { db.exec(`ALTER TABLE skills ADD COLUMN platforms TEXT DEFAULT '[]'`); } catch (e) { /* column likely already exists */ } // ─── Arena 评分表 ─── db.exec(` CREATE TABLE IF NOT EXISTS skill_arena ( id INTEGER PRIMARY KEY AUTOINCREMENT, skill_id TEXT UNIQUE NOT NULL REFERENCES skills(skill_id) ON DELETE CASCADE, elo REAL DEFAULT 1200, elo_margin REAL DEFAULT 50, rank INTEGER, rank_spread REAL, benchmark_runs INTEGER DEFAULT 0, last_benchmark DATETIME, score_success_rate REAL, score_accuracy REAL, score_speed REAL, score_cost REAL, score_robustness REAL, score_completeness REAL, security_status TEXT DEFAULT 'pending', security_confidence REAL, security_last_scan DATETIME, highlighted INTEGER DEFAULT 0, trending INTEGER DEFAULT 0, global_rank INTEGER ); `); // ─── 版本历史表 ─── db.exec(` CREATE TABLE IF NOT EXISTS skill_versions ( id INTEGER PRIMARY KEY AUTOINCREMENT, skill_id TEXT NOT NULL REFERENCES skills(skill_id) ON DELETE CASCADE, version TEXT NOT NULL, previous_version TEXT, bump_type TEXT, score REAL, change_summary TEXT DEFAULT '', created_at DATETIME DEFAULT CURRENT_TIMESTAMP ); CREATE INDEX IF NOT EXISTS idx_versions_skill ON skill_versions(skill_id); `); // ─── 下载记录表 ─── db.exec(` CREATE TABLE IF NOT EXISTS skill_downloads ( id INTEGER PRIMARY KEY AUTOINCREMENT, skill_id TEXT NOT NULL REFERENCES skills(skill_id) ON DELETE CASCADE, user_id INTEGER, user_email TEXT, download_version TEXT, ip_address TEXT, created_at DATETIME DEFAULT CURRENT_TIMESTAMP ); CREATE INDEX IF NOT EXISTS idx_downloads_skill ON skill_downloads(skill_id); CREATE INDEX IF NOT EXISTS idx_downloads_user ON skill_downloads(user_id); `); // ─── 更新历史表 ─── db.exec(` CREATE TABLE IF NOT EXISTS skill_update_history ( id INTEGER PRIMARY KEY AUTOINCREMENT, skill_id TEXT NOT NULL REFERENCES skills(skill_id) ON DELETE CASCADE, from_version TEXT NOT NULL, to_version TEXT NOT NULL, count INTEGER DEFAULT 1, created_at DATETIME DEFAULT CURRENT_TIMESTAMP ); CREATE INDEX IF NOT EXISTS idx_update_hist_skill ON skill_update_history(skill_id); `); // ─── Hunt 数据源配置表 ─── db.exec(` CREATE TABLE IF NOT EXISTS skill_hunt_sources ( id INTEGER PRIMARY KEY AUTOINCREMENT, skill_id TEXT NOT NULL REFERENCES skills(skill_id) ON DELETE CASCADE, source_type TEXT, source_url TEXT, fetch_interval TEXT DEFAULT 'on_demand', last_fetched DATETIME, data_count INTEGER DEFAULT 0, config_json TEXT DEFAULT '{}', UNIQUE(skill_id, source_url) ); CREATE INDEX IF NOT EXISTS idx_hunt_sources_skill ON skill_hunt_sources(skill_id); `); // ─── Digest 版本记录表 ─── db.exec(` CREATE TABLE IF NOT EXISTS skill_digests ( id INTEGER PRIMARY KEY AUTOINCREMENT, skill_id TEXT NOT NULL REFERENCES skills(skill_id) ON DELETE CASCADE, version TEXT NOT NULL, digest_type TEXT, file_path TEXT, file_size INTEGER, checksum TEXT, created_at DATETIME DEFAULT CURRENT_TIMESTAMP ); CREATE INDEX IF NOT EXISTS idx_digests_skill ON skill_digests(skill_id); `); // ─── Evolve 测试记录表 ─── db.exec(` CREATE TABLE IF NOT EXISTS skill_evolve_runs ( id INTEGER PRIMARY KEY AUTOINCREMENT, skill_id TEXT NOT NULL REFERENCES skills(skill_id) ON DELETE CASCADE, version TEXT NOT NULL, run_type TEXT DEFAULT 'benchmark', status TEXT DEFAULT 'pending', score REAL, score_delta REAL, test_count INTEGER, pass_count INTEGER, started_at DATETIME, completed_at DATETIME, result_path TEXT ); CREATE INDEX IF NOT EXISTS idx_evolve_runs_skill ON skill_evolve_runs(skill_id); `); // ─── Production 日志聚合表 ─── db.exec(` CREATE TABLE IF NOT EXISTS skill_production_stats ( id INTEGER PRIMARY KEY AUTOINCREMENT, skill_id TEXT NOT NULL REFERENCES skills(skill_id) ON DELETE CASCADE, date TEXT NOT NULL, requests INTEGER DEFAULT 0, downloads INTEGER DEFAULT 0, errors INTEGER DEFAULT 0, avg_latency_ms REAL, UNIQUE(skill_id, date) ); CREATE INDEX IF NOT EXISTS idx_prod_stats_skill ON skill_production_stats(skill_id); `); // ─── FTS5 全文搜索索引 ─── try { db.exec(` CREATE VIRTUAL TABLE IF NOT EXISTS skills_fts USING fts5( skill_name, description, domain, keywords, tags, content='skills', content_rowid='id' ); `); // FTS5 同步触发器 — INSERT/UPDATE/DELETE 时自动更新索引 db.exec(` CREATE TRIGGER IF NOT EXISTS skills_ai AFTER INSERT ON skills BEGIN INSERT INTO skills_fts(rowid, skill_name, description, domain, keywords, tags) VALUES (new.id, new.skill_name, new.description, new.domain, new.keywords, new.tags); END; `); db.exec(` CREATE TRIGGER IF NOT EXISTS skills_ad AFTER DELETE ON skills BEGIN INSERT INTO skills_fts(skills_fts, rowid, skill_name, description, domain, keywords, tags) VALUES ('delete', old.id, old.skill_name, old.description, old.domain, old.keywords, old.tags); END; `); db.exec(` CREATE TRIGGER IF NOT EXISTS skills_au AFTER UPDATE ON skills BEGIN INSERT INTO skills_fts(skills_fts, rowid, skill_name, description, domain, keywords, tags) VALUES ('delete', old.id, old.skill_name, old.description, old.domain, old.keywords, old.tags); INSERT INTO skills_fts(rowid, skill_name, description, domain, keywords, tags) VALUES (new.id, new.skill_name, new.description, new.domain, new.keywords, new.tags); END; `); // 确保 FTS5 索引与 skills 表内容同步 db.exec(`INSERT INTO skills_fts(skills_fts) VALUES('rebuild');`); } catch (e) { // FTS5 may not be available on all builds, silently skip console.warn('[skill-db] FTS5 not available:', e.message); } // ─── 收藏表 ─── db.exec(` CREATE TABLE IF NOT EXISTS skill_favorites ( id INTEGER PRIMARY KEY AUTOINCREMENT, skill_id TEXT NOT NULL REFERENCES skills(skill_id) ON DELETE CASCADE, user_id TEXT NOT NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, UNIQUE(skill_id, user_id) ); CREATE INDEX IF NOT EXISTS idx_favorites_user ON skill_favorites(user_id); CREATE INDEX IF NOT EXISTS idx_favorites_skill ON skill_favorites(skill_id); `); // ─── 收藏计数字段迁移 ─── try { db.exec(`ALTER TABLE skills ADD COLUMN favorite_count INTEGER DEFAULT 0`); } catch (e) { /* column likely already exists */ } // ─── 训练 skill 用户隔离字段迁移 ─── try { db.exec(`ALTER TABLE skills ADD COLUMN user_id TEXT DEFAULT NULL`); } catch (e) { /* column likely already exists */ } try { db.exec(`ALTER TABLE skills ADD COLUMN storage_type TEXT DEFAULT 'marketplace'`); } catch (e) { /* column likely already exists */ } return db; } // ═══════════════════════════════════════════════════ // Helper: JSON ↔ Row 转换 // ═══════════════════════════════════════════════════ /** 将 JSON skill 对象转为 DB 行格式 */ function skillToRow(skill) { return { skill_id: skill.skillId, skill_name: skill.skillName, directory: skill.directory || null, description: skill.description || '', domain: skill.domain || '', scope: skill.scope || '', status: skill.status || 'training', version: skill.version || '1.0.0', benchmark_version: skill.benchmarkVersion || '1.0.0', industry_avg_score: skill.industryAvgScore ?? null, elite_avg_score: skill.eliteAvgScore ?? null, current_score: skill.currentScore ?? null, use_count: skill.useCount || 0, download_count: skill.downloadCount || 0, update_count: skill.updateCount || 0, last_used: skill.lastUsed || null, last_updated: skill.lastUpdated || new Date().toISOString(), created_at: skill.createdAt || new Date().toISOString(), keywords: JSON.stringify(skill.keywords || []), type: skill.type || 'executor', agent_name: skill.agentName || null, layer: skill.layer ?? null, dependencies: JSON.stringify(skill.dependencies || []), scene: skill.scene || '', author: skill.author || 'ArenaSK', license: skill.license || 'MIT', source: skill.source || 'local', tags: JSON.stringify(skill.tags || []), roles: JSON.stringify(skill.roles || []), scenes: JSON.stringify(skill.scenes || []), platforms: JSON.stringify(skill.platforms || []), user_id: skill.userId || null, storage_type: skill.storageType || 'marketplace', }; } /** 将 DB 行转回 JSON skill 对象（兼容旧接口） */ function rowToSkill(row) { if (!row) return null; const skill = { skillId: row.skill_id, skillName: row.skill_name, directory: row.directory, description: row.description, domain: row.domain, scope: row.scope, status: row.status, version: row.version, benchmarkVersion: row.benchmark_version, industryAvgScore: row.industry_avg_score, eliteAvgScore: row.elite_avg_score, currentScore: row.current_score, useCount: row.use_count, downloadCount: row.download_count, updateCount: row.update_count, lastUsed: row.last_used, lastUpdated: row.last_updated, createdAt: row.created_at, keywords: safeJsonParse(row.keywords, []), type: row.type, agentName: row.agent_name, layer: row.layer, dependencies: safeJsonParse(row.dependencies, []), scene: row.scene, author: row.author, license: row.license, source: row.source, tags: safeJsonParse(row.tags, []), roles: safeJsonParse(row.roles, []), scenes: safeJsonParse(row.scenes, []), platforms: safeJsonParse(row.platforms, []), userId: row.user_id || null, storageType: row.storage_type || 'marketplace', }; return skill; } /** 将 arena DB 行转为嵌套 JSON */ function rowToArena(row) { if (!row) return null; return { elo: row.elo, eloMargin: row.elo_margin, rank: row.rank, rankSpread: row.rank_spread, benchmarkRuns: row.benchmark_runs, lastBenchmark: row.last_benchmark, scores: { successRate: row.score_success_rate, accuracy: row.score_accuracy, speed: row.score_speed, cost: row.score_cost, robustness: row.score_robustness, completeness: row.score_completeness, }, securityScan: { status: row.security_status, confidence: row.security_confidence, lastScan: row.security_last_scan, }, highlighted: !!row.highlighted, trending: !!row.trending, globalRank: row.global_rank, }; } function safeJsonParse(str, fallback) { try { return JSON.parse(str); } catch { return fallback; } } /** 获取完整 skill + arena */ function getFullSkill(skillName) { const d = getDb(); const row = d.prepare('SELECT * FROM skills WHERE skill_name = ?').get(skillName); if (!row) return null; const skill = rowToSkill(row); const arenaRow = d.prepare('SELECT * FROM skill_arena WHERE skill_id = ?').get(row.skill_id); if (arenaRow) skill.arena = rowToArena(arenaRow); return skill; } // ═══════════════════════════════════════════════════ // CRUD — SkillRegistry 兼容接口 // ═══════════════════════════════════════════════════ function registerSkill(meta) { const d = getDb(); const existing = d.prepare('SELECT 1 FROM skills WHERE skill_name = ?').get(meta.skillName); if (existing) throw new Error(`Skill "${meta.skillName}" already exists`); const skillId = `skill-${Date.now()}`; const now = new Date().toISOString(); const targetDir = meta.directory || (meta.skillsDir ? path.join(meta.skillsDir, meta.skillName) : getTargetDir(meta.skillName)); // Initialize lifecycle directory structure try { initSkillDir(meta.skillName); } catch (e) { console.warn(`[skill-db] Failed to init skill dir for ${meta.skillName}:`, e.message); } const row = skillToRow({ skillId, skillName: meta.skillName, directory: targetDir, description: meta.description || '', domain: meta.domain || '', scope: meta.scope || '', status: 'training', version: '1.0.0', benchmarkVersion: '1.0.0', keywords: meta.keywords || [], layer: meta.layer || null, dependencies: meta.dependencies || [], source: meta.source || (require('./skill-dir-manager').isCodeSkill(meta.skillName) ? 'local' : 'crawled'), createdAt: now, lastUpdated: now, }); const cols = Object.keys(row); const placeholders = cols.map(() => '?').join(', '); d.prepare(`INSERT INTO skills (${cols.join(', ')}) VALUES (${placeholders})`).run(...Object.values(row)); // Record initial version d.prepare(`INSERT INTO skill_versions (skill_id, version, bump_type, change_summary) VALUES (?, ?, ?, ?)`) .run(skillId, '1.0.0', 'initial', 'Initial registration'); return getFullSkill(meta.skillName); } function updateSkill(skillName, updates) { const d = getDb(); const row = d.prepare('SELECT * FROM skills WHERE skill_name = ?').get(skillName); if (!row) throw new Error(`Skill "${skillName}" not found`); // Build dynamic SET clause const setters = []; const params = []; // Map camelCase to snake_case for common fields const fieldMap = { directory: 'directory', description: 'description', domain: 'domain', scope: 'scope', status: 'status', version: 'version', benchmarkVersion: 'benchmark_version', industryAvgScore: 'industry_avg_score', eliteAvgScore: 'elite_avg_score', currentScore: 'current_score', useCount: 'use_count', downloadCount: 'download_count', updateCount: 'update_count', lastUsed: 'last_used', type: 'type', agentName: 'agent_name', layer: 'layer', scene: 'scene', author: 'author', license: 'license', source: 'source', userId: 'user_id', storageType: 'storage_type', }; for (const [key, val] of Object.entries(updates)) { if (key === 'arena') { // Arena is handled separately upsertArena(row.skill_id, val); continue; } const col = fieldMap[key]; if (col) { setters.push(`${col} = ?`); params.push(val); } else if (key === 'keywords' || key === 'dependencies' || key === 'tags' || key === 'roles' || key === 'scenes' || key === 'platforms') { const snakeKey = key; // same name in DB setters.push(`${snakeKey} = ?`); params.push(JSON.stringify(val)); } } setters.push('last_updated = ?'); params.push(new Date().toISOString()); params.push(skillName); if (setters.length > 0) { d.prepare(`UPDATE skills SET ${setters.join(', ')} WHERE skill_name = ?`).run(...params); } return getFullSkill(skillName); } function upsertArena(skillId, arena) { const d = getDb(); const existing = d.prepare('SELECT 1 FROM skill_arena WHERE skill_id = ?').get(skillId); const scores = arena.scores || {}; const sec = arena.securityScan || {}; const vals = { skill_id: skillId, elo: arena.elo ?? 1200, elo_margin: arena.eloMargin ?? 50, rank: arena.rank ?? null, rank_spread: arena.rankSpread ?? null, benchmark_runs: arena.benchmarkRuns ?? 0, last_benchmark: arena.lastBenchmark ?? null, score_success_rate: scores.successRate ?? null, score_accuracy: scores.accuracy ?? null, score_speed: scores.speed ?? null, score_cost: scores.cost ?? null, score_robustness: scores.robustness ?? null, score_completeness: scores.completeness ?? null, security_status: sec.status ?? 'pending', security_confidence: sec.confidence ?? null, security_last_scan: sec.lastScan ?? null, highlighted: arena.highlighted ? 1 : 0, trending: arena.trending ? 1 : 0, global_rank: arena.globalRank ?? null, }; if (existing) { const sets = Object.keys(vals).filter(k => k !== 'skill_id').map(k => `${k} = ?`); const params = Object.keys(vals).filter(k => k !== 'skill_id').map(k => vals[k]); params.push(skillId); d.prepare(`UPDATE skill_arena SET ${sets.join(', ')} WHERE skill_id = ?`).run(...params); } else { const cols = Object.keys(vals); const placeholders = cols.map(() => '?').join(', '); d.prepare(`INSERT INTO skill_arena (${cols.join(', ')}) VALUES (${placeholders})`).run(...Object.values(vals)); } } function renameSkill(oldName, newName, skillsDir) { if (!newName || !newName.trim()) throw new Error('新名称不能为空'); newName = newName.trim().replace(/\s+/g, '_'); if (oldName === newName) return getFullSkill(oldName); const d = getDb(); const existing = d.prepare('SELECT 1 FROM skills WHERE skill_name = ?').get(newName); if (existing) throw new Error(`Skill "${newName}" 已存在`); // Rename directory (use resolveSkillDir to find actual location) const oldDir = skillsDir ? path.join(skillsDir, oldName) : resolveSkillDir(oldName); if (oldDir && fs.existsSync(oldDir)) { const newDir = path.join(path.dirname(oldDir), newName); fs.renameSync(oldDir, newDir); } d.prepare('UPDATE skills SET skill_name = ?, last_updated = ? WHERE skill_name = ?') .run(newName, new Date().toISOString(), oldName); return getFullSkill(newName); } function getSkill(skillName) { return getFullSkill(skillName); } function getSkillById(skillId) { const d = getDb(); const row = d.prepare('SELECT * FROM skills WHERE skill_id = ?').get(skillId); if (!row) return null; const skill = rowToSkill(row); const arenaRow = d.prepare('SELECT * FROM skill_arena WHERE skill_id = ?').get(skillId); if (arenaRow) skill.arena = rowToArena(arenaRow); return skill; } function listSkills({ limit, offset = 0, status, domain, layer, storageType, orderBy = 'created_at', order = 'DESC' } = {}) { const d = getDb(); // Build dynamic WHERE clause const conditions = []; const params = []; if (status) { conditions.push('s.status = ?'); params.push(status); } if (domain) { conditions.push('s.domain = ?'); params.push(domain); } if (layer != null) { conditions.push('s.layer = ?'); params.push(layer); } if (storageType === 'marketplace') { conditions.push("(s.storage_type IS NULL OR s.storage_type = 'marketplace')"); } else if (storageType) { conditions.push('s.storage_type = ?'); params.push(storageType); } const where = conditions.length > 0 ? `WHERE ${conditions.join(' AND ')}` : ''; // Validate orderBy to prevent injection const allowedOrderBy = ['created_at', 'skill_name', 'use_count', 'download_count', 'current_score', 'last_updated']; const safeOrderBy = allowedOrderBy.includes(orderBy) ? orderBy : 'created_at'; const safeOrder = order === 'ASC' ? 'ASC' : 'DESC'; let sql = `SELECT s.*, a.elo, a.rank, a.benchmark_runs, a.highlighted, a.trending, a.score_success_rate, a.score_accuracy, a.score_speed, a.score_cost, a.score_robustness, a.score_completeness, a.global_rank, a.elo_margin, a.rank_spread, a.last_benchmark, a.security_status, a.security_confidence, a.security_last_scan FROM skills s LEFT JOIN skill_arena a ON s.skill_id = a.skill_id ${where} ORDER BY s.${safeOrderBy} ${safeOrder}`; if (limit != null) { sql += ' LIMIT ? OFFSET ?'; params.push(limit, offset); } const rows = d.prepare(sql).all(...params); return rows.map(row => { const skill = rowToSkill(row); // Arena data is joined, build arena object if present if (row.elo != null) { skill.arena = { elo: row.elo, eloMargin: row.elo_margin, rank: row.rank, rankSpread: row.rank_spread, benchmarkRuns: row.benchmark_runs, lastBenchmark: row.last_benchmark, scores: { successRate: row.score_success_rate, accuracy: row.score_accuracy, speed: row.score_speed, cost: row.score_cost, robustness: row.score_robustness, completeness: row.score_completeness, }, securityScan: { status: row.security_status, confidence: row.security_confidence, lastScan: row.security_last_scan, }, highlighted: !!row.highlighted, trending: !!row.trending, globalRank: row.global_rank, }; } return skill; }); } /** 获取 skill 总数（用于分页） */ function countSkills({ status, domain, layer, storageType } = {}) { const d = getDb(); const conditions = []; const params = []; if (status) { conditions.push('status = ?'); params.push(status); } if (domain) { conditions.push('domain = ?'); params.push(domain); } if (layer != null) { conditions.push('layer = ?'); params.push(layer); } if (storageType === 'marketplace') { conditions.push("(storage_type IS NULL OR storage_type = 'marketplace')"); } else if (storageType) { conditions.push('storage_type = ?'); params.push(storageType); } const where = conditions.length > 0 ? `WHERE ${conditions.join(' AND ')}` : ''; return d.prepare(`SELECT COUNT(*) as count FROM skills ${where}`).get(...params).count; } function removeSkill(skillName) { const d = getDb(); const result = d.prepare('DELETE FROM skills WHERE skill_name = ?').run(skillName); return result.changes > 0; } function bumpVersion(skillName, type = 'minor', score = null) { const skill = getFullSkill(skillName); if (!skill) throw new Error(`Skill "${skillName}" not found`); const [major, minor, patch] = (skill.version || '1.0.0').split('.').map(Number); let newVersion; if (type === 'patch') { newVersion = `${major}.${minor}.${patch + 1}`; } else { newVersion = `${major}.${minor + 1}.0`; } const updates = { version: newVersion }; if (score !== null) updates.currentScore = score; // Record version history const d = getDb(); d.prepare(`INSERT INTO skill_versions (skill_id, version, previous_version, bump_type, score) VALUES (?, ?, ?, ?, ?)`) .run(skill.skillId, newVersion, skill.version, type, score); return updateSkill(skillName, updates); } function incrementUseCount(skillName) { const d = getDb(); d.prepare('UPDATE skills SET use_count = use_count + 1, last_used = ? WHERE skill_name = ?') .run(new Date().toISOString(), skillName); } function incrementDownloadCount(skillName) { const d = getDb(); d.prepare('UPDATE skills SET download_count = download_count + 1 WHERE skill_name = ?') .run(skillName); } function recordDownload(skillName, { userId, userEmail, version, ipAddress } = {}) { const d = getDb(); const row = d.prepare('SELECT skill_id FROM skills WHERE skill_name = ?').get(skillName); if (!row) return; d.prepare(`INSERT INTO skill_downloads (skill_id, user_id, user_email, download_version, ip_address) VALUES (?, ?, ?, ?, ?)`) .run(row.skill_id, userId || null, userEmail || null, version || null, ipAddress || null); incrementDownloadCount(skillName); } function recordUpdate(skillName, fromVersion, toVersion) { const d = getDb(); const row = d.prepare('SELECT skill_id FROM skills WHERE skill_name = ?').get(skillName); if (!row) return; // Upsert update history const existing = d.prepare('SELECT id, count FROM skill_update_history WHERE skill_id = ? AND from_version = ? AND to_version = ?') .get(row.skill_id, fromVersion, toVersion); if (existing) { d.prepare('UPDATE skill_update_history SET count = count + 1 WHERE id = ?').run(existing.id); } else { d.prepare('INSERT INTO skill_update_history (skill_id, from_version, to_version) VALUES (?, ?, ?)') .run(row.skill_id, fromVersion, toVersion); } d.prepare('UPDATE skills SET update_count = update_count + 1 WHERE skill_name = ?').run(skillName); } // ═══════════════════════════════════════════════════ // Search — FTS5 + SQL 优化 // ═══════════════════════════════════════════════════ /** * FTS5 全文搜索，带 SQL 回退 * @param {string} query - 搜索关键词 * @param {object} options * @param {string} [options.domain] - 按 domain 过滤 * @param {string} [options.type] - 按 type 过滤 * @param {string} [options.status] - 按 status 过滤，默认 'enable' * @param {number} [options.limit] - 限制返回数量，默认 20 * @param {number} [options.offset] - 偏移，默认 0 */ function searchSkills(query = '', { domain, type, status = 'enable', limit = 20, offset = 0 } = {}) { const d = getDb(); // 无查询词时，直接返回列表 if (!query || !query.trim()) { return listSkills({ status, domain, limit, offset, orderBy: 'download_count' }).map(toPublicInfo); } // 尝试 FTS5 搜索 try { return _ftsSearch(query, { domain, type, status, limit, offset }); } catch (e) { // FTS5 不可用时回退到 SQL LIKE return _likeSearch(query, { domain, type, status, limit, offset }); } } /** FTS5 全文搜索 */ function _ftsSearch(query, { domain, type, status, limit, offset }) { const d = getDb(); // 给 FTS5 的查询词加 * 前缀匹配 const ftsQuery = query.trim().split(/\s+/).map(w => `"${w}"*`).join(' '); const conditions = ["s.status = ?"]; const params = [status]; if (domain) { conditions.push('s.domain = ?'); params.push(domain); } if (type) { conditions.push('s.type = ?'); params.push(type); } const sql = ` SELECT s.*, rank AS fts_rank FROM skills_fts fts JOIN skills s ON s.id = fts.rowid WHERE skills_fts MATCH ? AND ${conditions.join(' AND ')} ORDER BY fts_rank LIMIT ? OFFSET ? `; const rows = d.prepare(sql).all(ftsQuery, ...params, limit, offset); return rows.map(row => toPublicInfo(rowToSkill(row))); } /** SQL LIKE 回退搜索 */ function _likeSearch(query, { domain, type, status, limit, offset }) { const d = getDb(); const q = `%${query.trim()}%`; const conditions = ['s.status = ?']; const params = [status]; if (domain) { conditions.push('s.domain = ?'); params.push(domain); } if (type) { conditions.push('s.type = ?'); params.push(type); } const sql = ` SELECT s.*, CASE WHEN s.skill_name LIKE ? THEN 10 WHEN s.domain LIKE ? THEN 8 WHEN s.keywords LIKE ? THEN 7 WHEN s.description LIKE ? THEN 5 ELSE 1 END AS relevance FROM skills s WHERE (s.skill_name LIKE ? OR s.description LIKE ? OR s.domain LIKE ? OR s.keywords LIKE ? OR s.tags LIKE ?) AND ${conditions.join(' AND ')} ORDER BY relevance DESC, s.download_count DESC LIMIT ? OFFSET ? `; const rows = d.prepare(sql).all( q, q, q, q, // relevance CASE q, q, q, q, q, // WHERE LIKE ...params, limit, offset ); return rows.map(row => toPublicInfo(rowToSkill(row))); } /** * 精确 + 语义匹配查找（SQL 优化版） */ function findSkill(domain, keywords = [], skillName = '') { const d = getDb(); // Tier 1: Exact match by name if (skillName) { const row = d.prepare("SELECT * FROM skills WHERE skill_name = ? AND status IN ('enable','training')").get(skillName); if (row) return { match: 'exact', skill: rowToSkill(row) }; } // Tier 1: Exact keyword match if (keywords.length > 0) { const rows = d.prepare("SELECT * FROM skills WHERE status IN ('enable','training')").all(); for (const row of rows) { const sk = safeJsonParse(row.keywords, []); const overlap = keywords.filter(k => sk.includes(k)); if (overlap.length === keywords.length) return { match: 'exact', skill: rowToSkill(row) }; } } // Tier 2: Domain match if (domain) { const row = d.prepare("SELECT * FROM skills WHERE domain = ? AND status IN ('enable','training') LIMIT 1").get(domain); if (row) return { match: 'semantic', skill: rowToSkill(row) }; } // Tier 2: Partial keyword match (≥50%) if (keywords.length > 0) { const rows = d.prepare("SELECT * FROM skills WHERE status IN ('enable','training')").all(); for (const row of rows) { const sk = safeJsonParse(row.keywords, []); const overlap = keywords.filter(k => sk.includes(k)); if (overlap.length / keywords.length >= 0.5) return { match: 'semantic', skill: rowToSkill(row) }; } } return { match: 'none', skill: null }; } function toPublicInfo(skill) { const { directory, _searchScore, _cache, ...rest } = skill; return rest; } // ═══════════════════════════════════════════════════ // 新增查询接口 // ═══════════════════════════════════════════════════ /** 获取 skill 的版本历史 */ function getVersionHistory(skillName) { const d = getDb(); const row = d.prepare('SELECT skill_id FROM skills WHERE skill_name = ?').get(skillName); if (!row) return []; return d.prepare('SELECT * FROM skill_versions WHERE skill_id = ? ORDER BY created_at DESC').all(row.skill_id); } /** 获取 skill 的下载记录 */ function getDownloadHistory(skillName, { limit = 50 } = {}) { const d = getDb(); const row = d.prepare('SELECT skill_id FROM skills WHERE skill_name = ?').get(skillName); if (!row) return []; return d.prepare('SELECT * FROM skill_downloads WHERE skill_id = ? ORDER BY created_at DESC LIMIT ?').all(row.skill_id, limit); } /** 获取统计信息 */ function getStats() { const d = getDb(); const total = d.prepare('SELECT COUNT(*) as count FROM skills').get().count; const enabled = d.prepare("SELECT COUNT(*) as count FROM skills WHERE status = 'enable'").get().count; const byDomain = d.prepare('SELECT domain, COUNT(*) as count FROM skills GROUP BY domain ORDER BY count DESC').all(); const byType = d.prepare('SELECT type, COUNT(*) as count FROM skills GROUP BY type ORDER BY count DESC').all(); const totalDownloads = d.prepare('SELECT COALESCE(SUM(download_count), 0) as total FROM skills').get().total; const totalUses = d.prepare('SELECT COALESCE(SUM(use_count), 0) as total FROM skills').get().total; return { total, enabled, byDomain, byType, totalDownloads, totalUses }; } // ═══════════════════════════════════════════════════ // 收藏 (Favorites) 功能 // ═══════════════════════════════════════════════════ function addFavorite(skillName, userId) { const d = getDb(); const skill = d.prepare('SELECT skill_id FROM skills WHERE skill_name = ?').get(skillName); if (!skill) throw new Error(`Skill "${skillName}" not found`); d.prepare('INSERT OR IGNORE INTO skill_favorites (skill_id, user_id) VALUES (?, ?)').run(skill.skill_id, userId); d.prepare('UPDATE skills SET favorite_count = (SELECT COUNT(*) FROM skill_favorites WHERE skill_id = ?) WHERE skill_id = ?').run(skill.skill_id, skill.skill_id); return { skillName, userId, favorited: true }; } function removeFavorite(skillName, userId) { const d = getDb(); const skill = d.prepare('SELECT skill_id FROM skills WHERE skill_name = ?').get(skillName); if (!skill) throw new Error(`Skill "${skillName}" not found`); d.prepare('DELETE FROM skill_favorites WHERE skill_id = ? AND user_id = ?').run(skill.skill_id, userId); d.prepare('UPDATE skills SET favorite_count = (SELECT COUNT(*) FROM skill_favorites WHERE skill_id = ?) WHERE skill_id = ?').run(skill.skill_id, skill.skill_id); return { skillName, userId, favorited: false }; } function isFavorited(skillName, userId) { const d = getDb(); const skill = d.prepare('SELECT skill_id FROM skills WHERE skill_name = ?').get(skillName); if (!skill) return false; const row = d.prepare('SELECT 1 FROM skill_favorites WHERE skill_id = ? AND user_id = ?').get(skill.skill_id, userId); return !!row; } function getUserFavorites(userId, { limit = 50, offset = 0 } = {}) { const d = getDb(); const rows = d.prepare(` SELECT s.*, f.created_at as favorited_at FROM skill_favorites f JOIN skills s ON s.skill_id = f.skill_id WHERE f.user_id = ? ORDER BY f.created_at DESC LIMIT ? OFFSET ? `).all(userId, limit, offset); return rows.map(row => ({ ...rowToSkill(row), favoritedAt: row.favorited_at })); } function getFavoriteCount(skillName) { const d = getDb(); const row = d.prepare('SELECT favorite_count FROM skills WHERE skill_name = ?').get(skillName); return row?.favorite_count || 0; } function getUserDownloads(userId, { limit = 50, offset = 0 } = {}) { const d = getDb(); const rows = d.prepare(` SELECT s.*, dl.created_at as downloaded_at, dl.download_version FROM skill_downloads dl JOIN skills s ON s.skill_id = dl.skill_id WHERE dl.user_id = ? ORDER BY dl.created_at DESC LIMIT ? OFFSET ? `).all(userId, limit, offset); return rows.map(row => ({ ...rowToSkill(row), downloadedAt: row.downloaded_at, downloadVersion: row.download_version })); } function getUserTrainedSkills(userId, { limit = 50, offset = 0 } = {}) { const d = getDb(); // 优先基于 user_id 字段查询 (v6.0) const byUserId = d.prepare(` SELECT * FROM skills WHERE user_id = ? AND storage_type = 'trained' ORDER BY last_updated DESC LIMIT ? OFFSET ? `).all(String(userId), limit, offset); if (byUserId.length > 0) { return byUserId.map(row => ({ ...rowToSkill(row), trainedAt: row.last_updated, trainScore: row.current_score })); } // 回退: 从 evolve_runs 查 (兼容旧数据) const rows = d.prepare(` SELECT s.*, e.completed_at as trained_at, e.score, e.status as run_status FROM skill_evolve_runs e JOIN skills s ON s.skill_id = e.skill_id WHERE e.status = 'completed' ORDER BY e.completed_at DESC LIMIT ? OFFSET ? `).all(limit, offset); return rows.map(row => ({ ...rowToSkill(row), trainedAt: row.trained_at, trainScore: row.score })); } module.exports = { getDb, // SkillRegistry 兼容 registerSkill, updateSkill, renameSkill, getSkill, getSkillById, listSkills, countSkills, removeSkill, bumpVersion, incrementUseCount, incrementDownloadCount, recordUpdate, searchSkills, findSkill, toPublicInfo, // 新增 recordDownload, upsertArena, getVersionHistory, getDownloadHistory, getStats, // 收藏 addFavorite, removeFavorite, isFavorited, getUserFavorites, getFavoriteCount, getUserDownloads, getUserTrainedSkills, // 内部工具 skillToRow, rowToSkill, rowToArena, };