99 lines
3.9 KiB
JavaScript
99 lines
3.9 KiB
JavaScript
'use strict';
|
||
|
||
const { devWarn } = require('./log');
|
||
|
||
// clips -> clips_fts 的同步触发器。v2 / v3 共用。
|
||
const FTS_TRIGGERS = `
|
||
CREATE TRIGGER IF NOT EXISTS clips_ai AFTER INSERT ON clips BEGIN
|
||
INSERT INTO clips_fts(rowid, text) VALUES (new.id, new.text);
|
||
END;
|
||
CREATE TRIGGER IF NOT EXISTS clips_ad AFTER DELETE ON clips BEGIN
|
||
INSERT INTO clips_fts(clips_fts, rowid, text) VALUES('delete', old.id, old.text);
|
||
END;
|
||
-- 只在 text 真的变了才重建索引。没有这个 WHEN 的话，每次
|
||
-- "UPDATE clips SET top_at = ?"（也就是每次复制/置顶）都会白白
|
||
-- 把该行从 FTS 删掉再插回去。
|
||
CREATE TRIGGER IF NOT EXISTS clips_au AFTER UPDATE ON clips
|
||
WHEN old.text IS NOT new.text BEGIN
|
||
INSERT INTO clips_fts(clips_fts, rowid, text) VALUES('delete', old.id, old.text);
|
||
INSERT INTO clips_fts(rowid, text) VALUES (new.id, new.text);
|
||
END;
|
||
`;
|
||
|
||
// STEPS[i] upgrades the schema from version (i + 1) to (i + 2).
|
||
// STEPS[0] therefore is the v1 -> v2 step.
|
||
const STEPS = [
|
||
// v1 -> v2: add FTS5 table + triggers (idempotent).
|
||
function v1_to_v2(db) {
|
||
db.exec(`
|
||
CREATE VIRTUAL TABLE IF NOT EXISTS clips_fts USING fts5(text, content='clips', content_rowid='id');
|
||
${FTS_TRIGGERS}
|
||
INSERT INTO clips_fts(rowid, text) SELECT id, text FROM clips WHERE text IS NOT NULL;
|
||
`);
|
||
},
|
||
|
||
// v2 -> v3: 换成 trigram 分词器。
|
||
//
|
||
// v2 用的是默认的 unicode61 分词器，它把一整串连续的中文当成**一个** token，
|
||
// 而 FTS5 的 MATCH 是整 token 匹配、不做子串匹配。结果：搜"公园"在
|
||
// "今天去公园散步"里一条都搜不到 —— 对一个中文剪贴板工具来说等于搜索全废。
|
||
// （v2 这一步之前因为索引下标算错从没真正执行过，所以线上一直悄悄退回 LIKE，
|
||
// 反而"正好"是对的；把 v2 修好之后这个坑才会暴露出来。）
|
||
//
|
||
// trigram 分词器按 3 字符滑窗切分，支持子串匹配，中文可用。
|
||
// 代价：≤2 字符的查询它匹配不到，这部分由调用方退回 LIKE 处理。
|
||
function v2_to_v3(db) {
|
||
db.exec(`
|
||
DROP TRIGGER IF EXISTS clips_ai;
|
||
DROP TRIGGER IF EXISTS clips_ad;
|
||
DROP TRIGGER IF EXISTS clips_au;
|
||
DROP TABLE IF EXISTS clips_fts;
|
||
CREATE VIRTUAL TABLE clips_fts USING fts5(
|
||
text, content='clips', content_rowid='id', tokenize='trigram'
|
||
);
|
||
${FTS_TRIGGERS}
|
||
INSERT INTO clips_fts(rowid, text) SELECT id, text FROM clips WHERE text IS NOT NULL;
|
||
`);
|
||
},
|
||
];
|
||
|
||
/**
|
||
* Applies each pending schema step in order.
|
||
*
|
||
* The DB's own `PRAGMA user_version` is the source of truth for what has already
|
||
* been applied — not the config file, which only records the version the app
|
||
* *wants*. Passing a config-derived `fromVersion` would re-run every step on
|
||
* every launch (re-indexing the whole FTS table each time the app starts).
|
||
*
|
||
* Returns the version actually reached, so callers can tell whether a step was
|
||
* skipped because of an error.
|
||
*/
|
||
function migrate(db, fromVersion, toVersion) {
|
||
let current = fromVersion;
|
||
try {
|
||
const row = db.pragma('user_version', { simple: true });
|
||
if (typeof row === 'number' && row > 0) current = row;
|
||
} catch (e) {
|
||
// Non-sqlite stub (tests) — fall back to the caller-supplied version.
|
||
}
|
||
|
||
for (let v = current + 1; v <= toVersion; v++) {
|
||
const step = STEPS[v - 2];
|
||
if (!step) continue;
|
||
try {
|
||
step(db);
|
||
current = v;
|
||
try { db.pragma(`user_version = ${v}`); } catch (e) {}
|
||
} catch (e) {
|
||
// Most likely FTS5 is not compiled into this SQLite build. Leave the
|
||
// version marker where it is so a later launch can retry, and let the
|
||
// caller fall back to LIKE-based search.
|
||
devWarn('[migrate] step', v, 'failed, leaving schema at v' + current + ':', e.message);
|
||
break;
|
||
}
|
||
}
|
||
return current;
|
||
}
|
||
|
||
module.exports = { migrate, STEPS };
|