- pyrightconfig.json 钉死 Python 3.12 虚拟环境,消除由于查找系统 Python 3.9 带来的假性报错 - 修复 huya/login.py 引用不存在函数的问题并补充单元测试 - 修复 browser_harvest.py 对 set 下标访问的错误并提升报错信息质量 - 虎牙 6 个子路由显式导入依赖,消除 import * 带来的未定义变量报警 - 规范化 Pydantic Field(default=...) 默认值声明 - 完善 solver.py / app_login.py 等核心路径类型守卫与 @overload 重载 - 在 pyproject.toml 中显式锁定 ruff lint 规则集 (E4, E7, E9, F, B, I, BLE) - 门禁脚本同步增加 ruff check 检查并在 AGENTS.md 补齐实践经验总结
210 lines
8.3 KiB
Python
210 lines
8.3 KiB
Python
"""作业:清理 `douyu_tasks` 的历史任务日志。
|
||
|
||
## 为什么需要
|
||
|
||
`douyu_tasks` 是**执行日志**,不是账号历史。它按天增长(生产实测 222,679 行 /
|
||
687MB,且绝大多数是例行查询),而其中真正有长期价值的业务事实,S5 之后都会
|
||
由业务代码在发生那一刻写进 `account_events`(体量小几个数量级)。
|
||
|
||
所以这里只做一件事:**按任务类型的价值分档,把过期的执行日志删掉。**
|
||
|
||
## 分档依据(关键设计)
|
||
|
||
不是「按时间一刀切」,而是按「这条日志里的 `result` 还有没有人需要」分三档:
|
||
|
||
| 档 | 判定 | 默认保留 | 说明 |
|
||
| --- | --- | --- | --- |
|
||
| **噪声查询** | 只读、看一眼就走,且前端**不**用它的 `result` 派生账号状态 | 7 天 | 体量最大的就是这类(`query_limited_goods` 单类 6.7 万行) |
|
||
| **状态派生** | 前端从任务列表反推账号当前状态时要读它(`latest*ByAccount`) | 90 天 | 删早了工作台账号行的「已绑定/未绑定」会变成「未查」 |
|
||
| **业务事实** | 真花钱/花积分/改变账号归属的动作 | 90 天 | 90 天后仍有 `account_events` 兜底 |
|
||
|
||
⚠️ **为什么「状态派生」类不能按 7 天删**:工作台是从任务列表**反推**账号状态的
|
||
(`DouyuTasksPage` 的 `latestConfirmBindTaskByAccount` 等),而且明确要求
|
||
`task.result` 非空。这是历史包袱(和 `bind_status` 同源:账号的「现在是什么」
|
||
存在执行日志里),设计文档 §3.6 的状态列拆解才是根治。在拆解完成之前,
|
||
这里保守地把这类查询留长一点 —— 否则会把「当前显示」搞坏,那不是在清历史。
|
||
|
||
两类窗口都可以在 `/automation` 页面按作业改(`config.noise_days` /
|
||
`config.business_days`),不必发版。
|
||
|
||
## 安全约束
|
||
|
||
- **绝不删在途任务**(`planned` / `pending` / `running`)—— 批次线程可能正拿着它;
|
||
- 分批删(每批 `batch_size` 行、最多 `max_batches` 批),避免长事务长时间锁表;
|
||
- 不删行、只删「过期的整行」:不做「把 result 清空保留行」这种半吊子操作,
|
||
因为部分前端派生**明确要求 result 非空**,清空等于制造「有行但读不出状态」的
|
||
更隐蔽的坏状态;
|
||
- 作业**默认关闭**(`ensure_jobs` 建行时 enabled=False),由运维在页面上打开。
|
||
"""
|
||
|
||
from __future__ import annotations
|
||
|
||
import json
|
||
from datetime import UTC, datetime, timedelta
|
||
from typing import Any, cast
|
||
|
||
from loguru import logger
|
||
from sqlalchemy import CursorResult, text
|
||
from sqlalchemy.orm import Session
|
||
|
||
from ....models import AutomationJob
|
||
|
||
JOB_KEY = "douyu_tasks_retention"
|
||
|
||
#: 只读、看一眼就走,且前端**不**拿它派生账号状态 → 短保留。
|
||
#: 体量最大的就在这一类(`query_limited_goods` / `query_change_bind_time`)。
|
||
NOISE_TASK_TYPES: tuple[str, ...] = (
|
||
"query_limited_goods",
|
||
"query_change_bind_time",
|
||
"query_gold_balance",
|
||
"query_xpd_balance",
|
||
"query_xpd_fragments",
|
||
"query_points",
|
||
"refresh_goods",
|
||
"refresh_esports_goods",
|
||
"refresh_xpd_goods",
|
||
"prefetch_csrf_token",
|
||
)
|
||
|
||
DEFAULT_NOISE_DAYS = 7
|
||
DEFAULT_BUSINESS_DAYS = 90
|
||
DEFAULT_BATCH = 2000
|
||
DEFAULT_MAX_BATCHES = 50
|
||
|
||
#: 在途任务绝不可删:批次线程正拿着这些行。
|
||
IN_FLIGHT_STATUSES = ("planned", "pending", "running")
|
||
|
||
|
||
def _load_config(job: AutomationJob) -> dict[str, Any]:
|
||
config = job.config
|
||
if isinstance(config, str):
|
||
try:
|
||
config = json.loads(config)
|
||
except ValueError:
|
||
config = {}
|
||
return dict(config) if isinstance(config, dict) else {}
|
||
|
||
|
||
def _positive_int(value: Any, default: int) -> int:
|
||
try:
|
||
parsed = int(value)
|
||
except (TypeError, ValueError):
|
||
return default
|
||
return parsed if parsed > 0 else default
|
||
|
||
|
||
def _delete_batch(
|
||
db: Session,
|
||
*,
|
||
task_types: tuple[str, ...],
|
||
cutoff: datetime,
|
||
batch: int,
|
||
negate: bool = False,
|
||
) -> int:
|
||
"""删一批过期行,返回删除条数。
|
||
|
||
`negate=True` 时语义变成「**除这些类型之外**的都在这一档」—— 长保留档就是这么
|
||
表达的。这样做有个重要的安全属性:**新增的任务类型默认落在长保留档**,
|
||
不会因为「忘了往某个列表里加」而在 7 天后被误删(收集器当年就是反过来,
|
||
忘了加 allowlist 就静默丢数据)。
|
||
|
||
`LIMIT` 让单条语句不会长时间持锁。
|
||
"""
|
||
if not task_types:
|
||
return 0
|
||
placeholders = ", ".join(f":t{i}" for i in range(len(task_types)))
|
||
status_placeholders = ", ".join(f":s{i}" for i in range(len(IN_FLIGHT_STATUSES)))
|
||
operator = "NOT IN" if negate else "IN"
|
||
params: dict[str, Any] = {
|
||
"cutoff": cutoff,
|
||
"limit": batch,
|
||
**{f"t{i}": t for i, t in enumerate(task_types)},
|
||
**{f"s{i}": s for i, s in enumerate(IN_FLIGHT_STATUSES)},
|
||
}
|
||
result = db.execute(
|
||
text(
|
||
f"""
|
||
DELETE FROM douyu_tasks
|
||
WHERE task_type {operator} ({placeholders})
|
||
AND status NOT IN ({status_placeholders})
|
||
AND created_at < :cutoff
|
||
LIMIT :limit
|
||
"""
|
||
),
|
||
params,
|
||
)
|
||
# Session.execute 的静态返回类型是 Result[Any],但 DML 语句实际拿到的是
|
||
# CursorResult(rowcount 只定义在它上面)。运行期行为不变,只是把这一点写实。
|
||
return int(cast(CursorResult[Any], result).rowcount or 0)
|
||
|
||
|
||
def run_douyu_tasks_retention(db: Session, job: AutomationJob) -> dict[str, Any]:
|
||
config = _load_config(job)
|
||
noise_days = _positive_int(config.get("noise_days"), DEFAULT_NOISE_DAYS)
|
||
business_days = _positive_int(config.get("business_days"), DEFAULT_BUSINESS_DAYS)
|
||
batch = _positive_int(job.batch_size, DEFAULT_BATCH)
|
||
max_batches = _positive_int(config.get("max_batches"), DEFAULT_MAX_BATCHES)
|
||
|
||
now = datetime.now(UTC)
|
||
# 传 naive UTC:created_at 是 UTCDateTime 列,裸 SQL 参数不经过类型转换,
|
||
# 带 tzinfo 会被驱动按本地时区处理(参见设计文档里那次 8 小时偏移事故)。
|
||
noise_cutoff = (now - timedelta(days=noise_days)).replace(tzinfo=None)
|
||
business_cutoff = (now - timedelta(days=business_days)).replace(tzinfo=None)
|
||
|
||
total_deleted = 0
|
||
batches = 0
|
||
|
||
# 两档:噪声查询只留 noise_days;**其余一切**(业务事实 + 前端状态派生依赖的
|
||
# 查询 + 以后新增的类型)留 business_days。
|
||
passes = (
|
||
(noise_cutoff, NOISE_TASK_TYPES, False, "noise"),
|
||
(business_cutoff, NOISE_TASK_TYPES, True, "rest"),
|
||
)
|
||
per_pass: dict[str, int] = {}
|
||
for cutoff, task_types, negate, label in passes:
|
||
deleted_this_pass = 0
|
||
while batches < max_batches:
|
||
deleted = _delete_batch(
|
||
db,
|
||
task_types=task_types,
|
||
cutoff=cutoff,
|
||
batch=batch,
|
||
negate=negate,
|
||
)
|
||
batches += 1
|
||
total_deleted += deleted
|
||
deleted_this_pass += deleted
|
||
db.commit()
|
||
if deleted < batch:
|
||
break
|
||
per_pass[label] = deleted_this_pass
|
||
|
||
# 注意措辞:这只说明「批次预算用光了」,**不等于**「确实还有积压」——
|
||
# 预算在上一档用完时,下一档根本没轮到执行,这里无法区分「没得删」和「没轮到」。
|
||
# 所以做成提示而非结论,措辞上也别谎称「仍有积压」。
|
||
batch_budget_exhausted = batches >= max_batches
|
||
if batch_budget_exhausted:
|
||
logger.warning(
|
||
"任务日志清理用光了批次预算({} 批,共删 {} 条);"
|
||
"若日志量持续很大,可调大 max_batches 或 batch_size",
|
||
max_batches,
|
||
total_deleted,
|
||
)
|
||
|
||
message = (
|
||
f"清理 {total_deleted} 条任务日志"
|
||
f"(噪声>{noise_days}天 / 其余>{business_days}天,{batches} 批)"
|
||
)
|
||
logger.info(message)
|
||
return {
|
||
"scanned": total_deleted,
|
||
"deleted": total_deleted,
|
||
"deleted_noise": per_pass.get("noise", 0),
|
||
"deleted_rest": per_pass.get("rest", 0),
|
||
"batches": batches,
|
||
"noise_days": noise_days,
|
||
"business_days": business_days,
|
||
"batch_budget_exhausted": batch_budget_exhausted,
|
||
"message": message,
|
||
}
|