slices.network
Auto-indexing service and GraphQL API for AT Protocol Records
quickslice.slices.network/
atproto
gleam
graphql
chadtmiller.com
1/// Test helper utilities for database setup
2///
3/// Provides common database setup functions for tests
4import database/executor.{type DbError, type Executor}
5import database/sqlite/connection as db_connection
6import gleam/result
7
8/// Create an in-memory SQLite database for testing
9pub fn create_test_db() -> Result(Executor, DbError) {
10 db_connection.connect("sqlite::memory:")
11}
12
13/// Create the record table for tests
14pub fn create_record_table(exec: Executor) -> Result(Nil, DbError) {
15 executor.exec(
16 exec,
17 "CREATE TABLE IF NOT EXISTS record (
18 uri TEXT PRIMARY KEY NOT NULL,
19 cid TEXT NOT NULL,
20 did TEXT NOT NULL,
21 collection TEXT NOT NULL,
22 json TEXT NOT NULL,
23 indexed_at TEXT NOT NULL DEFAULT (datetime('now')),
24 rkey TEXT GENERATED ALWAYS AS (
25 substr(uri, instr(substr(uri, instr(substr(uri, 6), '/') + 6), '/') + instr(substr(uri, 6), '/') + 6)
26 ) STORED
27 )",
28 [],
29 )
30}
31
32/// Create the actor table for tests
33pub fn create_actor_table(exec: Executor) -> Result(Nil, DbError) {
34 executor.exec(
35 exec,
36 "CREATE TABLE IF NOT EXISTS actor (
37 did TEXT PRIMARY KEY NOT NULL,
38 handle TEXT,
39 indexed_at TEXT NOT NULL
40 )",
41 [],
42 )
43}
44
45/// Create the lexicon table for tests
46pub fn create_lexicon_table(exec: Executor) -> Result(Nil, DbError) {
47 executor.exec(
48 exec,
49 "CREATE TABLE IF NOT EXISTS lexicon (
50 id TEXT PRIMARY KEY NOT NULL,
51 json TEXT NOT NULL,
52 created_at TEXT NOT NULL DEFAULT (datetime('now'))
53 )",
54 [],
55 )
56}
57
58/// Create the config table for tests
59pub fn create_config_table(exec: Executor) -> Result(Nil, DbError) {
60 executor.exec(
61 exec,
62 "CREATE TABLE IF NOT EXISTS config (
63 key TEXT PRIMARY KEY NOT NULL,
64 value TEXT NOT NULL,
65 updated_at TEXT NOT NULL DEFAULT (datetime('now'))
66 )",
67 [],
68 )
69}
70
71/// Create jetstream activity table for tests
72pub fn create_jetstream_activity_table(exec: Executor) -> Result(Nil, DbError) {
73 executor.exec(
74 exec,
75 "CREATE TABLE IF NOT EXISTS jetstream_activity (
76 id INTEGER PRIMARY KEY AUTOINCREMENT,
77 timestamp TEXT NOT NULL,
78 operation TEXT NOT NULL,
79 collection TEXT NOT NULL,
80 did TEXT NOT NULL,
81 status TEXT NOT NULL,
82 error_message TEXT,
83 event_json TEXT NOT NULL
84 )",
85 [],
86 )
87}
88
89/// Create jetstream cursor table for tests
90pub fn create_jetstream_cursor_table(exec: Executor) -> Result(Nil, DbError) {
91 executor.exec(
92 exec,
93 "CREATE TABLE IF NOT EXISTS jetstream_cursor (
94 id INTEGER PRIMARY KEY CHECK (id = 1),
95 cursor INTEGER NOT NULL,
96 updated_at TEXT NOT NULL DEFAULT (datetime('now'))
97 )",
98 [],
99 )
100}
101
102/// Create all OAuth tables for tests
103pub fn create_oauth_tables(exec: Executor) -> Result(Nil, DbError) {
104 use _ <- result.try(
105 executor.exec(
106 exec,
107 "CREATE TABLE IF NOT EXISTS oauth_client (
108 client_id TEXT PRIMARY KEY,
109 client_secret TEXT,
110 client_name TEXT NOT NULL,
111 redirect_uris TEXT NOT NULL,
112 grant_types TEXT NOT NULL,
113 response_types TEXT NOT NULL,
114 scope TEXT,
115 token_endpoint_auth_method TEXT NOT NULL,
116 client_type TEXT NOT NULL,
117 created_at INTEGER NOT NULL,
118 updated_at INTEGER NOT NULL,
119 metadata TEXT NOT NULL DEFAULT '{}',
120 access_token_expiration INTEGER NOT NULL DEFAULT 86400,
121 refresh_token_expiration INTEGER NOT NULL DEFAULT 1209600,
122 require_redirect_exact INTEGER NOT NULL DEFAULT 1,
123 registration_access_token TEXT,
124 jwks TEXT
125 )",
126 [],
127 ),
128 )
129
130 use _ <- result.try(
131 executor.exec(
132 exec,
133 "CREATE TABLE IF NOT EXISTS oauth_access_token (
134 token TEXT PRIMARY KEY,
135 token_type TEXT NOT NULL DEFAULT 'Bearer',
136 client_id TEXT NOT NULL,
137 user_id TEXT,
138 session_id TEXT,
139 session_iteration INTEGER,
140 scope TEXT,
141 created_at INTEGER NOT NULL,
142 expires_at INTEGER NOT NULL,
143 revoked INTEGER NOT NULL DEFAULT 0,
144 dpop_jkt TEXT,
145 FOREIGN KEY (client_id) REFERENCES oauth_client(client_id) ON DELETE CASCADE
146 )",
147 [],
148 ),
149 )
150
151 use _ <- result.try(
152 executor.exec(
153 exec,
154 "CREATE TABLE IF NOT EXISTS oauth_refresh_token (
155 token TEXT PRIMARY KEY,
156 access_token TEXT NOT NULL,
157 client_id TEXT NOT NULL,
158 user_id TEXT NOT NULL,
159 session_id TEXT,
160 session_iteration INTEGER,
161 scope TEXT,
162 created_at INTEGER NOT NULL,
163 expires_at INTEGER,
164 revoked INTEGER NOT NULL DEFAULT 0,
165 FOREIGN KEY (client_id) REFERENCES oauth_client(client_id) ON DELETE CASCADE
166 )",
167 [],
168 ),
169 )
170
171 use _ <- result.try(
172 executor.exec(
173 exec,
174 "CREATE TABLE IF NOT EXISTS oauth_par_request (
175 request_uri TEXT PRIMARY KEY,
176 authorization_request TEXT NOT NULL,
177 client_id TEXT NOT NULL,
178 created_at INTEGER NOT NULL,
179 expires_at INTEGER NOT NULL,
180 subject TEXT,
181 metadata TEXT NOT NULL DEFAULT '{}',
182 FOREIGN KEY (client_id) REFERENCES oauth_client(client_id) ON DELETE CASCADE
183 )",
184 [],
185 ),
186 )
187
188 use _ <- result.try(
189 executor.exec(
190 exec,
191 "CREATE TABLE IF NOT EXISTS oauth_dpop_nonce (
192 nonce TEXT PRIMARY KEY,
193 expires_at INTEGER NOT NULL
194 )",
195 [],
196 ),
197 )
198
199 use _ <- result.try(
200 executor.exec(
201 exec,
202 "CREATE TABLE IF NOT EXISTS oauth_dpop_jti (
203 jti TEXT PRIMARY KEY,
204 created_at INTEGER NOT NULL
205 )",
206 [],
207 ),
208 )
209
210 use _ <- result.try(
211 executor.exec(
212 exec,
213 "CREATE TABLE IF NOT EXISTS oauth_auth_request (
214 session_id TEXT PRIMARY KEY,
215 client_id TEXT NOT NULL,
216 redirect_uri TEXT NOT NULL,
217 scope TEXT,
218 state TEXT,
219 code_challenge TEXT,
220 code_challenge_method TEXT,
221 response_type TEXT NOT NULL,
222 nonce TEXT,
223 login_hint TEXT,
224 created_at INTEGER NOT NULL,
225 expires_at INTEGER NOT NULL,
226 FOREIGN KEY (client_id) REFERENCES oauth_client(client_id) ON DELETE CASCADE
227 )",
228 [],
229 ),
230 )
231
232 use _ <- result.try(
233 executor.exec(
234 exec,
235 "CREATE TABLE IF NOT EXISTS oauth_atp_session (
236 session_id TEXT NOT NULL,
237 iteration INTEGER NOT NULL,
238 did TEXT,
239 session_created_at INTEGER NOT NULL,
240 atp_oauth_state TEXT NOT NULL,
241 signing_key_jkt TEXT NOT NULL,
242 dpop_key TEXT NOT NULL,
243 access_token TEXT,
244 refresh_token TEXT,
245 access_token_created_at INTEGER,
246 access_token_expires_at INTEGER,
247 access_token_scopes TEXT,
248 session_exchanged_at INTEGER,
249 exchange_error TEXT,
250 PRIMARY KEY (session_id, iteration)
251 )",
252 [],
253 ),
254 )
255
256 use _ <- result.try(
257 executor.exec(
258 exec,
259 "CREATE TABLE IF NOT EXISTS oauth_atp_request (
260 oauth_state TEXT PRIMARY KEY,
261 authorization_server TEXT NOT NULL,
262 nonce TEXT NOT NULL,
263 pkce_verifier TEXT NOT NULL,
264 signing_public_key TEXT NOT NULL,
265 dpop_private_key TEXT NOT NULL,
266 created_at INTEGER NOT NULL,
267 expires_at INTEGER NOT NULL
268 )",
269 [],
270 ),
271 )
272
273 executor.exec(
274 exec,
275 "CREATE TABLE IF NOT EXISTS oauth_authorization_code (
276 code TEXT PRIMARY KEY,
277 client_id TEXT NOT NULL,
278 user_id TEXT NOT NULL,
279 session_id TEXT,
280 session_iteration INTEGER,
281 redirect_uri TEXT NOT NULL,
282 scope TEXT,
283 code_challenge TEXT,
284 code_challenge_method TEXT,
285 nonce TEXT,
286 created_at INTEGER NOT NULL,
287 expires_at INTEGER NOT NULL,
288 used INTEGER NOT NULL DEFAULT 0
289 )",
290 [],
291 )
292}
293
294/// Create admin session table for tests
295pub fn create_admin_session_table(exec: Executor) -> Result(Nil, DbError) {
296 executor.exec(
297 exec,
298 "CREATE TABLE IF NOT EXISTS admin_session (
299 session_id TEXT PRIMARY KEY,
300 atp_session_id TEXT NOT NULL,
301 created_at INTEGER NOT NULL DEFAULT (unixepoch())
302 )",
303 [],
304 )
305}
306
307/// Create all core tables (record, actor, lexicon, config) for tests
308pub fn create_core_tables(exec: Executor) -> Result(Nil, DbError) {
309 use _ <- result.try(create_record_table(exec))
310 use _ <- result.try(create_actor_table(exec))
311 use _ <- result.try(create_lexicon_table(exec))
312 create_config_table(exec)
313}
314
315/// Create all tables needed for a full test environment
316pub fn create_all_tables(exec: Executor) -> Result(Nil, DbError) {
317 use _ <- result.try(create_core_tables(exec))
318 use _ <- result.try(create_jetstream_activity_table(exec))
319 use _ <- result.try(create_jetstream_cursor_table(exec))
320 use _ <- result.try(create_oauth_tables(exec))
321 create_admin_session_table(exec)
322}
323
324/// Create label_definition table for tests
325pub fn create_label_definition_table(exec: Executor) -> Result(Nil, DbError) {
326 use _ <- result.try(
327 executor.exec(
328 exec,
329 "CREATE TABLE IF NOT EXISTS label_definition (
330 val TEXT PRIMARY KEY NOT NULL,
331 description TEXT NOT NULL,
332 severity TEXT NOT NULL CHECK (severity IN ('inform', 'alert', 'takedown')),
333 default_visibility TEXT NOT NULL DEFAULT 'warn',
334 created_at TEXT NOT NULL DEFAULT (datetime('now'))
335 )",
336 [],
337 ),
338 )
339
340 // Seed default label definitions
341 executor.exec(
342 exec,
343 "INSERT INTO label_definition (val, description, severity, default_visibility) VALUES
344 ('!takedown', 'Content removed by moderators', 'takedown', 'hide'),
345 ('!suspend', 'Account suspended', 'takedown', 'hide'),
346 ('!warn', 'Show warning before displaying', 'alert', 'warn'),
347 ('!hide', 'Hide from feeds', 'alert', 'hide'),
348 ('porn', 'Pornographic content', 'alert', 'hide'),
349 ('spam', 'Spam or unwanted content', 'inform', 'warn'),
350 ('sexual', 'Sexually suggestive content', 'alert', 'warn'),
351 ('nudity', 'Non-sexual nudity', 'alert', 'warn')",
352 [],
353 )
354}
355
356/// Create actor_label_preference table for tests
357pub fn create_label_preference_table(exec: Executor) -> Result(Nil, DbError) {
358 executor.exec(
359 exec,
360 "CREATE TABLE IF NOT EXISTS actor_label_preference (
361 did TEXT NOT NULL,
362 label_val TEXT NOT NULL,
363 visibility TEXT NOT NULL,
364 created_at TEXT NOT NULL DEFAULT (datetime('now')),
365 PRIMARY KEY (did, label_val)
366 )",
367 [],
368 )
369}
370
371/// Create label table for tests
372pub fn create_label_table(exec: Executor) -> Result(Nil, DbError) {
373 executor.exec(
374 exec,
375 "CREATE TABLE IF NOT EXISTS label (
376 id INTEGER PRIMARY KEY AUTOINCREMENT,
377 src TEXT NOT NULL,
378 uri TEXT NOT NULL,
379 cid TEXT,
380 val TEXT NOT NULL,
381 neg INTEGER NOT NULL DEFAULT 0,
382 cts TEXT NOT NULL DEFAULT (datetime('now')),
383 exp TEXT,
384 FOREIGN KEY (val) REFERENCES label_definition(val)
385 )",
386 [],
387 )
388}
389
390/// Create report table for tests
391pub fn create_report_table(exec: Executor) -> Result(Nil, DbError) {
392 executor.exec(
393 exec,
394 "CREATE TABLE IF NOT EXISTS report (
395 id INTEGER PRIMARY KEY AUTOINCREMENT,
396 reporter_did TEXT NOT NULL,
397 subject_uri TEXT NOT NULL,
398 reason_type TEXT NOT NULL CHECK (reason_type IN ('spam', 'violation', 'misleading', 'sexual', 'rude', 'other')),
399 reason TEXT,
400 status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'resolved', 'dismissed')),
401 resolved_by TEXT,
402 resolved_at TEXT,
403 created_at TEXT NOT NULL DEFAULT (datetime('now'))
404 )",
405 [],
406 )
407}
408
409/// Create labels and reports tables for tests
410pub fn create_moderation_tables(exec: Executor) -> Result(Nil, DbError) {
411 use _ <- result.try(create_label_definition_table(exec))
412 use _ <- result.try(create_label_table(exec))
413 use _ <- result.try(create_report_table(exec))
414 create_label_preference_table(exec)
415}
416
417/// Insert a test token that maps to a DID for testing viewer authentication
418pub fn insert_test_token(
419 exec: Executor,
420 token: String,
421 did: String,
422) -> Result(Nil, DbError) {
423 // First, insert a test client if it doesn't exist (required for foreign key)
424 use _ <- result.try(
425 executor.exec(
426 exec,
427 "INSERT OR IGNORE INTO oauth_client (client_id, client_name, redirect_uris, grant_types, response_types, token_endpoint_auth_method, client_type, created_at, updated_at) VALUES ('test-client', 'Test Client', '[]', '[]', '[]', 'none', 'public', 0, 0)",
428 [],
429 ),
430 )
431
432 let far_future = 9_999_999_999
433 // Won't expire
434 executor.exec(
435 exec,
436 "INSERT INTO oauth_access_token (token, token_type, client_id, user_id, scope, created_at, expires_at, revoked) VALUES (?, 'Bearer', 'test-client', ?, 'atproto', 0, ?, 0)",
437 [executor.Text(token), executor.Text(did), executor.Int(far_future)],
438 )
439}