Skip to content

armgit / peach

Public Imported from GitHub
0

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

6.2 KB
Raw Blame
"""把同一条实体上指向同一个去处的链接合并成一条。

`entity_link` 的 `UNIQUE(entity_id,url)` 只认字面,而同一个去处有很多种写法:

    http://kawakami-yuu.livedoor.biz      http://kawakami-yuu.livedoor.biz/
    https://x.com/yu_kinao               https://x.com/yu_kinao/status/1221041995415580672
    …/talent/%e6%b6%bc%e6%a3%ae…/        …/talent/%E6%B6%BC%E6%A3%AE…/

第一组差一个斜杠,第二组是同一个账号的主页和它的一条推文,第三组只差百分号转义的
大小写。三组在资料页上都是并排两枚一模一样的图标,点下去到同一个人那里。采集器还在
跑,所以这不是一次性清理:不同来源对同一个账号的写法本来就不同,这条路要留着复用。

判据分两种,因为「同一个去处」在社媒和普通网站上不是一回事:
  * 社媒按账号算。平台加 handle 就是身份,路径后面挂什么(status、photo、媒体页)
    都还是同一个人的账号。
  * 其余按主机加路径算,路径末尾的斜杠不算差别。查询串和片段照算——`?id=3` 换一个
    值多半就是另一个页面。

路径先解回字节再比:`%E6` 和 `%e6` 是同一个字节,而日文艺人页的路径整段都是转义的
假名,两个来源各按自己的习惯写大小写。解成字节而不是文字,是因为路径不保证是 UTF-8。

同组保留 URL 最短的那条:最短的就是账号主页或站点根,也就是点进去最有用的那个。

默认 dry-run。`--apply` 必须同时给 `--backup`:这是真实账本写入。
"""
from __future__ import annotations

import argparse
import sys
from pathlib import Path
from urllib.parse import unquote_to_bytes, urlsplit

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.review_csv import write_rows   # noqa: E402
from peach.scripting import (   # noqa: E402
    add_ledger_write_args,
    counts_of,
    open_for_write,
    verify_after_write,
)
from peach.social_links import account_segment, host_of, platform   # noqa: E402

FIELDS = ("entity_id", "entity", "verdict", "link_id", "link_kind", "label", "url", "note")

EXTRA_COUNTS = {"entity_link": "SELECT count(*) FROM entity_link"}


def destination(url: str) -> str:
    """「这条链接指向哪里」的身份键。写法不同、去处相同的链接得到同一个键。"""
    name = platform(url)
    if name:
        handle = account_segment(url)
        # 平台的功能页(没有账号段)落回普通规则,否则一个域下所有功能页会挤成一条。
        if handle:
            return f"{name}\t{handle.casefold()}"
    parts = urlsplit(url.strip())
    path = unquote_to_bytes(parts.path).rstrip(b"/").decode("latin-1")
    return "\t".join((host_of(url), path, parts.query, parts.fragment))


def keep_order(item: dict) -> tuple:
    """同一组里留哪条。https 先于 http,再取最短,最后按 id 定序。

    只按最短取会把 `https://www.prestige-av.com/` 换成 `http://www.prestige-av.com/`:
    少一个字符,代价是资料页上一条本来加密的外链变成明文跳转。长度是用来挑出「账号
    主页而不是某条推文」的,不该顺带决定协议。末尾的 id 保证结果不随查询顺序变。
    """
    return (urlsplit(item["url"]).scheme != "https", len(item["url"]), item["link_id"])


def collect(connection) -> list[dict]:
    """逐实体找出指向同一个去处的多条链接,标出留哪条、删哪条。"""
    groups: dict[tuple[int, str], list[dict]] = {}
    for row in connection.execute(
            "SELECT l.id,l.entity_id,l.link_kind,l.label,l.url,e.canonical_name "
            "FROM entity_link l JOIN entity e ON e.id=l.entity_id ORDER BY l.id"):
        item = {"entity_id": row["entity_id"], "entity": row["canonical_name"],
                "link_id": row["id"], "link_kind": row["link_kind"],
                "label": row["label"], "url": row["url"]}
        groups.setdefault((row["entity_id"], destination(row["url"])), []).append(item)
    out: list[dict] = []
    for members in groups.values():
        if len(members) < 2:
            continue
        keep = min(members, key=keep_order)
        for item in members:
            same = item is keep
            out.append(dict(item, verdict="keep" if same else "delete",
                            note="" if same else f"与 {keep['link_id']} 同一个去处"))
    return out


def apply_rows(connection, rows) -> int:
    doomed = [row["link_id"] for row in rows if row["verdict"] == "delete"]
    for link_id in doomed:
        connection.execute("DELETE FROM entity_link WHERE id=?", (link_id,))
    return len(doomed)


def main() -> int:
    parser = argparse.ArgumentParser(description=__doc__)
    add_ledger_write_args(parser)
    parser.add_argument("--out", help="把逐条判定写成 CSV 复核产物")
    args = parser.parse_args()

    connection = open_for_write(args)
    try:
        rows = collect(connection)
        before = counts_of(connection, EXTRA_COUNTS)
        if args.out:
            write_rows(args.out, FIELDS, rows, atomic=True, fill_missing=True)
        groups = sum(1 for row in rows if row["verdict"] == "keep")
        doomed = sum(1 for row in rows if row["verdict"] == "delete")
        print(f"重复组 {groups},多余链接 {doomed}")
        for row in rows:
            if row["verdict"] == "delete":
                print(f"  删 {row['link_id']:5} {row['entity']}  {row['url']}")
        if not args.apply:
            print("dry-run:没有写入。加 --apply --backup <路径> 才真的删。")
            return 0
        removed = apply_rows(connection, rows)
        connection.commit()
        after = counts_of(connection, EXTRA_COUNTS)
        integrity, violations = verify_after_write(connection)
        print(f"删除 {removed} 条")
        for key in sorted(before):
            print(f"  {key}: {before[key]} -> {after[key]}")
        print(f"integrity_check={integrity} foreign_key_check={violations}")
        return 0
    finally:
        connection.close()


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