Files
2026-04-04 11:01:11 +09:00

245 lines
8.3 KiB
Python

from __future__ import annotations
from pathlib import Path
from typing import Optional
from uuid import uuid4
from sqlalchemy.orm.attributes import flag_modified
from sqlalchemy import text
from ..db import ProjectRecord, ProjectStemJobLink, SessionLocal, init_db
from . import jobs as jobs_repo
from .timestamps import serialize_utc
def _session():
init_db()
return SessionLocal()
def find_or_create(name: str, sep_job_id: str, tab_label: str = '') -> dict:
with _session() as session:
record = session.query(ProjectRecord).filter_by(sep_job_id=sep_job_id).first()
if record is None:
record = ProjectRecord(
id=str(uuid4()),
name=name,
sep_job_id=sep_job_id,
tab_label=tab_label,
)
session.add(record)
session.flush() # populate record.id before upsert
# Normalize sep_job_id into the link table
_upsert_stem_job_link(session, record.id, "sep", "__default__", sep_job_id)
session.commit()
return _to_dict_with_links(session, record)
def get(project_id: str) -> Optional[dict]:
with _session() as session:
record = session.get(ProjectRecord, project_id)
return _to_dict_with_links(session, record) if record else None
def get_by_sep_job(sep_job_id: str) -> Optional[dict]:
with _session() as session:
record = session.query(ProjectRecord).filter_by(sep_job_id=sep_job_id).first()
return _to_dict_with_links(session, record) if record else None
def update(project_id: str, **kwargs) -> bool:
with _session() as session:
record = session.get(ProjectRecord, project_id)
if record is None:
return False
for key, value in kwargs.items():
setattr(record, key, value)
# Keep normalized link table in sync for scalar job-id fields
if "mix_job_id" in kwargs and kwargs["mix_job_id"]:
_upsert_stem_job_link(session, project_id, "mix", "__default__", kwargs["mix_job_id"])
if "sep_job_id" in kwargs and kwargs["sep_job_id"]:
_upsert_stem_job_link(session, project_id, "sep", "__default__", kwargs["sep_job_id"])
session.commit()
return True
def delete(project_id: str) -> bool:
with _session() as session:
record = session.get(ProjectRecord, project_id)
if record is None:
return False
session.query(ProjectStemJobLink).filter_by(project_id=project_id).delete()
session.delete(record)
session.commit()
return True
def list_persisted() -> list[dict]:
"""Return only existing project records without auto-materializing new ones."""
with _session() as session:
records = session.query(ProjectRecord).order_by(ProjectRecord.created_at.desc()).all()
return [_to_dict_with_links(session, record) for record in records]
def list_all() -> list[dict]:
with _session() as session:
completed_sep_jobs = jobs_repo.list_completed_separation_candidates()
existing_sep_ids = {record.sep_job_id for record in session.query(ProjectRecord).all()}
for job in completed_sep_jobs:
if job.id not in existing_sep_ids:
session.add(
ProjectRecord(
id=str(uuid4()),
name=_extract_project_name(job),
sep_job_id=job.id,
created_at=job.created_at,
)
)
existing_sep_ids.add(job.id)
session.commit()
records = session.query(ProjectRecord).order_by(ProjectRecord.created_at.desc()).all()
return [_to_dict_with_links(session, record) for record in records]
def set_transcription_job(project_id: str, stem_name: str, job_id: str) -> bool:
return _set_json_job_map(project_id, "trans_jobs", stem_name, job_id)
def set_abc_job(project_id: str, stem_name: str, job_id: str) -> bool:
return _set_json_job_map(project_id, "abc_jobs", stem_name, job_id)
def set_lrc_job(project_id: str, stem_name: str, job_id: str) -> bool:
return _set_json_job_map(project_id, "lrc_jobs", stem_name, job_id)
def _set_json_job_map(project_id: str, field_name: str, stem_name: str, job_id: str) -> bool:
with _session() as session:
record = session.get(ProjectRecord, project_id)
if record is None:
return False
jobs = dict(getattr(record, field_name) or {})
jobs[stem_name] = job_id
setattr(record, field_name, jobs)
flag_modified(record, field_name)
stage = {
"trans_jobs": "transcribe",
"abc_jobs": "abc",
"lrc_jobs": "lrc",
}[field_name]
_upsert_stem_job_link(session, project_id, stage, stem_name, job_id)
session.commit()
return True
def _upsert_stem_job_link(session, project_id: str, stage: str, stem_name: str, job_id: str) -> None:
now_expr = "CURRENT_TIMESTAMP"
session.execute(
text(
"INSERT 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, " + now_expr + ", " + now_expr + ") "
"ON CONFLICT(project_id, stage, stem_name) DO UPDATE SET "
"job_id=excluded.job_id, updated_at=" + now_expr
),
{
"id": str(uuid4()),
"project_id": project_id,
"stage": stage,
"stem_name": stem_name,
"job_id": job_id,
},
)
def _load_stage_map(session, project_id: str, stage: str) -> dict:
links = (
session.query(ProjectStemJobLink)
.filter_by(project_id=project_id, stage=stage)
.all()
)
return {link.stem_name: link.job_id for link in links}
def _to_dict_with_links(session, record: ProjectRecord | None) -> Optional[dict]:
if record is None:
return None
trans_jobs = _load_stage_map(session, record.id, "transcribe")
abc_jobs = _load_stage_map(session, record.id, "abc")
lrc_jobs = _load_stage_map(session, record.id, "lrc")
# Fall back to legacy JSON columns when normalized rows are absent
if not trans_jobs:
trans_jobs = record.trans_jobs or {}
if not abc_jobs:
abc_jobs = record.abc_jobs or {}
if not lrc_jobs:
lrc_jobs = record.lrc_jobs or {}
# sep / mix — prefer normalized table, fall back to scalar columns
sep_link = _load_stage_map(session, record.id, "sep")
mix_link = _load_stage_map(session, record.id, "mix")
sep_job_id = sep_link.get("__default__") or record.sep_job_id
mix_job_id = mix_link.get("__default__") or record.mix_job_id
return {
"id": record.id,
"name": record.name,
"tab_label": record.tab_label or '',
"created_at": serialize_utc(record.created_at),
"sep_job_id": sep_job_id,
"trans_jobs": trans_jobs,
"abc_jobs": abc_jobs,
"lrc_jobs": lrc_jobs,
"mix_job_id": mix_job_id,
}
def to_dict(record: ProjectRecord | None) -> Optional[dict]:
if record is None:
return None
return {
"id": record.id,
"name": record.name,
"tab_label": record.tab_label or '',
"created_at": serialize_utc(record.created_at),
"sep_job_id": record.sep_job_id,
"trans_jobs": record.trans_jobs or {},
"abc_jobs": record.abc_jobs or {},
"lrc_jobs": record.lrc_jobs or {},
"mix_job_id": record.mix_job_id,
}
def _extract_project_name(job) -> str:
if job.result and isinstance(job.result, dict):
name = job.result.get("project_name") or ""
if name:
return name
project_path = job.result.get("project_path") or ""
if project_path:
return Path(project_path).name
for file_entry in (job.files or []):
file_path = file_entry.get("path", "")
if not file_path:
continue
path = Path(file_path)
parts = path.parts
for index, part in enumerate(parts):
if part == "Stems" and index > 0:
return parts[index - 1]
if job.files:
first_name = (job.files[0] or {}).get("name", "")
stem = Path(first_name).stem
parts = stem.rsplit(" - ", 1)
if len(parts) == 2:
return parts[0]
return stem or job.id[:8]
return job.id[:8]