slices.network
Auto-indexing service and GraphQL API for AT Protocol Records
quickslice.slices.network/
atproto
gleam
graphql
chadtmiller.com
1/// Database sorting integration tests
2///
3/// Tests that SQL ORDER BY clauses are generated correctly and
4/// that sorting works properly with the database
5import database/executor.{type Executor}
6import database/repositories/records
7import database/types
8import gleam/list
9import gleam/option.{None, Some}
10import gleeunit/should
11import test_helpers
12
13// Helper to create test database with records
14fn create_test_db_with_records() -> Executor {
15 // Create in-memory database
16 let assert Ok(exec) = test_helpers.create_test_db()
17
18 // Create schema using the database module
19 let assert Ok(_) = test_helpers.create_record_table(exec)
20
21 // Insert test records with different dates
22 let records = [
23 #(
24 "at://did:plc:1/xyz.statusphere.status/1",
25 "cid1",
26 "did:plc:1",
27 "xyz.statusphere.status",
28 "{\"status\":\"😊\",\"createdAt\":\"2025-01-15T10:00:00Z\"}",
29 "2025-01-15T10:00:00Z",
30 ),
31 #(
32 "at://did:plc:2/xyz.statusphere.status/2",
33 "cid2",
34 "did:plc:2",
35 "xyz.statusphere.status",
36 "{\"status\":\"🎉\",\"createdAt\":\"2025-01-20T10:00:00Z\"}",
37 "2025-01-20T10:00:00Z",
38 ),
39 #(
40 "at://did:plc:3/xyz.statusphere.status/3",
41 "cid3",
42 "did:plc:3",
43 "xyz.statusphere.status",
44 "{\"status\":\"🤔\",\"createdAt\":\"2025-01-10T10:00:00Z\"}",
45 "2025-01-10T10:00:00Z",
46 ),
47 // Record with NULL createdAt
48 #(
49 "at://did:plc:4/xyz.statusphere.status/4",
50 "cid4",
51 "did:plc:4",
52 "xyz.statusphere.status",
53 "{\"status\":\"😴\",\"createdAt\":null}",
54 "2025-01-25T10:00:00Z",
55 ),
56 // Record with invalid createdAt
57 #(
58 "at://did:plc:5/xyz.statusphere.status/5",
59 "cid5",
60 "did:plc:5",
61 "xyz.statusphere.status",
62 "{\"status\":\"🤷\",\"createdAt\":\"wowzers\"}",
63 "2025-01-18T10:00:00Z",
64 ),
65 ]
66
67 list.each(records, fn(record) {
68 let #(uri, cid, did, collection, json, indexed_at) = record
69 let insert_sql =
70 "INSERT INTO record (uri, cid, did, collection, json, indexed_at)
71 VALUES ('" <> uri <> "', '" <> cid <> "', '" <> did <> "', '" <> collection <> "', '" <> json <> "', '" <> indexed_at <> "')"
72
73 let assert Ok(_) = executor.exec(exec, insert_sql, [])
74 Nil
75 })
76
77 exec
78}
79
80// Test: Sort by indexedAt DESC (default)
81pub fn test_sort_by_indexed_at_desc() {
82 let exec = create_test_db_with_records()
83
84 let result =
85 records.get_by_collection_paginated(
86 exec,
87 "xyz.statusphere.status",
88 Some(10),
89 None,
90 None,
91 None,
92 Some([#("indexed_at", "desc")]),
93 )
94
95 case result {
96 Ok(#(records, _, _, _)) -> {
97 // First record should be most recent (2025-01-25)
98 case list.first(records) {
99 Ok(first) -> should.equal(first.indexed_at, "2025-01-25T10:00:00Z")
100 Error(_) -> should.be_true(False)
101 }
102
103 // Verify order: 2025-01-25, 2025-01-20, 2025-01-18, 2025-01-15, 2025-01-10
104 let dates = list.map(records, fn(r) { r.indexed_at })
105 should.equal(dates, [
106 "2025-01-25T10:00:00Z",
107 "2025-01-20T10:00:00Z",
108 "2025-01-18T10:00:00Z",
109 "2025-01-15T10:00:00Z",
110 "2025-01-10T10:00:00Z",
111 ])
112 }
113 Error(_) -> should.be_true(False)
114 }
115}
116
117// Test: Sort by indexedAt ASC
118pub fn test_sort_by_indexed_at_asc() {
119 let exec = create_test_db_with_records()
120
121 let result =
122 records.get_by_collection_paginated(
123 exec,
124 "xyz.statusphere.status",
125 Some(10),
126 None,
127 None,
128 None,
129 Some([#("indexed_at", "asc")]),
130 )
131
132 case result {
133 Ok(#(records, _, _, _)) -> {
134 // First record should be oldest (2025-01-10)
135 case list.first(records) {
136 Ok(first) -> should.equal(first.indexed_at, "2025-01-10T10:00:00Z")
137 Error(_) -> should.be_true(False)
138 }
139
140 // Verify ascending order
141 let dates = list.map(records, fn(r) { r.indexed_at })
142 should.equal(dates, [
143 "2025-01-10T10:00:00Z",
144 "2025-01-15T10:00:00Z",
145 "2025-01-18T10:00:00Z",
146 "2025-01-20T10:00:00Z",
147 "2025-01-25T10:00:00Z",
148 ])
149 }
150 Error(_) -> should.be_true(False)
151 }
152}
153
154// Test: Sort by JSON field (createdAt) DESC with NULLS LAST
155pub fn test_sort_by_json_field_desc_nulls_last() {
156 let exec = create_test_db_with_records()
157
158 let result =
159 records.get_by_collection_paginated(
160 exec,
161 "xyz.statusphere.status",
162 Some(10),
163 None,
164 None,
165 None,
166 Some([#("createdAt", "desc")]),
167 )
168
169 case result {
170 Ok(#(records, _, _, _)) -> {
171 // First record should have newest createdAt (2025-01-20)
172 case list.first(records) {
173 Ok(first) -> should.equal(first.indexed_at, "2025-01-20T10:00:00Z")
174 Error(_) -> should.be_true(False)
175 }
176
177 // Last two records should be NULL and invalid date (NULLS LAST)
178 case list.reverse(records) {
179 [last, second_last, ..] -> {
180 // These should be the records with null or invalid dates
181 let last_indexed = [last.indexed_at, second_last.indexed_at]
182 // Should contain both the null and "wowzers" records
183 should.be_true(
184 list.contains(last_indexed, "2025-01-25T10:00:00Z")
185 || list.contains(last_indexed, "2025-01-18T10:00:00Z"),
186 )
187 }
188 _ -> should.be_true(False)
189 }
190 }
191 Error(_) -> should.be_true(False)
192 }
193}
194
195// Test: Sort by JSON field (createdAt) ASC with NULLS LAST
196pub fn test_sort_by_json_field_asc_nulls_last() {
197 let exec = create_test_db_with_records()
198
199 let result =
200 records.get_by_collection_paginated(
201 exec,
202 "xyz.statusphere.status",
203 Some(10),
204 None,
205 None,
206 None,
207 Some([#("createdAt", "asc")]),
208 )
209
210 case result {
211 Ok(#(records, _, _, _)) -> {
212 // First record should have oldest valid createdAt (2025-01-10)
213 case list.first(records) {
214 Ok(first) -> should.equal(first.indexed_at, "2025-01-10T10:00:00Z")
215 Error(_) -> should.be_true(False)
216 }
217
218 // Last two should still be NULL/invalid (NULLS LAST even with ASC)
219 case list.reverse(records) {
220 [last, second_last, ..] -> {
221 let last_indexed = [last.indexed_at, second_last.indexed_at]
222 should.be_true(
223 list.contains(last_indexed, "2025-01-25T10:00:00Z")
224 || list.contains(last_indexed, "2025-01-18T10:00:00Z"),
225 )
226 }
227 _ -> should.be_true(False)
228 }
229 }
230 Error(_) -> should.be_true(False)
231 }
232}
233
234// Test: Pagination with sorting (first N records)
235pub fn test_pagination_with_sorting() {
236 let exec = create_test_db_with_records()
237
238 // Get first 2 records sorted by createdAt DESC
239 let result =
240 records.get_by_collection_paginated(
241 exec,
242 "xyz.statusphere.status",
243 Some(2),
244 None,
245 None,
246 None,
247 Some([#("createdAt", "desc")]),
248 )
249
250 case result {
251 Ok(#(records, _, has_next, _)) -> {
252 // Should get exactly 2 records
253 should.equal(list.length(records), 2)
254
255 // Should have next page
256 should.be_true(has_next)
257
258 // First should be 2025-01-20, second should be 2025-01-15
259 case records {
260 [first, second] -> {
261 should.equal(first.indexed_at, "2025-01-20T10:00:00Z")
262 should.equal(second.indexed_at, "2025-01-15T10:00:00Z")
263 }
264 _ -> should.be_true(False)
265 }
266 }
267 Error(_) -> should.be_true(False)
268 }
269}
270
271// Test: Invalid date strings are treated as NULL
272pub fn test_invalid_dates_treated_as_null() {
273 let exec = create_test_db_with_records()
274
275 let result =
276 records.get_by_collection_paginated(
277 exec,
278 "xyz.statusphere.status",
279 Some(10),
280 None,
281 None,
282 None,
283 Some([#("createdAt", "desc")]),
284 )
285
286 case result {
287 Ok(#(records, _, _, _)) -> {
288 // The record with "wowzers" should be near the end (treated as NULL)
289 // Find the "wowzers" record by its indexed_at
290 let wowzers_position =
291 list.index_map(records, fn(r: types.Record, idx) {
292 case r.indexed_at == "2025-01-18T10:00:00Z" {
293 True -> Some(idx)
294 False -> None
295 }
296 })
297 |> list.filter_map(fn(x) { option.to_result(x, Nil) })
298 |> list.first
299
300 case wowzers_position {
301 Ok(pos) -> {
302 // Should be in last 2 positions (index 3 or 4 out of 5 records)
303 should.be_true(pos >= 3)
304 }
305 Error(_) -> should.be_true(False)
306 }
307 }
308 Error(_) -> should.be_true(False)
309 }
310}
311
312// Test: Cursor-based pagination works correctly
313pub fn test_cursor_pagination() {
314 let exec = create_test_db_with_records()
315
316 // Get first page of 2 records
317 let first_page =
318 records.get_by_collection_paginated(
319 exec,
320 "xyz.statusphere.status",
321 Some(2),
322 None,
323 None,
324 None,
325 Some([#("indexed_at", "desc")]),
326 )
327
328 case first_page {
329 Ok(#(first_records, Some(end_cursor), has_next, _)) -> {
330 // Should get exactly 2 records
331 should.equal(list.length(first_records), 2)
332
333 // Should have next page
334 should.be_true(has_next)
335
336 // First page should be most recent (2025-01-25 and 2025-01-20)
337 case first_records {
338 [first, second] -> {
339 should.equal(first.indexed_at, "2025-01-25T10:00:00Z")
340 should.equal(second.indexed_at, "2025-01-20T10:00:00Z")
341
342 // Now get second page using the cursor
343 let second_page =
344 records.get_by_collection_paginated(
345 exec,
346 "xyz.statusphere.status",
347 Some(2),
348 Some(end_cursor),
349 None,
350 None,
351 Some([#("indexed_at", "desc")]),
352 )
353
354 case second_page {
355 Ok(#(second_records, _, second_has_next, _)) -> {
356 // Should get exactly 2 records
357 should.equal(list.length(second_records), 2)
358
359 // Should have next page (1 record remaining)
360 should.be_true(second_has_next)
361
362 // Second page should be next two (2025-01-18 and 2025-01-15)
363 case second_records {
364 [third, fourth] -> {
365 should.equal(third.indexed_at, "2025-01-18T10:00:00Z")
366 should.equal(fourth.indexed_at, "2025-01-15T10:00:00Z")
367
368 // Verify no overlap - records should be different
369 should.not_equal(first.uri, third.uri)
370 should.not_equal(first.uri, fourth.uri)
371 should.not_equal(second.uri, third.uri)
372 should.not_equal(second.uri, fourth.uri)
373 }
374 _ -> should.be_true(False)
375 }
376 }
377 Error(_) -> should.be_true(False)
378 }
379 }
380 _ -> should.be_true(False)
381 }
382 }
383 _ -> should.be_true(False)
384 }
385}
386
387// Test: Cursor pagination with no next page
388pub fn test_cursor_pagination_last_page() {
389 let exec = create_test_db_with_records()
390
391 // Get first 4 records, leaving only 1
392 let first_page =
393 records.get_by_collection_paginated(
394 exec,
395 "xyz.statusphere.status",
396 Some(4),
397 None,
398 None,
399 None,
400 Some([#("indexed_at", "desc")]),
401 )
402
403 case first_page {
404 Ok(#(_, Some(end_cursor), has_next, _)) -> {
405 // Should have next page
406 should.be_true(has_next)
407
408 // Get last page
409 let last_page =
410 records.get_by_collection_paginated(
411 exec,
412 "xyz.statusphere.status",
413 Some(2),
414 Some(end_cursor),
415 None,
416 None,
417 Some([#("indexed_at", "desc")]),
418 )
419
420 case last_page {
421 Ok(#(last_records, _, last_has_next, _)) -> {
422 // Should get exactly 1 record (only 1 remaining)
423 should.equal(list.length(last_records), 1)
424
425 // Should NOT have next page
426 should.be_false(last_has_next)
427
428 // Should be the oldest record
429 case list.first(last_records) {
430 Ok(last) -> should.equal(last.indexed_at, "2025-01-10T10:00:00Z")
431 Error(_) -> should.be_true(False)
432 }
433 }
434 Error(_) -> should.be_true(False)
435 }
436 }
437 _ -> should.be_true(False)
438 }
439}