168 lines
5.8 KiB
Python
168 lines
5.8 KiB
Python
"""Tests fuer Datenbank, Repository, Migrationen und Suchindex."""
|
|
|
|
from __future__ import annotations
|
|
|
|
from pathlib import Path
|
|
|
|
from sjaudiosearch.database.migrations import current_version, migrate
|
|
from sjaudiosearch.database.repository import Repository
|
|
|
|
|
|
def test_schema_and_migration_version(repo):
|
|
assert current_version(repo.conn) == 1
|
|
# Migration ist idempotent.
|
|
assert migrate(repo.conn) == 1
|
|
tables = {
|
|
row["name"]
|
|
for row in repo.conn.execute(
|
|
"SELECT name FROM sqlite_master WHERE type IN ('table', 'view')"
|
|
)
|
|
}
|
|
for table in (
|
|
"audio_files",
|
|
"transcripts",
|
|
"segments",
|
|
"words",
|
|
"search_index",
|
|
"search_trigram",
|
|
):
|
|
assert table in tables
|
|
|
|
|
|
def test_transcript_stored_with_segments_and_words(repo, add_audio, store_transcript):
|
|
record = add_audio("/tmp/folge1.mp3", duration=125.0)
|
|
store_transcript(
|
|
record.id,
|
|
[("Der Professor sah sich um.", 0.0, 4.0), ("Der Schlüssel liegt im alten Haus.", 4.0, 9.5)],
|
|
with_words=True,
|
|
duration=9.5,
|
|
)
|
|
|
|
stored = repo.get_transcript(record.id)
|
|
assert stored is not None
|
|
assert stored["model"] == "test-model"
|
|
assert stored["language"] == "de"
|
|
assert "Schlüssel" in stored["full_text"]
|
|
|
|
segments = repo.get_segments(record.id)
|
|
assert len(segments) == 2
|
|
assert segments[1]["start_time"] == 4.0
|
|
|
|
words = repo.get_words([int(segment["id"]) for segment in segments])
|
|
first_segment_words = words[int(segments[0]["id"])]
|
|
assert [row["word"] for row in first_segment_words][:2] == ["Der", "Professor"]
|
|
|
|
updated = repo.get_audio(record.id)
|
|
assert updated.status == "done"
|
|
assert updated.duration == 125.0
|
|
|
|
|
|
def test_status_management_and_retry(repo, add_audio):
|
|
ok = add_audio("/tmp/a.mp3")
|
|
failing = add_audio("/tmp/b.mp3")
|
|
|
|
repo.set_status(ok.id, "processing")
|
|
repo.set_status(failing.id, "error", "Decoder kaputt")
|
|
|
|
counts = repo.count_by_status()
|
|
assert counts["processing"] == 1
|
|
assert counts["error"] == 1
|
|
|
|
# Abgestuerzte 'processing'-Eintraege werden zurueckgesetzt
|
|
assert repo.reset_stale_processing() == 1
|
|
assert repo.get_audio(ok.id).status == "pending"
|
|
|
|
assert repo.reset_errors() == 1
|
|
reset = repo.get_audio(failing.id)
|
|
assert reset.status == "pending"
|
|
assert reset.error_message is None
|
|
|
|
|
|
def test_queue_skips_duplicates_and_done(repo, add_audio):
|
|
done = add_audio("/tmp/done.mp3", status="done")
|
|
pending = add_audio("/tmp/pending.mp3")
|
|
duplicate = add_audio("/tmp/dup.mp3", duplicate_of=done.id)
|
|
|
|
queue = repo.transcription_queue()
|
|
assert [record.id for record in queue] == [pending.id]
|
|
|
|
forced = repo.transcription_queue(force=True)
|
|
assert {record.id for record in forced} == {done.id, pending.id}
|
|
assert duplicate.id not in {record.id for record in forced}
|
|
|
|
|
|
def test_delete_audio_keeps_file_and_removes_index(repo, add_audio, store_transcript, tmp_path):
|
|
audio_file = tmp_path / "folge.mp3"
|
|
audio_file.write_bytes(b"audio")
|
|
record = add_audio(audio_file)
|
|
store_transcript(record.id, ["Der Schlüssel liegt im alten Haus."])
|
|
|
|
assert repo.fts_candidates('"schlüssel"', 10)
|
|
|
|
assert repo.delete_audio(record.id) is True
|
|
assert audio_file.exists()
|
|
assert repo.get_audio(record.id) is None
|
|
assert repo.get_transcript(record.id) is None
|
|
assert repo.fts_candidates('"schlüssel"', 10) == []
|
|
|
|
|
|
def test_foreign_key_cascade(repo, add_audio, store_transcript):
|
|
record = add_audio("/tmp/a.mp3")
|
|
store_transcript(record.id, ["Erster Satz.", "Zweiter Satz."])
|
|
assert repo.get_segments(record.id)
|
|
|
|
with repo.conn:
|
|
repo.conn.execute("DELETE FROM audio_files WHERE id = ?", (record.id,))
|
|
|
|
segments = repo.conn.execute("SELECT COUNT(*) AS amount FROM segments").fetchone()
|
|
words = repo.conn.execute("SELECT COUNT(*) AS amount FROM words").fetchone()
|
|
transcripts = repo.conn.execute("SELECT COUNT(*) AS amount FROM transcripts").fetchone()
|
|
assert segments["amount"] == 0
|
|
assert words["amount"] == 0
|
|
assert transcripts["amount"] == 0
|
|
|
|
|
|
def test_reindex_preserves_transcripts(repo, add_audio, store_transcript):
|
|
record = add_audio("/tmp/a.mp3")
|
|
store_transcript(record.id, ["Der Schlüssel liegt im alten Haus.", "Niemand bemerkte es."])
|
|
|
|
before_transcript = dict(repo.get_transcript(record.id))
|
|
before_segments = [dict(row) for row in repo.get_segments(record.id)]
|
|
assert repo.fts_candidates('"schlüssel"', 10)
|
|
|
|
indexed = repo.rebuild_search_index()
|
|
assert indexed == 2
|
|
|
|
after_transcript = dict(repo.get_transcript(record.id))
|
|
after_segments = [dict(row) for row in repo.get_segments(record.id)]
|
|
assert before_transcript == after_transcript
|
|
assert before_segments == after_segments
|
|
assert repo.fts_candidates('"schlüssel"', 10)
|
|
|
|
|
|
def test_saving_new_transcript_replaces_old(repo, add_audio, store_transcript):
|
|
record = add_audio("/tmp/a.mp3")
|
|
store_transcript(record.id, ["Alter Text mit Schlüssel."])
|
|
store_transcript(record.id, ["Neuer Text ohne alles."])
|
|
|
|
transcript = repo.get_transcript(record.id)
|
|
assert transcript["full_text"] == "Neuer Text ohne alles."
|
|
assert len(repo.get_segments(record.id)) == 1
|
|
assert repo.fts_candidates('"schlüssel"', 10) == []
|
|
|
|
|
|
def test_database_size_and_stats(repo, add_audio, store_transcript):
|
|
first = add_audio("/tmp/a.mp3", duration=100.0)
|
|
second = add_audio("/tmp/b.mp3", duration=50.0, status="error")
|
|
store_transcript(first.id, ["Ein Satz."], duration=100.0)
|
|
|
|
stats = repo.stats(repo.databases_size())
|
|
assert stats.total_files == 2
|
|
assert stats.done == 1
|
|
assert stats.error == 1
|
|
assert stats.total_duration == 150.0
|
|
assert stats.transcripts == 1
|
|
assert stats.segment_count == 1
|
|
assert stats.database_size > 0
|
|
assert "test-model" in stats.models
|