trezy.codes
forked from
slices.network/quickslice
Auto-indexing service and GraphQL API for AT Protocol Records
1import database/executor.{type Executor, type Value, Text}
2import gleam/dict.{type Dict}
3import gleam/list
4import gleam/option.{type Option, None, Some}
5import gleam/result
6import gleam/string
7
8/// Represents a single condition on a field with various comparison operators
9pub type WhereCondition {
10 WhereCondition(
11 eq: Option(Value),
12 in_values: Option(List(Value)),
13 contains: Option(String),
14 gt: Option(Value),
15 gte: Option(Value),
16 lt: Option(Value),
17 lte: Option(Value),
18 is_null: Option(Bool),
19 /// Whether the comparison values are numeric (affects JSON field casting)
20 is_numeric: Bool,
21 )
22}
23
24/// Represents a complete where clause with support for nested AND/OR logic
25pub type WhereClause {
26 WhereClause(
27 /// Field-level conditions (combined with AND)
28 conditions: Dict(String, WhereCondition),
29 /// Nested AND clauses - all must be true
30 and: Option(List(WhereClause)),
31 /// Nested OR clauses - at least one must be true
32 or: Option(List(WhereClause)),
33 )
34}
35
36/// Creates an empty WhereCondition with all operators set to None
37pub fn empty_condition() -> WhereCondition {
38 WhereCondition(
39 eq: None,
40 in_values: None,
41 contains: None,
42 gt: None,
43 gte: None,
44 lt: None,
45 lte: None,
46 is_null: None,
47 is_numeric: False,
48 )
49}
50
51/// Creates an empty WhereClause with no conditions
52pub fn empty_clause() -> WhereClause {
53 WhereClause(conditions: dict.new(), and: None, or: None)
54}
55
56/// Checks if a WhereCondition has any operators set
57pub fn is_condition_empty(condition: WhereCondition) -> Bool {
58 case condition {
59 WhereCondition(
60 eq: None,
61 in_values: None,
62 contains: None,
63 gt: None,
64 gte: None,
65 lt: None,
66 lte: None,
67 is_null: None,
68 is_numeric: _,
69 ) -> True
70 _ -> False
71 }
72}
73
74/// Checks if a WhereClause is empty (no conditions at all)
75pub fn is_clause_empty(clause: WhereClause) -> Bool {
76 dict.is_empty(clause.conditions) && clause.and == None && clause.or == None
77}
78
79/// Checks if a WhereClause requires a join with the actor table
80pub fn requires_actor_join(clause: WhereClause) -> Bool {
81 // Check if actorHandle is in the conditions
82 let has_actor_handle = dict.has_key(clause.conditions, "actorHandle")
83
84 // Check nested AND clauses
85 let has_actor_in_and = case clause.and {
86 Some(and_clauses) -> list.any(and_clauses, requires_actor_join)
87 None -> False
88 }
89
90 // Check nested OR clauses
91 let has_actor_in_or = case clause.or {
92 Some(or_clauses) -> list.any(or_clauses, requires_actor_join)
93 None -> False
94 }
95
96 has_actor_handle || has_actor_in_and || has_actor_in_or
97}
98
99// Table columns that should not use json_extract
100const table_columns = ["uri", "cid", "did", "collection", "indexed_at"]
101
102/// Determines if a field is a table column or a JSON field
103fn is_table_column(field: String) -> Bool {
104 list.contains(table_columns, field)
105}
106
107/// Builds the SQL reference for a field (either table column or JSON path)
108/// If use_table_prefix is true, table columns are prefixed with "record."
109fn build_field_ref(
110 exec: Executor,
111 field: String,
112 use_table_prefix: Bool,
113) -> String {
114 case field {
115 "actorHandle" -> "actor.handle"
116 _ ->
117 case is_table_column(field) {
118 True ->
119 case use_table_prefix {
120 True -> "record." <> field
121 False -> field
122 }
123 False -> {
124 let table_name = case use_table_prefix {
125 True -> "record."
126 False -> ""
127 }
128 executor.json_extract(exec, table_name <> "json", field)
129 }
130 }
131 }
132}
133
134/// Helper to determine if we should cast to numeric
135/// Uses the is_numeric flag set during value conversion
136fn should_cast_numeric(condition: WhereCondition) -> Bool {
137 // Only cast if we have numeric comparison operators AND the values are numeric
138 // String values (like ISO dates) should not be cast
139 condition.is_numeric
140 && {
141 option.is_some(condition.gt)
142 || option.is_some(condition.gte)
143 || option.is_some(condition.lt)
144 || option.is_some(condition.lte)
145 }
146}
147
148/// Builds field reference with optional numeric cast for JSON fields
149fn build_field_ref_with_cast(
150 exec: Executor,
151 field: String,
152 use_table_prefix: Bool,
153 cast_numeric: Bool,
154) -> String {
155 let field_ref = build_field_ref(exec, field, use_table_prefix)
156
157 // If it's a JSON field and we need numeric cast, wrap in CAST
158 case is_table_column(field) || !cast_numeric {
159 True -> field_ref
160 False -> {
161 // Use dialect-specific cast syntax
162 case executor.dialect(exec) {
163 executor.SQLite -> "CAST(" <> field_ref <> " AS INTEGER)"
164 executor.PostgreSQL -> "(" <> field_ref <> ")::INTEGER"
165 }
166 }
167 }
168}
169
170/// Get the LIKE collation syntax for case-insensitive search
171fn case_insensitive_like(exec: Executor) -> String {
172 case executor.dialect(exec) {
173 executor.SQLite -> " COLLATE NOCASE"
174 executor.PostgreSQL -> ""
175 // PostgreSQL ILIKE is case-insensitive by default
176 }
177}
178
179/// Get the LIKE operator for case-insensitive search
180fn like_operator(exec: Executor) -> String {
181 case executor.dialect(exec) {
182 executor.SQLite -> " LIKE "
183 executor.PostgreSQL -> " ILIKE "
184 }
185}
186
187/// Builds SQL for a single condition on a field
188/// Returns a list of SQL strings and accumulated parameters
189/// param_offset is the starting parameter index (1-based)
190fn build_single_condition(
191 exec: Executor,
192 field: String,
193 condition: WhereCondition,
194 use_table_prefix: Bool,
195 param_offset: Int,
196) -> #(List(String), List(Value), Int) {
197 // Check if numeric casting is needed (for gt/gte/lt/lte operators)
198 let has_numeric_comparison = should_cast_numeric(condition)
199
200 let field_ref =
201 build_field_ref_with_cast(
202 exec,
203 field,
204 use_table_prefix,
205 has_numeric_comparison,
206 )
207
208 // For isNull, we need the field ref without numeric cast
209 let field_ref_no_cast = build_field_ref(exec, field, use_table_prefix)
210
211 let mut_sql_parts = []
212 let mut_params = []
213 let mut_offset = param_offset
214
215 // eq operator
216 let #(sql_parts, params, offset) = case condition.eq {
217 Some(value) -> {
218 let placeholder = executor.placeholder(exec, mut_offset)
219 #(
220 [field_ref <> " = " <> placeholder, ..mut_sql_parts],
221 [value, ..mut_params],
222 mut_offset + 1,
223 )
224 }
225 None -> #(mut_sql_parts, mut_params, mut_offset)
226 }
227 let mut_sql_parts = sql_parts
228 let mut_params = params
229 let mut_offset = offset
230
231 // in operator
232 let #(sql_parts, params, offset) = case condition.in_values {
233 Some(values) -> {
234 case values {
235 [] -> #(mut_sql_parts, mut_params, mut_offset)
236 // Empty list - skip this condition
237 _ -> {
238 let placeholders =
239 list.index_map(values, fn(_, i) {
240 executor.placeholder(exec, mut_offset + i)
241 })
242 |> string.join(", ")
243 let sql = field_ref <> " IN (" <> placeholders <> ")"
244 #(
245 [sql, ..mut_sql_parts],
246 list.append(values, mut_params),
247 mut_offset + list.length(values),
248 )
249 }
250 }
251 }
252 None -> #(mut_sql_parts, mut_params, mut_offset)
253 }
254 let mut_sql_parts = sql_parts
255 let mut_params = params
256 let mut_offset = offset
257
258 // gt operator
259 let #(sql_parts, params, offset) = case condition.gt {
260 Some(value) -> {
261 let placeholder = executor.placeholder(exec, mut_offset)
262 #(
263 [field_ref <> " > " <> placeholder, ..mut_sql_parts],
264 [value, ..mut_params],
265 mut_offset + 1,
266 )
267 }
268 None -> #(mut_sql_parts, mut_params, mut_offset)
269 }
270 let mut_sql_parts = sql_parts
271 let mut_params = params
272 let mut_offset = offset
273
274 // gte operator
275 let #(sql_parts, params, offset) = case condition.gte {
276 Some(value) -> {
277 let placeholder = executor.placeholder(exec, mut_offset)
278 #(
279 [field_ref <> " >= " <> placeholder, ..mut_sql_parts],
280 [value, ..mut_params],
281 mut_offset + 1,
282 )
283 }
284 None -> #(mut_sql_parts, mut_params, mut_offset)
285 }
286 let mut_sql_parts = sql_parts
287 let mut_params = params
288 let mut_offset = offset
289
290 // lt operator
291 let #(sql_parts, params, offset) = case condition.lt {
292 Some(value) -> {
293 let placeholder = executor.placeholder(exec, mut_offset)
294 #(
295 [field_ref <> " < " <> placeholder, ..mut_sql_parts],
296 [value, ..mut_params],
297 mut_offset + 1,
298 )
299 }
300 None -> #(mut_sql_parts, mut_params, mut_offset)
301 }
302 let mut_sql_parts = sql_parts
303 let mut_params = params
304 let mut_offset = offset
305
306 // lte operator
307 let #(sql_parts, params, offset) = case condition.lte {
308 Some(value) -> {
309 let placeholder = executor.placeholder(exec, mut_offset)
310 #(
311 [field_ref <> " <= " <> placeholder, ..mut_sql_parts],
312 [value, ..mut_params],
313 mut_offset + 1,
314 )
315 }
316 None -> #(mut_sql_parts, mut_params, mut_offset)
317 }
318 let mut_sql_parts = sql_parts
319 let mut_params = params
320 let mut_offset = offset
321
322 // contains operator (case-insensitive LIKE)
323 let #(sql_parts, params, offset) = case condition.contains {
324 Some(search_text) -> {
325 let placeholder = executor.placeholder(exec, mut_offset)
326 let like_op = like_operator(exec)
327 let collation = case_insensitive_like(exec)
328 let sql =
329 field_ref
330 <> like_op
331 <> "'%' || "
332 <> placeholder
333 <> " || '%'"
334 <> collation
335 #(
336 [sql, ..mut_sql_parts],
337 [Text(search_text), ..mut_params],
338 mut_offset + 1,
339 )
340 }
341 None -> #(mut_sql_parts, mut_params, mut_offset)
342 }
343 let mut_sql_parts = sql_parts
344 let mut_params = params
345 let mut_offset = offset
346
347 // isNull operator (no parameters needed)
348 let #(sql_parts, params, offset) = case condition.is_null {
349 Some(True) -> {
350 let sql = field_ref_no_cast <> " IS NULL"
351 #([sql, ..mut_sql_parts], mut_params, mut_offset)
352 }
353 Some(False) -> {
354 let sql = field_ref_no_cast <> " IS NOT NULL"
355 #([sql, ..mut_sql_parts], mut_params, mut_offset)
356 }
357 None -> #(mut_sql_parts, mut_params, mut_offset)
358 }
359
360 // Reverse to maintain correct order (we built backwards)
361 #(list.reverse(sql_parts), list.reverse(params), offset)
362}
363
364/// Builds WHERE clause SQL from a WhereClause
365/// Returns tuple of (sql_string, parameters)
366/// use_table_prefix: if True, prefixes table columns with "record." for joins
367/// start_index: the starting parameter index (1-based) for placeholders
368pub fn build_where_sql(
369 exec: Executor,
370 clause: WhereClause,
371 use_table_prefix: Bool,
372 start_index: Int,
373) -> #(String, List(Value)) {
374 case is_clause_empty(clause) {
375 True -> #("", [])
376 False -> {
377 let #(sql_parts, params, _) =
378 build_where_clause_internal(exec, clause, use_table_prefix, start_index)
379 let sql = string.join(sql_parts, " AND ")
380 #(sql, params)
381 }
382 }
383}
384
385/// Internal recursive function to build where clause parts
386fn build_where_clause_internal(
387 exec: Executor,
388 clause: WhereClause,
389 use_table_prefix: Bool,
390 param_offset: Int,
391) -> #(List(String), List(Value), Int) {
392 let mut_sql_parts = []
393 let mut_params = []
394 let mut_offset = param_offset
395
396 // Build conditions from field-level conditions
397 let #(field_sql_parts, field_params, new_offset) =
398 dict.fold(
399 clause.conditions,
400 #([], [], mut_offset),
401 fn(acc, field, condition) {
402 let #(acc_sql, acc_params, acc_offset) = acc
403 let #(cond_sql_parts, cond_params, new_offset) =
404 build_single_condition(
405 exec,
406 field,
407 condition,
408 use_table_prefix,
409 acc_offset,
410 )
411 #(
412 list.append(acc_sql, cond_sql_parts),
413 list.append(acc_params, cond_params),
414 new_offset,
415 )
416 },
417 )
418
419 let mut_sql_parts = list.append(mut_sql_parts, field_sql_parts)
420 let mut_params = list.append(mut_params, field_params)
421 let mut_offset = new_offset
422
423 // Handle nested AND clauses
424 let #(and_sql_parts, and_params, new_offset) = case clause.and {
425 Some(and_clauses) -> {
426 list.fold(and_clauses, #([], [], mut_offset), fn(acc, nested_clause) {
427 let #(acc_sql, acc_params, acc_offset) = acc
428 case is_clause_empty(nested_clause) {
429 True -> acc
430 False -> {
431 let #(nested_sql_parts, nested_params, new_offset) =
432 build_where_clause_internal(
433 exec,
434 nested_clause,
435 use_table_prefix,
436 acc_offset,
437 )
438 // Wrap nested clause in parentheses if it has multiple parts
439 let nested_sql = case list.length(nested_sql_parts) {
440 0 -> ""
441 1 -> list.first(nested_sql_parts) |> result.unwrap("")
442 _ -> "(" <> string.join(nested_sql_parts, " AND ") <> ")"
443 }
444 let new_sql = case nested_sql {
445 "" -> acc_sql
446 _ -> [nested_sql, ..acc_sql]
447 }
448 #(new_sql, list.append(nested_params, acc_params), new_offset)
449 }
450 }
451 })
452 }
453 None -> #([], [], mut_offset)
454 }
455
456 let mut_sql_parts = list.append(mut_sql_parts, and_sql_parts)
457 let mut_params = list.append(mut_params, and_params)
458 let mut_offset = new_offset
459
460 // Handle nested OR clauses
461 let #(or_sql_parts, or_params, new_offset) = case clause.or {
462 Some(or_clauses) -> {
463 list.fold(or_clauses, #([], [], mut_offset), fn(acc, nested_clause) {
464 let #(acc_sql, acc_params, acc_offset) = acc
465 case is_clause_empty(nested_clause) {
466 True -> acc
467 False -> {
468 let #(nested_sql_parts, nested_params, new_offset) =
469 build_where_clause_internal(
470 exec,
471 nested_clause,
472 use_table_prefix,
473 acc_offset,
474 )
475 // Wrap nested clause in parentheses if it has multiple parts
476 let nested_sql = case list.length(nested_sql_parts) {
477 0 -> ""
478 1 -> list.first(nested_sql_parts) |> result.unwrap("")
479 _ -> "(" <> string.join(nested_sql_parts, " AND ") <> ")"
480 }
481 let new_sql = case nested_sql {
482 "" -> acc_sql
483 _ -> [nested_sql, ..acc_sql]
484 }
485 #(new_sql, list.append(nested_params, acc_params), new_offset)
486 }
487 }
488 })
489 }
490 None -> #([], [], mut_offset)
491 }
492
493 // If we have OR parts, wrap them in parentheses and join with OR
494 let #(final_sql_parts, final_params, final_offset) = case
495 list.length(or_sql_parts)
496 {
497 0 -> #(mut_sql_parts, mut_params, new_offset)
498 _ -> {
499 // Reverse the OR parts since we built them backwards
500 let reversed_or = list.reverse(or_sql_parts)
501 let or_combined = "(" <> string.join(reversed_or, " OR ") <> ")"
502 #(
503 [or_combined, ..mut_sql_parts],
504 list.append(or_params, mut_params),
505 new_offset,
506 )
507 }
508 }
509
510 #(final_sql_parts, final_params, final_offset)
511}