Files
2026-04-14 16:32:36 +09:00

238 lines
9.0 KiB
Python

"""
Database bootstrap for the backend.
Owns SQLAlchemy engine/session/model definitions plus explicit schema
initialization. Runtime code should call `init_db()` during startup instead of
relying on import-time table creation side effects.
"""
from __future__ import annotations
import threading
from datetime import datetime, timezone
from uuid import uuid4
from sqlalchemy import JSON, Column, DateTime, String, Text, create_engine, event, text
from sqlalchemy.orm import DeclarativeBase, sessionmaker
from .config import DB_PATH
engine = create_engine(
f"sqlite:///{DB_PATH}",
connect_args={"check_same_thread": False, "timeout": 30},
)
@event.listens_for(engine, "connect")
def _set_sqlite_pragmas(dbapi_connection, _connection_record):
"""Enable WAL mode and set busy timeout so concurrent writes don't deadlock."""
cursor = dbapi_connection.cursor()
cursor.execute("PRAGMA journal_mode=WAL")
cursor.execute("PRAGMA synchronous=NORMAL")
cursor.execute("PRAGMA busy_timeout=30000")
cursor.close()
class Base(DeclarativeBase):
pass
class JobRecord(Base):
__tablename__ = "jobs"
id = Column(String, primary_key=True)
job_type = Column(String, nullable=False)
status = Column(String, default="queued")
progress = Column(String, default="")
error = Column(Text, default="")
files = Column(JSON, default=list)
result = Column(JSON, default=dict)
created_at = Column(DateTime, default=lambda: datetime.now(timezone.utc))
updated_at = Column(DateTime, default=lambda: datetime.now(timezone.utc), onupdate=lambda: datetime.now(timezone.utc))
class ProjectRecord(Base):
__tablename__ = "projects"
id = Column(String, primary_key=True, default=lambda: str(uuid4()))
name = Column(String, nullable=False)
tab_label = Column(String, default='')
created_at = Column(DateTime, default=lambda: datetime.now(timezone.utc))
sep_job_id = Column(String, nullable=False)
trans_jobs = Column(JSON, default=dict)
abc_jobs = Column(JSON, default=dict)
lrc_jobs = Column(JSON, default=dict)
mix_job_id = Column(String, nullable=True)
class ProjectStemJobLink(Base):
"""Normalized mapping of project + stem + stage -> job_id.
This reduces reliance on JSON blobs (`trans_jobs`, `abc_jobs`, `lrc_jobs`) for
operational queries while preserving backward compatibility.
"""
__tablename__ = "project_stem_job_links"
id = Column(String, primary_key=True, default=lambda: str(uuid4()))
project_id = Column(String, nullable=False, index=True)
stage = Column(String, nullable=False, index=True) # transcribe | abc | lrc
stem_name = Column(String, nullable=False)
job_id = Column(String, nullable=False, index=True)
created_at = Column(DateTime, default=lambda: datetime.now(timezone.utc))
updated_at = Column(DateTime, default=lambda: datetime.now(timezone.utc), onupdate=lambda: datetime.now(timezone.utc))
SessionLocal = sessionmaker(bind=engine, expire_on_commit=False)
_init_lock = threading.Lock()
_initialized = False
def init_db(logger=None) -> None:
"""Create tables and run lightweight legacy migrations once per process."""
global _initialized
if _initialized:
return
with _init_lock:
if _initialized:
return
Base.metadata.create_all(engine)
migrations = (
"ALTER TABLE jobs ADD COLUMN result JSON",
"ALTER TABLE projects ADD COLUMN trans_jobs JSON",
"ALTER TABLE projects ADD COLUMN abc_jobs JSON",
"ALTER TABLE projects ADD COLUMN lrc_jobs JSON",
"ALTER TABLE projects ADD COLUMN tab_label TEXT DEFAULT ''",
"CREATE TABLE IF NOT EXISTS project_stem_job_links ("
"id TEXT PRIMARY KEY, "
"project_id TEXT NOT NULL, "
"stage TEXT NOT NULL, "
"stem_name TEXT NOT NULL, "
"job_id TEXT NOT NULL, "
"created_at DATETIME, "
"updated_at DATETIME"
")",
"CREATE INDEX IF NOT EXISTS ix_project_stem_job_links_project_id ON project_stem_job_links (project_id)",
"CREATE INDEX IF NOT EXISTS ix_project_stem_job_links_stage ON project_stem_job_links (stage)",
"CREATE INDEX IF NOT EXISTS ix_project_stem_job_links_job_id ON project_stem_job_links (job_id)",
"CREATE UNIQUE INDEX IF NOT EXISTS uq_project_stage_stem ON project_stem_job_links (project_id, stage, stem_name)",
)
for migration_sql in migrations:
try:
with engine.connect() as conn:
conn.execute(text(migration_sql))
conn.commit()
if logger:
logger.info("Applied DB migration: %s", migration_sql)
except Exception as exc:
if logger:
logger.debug("Skipped DB migration %s: %s", migration_sql, exc)
_backfill_project_stem_job_links(logger)
_initialized = True
def _backfill_project_stem_job_links(logger=None) -> None:
"""Backfill normalized link rows from legacy JSON columns and scalar fields.
Covers:
- trans_jobs / abc_jobs / lrc_jobs JSON maps → stages transcribe / abc / lrc
- sep_job_id scalar string → stage sep (stem_name "__default__")
- mix_job_id scalar string → stage mix (stem_name "__default__")
Safe to run repeatedly due to unique constraint and insert-or-ignore behavior.
"""
stage_map = (
("transcribe", "trans_jobs"),
("abc", "abc_jobs"),
("lrc", "lrc_jobs"),
)
inserted = 0
with SessionLocal() as session:
projects = session.query(ProjectRecord).all()
for project in projects:
for stage, field_name in stage_map:
job_map = getattr(project, field_name) or {}
if not isinstance(job_map, dict):
continue
for stem_name, job_id in job_map.items():
if not stem_name or not job_id:
continue
stmt = text(
"INSERT OR IGNORE INTO project_stem_job_links "
"(id, project_id, stage, stem_name, job_id, created_at, updated_at) "
"VALUES (:id, :project_id, :stage, :stem_name, :job_id, :created_at, :updated_at)"
)
now = datetime.now(timezone.utc)
result = session.execute(
stmt,
{
"id": str(uuid4()),
"project_id": project.id,
"stage": stage,
"stem_name": str(stem_name),
"job_id": str(job_id),
"created_at": now,
"updated_at": now,
},
)
if result.rowcount:
inserted += 1
# Backfill sep_job_id → stage "sep", stem_name "__default__"
if project.sep_job_id:
stmt = text(
"INSERT OR IGNORE INTO project_stem_job_links "
"(id, project_id, stage, stem_name, job_id, created_at, updated_at) "
"VALUES (:id, :project_id, :stage, :stem_name, :job_id, :created_at, :updated_at)"
)
now = datetime.now(timezone.utc)
result = session.execute(
stmt,
{
"id": str(uuid4()),
"project_id": project.id,
"stage": "sep",
"stem_name": "__default__",
"job_id": str(project.sep_job_id),
"created_at": now,
"updated_at": now,
},
)
if result.rowcount:
inserted += 1
# Backfill mix_job_id → stage "mix", stem_name "__default__"
if project.mix_job_id:
stmt = text(
"INSERT OR IGNORE INTO project_stem_job_links "
"(id, project_id, stage, stem_name, job_id, created_at, updated_at) "
"VALUES (:id, :project_id, :stage, :stem_name, :job_id, :created_at, :updated_at)"
)
now = datetime.now(timezone.utc)
result = session.execute(
stmt,
{
"id": str(uuid4()),
"project_id": project.id,
"stage": "mix",
"stem_name": "__default__",
"job_id": str(project.mix_job_id),
"created_at": now,
"updated_at": now,
},
)
if result.rowcount:
inserted += 1
session.commit()
if logger and inserted:
logger.info("Backfilled %d project stem-job link rows", inserted)