馃 distributed transcription service
thistle.dunkirk.sh
dunkirk.sh
1import { describe, expect, test, beforeAll, afterAll } from "bun:test";
2import { Database } from "bun:sqlite";
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 [
175 "user1@test.com",
176 "hash1",
177 Math.floor(Date.now() / 1000) - 100,
178 "user",
179 ],
180 );
181 testDb.run(
182 "INSERT INTO users (email, password_hash, email_verified, created_at, role) VALUES (?, ?, 1, ?, ?)",
183 [
184 "user2@test.com",
185 "hash2",
186 Math.floor(Date.now() / 1000) - 50,
187 "user",
188 ],
189 );
190 testDb.run(
191 "INSERT INTO users (email, password_hash, email_verified, created_at, role) VALUES (?, ?, 1, ?, ?)",
192 ["admin@test.com", "hash3", Math.floor(Date.now() / 1000), "admin"],
193 );
194
195 // Create test transcriptions
196 for (let i = 0; i < 5; i++) {
197 testDb.run(
198 "INSERT INTO transcriptions (id, user_id, filename, original_filename, status, created_at) VALUES (?, ?, ?, ?, ?, ?)",
199 [
200 `trans-${i}`,
201 1,
202 `file-${i}.mp3`,
203 `original-${i}.mp3`,
204 "completed",
205 Math.floor(Date.now() / 1000) - (100 - i * 10),
206 ],
207 );
208 }
209
210 // Create test classes
211 testDb.run(
212 "INSERT INTO classes (id, course_code, name, professor, semester, year) VALUES (?, ?, ?, ?, ?, ?)",
213 ["class-1", "CS101", "Intro to CS", "Dr. Smith", "Fall", 2024],
214 );
215 testDb.run(
216 "INSERT INTO classes (id, course_code, name, professor, semester, year) VALUES (?, ?, ?, ?, ?, ?)",
217 ["class-2", "CS102", "Data Structures", "Dr. Jones", "Spring", 2024],
218 );
219
220 // Add user to classes
221 testDb.run("INSERT INTO class_members (user_id, class_id) VALUES (?, ?)", [
222 1,
223 "class-1",
224 ]);
225 testDb.run("INSERT INTO class_members (user_id, class_id) VALUES (?, ?)", [
226 1,
227 "class-2",
228 ]);
229});
230
231afterAll(() => {
232 testDb.close();
233});
234
235describe("Transcription Pagination", () => {
236 test("returns first page without cursor", () => {
237 const result = getAllTranscriptions_test(testDb, 2);
238
239 expect(result.data.length).toBe(2);
240 expect(result.pagination.limit).toBe(2);
241 expect(result.pagination.hasMore).toBe(true);
242 expect(result.pagination.nextCursor).toBeTruthy();
243 });
244
245 test("returns second page with cursor", () => {
246 const page1 = getAllTranscriptions_test(testDb, 2);
247 const page2 = getAllTranscriptions_test(
248 testDb,
249 2,
250 page1.pagination.nextCursor || "",
251 );
252
253 expect(page2.data.length).toBe(2);
254 expect(page2.pagination.hasMore).toBe(true);
255 expect(page2.data[0]?.id).not.toBe(page1.data[0]?.id);
256 });
257
258 test("returns last page correctly", () => {
259 const result = getAllTranscriptions_test(testDb, 10);
260
261 expect(result.data.length).toBe(5);
262 expect(result.pagination.hasMore).toBe(false);
263 expect(result.pagination.nextCursor).toBeNull();
264 });
265
266 test("rejects invalid cursor format", () => {
267 expect(() => {
268 getAllTranscriptions_test(testDb, 10, "invalid-cursor");
269 }).toThrow("Invalid cursor format");
270 });
271
272 test("returns results ordered by created_at DESC", () => {
273 const result = getAllTranscriptions_test(testDb, 10);
274
275 for (let i = 0; i < result.data.length - 1; i++) {
276 const current = result.data[i];
277 const next = result.data[i + 1];
278 if (current && next) {
279 expect(current.created_at).toBeGreaterThanOrEqual(next.created_at);
280 }
281 }
282 });
283});
284
285describe("Cursor Format", () => {
286 test("transcription cursor format is base64url", () => {
287 const result = getAllTranscriptions_test(testDb, 1);
288 const cursor = result.pagination.nextCursor;
289
290 // Should be base64url-encoded (alphanumeric, no padding)
291 expect(cursor).toMatch(/^[A-Za-z0-9_-]+$/);
292 expect(cursor).not.toContain("="); // No padding
293 expect(cursor).not.toContain("+"); // URL-safe
294 expect(cursor).not.toContain("/"); // URL-safe
295 });
296});
297
298describe("Limit Boundaries", () => {
299 test("respects minimum limit of 1", () => {
300 const result = getAllTranscriptions_test(testDb, 1);
301 expect(result.data.length).toBeLessThanOrEqual(1);
302 });
303
304 test("handles empty results", () => {
305 // Query with a user that has no transcriptions
306 const emptyDb = new Database(":memory:");
307 emptyDb.run(`
308 CREATE TABLE users (
309 id INTEGER PRIMARY KEY AUTOINCREMENT,
310 email TEXT UNIQUE NOT NULL,
311 password_hash TEXT,
312 name TEXT,
313 created_at INTEGER NOT NULL
314 )
315 `);
316 emptyDb.run(`
317 CREATE TABLE transcriptions (
318 id TEXT PRIMARY KEY,
319 user_id INTEGER NOT NULL,
320 filename TEXT NOT NULL,
321 original_filename TEXT NOT NULL,
322 status TEXT NOT NULL,
323 created_at INTEGER NOT NULL,
324 error_message TEXT
325 )
326 `);
327
328 const result = getAllTranscriptions_test(emptyDb, 10);
329
330 expect(result.data.length).toBe(0);
331 expect(result.pagination.hasMore).toBe(false);
332 expect(result.pagination.nextCursor).toBeNull();
333
334 emptyDb.close();
335 });
336});