68 lines
3.3 KiB
SQL
68 lines
3.3 KiB
SQL
-- 图片索引缓存迁移：为 cache.db 的两张索引表补唯一约束与查询索引。
|
|
--
|
|
-- 目标（对应本次重写的三条不变量）：
|
|
-- 1. gid 唯一 —— 同一本画廊只能有一条 gidToKey 记录；
|
|
-- 2. (gid,page) 唯一 —— 同一页只能有一个 imgkey，同时天然满足按页查询的索引需求；
|
|
-- 3. 只保留「完整」的索引 —— 半截或冲突的 gid 整组删除，让下一次在线阅读重新抓取。
|
|
--
|
|
-- 为什么用唯一索引而不是重建表：
|
|
-- * 唯一索引对 SQLite 与唯一约束等价，都会拒绝重复写入；
|
|
-- * 不需要搬表、改列，旧版本二进制仍可读写同一份数据；
|
|
-- * 回滚只需 drop index，最坏情况也只需回到脚本自动生成的备份文件。
|
|
--
|
|
-- 幂等：所有重建索引都带 IF NOT EXISTS；清理语句只按集合删除，重复执行结果相同。
|
|
-- 安全：本脚本只做 DELETE 与 CREATE INDEX，不建表、不改列、不碰 gidToKey/ImageKeyCache
|
|
-- 之外的任何对象。必须在事务中整体提交，调用方使用 sqlite3 -bail 保证任一语句
|
|
-- 失败即终止、事务回滚。
|
|
--
|
|
-- 执行前请务必先备份数据库文件（迁移脚本 migrate_image_index_cache.sh --apply 会自动备份）。
|
|
|
|
BEGIN IMMEDIATE;
|
|
|
|
-- ---------- 1. 冲突 gid：同一 gid 有多条头部记录 ----------
|
|
-- 无法判断哪一条正确，整组移除，由下次访问重抓。
|
|
CREATE TEMP TABLE __conflict_gid AS
|
|
SELECT gid FROM gidToKey GROUP BY gid HAVING COUNT(*) > 1;
|
|
|
|
DELETE FROM ImageKeyCache WHERE gid IN (SELECT gid FROM __conflict_gid);
|
|
DELETE FROM gidToKey WHERE gid IN (SELECT gid FROM __conflict_gid);
|
|
|
|
-- ---------- 2. 半截或非法 gid：头部与页 key 不一致 ----------
|
|
-- 判定同时覆盖：页码数量不符、页码不连续（跳号/重复）、区间不是 1..pages、
|
|
-- 头部字段为空、以及存在空的 imgkey 行。
|
|
CREATE TEMP TABLE __broken_gid AS
|
|
SELECT 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) = ''));
|
|
|
|
DELETE FROM ImageKeyCache WHERE gid IN (SELECT gid FROM __broken_gid);
|
|
DELETE FROM gidToKey WHERE gid IN (SELECT gid FROM __broken_gid);
|
|
|
|
-- ---------- 3. 孤儿页 key：没有对应头部记录 ----------
|
|
DELETE FROM ImageKeyCache WHERE gid NOT IN (SELECT gid FROM gidToKey);
|
|
|
|
-- ---------- 4. 唯一约束 ----------
|
|
-- 到这里若仍有重复，说明上面的判定有遗漏；CREATE UNIQUE INDEX 会失败，
|
|
-- 配合 -bail 让整个事务回滚，不会留下「一半约束」的库。
|
|
CREATE UNIQUE INDEX IF NOT EXISTS ux_gidToKey_gid
|
|
ON gidToKey (gid);
|
|
|
|
CREATE UNIQUE INDEX IF NOT EXISTS ux_ImageKeyCache_gid_page
|
|
ON ImageKeyCache (gid, page);
|
|
|
|
-- 按 gid 统计页数会用到 (gid,page) 前缀，按页取 key 也走同一索引，
|
|
-- 因此不再单独建 page 索引，避免重复占用写放大。
|
|
|
|
DROP TABLE __conflict_gid;
|
|
DROP TABLE __broken_gid;
|
|
|
|
COMMIT;
|
|
|