1
#!/usr/bin/env bash
2
# repair_fts.sh - 修复 FTS5 索引
3
# 用法: bash repair_fts.sh <数据库路径> <FTS表名> <主表名> <内容列>
4

5
set -euo pipefail
6

7
DB="${1:?用法: bash repair_fts.sh <db_path> <fts_table> <main_table> <content_columns>}"
8
FTS_TABLE="${2:?请指定 FTS5 虚拟表名}"
9
MAIN_TABLE="${3:?请指定主表名}"
10
CONTENT_COLS="${4:?请指定内容列名，如 'role, content'}"
11

12
BACKUP="${DB}.bak.$(date +%Y%m%d%H%M%S)"
13

14
echo "=== SQLite FTS5 索引修复工具 ==="
15
echo ""
16
echo "数据库: $DB"
17
echo "FTS表:  $FTS_TABLE"
18
echo "主表:   $MAIN_TABLE"
19
echo "列:     $CONTENT_COLS"
20
echo ""
21

22
# 1. 备份
23
echo "[1/5] 备份数据库..."
24
cp "$DB" "$BACKUP"
25
echo "  备份已保存到: $BACKUP"
26
echo ""
27

28
# 2. 诊断
29
echo "[2/5] 诊断当前状态..."
30
echo "  主表行数:"
31
sqlite3 "$DB" "SELECT COUNT(*) FROM $MAIN_TABLE;"
32
echo "  FTS5 完整性检查:"
33
RESULT=$(sqlite3 "$DB" "SELECT * FROM ${FTS_TABLE}('integrity-check');")
34
if [ -z "$RESULT" ]; then
35
    echo "  ✓ FTS5 索引完好"
36
    read -p "  索引看起来正常，是否仍要继续？(y/N) " -n 1 -r
37
    echo
38
    [[ $REPLY =~ ^[Yy]$ ]] || exit 0
39
else
40
    echo "  ✗ 发现问题: $RESULT"
41
fi
42
echo ""
43

44
# 3. 删除旧 FTS 表及其相关对象
45
echo "[3/5] 删除旧的 FTS 表..."
46
sqlite3 "$DB" <<SQL
47
-- 删除触发器（如果存在）
48
SELECT 'DROP TRIGGER IF EXISTS ' || name || ';'
49
FROM sqlite_master WHERE type='trigger' AND tbl_name='$MAIN_TABLE'
50
AND (name LIKE '%fts%' OR name LIKE '%ai%' OR name LIKE '%ad%' OR name LIKE '%au%');
51

52
-- 列出将被删除的相关表
53
SELECT '  将删除: ' || name FROM sqlite_master
54
WHERE type='table' AND name LIKE '${FTS_TABLE}%';
55
SQL
56

57
sqlite3 "$DB" <<SQL
58
DROP TABLE IF EXISTS $FTS_TABLE;
59
DROP TABLE IF EXISTS ${FTS_TABLE}_data;
60
DROP TABLE IF EXISTS ${FTS_TABLE}_idx;
61
DROP TABLE IF EXISTS ${FTS_TABLE}_content;
62
DROP TABLE IF EXISTS ${FTS_TABLE}_docsize;
63
DROP TABLE IF EXISTS ${FTS_TABLE}_config;
64
SQL
65
echo "  ✓ 已删除"
66
echo ""
67

68
# 4. 重建 FTS 表和索引
69
echo "[4/5] 重建 FTS 表..."
70
sqlite3 "$DB" <<SQL
71
CREATE VIRTUAL TABLE IF NOT EXISTS ${FTS_TABLE} USING fts5($CONTENT_COLS, content='$MAIN_TABLE', content_rowid='id');
72
INSERT INTO ${FTS_TABLE}(${FTS_TABLE}) VALUES('rebuild');
73
SQL
74
echo "  ✓ 已重建"
75
echo ""
76

77
# 5. 验证
78
echo "[5/5] 验证..."
79
RESULT=$(sqlite3 "$DB" "SELECT * FROM ${FTS_TABLE}('integrity-check');")
80
if [ -z "$RESULT" ]; then
81
    echo "  ✓ FTS5 索引已修复！"
82
    COUNT=$(sqlite3 "$DB" "SELECT COUNT(*) FROM $MAIN_TABLE;")
83
    echo "  ✓ 索引覆盖 $COUNT 条记录"
84
else
85
    echo "  ✗ 仍有问题: $RESULT"
86
    echo "  请从备份恢复: cp $BACKUP $DB"
87
    exit 1
88
fi
89

90
echo ""
91
echo "=== 修复完成 ==="