548 lines
21 KiB
Python
548 lines
21 KiB
Python
"""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
|