从东方年代记吧一次性备份脚本整理成的贴吧存档工具:crawl/update/verify/as-of/restore/rebuild/images/build-zim,SQLite 事件流 + 回收站 + 中文全文索引 ZIM。
223 lines
8.5 KiB
SQL
223 lines
8.5 KiB
SQL
-- 贴吧归档库结构(与 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';
|