微信几百 G 争论背后：SQLite 到底该不该背锅？

#!/usr/bin/env python3
import hashlib
import os
import shutil
import sqlite3
import sys
from pathlib import Path

ROOT = Path("chat_store")
DB = ROOT / "chat.db"
BLOBS = ROOT / "blobs"


def sha256_file(path: Path) -> str:
    h = hashlib.sha256()
    with path.open("rb") as f:
        for chunk in iter(lambda: f.read(1024 * 1024), b""):
            h.update(chunk)
    return h.hexdigest()


def connect():
    ROOT.mkdir(exist_ok=True)
    BLOBS.mkdir(exist_ok=True)
    conn = sqlite3.connect(DB)
    conn.execute("PRAGMA journal_mode=WAL")
    conn.execute("PRAGMA foreign_keys=ON")
    conn.executescript(
        """
        CREATE TABLE IF NOT EXISTS messages (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            conversation_id TEXT NOT NULL,
            sender TEXT NOT NULL,
            text TEXT,
            created_at INTEGER NOT NULL DEFAULT (unixepoch())
        );

        CREATE TABLE IF NOT EXISTS attachments (
            hash TEXT PRIMARY KEY,
            size INTEGER NOT NULL,
            path TEXT NOT NULL,
            created_at INTEGER NOT NULL DEFAULT (unixepoch())
        );

        CREATE TABLE IF NOT EXISTS message_attachments (
            message_id INTEGER NOT NULL,
            attachment_hash TEXT NOT NULL,
            PRIMARY KEY (message_id, attachment_hash),
            FOREIGN KEY (message_id) REFERENCES messages(id) ON DELETE CASCADE,
            FOREIGN KEY (attachment_hash) REFERENCES attachments(hash) ON DELETE CASCADE
        );
        """
    )
    return conn


def add_message(conn, conversation_id: str, sender: str, text: str, files):
    cur = conn.execute(
        "INSERT INTO messages(conversation_id, sender, text) VALUES (?, ?, ?)",
        (conversation_id, sender, text),
    )
    message_id = cur.lastrowid

    for file_name in files:
        src = Path(file_name)
        digest = sha256_file(src)
        suffix = src.suffix.lower()
        dst = BLOBS / f"{digest}{suffix}"

        if not dst.exists():
            shutil.copy2(src, dst)

        conn.execute(
            "INSERT OR IGNORE INTO attachments(hash, size, path) VALUES (?, ?, ?)",
            (digest, dst.stat().st_size, str(dst)),
        )
        conn.execute(
            "INSERT OR IGNORE INTO message_attachments(message_id, attachment_hash) VALUES (?, ?)",
            (message_id, digest),
        )

    conn.commit()
    print(f"added message_id={message_id}")


def gc_orphan_blobs(conn):
    rows = conn.execute(
        """
        SELECT hash, path FROM attachments
        WHERE hash NOT IN (SELECT attachment_hash FROM message_attachments)
        """
    ).fetchall()

    for digest, path in rows:
        p = Path(path)
        if p.exists():
            p.unlink()
        conn.execute("DELETE FROM attachments WHERE hash = ?", (digest,))

    conn.commit()
    # 让 WAL 合并回主库，避免辅助文件长期膨胀
    conn.execute("PRAGMA wal_checkpoint(TRUNCATE)")
    conn.execute("VACUUM")
    print(f"removed {len(rows)} orphan blobs")


def stats(conn):
    msg_count = conn.execute("SELECT count(*) FROM messages").fetchone()[0]
    att_count = conn.execute("SELECT count(*) FROM attachments").fetchone()[0]
    att_size = conn.execute("SELECT coalesce(sum(size), 0) FROM attachments").fetchone()[0]
    db_size = DB.stat().st_size if DB.exists() else 0
    print(f"messages={msg_count}, attachments={att_count}, attachment_bytes={att_size}, db_bytes={db_size}")


if __name__ == "__main__":
    conn = connect()
    cmd = sys.argv[1] if len(sys.argv) > 1 else "stats"

    if cmd == "add":
        # 用法：python chat_store_demo.py add ./photo.jpg ./video.mp4
        add_message(conn, "group-1", "alice", "hello with files", sys.argv[2:])
    elif cmd == "gc":
        gc_orphan_blobs(conn)
    else:
        stats(conn)

python3 chat_store_demo.py stats
python3 chat_store_demo.py add ./some-image.jpg
python3 chat_store_demo.py add ./some-image.jpg   # 同一个文件会复用同一份 blob
python3 chat_store_demo.py stats
python3 chat_store_demo.py gc

sqlite3 chat_store/chat.db <<'SQL'
PRAGMA page_size;
PRAGMA page_count;
PRAGMA freelist_count;
PRAGMA journal_mode;
PRAGMA wal_checkpoint(PASSIVE);
SELECT name, type FROM sqlite_master WHERE type IN ('table', 'index') ORDER BY name;
SQL

ls -lh chat_store/
ls -lh chat_store/chat.db*
du -sh chat_store