"""SQL-Schema fuer SjAudioSearch (SQLite + FTS5).""" from __future__ import annotations SCHEMA_VERSION = 1 TABLES_SQL = """ CREATE TABLE IF NOT EXISTS audio_files ( id INTEGER PRIMARY KEY AUTOINCREMENT, path TEXT NOT NULL UNIQUE, filename TEXT NOT NULL, duration REAL, file_size INTEGER NOT NULL, modified_at REAL NOT NULL, content_hash TEXT, status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'processing', 'done', 'error')), duplicate_of INTEGER REFERENCES audio_files(id) ON DELETE SET NULL, error_message TEXT, created_at TEXT NOT NULL, updated_at TEXT NOT NULL ); CREATE INDEX IF NOT EXISTS idx_audio_status ON audio_files(status); CREATE INDEX IF NOT EXISTS idx_audio_hash ON audio_files(content_hash); CREATE INDEX IF NOT EXISTS idx_audio_size ON audio_files(file_size); CREATE INDEX IF NOT EXISTS idx_audio_duplicate ON audio_files(duplicate_of); CREATE TABLE IF NOT EXISTS transcripts ( id INTEGER PRIMARY KEY AUTOINCREMENT, audio_id INTEGER NOT NULL UNIQUE REFERENCES audio_files(id) ON DELETE CASCADE, language TEXT, model TEXT NOT NULL, full_text TEXT NOT NULL, created_at TEXT NOT NULL ); CREATE INDEX IF NOT EXISTS idx_transcripts_language ON transcripts(language); CREATE TABLE IF NOT EXISTS segments ( id INTEGER PRIMARY KEY AUTOINCREMENT, transcript_id INTEGER NOT NULL REFERENCES transcripts(id) ON DELETE CASCADE, start_time REAL NOT NULL, end_time REAL NOT NULL, text TEXT NOT NULL, confidence REAL ); CREATE INDEX IF NOT EXISTS idx_segments_transcript ON segments(transcript_id, start_time); CREATE TABLE IF NOT EXISTS words ( id INTEGER PRIMARY KEY AUTOINCREMENT, segment_id INTEGER NOT NULL REFERENCES segments(id) ON DELETE CASCADE, word TEXT NOT NULL, start_time REAL, end_time REAL ); CREATE INDEX IF NOT EXISTS idx_words_segment ON words(segment_id); CREATE VIRTUAL TABLE IF NOT EXISTS search_index USING fts5( text, audio_id UNINDEXED, transcript_id UNINDEXED, segment_id UNINDEXED, tokenize = "unicode61 remove_diacritics 2" ); CREATE VIRTUAL TABLE IF NOT EXISTS search_trigram USING fts5( text, audio_id UNINDEXED, segment_id UNINDEXED, tokenize = "trigram" ); CREATE TABLE IF NOT EXISTS meta ( key TEXT PRIMARY KEY, value TEXT ); """