nex_animation/backend/app/db.py

402 lines
19 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.

"""数据库会话与基类。支持 PostgreSQL(生产)与 SQLite(本地自检)。"""
from __future__ import annotations
import logging
import os
import threading
import time
import traceback
import weakref
from collections.abc import Callable, Iterator
from sqlalchemy import create_engine, event, inspect, text
from sqlalchemy.schema import CreateColumn
from sqlalchemy.orm import DeclarativeBase, Session, sessionmaker
from sqlalchemy.pool import NullPool, StaticPool
from app.config import settings
log = logging.getLogger("nex.db")
class Base(DeclarativeBase):
pass
def _sqlite_pragmas(dbapi_conn, _record=None) -> None:
"""本地 SQLite 的连接级调优;PostgreSQL 不走这里。
三条各解决一个真实卡点:
1. ``journal_mode=WAL``——默认 delete 模式下写事务会锁整库,而内联守护线程
每 5s 就提交一次调度/状态回写,浏览资产库这类只读请求会被排在锁后面,
表现就是「页面转圈、越用越卡」。WAL 让读写并发。
2. ``busy_timeout``——出厂是 0,抢不到锁当场抛 database is locked,
给 5s 让短事务自己排队过去,而不是把 500 抛给前端。
3. ``synchronous=NORMAL``——WAL 下的推荐档,省掉每次提交的 fsync,
写事务从十几毫秒降到毫秒级。
注意 journal_mode 会写进库文件头,设一次对已存在的库同样生效;
busy_timeout 是连接属性,必须对每个新连接执行。
foreign_keys 等语义相关开关一律不动。
"""
cursor = dbapi_conn.cursor()
try:
cursor.execute("PRAGMA journal_mode=WAL")
cursor.execute("PRAGMA synchronous=NORMAL")
# 内联执行器是多线程的:关键帧、视频、配音同时在跑时,心跳/状态回写
# 会挤在同一把写锁上。5s 挡不住这种突发排队,一次 database is locked
# 就把跑了 4 分钟的出图判成 runtime_error(项目 9 镜头 02 实测)。
cursor.execute("PRAGMA busy_timeout=15000")
finally:
cursor.close()
def _pool_kwargs(url: str) -> dict:
"""连接池选型(拆出来是为了能在没有 Postgres 驱动的机器上自检)。"""
if not url.startswith("sqlite"):
return {"pool_pre_ping": True, "pool_size": 10, "max_overflow": 20}
if ":memory:" in url or url in ("sqlite://", "sqlite:///"):
# 内存库:所有线程必须共享同一条连接,否则第二个连接看到的是空库。
return {"pool_pre_ping": True, "poolclass": StaticPool}
return {"poolclass": NullPool}
def _build_engine(url: str):
"""按方言挑连接池:SQLite 用 NullPool,PostgreSQL 用默认 QueuePool。
SQLite 为什么必须放弃连接池(一次真实翻车换来的):
QueuePool 的出厂参数是 5 常驻 + 10 溢出,而本项目的会话**生来就很长命**——
内联 worker 一条会话要跨过几分钟的供应商调用(ComfyUI 还是 GPU 串行的),
LLM 总控台一轮调度握着一条会话能跑几小时,心跳线程每个心跳再借一条。
15 条连接被这类长会话占满之后,后来的请求只能在池口排队,等满 30 秒抛出
``QueuePool limit of size 5 overflow 10 reached``,任务被误判成生成失败:
《大漠客栈二番局》shot 30/31 就是这么连烧三次重试、白白丢掉两条视频。
换成 NullPool 之后:
1. 池口不存在了——每次借用新开连接、归还即真关闭,并发上限等于「此刻真在跑的
线程数」,由线程池与请求并发天然决定,再不会排队 30 秒后超时。
2. 空闲连接不再长期握着 WAL 读快照,checkpoint 能把 -wal 截断回小块。
3. 代价可接受:本机打开一个 SQLite 文件连接是微秒级(外加 3 条 PRAGMA),
跟 Postgres 的 TCP+认证不是一个量级,池化收益本来就接近零;既然不复用,
pre_ping 也没有意义,一并关掉少一次往返。
内存库(自检用)继续 StaticPool:那条路径上所有线程必须共享同一条连接,
否则第二个连接看到的是空库。
"""
kwargs: dict = {"future": True, **_pool_kwargs(url)}
if url.startswith("sqlite"):
kwargs["connect_args"] = {"check_same_thread": False}
if ":memory:" not in url and url not in ("sqlite://", "sqlite:///"):
# SQLite 不会自己创建父目录,缺失时只报「unable to open database file」,
# 对刚配好 .env 的使用者几乎无法排查,这里直接替他把目录建好。
from pathlib import Path
from sqlalchemy.engine.url import make_url
database = make_url(url).database
if database:
Path(database).expanduser().parent.mkdir(parents=True, exist_ok=True)
built = create_engine(url, **kwargs)
if url.startswith("sqlite"):
event.listens_for(built, "connect")(_sqlite_pragmas)
return built
engine = _build_engine(settings.database_url)
# ---------------------------------------------------------------- 会话留痕
# 池子被占满时,pool.status() 只能告诉你「15 条都在外面」,问不出是谁借走的。
# 设 NEX_DB_DEBUG_SESSIONS=1 启动后,每条会话记下自己的出生线程与调用栈,
# /api/system/db-debug 就会列出「还活着且没 close 的会话」并按年龄排序。
# 默认关闭:留痕只在会话出生时多存一条记录,不改动正常路径的语义。
_trace_enabled = os.environ.get("NEX_DB_DEBUG_SESSIONS", "").strip().lower() in {"1", "true", "yes", "on"}
_tracked: "weakref.WeakKeyDictionary[Session, dict]" = weakref.WeakKeyDictionary()
_tracked_lock = threading.Lock()
class _Session(Session):
"""可选留痕的 Session;除留痕之外与 Session 完全一致。"""
def __init__(self, *args, **kwargs) -> None:
if _trace_enabled:
entry = {
"thread": threading.current_thread().name,
"since": time.monotonic(),
"stack": "".join(traceback.format_stack()[:-1][-7:-1]),
"closed": False,
}
with _tracked_lock:
_tracked[self] = entry
super().__init__(*args, **kwargs)
def close(self) -> None:
if _trace_enabled:
with _tracked_lock:
entry = _tracked.get(self)
if entry is not None:
entry["closed"] = True
super().close()
SessionLocal = sessionmaker(bind=engine, autoflush=False, autocommit=False, expire_on_commit=False,
class_=_Session)
def pool_status() -> dict:
"""连接池现状,供 /health 直接读出,用来回答「是不是又在池口排队」。
只有池化模型才有借还计数:NullPool 每条连接用完即关,没有「占用」概念,
这时连接数就等于此刻真在跑任务的线程数,没有上限可撞。
"""
pool = engine.pool
status = {"class": type(pool).__name__}
for key, name in (("checked_out", "checkedout"), ("checked_in", "checkedin"), ("overflow", "overflow")):
probe = getattr(pool, name, None)
if callable(probe):
status[key] = int(probe())
if _trace_enabled:
# NullPool 没有「池内占用」可看,留痕开启时直接用活着的会话数代替。
with _tracked_lock:
status["open_sessions"] = sum(1 for entry in list(_tracked.values()) if not entry["closed"])
return status
def session_trace_enabled() -> bool:
return _trace_enabled
def open_sessions(*, limit: int = 20) -> list[dict]:
"""当前借出未归还的会话(含年龄与出生调用栈);仅在留痕开启时有内容。"""
if not _trace_enabled:
return []
now = time.monotonic()
with _tracked_lock:
rows = [dict(entry, age_seconds=round(now - entry["since"], 1))
for entry in list(_tracked.values()) if not entry["closed"]]
rows.sort(key=lambda row: row["age_seconds"], reverse=True)
return rows[:max(1, limit)]
def get_db() -> Iterator[Session]:
db = SessionLocal()
try:
yield db
finally:
db.close()
# 启动期等锁预算:热重载/双进程交替时最容易被踩到(见 _wait_for_lock 的说明)。
STARTUP_LOCK_BUDGET_SECONDS = float(os.environ.get("NEX_DB_STARTUP_LOCK_BUDGET", "60"))
def is_lock_error(exc: BaseException) -> bool:
"""是否是「库被别人占着」这一类错误(可以等),而不是语句本身写错了。"""
if "database is locked" in str(exc).lower() or "database table is locked" in str(exc).lower():
return True
return type(exc).__name__ in ("OperationalError", "DatabaseError") and "locked" in str(exc).lower()
def wait_for_lock(step: str, run: Callable[[], None], *, budget: float | None = None,
interval: float = 1.0) -> None:
"""跑一条启动期维护语句,遇到 SQLite 文件锁就等一会儿再试。
为什么必须有这一层(本轮线上现象):后端用 ``--reload`` 跑,改一次文件就换一次
worker,而**上一个 worker 可能正握着写事务**——内联生成任务的会话要跨过几分钟的
ComfyUI 调用,长会话是这套架构的常态。新 worker 的 ``init_db()`` 抢不到锁,
``busy_timeout``(5s)到点抛 ``database is locked`` → ``Application startup failed.
Exiting.``。更要命的是 ``--reload`` 的父进程还占着 8000 端口:TCP 连得上、永远不
响应,整个平台看起来就是「流程卡住了」,而日志里那句 startup failed 藏在
几千行 SQLAlchemy traceback 中间。这些维护语句都是幂等的 ``UPDATE``/``ALTER``,
等得起,也**只该等锁**——语句真的写错时立刻抛出,别把 bug 藏成 60s 的停顿。
"""
limit = STARTUP_LOCK_BUDGET_SECONDS if budget is None else float(budget)
started = time.monotonic()
attempts = 0
while True:
attempts += 1
try:
return run()
except Exception as exc: # noqa: BLE001
if not is_lock_error(exc) or time.monotonic() - started >= max(0.0, limit):
if not is_lock_error(exc):
raise
raise RuntimeError(
f"数据库启动维护({step})等锁 {time.monotonic() - started:.0f}s 仍被占用,"
"后端未能启动。通常是上一个后端进程还没退出(或另一个进程正持有同一份 "
f"SQLite 文件写事务);等它退出或把 "
"PRAGMA busy_timeout 调大后重试即可。最后一次错误:"
f"{type(exc).__name__}: {str(exc)[:200]}"
) from exc
time.sleep(min(interval, max(0.05, limit - (time.monotonic() - started))))
def _maintenance(fn: Callable[[], None], step: str) -> None:
return wait_for_lock(step, fn)
def init_db() -> None:
"""开发/自建环境建表,并为旧数据库补齐新增列。
自建环境没有 Alembic,但升级代码时不能要求用户删库重建。这里先 create_all
创建新表,再对比 ORM 元数据与真实列;只补缺失列,不删改已有数据。
新补列一律按 nullable 创建,便于旧数据行平滑升级。
建表之后的补列/放开非空/回填三步都走 ``wait_for_lock``:抢不到文件锁时等上一个进程退出,
而不是让整个后端起不来(起不来时端口还占着,表现是「全站无响应」)。
``_relax_nullability`` 是这里唯一会**改已有列**的一步,因为只有它避不开:
平台资产接入统一任务表之后 ``tasks.project_id`` 必须可空,
而 SQLite 的 ALTER TABLE 改不了列约束——只能整表重建(数据逐列搬过去)。
"""
from app import models # noqa: F401 确保模型全部注册
wait_for_lock("建表 create_all", lambda: Base.metadata.create_all(bind=engine))
_maintenance(_add_missing_columns, "补齐新增列")
_maintenance(_relax_nullability, "放开新增可空列")
_maintenance(_backfill_defaults, "回填新增列默认值")
def _add_missing_columns() -> None:
inspector = inspect(engine)
existing_tables = set(inspector.get_table_names())
with engine.begin() as conn:
for table in Base.metadata.sorted_tables:
if table.name not in existing_tables:
continue
existing = {column["name"] for column in inspector.get_columns(table.name)}
for column in table.columns:
if column.name in existing:
continue
copy = column._copy()
copy.nullable = True
copy.server_default = None
ddl = str(CreateColumn(copy).compile(dialect=engine.dialect))
conn.execute(text(f'ALTER TABLE "{table.name}" ADD COLUMN {ddl}'))
def _relax_nullability() -> None:
"""把「ORM 已放开、老库里却还是 NOT NULL」的列改成可空。
为什么只此一列要命:``tasks.project_id`` 建库时是必填,因为当时所有异步工作
都挂在某个项目下;平台资产库的 AI 生成接入同一张任务表之后,它没有项目可挂
(平台母版本就不属于任何项目)。硬塞 0/-1 会让每一条按项目统计的 SQL
(容量、成本、流程进度)多背一个特例,所以老老实实放开约束。
SQLite 没有 ``ALTER COLUMN ... DROP NOT NULL``,只能整表重建:
按 ORM 元数据建新表 → 逐列搬数据(只搬两边都有的列)→ 删旧表 → 改名 → 重建索引。
搬完再核对行数,对不上就抛错:宁可后端起不来,也不能悄悄少几十条任务履历。
"""
inspector = inspect(engine)
existing_tables = set(inspector.get_table_names())
dialect = engine.dialect.name
for table in Base.metadata.sorted_tables:
if table.name not in existing_tables:
continue
db_columns = {column["name"]: column for column in inspector.get_columns(table.name)}
stale = [column.name for column in table.columns
if column.nullable and column.name in db_columns
and not db_columns[column.name]["nullable"] and not column.primary_key]
if not stale:
continue
if dialect == "sqlite":
with engine.begin() as conn:
before = conn.execute(text(f'SELECT COUNT(*) FROM "{table.name}"')).scalar() or 0
_rebuild_sqlite_table(conn, inspector, table, set(db_columns))
after = conn.execute(text(f'SELECT COUNT(*) FROM "{table.name}"')).scalar() or 0
if before != after:
raise RuntimeError(f"重建 {table.name} 后行数不一致({before} → {after}),已中止启动")
log.info("已放开 %s.%s 的 NOT NULL(整表重建,%d 行数据已搬运)",
table.name, "、".join(stale), after)
elif dialect in ("postgresql", "mysql", "mariadb"):
with engine.begin() as conn:
for name in stale:
conn.execute(text(f'ALTER TABLE "{table.name}" ALTER COLUMN "{name}" DROP NOT NULL'))
log.info("已放开 %s.%s 的 NOT NULL", table.name, "、".join(stale))
else:
log.warning("方言 %s 未实现放开非空,%s.%s 仍是 NOT NULL",
dialect, table.name, "、".join(stale))
def _literal(column) -> str:
"""按列类型给一个「空值当没有」的兜底字面量(与 Python 侧 default 同语义)。"""
from sqlalchemy import Boolean, Date, DateTime, Float, Integer, Interval, Numeric, Time
if isinstance(column.type, Interval):
return "'00:00:00'"
if isinstance(column.type, (DateTime, Date, Time)):
return "'1970-01-01 00:00:00'"
if isinstance(column.type, Boolean):
return "0"
if isinstance(column.type, (Integer, Float, Numeric)):
return "0"
if column.type.__class__.__name__ in ("JSON",):
return "'{}'"
return "''"
def _rebuild_sqlite_table(conn, inspector, table, db_columns: set[str]) -> None:
"""按 ORM 元数据重建一张 SQLite 表(列定义以模型为准,数据逐列搬运)。"""
from sqlalchemy.schema import CreateIndex, CreateTable
temp = f"_nex_migrate_{table.name}"
# 镜像表借 Base.metadata 建:新建的 MetaData 里没有 projects/shots,
# 编译外键时会报 NoReferencedTableError。用完即摘,别把临时表留在元数据里。
mirror = table.to_metadata(Base.metadata, name=temp)
# 只搬两边都有的列:库里多出来的历史列(早已从模型里删掉)不值得再留一份空壳
db_null = {column["name"] for column in inspector.get_columns(table.name) if column["nullable"]}
shared = [column.name for column in table.columns if column.name in db_columns]
quote = lambda name: '"' + name.replace('"', '""') + '"'
def source(column) -> str:
"""搬运表达式:刚补进来的列在旧行里全是 NULL,而新表按模型是 NOT NULL。
``_add_missing_columns`` 只能把新列加成可空(SQLite 的限制),
所以这里必须给个兜底值,否则「补列 → 重建」这两步自己打架:
一条 NOT NULL 的老库记录搬进新表就会当场抛 IntegrityError。
"""
name = quote(column.name)
if column.nullable or column.name not in db_null:
return name
return f"COALESCE({name}, {_literal(column)})"
try:
conn.execute(text(str(CreateTable(mirror).compile(dialect=engine.dialect))))
finally:
Base.metadata.remove(mirror)
columns = ", ".join(quote(column.name) for column in table.columns
if column.name in db_columns)
values = ", ".join(source(column) for column in table.columns if column.name in db_columns)
conn.execute(text(f'INSERT INTO {quote(temp)} ({columns}) '
f'SELECT {values} FROM {quote(table.name)}'))
conn.execute(text(f'DROP TABLE {quote(table.name)}'))
# 改名放在建索引之前:索引挂在表对象上,DDL 里写的是正式表名,
# 先建就会指向还不存在的表。
conn.execute(text(f'ALTER TABLE {quote(temp)} RENAME TO {quote(table.name)}'))
for index in table.indexes:
conn.execute(text(str(CreateIndex(index).compile(dialect=engine.dialect))))
def _backfill_defaults() -> None:
"""只回填代码依赖的新增标量列;JSON 列由读取侧继续兼容 null。"""
statements = [
"UPDATE model_specs SET model_code='' WHERE model_code IS NULL",
"UPDATE model_specs SET timeout_seconds=120 WHERE timeout_seconds IS NULL",
"UPDATE model_specs SET api_key_encrypted='' WHERE api_key_encrypted IS NULL",
"UPDATE characters SET platform_source_type='' WHERE platform_source_type IS NULL",
"UPDATE scenes SET platform_source_type='' WHERE platform_source_type IS NULL",
"UPDATE voice_assets SET platform_source_type='' WHERE platform_source_type IS NULL",
# 统一任务表新列:旧行没有归属与状态说明,补齐空串,读取侧不必再判 null
"UPDATE tasks SET owner_type='' WHERE owner_type IS NULL",
"UPDATE tasks SET message='' WHERE message IS NULL",
]
with engine.begin() as conn:
for sql in statements:
conn.execute(text(sql))
def supports_row_locking() -> bool:
return engine.dialect.name in ("postgresql", "mysql", "mariadb", "oracle")