Spaces:
Sleeping
Sleeping
File size: 5,642 Bytes
b2300e2 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 | 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
|