81 lines
2.5 KiB
Python
81 lines
2.5 KiB
Python
"""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
|
|
);
|
|
"""
|