402 lines
19 KiB
Python
402 lines
19 KiB
Python
"""数据库会话与基类。支持 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")
|