trezy.codes
forked from
slices.network/quickslice
Auto-indexing service and GraphQL API for AT Protocol Records
chadtmiller.com
1/// Integration tests for sortBy and where on nested join connections
2///
3/// Tests verify that:
4/// - sortBy works on nested DID joins and reverse joins
5/// - where filters work on nested joins
6/// - totalCount reflects filtered results
7/// - Combination of sortBy + where works correctly
8import database/repositories/actors
9import database/repositories/lexicons
10import database/repositories/records
11import gleam/int
12import gleam/json
13import gleam/list
14import gleam/option
15import gleam/string
16import gleeunit/should
17import graphql/lexicon/schema as lexicon_schema
18import lib/oauth/did_cache
19import test_helpers
20
21// Helper to create a status lexicon with createdAt field
22fn create_status_lexicon() -> String {
23 json.object([
24 #("lexicon", json.int(1)),
25 #("id", json.string("xyz.statusphere.status")),
26 #(
27 "defs",
28 json.object([
29 #(
30 "main",
31 json.object([
32 #("type", json.string("record")),
33 #("key", json.string("tid")),
34 #(
35 "record",
36 json.object([
37 #("type", json.string("object")),
38 #(
39 "required",
40 json.array([json.string("status")], of: fn(x) { x }),
41 ),
42 #(
43 "properties",
44 json.object([
45 #(
46 "status",
47 json.object([
48 #("type", json.string("string")),
49 #("maxLength", json.int(300)),
50 ]),
51 ),
52 #(
53 "createdAt",
54 json.object([
55 #("type", json.string("string")),
56 #("format", json.string("datetime")),
57 ]),
58 ),
59 ]),
60 ),
61 ]),
62 ),
63 ]),
64 ),
65 ]),
66 ),
67 ])
68 |> json.to_string
69}
70
71// Helper to create a profile lexicon
72fn create_profile_lexicon() -> String {
73 json.object([
74 #("lexicon", json.int(1)),
75 #("id", json.string("app.bsky.actor.profile")),
76 #(
77 "defs",
78 json.object([
79 #(
80 "main",
81 json.object([
82 #("type", json.string("record")),
83 #("key", json.string("literal:self")),
84 #(
85 "record",
86 json.object([
87 #("type", json.string("object")),
88 #(
89 "properties",
90 json.object([
91 #(
92 "displayName",
93 json.object([#("type", json.string("string"))]),
94 ),
95 ]),
96 ),
97 ]),
98 ),
99 ]),
100 ),
101 ]),
102 ),
103 ])
104 |> json.to_string
105}
106
107// Test: DID join with sortBy on createdAt DESC
108pub fn did_join_sortby_createdat_desc_test() {
109 // Setup database
110 let assert Ok(exec) = test_helpers.create_test_db()
111 let assert Ok(_) = test_helpers.create_lexicon_table(exec)
112 let assert Ok(_) = test_helpers.create_record_table(exec)
113 let assert Ok(_) = test_helpers.create_actor_table(exec)
114
115 // Insert lexicons
116 let status_lexicon = create_status_lexicon()
117 let profile_lexicon = create_profile_lexicon()
118 let assert Ok(_) =
119 lexicons.insert(exec, "xyz.statusphere.status", status_lexicon)
120 let assert Ok(_) =
121 lexicons.insert(exec, "app.bsky.actor.profile", profile_lexicon)
122
123 // Insert a profile
124 let profile_uri = "at://did:plc:user1/app.bsky.actor.profile/self"
125 let profile_json =
126 json.object([#("displayName", json.string("User One"))])
127 |> json.to_string
128
129 let assert Ok(_) =
130 records.insert(
131 exec,
132 profile_uri,
133 "cid_profile",
134 "did:plc:user1",
135 "app.bsky.actor.profile",
136 profile_json,
137 )
138
139 // Insert 5 statuses with DIFFERENT createdAt timestamps
140 // Status 1: 2024-01-01 (oldest)
141 // Status 2: 2024-01-02
142 // Status 3: 2024-01-03
143 // Status 4: 2024-01-04
144 // Status 5: 2024-01-05 (newest)
145 list.range(1, 5)
146 |> list.each(fn(i) {
147 let status_uri =
148 "at://did:plc:user1/xyz.statusphere.status/status" <> int.to_string(i)
149 let status_json =
150 json.object([
151 #("status", json.string("Status #" <> int.to_string(i))),
152 #(
153 "createdAt",
154 json.string("2024-01-0" <> int.to_string(i) <> "T12:00:00Z"),
155 ),
156 ])
157 |> json.to_string
158
159 let assert Ok(_) =
160 records.insert(
161 exec,
162 status_uri,
163 "cid_status" <> int.to_string(i),
164 "did:plc:user1",
165 "xyz.statusphere.status",
166 status_json,
167 )
168 Nil
169 })
170
171 // Execute GraphQL query with sortBy createdAt DESC and first:3
172 let query =
173 "
174 {
175 appBskyActorProfile {
176 edges {
177 node {
178 uri
179 statuses: xyzStatusphereStatusByDid(
180 first: 3
181 sortBy: [{field: \"createdAt\", direction: DESC}]
182 ) {
183 totalCount
184 edges {
185 node {
186 status
187 createdAt
188 }
189 }
190 }
191 }
192 }
193 }
194 }
195 "
196
197 let assert Ok(cache) = did_cache.start()
198 let assert Ok(response_json) =
199 lexicon_schema.execute_query_with_db(
200 exec,
201 query,
202 "{}",
203 Error(Nil),
204 cache,
205 option.None,
206 "",
207 "https://plc.directory",
208 )
209
210 // Verify totalCount is 5 (all statuses)
211 {
212 string.contains(response_json, "\"totalCount\":5")
213 || string.contains(response_json, "\"totalCount\": 5")
214 }
215 |> should.be_true
216
217 // With sortBy createdAt DESC, first:3 should return Status 5, 4, 3 (newest first)
218 string.contains(response_json, "Status #5")
219 |> should.be_true
220
221 string.contains(response_json, "Status #4")
222 |> should.be_true
223
224 string.contains(response_json, "Status #3")
225 |> should.be_true
226
227 // Should NOT contain Status 1 or 2 (they're older)
228 string.contains(response_json, "Status #1")
229 |> should.be_false
230
231 string.contains(response_json, "Status #2")
232 |> should.be_false
233
234 // Verify order: Status 5 should appear before Status 4
235 let pos_5 = case string.split(response_json, "Status #5") {
236 [before, ..] -> string.length(before)
237 [] -> 999_999
238 }
239
240 let pos_4 = case string.split(response_json, "Status #4") {
241 [before, ..] -> string.length(before)
242 [] -> 999_999
243 }
244
245 let pos_3 = case string.split(response_json, "Status #3") {
246 [before, ..] -> string.length(before)
247 [] -> 999_999
248 }
249
250 // Status 5 should come before Status 4
251 { pos_5 < pos_4 }
252 |> should.be_true
253
254 // Status 4 should come before Status 3
255 { pos_4 < pos_3 }
256 |> should.be_true
257}
258
259// Test: DID join with sortBy createdAt ASC
260pub fn did_join_sortby_createdat_asc_test() {
261 // Setup database
262 let assert Ok(exec) = test_helpers.create_test_db()
263 let assert Ok(_) = test_helpers.create_lexicon_table(exec)
264 let assert Ok(_) = test_helpers.create_record_table(exec)
265 let assert Ok(_) = test_helpers.create_actor_table(exec)
266
267 // Insert lexicons
268 let status_lexicon = create_status_lexicon()
269 let profile_lexicon = create_profile_lexicon()
270 let assert Ok(_) =
271 lexicons.insert(exec, "xyz.statusphere.status", status_lexicon)
272 let assert Ok(_) =
273 lexicons.insert(exec, "app.bsky.actor.profile", profile_lexicon)
274
275 // Insert a profile
276 let profile_uri = "at://did:plc:user1/app.bsky.actor.profile/self"
277 let profile_json =
278 json.object([#("displayName", json.string("User One"))])
279 |> json.to_string
280
281 let assert Ok(_) =
282 records.insert(
283 exec,
284 profile_uri,
285 "cid_profile",
286 "did:plc:user1",
287 "app.bsky.actor.profile",
288 profile_json,
289 )
290
291 // Insert 5 statuses with different timestamps
292 list.range(1, 5)
293 |> list.each(fn(i) {
294 let status_uri =
295 "at://did:plc:user1/xyz.statusphere.status/status" <> int.to_string(i)
296 let status_json =
297 json.object([
298 #("status", json.string("Status #" <> int.to_string(i))),
299 #(
300 "createdAt",
301 json.string("2024-01-0" <> int.to_string(i) <> "T12:00:00Z"),
302 ),
303 ])
304 |> json.to_string
305
306 let assert Ok(_) =
307 records.insert(
308 exec,
309 status_uri,
310 "cid_status" <> int.to_string(i),
311 "did:plc:user1",
312 "xyz.statusphere.status",
313 status_json,
314 )
315 Nil
316 })
317
318 // Execute GraphQL query with sortBy createdAt ASC and first:3
319 let query =
320 "
321 {
322 appBskyActorProfile {
323 edges {
324 node {
325 statuses: xyzStatusphereStatusByDid(
326 first: 3
327 sortBy: [{field: \"createdAt\", direction: ASC}]
328 ) {
329 totalCount
330 edges {
331 node {
332 status
333 createdAt
334 }
335 }
336 }
337 }
338 }
339 }
340 }
341 "
342
343 let assert Ok(cache) = did_cache.start()
344 let assert Ok(response_json) =
345 lexicon_schema.execute_query_with_db(
346 exec,
347 query,
348 "{}",
349 Error(Nil),
350 cache,
351 option.None,
352 "",
353 "https://plc.directory",
354 )
355
356 // With sortBy createdAt ASC, first:3 should return Status 1, 2, 3 (oldest first)
357 string.contains(response_json, "Status #1")
358 |> should.be_true
359
360 string.contains(response_json, "Status #2")
361 |> should.be_true
362
363 string.contains(response_json, "Status #3")
364 |> should.be_true
365
366 // Should NOT contain Status 4 or 5
367 string.contains(response_json, "Status #4")
368 |> should.be_false
369
370 string.contains(response_json, "Status #5")
371 |> should.be_false
372}
373
374// Test: DID join with where filter on status field
375pub fn did_join_where_filter_test() {
376 // Setup database
377 let assert Ok(exec) = test_helpers.create_test_db()
378 let assert Ok(_) = test_helpers.create_lexicon_table(exec)
379 let assert Ok(_) = test_helpers.create_record_table(exec)
380 let assert Ok(_) = test_helpers.create_actor_table(exec)
381
382 // Insert lexicons
383 let status_lexicon = create_status_lexicon()
384 let profile_lexicon = create_profile_lexicon()
385 let assert Ok(_) =
386 lexicons.insert(exec, "xyz.statusphere.status", status_lexicon)
387 let assert Ok(_) =
388 lexicons.insert(exec, "app.bsky.actor.profile", profile_lexicon)
389
390 // Insert a profile
391 let profile_uri = "at://did:plc:user1/app.bsky.actor.profile/self"
392 let profile_json =
393 json.object([#("displayName", json.string("User One"))])
394 |> json.to_string
395
396 let assert Ok(_) =
397 records.insert(
398 exec,
399 profile_uri,
400 "cid_profile",
401 "did:plc:user1",
402 "app.bsky.actor.profile",
403 profile_json,
404 )
405
406 // Insert 5 statuses: 3 containing "gleam", 2 not
407 let statuses = [
408 #("Status about gleam programming", "2024-01-01T12:00:00Z"),
409 #("Random post", "2024-01-02T12:00:00Z"),
410 #("Learning gleam today", "2024-01-03T12:00:00Z"),
411 #("Another random post", "2024-01-04T12:00:00Z"),
412 #("Gleam is awesome", "2024-01-05T12:00:00Z"),
413 ]
414
415 list.index_map(statuses, fn(status_data, idx) {
416 let #(status_text, created_at) = status_data
417 let i = idx + 1
418 let status_uri =
419 "at://did:plc:user1/xyz.statusphere.status/status" <> int.to_string(i)
420 let status_json =
421 json.object([
422 #("status", json.string(status_text)),
423 #("createdAt", json.string(created_at)),
424 ])
425 |> json.to_string
426
427 let assert Ok(_) =
428 records.insert(
429 exec,
430 status_uri,
431 "cid_status" <> int.to_string(i),
432 "did:plc:user1",
433 "xyz.statusphere.status",
434 status_json,
435 )
436 Nil
437 })
438
439 // Execute GraphQL query with where filter: status contains "gleam"
440 let query =
441 "
442 {
443 appBskyActorProfile {
444 edges {
445 node {
446 statuses: xyzStatusphereStatusByDid(
447 sortBy: [{field: \"createdAt\", direction: DESC}]
448 where: {status: {contains: \"gleam\"}}
449 ) {
450 totalCount
451 edges {
452 node {
453 status
454 createdAt
455 }
456 }
457 }
458 }
459 }
460 }
461 }
462 "
463
464 let assert Ok(cache) = did_cache.start()
465 let assert Ok(response_json) =
466 lexicon_schema.execute_query_with_db(
467 exec,
468 query,
469 "{}",
470 Error(Nil),
471 cache,
472 option.None,
473 "",
474 "https://plc.directory",
475 )
476
477 // totalCount should be 3 (only statuses containing "gleam")
478 {
479 string.contains(response_json, "\"totalCount\":3")
480 || string.contains(response_json, "\"totalCount\": 3")
481 }
482 |> should.be_true
483
484 // Should contain statuses with "gleam"
485 string.contains(response_json, "gleam programming")
486 |> should.be_true
487
488 string.contains(response_json, "Learning gleam")
489 |> should.be_true
490
491 string.contains(response_json, "Gleam is awesome")
492 |> should.be_true
493
494 // Should NOT contain "Random post"
495 string.contains(response_json, "Random post")
496 |> should.be_false
497}
498
499// Test: Combination of sortBy + where + first
500pub fn did_join_sortby_where_first_test() {
501 // Setup database
502 let assert Ok(exec) = test_helpers.create_test_db()
503 let assert Ok(_) = test_helpers.create_lexicon_table(exec)
504 let assert Ok(_) = test_helpers.create_record_table(exec)
505 let assert Ok(_) = test_helpers.create_actor_table(exec)
506
507 // Insert lexicons
508 let status_lexicon = create_status_lexicon()
509 let profile_lexicon = create_profile_lexicon()
510 let assert Ok(_) =
511 lexicons.insert(exec, "xyz.statusphere.status", status_lexicon)
512 let assert Ok(_) =
513 lexicons.insert(exec, "app.bsky.actor.profile", profile_lexicon)
514
515 // Insert a profile
516 let profile_uri = "at://did:plc:user1/app.bsky.actor.profile/self"
517 let profile_json =
518 json.object([#("displayName", json.string("User One"))])
519 |> json.to_string
520
521 let assert Ok(_) =
522 records.insert(
523 exec,
524 profile_uri,
525 "cid_profile",
526 "did:plc:user1",
527 "app.bsky.actor.profile",
528 profile_json,
529 )
530
531 // Insert 5 statuses: 3 containing "rust", 2 not
532 let statuses = [
533 #("Learning rust basics", "2024-01-01T12:00:00Z"),
534 #("Random gleam post", "2024-01-02T12:00:00Z"),
535 #("Rust ownership is cool", "2024-01-03T12:00:00Z"),
536 #("Another elixir post", "2024-01-04T12:00:00Z"),
537 #("Rust async programming", "2024-01-05T12:00:00Z"),
538 ]
539
540 list.index_map(statuses, fn(status_data, idx) {
541 let #(status_text, created_at) = status_data
542 let i = idx + 1
543 let status_uri =
544 "at://did:plc:user1/xyz.statusphere.status/status" <> int.to_string(i)
545 let status_json =
546 json.object([
547 #("status", json.string(status_text)),
548 #("createdAt", json.string(created_at)),
549 ])
550 |> json.to_string
551
552 let assert Ok(_) =
553 records.insert(
554 exec,
555 status_uri,
556 "cid_status" <> int.to_string(i),
557 "did:plc:user1",
558 "xyz.statusphere.status",
559 status_json,
560 )
561 Nil
562 })
563
564 // Execute query: where status contains "rust", sortBy createdAt DESC, first: 2
565 // Should return the 2 newest rust posts
566 let query =
567 "
568 {
569 appBskyActorProfile {
570 edges {
571 node {
572 statuses: xyzStatusphereStatusByDid(
573 first: 2
574 sortBy: [{field: \"createdAt\", direction: DESC}]
575 where: {status: {contains: \"rust\"}}
576 ) {
577 totalCount
578 edges {
579 node {
580 status
581 createdAt
582 }
583 }
584 pageInfo {
585 hasNextPage
586 }
587 }
588 }
589 }
590 }
591 }
592 "
593
594 let assert Ok(cache) = did_cache.start()
595 let assert Ok(response_json) =
596 lexicon_schema.execute_query_with_db(
597 exec,
598 query,
599 "{}",
600 Error(Nil),
601 cache,
602 option.None,
603 "",
604 "https://plc.directory",
605 )
606
607 // totalCount should be 3 (all rust posts)
608 {
609 string.contains(response_json, "\"totalCount\":3")
610 || string.contains(response_json, "\"totalCount\": 3")
611 }
612 |> should.be_true
613
614 // Should contain the 2 newest rust posts
615 string.contains(response_json, "Rust async programming")
616 |> should.be_true
617
618 string.contains(response_json, "Rust ownership")
619 |> should.be_true
620
621 // Should NOT contain the oldest rust post
622 string.contains(response_json, "Learning rust basics")
623 |> should.be_false
624
625 // Should NOT contain non-rust posts
626 string.contains(response_json, "elixir")
627 |> should.be_false
628
629 string.contains(response_json, "gleam")
630 |> should.be_false
631
632 // hasNextPage should be true (1 more rust post available)
633 {
634 string.contains(response_json, "\"hasNextPage\":true")
635 || string.contains(response_json, "\"hasNextPage\": true")
636 }
637 |> should.be_true
638}
639
640// Test: Exact query pattern from user - top-level where + nested sortBy
641pub fn user_query_pattern_test() {
642 // Setup database
643 let assert Ok(exec) = test_helpers.create_test_db()
644 let assert Ok(_) = test_helpers.create_lexicon_table(exec)
645 let assert Ok(_) = test_helpers.create_record_table(exec)
646 let assert Ok(_) = test_helpers.create_actor_table(exec)
647
648 // Insert lexicons
649 let status_lexicon = create_status_lexicon()
650 let profile_lexicon = create_profile_lexicon()
651 let assert Ok(_) =
652 lexicons.insert(exec, "xyz.statusphere.status", status_lexicon)
653 let assert Ok(_) =
654 lexicons.insert(exec, "app.bsky.actor.profile", profile_lexicon)
655
656 // Insert 2 profiles with different handles
657 let profile1_uri = "at://did:plc:user1/app.bsky.actor.profile/self"
658 let profile1_json =
659 json.object([#("displayName", json.string("User One"))])
660 |> json.to_string
661
662 let assert Ok(_) =
663 records.insert(
664 exec,
665 profile1_uri,
666 "cid_profile1",
667 "did:plc:user1",
668 "app.bsky.actor.profile",
669 profile1_json,
670 )
671
672 let assert Ok(_) = actors.upsert(exec, "did:plc:user1", "chadtmiller.com")
673
674 let profile2_uri = "at://did:plc:user2/app.bsky.actor.profile/self"
675 let profile2_json =
676 json.object([#("displayName", json.string("User Two"))])
677 |> json.to_string
678
679 let assert Ok(_) =
680 records.insert(
681 exec,
682 profile2_uri,
683 "cid_profile2",
684 "did:plc:user2",
685 "app.bsky.actor.profile",
686 profile2_json,
687 )
688
689 let assert Ok(_) = actors.upsert(exec, "did:plc:user2", "other.com")
690
691 // Insert statuses for user1 (chadtmiller.com)
692 let statuses1 = [
693 #("First status", "2024-01-01T12:00:00Z"),
694 #("Second status", "2024-01-02T12:00:00Z"),
695 #("Third status", "2024-01-03T12:00:00Z"),
696 ]
697
698 list.index_map(statuses1, fn(status_data, idx) {
699 let #(status_text, created_at) = status_data
700 let i = idx + 1
701 let status_uri =
702 "at://did:plc:user1/xyz.statusphere.status/status" <> int.to_string(i)
703 let status_json =
704 json.object([
705 #("status", json.string(status_text)),
706 #("createdAt", json.string(created_at)),
707 ])
708 |> json.to_string
709
710 let assert Ok(_) =
711 records.insert(
712 exec,
713 status_uri,
714 "cid_status1_" <> int.to_string(i),
715 "did:plc:user1",
716 "xyz.statusphere.status",
717 status_json,
718 )
719 Nil
720 })
721
722 // Insert statuses for user2 (should be filtered out)
723 let assert Ok(_) =
724 records.insert(
725 exec,
726 "at://did:plc:user2/xyz.statusphere.status/status1",
727 "cid_status2_1",
728 "did:plc:user2",
729 "xyz.statusphere.status",
730 json.object([
731 #("status", json.string("Other user status")),
732 #("createdAt", json.string("2024-01-01T12:00:00Z")),
733 ])
734 |> json.to_string,
735 )
736
737 // Execute query matching user's pattern: top-level where + nested sortBy
738 let query =
739 "
740 {
741 appBskyActorProfile(where: {actorHandle: {eq: \"chadtmiller.com\"}}) {
742 totalCount
743 edges {
744 node {
745 actorHandle
746 statuses: xyzStatusphereStatusByDid(
747 first: 12
748 sortBy: [{field: \"createdAt\", direction: DESC}]
749 ) {
750 totalCount
751 edges {
752 node {
753 status
754 createdAt
755 }
756 }
757 }
758 }
759 }
760 }
761 }
762 "
763
764 let assert Ok(cache) = did_cache.start()
765 let assert Ok(response_json) =
766 lexicon_schema.execute_query_with_db(
767 exec,
768 query,
769 "{}",
770 Error(Nil),
771 cache,
772 option.None,
773 "",
774 "https://plc.directory",
775 )
776
777 // Should only return 1 profile (chadtmiller.com)
778 {
779 string.contains(response_json, "\"totalCount\":1")
780 || string.contains(response_json, "\"totalCount\": 1")
781 }
782 |> should.be_true
783
784 // Should contain chadtmiller.com handle
785 string.contains(response_json, "chadtmiller.com")
786 |> should.be_true
787
788 // Should NOT contain other.com
789 string.contains(response_json, "other.com")
790 |> should.be_false
791
792 // Nested statuses should have totalCount 3
793 // Count occurrences of totalCount in response
794 let total_count_occurrences =
795 string.split(response_json, "\"totalCount\"")
796 |> list.length
797 |> fn(x) { x - 1 }
798
799 // Should have 2 totalCount fields (one for profiles, one for statuses)
800 total_count_occurrences
801 |> should.equal(2)
802
803 // Statuses should be in DESC order: Third, Second, First
804 string.contains(response_json, "Third status")
805 |> should.be_true
806
807 string.contains(response_json, "Second status")
808 |> should.be_true
809
810 string.contains(response_json, "First status")
811 |> should.be_true
812
813 // Verify order: Third should come before Second
814 let pos_third = case string.split(response_json, "Third status") {
815 [before, ..] -> string.length(before)
816 [] -> 999_999
817 }
818
819 let pos_second = case string.split(response_json, "Second status") {
820 [before, ..] -> string.length(before)
821 [] -> 999_999
822 }
823
824 let pos_first = case string.split(response_json, "First status") {
825 [before, ..] -> string.length(before)
826 [] -> 999_999
827 }
828
829 { pos_third < pos_second }
830 |> should.be_true
831
832 { pos_second < pos_first }
833 |> should.be_true
834
835 // Should NOT contain other user's status
836 string.contains(response_json, "Other user status")
837 |> should.be_false
838}