"""Datenbankzugriff fuer SjAudioSearch. Alle Schreibvorgaenge laufen in expliziten Transaktionen, damit ein Abbruch keine halben Transkripte hinterlaesst. """ from __future__ import annotations import sqlite3 from collections.abc import Iterator, Sequence from pathlib import Path from typing import Any from ..core.models import AudioFileRecord, FileStatus, RepositoryStats, ScanCandidate from ..search.fuzzy import normalize_text from ..utils.formatting import utc_now_iso from ..utils.logging import get_logger from .connection import connect from .migrations import migrate logger = get_logger("database.repository") _AUDIO_COLUMNS = """ id, path, filename, duration, file_size, modified_at, content_hash, status, duplicate_of, error_message, created_at, updated_at """ def _row_to_audio(row: sqlite3.Row) -> AudioFileRecord: return AudioFileRecord( id=row["id"], path=row["path"], filename=row["filename"], duration=row["duration"], file_size=row["file_size"], modified_at=row["modified_at"], content_hash=row["content_hash"], status=row["status"], duplicate_of=row["duplicate_of"], error_message=row["error_message"], created_at=row["created_at"], updated_at=row["updated_at"], ) class Repository: """Kapselt saemtliche SQL-Zugriffe.""" def __init__(self, connection: sqlite3.Connection): self.conn = connection @classmethod def open(cls, db_path: str | Path) -> "Repository": conn = connect(db_path) migrate(conn) return cls(conn) def close(self) -> None: self.conn.close() def __enter__(self) -> "Repository": return self def __exit__(self, *exc_info: object) -> None: self.close() # ------------------------------------------------------------------ # audio_files # ------------------------------------------------------------------ def get_audio(self, audio_id: int) -> AudioFileRecord | None: row = self.conn.execute( f"SELECT {_AUDIO_COLUMNS} FROM audio_files WHERE id = ?", (audio_id,) ).fetchone() return _row_to_audio(row) if row else None def get_audio_by_path(self, path: str) -> AudioFileRecord | None: row = self.conn.execute( f"SELECT {_AUDIO_COLUMNS} FROM audio_files WHERE path = ?", (str(path),) ).fetchone() return _row_to_audio(row) if row else None def list_audio( self, *, status: str | None = None, limit: int | None = None, search: str | None = None, include_duplicates: bool = True, ) -> list[AudioFileRecord]: clauses: list[str] = [] params: list[Any] = [] if status: clauses.append("status = ?") params.append(status) if not include_duplicates: clauses.append("duplicate_of IS NULL") if search: clauses.append("(filename LIKE ? OR path LIKE ?)") like = f"%{search}%" params.extend([like, like]) where = f"WHERE {' AND '.join(clauses)}" if clauses else "" sql = f"SELECT {_AUDIO_COLUMNS} FROM audio_files {where} ORDER BY filename COLLATE NOCASE, id" if limit: sql += " LIMIT ?" params.append(int(limit)) rows = self.conn.execute(sql, params).fetchall() return [_row_to_audio(row) for row in rows] def insert_audio( self, candidate: ScanCandidate, *, status: str = FileStatus.PENDING.value, duplicate_of: int | None = None, error_message: str | None = None, ) -> int: now = utc_now_iso() with self.conn: cursor = self.conn.execute( """ INSERT INTO audio_files (path, filename, duration, file_size, modified_at, content_hash, status, duplicate_of, error_message, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) """, ( candidate.path, candidate.filename, candidate.duration, int(candidate.file_size), float(candidate.modified_at), candidate.content_hash, status, duplicate_of, error_message, now, now, ), ) return int(cursor.lastrowid) def update_scan_data( self, audio_id: int, candidate: ScanCandidate, *, status: str = FileStatus.PENDING.value, duplicate_of: int | None = None, error_message: str | None = None, ) -> None: with self.conn: self.conn.execute( """ UPDATE audio_files SET duration = ?, file_size = ?, modified_at = ?, content_hash = ?, status = ?, duplicate_of = ?, error_message = ?, updated_at = ? WHERE id = ? """, ( candidate.duration, int(candidate.file_size), float(candidate.modified_at), candidate.content_hash, status, duplicate_of, error_message, utc_now_iso(), audio_id, ), ) def set_duration(self, audio_id: int, duration: float | None) -> None: if duration is None: return with self.conn: self.conn.execute( "UPDATE audio_files SET duration = ?, updated_at = ? WHERE id = ? AND duration IS NULL", (float(duration), utc_now_iso(), audio_id), ) def set_status(self, audio_id: int, status: str, error_message: str | None = None) -> None: with self.conn: self.conn.execute( "UPDATE audio_files SET status = ?, error_message = ?, updated_at = ? WHERE id = ?", (status, error_message, utc_now_iso(), audio_id), ) def reset_stale_processing(self) -> int: """Setzt abgebrochene 'processing'-Eintraege nach einem Absturz zurueck.""" with self.conn: cursor = self.conn.execute( "UPDATE audio_files SET status = 'pending', updated_at = ? WHERE status = 'processing'", (utc_now_iso(),), ) return cursor.rowcount or 0 def reset_errors(self) -> int: """Setzt fehlgeschlagene Transkriptionen zurueck auf 'pending'.""" with self.conn: cursor = self.conn.execute( """ UPDATE audio_files SET status = 'pending', error_message = NULL, updated_at = ? WHERE status = 'error' """, (utc_now_iso(),), ) return cursor.rowcount or 0 def transcription_queue(self, *, force: bool = False) -> list[AudioFileRecord]: if force: sql = f""" SELECT {_AUDIO_COLUMNS} FROM audio_files WHERE duplicate_of IS NULL ORDER BY id """ rows = self.conn.execute(sql).fetchall() else: sql = f""" SELECT {_AUDIO_COLUMNS} FROM audio_files WHERE duplicate_of IS NULL AND status IN ('pending', 'processing') ORDER BY id """ rows = self.conn.execute(sql).fetchall() return [_row_to_audio(row) for row in rows] def find_by_hash(self, content_hash: str, *, exclude_id: int | None = None) -> AudioFileRecord | None: sql = f"SELECT {_AUDIO_COLUMNS} FROM audio_files WHERE content_hash = ? AND duplicate_of IS NULL" params: list[Any] = [content_hash] if exclude_id is not None: sql += " AND id != ?" params.append(exclude_id) row = self.conn.execute(sql + " ORDER BY id LIMIT 1", params).fetchone() return _row_to_audio(row) if row else None def paths_with_size(self, size: int) -> list[AudioFileRecord]: rows = self.conn.execute( f"SELECT {_AUDIO_COLUMNS} FROM audio_files WHERE file_size = ? AND content_hash IS NOT NULL", (int(size),), ).fetchall() return [_row_to_audio(row) for row in rows] def count_by_status(self) -> dict[str, int]: rows = self.conn.execute( "SELECT status, COUNT(*) AS amount FROM audio_files GROUP BY status" ).fetchall() return {row["status"]: int(row["amount"]) for row in rows} def stats(self, db_size_bytes: int = 0) -> RepositoryStats: counts = self.count_by_status() total_row = self.conn.execute( "SELECT COUNT(*) AS total, COALESCE(SUM(duration), 0) AS duration FROM audio_files" ).fetchone() dup_row = self.conn.execute( "SELECT COUNT(*) AS amount FROM audio_files WHERE duplicate_of IS NOT NULL" ).fetchone() segment_row = self.conn.execute("SELECT COUNT(*) AS amount FROM segments").fetchone() word_row = self.conn.execute("SELECT COUNT(*) AS amount FROM words").fetchone() transcript_row = self.conn.execute("SELECT COUNT(*) AS amount FROM transcripts").fetchone() model_rows = self.conn.execute("SELECT DISTINCT model FROM transcripts").fetchall() return RepositoryStats( total_files=int(total_row["total"]), done=counts.get("done", 0), pending=counts.get("pending", 0), processing=counts.get("processing", 0), error=counts.get("error", 0), duplicates=int(dup_row["amount"]), total_duration=float(total_row["duration"] or 0.0), database_size=int(db_size_bytes), segment_count=int(segment_row["amount"]), word_count=int(word_row["amount"]), transcripts=int(transcript_row["amount"]), models=[row["model"] for row in model_rows], ) def databases_size(self) -> int: row = self.conn.execute("PRAGMA database_list").fetchone() if not row: return 0 path = Path(row["file"]) size = path.stat().st_size if path.exists() else 0 wal = path.with_name(path.name + "-wal") if wal.exists(): size += wal.stat().st_size return size # ------------------------------------------------------------------ # Transkripte # ------------------------------------------------------------------ def save_transcript( self, audio_id: int, *, language: str | None, model: str, full_text: str, segments: Sequence[Any], duration: float | None = None, ) -> int: """Speichert ein Transkript samt Segmenten, Woertern und Suchindex. Vorhandene Daten der Datei werden ersetzt. Alles geschieht in einer Transaktion, damit ein Abbruch keinen Teilzustand hinterlaesst. """ now = utc_now_iso() with self.conn: self.conn.execute("DELETE FROM transcripts WHERE audio_id = ?", (audio_id,)) self.conn.execute("DELETE FROM search_index WHERE audio_id = ?", (audio_id,)) self.conn.execute("DELETE FROM search_trigram WHERE audio_id = ?", (audio_id,)) cursor = self.conn.execute( """ INSERT INTO transcripts (audio_id, language, model, full_text, created_at) VALUES (?, ?, ?, ?, ?) """, (audio_id, language, model, full_text, now), ) transcript_id = int(cursor.lastrowid) for segment in segments: cursor = self.conn.execute( """ INSERT INTO segments (transcript_id, start_time, end_time, text, confidence) VALUES (?, ?, ?, ?, ?) """, ( transcript_id, float(segment.start), float(segment.end), segment.text, getattr(segment, "confidence", None), ), ) segment_id = int(cursor.lastrowid) words = getattr(segment, "words", None) or [] if words: self.conn.executemany( "INSERT INTO words (segment_id, word, start_time, end_time) VALUES (?, ?, ?, ?)", [ ( segment_id, word.word, getattr(word, "start", None), getattr(word, "end", None), ) for word in words ], ) normalized = normalize_text(segment.text) if normalized: self.conn.execute( """ INSERT INTO search_index (text, audio_id, transcript_id, segment_id) VALUES (?, ?, ?, ?) """, (normalized, audio_id, transcript_id, segment_id), ) self.conn.execute( "INSERT INTO search_trigram (text, audio_id, segment_id) VALUES (?, ?, ?)", (normalized, audio_id, segment_id), ) if duration is not None: self.conn.execute( """ UPDATE audio_files SET duration = COALESCE(duration, ?), status = 'done', error_message = NULL, updated_at = ? WHERE id = ? """, (float(duration), now, audio_id), ) else: self.conn.execute( """ UPDATE audio_files SET status = 'done', error_message = NULL, updated_at = ? WHERE id = ? """, (now, audio_id), ) return transcript_id def get_transcript(self, audio_id: int) -> sqlite3.Row | None: return self.conn.execute( "SELECT * FROM transcripts WHERE audio_id = ?", (audio_id,) ).fetchone() def get_segments(self, audio_id: int) -> list[sqlite3.Row]: return self.conn.execute( """ SELECT s.id, s.start_time, s.end_time, s.text, s.confidence FROM segments s JOIN transcripts t ON t.id = s.transcript_id WHERE t.audio_id = ? ORDER BY s.start_time, s.id """, (audio_id,), ).fetchall() def get_words(self, segment_ids: Sequence[int]) -> dict[int, list[sqlite3.Row]]: result: dict[int, list[sqlite3.Row]] = {} ids = list(segment_ids) for start in range(0, len(ids), 500): chunk = ids[start : start + 500] placeholders = ",".join("?" for _ in chunk) rows = self.conn.execute( f""" SELECT id, segment_id, word, start_time, end_time FROM words WHERE segment_id IN ({placeholders}) ORDER BY segment_id, start_time, id """, chunk, ).fetchall() for row in rows: result.setdefault(int(row["segment_id"]), []).append(row) return result def segment_texts(self, audio_id: int) -> list[str]: return [row["text"] for row in self.get_segments(audio_id)] def iter_audio_ids_with_transcripts(self, language: str | None = None) -> Iterator[int]: sql = "SELECT DISTINCT audio_id FROM transcripts" params: list[Any] = [] if language: sql += " WHERE language = ?" params.append(language) sql += " ORDER BY audio_id" cursor = self.conn.execute(sql, params) for row in cursor: yield int(row["audio_id"]) def transcript_languages(self) -> list[str]: rows = self.conn.execute( "SELECT DISTINCT language FROM transcripts WHERE language IS NOT NULL ORDER BY language" ).fetchall() return [row["language"] for row in rows] # ------------------------------------------------------------------ # Suche # ------------------------------------------------------------------ def fts_candidates(self, match_query: str, limit: int) -> list[tuple[int, int]]: """Liefert (audio_id, segment_id) fuer einen FTS5-Ausdruck.""" if not match_query: return [] try: rows = self.conn.execute( """ SELECT audio_id, segment_id, bm25(search_index) AS rank FROM search_index WHERE search_index MATCH ? ORDER BY rank LIMIT ? """, (match_query, int(limit)), ).fetchall() except sqlite3.OperationalError as exc: logger.warning("FTS-Anfrage fehlgeschlagen (%s): %s", match_query, exc) return [] return [(int(row["audio_id"]), int(row["segment_id"])) for row in rows] def trigram_candidates(self, terms: Sequence[str], limit: int) -> list[tuple[int, int]]: """Teilstring-Kandidaten ueber den FTS5-Trigram-Index.""" results: list[tuple[int, int]] = [] seen: set[tuple[int, int]] = set() for term in terms: escaped = '"' + term.replace('"', '""') + '"' try: rows = self.conn.execute( """ SELECT audio_id, segment_id FROM search_trigram WHERE search_trigram MATCH ? LIMIT ? """, (escaped, int(limit)), ).fetchall() except sqlite3.OperationalError as exc: logger.warning("Trigramm-Anfrage fehlgeschlagen (%s): %s", term, exc) continue for row in rows: key = (int(row["audio_id"]), int(row["segment_id"])) if key not in seen: seen.add(key) results.append(key) if len(results) >= limit: return results return results def timeline(self, audio_id: int) -> tuple[list[sqlite3.Row], dict[int, list[sqlite3.Row]]]: segments = self.get_segments(audio_id) words = self.get_words([int(segment["id"]) for segment in segments]) return segments, words def rebuild_search_index(self) -> int: """Baut den FTS-Index aus den gespeicherten Segmenten neu auf.""" with self.conn: self.conn.execute("DELETE FROM search_index") self.conn.execute("DELETE FROM search_trigram") rows = self.conn.execute( """ SELECT s.id AS segment_id, t.audio_id AS audio_id, t.id AS transcript_id, s.text FROM segments s JOIN transcripts t ON t.id = s.transcript_id ORDER BY s.id """ ).fetchall() index_rows = [] trigram_rows = [] for row in rows: normalized = normalize_text(row["text"]) if not normalized: continue index_rows.append( (normalized, int(row["audio_id"]), int(row["transcript_id"]), int(row["segment_id"])) ) trigram_rows.append((normalized, int(row["audio_id"]), int(row["segment_id"]))) self.conn.executemany( "INSERT INTO search_index (text, audio_id, transcript_id, segment_id) VALUES (?, ?, ?, ?)", index_rows, ) self.conn.executemany( "INSERT INTO search_trigram (text, audio_id, segment_id) VALUES (?, ?, ?)", trigram_rows, ) return len(index_rows) def delete_audio(self, audio_id: int) -> bool: """Entfernt eine Datei aus dem Index (die Audiodatei bleibt unberuehrt).""" with self.conn: self.conn.execute("DELETE FROM search_index WHERE audio_id = ?", (audio_id,)) self.conn.execute("DELETE FROM search_trigram WHERE audio_id = ?", (audio_id,)) cursor = self.conn.execute("DELETE FROM audio_files WHERE id = ?", (audio_id,)) return (cursor.rowcount or 0) > 0 def upsert_meta(self, key: str, value: str) -> None: with self.conn: self.conn.execute( "INSERT INTO meta (key, value) VALUES (?, ?) " "ON CONFLICT(key) DO UPDATE SET value = excluded.value", (key, value), ) def get_meta(self, key: str) -> str | None: row = self.conn.execute("SELECT value FROM meta WHERE key = ?", (key,)).fetchone() return row["value"] if row else None