"""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