nex_animation/backend/tests/test_platform_version_reuse.py

262 lines
14 KiB
Python
Raw Permalink Blame History

This file contains ambiguous Unicode characters!

This file contains ambiguous Unicode characters that may be confused with others in your current locale. If your use case is intentional and legitimate, you can safely ignore this warning. Use the Escape button to highlight these characters.

"""回归用例:平台母版删除泄漏版本行 + SQLite 主键复用导致登记 500。
现场缺陷(`POST /api/platform/human/9/generate` 返回 500,
`UNIQUE constraint failed: platform_media_versions.media_id, platform_media_versions.version_no`):
1. `delete_platform_asset` 用 `query(...).delete()` 批量删母版。批量 delete 是纯 SQL,
不走 `PlatformMedia.versions` 上的 `cascade="all, delete-orphan"`,SQLite 又没开
`PRAGMA foreign_keys`,于是子表 `platform_media_versions` 留下孤儿行;
2. `platform_media.id` 是 `INTEGER PRIMARY KEY` 且没有 AUTOINCREMENT,
删掉最大行后 SQLite 会**复用**同一个 id;
3. `register_platform_media` 无条件写 `version_no=1`,于是复用 id 的新母版撞上
孤儿行占用的 `(media_id, 1)` → `uq_platform_media_version` → IntegrityError → 500。
用例同时锁定「登记路径自带韧性」:版本号按子表实际 `max(version_no)+1` 推导,
因此修复前已经产生的脏数据(孤儿行)也能自愈,而不是继续 500。
"""
from __future__ import annotations
from pathlib import Path
from sqlalchemy import text
from app.db import SessionLocal
from app.models import Asset, AssetVersion, PlatformMedia
from app.services import storage
from tests.conftest import _make_image_png
# ------------------------------------------------------------------ 裸 SQL 断言小工具
# 用独立短会话读写,避免与 TestClient 的会话共享快照事务;
# 否则 SQLite 的只读事务会停在「接口调用之前」的视图上,断言看到的是旧数据。
def _rows(sql: str, **params) -> list:
with SessionLocal() as session:
try:
return [tuple(row) for row in session.execute(text(sql), params)]
finally:
session.rollback()
def _scalar(sql: str, **params):
rows = _rows(sql, **params)
return rows[0][0] if rows else None
def _version_nos(table: str, owner_column: str, owner_id: int) -> list[int]:
return sorted(row[0] for row in _rows(
f"SELECT version_no FROM {table} WHERE {owner_column} = :oid", oid=owner_id))
def _orphan_ids(child: str, child_column: str, parent: str) -> list[int]:
return sorted(row[0] for row in _rows(
f"SELECT DISTINCT c.{child_column} FROM {child} c "
f"LEFT JOIN {parent} p ON p.id = c.{child_column} WHERE p.id IS NULL"))
def _delete_row(table: str, row_id: int) -> None:
"""只删父表行、故意留下子表行,复刻「批量 delete 泄漏 + 主键复用」的脏库现场。"""
with SessionLocal() as session:
session.execute(text(f"DELETE FROM {table} WHERE id = :rid"), {"rid": row_id})
session.commit()
def _upload(api, owner_type, owner_id, *, kind="portrait", name="定妆图", color="0x224466"):
return api.post(
f"/api/platform/{'characters' if owner_type == 'human' else owner_type}/{owner_id}/media",
files={"file": (f"{name}.png", _make_image_png(180, 180, color), "image/png")},
data={"kind": kind, "name": name},
)["result"]
def _platform_orphans() -> list[int]:
return _orphan_ids("platform_media_versions", "media_id", "platform_media")
# ------------------------------------------------------------------ 1. 删除不得泄漏子行
def test_deleting_platform_master_removes_its_version_rows(api, db):
human = api.post("/api/platform/characters", json={"name": "泄漏校验", "role": "主角"})["result"]
first = _upload(api, "human", human["id"], name="定妆图")
second = _upload(api, "human", human["id"], kind="turnaround", name="三视图")
_upload(api, "human", human["id"], name="定妆图", color="0x1177aa") # 同名 → 升到 v2
assert _version_nos("platform_media_versions", "media_id", first["id"]) == [1, 2]
deleted = api.delete(f"/api/platform/human/{human['id']}")
assert deleted["ok"] is True and deleted["deleted"] == human["id"]
assert deleted["files_removed"] >= 2, "删除接口仍要清理媒体文件"
assert _scalar("SELECT COUNT(*) FROM platform_media") == 0
assert _orphan_ids("platform_media_versions", "media_id", "platform_media") == [], "不得残留孤儿版本行"
assert _scalar("SELECT COUNT(*) FROM platform_media_versions") == 0
assert _scalar("SELECT COUNT(*) FROM platform_characters WHERE id = :cid", cid=human["id"]) == 0
# 文件与事件日志语义保持不变
assert not storage.exists(first["path"]) and not storage.exists(second["path"])
assert _scalar("SELECT COUNT(*) FROM event_logs WHERE action = 'platform.asset.deleted'") == 1
def test_batch_delete_masters_leaves_no_orphans(api):
"""批量删除复用同一段逻辑,同样不能留孤儿版本行。"""
human = api.post("/api/platform/characters", json={"name": "批量甲"})["result"]
scene = api.post("/api/platform/scenes", json={"name": "批量乙", "prompt": "雨夜街边"})["result"]
_upload(api, "human", human["id"], name="甲定妆图")
_upload(api, "scene", scene["id"], kind="scene_image", name="乙全景")
body = api.post("/api/platform/assets/batch-delete", json={"items": [
{"owner_type": "human", "asset_id": human["id"]},
{"owner_type": "scene", "asset_id": scene["id"]},
]})["result"]
assert body["deleted_count"] == 2 and not body["failures"]
assert _platform_orphans() == []
def test_deleting_single_master_media_still_removes_only_its_rows(api):
"""单个素材删除走 ORM 级联,行为不受本次改动影响。"""
human = api.post("/api/platform/characters", json={"name": "单删校验"})["result"]
keep = _upload(api, "human", human["id"], name="留下")
drop = _upload(api, "human", human["id"], kind="turnaround", name="删掉")
_upload(api, "human", human["id"], name="留下", color="0x557733") # keep → v2
result = api.delete(f"/api/platform/media/{drop['id']}")
assert result["files_removed"] >= 1
assert _platform_orphans() == []
assert _scalar("SELECT COUNT(*) FROM platform_media_versions WHERE media_id = :mid",
mid=drop["id"]) == 0
assert _version_nos("platform_media_versions", "media_id", keep["id"]) == [1, 2]
# --------------------------------------------- 2. 母版删除后重建(主键复用)不得再撞唯一约束
def test_upload_after_master_deletion_reuses_id_without_integrity_error(api):
human = api.post("/api/platform/characters", json={"name": "保留母版"})["result"]
_upload(api, "human", human["id"], name="保留定妆图")
doomed = api.post("/api/platform/characters", json={"name": "待删母版"})["result"]
doomed_media = _upload(api, "human", doomed["id"], name="待删定妆图")
assert _scalar("SELECT MAX(id) FROM platform_media") == doomed_media["id"]
# 走接口删除(修复后子表同步清干净)
assert api.delete(f"/api/platform/human/{doomed['id']}")["files_removed"] >= 1
assert _platform_orphans() == []
assert _scalar("SELECT MAX(id) FROM platform_media") < doomed_media["id"], "最大主键已释放,可被复用"
again = api.post("/api/platform/characters", json={"name": "复用母版"})["result"]
media = _upload(api, "human", again["id"], name="复用后定妆图")
assert media["id"] == doomed_media["id"], "SQLite 复用了被删掉的最大主键"
assert media["version"] == 1 and _version_nos("platform_media_versions", "media_id", media["id"]) == [1]
bumped = _upload(api, "human", again["id"], name="复用后定妆图", color="0x993355")
assert bumped["id"] == media["id"] and bumped["version"] == 2
assert _version_nos("platform_media_versions", "media_id", media["id"]) == [1, 2]
def test_generate_after_reused_id_does_not_500(api):
"""端到端复现报错接口:主键复用 + 遗留孤儿行时 generate 必须成功且版本号正确递增。
三次生成串行等待(api.generate),因为同一条母版同一类素材在生成中会被去重挡住。
"""
human = api.post("/api/platform/characters", json={
"name": "生成复用", "appearance": "银色短发,深蓝机甲",
"persona_prompt": "冷静果决的领航员"})["result"]
payload = {"kind": "portrait", "provider": "mock", "model": "mock-image-1",
"prompt": "冷蓝定妆", "width": 240, "height": 240}
generated = api.generate(f"/api/platform/human/{human['id']}/generate", payload)
media_id = generated["id"]
assert generated["version"] == 1 and _version_nos("platform_media_versions", "media_id", media_id) == [1]
# 复刻现场:父行被删(旧代码会在这里泄漏子行),随后新母版复用同一 id
_delete_row("platform_media", media_id)
assert _platform_orphans() == [media_id]
reuse = api.post("/api/platform/characters", json={
"name": "生成复用", "appearance": "银色短发,深蓝机甲",
"persona_prompt": "冷静果决的领航员"})["result"]
assert reuse["id"] != human["id"]
second = api.generate(f"/api/platform/human/{reuse['id']}/generate", payload)
assert second["id"] == media_id, "新母版复用同一 media_id,正是当年 500 的触发条件"
assert second["version"] == 2, "首版号按子表现状推导,跳过孤儿行占用的 version_no=1"
assert _version_nos("platform_media_versions", "media_id", media_id) == [1, 2]
assert Path(storage.to_absolute(second["path"])).exists()
third = api.generate(f"/api/platform/human/{reuse['id']}/generate", payload)
assert third["version"] == 3 and _version_nos("platform_media_versions", "media_id", media_id) == [1, 2, 3]
def test_register_platform_media_self_heals_legacy_orphan_rows(api):
"""纯 service 层:孤儿行 + 主键复用下登记不抛 IntegrityError,父行 current_version 与子表对齐。"""
human = api.post("/api/platform/characters", json={"name": "脏数据母版"})["result"]
first = _upload(api, "human", human["id"], name="脏数据定妆图")
second = _upload(api, "human", human["id"], kind="turnaround", name="脏数据三视图")
_upload(api, "human", human["id"], kind="turnaround", name="脏数据三视图", color="0x993355")
assert _version_nos("platform_media_versions", "media_id", second["id"]) == [1, 2]
# 只删父行:turnaround 的 (id,1)/(id,2) 与 portrait 的 (id,1) 全部变成孤儿行
_delete_row("platform_media", second["id"])
assert _platform_orphans() == [second["id"]]
with SessionLocal() as session:
reused = storage.register_platform_media(
session, owner_type="human", owner_id=human["id"], kind="turnaround",
path=f"platform/human-{human['id']}/turnaround/legacy.png",
name="脏数据三视图", source="upload",
info={"checksum": "c1", "size_bytes": 10, "mime": "image/png"},
)
session.commit()
assert reused.id == second["id"], "SQLite 复用被删掉的最大主键"
# 子表已占用 1、2,父行 current_version 却是默认值 1:必须按子表最大值推导才不撞约束
assert reused.current_version == 3
assert _version_nos("platform_media_versions", "media_id", reused.id) == [1, 2, 3]
assert [v.version_no for v in session.get(PlatformMedia, reused.id).versions] == [3, 2, 1]
# 母版仍然存在时同样不能回退:孤儿行 + 落后于子表的 current_version 都要让版本号继续前进
with SessionLocal() as session:
session.execute(text("UPDATE platform_media SET current_version = 1 WHERE id = :mid"),
{"mid": first["id"]})
session.commit()
bumped = storage.register_platform_media(
session, owner_type="human", owner_id=human["id"], kind="portrait",
path=f"platform/human-{human['id']}/portrait/legacy2.png",
name="脏数据定妆图", source="upload", info={"checksum": "c2", "size_bytes": 11},
)
session.commit()
assert bumped.id == first["id"] and bumped.current_version == 2
assert _version_nos("platform_media_versions", "media_id", first["id"]) == [1, 2]
# ---------------------------------------------------------------- 3. 同类隐患:Asset / AssetVersion
def test_register_asset_survives_orphan_rows_and_reused_primary_key(api, studio):
"""`register_asset` 与 `register_platform_media` 同构,同样不得硬编码 version_no=1。"""
pid, character = studio["project_id"], studio["character_id"]
uploaded = api.post(f"/api/projects/{pid}/characters/{character}/assets",
files={"file": ("portrait.png", _make_image_png(240, 240, "0x113355"), "image/png")},
data={"kind": "portrait", "name": "复用前定妆图"})["result"]
asset_id = uploaded["id"]
assert uploaded["version"] == 1
assert _version_nos("asset_versions", "asset_id", asset_id) == [1]
_delete_row("assets", asset_id)
assert _orphan_ids("asset_versions", "asset_id", "assets") == [asset_id]
with SessionLocal() as session:
asset = storage.register_asset(
session, project_id=pid, owner_type="human", owner_id=character, kind="portrait",
path=f"{pid}/portrait/reused.png", name="复用前定妆图", source="upload",
info={"checksum": "abc", "size_bytes": 10},
)
session.commit()
assert asset.id == asset_id, "SQLite 复用被删掉的最大主键"
assert asset.current_version == 2, "避开孤儿行占用的 version_no=1"
assert _version_nos("asset_versions", "asset_id", asset_id) == [1, 2]
assert [v.version_no for v in session.get(Asset, asset_id).versions] == [2, 1]
second = storage.register_asset(
session, project_id=pid, owner_type="human", owner_id=character, kind="portrait",
path=f"{pid}/portrait/reused2.png", name="复用前定妆图", source="upload",
info={"checksum": "def", "size_bytes": 11},
)
session.commit()
assert second.id == asset_id and second.current_version == 3
assert _version_nos("asset_versions", "asset_id", asset_id) == [1, 2, 3]
assert _scalar("SELECT current_version FROM assets WHERE id = :aid", aid=asset_id) == 3
# 父行复用后重新存在,孤儿版本行被「收养」,不再有 UNIQUE 冲突风险
assert _orphan_ids("asset_versions", "asset_id", "assets") == []
with SessionLocal() as session:
rows = session.query(AssetVersion).filter(AssetVersion.asset_id == asset_id).all()
assert sorted(row.version_no for row in rows) == [1, 2, 3]
assert len({row.version_no for row in rows}) == 3, "同一 asset_id 下版本号必须唯一"