367 lines
11 KiB
Python
367 lines
11 KiB
Python
|
"""
|
||
|
|
SQLite database for recordings, transcripts, and highlights.
|
||
|
|
"""
|
||
|
|
|
||
|
|
import sqlite3
|
||
|
|
from contextlib import contextmanager
|
||
|
|
from datetime import datetime
|
||
|
|
from pathlib import Path
|
||
|
|
from typing import Optional
|
||
|
|
|
||
|
|
import config
|
||
|
|
|
||
|
|
# Schema version for migrations
|
||
|
|
SCHEMA_VERSION = 2
|
||
|
|
|
||
|
|
|
||
|
|
def get_connection() -> sqlite3.Connection:
|
||
|
|
"""Get a database connection with row factory."""
|
||
|
|
conn = sqlite3.connect(str(config.DATABASE_PATH))
|
||
|
|
conn.row_factory = sqlite3.Row
|
||
|
|
conn.execute("PRAGMA foreign_keys = ON")
|
||
|
|
return conn
|
||
|
|
|
||
|
|
|
||
|
|
@contextmanager
|
||
|
|
def get_db():
|
||
|
|
"""Context manager for database connections."""
|
||
|
|
conn = get_connection()
|
||
|
|
try:
|
||
|
|
yield conn
|
||
|
|
conn.commit()
|
||
|
|
except Exception:
|
||
|
|
conn.rollback()
|
||
|
|
raise
|
||
|
|
finally:
|
||
|
|
conn.close()
|
||
|
|
|
||
|
|
|
||
|
|
def init_db():
|
||
|
|
"""Initialize database schema."""
|
||
|
|
with get_db() as conn:
|
||
|
|
conn.executescript("""
|
||
|
|
-- Recordings table
|
||
|
|
CREATE TABLE IF NOT EXISTS recordings (
|
||
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
|
|
source_name TEXT NOT NULL DEFAULT 'phone',
|
||
|
|
source_file TEXT UNIQUE NOT NULL,
|
||
|
|
audio_path TEXT,
|
||
|
|
filename TEXT NOT NULL,
|
||
|
|
duration REAL,
|
||
|
|
language TEXT,
|
||
|
|
language_probability REAL,
|
||
|
|
sample_rate INTEGER,
|
||
|
|
bit_depth INTEGER,
|
||
|
|
file_size_bytes INTEGER,
|
||
|
|
recorded_at TIMESTAMP,
|
||
|
|
imported_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
transcribed_at TIMESTAMP
|
||
|
|
);
|
||
|
|
|
||
|
|
-- Transcript segments
|
||
|
|
CREATE TABLE IF NOT EXISTS segments (
|
||
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
|
|
recording_id INTEGER NOT NULL,
|
||
|
|
start_time REAL NOT NULL,
|
||
|
|
end_time REAL NOT NULL,
|
||
|
|
text TEXT NOT NULL,
|
||
|
|
confidence REAL,
|
||
|
|
FOREIGN KEY (recording_id) REFERENCES recordings(id) ON DELETE CASCADE
|
||
|
|
);
|
||
|
|
|
||
|
|
-- User highlights/bookmarks
|
||
|
|
CREATE TABLE IF NOT EXISTS highlights (
|
||
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
|
|
recording_id INTEGER NOT NULL,
|
||
|
|
segment_id INTEGER,
|
||
|
|
start_seconds REAL NOT NULL,
|
||
|
|
end_seconds REAL,
|
||
|
|
tag TEXT NOT NULL DEFAULT 'important',
|
||
|
|
note TEXT,
|
||
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
FOREIGN KEY (recording_id) REFERENCES recordings(id) ON DELETE CASCADE,
|
||
|
|
FOREIGN KEY (segment_id) REFERENCES segments(id) ON DELETE SET NULL
|
||
|
|
);
|
||
|
|
|
||
|
|
-- Indexes
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_recordings_source ON recordings(source_name);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_recordings_recorded_at ON recordings(recorded_at);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_segments_recording ON segments(recording_id);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_highlights_recording ON highlights(recording_id);
|
||
|
|
|
||
|
|
-- Schema version tracking
|
||
|
|
CREATE TABLE IF NOT EXISTS schema_version (
|
||
|
|
version INTEGER PRIMARY KEY
|
||
|
|
);
|
||
|
|
""")
|
||
|
|
|
||
|
|
# Check/set schema version and run migrations
|
||
|
|
cur = conn.execute("SELECT version FROM schema_version LIMIT 1")
|
||
|
|
row = cur.fetchone()
|
||
|
|
if row is None:
|
||
|
|
conn.execute("INSERT INTO schema_version (version) VALUES (?)", (SCHEMA_VERSION,))
|
||
|
|
else:
|
||
|
|
current_version = row[0]
|
||
|
|
# Migration v1 -> v2: add end_seconds to highlights
|
||
|
|
if current_version < 2:
|
||
|
|
try:
|
||
|
|
conn.execute("ALTER TABLE highlights ADD COLUMN end_seconds REAL")
|
||
|
|
except sqlite3.OperationalError:
|
||
|
|
pass # Column already exists
|
||
|
|
conn.execute("UPDATE schema_version SET version = 2")
|
||
|
|
|
||
|
|
|
||
|
|
# Recording operations
|
||
|
|
|
||
|
|
def get_recording_by_source_file(source_file: str) -> Optional[dict]:
|
||
|
|
"""Get recording by source file path (for idempotent import)."""
|
||
|
|
with get_db() as conn:
|
||
|
|
cur = conn.execute(
|
||
|
|
"SELECT * FROM recordings WHERE source_file = ?",
|
||
|
|
(source_file,)
|
||
|
|
)
|
||
|
|
row = cur.fetchone()
|
||
|
|
return dict(row) if row else None
|
||
|
|
|
||
|
|
|
||
|
|
def get_recording_by_source_file(source_file: str) -> Optional[dict]:
|
||
|
|
"""Get a recording by its source file name."""
|
||
|
|
with get_db() as conn:
|
||
|
|
cur = conn.execute(
|
||
|
|
"SELECT * FROM recordings WHERE source_file = ?",
|
||
|
|
(source_file,)
|
||
|
|
)
|
||
|
|
row = cur.fetchone()
|
||
|
|
return dict(row) if row else None
|
||
|
|
|
||
|
|
|
||
|
|
def create_recording(
|
||
|
|
source_file: str,
|
||
|
|
filename: str,
|
||
|
|
source_name: str = "phone",
|
||
|
|
audio_path: Optional[str] = None,
|
||
|
|
duration: Optional[float] = None,
|
||
|
|
language: Optional[str] = None,
|
||
|
|
language_probability: Optional[float] = None,
|
||
|
|
recorded_at: Optional[datetime] = None,
|
||
|
|
transcribed_at: Optional[datetime] = None,
|
||
|
|
) -> int:
|
||
|
|
"""Create a new recording entry. Returns the recording ID."""
|
||
|
|
with get_db() as conn:
|
||
|
|
cur = conn.execute(
|
||
|
|
"""
|
||
|
|
INSERT INTO recordings
|
||
|
|
(source_file, filename, source_name, audio_path, duration, language,
|
||
|
|
language_probability, recorded_at, transcribed_at)
|
||
|
|
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
|
||
|
|
""",
|
||
|
|
(source_file, filename, source_name, audio_path, duration, language,
|
||
|
|
language_probability, recorded_at, transcribed_at)
|
||
|
|
)
|
||
|
|
return cur.lastrowid
|
||
|
|
|
||
|
|
|
||
|
|
def update_recording(recording_id: int, **kwargs) -> bool:
|
||
|
|
"""Update recording fields."""
|
||
|
|
if not kwargs:
|
||
|
|
return False
|
||
|
|
|
||
|
|
allowed_fields = {
|
||
|
|
'audio_path', 'duration', 'language', 'language_probability',
|
||
|
|
'sample_rate', 'bit_depth', 'file_size_bytes', 'transcribed_at'
|
||
|
|
}
|
||
|
|
fields = {k: v for k, v in kwargs.items() if k in allowed_fields}
|
||
|
|
|
||
|
|
if not fields:
|
||
|
|
return False
|
||
|
|
|
||
|
|
set_clause = ", ".join(f"{k} = ?" for k in fields.keys())
|
||
|
|
values = list(fields.values()) + [recording_id]
|
||
|
|
|
||
|
|
with get_db() as conn:
|
||
|
|
conn.execute(
|
||
|
|
f"UPDATE recordings SET {set_clause} WHERE id = ?",
|
||
|
|
values
|
||
|
|
)
|
||
|
|
return True
|
||
|
|
|
||
|
|
|
||
|
|
def get_recording(recording_id: int) -> Optional[dict]:
|
||
|
|
"""Get recording by ID with segments."""
|
||
|
|
with get_db() as conn:
|
||
|
|
cur = conn.execute("SELECT * FROM recordings WHERE id = ?", (recording_id,))
|
||
|
|
row = cur.fetchone()
|
||
|
|
if not row:
|
||
|
|
return None
|
||
|
|
|
||
|
|
recording = dict(row)
|
||
|
|
|
||
|
|
# Get segments
|
||
|
|
cur = conn.execute(
|
||
|
|
"SELECT * FROM segments WHERE recording_id = ? ORDER BY start_time",
|
||
|
|
(recording_id,)
|
||
|
|
)
|
||
|
|
recording['segments'] = [dict(r) for r in cur.fetchall()]
|
||
|
|
|
||
|
|
# Get highlights
|
||
|
|
cur = conn.execute(
|
||
|
|
"SELECT * FROM highlights WHERE recording_id = ? ORDER BY start_seconds",
|
||
|
|
(recording_id,)
|
||
|
|
)
|
||
|
|
recording['highlights'] = [dict(r) for r in cur.fetchall()]
|
||
|
|
|
||
|
|
return recording
|
||
|
|
|
||
|
|
|
||
|
|
def list_recordings(
|
||
|
|
source_name: Optional[str] = None,
|
||
|
|
date_from: Optional[str] = None,
|
||
|
|
date_to: Optional[str] = None,
|
||
|
|
limit: int = 100,
|
||
|
|
offset: int = 0,
|
||
|
|
) -> list[dict]:
|
||
|
|
"""List recordings with optional filters."""
|
||
|
|
query = "SELECT * FROM recordings WHERE 1=1"
|
||
|
|
params = []
|
||
|
|
|
||
|
|
if source_name:
|
||
|
|
query += " AND source_name = ?"
|
||
|
|
params.append(source_name)
|
||
|
|
|
||
|
|
if date_from:
|
||
|
|
query += " AND recorded_at >= ?"
|
||
|
|
params.append(date_from)
|
||
|
|
|
||
|
|
if date_to:
|
||
|
|
query += " AND recorded_at <= ?"
|
||
|
|
params.append(date_to)
|
||
|
|
|
||
|
|
query += " ORDER BY recorded_at DESC LIMIT ? OFFSET ?"
|
||
|
|
params.extend([limit, offset])
|
||
|
|
|
||
|
|
with get_db() as conn:
|
||
|
|
cur = conn.execute(query, params)
|
||
|
|
return [dict(r) for r in cur.fetchall()]
|
||
|
|
|
||
|
|
|
||
|
|
def get_recordings_count(source_name: Optional[str] = None) -> int:
|
||
|
|
"""Get total count of recordings."""
|
||
|
|
query = "SELECT COUNT(*) FROM recordings"
|
||
|
|
params = []
|
||
|
|
|
||
|
|
if source_name:
|
||
|
|
query += " WHERE source_name = ?"
|
||
|
|
params.append(source_name)
|
||
|
|
|
||
|
|
with get_db() as conn:
|
||
|
|
cur = conn.execute(query, params)
|
||
|
|
return cur.fetchone()[0]
|
||
|
|
|
||
|
|
|
||
|
|
# Segment operations
|
||
|
|
|
||
|
|
def create_segments(recording_id: int, segments: list[dict]) -> int:
|
||
|
|
"""Bulk create segments for a recording. Returns count created."""
|
||
|
|
if not segments:
|
||
|
|
return 0
|
||
|
|
|
||
|
|
with get_db() as conn:
|
||
|
|
conn.executemany(
|
||
|
|
"""
|
||
|
|
INSERT INTO segments (recording_id, start_time, end_time, text, confidence)
|
||
|
|
VALUES (?, ?, ?, ?, ?)
|
||
|
|
""",
|
||
|
|
[
|
||
|
|
(recording_id, s['start'], s['end'], s['text'], s.get('confidence'))
|
||
|
|
for s in segments
|
||
|
|
]
|
||
|
|
)
|
||
|
|
return len(segments)
|
||
|
|
|
||
|
|
|
||
|
|
def delete_segments(recording_id: int) -> int:
|
||
|
|
"""Delete all segments for a recording. Returns count deleted."""
|
||
|
|
with get_db() as conn:
|
||
|
|
cur = conn.execute(
|
||
|
|
"DELETE FROM segments WHERE recording_id = ?",
|
||
|
|
(recording_id,)
|
||
|
|
)
|
||
|
|
return cur.rowcount
|
||
|
|
|
||
|
|
|
||
|
|
# Highlight operations
|
||
|
|
|
||
|
|
def create_highlight(
|
||
|
|
recording_id: int,
|
||
|
|
start_seconds: float,
|
||
|
|
end_seconds: Optional[float] = None,
|
||
|
|
tag: str = "important",
|
||
|
|
note: Optional[str] = None,
|
||
|
|
segment_id: Optional[int] = None,
|
||
|
|
) -> int:
|
||
|
|
"""Create a highlight. Returns the highlight ID."""
|
||
|
|
with get_db() as conn:
|
||
|
|
cur = conn.execute(
|
||
|
|
"""
|
||
|
|
INSERT INTO highlights (recording_id, segment_id, start_seconds, end_seconds, tag, note)
|
||
|
|
VALUES (?, ?, ?, ?, ?, ?)
|
||
|
|
""",
|
||
|
|
(recording_id, segment_id, start_seconds, end_seconds, tag, note)
|
||
|
|
)
|
||
|
|
return cur.lastrowid
|
||
|
|
|
||
|
|
|
||
|
|
def delete_highlight(highlight_id: int) -> bool:
|
||
|
|
"""Delete a highlight."""
|
||
|
|
with get_db() as conn:
|
||
|
|
cur = conn.execute("DELETE FROM highlights WHERE id = ?", (highlight_id,))
|
||
|
|
return cur.rowcount > 0
|
||
|
|
|
||
|
|
|
||
|
|
def list_highlights(
|
||
|
|
recording_id: Optional[int] = None,
|
||
|
|
tag: Optional[str] = None,
|
||
|
|
limit: int = 100,
|
||
|
|
) -> list[dict]:
|
||
|
|
"""List highlights with optional filters."""
|
||
|
|
query = """
|
||
|
|
SELECT h.*, r.filename as recording_filename, r.recorded_at
|
||
|
|
FROM highlights h
|
||
|
|
JOIN recordings r ON h.recording_id = r.id
|
||
|
|
WHERE 1=1
|
||
|
|
"""
|
||
|
|
params = []
|
||
|
|
|
||
|
|
if recording_id:
|
||
|
|
query += " AND h.recording_id = ?"
|
||
|
|
params.append(recording_id)
|
||
|
|
|
||
|
|
if tag:
|
||
|
|
query += " AND h.tag = ?"
|
||
|
|
params.append(tag)
|
||
|
|
|
||
|
|
query += " ORDER BY h.created_at DESC LIMIT ?"
|
||
|
|
params.append(limit)
|
||
|
|
|
||
|
|
with get_db() as conn:
|
||
|
|
cur = conn.execute(query, params)
|
||
|
|
return [dict(r) for r in cur.fetchall()]
|
||
|
|
|
||
|
|
|
||
|
|
# Source/device operations
|
||
|
|
|
||
|
|
def list_sources() -> list[dict]:
|
||
|
|
"""Get summary of all source names."""
|
||
|
|
with get_db() as conn:
|
||
|
|
cur = conn.execute("""
|
||
|
|
SELECT
|
||
|
|
source_name,
|
||
|
|
COUNT(*) as recording_count,
|
||
|
|
SUM(duration) / 3600.0 as total_hours,
|
||
|
|
MAX(recorded_at) as last_recording
|
||
|
|
FROM recordings
|
||
|
|
GROUP BY source_name
|
||
|
|
ORDER BY last_recording DESC
|
||
|
|
""")
|
||
|
|
return [dict(r) for r in cur.fetchall()]
|