Files
dsh 0824e0ef83 v1.2.0: 事件流对账、增量 update、ZIM 发布与表情渲染
从东方年代记吧一次性备份脚本整理成的贴吧存档工具:crawl/update/verify/as-of/restore/rebuild/images/build-zim,SQLite 事件流 + 回收站 + 中文全文索引 ZIM。
2026-11-18 11:41:25 +08:00

223 lines
8.5 KiB
SQL
Raw Permalink Blame History

This file contains ambiguous Unicode characters
This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.
-- 贴吧归档库结构(与 tieba-archive/data/tieba.db 的列名、列序一致)
--
-- 设计原则:
-- 1. 所有表都用 INSERT ... ON CONFLICT / INSERT OR REPLACE 写入,重跑不产生重复行;
-- 2. 文本轻、图片重:正文进 post/comment,图片只存 URL,文件在 imgs/ 按 md5 分桶;
-- 3. 断点续传优先:thread.status / pages_done 记录进度,抓取队列在 raw/ 的 JSONL 里,
-- 数据库只做最终汇总,抓取进程不直接写库(理由见 crawler.py)。
--
-- 列序刻意和旧库保持一致:v2 新增的列(agree/disagree/sign/frags、floor/rto/...)
-- 追加在表尾。旧代码里如果有 `INSERT INTO post VALUES(...)` 这种不带列名的写法,
-- 列序一致才不会错位。旧库缺这些列时由 db.ensure_compat() 用 ALTER TABLE 补齐。
--
-- journal_mode=WAL / synchronous=NORMAL 不写在这里:它们由 db.connect() 设置,
-- 因为 schema.sql 也可能被拿去做只读检查,PRAGMA 写进脚本会让人误以为执行 DDL
-- 必然改文件。
-- 吧信息(enum 阶段写入,主要用来保存 fid)
CREATE TABLE IF NOT EXISTS forum (
fid INTEGER PRIMARY KEY,
kw TEXT,
name TEXT,
member_num INTEGER,
post_num INTEGER,
fetched_at TEXT
);
-- 主题帖索引:先枚举出来,再逐帖抓
CREATE TABLE IF NOT EXISTS thread (
tid INTEGER PRIMARY KEY,
fid INTEGER,
title TEXT,
author TEXT,
author_id TEXT,
reply_num INTEGER,
is_good INTEGER DEFAULT 0,
is_top INTEGER DEFAULT 0,
pages INTEGER, -- 预计页数
pages_done INTEGER DEFAULT 0,
status TEXT DEFAULT 'new', -- new / part / done / gone
err TEXT,
updated_at TEXT,
deleted_at TEXT -- 整帖被删的时间(楼一起进回收站)
);
CREATE INDEX IF NOT EXISTS idx_thread_status ON thread(status);
-- 楼层(正文)
CREATE TABLE IF NOT EXISTS post (
pid INTEGER PRIMARY KEY,
tid INTEGER NOT NULL,
floor INTEGER,
author TEXT,
author_id TEXT,
content TEXT, -- 纯文本(去标签),检索用
content_html TEXT, -- 原始 HTML 片段,保真用
post_time TEXT,
comment_num INTEGER DEFAULT 0, -- 楼中楼数量
imgs TEXT, -- JSON 数组,图片原始 URL
is_lz INTEGER DEFAULT 0,
fetched_at TEXT,
-- 以下为 v2 补齐字段(旧库由 ALTER 追加,列序必须留在表尾)
agree INTEGER DEFAULT 0, -- 赞
disagree INTEGER DEFAULT 0, -- 踩
sign TEXT DEFAULT '', -- 小尾巴
frags TEXT, -- 正文 fragment 的 JSON 数组(text/emoji/img/at/link/video/voice)
-- 事件流相关:当前表只是物化结果,删除走 status,不物理删
status TEXT DEFAULT 'live', -- live / recycled
recycled_at TEXT,
recycle_reason TEXT, -- post_deleted / thread_deleted
content_hash TEXT -- 正文指纹,verify 用来发现编辑
);
CREATE INDEX IF NOT EXISTS idx_post_tid ON post(tid, floor);
-- 楼中楼(子回复)
CREATE TABLE IF NOT EXISTS comment (
cid INTEGER PRIMARY KEY,
pid INTEGER, -- 所在楼层的 pid
tid INTEGER,
author TEXT,
author_id TEXT,
content TEXT,
post_time TEXT,
fetched_at TEXT,
-- 以下为 v2 补齐字段
floor INTEGER, -- 所在楼层号
agree INTEGER DEFAULT 0,
disagree INTEGER DEFAULT 0,
rto INTEGER DEFAULT 0, -- 协议给的 reply_to_id(很多老帖是 0)
reply_to_name TEXT, -- 从正文「回复 某某 :」解析出的昵称
reply_to_uid INTEGER, -- 最终回复对象:rto 优先,其次昵称唯一匹配
frags TEXT, -- fragment JSON 数组
status TEXT DEFAULT 'live',
recycled_at TEXT,
recycle_reason TEXT
);
CREATE INDEX IF NOT EXISTS idx_comment_tid ON comment(tid);
-- 图片队列:url 唯一,md5 下载后回填
CREATE TABLE IF NOT EXISTS image (
url TEXT PRIMARY KEY,
tid INTEGER,
pid INTEGER,
md5 TEXT,
size INTEGER,
path TEXT,
status TEXT DEFAULT 'new', -- new / done / dead / fail
tries INTEGER DEFAULT 0,
code INTEGER,
err TEXT,
fetched_at TEXT
);
CREATE INDEX IF NOT EXISTS idx_image_status ON image(status);
CREATE INDEX IF NOT EXISTS idx_image_md5 ON image(md5);
-- 抓取任务队列(通用,按 key 幂等)
CREATE TABLE IF NOT EXISTS task (
key TEXT PRIMARY KEY,
kind TEXT,
payload TEXT,
status TEXT DEFAULT 'new', -- new / done / fail
tries INTEGER DEFAULT 0,
last_err TEXT,
updated_at TEXT
);
CREATE INDEX IF NOT EXISTS idx_task_status ON task(status, kind);
-- 杂项计数(例如 kv['fid'])
CREATE TABLE IF NOT EXISTS kv (
k TEXT PRIMARY KEY,
v TEXT
);
-- 用户资料:楼层/楼中楼里只带昵称,详细资料单独存一份
CREATE TABLE IF NOT EXISTS user (
uid INTEGER PRIMARY KEY,
uname TEXT,
nick TEXT,
portrait TEXT,
level INTEGER DEFAULT 0, -- 吧等级
glevel INTEGER DEFAULT 0, -- 成长等级
gender INTEGER DEFAULT 0,
ip TEXT DEFAULT '',
bawu INTEGER DEFAULT 0, -- 是否吧务
vip INTEGER DEFAULT 0, -- 是否会员
icons TEXT DEFAULT '', -- 逗号分隔的图标名
updated_at TEXT
);
-- ================================================================
-- 事件流:事件是唯一真相,post/comment 是「快照 + 事件」物化出来的结果。
-- update 只产生 add;verify 产生 add/del/edit/thread_deleted。
-- ================================================================
CREATE TABLE IF NOT EXISTS batch (
id INTEGER PRIMARY KEY AUTOINCREMENT,
kind TEXT NOT NULL, -- update / verify / import / manual
started_at TEXT,
finished_at TEXT,
note TEXT
);
CREATE TABLE IF NOT EXISTS event (
id INTEGER PRIMARY KEY AUTOINCREMENT,
batch INTEGER,
ts TEXT NOT NULL, -- 观测时间
kind TEXT NOT NULL, -- post_add/post_del/post_edit/comment_add/comment_del/thread_deleted/thread_restored
tid INTEGER,
pid INTEGER,
cid INTEGER,
floor INTEGER, -- 观测到的楼层号(会漂,所以记在事件里)
payload TEXT, -- JSON:完整行或变更后的字段
source TEXT, -- update / verify / manual
evidence TEXT -- 例如 missing_hits=2
);
CREATE INDEX IF NOT EXISTS idx_event_tid ON event(tid, ts);
CREATE INDEX IF NOT EXISTS idx_event_kind ON event(kind, ts);
-- 增量判定用的快照:reply_num 变了才去翻尾页
CREATE TABLE IF NOT EXISTS thread_seen (
tid INTEGER PRIMARY KEY,
reply_num INTEGER,
max_floor INTEGER,
seen_at TEXT
);
-- 点赞采样:只记变化点,永久保留
CREATE TABLE IF NOT EXISTS post_stat_history (
pid INTEGER NOT NULL,
ts TEXT NOT NULL,
agree INTEGER,
disagree INTEGER,
PRIMARY KEY (pid, ts)
);
-- 两次确认:第一次发现缺失只记怀疑,第二次还在缺才发删除事件
CREATE TABLE IF NOT EXISTS verify_suspect (
tid INTEGER NOT NULL,
pid INTEGER NOT NULL,
first_seen_at TEXT,
last_seen_at TEXT,
hits INTEGER DEFAULT 1,
PRIMARY KEY (tid, pid)
);
CREATE TABLE IF NOT EXISTS comment_suspect (
cid INTEGER PRIMARY KEY,
first_seen_at TEXT,
last_seen_at TEXT,
hits INTEGER DEFAULT 1
);
CREATE TABLE IF NOT EXISTS thread_suspect (
tid INTEGER PRIMARY KEY,
first_seen_at TEXT,
last_seen_at TEXT,
hits INTEGER DEFAULT 1
);
-- 回收站视图:查询默认用 post_live / comment_live
CREATE VIEW IF NOT EXISTS post_live AS SELECT * FROM post WHERE status='live';
CREATE VIEW IF NOT EXISTS post_recycled AS SELECT * FROM post WHERE status='recycled';
CREATE VIEW IF NOT EXISTS comment_live AS SELECT * FROM comment WHERE status='live';
CREATE VIEW IF NOT EXISTS comment_recycled AS SELECT * FROM comment WHERE status='recycled';