馃 distributed transcription service
thistle.dunkirk.sh
dunkirk.sh
1import { Database } from "bun:sqlite";
2import { afterAll, beforeAll, describe, expect, test } from "bun:test";
3
4let testDb: Database;
5
6// Test helper functions that accept a db parameter
7function getAllTranscriptions_test(
8 db: Database,
9 limit = 50,
10 cursor?: string,
11): {
12 data: Array<{
13 id: string;
14 user_id: number;
15 user_email: string;
16 user_name: string | null;
17 original_filename: string;
18 status: string;
19 created_at: number;
20 error_message: string | null;
21 }>;
22 pagination: {
23 limit: number;
24 hasMore: boolean;
25 nextCursor: string | null;
26 };
27} {
28 type TranscriptionRow = {
29 id: string;
30 user_id: number;
31 user_email: string;
32 user_name: string | null;
33 original_filename: string;
34 status: string;
35 created_at: number;
36 error_message: string | null;
37 };
38
39 let transcriptions: TranscriptionRow[];
40
41 if (cursor) {
42 const { decodeCursor } = require("./cursor");
43 const parts = decodeCursor(cursor);
44
45 if (parts.length !== 2) {
46 throw new Error("Invalid cursor format");
47 }
48
49 const cursorTime = Number.parseInt(parts[0] || "", 10);
50 const id = parts[1] || "";
51
52 if (Number.isNaN(cursorTime) || !id) {
53 throw new Error("Invalid cursor format");
54 }
55
56 transcriptions = db
57 .query<TranscriptionRow, [number, number, string, number]>(
58 `SELECT
59 t.id,
60 t.user_id,
61 u.email as user_email,
62 u.name as user_name,
63 t.original_filename,
64 t.status,
65 t.created_at,
66 t.error_message
67 FROM transcriptions t
68 LEFT JOIN users u ON t.user_id = u.id
69 WHERE t.created_at < ? OR (t.created_at = ? AND t.id < ?)
70 ORDER BY t.created_at DESC, t.id DESC
71 LIMIT ?`,
72 )
73 .all(cursorTime, cursorTime, id, limit + 1);
74 } else {
75 transcriptions = db
76 .query<TranscriptionRow, [number]>(
77 `SELECT
78 t.id,
79 t.user_id,
80 u.email as user_email,
81 u.name as user_name,
82 t.original_filename,
83 t.status,
84 t.created_at,
85 t.error_message
86 FROM transcriptions t
87 LEFT JOIN users u ON t.user_id = u.id
88 ORDER BY t.created_at DESC, t.id DESC
89 LIMIT ?`,
90 )
91 .all(limit + 1);
92 }
93
94 const hasMore = transcriptions.length > limit;
95 if (hasMore) {
96 transcriptions.pop();
97 }
98
99 let nextCursor: string | null = null;
100 if (hasMore && transcriptions.length > 0) {
101 const { encodeCursor } = require("./cursor");
102 const last = transcriptions[transcriptions.length - 1];
103 if (last) {
104 nextCursor = encodeCursor([last.created_at.toString(), last.id]);
105 }
106 }
107
108 return {
109 data: transcriptions,
110 pagination: {
111 limit,
112 hasMore,
113 nextCursor,
114 },
115 };
116}
117
118beforeAll(() => {
119 testDb = new Database(":memory:");
120
121 // Create test tables
122 testDb.run(`
123 CREATE TABLE users (
124 id INTEGER PRIMARY KEY AUTOINCREMENT,
125 email TEXT UNIQUE NOT NULL,
126 password_hash TEXT,
127 name TEXT,
128 avatar TEXT DEFAULT 'd',
129 role TEXT NOT NULL DEFAULT 'user',
130 created_at INTEGER NOT NULL,
131 email_verified BOOLEAN DEFAULT 0,
132 last_login INTEGER
133 )
134 `);
135
136 testDb.run(`
137 CREATE TABLE transcriptions (
138 id TEXT PRIMARY KEY,
139 user_id INTEGER NOT NULL,
140 filename TEXT NOT NULL,
141 original_filename TEXT NOT NULL,
142 status TEXT NOT NULL,
143 created_at INTEGER NOT NULL,
144 error_message TEXT,
145 FOREIGN KEY (user_id) REFERENCES users(id)
146 )
147 `);
148
149 testDb.run(`
150 CREATE TABLE classes (
151 id TEXT PRIMARY KEY,
152 course_code TEXT NOT NULL,
153 name TEXT NOT NULL,
154 professor TEXT NOT NULL,
155 semester TEXT NOT NULL,
156 year INTEGER NOT NULL,
157 archived BOOLEAN DEFAULT 0
158 )
159 `);
160
161 testDb.run(`
162 CREATE TABLE class_members (
163 id INTEGER PRIMARY KEY AUTOINCREMENT,
164 user_id INTEGER NOT NULL,
165 class_id TEXT NOT NULL,
166 FOREIGN KEY (user_id) REFERENCES users(id),
167 FOREIGN KEY (class_id) REFERENCES classes(id)
168 )
169 `);
170
171 // Create test users
172 testDb.run(
173 "INSERT INTO users (email, password_hash, email_verified, created_at, role) VALUES (?, ?, 1, ?, ?)",
174 ["user1@test.com", "hash1", Math.floor(Date.now() / 1000) - 100, "user"],
175 );
176 testDb.run(
177 "INSERT INTO users (email, password_hash, email_verified, created_at, role) VALUES (?, ?, 1, ?, ?)",
178 ["user2@test.com", "hash2", Math.floor(Date.now() / 1000) - 50, "user"],
179 );
180 testDb.run(
181 "INSERT INTO users (email, password_hash, email_verified, created_at, role) VALUES (?, ?, 1, ?, ?)",
182 ["admin@test.com", "hash3", Math.floor(Date.now() / 1000), "admin"],
183 );
184
185 // Create test transcriptions
186 for (let i = 0; i < 5; i++) {
187 testDb.run(
188 "INSERT INTO transcriptions (id, user_id, filename, original_filename, status, created_at) VALUES (?, ?, ?, ?, ?, ?)",
189 [
190 `trans-${i}`,
191 1,
192 `file-${i}.mp3`,
193 `original-${i}.mp3`,
194 "completed",
195 Math.floor(Date.now() / 1000) - (100 - i * 10),
196 ],
197 );
198 }
199
200 // Create test classes
201 testDb.run(
202 "INSERT INTO classes (id, course_code, name, professor, semester, year) VALUES (?, ?, ?, ?, ?, ?)",
203 ["class-1", "CS101", "Intro to CS", "Dr. Smith", "Fall", 2024],
204 );
205 testDb.run(
206 "INSERT INTO classes (id, course_code, name, professor, semester, year) VALUES (?, ?, ?, ?, ?, ?)",
207 ["class-2", "CS102", "Data Structures", "Dr. Jones", "Spring", 2024],
208 );
209
210 // Add user to classes
211 testDb.run("INSERT INTO class_members (user_id, class_id) VALUES (?, ?)", [
212 1,
213 "class-1",
214 ]);
215 testDb.run("INSERT INTO class_members (user_id, class_id) VALUES (?, ?)", [
216 1,
217 "class-2",
218 ]);
219});
220
221afterAll(() => {
222 testDb.close();
223});
224
225describe("Transcription Pagination", () => {
226 test("returns first page without cursor", () => {
227 const result = getAllTranscriptions_test(testDb, 2);
228
229 expect(result.data.length).toBe(2);
230 expect(result.pagination.limit).toBe(2);
231 expect(result.pagination.hasMore).toBe(true);
232 expect(result.pagination.nextCursor).toBeTruthy();
233 });
234
235 test("returns second page with cursor", () => {
236 const page1 = getAllTranscriptions_test(testDb, 2);
237 const page2 = getAllTranscriptions_test(
238 testDb,
239 2,
240 page1.pagination.nextCursor || "",
241 );
242
243 expect(page2.data.length).toBe(2);
244 expect(page2.pagination.hasMore).toBe(true);
245 expect(page2.data[0]?.id).not.toBe(page1.data[0]?.id);
246 });
247
248 test("returns last page correctly", () => {
249 const result = getAllTranscriptions_test(testDb, 10);
250
251 expect(result.data.length).toBe(5);
252 expect(result.pagination.hasMore).toBe(false);
253 expect(result.pagination.nextCursor).toBeNull();
254 });
255
256 test("rejects invalid cursor format", () => {
257 expect(() => {
258 getAllTranscriptions_test(testDb, 10, "invalid-cursor");
259 }).toThrow("Invalid cursor format");
260 });
261
262 test("returns results ordered by created_at DESC", () => {
263 const result = getAllTranscriptions_test(testDb, 10);
264
265 for (let i = 0; i < result.data.length - 1; i++) {
266 const current = result.data[i];
267 const next = result.data[i + 1];
268 if (current && next) {
269 expect(current.created_at).toBeGreaterThanOrEqual(next.created_at);
270 }
271 }
272 });
273});
274
275describe("Cursor Format", () => {
276 test("transcription cursor format is base64url", () => {
277 const result = getAllTranscriptions_test(testDb, 1);
278 const cursor = result.pagination.nextCursor;
279
280 // Should be base64url-encoded (alphanumeric, no padding)
281 expect(cursor).toMatch(/^[A-Za-z0-9_-]+$/);
282 expect(cursor).not.toContain("="); // No padding
283 expect(cursor).not.toContain("+"); // URL-safe
284 expect(cursor).not.toContain("/"); // URL-safe
285 });
286});
287
288describe("Limit Boundaries", () => {
289 test("respects minimum limit of 1", () => {
290 const result = getAllTranscriptions_test(testDb, 1);
291 expect(result.data.length).toBeLessThanOrEqual(1);
292 });
293
294 test("handles empty results", () => {
295 // Query with a user that has no transcriptions
296 const emptyDb = new Database(":memory:");
297 emptyDb.run(`
298 CREATE TABLE users (
299 id INTEGER PRIMARY KEY AUTOINCREMENT,
300 email TEXT UNIQUE NOT NULL,
301 password_hash TEXT,
302 name TEXT,
303 created_at INTEGER NOT NULL
304 )
305 `);
306 emptyDb.run(`
307 CREATE TABLE transcriptions (
308 id TEXT PRIMARY KEY,
309 user_id INTEGER NOT NULL,
310 filename TEXT NOT NULL,
311 original_filename TEXT NOT NULL,
312 status TEXT NOT NULL,
313 created_at INTEGER NOT NULL,
314 error_message TEXT
315 )
316 `);
317
318 const result = getAllTranscriptions_test(emptyDb, 10);
319
320 expect(result.data.length).toBe(0);
321 expect(result.pagination.hasMore).toBe(false);
322 expect(result.pagination.nextCursor).toBeNull();
323
324 emptyDb.close();
325 });
326});