152 lines
6.4 KiB
Bash
Executable File
152 lines
6.4 KiB
Bash
Executable File
#!/usr/bin/env bash
|
||
# 图片索引（cache.db）迁移：为缓存表补唯一约束与查询索引。
|
||
#
|
||
# 设计约束：
|
||
# * 默认 dry-run，只读检查；必须显式 --apply 才执行写入。
|
||
# * 必须显式 --db 指定数据库文件，不读取任何运行时配置，也不推断生产路径。
|
||
# * 写库用 sqlite3 -cmd '.bail on'，任一语句出错立即以非零码退出，事务不提交。
|
||
# * 只以读写模式打开目标库（文件必须已存在，不会新建）；dry-run 全用 -readonly。
|
||
# * 备份用 SQLite 自身的 .backup（online backup API），而不是 cp：
|
||
# cp 会漏掉 WAL 里尚未 checkpoint 的已提交内容。
|
||
# * 备份后立即校验备份可读且含两张缓存表，失败则中止并删除无效备份。
|
||
# * 不健康（不完整/冲突）的 gid 整组索引会被删除，由下一次在线阅读重新抓取。
|
||
# * 唯一约束建在唯一索引上：若清理后仍有重复，建索引会失败并整体回滚。
|
||
#
|
||
# 用法：
|
||
# scripts/migrate_image_index_cache.sh --db /tmp/cache-copy.db # dry-run（只读）
|
||
# scripts/migrate_image_index_cache.sh --db /tmp/cache-copy.db --apply # 执行迁移
|
||
set -euo pipefail
|
||
|
||
SCRIPT_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"
|
||
SQL_FILE="$SCRIPT_DIR/migrate_image_index_cache.sql"
|
||
|
||
db_path=""
|
||
apply="no"
|
||
|
||
usage() {
|
||
sed -n '2,30p' "${BASH_SOURCE[0]}" | sed 's/^# \{0,1\}//'
|
||
}
|
||
|
||
while [[ $# -gt 0 ]]; do
|
||
case "$1" in
|
||
--db) db_path="${2:-}"; shift 2 ;;
|
||
--apply) apply="yes"; shift ;;
|
||
-h|--help) usage; exit 0 ;;
|
||
*)
|
||
echo "未知参数: $1" >&2
|
||
exit 2 ;;
|
||
esac
|
||
done
|
||
|
||
if [[ -z "$db_path" ]]; then
|
||
echo "必须显式指定缓存库: --db <cache.db>；本脚本不会自动推断数据库位置。" >&2
|
||
exit 2
|
||
fi
|
||
|
||
if [[ ! -f "$db_path" ]]; then
|
||
echo "数据库不存在: $db_path" >&2
|
||
exit 2
|
||
fi
|
||
|
||
if [[ ! -f "$SQL_FILE" ]]; then
|
||
echo "缺少 SQL: $SQL_FILE" >&2
|
||
exit 2
|
||
fi
|
||
|
||
if ! command -v sqlite3 >/dev/null 2>&1; then
|
||
echo "缺少 sqlite3 命令行工具" >&2
|
||
exit 2
|
||
fi
|
||
|
||
if [[ "$(sqlite3 -readonly "$db_path" \
|
||
"select count(*) from sqlite_master where type='table' and name in ('gidToKey','ImageKeyCache');")" != "2" ]]; then
|
||
echo "缓存表缺失：$db_path 中找不到 gidToKey 与 ImageKeyCache。" >&2
|
||
exit 2
|
||
fi
|
||
|
||
# 体检统计，与 SQL 里实际清理的范围一致：
|
||
# bad_gids 将被整组删除的 gid 个数（去重），覆盖：
|
||
# 重复头部 / 行数不符 / 页码去重后不足 / 页码区间异常 /
|
||
# 空 imgkey / 空 key / 非法 pages
|
||
# bad_gid_rows 这些 gid 占用的头部行数（重复头部的 gid 会多于 1 行）
|
||
# duplicate_pages 重复的 (gid,page) 组数，仅用于展示，会随所属 gid 一并清理
|
||
# orphan_gids 有页 key 但无头部记录的 gid 个数
|
||
read_stats() {
|
||
sqlite3 -readonly "$db_path" "
|
||
select 'header_rows', count(*) from gidToKey;
|
||
select 'page_rows', count(*) from ImageKeyCache;
|
||
select 'duplicate_gids', count(*) from (select gid from gidToKey group by gid having count(*) > 1);
|
||
select 'duplicate_pages', count(*) from (select gid,page from ImageKeyCache group by gid,page having count(*) > 1);
|
||
select 'bad_gids', count(distinct g.gid) from gidToKey g
|
||
where g.pages is null or g.pages <= 0
|
||
or g.key is null or trim(g.key) = ''
|
||
or (select count(*) from ImageKeyCache i where i.gid = g.gid) <> g.pages
|
||
or (select count(distinct i.page) from ImageKeyCache i where i.gid = g.gid) <> g.pages
|
||
or (select min(i.page) from ImageKeyCache i where i.gid = g.gid) <> 1
|
||
or (select max(i.page) from ImageKeyCache i where i.gid = g.gid) <> g.pages
|
||
or exists (select 1 from ImageKeyCache i
|
||
where i.gid = g.gid and (i.imgkey is null or trim(i.imgkey) = ''));
|
||
select 'bad_gid_rows', coalesce(sum(c), 0) from (
|
||
select count(*) as c from gidToKey g where g.gid in (
|
||
select g2.gid from gidToKey g2 where g2.pages is null or g2.pages <= 0
|
||
or g2.key is null or trim(g2.key) = ''
|
||
or (select count(*) from ImageKeyCache i where i.gid = g2.gid) <> g2.pages
|
||
or (select count(distinct i.page) from ImageKeyCache i where i.gid = g2.gid) <> g2.pages
|
||
or (select min(i.page) from ImageKeyCache i where i.gid = g2.gid) <> 1
|
||
or (select max(i.page) from ImageKeyCache i where i.gid = g2.gid) <> g2.pages
|
||
or exists (select 1 from ImageKeyCache i
|
||
where i.gid = g2.gid and (i.imgkey is null or trim(i.imgkey) = '')))
|
||
group by g.gid);
|
||
select 'orphan_gids', count(distinct gid) from ImageKeyCache
|
||
where gid not in (select gid from gidToKey);
|
||
select 'strict_index', count(*) from sqlite_master
|
||
where type='index' and name='ux_ImageKeyCache_gid_page';
|
||
"
|
||
}
|
||
|
||
mode_label="dry-run（只读）"
|
||
if [[ "$apply" == "yes" ]]; then mode_label="apply（读写）"; fi
|
||
|
||
echo "=== 缓存库图片索引迁移（$mode_label） ==="
|
||
echo "数据库: $db_path"
|
||
echo "--- 迁移前体检 ---"
|
||
read_stats | while IFS='|' read -r name value; do
|
||
printf ' %-16s %s\n' "$name" "$value"
|
||
done
|
||
|
||
if [[ "$apply" != "yes" ]]; then
|
||
echo
|
||
echo "dry-run：未做任何写入。确认无误后加 --apply 执行迁移。"
|
||
echo "提示：请先在 SQLite 副本上演练，副本应这样生成（含 WAL 内容）："
|
||
echo " sqlite3 -readonly <生产库> \".backup '<副本路径>'\""
|
||
exit 0
|
||
fi
|
||
|
||
# ---- 备份：SQLite online backup，包含 WAL 中已提交但未 checkpoint 的内容 ----
|
||
backup="$db_path.before-image-index.$(date -u +%Y%m%dT%H%M%SZ)"
|
||
if ! sqlite3 "$db_path" ".backup '$backup'"; then
|
||
echo "备份失败，未做任何修改: $backup" >&2
|
||
exit 3
|
||
fi
|
||
if [[ ! -s "$backup" ]]; then
|
||
echo "备份文件为空，未做任何修改: $backup" >&2
|
||
exit 3
|
||
fi
|
||
if [[ "$(sqlite3 -readonly "$backup" \
|
||
"select count(*) from sqlite_master where type='table' and name in ('gidToKey','ImageKeyCache');")" != "2" ]]; then
|
||
echo "备份校验失败（缺少缓存表），未做任何修改，已删除无效备份。" >&2
|
||
rm -f -- "$backup"
|
||
exit 3
|
||
fi
|
||
echo "已备份（SQLite .backup）: $backup"
|
||
|
||
# ---- 执行迁移：.bail on 保证任一语句失败即退出，事务不提交 ----
|
||
sqlite3 -cmd '.bail on' "$db_path" < "$SQL_FILE"
|
||
|
||
echo "--- 迁移后体检 ---"
|
||
read_stats | while IFS='|' read -r name value; do
|
||
printf ' %-16s %s\n' "$name" "$value"
|
||
done
|
||
|
||
echo "图片索引迁移完成: $db_path"
|