Skip to content

armgit / peach

Public Imported from GitHub
0

Peach: Local-first video metadata scraper and media manager.

15.2 KB
Raw Blame
#!/usr/bin/env python3
# -*- coding: utf-8 -*-
r"""给已入库的 rule34.xxx 追更条目补上列表请求之外的细节。

posts DAPI 的默认 JSON 只回扁平标签和最后修改时间,三样东西要另外问:

- 标签类型(general/artist/character/copyright/metadata):JSON 带
  `fields=tag_info`。帖子页 `#tag-sidebar` 自 2026-10-20 起被 Cloudflare 验证挡住,
  不再是来源。
- 上传时间:XML 的 `created_at`。DAPI 的 `change` 是最后一次修改:17361475
  五月上传、九月被改过标签,入库时记成了九月,卡片就排到了新内容里。
- 视频时长只在原文件头的 `mvhd` 里,接口都不给。

常规「检查更新」只看第一页,而且已经补齐过类型的条目不再进第二阶段,存量行靠
它永远改不过来,所以要这一趟。判据不重写,直接复用连接器的 `_detail` 与
`_video_seconds`:判据只能有一处。

默认 dry-run,只写复核 CSV。`--apply` 必须同时给 `--backup`,与本仓库其它真实写入
脚本一致。`--resume` 跳过同一份 CSV 里已经写入的条目,中断后用原命令加它接着跑。

`--missing-types` 只挑账本里还没有标签类型的条目。帖子页被挡住的那段时间入库的行
正是这一批,它们的上传时间也还是 `change` 占位,一趟一起改正。

`--duration-only` 只补 mp4 缺的时长:只挑 `duration` 为空的 mp4 条目,不问 dapi,
只读原文件头,也只写 `duration`。时长在视频 CDN 上,不受 dapi 每秒一次的限流牵连,
授权只覆盖时长时用它,别的列一格不动。
"""
from __future__ import annotations

import argparse
import json
import sqlite3
import time
import sys
from pathlib import Path

PROJECT_ROOT = Path(__file__).resolve().parent.parent
SRC_DIR = PROJECT_ROOT / "src"
if str(SRC_DIR) not in sys.path:
    sys.path.insert(0, str(SRC_DIR))

from peach.config import GENERATED_DIR, SECRETS_DIR, SHARED_CREDENTIAL_ROOT
from peach.follow_secrets import CredentialError, credential_store_for
from peach.follow_sources import FollowSourceError, build_connector
from peach.review_csv import read_rows, write_rows
from peach.scripting import (
    BACKUP_REQUIRED, add_ledger_write_args, open_for_write, open_readonly,
)

FIELDS = ("item_id", "external_id", "result", "published_before", "published_after",
          "duration", "tag_types_added", "written", "note")

#: 每条之间的最小间隔。rule34.xxx 自己公布的口径是每 60 秒 60 次,`_detail` 每条
#: 问两次 dapi(JSON 的分类、XML 的上传时间),所以每条至少 2 秒;留一点余量。
#: 首轮按 0.35 秒跑过一次:400 条里 372 条被限流挡了,被读成「没有类型」——速度
#: 换来的全是白跑的请求。视频文件头在另一个主机上,在这段间隔里发,不另占时间。
DEFAULT_DELAY = 2.2
#: 连续这么多条取不到就认定是被限流,而不是这一段恰好都取不到。
MISS_STREAK_LIMIT = 8
#: 限流后的静默时长,按站方公布的窗口取满一格。
COOLDOWN_SECONDS = 60.0
#: 每这么多条落一次库、写一次 CSV。
FLUSH_EVERY = 50


def build_parser() -> argparse.ArgumentParser:
    parser = argparse.ArgumentParser(
        description="补齐 rule34.xxx 追更条目的标签类型、上传时间与视频时长")
    add_ledger_write_args(parser)
    parser.add_argument("--out", type=Path,
                        default=GENERATED_DIR / "rule34-detail-backfill.csv")
    parser.add_argument("--limit", type=int, default=0, help="只处理前 N 条,0 为全部")
    parser.add_argument("--delay", type=float, default=DEFAULT_DELAY)
    parser.add_argument("--resume", action="store_true",
                        help="跳过 --out 里已经写入的条目,接着上一趟跑")
    parser.add_argument("--duration-only", action="store_true",
                        help="只补 mp4 缺的时长:不问 dapi,只读原文件头,只写 duration")
    parser.add_argument("--missing-types", action="store_true",
                        help="只挑还没有标签类型的条目")
    return parser


def written_ids(path: Path) -> set[int]:
    """上一趟已经写进账本的条目。只认 `written=是`:dry-run 与未取得的都要再问。"""
    return {int(row["item_id"]) for row in read_rows(path, missing_ok=True)
            if row.get("written") == "是" and str(row.get("item_id") or "").isdigit()}


def pending_rows(connection: sqlite3.Connection, limit: int,
                 done: set[int] = frozenset(), *, duration_only: bool = False,
                 missing_types: bool = False) -> list[dict]:
    """待补条目:rule34xxx 来源的全部条目,扣掉上一趟已经写入的。

    上传时间错没错,不问 dapi 就看不出来——被改过的帖子 `change` 晚于上传,没改过的
    两者逐秒相同,账本里长得一样。所以每条都要问一遍,续跑靠 CSV 而不是账本里的值。
    只补时长时缺不缺看账本就知道,只挑 `duration` 为空的 mp4;只补类型时同理,只挑
    metadata 里没有 `tag_types` 的。
    """
    rows = []
    query = (
        "SELECT i.id, i.external_id, i.media_url, i.published_at, i.duration,"
        " i.metadata_json FROM follow_item i JOIN follow_source s ON s.id=i.source_id"
        " WHERE s.provider='rule34xxx'"
        + (" AND i.duration IS NULL" if duration_only else "")
        + " ORDER BY i.id"
    )
    for row in connection.execute(query):
        if row["id"] in done or (duration_only and not _is_mp4(row["media_url"])):
            continue
        try:
            metadata = json.loads(row["metadata_json"] or "{}")
        except ValueError:
            metadata = {}
        # 空表与缺键同样算缺:`plan_update` 也只在没有非空类型时才补。
        if missing_types and isinstance(metadata, dict) and metadata.get("tag_types"):
            continue
        rows.append({"id": row["id"], "external_id": str(row["external_id"] or ""),
                     "media_url": row["media_url"], "published_at": row["published_at"],
                     "duration": row["duration"],
                     "metadata": metadata if isinstance(metadata, dict) else {}})
        if limit and len(rows) >= limit:
            break
    return rows


def plan_update(row: dict, detail: dict, duration: float | None) -> dict | None:
    """这一条要改哪几列。没有可写的就是 None。

    只补不抹:dapi 没给上传时间就不动 `published_at`;已有时长不覆盖;已有非空
    类型不覆盖。取不到的留待下一趟,不写空值冒充已补。
    """
    update: dict = {}
    posted = detail.get("published_at")
    if posted and posted != row["published_at"]:
        update["published_at"] = posted
    if duration and row["duration"] is None:
        update["duration"] = duration
    tag_types = detail.get("tag_types")
    if tag_types and not row["metadata"].get("tag_types"):
        update["metadata_json"] = json.dumps(
            {**row["metadata"], "tag_types": dict(tag_types)}, ensure_ascii=False)
    return update or None


def inspect_row(connector, row: dict) -> tuple[dict, dict | None]:
    """问一条:dapi 的分类与上传时间,缺时长的视频再问一次文件头。返回 CSV 行和要写的列。

    分类没取到时 `result` 是「未取得」:被限流挡回来与帖子已删都是这样,不能当成
    「这条没有」写进去,留给下一趟再问。
    """
    post_id = row["external_id"]
    entry = {"item_id": row["id"], "external_id": post_id, "result": "",
             "published_before": row["published_at"] or "", "published_after": "",
             "duration": row["duration"] if row["duration"] is not None else "",
             "tag_types_added": "", "written": "否", "note": ""}
    if not post_id.isdigit():
        entry.update(result="跳过", note="external_id 不是帖子号")
        return entry, None
    try:
        detail = connector._detail(post_id)
    except (FollowSourceError, OSError) as error:
        detail, entry["note"] = {}, f"{type(error).__name__}: {error}"[:120]
    if not detail.get("tag_types"):
        entry.update(result="未取得", note=entry["note"] or "dapi 没有给出标签分类")
        return entry, None
    duration = (connector._video_seconds(row["media_url"])
                if row["duration"] is None else None)
    update = plan_update(row, detail, duration) or {}
    entry.update(result="取得" if update else "无需改动",
                 published_after=detail.get("published_at") or "",
                 duration=update.get("duration", entry["duration"]),
                 tag_types_added="是" if "metadata_json" in update else "")
    if not detail.get("published_at"):
        entry["note"] = "dapi 没有给出 created_at"
    elif _is_mp4(row["media_url"]) and row["duration"] is None and not duration:
        entry["note"] = "时长未取得"
    return entry, update or None


def inspect_duration(connector, row: dict) -> tuple[dict, dict | None]:
    """只补时长:读一次原文件头,读到就只写 `duration`。

    读不到记「未取得」而不是写空:`moov` 在文件结尾、CDN 一时不通都会这样,
    留给下一趟再问,不冒充测过。
    """
    entry = {"item_id": row["id"], "external_id": row["external_id"], "result": "",
             "published_before": row["published_at"] or "", "published_after": "",
             "duration": "", "tag_types_added": "", "written": "否", "note": ""}
    seconds = connector._video_seconds(row["media_url"])
    if not seconds:
        entry.update(result="未取得", note="时长未取得")
        return entry, None
    entry.update(result="取得", duration=seconds)
    return entry, {"duration": seconds}


def _is_mp4(url: str | None) -> bool:
    return str(url or "").lower().split("?")[0].endswith(".mp4")


def _apply(writer: sqlite3.Connection, updates: list[tuple[int, dict]]) -> None:
    for item_id, update in updates:
        if "published_at" in update:
            writer.execute("UPDATE follow_item SET published_at=?, published_precision='exact'"
                           " WHERE id=?", (update["published_at"], item_id))
        if "duration" in update:
            writer.execute("UPDATE follow_item SET duration=? WHERE id=? AND duration IS NULL",
                           (update["duration"], item_id))
        if "metadata_json" in update:
            writer.execute("UPDATE follow_item SET metadata_json=? WHERE id=?",
                           (update["metadata_json"], item_id))
    writer.commit()


def run(args: argparse.Namespace) -> int:
    if args.apply and not args.backup:
        print(BACKUP_REQUIRED, file=sys.stderr)
        return 2
    done = written_ids(args.out) if args.resume else set()
    previous = [row for row in read_rows(args.out, missing_ok=True)
                if row.get("written") == "是"] if args.resume else []
    with open_readonly(args.db) as reader:
        rows = pending_rows(reader, args.limit, done, duration_only=args.duration_only,
                            missing_types=args.missing_types)
        total = reader.execute(
            "SELECT COUNT(*) FROM follow_item i JOIN follow_source s ON s.id=i.source_id"
            " WHERE s.provider='rule34xxx'").fetchone()[0]
    print(f"rule34xxx 共 {total} 条,已写入 {len(done)} 条,本趟待问 {len(rows)} 条", flush=True)
    if not rows:
        return 0
    # dapi 要账号自己的 user_id + api_key;只读文件头不需要。缺凭据在备份与写入之前就停。
    credential = None
    if not args.duration_only:
        try:
            credential = credential_store_for(
                SECRETS_DIR, shared_root=SHARED_CREDENTIAL_ROOT).load("rule34xxx")
        except CredentialError as error:
            print(f"rule34xxx 凭据不可用:{error}", file=sys.stderr)
            return 2
        if credential is None:
            print("rule34xxx 需要 user_id 与 api_key;请把它们写进 "
                  "peach-data/secrets/follow/rule34xxx.json", file=sys.stderr)
            return 2

    writer: sqlite3.Connection | None = None
    before = 0
    if args.apply:
        # 备份在任何写入之前由 `open_for_write` 落;分批提交,中断一次不至于整趟白跑。
        writer = open_for_write(args)
        # 服务的关注检查同时在写 follow_item;它的事务很短,等它提交而不是 5 秒就报锁。
        writer.execute("PRAGMA busy_timeout=60000")
        print(f"备份:{args.backup}", flush=True)
        before = writer.execute("SELECT COUNT(*) FROM follow_item").fetchone()[0]

    connector = build_connector("rule34xxx", credential=credential)
    log: list[dict] = []
    staged: list[tuple[int, dict]] = []
    staged_logs: list[dict] = []
    counts = {"published_at": 0, "duration": 0, "metadata_json": 0, "未取得": 0}
    miss_streak = 0
    next_detail = 0.0

    def flush() -> None:
        if writer is not None and staged:
            _apply(writer, staged)
            for entry in staged_logs:
                entry["written"] = "是"
        staged.clear()
        staged_logs.clear()
        args.out.parent.mkdir(parents=True, exist_ok=True)
        write_rows(args.out, FIELDS, previous + log, atomic=True)

    for index, row in enumerate(rows, 1):
        # 间隔按 dapi 请求的起点算:文件头在另一个主机上,问它的时间落在这段间隔里。
        time.sleep(max(0.0, next_detail - time.monotonic()))
        next_detail = time.monotonic() + args.delay
        entry, update = (inspect_duration if args.duration_only else inspect_row)(connector, row)
        log.append(entry)
        if entry["result"] == "未取得":
            counts["未取得"] += 1
            miss_streak += 1
            if miss_streak >= MISS_STREAK_LIMIT:
                print(f"连续 {miss_streak} 条取不到,按限流处理,静默 "
                      f"{COOLDOWN_SECONDS:.0f} 秒", flush=True)
                flush()
                time.sleep(COOLDOWN_SECONDS)
                miss_streak = 0
            continue
        miss_streak = 0
        if update:
            for key in update:
                counts[key] += 1
            staged.append((row["id"], update))
            staged_logs.append(entry)
        if index % FLUSH_EVERY == 0:
            flush()
            print(f"{index}/{len(rows)}:上传时间 {counts['published_at']},时长 "
                  f"{counts['duration']},类型 {counts['metadata_json']},未取得 "
                  f"{counts['未取得']}", flush=True)
    flush()
    summary = (f"上传时间改正 {counts['published_at']},补时长 {counts['duration']},"
               f"补类型 {counts['metadata_json']},未取得 {counts['未取得']} → {args.out}")
    if writer is None:
        print(f"dry-run:{summary};加 --apply --backup 才真正写入", flush=True)
        return 0
    after = writer.execute("SELECT COUNT(*) FROM follow_item").fetchone()[0]
    writer.close()
    # 条数必须不变:这一趟只补已有条目的列,不新增也不删除条目。
    print(f"已写入:{summary};follow_item {before} → {after}", flush=True)
    return 0 if before == after else 1


def main(argv: list[str] | None = None) -> int:
    return run(build_parser().parse_args(argv))


if __name__ == "__main__":
    raise SystemExit(main())