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

255 lines
9.8 KiB
Python
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.
# -*- coding: utf-8 -*-
"""SQLite 连接、建库、旧库补列、统计。
三个关键决定:
**为什么用 WAL**
库放在普通磁盘(旧归档甚至放在 OneDrive 目录)时,DELETE 日志模式每提交一次
事务都要创建/删除一个 ``-journal`` 文件,同步盘会把它当成「文件变动」立刻重新
扫描上传,几十万行写下来会慢两个数量级。WAL 只往 ``-wal`` 追加,提交是顺序写,
并且读写不互相阻塞:抓取进程只写 JSONL、导入进程写库,统计可以同时读。
**为什么 synchronous=NORMAL**
WAL 下 NORMAL 只在 checkpoint 时 fsync,断电最多丢最后几个事务;对归档这种
「重跑一次就能补齐」的场景,用 FULL 换那点安全性不值。库坏了可以重导,慢是
每分钟都在付的成本。
**为什么统计默认只读 + immutable**
``stats --data ..\\东方年代记吧_全量备份_2011-2026\\data`` 要能打开别人的成品库而一个字节都不写。
普通只读连接在 WAL 模式下仍可能创建 ``-shm``;``immutable=1`` 告诉 SQLite
「这个文件不会变」,于是它连锁都不加,绝不会在数据目录里留下新文件。代价是
看不到还没 checkpoint 的 WAL 内容——导入进程正常关闭时会自动 checkpoint,
所以日常用法没问题;真要看正在写入的库,用 ``stats --live``。
"""
from __future__ import annotations
import sqlite3
import time
from pathlib import Path
from urllib.parse import quote
from .config import Config
SCHEMA_PATH = Path(__file__).with_name("schema.sql")
# 旧库(v1)缺的列。新库在 schema.sql 里已经内建,这里对它是空操作。
_POST_EXTRA = {
"agree": "INTEGER DEFAULT 0",
"disagree": "INTEGER DEFAULT 0",
"sign": "TEXT DEFAULT ''",
"frags": "TEXT",
"status": "TEXT DEFAULT 'live'",
"recycled_at": "TEXT",
"recycle_reason": "TEXT",
"content_hash": "TEXT",
}
_COMMENT_EXTRA = {
"floor": "INTEGER",
"agree": "INTEGER DEFAULT 0",
"disagree": "INTEGER DEFAULT 0",
"rto": "INTEGER DEFAULT 0",
"reply_to_name": "TEXT",
"reply_to_uid": "INTEGER",
"frags": "TEXT",
"status": "TEXT DEFAULT 'live'",
"recycled_at": "TEXT",
"recycle_reason": "TEXT",
}
_THREAD_EXTRA = {
"deleted_at": "TEXT",
}
_USER_DDL = """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)"""
# ---------------------------------------------------------------- 连接
def _sqlite_uri(path: Path, immutable: bool) -> str:
"""拼只读 URI。Windows 路径转 POSIX 再百分号编码,避免盘符/空格出错。"""
posix = path.resolve().as_posix()
params = "mode=ro" + ("&immutable=1" if immutable else "")
return f"file:{quote(posix, safe='/:')}?{params}"
def connect(cfg: Config, *, readonly: bool = False, immutable: bool = False) -> sqlite3.Connection:
"""打开数据库。
``readonly=True`` 时要求文件已存在,绝不创建;``immutable=True`` 进一步保证
连 ``-shm`` 都不产生,适合读别人的归档。
"""
if readonly:
if not cfg.db_path.exists():
raise FileNotFoundError(
f"数据库不存在:{cfg.db_path}\n"
f"新归档先跑 `python tieba.py init --kw <吧名> --data {cfg.data_dir}`"
)
conn = sqlite3.connect(
_sqlite_uri(cfg.db_path, immutable=immutable), uri=True, timeout=30
)
conn.row_factory = sqlite3.Row
conn.execute("PRAGMA busy_timeout=20000")
return conn
cfg.data_dir.mkdir(parents=True, exist_ok=True)
conn = sqlite3.connect(str(cfg.db_path), timeout=30)
conn.row_factory = sqlite3.Row
# 见模块 docstring:WAL 避免每次提交创建/删除日志文件,读写可并行
conn.execute("PRAGMA journal_mode=WAL")
conn.execute("PRAGMA synchronous=NORMAL")
conn.execute("PRAGMA busy_timeout=20000")
# 某些环境临时目录不可写/同步盘上建临时文件很慢;统计的 GROUP BY 全放内存
conn.execute("PRAGMA temp_store=MEMORY")
return conn
def init_db(conn: sqlite3.Connection) -> None:
"""按 schema.sql 建表(全部 IF NOT EXISTS,可反复执行)。"""
conn.executescript(SCHEMA_PATH.read_text(encoding="utf-8"))
conn.commit()
def table_columns(conn: sqlite3.Connection, table: str) -> set[str]:
try:
return {row[1] for row in conn.execute(f'PRAGMA table_info("{table}")')}
except sqlite3.Error:
return set()
def ensure_compat(conn: sqlite3.Connection) -> None:
"""给旧库补 v2 列和 user 表。
只 ADD COLUMN,不重建表、不改已有列的类型:旧库可能已经有 40 万行数据,
重建表既慢又危险。SQLite 的 ADD COLUMN 是 O(1) 的元数据操作。
"""
for table, extra in (("post", _POST_EXTRA), ("comment", _COMMENT_EXTRA),
("thread", _THREAD_EXTRA)):
cols = table_columns(conn, table)
if not cols: # 表不存在(空库),交给 init_db
continue
for name, ddl in extra.items():
if name not in cols:
conn.execute(f'ALTER TABLE "{table}" ADD COLUMN {name} {ddl}')
conn.execute(_USER_DDL)
conn.execute(
"""CREATE TABLE IF NOT EXISTS comment_suspect(
cid INTEGER PRIMARY KEY, first_seen_at TEXT, last_seen_at TEXT,
hits INTEGER DEFAULT 1)"""
)
conn.commit()
# ---------------------------------------------------------------- 统计
def _scalar(conn: sqlite3.Connection, sql: str, default: int = 0) -> int:
"""跑一条 COUNT/SUM;表或列不存在时退化为 0(旧库兼容用)。"""
try:
value = conn.execute(sql).fetchone()[0]
return int(value or 0)
except sqlite3.Error:
return default
def stats(conn: sqlite3.Connection) -> dict[str, int]:
"""汇总归档规模,供 stats 命令打印。
除了「主题/楼层/楼中楼/图片/用户」,还给出三个能反映抓取质量的细项:
有小尾巴的楼层、有赞的楼层、有回复对象的楼中楼。
"""
tables = {
row[0]
for row in conn.execute("SELECT name FROM sqlite_master WHERE type='table'")
}
if "thread" not in tables:
raise sqlite3.DatabaseError("这个库里没有 thread 表,不像是归档库")
pc = table_columns(conn, "post")
cc = table_columns(conn, "comment")
result: dict[str, int] = {
"threads": _scalar(conn, "SELECT COUNT(*) FROM thread"),
"threads_done": _scalar(conn, "SELECT COUNT(*) FROM thread WHERE status='done'"),
"threads_err": _scalar(
conn, "SELECT COUNT(*) FROM thread WHERE err IS NOT NULL AND err != ''"
),
"threads_with_posts": _scalar(conn, "SELECT COUNT(DISTINCT tid) FROM post"),
"posts": _scalar(conn, "SELECT COUNT(*) FROM post"),
"posts_signed": _scalar(conn, "SELECT COUNT(*) FROM post WHERE sign != ''")
if "sign" in pc
else 0,
"posts_agreed": _scalar(conn, "SELECT COUNT(*) FROM post WHERE agree > 0")
if "agree" in pc
else 0,
"comments": _scalar(conn, "SELECT COUNT(*) FROM comment"),
"comments_rto": _scalar(conn, "SELECT COUNT(*) FROM comment WHERE rto != 0")
if "rto" in cc
else 0,
"comments_named": _scalar(
conn, "SELECT COUNT(*) FROM comment WHERE reply_to_name IS NOT NULL"
)
if "reply_to_name" in cc
else 0,
}
# 「有回复对象」兼容两种情况:协议直接给的 rto,或从正文昵称解析出的 uid
if "rto" in cc and "reply_to_uid" in cc:
result["comments_targeted"] = _scalar(
conn,
"SELECT COUNT(*) FROM comment WHERE rto != 0 OR reply_to_uid IS NOT NULL",
)
elif "rto" in cc:
result["comments_targeted"] = result["comments_rto"]
elif "reply_to_uid" in cc:
result["comments_targeted"] = _scalar(
conn, "SELECT COUNT(*) FROM comment WHERE reply_to_uid IS NOT NULL"
)
else:
result["comments_targeted"] = 0
result.update(
{
"images": _scalar(conn, "SELECT COUNT(*) FROM image"),
"images_done": _scalar(conn, "SELECT COUNT(*) FROM image WHERE status='done'"),
"image_files": _scalar(
conn,
"SELECT COUNT(DISTINCT md5) FROM image "
"WHERE status='done' AND md5 IS NOT NULL AND md5 != ''",
),
"image_bytes": _scalar(
conn, "SELECT COALESCE(SUM(size), 0) FROM image WHERE status='done'"
),
"users": _scalar(conn, "SELECT COUNT(*) FROM user") if "user" in tables else 0,
"posts_recycled": _scalar(conn, "SELECT COUNT(*) FROM post WHERE status='recycled'")
if "status" in pc
else 0,
"comments_recycled": _scalar(conn, "SELECT COUNT(*) FROM comment WHERE status='recycled'")
if "status" in cc
else 0,
"threads_deleted": _scalar(conn, "SELECT COUNT(*) FROM thread WHERE deleted_at IS NOT NULL"),
}
)
return result
def coverage(result: dict[str, int]) -> float:
"""主题覆盖率:至少抓到一层的主题 / 枚举到的主题。"""
total = result.get("threads") or 0
if not total:
return 0.0
return 100.0 * (result.get("threads_with_posts") or 0) / total
def fmt_size(size: float) -> str:
"""人话体积:1.2 GB 比 1288490188 好读。"""
for unit in ("B", "KB", "MB", "GB", "TB"):
if abs(size) < 1024 or unit == "TB":
return f"{size:.1f} {unit}"
size /= 1024
return f"{size:.1f} TB"
def now() -> str:
return time.strftime("%Y-%m-%d %H:%M:%S")