馃 distributed transcription service
thistle.dunkirk.sh
dunkirk.sh
1import { Database } from "bun:sqlite";
2import { afterEach, expect, test } 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<{ class_id: string; user_id: number }, []>(
164 "SELECT class_id, user_id FROM class_members",
165 )
166 .get();
167
168 expect(enrollment?.class_id).toBe("class-1");
169 expect(enrollment?.user_id).toBe(userId);
170
171 // Delete class should cascade delete enrollment
172 db.run("DELETE FROM classes WHERE id = ?", ["class-1"]);
173
174 const enrollments = db
175 .query<{ class_id: string }, []>("SELECT class_id FROM class_members")
176 .all();
177 expect(enrollments.length).toBe(0);
178
179 db.close();
180});
181
182test("transcription status defaults to pending", () => {
183 const db = new Database(TEST_DB);
184
185 db.run(`
186 CREATE TABLE users (
187 id INTEGER PRIMARY KEY AUTOINCREMENT,
188 email TEXT UNIQUE NOT NULL,
189 password_hash TEXT
190 );
191
192 CREATE TABLE transcriptions (
193 id TEXT PRIMARY KEY,
194 user_id INTEGER NOT NULL,
195 class_id TEXT,
196 meeting_time_id TEXT,
197 filename TEXT NOT NULL,
198 original_filename TEXT NOT NULL,
199 status TEXT NOT NULL DEFAULT 'pending',
200 progress INTEGER NOT NULL DEFAULT 0,
201 created_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
202 updated_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
203 FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
204 );
205 `);
206
207 db.run("INSERT INTO users (email, password_hash) VALUES (?, ?)", [
208 "test@example.com",
209 "hash",
210 ]);
211 const userId = db
212 .query<{ id: number }, []>("SELECT last_insert_rowid() as id")
213 .get()?.id;
214
215 db.run(
216 "INSERT INTO transcriptions (id, user_id, filename, original_filename) VALUES (?, ?, ?, ?)",
217 ["trans-1", userId, "file.mp3", "original.mp3"],
218 );
219
220 const transcription = db
221 .query<{ status: string; progress: number }, []>(
222 "SELECT status, progress FROM transcriptions WHERE id = 'trans-1'",
223 )
224 .get();
225
226 expect(transcription?.status).toBe("pending");
227 expect(transcription?.progress).toBe(0);
228
229 db.close();
230});