245 lines
8.3 KiB
Python
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] |