slices.network
Auto-indexing service and GraphQL API for AT Protocol Records
quickslice.slices.network/
atproto
gleam
graphql
chadtmiller.com
1import database/executor.{type Executor, Int, Text}
2import database/queries/where_clause
3import database/repositories/records
4import gleam/dict
5import gleam/list
6import gleam/option.{None, Some}
7import gleam/result
8import gleam/string
9import gleeunit
10import gleeunit/should
11import test_helpers
12
13pub fn main() {
14 gleeunit.main()
15}
16
17// Helper to setup test database with sample records
18fn setup_test_db() -> Result(Executor, String) {
19 use exec <- result.try(
20 test_helpers.create_test_db()
21 |> result.map_error(fn(_) { "Failed to connect" }),
22 )
23 use _ <- result.try(
24 test_helpers.create_record_table(exec)
25 |> result.map_error(fn(_) { "Failed to create table" }),
26 )
27
28 // Insert test records
29 use _ <- result.try(
30 records.insert(
31 exec,
32 "at://did:plc:1/app.bsky.feed.post/1",
33 "cid1",
34 "did:plc:1",
35 "app.bsky.feed.post",
36 "{\"text\":\"Hello World\",\"likes\":100}",
37 )
38 |> result.map_error(fn(_) { "Failed to insert record" }),
39 )
40
41 use _ <- result.try(
42 records.insert(
43 exec,
44 "at://did:plc:2/app.bsky.feed.post/2",
45 "cid2",
46 "did:plc:2",
47 "app.bsky.feed.post",
48 "{\"text\":\"Goodbye World\",\"likes\":50}",
49 )
50 |> result.map_error(fn(_) { "Failed to insert record" }),
51 )
52
53 use _ <- result.try(
54 records.insert(
55 exec,
56 "at://did:plc:3/app.bsky.feed.post/3",
57 "cid3",
58 "did:plc:3",
59 "app.bsky.feed.post",
60 "{\"text\":\"Test post\",\"likes\":200}",
61 )
62 |> result.map_error(fn(_) { "Failed to insert record" }),
63 )
64
65 use _ <- result.try(
66 records.insert(
67 exec,
68 "at://did:plc:1/app.bsky.actor.profile/1",
69 "cid4",
70 "did:plc:1",
71 "app.bsky.actor.profile",
72 "{\"displayName\":\"Alice\"}",
73 )
74 |> result.map_error(fn(_) { "Failed to insert record" }),
75 )
76
77 Ok(exec)
78}
79
80// Test: Filter by DID (table column)
81pub fn filter_by_did_test() {
82 let assert Ok(exec) = setup_test_db()
83
84 let where_clause =
85 where_clause.WhereClause(
86 conditions: dict.from_list([
87 #(
88 "did",
89 where_clause.WhereCondition(
90 eq: Some(Text("did:plc:1")),
91 in_values: None,
92 contains: None,
93 gt: None,
94 gte: None,
95 lt: None,
96 lte: None,
97 is_null: None,
98 is_numeric: False,
99 ),
100 ),
101 ]),
102 and: None,
103 or: None,
104 )
105
106 let result =
107 records.get_by_collection_paginated_with_where(
108 exec,
109 "app.bsky.feed.post",
110 Some(10),
111 None,
112 None,
113 None,
114 None,
115 Some(where_clause),
116 )
117
118 case result {
119 Ok(#(records, _, _, _)) -> {
120 list.length(records) |> should.equal(1)
121 case list.first(records) {
122 Ok(record) -> record.did |> should.equal("did:plc:1")
123 Error(_) -> should.fail()
124 }
125 }
126 Error(_) -> should.fail()
127 }
128}
129
130// Test: Filter by JSON field with contains
131pub fn filter_by_json_contains_test() {
132 let assert Ok(exec) = setup_test_db()
133
134 let where_clause =
135 where_clause.WhereClause(
136 conditions: dict.from_list([
137 #(
138 "text",
139 where_clause.WhereCondition(
140 eq: None,
141 in_values: None,
142 contains: Some("Hello"),
143 gt: None,
144 gte: None,
145 lt: None,
146 lte: None,
147 is_null: None,
148 is_numeric: False,
149 ),
150 ),
151 ]),
152 and: None,
153 or: None,
154 )
155
156 let result =
157 records.get_by_collection_paginated_with_where(
158 exec,
159 "app.bsky.feed.post",
160 Some(10),
161 None,
162 None,
163 None,
164 None,
165 Some(where_clause),
166 )
167
168 case result {
169 Ok(#(records, _, _, _)) -> {
170 list.length(records) |> should.equal(1)
171 case list.first(records) {
172 Ok(record) -> should.be_true(string.contains(record.json, "Hello"))
173 Error(_) -> should.fail()
174 }
175 }
176 Error(_) -> should.fail()
177 }
178}
179
180// Test: Filter by JSON field comparison (gt)
181pub fn filter_by_json_comparison_test() {
182 let assert Ok(exec) = setup_test_db()
183
184 let where_clause =
185 where_clause.WhereClause(
186 conditions: dict.from_list([
187 #(
188 "likes",
189 where_clause.WhereCondition(
190 eq: None,
191 in_values: None,
192 contains: None,
193 gt: Some(Int(75)),
194 gte: None,
195 lt: None,
196 lte: None,
197 is_null: None,
198 is_numeric: False,
199 ),
200 ),
201 ]),
202 and: None,
203 or: None,
204 )
205
206 let result =
207 records.get_by_collection_paginated_with_where(
208 exec,
209 "app.bsky.feed.post",
210 Some(10),
211 None,
212 None,
213 None,
214 None,
215 Some(where_clause),
216 )
217
218 case result {
219 Ok(#(records, _, _, _)) -> {
220 // Should match records with likes > 75 (100 and 200)
221 list.length(records) |> should.equal(2)
222 }
223 Error(_) -> should.fail()
224 }
225}
226
227// Test: Range query with gte and lt
228pub fn filter_range_query_test() {
229 let assert Ok(exec) = setup_test_db()
230
231 let where_clause =
232 where_clause.WhereClause(
233 conditions: dict.from_list([
234 #(
235 "likes",
236 where_clause.WhereCondition(
237 eq: None,
238 in_values: None,
239 contains: None,
240 gt: None,
241 gte: Some(Int(50)),
242 lt: Some(Int(150)),
243 lte: None,
244 is_null: None,
245 is_numeric: False,
246 ),
247 ),
248 ]),
249 and: None,
250 or: None,
251 )
252
253 let result =
254 records.get_by_collection_paginated_with_where(
255 exec,
256 "app.bsky.feed.post",
257 Some(10),
258 None,
259 None,
260 None,
261 None,
262 Some(where_clause),
263 )
264
265 case result {
266 Ok(#(records, _, _, _)) -> {
267 // Should match records with 50 <= likes < 150 (50 and 100)
268 list.length(records) |> should.equal(2)
269 }
270 Error(_) -> should.fail()
271 }
272}
273
274// Test: Nested AND with multiple conditions
275pub fn filter_nested_and_test() {
276 let assert Ok(exec) = setup_test_db()
277
278 let did_clause =
279 where_clause.WhereClause(
280 conditions: dict.from_list([
281 #(
282 "did",
283 where_clause.WhereCondition(
284 eq: Some(Text("did:plc:1")),
285 in_values: None,
286 contains: None,
287 gt: None,
288 gte: None,
289 lt: None,
290 lte: None,
291 is_null: None,
292 is_numeric: False,
293 ),
294 ),
295 ]),
296 and: None,
297 or: None,
298 )
299
300 let likes_clause =
301 where_clause.WhereClause(
302 conditions: dict.from_list([
303 #(
304 "likes",
305 where_clause.WhereCondition(
306 eq: None,
307 in_values: None,
308 contains: None,
309 gt: Some(Int(50)),
310 gte: None,
311 lt: None,
312 lte: None,
313 is_null: None,
314 is_numeric: False,
315 ),
316 ),
317 ]),
318 and: None,
319 or: None,
320 )
321
322 let root_clause =
323 where_clause.WhereClause(
324 conditions: dict.new(),
325 and: Some([did_clause, likes_clause]),
326 or: None,
327 )
328
329 let result =
330 records.get_by_collection_paginated_with_where(
331 exec,
332 "app.bsky.feed.post",
333 Some(10),
334 None,
335 None,
336 None,
337 None,
338 Some(root_clause),
339 )
340
341 case result {
342 Ok(#(records, _, _, _)) -> {
343 // Should only match did:plc:1 with likes > 50 (the first record)
344 list.length(records) |> should.equal(1)
345 case list.first(records) {
346 Ok(record) -> {
347 record.did |> should.equal("did:plc:1")
348 should.be_true(string.contains(record.json, "100"))
349 }
350 Error(_) -> should.fail()
351 }
352 }
353 Error(_) -> should.fail()
354 }
355}
356
357// Test: Nested OR with multiple conditions
358pub fn filter_nested_or_test() {
359 let assert Ok(exec) = setup_test_db()
360
361 let did1_clause =
362 where_clause.WhereClause(
363 conditions: dict.from_list([
364 #(
365 "did",
366 where_clause.WhereCondition(
367 eq: Some(Text("did:plc:1")),
368 in_values: None,
369 contains: None,
370 gt: None,
371 gte: None,
372 lt: None,
373 lte: None,
374 is_null: None,
375 is_numeric: False,
376 ),
377 ),
378 ]),
379 and: None,
380 or: None,
381 )
382
383 let did2_clause =
384 where_clause.WhereClause(
385 conditions: dict.from_list([
386 #(
387 "did",
388 where_clause.WhereCondition(
389 eq: Some(Text("did:plc:2")),
390 in_values: None,
391 contains: None,
392 gt: None,
393 gte: None,
394 lt: None,
395 lte: None,
396 is_null: None,
397 is_numeric: False,
398 ),
399 ),
400 ]),
401 and: None,
402 or: None,
403 )
404
405 let root_clause =
406 where_clause.WhereClause(
407 conditions: dict.new(),
408 and: None,
409 or: Some([did1_clause, did2_clause]),
410 )
411
412 let result =
413 records.get_by_collection_paginated_with_where(
414 exec,
415 "app.bsky.feed.post",
416 Some(10),
417 None,
418 None,
419 None,
420 None,
421 Some(root_clause),
422 )
423
424 case result {
425 Ok(#(records, _, _, _)) -> {
426 // Should match both did:plc:1 and did:plc:2
427 list.length(records) |> should.equal(2)
428 }
429 Error(_) -> should.fail()
430 }
431}
432
433// Test: Empty where clause returns all records
434pub fn filter_empty_where_test() {
435 let assert Ok(exec) = setup_test_db()
436
437 let where_clause = where_clause.empty_clause()
438
439 let result =
440 records.get_by_collection_paginated_with_where(
441 exec,
442 "app.bsky.feed.post",
443 Some(10),
444 None,
445 None,
446 None,
447 None,
448 Some(where_clause),
449 )
450
451 case result {
452 Ok(#(records, _, _, _)) -> {
453 // Should return all 3 posts
454 list.length(records) |> should.equal(3)
455 }
456 Error(_) -> should.fail()
457 }
458}
459
460// Test: Where clause with pagination
461pub fn filter_with_pagination_test() {
462 let assert Ok(exec) = setup_test_db()
463
464 let where_clause =
465 where_clause.WhereClause(
466 conditions: dict.from_list([
467 #(
468 "likes",
469 where_clause.WhereCondition(
470 eq: None,
471 in_values: None,
472 contains: None,
473 gt: Some(Int(25)),
474 gte: None,
475 lt: None,
476 lte: None,
477 is_null: None,
478 is_numeric: False,
479 ),
480 ),
481 ]),
482 and: None,
483 or: None,
484 )
485
486 // First page: limit 2
487 let result =
488 records.get_by_collection_paginated_with_where(
489 exec,
490 "app.bsky.feed.post",
491 Some(2),
492 None,
493 None,
494 None,
495 None,
496 Some(where_clause),
497 )
498
499 case result {
500 Ok(#(records, next_cursor, has_next, has_prev)) -> {
501 list.length(records) |> should.equal(2)
502 should.be_true(has_next)
503 should.be_false(has_prev)
504 should.be_true(option.is_some(next_cursor))
505 }
506 Error(_) -> should.fail()
507 }
508}
509
510// Test: Numeric comparison with is_numeric=True uses INTEGER cast
511pub fn filter_numeric_with_cast_test() {
512 let assert Ok(exec) = setup_test_db()
513
514 // Test that numeric comparisons work with is_numeric: True
515 let where_clause =
516 where_clause.WhereClause(
517 conditions: dict.from_list([
518 #(
519 "likes",
520 where_clause.WhereCondition(
521 eq: None,
522 in_values: None,
523 contains: None,
524 gt: Some(Int(75)),
525 gte: None,
526 lt: None,
527 lte: None,
528 is_null: None,
529 is_numeric: True,
530 ),
531 ),
532 ]),
533 and: None,
534 or: None,
535 )
536
537 let result =
538 records.get_by_collection_paginated_with_where(
539 exec,
540 "app.bsky.feed.post",
541 Some(10),
542 None,
543 None,
544 None,
545 None,
546 Some(where_clause),
547 )
548
549 case result {
550 Ok(#(records, _, _, _)) -> {
551 // Should match records with likes > 75 (100 and 200)
552 list.length(records) |> should.equal(2)
553 }
554 Error(_) -> should.fail()
555 }
556}
557
558// Test: String datetime comparison without INTEGER cast
559pub fn filter_datetime_string_comparison_test() {
560 let assert Ok(exec) = setup_test_db()
561
562 // Insert records with ISO datetime strings in JSON
563 let assert Ok(_) =
564 executor.exec(
565 exec,
566 "INSERT INTO record (uri, cid, did, collection, json, indexed_at)
567 VALUES (?, 'cid5', 'did:plc:5', 'app.bsky.feed.post', ?, datetime('now'))",
568 [
569 Text("at://did:plc:5/app.bsky.feed.post/5"),
570 Text("{\"text\":\"Old post\",\"playedTime\":\"2024-01-01T00:00:00Z\"}"),
571 ],
572 )
573
574 let assert Ok(_) =
575 executor.exec(
576 exec,
577 "INSERT INTO record (uri, cid, did, collection, json, indexed_at)
578 VALUES (?, 'cid6', 'did:plc:6', 'app.bsky.feed.post', ?, datetime('now'))",
579 [
580 Text("at://did:plc:6/app.bsky.feed.post/6"),
581 Text("{\"text\":\"New post\",\"playedTime\":\"2024-12-01T00:00:00Z\"}"),
582 ],
583 )
584
585 // Filter for records with playedTime >= "2024-06-01" (string comparison)
586 let where_clause =
587 where_clause.WhereClause(
588 conditions: dict.from_list([
589 #(
590 "playedTime",
591 where_clause.WhereCondition(
592 eq: None,
593 in_values: None,
594 contains: None,
595 gt: None,
596 gte: Some(Text("2024-06-01T00:00:00Z")),
597 lt: None,
598 lte: None,
599 is_null: None,
600 is_numeric: False,
601 ),
602 ),
603 ]),
604 and: None,
605 or: None,
606 )
607
608 let result =
609 records.get_by_collection_paginated_with_where(
610 exec,
611 "app.bsky.feed.post",
612 Some(10),
613 None,
614 None,
615 None,
616 None,
617 Some(where_clause),
618 )
619
620 case result {
621 Ok(#(records, _, _, _)) -> {
622 // Should only match the "New post" with playedTime in December
623 list.length(records) |> should.equal(1)
624 case list.first(records) {
625 Ok(record) -> should.be_true(string.contains(record.json, "New post"))
626 Error(_) -> should.fail()
627 }
628 }
629 Error(_) -> should.fail()
630 }
631}
632
633// ===== isNull End-to-End Tests =====
634
635/// Test isNull: true end-to-end from GraphQL parsing through SQL execution
636pub fn is_null_true_end_to_end_test() {
637 let assert Ok(exec) = test_helpers.create_test_db()
638 let assert Ok(_) = test_helpers.create_record_table(exec)
639
640 // Insert records - some with replyParent, some without
641 let assert Ok(_) =
642 executor.exec(
643 exec,
644 "INSERT INTO record (uri, cid, did, collection, json, indexed_at)
645 VALUES (?, 'cid1', 'did:plc:1', 'app.bsky.feed.post', ?, datetime('now'))",
646 [
647 Text("at://did:plc:1/app.bsky.feed.post/1"),
648 Text("{\"text\":\"Root post\"}"),
649 ],
650 )
651
652 let assert Ok(_) =
653 executor.exec(
654 exec,
655 "INSERT INTO record (uri, cid, did, collection, json, indexed_at)
656 VALUES (?, 'cid2', 'did:plc:2', 'app.bsky.feed.post', ?, datetime('now'))",
657 [
658 Text("at://did:plc:2/app.bsky.feed.post/2"),
659 Text(
660 "{\"text\":\"Reply post\",\"replyParent\":\"at://did:plc:1/app.bsky.feed.post/1\"}",
661 ),
662 ],
663 )
664
665 let assert Ok(_) =
666 executor.exec(
667 exec,
668 "INSERT INTO record (uri, cid, did, collection, json, indexed_at)
669 VALUES (?, 'cid3', 'did:plc:3', 'app.bsky.feed.post', ?, datetime('now'))",
670 [
671 Text("at://did:plc:3/app.bsky.feed.post/3"),
672 Text("{\"text\":\"Another root post\"}"),
673 ],
674 )
675
676 // Filter for records where replyParent IS NULL (root posts only)
677 let where_clause =
678 where_clause.WhereClause(
679 conditions: dict.from_list([
680 #(
681 "replyParent",
682 where_clause.WhereCondition(
683 eq: None,
684 in_values: None,
685 contains: None,
686 gt: None,
687 gte: None,
688 lt: None,
689 lte: None,
690 is_null: Some(True),
691 is_numeric: False,
692 ),
693 ),
694 ]),
695 and: None,
696 or: None,
697 )
698
699 let result =
700 records.get_by_collection_paginated_with_where(
701 exec,
702 "app.bsky.feed.post",
703 Some(10),
704 None,
705 None,
706 None,
707 None,
708 Some(where_clause),
709 )
710
711 case result {
712 Ok(#(records_list, _, _, _)) -> {
713 // Should only match the 2 root posts (without replyParent)
714 list.length(records_list) |> should.equal(2)
715 // Verify none of them are replies
716 list.all(records_list, fn(record) {
717 !string.contains(record.json, "replyParent")
718 })
719 |> should.be_true
720 }
721 Error(_) -> should.fail()
722 }
723}
724
725/// Test isNull: false end-to-end from GraphQL parsing through SQL execution
726pub fn is_null_false_end_to_end_test() {
727 let assert Ok(exec) = test_helpers.create_test_db()
728 let assert Ok(_) = test_helpers.create_record_table(exec)
729
730 // Insert records - some with replyParent, some without
731 let assert Ok(_) =
732 executor.exec(
733 exec,
734 "INSERT INTO record (uri, cid, did, collection, json, indexed_at)
735 VALUES (?, 'cid1', 'did:plc:1', 'app.bsky.feed.post', ?, datetime('now'))",
736 [
737 Text("at://did:plc:1/app.bsky.feed.post/1"),
738 Text("{\"text\":\"Root post\"}"),
739 ],
740 )
741
742 let assert Ok(_) =
743 executor.exec(
744 exec,
745 "INSERT INTO record (uri, cid, did, collection, json, indexed_at)
746 VALUES (?, 'cid2', 'did:plc:2', 'app.bsky.feed.post', ?, datetime('now'))",
747 [
748 Text("at://did:plc:2/app.bsky.feed.post/2"),
749 Text(
750 "{\"text\":\"Reply post\",\"replyParent\":\"at://did:plc:1/app.bsky.feed.post/1\"}",
751 ),
752 ],
753 )
754
755 let assert Ok(_) =
756 executor.exec(
757 exec,
758 "INSERT INTO record (uri, cid, did, collection, json, indexed_at)
759 VALUES (?, 'cid3', 'did:plc:3', 'app.bsky.feed.post', ?, datetime('now'))",
760 [
761 Text("at://did:plc:3/app.bsky.feed.post/3"),
762 Text("{\"text\":\"Another root post\"}"),
763 ],
764 )
765
766 // Filter for records where replyParent IS NOT NULL (replies only)
767 let where_clause =
768 where_clause.WhereClause(
769 conditions: dict.from_list([
770 #(
771 "replyParent",
772 where_clause.WhereCondition(
773 eq: None,
774 in_values: None,
775 contains: None,
776 gt: None,
777 gte: None,
778 lt: None,
779 lte: None,
780 is_null: Some(False),
781 is_numeric: False,
782 ),
783 ),
784 ]),
785 and: None,
786 or: None,
787 )
788
789 let result =
790 records.get_by_collection_paginated_with_where(
791 exec,
792 "app.bsky.feed.post",
793 Some(10),
794 None,
795 None,
796 None,
797 None,
798 Some(where_clause),
799 )
800
801 case result {
802 Ok(#(records_list, _, _, _)) -> {
803 // Should only match the 1 reply post (with replyParent)
804 list.length(records_list) |> should.equal(1)
805 // Verify it's a reply
806 case list.first(records_list) {
807 Ok(record) ->
808 should.be_true(string.contains(record.json, "replyParent"))
809 Error(_) -> should.fail()
810 }
811 }
812 Error(_) -> should.fail()
813 }
814}