slices.network
Auto-indexing service and GraphQL API for AT Protocol Records
quickslice.slices.network/
atproto
gleam
graphql
1import database/executor.{Int, Text}
2import database/queries/where_clause
3import gleam/dict
4import gleam/list
5import gleam/option.{None, Some}
6import gleam/string
7import gleeunit
8import gleeunit/should
9import test_helpers
10
11pub fn main() {
12 gleeunit.main()
13}
14
15fn get_test_exec() {
16 let assert Ok(exec) = test_helpers.create_test_db()
17 exec
18}
19
20// Test: Empty where clause should produce empty SQL
21pub fn build_where_empty_clause_test() {
22 let exec = get_test_exec()
23 let clause = where_clause.empty_clause()
24 let #(sql, params) = where_clause.build_where_sql(exec, clause, False, 1)
25
26 sql |> should.equal("")
27 list.length(params) |> should.equal(0)
28}
29
30// Test: Single eq operator on table column
31pub fn build_where_eq_on_table_column_test() {
32 let exec = get_test_exec()
33 let condition =
34 where_clause.WhereCondition(
35 eq: Some(Text("app.bsky.feed.post")),
36 in_values: None,
37 contains: None,
38 gt: None,
39 gte: None,
40 lt: None,
41 lte: None,
42 is_null: None,
43 is_numeric: False,
44 )
45 let clause =
46 where_clause.WhereClause(
47 conditions: dict.from_list([#("collection", condition)]),
48 and: None,
49 or: None,
50 )
51
52 let #(sql, params) = where_clause.build_where_sql(exec, clause, False, 1)
53
54 sql |> should.equal("collection = ?")
55 list.length(params) |> should.equal(1)
56}
57
58// Test: in operator with multiple values
59pub fn build_where_in_operator_test() {
60 let exec = get_test_exec()
61 let condition =
62 where_clause.WhereCondition(
63 eq: None,
64 in_values: Some([
65 Text("did1"),
66 Text("did2"),
67 Text("did3"),
68 ]),
69 contains: None,
70 gt: None,
71 gte: None,
72 lt: None,
73 lte: None,
74 is_null: None,
75 is_numeric: False,
76 )
77 let clause =
78 where_clause.WhereClause(
79 conditions: dict.from_list([#("did", condition)]),
80 and: None,
81 or: None,
82 )
83
84 let #(sql, params) = where_clause.build_where_sql(exec, clause, False, 1)
85
86 sql |> should.equal("did IN (?, ?, ?)")
87 list.length(params) |> should.equal(3)
88}
89
90// Test: gt operator on indexed_at
91pub fn build_where_gt_operator_test() {
92 let exec = get_test_exec()
93 let condition =
94 where_clause.WhereCondition(
95 eq: None,
96 in_values: None,
97 contains: None,
98 gt: Some(Text("2024-01-01T00:00:00Z")),
99 gte: None,
100 lt: None,
101 lte: None,
102 is_null: None,
103 is_numeric: False,
104 )
105 let clause =
106 where_clause.WhereClause(
107 conditions: dict.from_list([#("indexed_at", condition)]),
108 and: None,
109 or: None,
110 )
111
112 let #(sql, params) = where_clause.build_where_sql(exec, clause, False, 1)
113
114 sql |> should.equal("indexed_at > ?")
115 list.length(params) |> should.equal(1)
116}
117
118// Test: gte operator
119pub fn build_where_gte_operator_test() {
120 let exec = get_test_exec()
121 let condition =
122 where_clause.WhereCondition(
123 eq: None,
124 in_values: None,
125 contains: None,
126 gt: None,
127 gte: Some(Int(2000)),
128 lt: None,
129 lte: None,
130 is_null: None,
131 is_numeric: True,
132 )
133 let clause =
134 where_clause.WhereClause(
135 conditions: dict.from_list([#("year", condition)]),
136 and: None,
137 or: None,
138 )
139
140 let #(sql, params) = where_clause.build_where_sql(exec, clause, False, 1)
141
142 // Now includes CAST for numeric comparisons on JSON fields
143 sql |> should.equal("CAST(json_extract(json, '$.year') AS INTEGER) >= ?")
144 list.length(params) |> should.equal(1)
145}
146
147// Test: lt operator
148pub fn build_where_lt_operator_test() {
149 let exec = get_test_exec()
150 let condition =
151 where_clause.WhereCondition(
152 eq: None,
153 in_values: None,
154 contains: None,
155 gt: None,
156 gte: None,
157 lt: Some(Text("2024-12-31T23:59:59Z")),
158 lte: None,
159 is_null: None,
160 is_numeric: False,
161 )
162 let clause =
163 where_clause.WhereClause(
164 conditions: dict.from_list([#("indexed_at", condition)]),
165 and: None,
166 or: None,
167 )
168
169 let #(sql, params) = where_clause.build_where_sql(exec, clause, False, 1)
170
171 sql |> should.equal("indexed_at < ?")
172 list.length(params) |> should.equal(1)
173}
174
175// Test: lte operator
176pub fn build_where_lte_operator_test() {
177 let exec = get_test_exec()
178 let condition =
179 where_clause.WhereCondition(
180 eq: None,
181 in_values: None,
182 contains: None,
183 gt: None,
184 gte: None,
185 lt: None,
186 lte: Some(Int(100)),
187 is_null: None,
188 is_numeric: True,
189 )
190 let clause =
191 where_clause.WhereClause(
192 conditions: dict.from_list([#("count", condition)]),
193 and: None,
194 or: None,
195 )
196
197 let #(sql, params) = where_clause.build_where_sql(exec, clause, False, 1)
198
199 // Now includes CAST for numeric comparisons on JSON fields
200 sql |> should.equal("CAST(json_extract(json, '$.count') AS INTEGER) <= ?")
201 list.length(params) |> should.equal(1)
202}
203
204// Test: Range query with both gt and lt
205pub fn build_where_range_query_test() {
206 let exec = get_test_exec()
207 let condition =
208 where_clause.WhereCondition(
209 eq: None,
210 in_values: None,
211 contains: None,
212 gt: Some(Text("2024-01-01T00:00:00Z")),
213 gte: None,
214 lt: Some(Text("2024-02-01T00:00:00Z")),
215 lte: None,
216 is_null: None,
217 is_numeric: False,
218 )
219 let clause =
220 where_clause.WhereClause(
221 conditions: dict.from_list([#("indexed_at", condition)]),
222 and: None,
223 or: None,
224 )
225
226 let #(sql, params) = where_clause.build_where_sql(exec, clause, False, 1)
227
228 // Should combine both conditions with AND
229 sql |> should.equal("indexed_at > ? AND indexed_at < ?")
230 list.length(params) |> should.equal(2)
231}
232
233// Test: Multiple fields combined with AND
234pub fn build_where_multiple_fields_test() {
235 let exec = get_test_exec()
236 let cond1 =
237 where_clause.WhereCondition(
238 eq: Some(Text("app.bsky.feed.post")),
239 in_values: None,
240 contains: None,
241 gt: None,
242 gte: None,
243 lt: None,
244 lte: None,
245 is_null: None,
246 is_numeric: False,
247 )
248 let cond2 =
249 where_clause.WhereCondition(
250 eq: Some(Text("did:plc:xyz")),
251 in_values: None,
252 contains: None,
253 gt: None,
254 gte: None,
255 lt: None,
256 lte: None,
257 is_null: None,
258 is_numeric: False,
259 )
260 let clause =
261 where_clause.WhereClause(
262 conditions: dict.from_list([
263 #("collection", cond1),
264 #("did", cond2),
265 ]),
266 and: None,
267 or: None,
268 )
269
270 let #(sql, params) = where_clause.build_where_sql(exec, clause, False, 1)
271
272 // Order might vary due to dict, but should have AND
273 should.be_true(string.contains(sql, "AND"))
274 should.be_true(string.contains(sql, "collection = ?"))
275 should.be_true(string.contains(sql, "did = ?"))
276 list.length(params) |> should.equal(2)
277}
278
279// Phase 3 Tests: JSON Field Filtering
280
281// Test: Simple JSON field with eq operator
282pub fn build_where_json_field_eq_test() {
283 let exec = get_test_exec()
284 let condition =
285 where_clause.WhereCondition(
286 eq: Some(Text("Hello World")),
287 in_values: None,
288 contains: None,
289 gt: None,
290 gte: None,
291 lt: None,
292 lte: None,
293 is_null: None,
294 is_numeric: False,
295 )
296 let clause =
297 where_clause.WhereClause(
298 conditions: dict.from_list([#("text", condition)]),
299 and: None,
300 or: None,
301 )
302
303 let #(sql, params) = where_clause.build_where_sql(exec, clause, False, 1)
304
305 sql |> should.equal("json_extract(json, '$.text') = ?")
306 list.length(params) |> should.equal(1)
307}
308
309// Test: Nested JSON path (dot notation)
310pub fn build_where_nested_json_path_test() {
311 let exec = get_test_exec()
312 let condition =
313 where_clause.WhereCondition(
314 eq: Some(Text("Alice")),
315 in_values: None,
316 contains: None,
317 gt: None,
318 gte: None,
319 lt: None,
320 lte: None,
321 is_null: None,
322 is_numeric: False,
323 )
324 let clause =
325 where_clause.WhereClause(
326 conditions: dict.from_list([#("user.name", condition)]),
327 and: None,
328 or: None,
329 )
330
331 let #(sql, params) = where_clause.build_where_sql(exec, clause, False, 1)
332
333 sql |> should.equal("json_extract(json, '$.user.name') = ?")
334 list.length(params) |> should.equal(1)
335}
336
337// Test: Deeply nested JSON path
338pub fn build_where_deeply_nested_json_path_test() {
339 let exec = get_test_exec()
340 let condition =
341 where_clause.WhereCondition(
342 eq: Some(Text("value")),
343 in_values: None,
344 contains: None,
345 gt: None,
346 gte: None,
347 lt: None,
348 lte: None,
349 is_null: None,
350 is_numeric: False,
351 )
352 let clause =
353 where_clause.WhereClause(
354 conditions: dict.from_list([#("metadata.tags.0", condition)]),
355 and: None,
356 or: None,
357 )
358
359 let #(sql, params) = where_clause.build_where_sql(exec, clause, False, 1)
360
361 sql |> should.equal("json_extract(json, '$.metadata.tags.0') = ?")
362 list.length(params) |> should.equal(1)
363}
364
365// Test: JSON field with comparison operators
366pub fn build_where_json_field_comparison_test() {
367 let exec = get_test_exec()
368 let condition =
369 where_clause.WhereCondition(
370 eq: None,
371 in_values: None,
372 contains: None,
373 gt: Some(Int(100)),
374 gte: None,
375 lt: Some(Int(1000)),
376 lte: None,
377 is_null: None,
378 is_numeric: True,
379 )
380 let clause =
381 where_clause.WhereClause(
382 conditions: dict.from_list([#("likes", condition)]),
383 and: None,
384 or: None,
385 )
386
387 let #(sql, params) = where_clause.build_where_sql(exec, clause, False, 1)
388
389 // Now includes CAST for numeric comparisons on JSON fields
390 sql
391 |> should.equal(
392 "CAST(json_extract(json, '$.likes') AS INTEGER) > ? AND CAST(json_extract(json, '$.likes') AS INTEGER) < ?",
393 )
394 list.length(params) |> should.equal(2)
395}
396
397// Test: Mix of table columns and JSON fields
398pub fn build_where_mixed_table_and_json_test() {
399 let exec = get_test_exec()
400 let cond1 =
401 where_clause.WhereCondition(
402 eq: Some(Text("app.bsky.feed.post")),
403 in_values: None,
404 contains: None,
405 gt: None,
406 gte: None,
407 lt: None,
408 lte: None,
409 is_null: None,
410 is_numeric: False,
411 )
412 let cond2 =
413 where_clause.WhereCondition(
414 eq: None,
415 in_values: None,
416 contains: None,
417 gt: Some(Int(10)),
418 gte: None,
419 lt: None,
420 lte: None,
421 is_null: None,
422 is_numeric: True,
423 )
424 let clause =
425 where_clause.WhereClause(
426 conditions: dict.from_list([
427 #("collection", cond1),
428 #("replyCount", cond2),
429 ]),
430 and: None,
431 or: None,
432 )
433
434 let #(sql, params) = where_clause.build_where_sql(exec, clause, False, 1)
435
436 // Should have both table column and JSON extract with CAST for numeric comparison
437 should.be_true(string.contains(sql, "collection = ?"))
438 should.be_true(string.contains(
439 sql,
440 "CAST(json_extract(json, '$.replyCount') AS INTEGER) > ?",
441 ))
442 should.be_true(string.contains(sql, "AND"))
443 list.length(params) |> should.equal(2)
444}
445
446// Phase 4 Tests: Contains Operator
447
448// Test: contains on JSON field
449pub fn build_where_contains_json_field_test() {
450 let exec = get_test_exec()
451 let condition =
452 where_clause.WhereCondition(
453 eq: None,
454 in_values: None,
455 contains: Some("hello"),
456 gt: None,
457 gte: None,
458 lt: None,
459 lte: None,
460 is_null: None,
461 is_numeric: False,
462 )
463 let clause =
464 where_clause.WhereClause(
465 conditions: dict.from_list([#("text", condition)]),
466 and: None,
467 or: None,
468 )
469
470 let #(sql, params) = where_clause.build_where_sql(exec, clause, False, 1)
471
472 sql
473 |> should.equal(
474 "json_extract(json, '$.text') LIKE '%' || ? || '%' COLLATE NOCASE",
475 )
476 list.length(params) |> should.equal(1)
477}
478
479// Test: contains on table column (uri)
480pub fn build_where_contains_table_column_test() {
481 let exec = get_test_exec()
482 let condition =
483 where_clause.WhereCondition(
484 eq: None,
485 in_values: None,
486 contains: Some("app.bsky"),
487 gt: None,
488 gte: None,
489 lt: None,
490 lte: None,
491 is_null: None,
492 is_numeric: False,
493 )
494 let clause =
495 where_clause.WhereClause(
496 conditions: dict.from_list([#("uri", condition)]),
497 and: None,
498 or: None,
499 )
500
501 let #(sql, params) = where_clause.build_where_sql(exec, clause, False, 1)
502
503 sql |> should.equal("uri LIKE '%' || ? || '%' COLLATE NOCASE")
504 list.length(params) |> should.equal(1)
505}
506
507// Test: contains with special LIKE characters (should be escaped)
508pub fn build_where_contains_special_chars_test() {
509 let exec = get_test_exec()
510 let condition =
511 where_clause.WhereCondition(
512 eq: None,
513 in_values: None,
514 contains: Some("test%value"),
515 gt: None,
516 gte: None,
517 lt: None,
518 lte: None,
519 is_null: None,
520 is_numeric: False,
521 )
522 let clause =
523 where_clause.WhereClause(
524 conditions: dict.from_list([#("text", condition)]),
525 and: None,
526 or: None,
527 )
528
529 let #(sql, _params) = where_clause.build_where_sql(exec, clause, False, 1)
530
531 // SQL should be generated (actual escaping would be handled by the parameter binding)
532 should.be_true(string.contains(sql, "LIKE"))
533 should.be_true(string.contains(sql, "COLLATE NOCASE"))
534}
535
536// Test: Multiple contains conditions
537pub fn build_where_multiple_contains_test() {
538 let exec = get_test_exec()
539 let cond1 =
540 where_clause.WhereCondition(
541 eq: None,
542 in_values: None,
543 contains: Some("pearl jam"),
544 gt: None,
545 gte: None,
546 lt: None,
547 lte: None,
548 is_null: None,
549 is_numeric: False,
550 )
551 let cond2 =
552 where_clause.WhereCondition(
553 eq: None,
554 in_values: None,
555 contains: Some("rock"),
556 gt: None,
557 gte: None,
558 lt: None,
559 lte: None,
560 is_null: None,
561 is_numeric: False,
562 )
563 let clause =
564 where_clause.WhereClause(
565 conditions: dict.from_list([
566 #("artist", cond1),
567 #("genre", cond2),
568 ]),
569 and: None,
570 or: None,
571 )
572
573 let #(sql, params) = where_clause.build_where_sql(exec, clause, False, 1)
574
575 // Should have both LIKE clauses
576 should.be_true(string.contains(sql, "LIKE"))
577 should.be_true(string.contains(sql, "AND"))
578 list.length(params) |> should.equal(2)
579}
580
581// Test: contains combined with eq operator on same field
582pub fn build_where_contains_with_other_operators_test() {
583 let exec = get_test_exec()
584 let condition =
585 where_clause.WhereCondition(
586 eq: None,
587 in_values: None,
588 contains: Some("search"),
589 gt: Some(Int(100)),
590 gte: None,
591 lt: None,
592 lte: None,
593 is_null: None,
594 is_numeric: False,
595 )
596 let clause =
597 where_clause.WhereClause(
598 conditions: dict.from_list([#("text", condition)]),
599 and: None,
600 or: None,
601 )
602
603 let #(sql, params) = where_clause.build_where_sql(exec, clause, False, 1)
604
605 // Should have both LIKE and > operator
606 should.be_true(string.contains(sql, "LIKE"))
607 should.be_true(string.contains(sql, ">"))
608 should.be_true(string.contains(sql, "AND"))
609 list.length(params) |> should.equal(2)
610}
611
612// Phase 5 Tests: AND Logic
613
614// Test: Nested AND with two simple clauses
615pub fn build_where_nested_and_simple_test() {
616 let exec = get_test_exec()
617 let clause1 =
618 where_clause.WhereClause(
619 conditions: dict.from_list([
620 #(
621 "collection",
622 where_clause.WhereCondition(
623 eq: Some(Text("app.bsky.feed.post")),
624 in_values: None,
625 contains: None,
626 gt: None,
627 gte: None,
628 lt: None,
629 lte: None,
630 is_null: None,
631 is_numeric: False,
632 ),
633 ),
634 ]),
635 and: None,
636 or: None,
637 )
638
639 let clause2 =
640 where_clause.WhereClause(
641 conditions: dict.from_list([
642 #(
643 "did",
644 where_clause.WhereCondition(
645 eq: Some(Text("did:plc:test")),
646 in_values: None,
647 contains: None,
648 gt: None,
649 gte: None,
650 lt: None,
651 lte: None,
652 is_null: None,
653 is_numeric: False,
654 ),
655 ),
656 ]),
657 and: None,
658 or: None,
659 )
660
661 let root_clause =
662 where_clause.WhereClause(
663 conditions: dict.new(),
664 and: Some([clause1, clause2]),
665 or: None,
666 )
667
668 let #(sql, params) = where_clause.build_where_sql(exec, root_clause, False, 1)
669
670 // Should have both conditions AND'ed together with parentheses
671 should.be_true(string.contains(sql, "collection = ?"))
672 should.be_true(string.contains(sql, "did = ?"))
673 should.be_true(string.contains(sql, "AND"))
674 list.length(params) |> should.equal(2)
675}
676
677// Test: Nested AND with conditions at root level
678pub fn build_where_and_with_root_conditions_test() {
679 let exec = get_test_exec()
680 let nested_clause =
681 where_clause.WhereClause(
682 conditions: dict.from_list([
683 #(
684 "text",
685 where_clause.WhereCondition(
686 eq: None,
687 in_values: None,
688 contains: Some("hello"),
689 gt: None,
690 gte: None,
691 lt: None,
692 lte: None,
693 is_null: None,
694 is_numeric: False,
695 ),
696 ),
697 ]),
698 and: None,
699 or: None,
700 )
701
702 let root_clause =
703 where_clause.WhereClause(
704 conditions: dict.from_list([
705 #(
706 "collection",
707 where_clause.WhereCondition(
708 eq: Some(Text("app.bsky.feed.post")),
709 in_values: None,
710 contains: None,
711 gt: None,
712 gte: None,
713 lt: None,
714 lte: None,
715 is_null: None,
716 is_numeric: False,
717 ),
718 ),
719 ]),
720 and: Some([nested_clause]),
721 or: None,
722 )
723
724 let #(sql, params) = where_clause.build_where_sql(exec, root_clause, False, 1)
725
726 // Should have both root condition and nested condition
727 should.be_true(string.contains(sql, "collection = ?"))
728 should.be_true(string.contains(sql, "LIKE"))
729 should.be_true(string.contains(sql, "AND"))
730 list.length(params) |> should.equal(2)
731}
732
733// Test: Complex nested AND matching Slice API example
734// Example: (artist contains "pearl jam") AND (year >= 2000)
735pub fn build_where_complex_and_test() {
736 let exec = get_test_exec()
737 let artist_clause =
738 where_clause.WhereClause(
739 conditions: dict.from_list([
740 #(
741 "artist",
742 where_clause.WhereCondition(
743 eq: None,
744 in_values: None,
745 contains: Some("pearl jam"),
746 gt: None,
747 gte: None,
748 lt: None,
749 lte: None,
750 is_null: None,
751 is_numeric: False,
752 ),
753 ),
754 ]),
755 and: None,
756 or: None,
757 )
758
759 let year_clause =
760 where_clause.WhereClause(
761 conditions: dict.from_list([
762 #(
763 "year",
764 where_clause.WhereCondition(
765 eq: None,
766 in_values: None,
767 contains: None,
768 gt: None,
769 gte: Some(Int(2000)),
770 lt: None,
771 lte: None,
772 is_null: None,
773 is_numeric: False,
774 ),
775 ),
776 ]),
777 and: None,
778 or: None,
779 )
780
781 let root_clause =
782 where_clause.WhereClause(
783 conditions: dict.new(),
784 and: Some([artist_clause, year_clause]),
785 or: None,
786 )
787
788 let #(sql, params) = where_clause.build_where_sql(exec, root_clause, False, 1)
789
790 // Should have both conditions
791 should.be_true(string.contains(sql, "artist"))
792 should.be_true(string.contains(sql, "LIKE"))
793 should.be_true(string.contains(sql, "year"))
794 should.be_true(string.contains(sql, ">="))
795 should.be_true(string.contains(sql, "AND"))
796 list.length(params) |> should.equal(2)
797}
798
799// Test: Three-level nested AND
800pub fn build_where_deeply_nested_and_test() {
801 let exec = get_test_exec()
802 let inner_clause =
803 where_clause.WhereClause(
804 conditions: dict.from_list([
805 #(
806 "likes",
807 where_clause.WhereCondition(
808 eq: None,
809 in_values: None,
810 contains: None,
811 gt: Some(Int(10)),
812 gte: None,
813 lt: None,
814 lte: None,
815 is_null: None,
816 is_numeric: False,
817 ),
818 ),
819 ]),
820 and: None,
821 or: None,
822 )
823
824 let middle_clause =
825 where_clause.WhereClause(
826 conditions: dict.from_list([
827 #(
828 "text",
829 where_clause.WhereCondition(
830 eq: None,
831 in_values: None,
832 contains: Some("test"),
833 gt: None,
834 gte: None,
835 lt: None,
836 lte: None,
837 is_null: None,
838 is_numeric: False,
839 ),
840 ),
841 ]),
842 and: Some([inner_clause]),
843 or: None,
844 )
845
846 let root_clause =
847 where_clause.WhereClause(
848 conditions: dict.from_list([
849 #(
850 "collection",
851 where_clause.WhereCondition(
852 eq: Some(Text("app.bsky.feed.post")),
853 in_values: None,
854 contains: None,
855 gt: None,
856 gte: None,
857 lt: None,
858 lte: None,
859 is_null: None,
860 is_numeric: False,
861 ),
862 ),
863 ]),
864 and: Some([middle_clause]),
865 or: None,
866 )
867
868 let #(sql, params) = where_clause.build_where_sql(exec, root_clause, False, 1)
869
870 // Should have all three conditions
871 should.be_true(string.contains(sql, "collection = ?"))
872 should.be_true(string.contains(sql, "LIKE"))
873 should.be_true(string.contains(sql, "likes"))
874 should.be_true(string.contains(sql, ">"))
875 list.length(params) |> should.equal(3)
876}
877
878// Phase 6 Tests: OR Logic
879
880// Test: Simple OR with two clauses
881pub fn build_where_simple_or_test() {
882 let exec = get_test_exec()
883 let clause1 =
884 where_clause.WhereClause(
885 conditions: dict.from_list([
886 #(
887 "artist",
888 where_clause.WhereCondition(
889 eq: None,
890 in_values: None,
891 contains: Some("pearl jam"),
892 gt: None,
893 gte: None,
894 lt: None,
895 lte: None,
896 is_null: None,
897 is_numeric: False,
898 ),
899 ),
900 ]),
901 and: None,
902 or: None,
903 )
904
905 let clause2 =
906 where_clause.WhereClause(
907 conditions: dict.from_list([
908 #(
909 "genre",
910 where_clause.WhereCondition(
911 eq: Some(Text("rock")),
912 in_values: None,
913 contains: None,
914 gt: None,
915 gte: None,
916 lt: None,
917 lte: None,
918 is_null: None,
919 is_numeric: False,
920 ),
921 ),
922 ]),
923 and: None,
924 or: None,
925 )
926
927 let root_clause =
928 where_clause.WhereClause(
929 conditions: dict.new(),
930 and: None,
931 or: Some([clause1, clause2]),
932 )
933
934 let #(sql, params) = where_clause.build_where_sql(exec, root_clause, False, 1)
935
936 // Should have both conditions OR'ed together
937 should.be_true(string.contains(sql, "artist"))
938 should.be_true(string.contains(sql, "LIKE"))
939 should.be_true(string.contains(sql, "genre"))
940 should.be_true(string.contains(sql, "= ?"))
941 should.be_true(string.contains(sql, "OR"))
942 list.length(params) |> should.equal(2)
943}
944
945// Test: Combined AND/OR - Slice API example
946// Example: (artist contains "pearl jam" OR genre = "rock") AND (year >= 2000)
947pub fn build_where_combined_and_or_test() {
948 let exec = get_test_exec()
949 let artist_clause =
950 where_clause.WhereClause(
951 conditions: dict.from_list([
952 #(
953 "artist",
954 where_clause.WhereCondition(
955 eq: None,
956 in_values: None,
957 contains: Some("pearl jam"),
958 gt: None,
959 gte: None,
960 lt: None,
961 lte: None,
962 is_null: None,
963 is_numeric: False,
964 ),
965 ),
966 ]),
967 and: None,
968 or: None,
969 )
970
971 let genre_clause =
972 where_clause.WhereClause(
973 conditions: dict.from_list([
974 #(
975 "genre",
976 where_clause.WhereCondition(
977 eq: Some(Text("rock")),
978 in_values: None,
979 contains: None,
980 gt: None,
981 gte: None,
982 lt: None,
983 lte: None,
984 is_null: None,
985 is_numeric: False,
986 ),
987 ),
988 ]),
989 and: None,
990 or: None,
991 )
992
993 let or_clause =
994 where_clause.WhereClause(
995 conditions: dict.new(),
996 and: None,
997 or: Some([artist_clause, genre_clause]),
998 )
999
1000 let year_clause =
1001 where_clause.WhereClause(
1002 conditions: dict.from_list([
1003 #(
1004 "year",
1005 where_clause.WhereCondition(
1006 eq: None,
1007 in_values: None,
1008 contains: None,
1009 gt: None,
1010 gte: Some(Int(2000)),
1011 lt: None,
1012 lte: None,
1013 is_null: None,
1014 is_numeric: False,
1015 ),
1016 ),
1017 ]),
1018 and: None,
1019 or: None,
1020 )
1021
1022 let root_clause =
1023 where_clause.WhereClause(
1024 conditions: dict.new(),
1025 and: Some([or_clause, year_clause]),
1026 or: None,
1027 )
1028
1029 let #(sql, params) = where_clause.build_where_sql(exec, root_clause, False, 1)
1030
1031 // Should have proper precedence: (artist LIKE OR genre =) AND year >=
1032 should.be_true(string.contains(sql, "OR"))
1033 should.be_true(string.contains(sql, "AND"))
1034 should.be_true(string.contains(sql, "artist"))
1035 should.be_true(string.contains(sql, "genre"))
1036 should.be_true(string.contains(sql, "year"))
1037 list.length(params) |> should.equal(3)
1038}
1039
1040// Test: Complex nested OR/AND from Slice API documentation
1041// { "and": [ { "or": [artist, genre] }, { "and": [uri1, uri2] }, year ] }
1042pub fn build_where_complex_nested_or_and_test() {
1043 let exec = get_test_exec()
1044 let artist_clause =
1045 where_clause.WhereClause(
1046 conditions: dict.from_list([
1047 #(
1048 "artist",
1049 where_clause.WhereCondition(
1050 eq: None,
1051 in_values: None,
1052 contains: Some("pearl jam"),
1053 gt: None,
1054 gte: None,
1055 lt: None,
1056 lte: None,
1057 is_null: None,
1058 is_numeric: False,
1059 ),
1060 ),
1061 ]),
1062 and: None,
1063 or: None,
1064 )
1065
1066 let genre_clause =
1067 where_clause.WhereClause(
1068 conditions: dict.from_list([
1069 #(
1070 "genre",
1071 where_clause.WhereCondition(
1072 eq: None,
1073 in_values: None,
1074 contains: Some("rock"),
1075 gt: None,
1076 gte: None,
1077 lt: None,
1078 lte: None,
1079 is_null: None,
1080 is_numeric: False,
1081 ),
1082 ),
1083 ]),
1084 and: None,
1085 or: None,
1086 )
1087
1088 let or_group =
1089 where_clause.WhereClause(
1090 conditions: dict.new(),
1091 and: None,
1092 or: Some([artist_clause, genre_clause]),
1093 )
1094
1095 let uri1_clause =
1096 where_clause.WhereClause(
1097 conditions: dict.from_list([
1098 #(
1099 "uri",
1100 where_clause.WhereCondition(
1101 eq: None,
1102 in_values: None,
1103 contains: Some("app.bsky"),
1104 gt: None,
1105 gte: None,
1106 lt: None,
1107 lte: None,
1108 is_null: None,
1109 is_numeric: False,
1110 ),
1111 ),
1112 ]),
1113 and: None,
1114 or: None,
1115 )
1116
1117 let uri2_clause =
1118 where_clause.WhereClause(
1119 conditions: dict.from_list([
1120 #(
1121 "uri",
1122 where_clause.WhereCondition(
1123 eq: None,
1124 in_values: None,
1125 contains: Some("post"),
1126 gt: None,
1127 gte: None,
1128 lt: None,
1129 lte: None,
1130 is_null: None,
1131 is_numeric: False,
1132 ),
1133 ),
1134 ]),
1135 and: None,
1136 or: None,
1137 )
1138
1139 let and_group =
1140 where_clause.WhereClause(
1141 conditions: dict.new(),
1142 and: Some([uri1_clause, uri2_clause]),
1143 or: None,
1144 )
1145
1146 let year_clause =
1147 where_clause.WhereClause(
1148 conditions: dict.from_list([
1149 #(
1150 "year",
1151 where_clause.WhereCondition(
1152 eq: None,
1153 in_values: None,
1154 contains: None,
1155 gt: None,
1156 gte: Some(Int(2000)),
1157 lt: None,
1158 lte: None,
1159 is_null: None,
1160 is_numeric: False,
1161 ),
1162 ),
1163 ]),
1164 and: None,
1165 or: None,
1166 )
1167
1168 let root_clause =
1169 where_clause.WhereClause(
1170 conditions: dict.new(),
1171 and: Some([or_group, and_group, year_clause]),
1172 or: None,
1173 )
1174
1175 let #(sql, params) = where_clause.build_where_sql(exec, root_clause, False, 1)
1176
1177 // Should have both OR and AND with proper nesting
1178 should.be_true(string.contains(sql, "OR"))
1179 should.be_true(string.contains(sql, "AND"))
1180 should.be_true(string.contains(sql, "artist"))
1181 should.be_true(string.contains(sql, "genre"))
1182 should.be_true(string.contains(sql, "uri"))
1183 should.be_true(string.contains(sql, "year"))
1184 list.length(params) |> should.equal(5)
1185}
1186
1187// Test: Multiple OR clauses at root level
1188pub fn build_where_multiple_or_at_root_test() {
1189 let exec = get_test_exec()
1190 let clause1 =
1191 where_clause.WhereClause(
1192 conditions: dict.from_list([
1193 #(
1194 "did",
1195 where_clause.WhereCondition(
1196 eq: Some(Text("did:plc:1")),
1197 in_values: None,
1198 contains: None,
1199 gt: None,
1200 gte: None,
1201 lt: None,
1202 lte: None,
1203 is_null: None,
1204 is_numeric: False,
1205 ),
1206 ),
1207 ]),
1208 and: None,
1209 or: None,
1210 )
1211
1212 let clause2 =
1213 where_clause.WhereClause(
1214 conditions: dict.from_list([
1215 #(
1216 "did",
1217 where_clause.WhereCondition(
1218 eq: Some(Text("did:plc:2")),
1219 in_values: None,
1220 contains: None,
1221 gt: None,
1222 gte: None,
1223 lt: None,
1224 lte: None,
1225 is_null: None,
1226 is_numeric: False,
1227 ),
1228 ),
1229 ]),
1230 and: None,
1231 or: None,
1232 )
1233
1234 let clause3 =
1235 where_clause.WhereClause(
1236 conditions: dict.from_list([
1237 #(
1238 "did",
1239 where_clause.WhereCondition(
1240 eq: Some(Text("did:plc:3")),
1241 in_values: None,
1242 contains: None,
1243 gt: None,
1244 gte: None,
1245 lt: None,
1246 lte: None,
1247 is_null: None,
1248 is_numeric: False,
1249 ),
1250 ),
1251 ]),
1252 and: None,
1253 or: None,
1254 )
1255
1256 let root_clause =
1257 where_clause.WhereClause(
1258 conditions: dict.new(),
1259 and: None,
1260 or: Some([clause1, clause2, clause3]),
1261 )
1262
1263 let #(sql, params) = where_clause.build_where_sql(exec, root_clause, False, 1)
1264
1265 // Should have all three OR'ed together
1266 should.be_true(string.contains(sql, "OR"))
1267 should.be_true(string.contains(sql, "did"))
1268 list.length(params) |> should.equal(3)
1269}
1270
1271// ===== isNull Operator Tests =====
1272
1273// Test: isNull true on JSON field
1274pub fn build_where_is_null_true_json_field_test() {
1275 let exec = get_test_exec()
1276 let condition =
1277 where_clause.WhereCondition(
1278 eq: None,
1279 in_values: None,
1280 contains: None,
1281 gt: None,
1282 gte: None,
1283 lt: None,
1284 lte: None,
1285 is_null: Some(True),
1286 is_numeric: False,
1287 )
1288 let clause =
1289 where_clause.WhereClause(
1290 conditions: dict.from_list([#("replyParent", condition)]),
1291 and: None,
1292 or: None,
1293 )
1294
1295 let #(sql, params) = where_clause.build_where_sql(exec, clause, False, 1)
1296
1297 sql |> should.equal("json_extract(json, '$.replyParent') IS NULL")
1298 list.length(params) |> should.equal(0)
1299}
1300
1301// Test: isNull false on JSON field
1302pub fn build_where_is_null_false_json_field_test() {
1303 let exec = get_test_exec()
1304 let condition =
1305 where_clause.WhereCondition(
1306 eq: None,
1307 in_values: None,
1308 contains: None,
1309 gt: None,
1310 gte: None,
1311 lt: None,
1312 lte: None,
1313 is_null: Some(False),
1314 is_numeric: False,
1315 )
1316 let clause =
1317 where_clause.WhereClause(
1318 conditions: dict.from_list([#("replyParent", condition)]),
1319 and: None,
1320 or: None,
1321 )
1322
1323 let #(sql, params) = where_clause.build_where_sql(exec, clause, False, 1)
1324
1325 sql |> should.equal("json_extract(json, '$.replyParent') IS NOT NULL")
1326 list.length(params) |> should.equal(0)
1327}
1328
1329// Test: isNull true on table column
1330pub fn build_where_is_null_true_table_column_test() {
1331 let exec = get_test_exec()
1332 let condition =
1333 where_clause.WhereCondition(
1334 eq: None,
1335 in_values: None,
1336 contains: None,
1337 gt: None,
1338 gte: None,
1339 lt: None,
1340 lte: None,
1341 is_null: Some(True),
1342 is_numeric: False,
1343 )
1344 let clause =
1345 where_clause.WhereClause(
1346 conditions: dict.from_list([#("cid", condition)]),
1347 and: None,
1348 or: None,
1349 )
1350
1351 let #(sql, params) = where_clause.build_where_sql(exec, clause, False, 1)
1352
1353 sql |> should.equal("cid IS NULL")
1354 list.length(params) |> should.equal(0)
1355}
1356
1357// Test: isNull false on table column
1358pub fn build_where_is_null_false_table_column_test() {
1359 let exec = get_test_exec()
1360 let condition =
1361 where_clause.WhereCondition(
1362 eq: None,
1363 in_values: None,
1364 contains: None,
1365 gt: None,
1366 gte: None,
1367 lt: None,
1368 lte: None,
1369 is_null: Some(False),
1370 is_numeric: False,
1371 )
1372 let clause =
1373 where_clause.WhereClause(
1374 conditions: dict.from_list([#("uri", condition)]),
1375 and: None,
1376 or: None,
1377 )
1378
1379 let #(sql, params) = where_clause.build_where_sql(exec, clause, False, 1)
1380
1381 sql |> should.equal("uri IS NOT NULL")
1382 list.length(params) |> should.equal(0)
1383}
1384
1385// Test: isNull with table prefix (for joins)
1386pub fn build_where_is_null_with_table_prefix_test() {
1387 let exec = get_test_exec()
1388 let condition =
1389 where_clause.WhereCondition(
1390 eq: None,
1391 in_values: None,
1392 contains: None,
1393 gt: None,
1394 gte: None,
1395 lt: None,
1396 lte: None,
1397 is_null: Some(True),
1398 is_numeric: False,
1399 )
1400 let clause =
1401 where_clause.WhereClause(
1402 conditions: dict.from_list([#("text", condition)]),
1403 and: None,
1404 or: None,
1405 )
1406
1407 let #(sql, params) = where_clause.build_where_sql(exec, clause, True, 1)
1408
1409 sql |> should.equal("json_extract(record.json, '$.text') IS NULL")
1410 list.length(params) |> should.equal(0)
1411}
1412
1413// Test: isNull in nested AND clause
1414pub fn build_where_is_null_in_and_clause_test() {
1415 let exec = get_test_exec()
1416 let is_null_clause =
1417 where_clause.WhereClause(
1418 conditions: dict.from_list([
1419 #(
1420 "replyParent",
1421 where_clause.WhereCondition(
1422 eq: None,
1423 in_values: None,
1424 contains: None,
1425 gt: None,
1426 gte: None,
1427 lt: None,
1428 lte: None,
1429 is_null: Some(True),
1430 is_numeric: False,
1431 ),
1432 ),
1433 ]),
1434 and: None,
1435 or: None,
1436 )
1437
1438 let text_clause =
1439 where_clause.WhereClause(
1440 conditions: dict.from_list([
1441 #(
1442 "text",
1443 where_clause.WhereCondition(
1444 eq: None,
1445 in_values: None,
1446 contains: Some("hello"),
1447 gt: None,
1448 gte: None,
1449 lt: None,
1450 lte: None,
1451 is_null: None,
1452 is_numeric: False,
1453 ),
1454 ),
1455 ]),
1456 and: None,
1457 or: None,
1458 )
1459
1460 let root_clause =
1461 where_clause.WhereClause(
1462 conditions: dict.new(),
1463 and: Some([is_null_clause, text_clause]),
1464 or: None,
1465 )
1466
1467 let #(sql, params) = where_clause.build_where_sql(exec, root_clause, False, 1)
1468
1469 should.be_true(string.contains(sql, "IS NULL"))
1470 should.be_true(string.contains(sql, "LIKE"))
1471 should.be_true(string.contains(sql, "AND"))
1472 list.length(params) |> should.equal(1)
1473}