cat-shark
37b75c2d21
- 流式查询（SSE 实时展示生成过程） - 三维度数据模型：history（查询）/ words（单词档案）/ roots（词根档案） - 词根拆解与发音拼读训练（词根点击揭晓历史词例） - 词义/同义例句跨查询累积，同义项自动合并 - 历史与词库分页、多维度搜索 - 模型：Qwen3.6-35B-A3B（硅基流动，可 LLM_MODEL 切换）
384 lines
13 KiB
JavaScript
384 lines
13 KiB
JavaScript
import Database from 'better-sqlite3';
|
|
import fs from 'node:fs';
|
|
import path from 'node:path';
|
|
import { fileURLToPath } from 'node:url';
|
|
|
|
const __dirname = path.dirname(fileURLToPath(import.meta.url));
|
|
const dataDir = path.join(__dirname, 'data');
|
|
if (!fs.existsSync(dataDir)) fs.mkdirSync(dataDir, { recursive: true });
|
|
|
|
const db = new Database(path.join(dataDir, 'app.db'));
|
|
db.pragma('journal_mode = WAL');
|
|
|
|
// 破坏性变更：重建表结构（旧数据已清除）
|
|
db.exec(`
|
|
CREATE TABLE IF NOT EXISTS history (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
sentence TEXT NOT NULL,
|
|
words TEXT NOT NULL, -- 查询的单词，JSON 数组
|
|
result TEXT NOT NULL, -- LLM 结构化结果，JSON（含词根拆解）
|
|
raw_result TEXT,
|
|
created_at TEXT NOT NULL DEFAULT (datetime('now', 'localtime')),
|
|
updated_at TEXT NOT NULL DEFAULT (datetime('now', 'localtime'))
|
|
);
|
|
|
|
-- 词库（按单词维度维护，跨句子累积）
|
|
CREATE TABLE IF NOT EXISTS words (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
word TEXT NOT NULL UNIQUE,
|
|
phonetic TEXT, -- 整词音标
|
|
roots TEXT NOT NULL DEFAULT '[]',
|
|
-- 词根拆解: [{part, type:前缀|词根|后缀|音节, phonetic, meaning}]
|
|
senses TEXT NOT NULL DEFAULT '[]',
|
|
-- 释义（同义项合并）: [{sense, examples: [{en, zh, from_sentence}]}]
|
|
created_at TEXT NOT NULL DEFAULT (datetime('now', 'localtime')),
|
|
updated_at TEXT NOT NULL DEFAULT (datetime('now', 'localtime'))
|
|
);
|
|
|
|
-- 词根库（按词根维度维护，跨单词累积）
|
|
CREATE TABLE IF NOT EXISTS roots (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
root TEXT NOT NULL UNIQUE, -- 规范化键：去连字符/空格、小写，如 'lysis'
|
|
display TEXT NOT NULL, -- 展示形（如 "lysis"、"-tion"），以最近出现为准
|
|
type TEXT, -- 前缀 / 词根 / 后缀 / 音节
|
|
phonetic TEXT, -- 发音
|
|
words TEXT NOT NULL DEFAULT '[]',
|
|
-- 包含该词根的词: [{word, phonetic, meaning}]
|
|
-- meaning = 该词中体现的词根含义（允许不同词里有差异，展示时并列，帮助大脑抽象）
|
|
created_at TEXT NOT NULL DEFAULT (datetime('now', 'localtime')),
|
|
updated_at TEXT NOT NULL DEFAULT (datetime('now', 'localtime'))
|
|
);
|
|
`);
|
|
|
|
// 迁移：words 表补充 root_keys、search_text 列
|
|
// search_text = 单词 + 义项标题 + 词根拆解（不含例句正文，避免例句里的无关词干扰检索）
|
|
function buildWordSearchText(word, senses, roots) {
|
|
return [
|
|
word,
|
|
...(senses || []).map((s) => s.sense),
|
|
...(roots || []).flatMap((r) => [r.part, normalizeRootKey(r.part), r.meaning]),
|
|
]
|
|
.filter(Boolean)
|
|
.join(' ')
|
|
.toLowerCase();
|
|
}
|
|
{
|
|
const cols = db.prepare("PRAGMA table_info(words)").all().map((c) => c.name);
|
|
if (!cols.includes('root_keys')) {
|
|
db.exec("ALTER TABLE words ADD COLUMN root_keys TEXT NOT NULL DEFAULT '[]'");
|
|
}
|
|
if (!cols.includes('search_text')) {
|
|
db.exec("ALTER TABLE words ADD COLUMN search_text TEXT NOT NULL DEFAULT ''");
|
|
}
|
|
// 回填历史数据的 search_text
|
|
const rows = db.prepare('SELECT word, senses, roots FROM words').all();
|
|
const upd = db.prepare('UPDATE words SET search_text = ? WHERE word = ?');
|
|
for (const r of rows) {
|
|
upd.run(
|
|
buildWordSearchText(
|
|
r.word,
|
|
JSON.parse(r.senses),
|
|
JSON.parse(r.roots)
|
|
),
|
|
r.word
|
|
);
|
|
}
|
|
}
|
|
|
|
const stmts = {
|
|
// ---- history ----
|
|
insert: db.prepare(
|
|
'INSERT INTO history (sentence, words, result, raw_result) VALUES (?, ?, ?, ?)'
|
|
),
|
|
get: db.prepare('SELECT * FROM history WHERE id = ?'),
|
|
update: db.prepare(
|
|
`UPDATE history SET sentence = @sentence, words = @words, result = @result,
|
|
updated_at = datetime('now', 'localtime') WHERE id = @id`
|
|
),
|
|
del: db.prepare('DELETE FROM history WHERE id = ?'),
|
|
|
|
// ---- words（词库）----
|
|
getWord: db.prepare('SELECT * FROM words WHERE word = ?'),
|
|
insertWord: db.prepare(
|
|
`INSERT INTO words (word, phonetic, roots, senses, root_keys, search_text) VALUES (@word, @phonetic, @roots, @senses, @root_keys, @search_text)`
|
|
),
|
|
updateWord: db.prepare(
|
|
`UPDATE words SET phonetic = @phonetic, roots = @roots, senses = @senses,
|
|
root_keys = @root_keys, search_text = @search_text,
|
|
updated_at = datetime('now', 'localtime') WHERE id = @id`
|
|
),
|
|
|
|
// ---- roots（词根库）----
|
|
getRoot: db.prepare('SELECT * FROM roots WHERE root = ?'),
|
|
insertRoot: db.prepare(
|
|
`INSERT INTO roots (root, display, type, phonetic, words) VALUES (@root, @display, @type, @phonetic, @words)`
|
|
),
|
|
updateRoot: db.prepare(
|
|
`UPDATE roots SET display = @display, type = @type, phonetic = @phonetic, words = @words,
|
|
updated_at = datetime('now', 'localtime') WHERE id = @id`
|
|
),
|
|
delRoot: db.prepare('DELETE FROM roots WHERE id = ?'),
|
|
};
|
|
|
|
/* ---------- history ---------- */
|
|
|
|
export function saveRecord({ sentence, words, result, raw_result }) {
|
|
const info = stmts.insert.run(
|
|
sentence,
|
|
JSON.stringify(words),
|
|
JSON.stringify(result),
|
|
raw_result ?? null
|
|
);
|
|
return getRecord(info.lastInsertRowid);
|
|
}
|
|
|
|
/* 搜索：LIKE 通配符转义（% _ 视为普通字符） */
|
|
function likeTerm(q) {
|
|
return '%' + String(q || '').replace(/[%_\\]/g, '\\$&') + '%';
|
|
}
|
|
|
|
function pagedQuery({ baseCols, from, orderBy = 'id DESC', whereCols, page, size, q, postProcess }) {
|
|
const hasSearch = q && String(q).trim();
|
|
const where = hasSearch
|
|
? 'WHERE ' + whereCols.map((c) => `${c} LIKE @q ESCAPE '\\'`).join(' OR ')
|
|
: '';
|
|
const total = db
|
|
.prepare(`SELECT COUNT(*) AS n FROM ${from} ${where}`)
|
|
.get(hasSearch ? { q: likeTerm(q) } : {}).n;
|
|
const items = db
|
|
.prepare(
|
|
`SELECT ${baseCols} FROM ${from} ${where} ORDER BY ${orderBy} LIMIT @limit OFFSET @offset`
|
|
)
|
|
.all({
|
|
...(hasSearch ? { q: likeTerm(q) } : {}),
|
|
limit: size,
|
|
offset: (page - 1) * size,
|
|
})
|
|
.map(postProcess);
|
|
return {
|
|
items,
|
|
total,
|
|
q: hasSearch ? String(q).trim() : '',
|
|
page,
|
|
size,
|
|
total_pages: Math.max(1, Math.ceil(total / size)),
|
|
};
|
|
}
|
|
|
|
export function listRecords(page = 1, size = 10, q = '') {
|
|
return pagedQuery({
|
|
baseCols: 'id, sentence, words, created_at, updated_at',
|
|
from: 'history',
|
|
whereCols: ['sentence', 'words'], // 匹配原句或查询的单词
|
|
page,
|
|
size,
|
|
q,
|
|
postProcess: (r) => ({ ...r, words: JSON.parse(r.words) }),
|
|
});
|
|
}
|
|
|
|
export function getRecord(id) {
|
|
const r = stmts.get.get(id);
|
|
if (!r) return null;
|
|
return { ...r, words: JSON.parse(r.words), result: JSON.parse(r.result) };
|
|
}
|
|
|
|
export function updateRecord(id, { sentence, words, result }) {
|
|
const info = stmts.update.run({
|
|
id,
|
|
sentence,
|
|
words: JSON.stringify(words),
|
|
result: JSON.stringify(result),
|
|
});
|
|
if (info.changes === 0) return null;
|
|
return getRecord(id);
|
|
}
|
|
|
|
export function deleteRecord(id) {
|
|
const info = stmts.del.run(id);
|
|
return info.changes > 0;
|
|
}
|
|
|
|
/* ---------- words（词库）---------- */
|
|
|
|
export function getWord(word) {
|
|
const r = stmts.getWord.get(word);
|
|
if (!r) return null;
|
|
return { ...r, roots: JSON.parse(r.roots), senses: JSON.parse(r.senses) };
|
|
}
|
|
|
|
export function listWords(page = 1, size = 10, q = '') {
|
|
return pagedQuery({
|
|
baseCols: 'word, phonetic, roots, senses, created_at, updated_at',
|
|
from: 'words',
|
|
orderBy: 'updated_at DESC',
|
|
whereCols: ['search_text'], // 匹配单词、释义或词根（不含例句正文）
|
|
page,
|
|
size,
|
|
q,
|
|
postProcess: (r) => {
|
|
const senses = JSON.parse(r.senses);
|
|
const occurrences = senses.reduce(
|
|
(n, s) => n + (s.examples || []).filter((e) => e.from_sentence).length,
|
|
0
|
|
);
|
|
return {
|
|
...r,
|
|
roots: JSON.parse(r.roots),
|
|
senses,
|
|
sense_count: senses.length,
|
|
occurrence_count: occurrences,
|
|
};
|
|
},
|
|
});
|
|
}
|
|
|
|
/**
|
|
* 按"单词维度"合并查询结果：
|
|
* - 词根拆解：首次创建时写入，之后保留（除非原来为空）
|
|
* - 原句语境对应已有释义（context_sense_id 有效）→ 该释义下追加"来自原句"的记录
|
|
* - 语境为新释义 → 新增义项；其他新释义也并入（按释义文案去重）
|
|
*/
|
|
export function upsertWordData(w, sentence, sentenceTranslation) {
|
|
if (!w?.word) return null;
|
|
const existing = stmts.getWord.get(w.word);
|
|
let senses = existing ? JSON.parse(existing.senses) : [];
|
|
// 原句记录
|
|
const occ = {
|
|
en: sentence,
|
|
zh: sentenceTranslation || '',
|
|
from_sentence: true,
|
|
};
|
|
|
|
// 查询时生成的同义例句：与原句一起并入词库
|
|
const generated = (w.context_examples || [])
|
|
.map((e) => ({ en: (e.en || '').trim(), zh: (e.zh || '').trim() }))
|
|
.filter((e) => e.en);
|
|
|
|
// 按英文句子去重（原句与例句可重复，避免同一句重复堆积）
|
|
const addUnique = (list, ex) => {
|
|
if (!ex.en) return;
|
|
if (!list.some((x) => x.en === ex.en)) list.push(ex);
|
|
};
|
|
|
|
const ctxId = w.context_sense_id;
|
|
if (
|
|
typeof ctxId === 'number' &&
|
|
Number.isInteger(ctxId) &&
|
|
ctxId >= 0 &&
|
|
ctxId < senses.length
|
|
) {
|
|
// 语境对应已有释义：原句 + 本次生成的同义例句一起并入
|
|
const s = senses[ctxId];
|
|
s.examples = s.examples || [];
|
|
addUnique(s.examples, occ);
|
|
generated.forEach((ex) => addUnique(s.examples, ex));
|
|
if (w.context_sense) s.sense = w.context_sense; // 释义文案以最新为准
|
|
} else if (w.context_sense) {
|
|
senses.push({ sense: w.context_sense, examples: [occ, ...generated] });
|
|
}
|
|
|
|
// 并入其他释义：已有同文案释义 → 例句并入；否则新增释义
|
|
for (const os of w.other_senses || []) {
|
|
if (!os?.sense) continue;
|
|
const osExamples = (os.examples || [])
|
|
.map((e) => ({ en: (e.en || '').trim(), zh: (e.zh || '').trim() }))
|
|
.filter((e) => e.en);
|
|
const matched = senses.find((s) => s.sense === os.sense);
|
|
if (matched) {
|
|
matched.examples = matched.examples || [];
|
|
osExamples.forEach((ex) => addUnique(matched.examples, ex));
|
|
} else {
|
|
senses.push({ sense: os.sense, examples: osExamples });
|
|
}
|
|
}
|
|
|
|
// 词根拆解：优先用最新结果；新词必写，旧词仅在原来为空时补写
|
|
|
|
// 词根拆解：优先用最新结果；新词必写，旧词仅在原来为空时补写
|
|
const oldRoots = existing ? JSON.parse(existing.roots) : [];
|
|
const roots = w.roots?.length ? w.roots : oldRoots;
|
|
|
|
// ---- 词根维度同步 ----
|
|
const newKeys = [...new Set(roots.map((r) => normalizeRootKey(r.part)).filter(Boolean))];
|
|
if (existing) {
|
|
// 单词重新拆解后，从不再包含的旧词根下摘除该词，避免幽灵条目
|
|
const oldKeys = JSON.parse(existing.root_keys || '[]');
|
|
for (const k of oldKeys) {
|
|
if (newKeys.includes(k)) continue;
|
|
const r = stmts.getRoot.get(k);
|
|
if (!r) continue;
|
|
const remaining = JSON.parse(r.words).filter((e) => e.word !== w.word);
|
|
if (remaining.length === 0) stmts.delRoot.run(r.id);
|
|
else stmts.updateRoot.run({ ...r, words: JSON.stringify(remaining) });
|
|
}
|
|
}
|
|
for (const r of roots) {
|
|
// 传入该词的全部释义，词根面板点击单词时一并揭晓
|
|
upsertRootData(r, w.word, w.phonetic || '', senses.map((s) => s.sense));
|
|
}
|
|
|
|
const row = {
|
|
word: w.word,
|
|
phonetic: w.phonetic || existing?.phonetic || '',
|
|
roots: JSON.stringify(roots),
|
|
senses: JSON.stringify(senses),
|
|
root_keys: JSON.stringify(newKeys),
|
|
search_text: buildWordSearchText(w.word, senses, roots),
|
|
id: existing?.id,
|
|
};
|
|
if (existing) stmts.updateWord.run(row);
|
|
else stmts.insertWord.run(row);
|
|
return getWord(w.word);
|
|
}
|
|
|
|
/* ---------- roots（词根库）---------- */
|
|
|
|
export function normalizeRootKey(part) {
|
|
return String(part || '').replace(/[^a-zA-Z]/g, '').toLowerCase();
|
|
}
|
|
|
|
export function getRoot(rootKey) {
|
|
const r = stmts.getRoot.get(rootKey);
|
|
if (!r) return null;
|
|
return { ...r, words: JSON.parse(r.words) };
|
|
}
|
|
|
|
/**
|
|
* 按词根维度累积：同一词根下追加包含它的单词（含该词中体现的含义）。
|
|
* 含义不强行统一——不同词中的含义变体并列展示，让学习者自行抽象。
|
|
*/
|
|
export function upsertRootData(
|
|
{ part, type, phonetic, meaning },
|
|
word,
|
|
wordPhonetic,
|
|
wordSenses = []
|
|
) {
|
|
const key = normalizeRootKey(part);
|
|
if (!key) return null;
|
|
const existing = stmts.getRoot.get(key);
|
|
let words = existing ? JSON.parse(existing.words) : [];
|
|
// word_senses: 该单词自身的释义列表（词根面板中点击单词时一并揭晓）
|
|
const entry = {
|
|
word,
|
|
phonetic: wordPhonetic || '',
|
|
meaning: meaning || '',
|
|
word_senses: (wordSenses || []).filter(Boolean),
|
|
};
|
|
const idx = words.findIndex((e) => e.word === word);
|
|
if (idx !== -1) words[idx] = entry; // 同一单词以最新拆解覆盖
|
|
else words.push(entry);
|
|
|
|
const row = {
|
|
id: existing?.id,
|
|
root: key,
|
|
display: part,
|
|
type: type || existing?.type || '',
|
|
phonetic: phonetic || existing?.phonetic || '',
|
|
words: JSON.stringify(words),
|
|
};
|
|
if (existing) stmts.updateRoot.run(row);
|
|
else stmts.insertRoot.run(row);
|
|
return getRoot(key);
|
|
}
|