| 1234567891011121314151617181920212223242526272829303132333435363738394041424344454647 |
- """请求和后台各自拥有会话；短写事务避免模型等待期间占用 SQLite 写锁。"""
- import asyncio
- from contextlib import asynccontextmanager
- from sqlalchemy import event, text
- from sqlalchemy.ext.asyncio import create_async_engine, async_sessionmaker
- from app.shared.config import settings
- from app.domain.models import Base
- settings.data_path.mkdir(parents=True, exist_ok=True)
- engine = create_async_engine(settings.db_url, connect_args={"timeout": 30})
- Session = async_sessionmaker(engine, expire_on_commit=False)
- write_lock = asyncio.Lock()
- @event.listens_for(engine.sync_engine, "connect")
- def configure_sqlite(connection, record):
- """外键和 WAL 不依赖客户端设置，否则不同启动路径会产生不一致数据。"""
- cursor = connection.cursor()
- cursor.execute("PRAGMA foreign_keys=ON")
- cursor.execute("PRAGMA journal_mode=WAL")
- cursor.execute("PRAGMA busy_timeout=30000")
- cursor.close()
- @asynccontextmanager
- async def transaction():
- """先领取写锁再读取要修改的数据，避免 deferred 事务读后升级引发快照冲突。"""
- async with write_lock:
- async with Session() as session:
- await session.execute(text("BEGIN IMMEDIATE"))
- try:
- yield session
- await session.commit()
- except Exception:
- await session.rollback()
- raise
- async def initialize():
- """首次建表并标记初始迁移；新版本变更应通过 Alembic，不能靠 create_all 改旧表。"""
- async with engine.begin() as connection:
- await connection.run_sync(Base.metadata.create_all)
- await connection.execute(text("CREATE TABLE IF NOT EXISTS alembic_version (version_num VARCHAR(32) NOT NULL PRIMARY KEY)"))
- await connection.execute(text("INSERT OR IGNORE INTO alembic_version(version_num) VALUES ('0001_initial')"))
- from app.application.seed import seed
- async with transaction() as session:
- await seed(session)
|
