馃 distributed transcription service
thistle.dunkirk.sh
dunkirk.sh
1import { Database } from "bun:sqlite";
2import { expect, test, afterEach } from "bun:test";
3import { unlinkSync } from "node:fs";
4
5const TEST_DB = "test-schema.db";
6
7afterEach(() => {
8 try {
9 unlinkSync(TEST_DB);
10 } catch {
11 // File may not exist
12 }
13});
14
15test("schema creates all required tables", () => {
16 const db = new Database(TEST_DB);
17
18 // Create schema_migrations table
19 db.run(`
20 CREATE TABLE IF NOT EXISTS schema_migrations (
21 version INTEGER PRIMARY KEY,
22 applied_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now'))
23 )
24 `);
25
26 // Apply migration (simplified version of migration 1)
27 const migration = `
28 CREATE TABLE IF NOT EXISTS users (
29 id INTEGER PRIMARY KEY AUTOINCREMENT,
30 email TEXT UNIQUE NOT NULL,
31 password_hash TEXT,
32 name TEXT,
33 avatar TEXT DEFAULT 'd',
34 role TEXT NOT NULL DEFAULT 'user',
35 last_login INTEGER,
36 created_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now'))
37 );
38
39 CREATE TABLE IF NOT EXISTS classes (
40 id TEXT PRIMARY KEY,
41 course_code TEXT NOT NULL,
42 name TEXT NOT NULL,
43 professor TEXT NOT NULL,
44 semester TEXT NOT NULL,
45 year INTEGER NOT NULL,
46 archived BOOLEAN DEFAULT 0,
47 created_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now'))
48 );
49
50 CREATE TABLE IF NOT EXISTS class_members (
51 class_id TEXT NOT NULL,
52 user_id INTEGER NOT NULL,
53 enrolled_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
54 PRIMARY KEY (class_id, user_id),
55 FOREIGN KEY (class_id) REFERENCES classes(id) ON DELETE CASCADE,
56 FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
57 );
58
59 CREATE TABLE IF NOT EXISTS meeting_times (
60 id TEXT PRIMARY KEY,
61 class_id TEXT NOT NULL,
62 label TEXT NOT NULL,
63 created_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
64 FOREIGN KEY (class_id) REFERENCES classes(id) ON DELETE CASCADE
65 );
66
67 CREATE TABLE IF NOT EXISTS transcriptions (
68 id TEXT PRIMARY KEY,
69 user_id INTEGER NOT NULL,
70 class_id TEXT,
71 meeting_time_id TEXT,
72 filename TEXT NOT NULL,
73 original_filename TEXT NOT NULL,
74 status TEXT NOT NULL DEFAULT 'pending',
75 progress INTEGER NOT NULL DEFAULT 0,
76 error_message TEXT,
77 whisper_job_id TEXT,
78 created_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
79 updated_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
80 FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
81 FOREIGN KEY (class_id) REFERENCES classes(id) ON DELETE CASCADE,
82 FOREIGN KEY (meeting_time_id) REFERENCES meeting_times(id) ON DELETE SET NULL
83 );
84 `;
85
86 db.run(migration);
87
88 // Verify tables exist
89 const tables = db
90 .query<{ name: string }, []>(
91 "SELECT name FROM sqlite_master WHERE type='table' ORDER BY name",
92 )
93 .all();
94
95 const tableNames = tables.map((t) => t.name);
96
97 expect(tableNames).toContain("users");
98 expect(tableNames).toContain("classes");
99 expect(tableNames).toContain("class_members");
100 expect(tableNames).toContain("meeting_times");
101 expect(tableNames).toContain("transcriptions");
102
103 db.close();
104});
105
106test("class foreign key constraints work", () => {
107 const db = new Database(TEST_DB);
108
109 // Create tables
110 db.run(`
111 CREATE TABLE users (
112 id INTEGER PRIMARY KEY AUTOINCREMENT,
113 email TEXT UNIQUE NOT NULL,
114 password_hash TEXT,
115 role TEXT NOT NULL DEFAULT 'user',
116 created_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now'))
117 );
118
119 CREATE TABLE classes (
120 id TEXT PRIMARY KEY,
121 course_code TEXT NOT NULL,
122 name TEXT NOT NULL,
123 professor TEXT NOT NULL,
124 semester TEXT NOT NULL,
125 year INTEGER NOT NULL,
126 archived BOOLEAN DEFAULT 0,
127 created_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now'))
128 );
129
130 CREATE TABLE class_members (
131 class_id TEXT NOT NULL,
132 user_id INTEGER NOT NULL,
133 enrolled_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
134 PRIMARY KEY (class_id, user_id),
135 FOREIGN KEY (class_id) REFERENCES classes(id) ON DELETE CASCADE,
136 FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
137 );
138 `);
139
140 db.run("PRAGMA foreign_keys = ON");
141
142 // Create test data
143 db.run("INSERT INTO users (email, password_hash) VALUES (?, ?)", [
144 "test@example.com",
145 "hash",
146 ]);
147 const userId = db
148 .query<{ id: number }, []>("SELECT last_insert_rowid() as id")
149 .get()?.id;
150
151 db.run(
152 "INSERT INTO classes (id, course_code, name, professor, semester, year) VALUES (?, ?, ?, ?, ?, ?)",
153 ["class-1", "CS 101", "Intro to CS", "Dr. Smith", "Fall", 2024],
154 );
155
156 // Enroll user in class
157 db.run("INSERT INTO class_members (class_id, user_id) VALUES (?, ?)", [
158 "class-1",
159 userId,
160 ]);
161
162 const enrollment = db
163 .query<
164 { class_id: string; user_id: number },
165 []
166 >("SELECT class_id, user_id FROM class_members")
167 .get();
168
169 expect(enrollment?.class_id).toBe("class-1");
170 expect(enrollment?.user_id).toBe(userId);
171
172 // Delete class should cascade delete enrollment
173 db.run("DELETE FROM classes WHERE id = ?", ["class-1"]);
174
175 const enrollments = db
176 .query<{ class_id: string }, []>("SELECT class_id FROM class_members")
177 .all();
178 expect(enrollments.length).toBe(0);
179
180 db.close();
181});
182
183test("transcription status defaults to pending", () => {
184 const db = new Database(TEST_DB);
185
186 db.run(`
187 CREATE TABLE users (
188 id INTEGER PRIMARY KEY AUTOINCREMENT,
189 email TEXT UNIQUE NOT NULL,
190 password_hash TEXT
191 );
192
193 CREATE TABLE transcriptions (
194 id TEXT PRIMARY KEY,
195 user_id INTEGER NOT NULL,
196 class_id TEXT,
197 meeting_time_id TEXT,
198 filename TEXT NOT NULL,
199 original_filename TEXT NOT NULL,
200 status TEXT NOT NULL DEFAULT 'pending',
201 progress INTEGER NOT NULL DEFAULT 0,
202 created_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
203 updated_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
204 FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
205 );
206 `);
207
208 db.run("INSERT INTO users (email, password_hash) VALUES (?, ?)", [
209 "test@example.com",
210 "hash",
211 ]);
212 const userId = db
213 .query<{ id: number }, []>("SELECT last_insert_rowid() as id")
214 .get()?.id;
215
216 db.run(
217 "INSERT INTO transcriptions (id, user_id, filename, original_filename) VALUES (?, ?, ?, ?)",
218 ["trans-1", userId, "file.mp3", "original.mp3"],
219 );
220
221 const transcription = db
222 .query<
223 { status: string; progress: number },
224 []
225 >("SELECT status, progress FROM transcriptions WHERE id = 'trans-1'")
226 .get();
227
228 expect(transcription?.status).toBe("pending");
229 expect(transcription?.progress).toBe(0);
230
231 db.close();
232});