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