262 lines
14 KiB
Python
262 lines
14 KiB
Python
"""回归用例:平台母版删除泄漏版本行 + 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 下版本号必须唯一"
|