从东方年代记吧一次性备份脚本整理成的贴吧存档工具:crawl/update/verify/as-of/restore/rebuild/images/build-zim,SQLite 事件流 + 回收站 + 中文全文索引 ZIM。
255 lines
9.8 KiB
Python
255 lines
9.8 KiB
Python
# -*- 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")
|