# -*- coding: utf-8 -*- """ 分片2 技术债①:questions FTS5 全文索引迁移(幂等可重复执行)。 - 虚拟表 questions_fts(外部内容表 content='questions', rowid=id) 索引字段:title + option_a..e + analysis(题干+选项+解析) - 3 个触发器 AFTER INSERT/DELETE/UPDATE 同步增删改 - 用 rebuild 一次性回填;仅对 is_deleted=0 的在库题,搜索时再 JOIN 过滤 注意:本脚本只做“建表/触发器/索引/回填”,可逆(DROP 即回退)。 """ import sqlite3, os, sys DB = os.path.abspath(os.path.join(os.path.dirname(__file__), '..', 'backend', 'data', 'shuili_exam.db')) DDL_DROP = [ "DROP TRIGGER IF EXISTS questions_fts_au", "DROP TRIGGER IF EXISTS questions_fts_ad", "DROP TRIGGER IF EXISTS questions_fts_ai", "DROP TABLE IF EXISTS questions_fts", ] DDL = [ # 1) 虚拟表(外部内容表);trigram 分词器支持中文子串匹配(>=3字) """CREATE VIRTUAL TABLE IF NOT EXISTS questions_fts USING fts5( title, option_a, option_b, option_c, option_d, option_e, analysis, content='questions', content_rowid='id', tokenize='trigram' )""", # 2) INSERT 触发器 """CREATE TRIGGER IF NOT EXISTS questions_fts_ai AFTER INSERT ON questions BEGIN INSERT INTO questions_fts(rowid, title, option_a, option_b, option_c, option_d, option_e, analysis) VALUES (new.id, new.title, new.option_a, new.option_b, new.option_c, new.option_d, new.option_e, new.analysis); END""", # 3) DELETE 触发器(外部内容表须用 'delete' command 删旧索引) """CREATE TRIGGER IF NOT EXISTS questions_fts_ad AFTER DELETE ON questions BEGIN INSERT INTO questions_fts(questions_fts, rowid, title, option_a, option_b, option_c, option_d, option_e, analysis) VALUES('delete', old.id, old.title, old.option_a, old.option_b, old.option_c, old.option_d, old.option_e, old.analysis); END""", # 4) UPDATE 触发器(先删旧再插新) """CREATE TRIGGER IF NOT EXISTS questions_fts_au AFTER UPDATE ON questions BEGIN INSERT INTO questions_fts(questions_fts, rowid, title, option_a, option_b, option_c, option_d, option_e, analysis) VALUES('delete', old.id, old.title, old.option_a, old.option_b, old.option_c, old.option_d, old.option_e, old.analysis); INSERT INTO questions_fts(rowid, title, option_a, option_b, option_c, option_d, option_e, analysis) VALUES (new.id, new.title, new.option_a, new.option_b, new.option_c, new.option_d, new.option_e, new.analysis); END""", ] def main(): c = sqlite3.connect(DB) c.execute("PRAGMA journal_mode=WAL;") for sql in DDL_DROP: c.execute(sql) for sql in DDL: c.execute(sql) c.commit() # 全量重建索引(从 questions 外部内容表回填) c.execute("INSERT INTO questions_fts(questions_fts) VALUES('rebuild');") c.commit() n_fts = c.execute("SELECT COUNT(*) FROM questions_fts").fetchone()[0] n_live = c.execute("SELECT COUNT(*) FROM questions WHERE is_deleted=0").fetchone()[0] n_dead = c.execute("SELECT COUNT(*) FROM questions WHERE is_deleted=1").fetchone()[0] print(f'[FTS] rows indexed={n_fts} live(is_deleted=0)={n_live} deleted(is_deleted=1)={n_dead}') # 验证:搜索命中里绝不出现 is_deleted=1 sample = c.execute(""" SELECT COUNT(*) FROM questions_fts f JOIN questions q ON q.id=f.rowid WHERE questions_fts MATCH '代位权' AND q.is_deleted=1 """).fetchone()[0] live_hit = c.execute(""" SELECT COUNT(*) FROM questions_fts f JOIN questions q ON q.id=f.rowid WHERE questions_fts MATCH '代位权' AND q.is_deleted=0 """).fetchone()[0] print(f'[FTS] MATCH代位权 -> deleted_hit={sample} (必须0) live_hit={live_hit}') c.close() print('[FTS] migration done.') if __name__ == '__main__': main()