IRIS / iris /storage /database.py
CompleteKris's picture
Bundle IRIS core for Space build
b2300e2
Raw History Blame Contribute Delete
5.64 kB
from __future__ import annotations
import sqlite3
from pathlib import Path
from typing import Any
from iris.models.schemas import VideoStatus
from iris.pipeline.chunk import TranscriptChunk
class Database:
def __init__(self, path: Path) -> None:
self.path = path
def init(self) -> None:
self.path.parent.mkdir(parents=True, exist_ok=True)
with self._connect() as connection:
connection.executescript(
"""
PRAGMA journal_mode=WAL;
CREATE TABLE IF NOT EXISTS videos (
id TEXT PRIMARY KEY,
original_filename TEXT NOT NULL,
video_path TEXT NOT NULL,
audio_path TEXT,
transcript_path TEXT,
embeddings_path TEXT,
status TEXT NOT NULL,
error TEXT,
created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS transcript_chunks (
id INTEGER PRIMARY KEY AUTOINCREMENT,
video_id TEXT NOT NULL,
start REAL NOT NULL,
end REAL NOT NULL,
text TEXT NOT NULL,
FOREIGN KEY(video_id) REFERENCES videos(id) ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS searches (
id INTEGER PRIMARY KEY AUTOINCREMENT,
video_id TEXT NOT NULL,
query TEXT NOT NULL,
include_visual INTEGER NOT NULL,
created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY(video_id) REFERENCES videos(id) ON DELETE CASCADE
);
"""
)
def create_video(self, video_id: str, original_filename: str, video_path: Path) -> dict[str, Any]:
with self._connect() as connection:
connection.execute(
"""
INSERT INTO videos (id, original_filename, video_path, status)
VALUES (?, ?, ?, 'queued')
""",
(video_id, original_filename, str(video_path)),
)
return self.get_video(video_id) or {}
def get_video(self, video_id: str) -> dict[str, Any] | None:
with self._connect() as connection:
row = connection.execute("SELECT * FROM videos WHERE id = ?", (video_id,)).fetchone()
return dict(row) if row else None
def update_video_status(self, video_id: str, status: VideoStatus, error: str | None = None) -> None:
with self._connect() as connection:
connection.execute(
"""
UPDATE videos
SET status = ?, error = ?, updated_at = CURRENT_TIMESTAMP
WHERE id = ?
""",
(status, error, video_id),
)
def set_artifact_paths(
self,
video_id: str,
audio_path: Path | None = None,
transcript_path: Path | None = None,
embeddings_path: Path | None = None,
) -> None:
with self._connect() as connection:
connection.execute(
"""
UPDATE videos
SET audio_path = COALESCE(?, audio_path),
transcript_path = COALESCE(?, transcript_path),
embeddings_path = COALESCE(?, embeddings_path),
updated_at = CURRENT_TIMESTAMP
WHERE id = ?
""",
(
str(audio_path) if audio_path else None,
str(transcript_path) if transcript_path else None,
str(embeddings_path) if embeddings_path else None,
video_id,
),
)
def replace_transcript_chunks(self, video_id: str, chunks: list[TranscriptChunk]) -> None:
with self._connect() as connection:
connection.execute("DELETE FROM transcript_chunks WHERE video_id = ?", (video_id,))
connection.executemany(
"""
INSERT INTO transcript_chunks (video_id, start, end, text)
VALUES (?, ?, ?, ?)
""",
[(video_id, chunk.start, chunk.end, chunk.text) for chunk in chunks],
)
def list_transcript_chunks(self, video_id: str) -> list[TranscriptChunk]:
with self._connect() as connection:
rows = connection.execute(
"""
SELECT start, end, text
FROM transcript_chunks
WHERE video_id = ?
ORDER BY start, end, id
""",
(video_id,),
).fetchall()
return [
TranscriptChunk(start=float(row["start"]), end=float(row["end"]), text=str(row["text"]))
for row in rows
]
def record_search(self, video_id: str, query: str, include_visual: bool) -> None:
with self._connect() as connection:
connection.execute(
"""
INSERT INTO searches (video_id, query, include_visual)
VALUES (?, ?, ?)
""",
(video_id, query, int(include_visual)),
)
def _connect(self) -> sqlite3.Connection:
connection = sqlite3.connect(self.path)
connection.row_factory = sqlite3.Row
connection.execute("PRAGMA foreign_keys=ON;")
return connection