trezy.codes
forked from
slices.network/quickslice
Auto-indexing service and GraphQL API for AT Protocol Records
chadtmiller.com
1import database/executor.{type Executor, Text}
2import database/queries/aggregates
3import database/queries/where_clause
4import database/types
5import gleam/dict
6import gleam/list
7import gleam/option.{None, Some}
8import gleeunit
9import gleeunit/should
10import test_helpers
11
12pub fn main() {
13 gleeunit.main()
14}
15
16// Helper to create an in-memory test database using existing database functions
17fn setup_test_db() -> Executor {
18 let assert Ok(exec) = test_helpers.create_test_db()
19
20 // Use existing database creation functions
21 let assert Ok(_) = test_helpers.create_record_table(exec)
22 let assert Ok(_) = test_helpers.create_actor_table(exec)
23
24 exec
25}
26
27// Helper to insert test records
28fn insert_test_record(
29 exec: Executor,
30 uri: String,
31 collection: String,
32 json: String,
33 did: String,
34) {
35 let assert Ok(_) =
36 executor.exec(
37 exec,
38 "INSERT INTO record (uri, cid, did, collection, json, indexed_at)
39 VALUES (?, 'cid123', ?, ?, ?, datetime('now'))",
40 [Text(uri), Text(did), Text(collection), Text(json)],
41 )
42 Nil
43}
44
45pub fn test_simple_group_by_single_field() {
46 let exec = setup_test_db()
47
48 // Insert test data with different statuses
49 insert_test_record(
50 exec,
51 "at://did1/xyz.statusphere.status/1",
52 "xyz.statusphere.status",
53 "{\"status\": \"👍\", \"text\": \"Great!\"}",
54 "did:plc:user1",
55 )
56 insert_test_record(
57 exec,
58 "at://did2/xyz.statusphere.status/2",
59 "xyz.statusphere.status",
60 "{\"status\": \"👍\", \"text\": \"Awesome!\"}",
61 "did:plc:user2",
62 )
63 insert_test_record(
64 exec,
65 "at://did3/xyz.statusphere.status/3",
66 "xyz.statusphere.status",
67 "{\"status\": \"👎\", \"text\": \"Not good\"}",
68 "did:plc:user3",
69 )
70
71 // Aggregate by status field
72 let assert Ok(results) =
73 aggregates.get_aggregated_records(
74 exec,
75 "xyz.statusphere.status",
76 [types.SimpleField("status")],
77 None,
78 True,
79 // order by count desc
80 10,
81 )
82
83 // Should have 2 groups (👍 and 👎)
84 list.length(results) |> should.equal(2)
85
86 // First group should be 👍 with count 2
87 let assert [first, second] = results
88 first.count |> should.equal(2)
89 second.count |> should.equal(1)
90}
91
92pub fn test_group_by_multiple_fields() {
93 let exec = setup_test_db()
94
95 // Insert test data
96 insert_test_record(
97 exec,
98 "at://did1/xyz.statusphere.status/1",
99 "xyz.statusphere.status",
100 "{\"status\": \"👍\", \"category\": \"work\"}",
101 "did:plc:user1",
102 )
103 insert_test_record(
104 exec,
105 "at://did2/xyz.statusphere.status/2",
106 "xyz.statusphere.status",
107 "{\"status\": \"👍\", \"category\": \"personal\"}",
108 "did:plc:user2",
109 )
110 insert_test_record(
111 exec,
112 "at://did3/xyz.statusphere.status/3",
113 "xyz.statusphere.status",
114 "{\"status\": \"👍\", \"category\": \"work\"}",
115 "did:plc:user3",
116 )
117
118 // Aggregate by status and category
119 let assert Ok(results) =
120 aggregates.get_aggregated_records(
121 exec,
122 "xyz.statusphere.status",
123 [types.SimpleField("status"), types.SimpleField("category")],
124 None,
125 True,
126 10,
127 )
128
129 // Should have 2 groups
130 list.length(results) |> should.equal(2)
131
132 // Check that we get the expected counts
133 let assert [first, ..] = results
134 first.count |> should.equal(2)
135}
136
137pub fn test_group_by_with_where_filter() {
138 let exec = setup_test_db()
139
140 // Insert test data
141 insert_test_record(
142 exec,
143 "at://did1/xyz.statusphere.status/1",
144 "xyz.statusphere.status",
145 "{\"status\": \"👍\", \"active\": \"true\"}",
146 "did:plc:user1",
147 )
148 insert_test_record(
149 exec,
150 "at://did2/xyz.statusphere.status/2",
151 "xyz.statusphere.status",
152 "{\"status\": \"👍\", \"active\": \"false\"}",
153 "did:plc:user2",
154 )
155 insert_test_record(
156 exec,
157 "at://did3/xyz.statusphere.status/3",
158 "xyz.statusphere.status",
159 "{\"status\": \"👎\", \"active\": \"true\"}",
160 "did:plc:user3",
161 )
162
163 // Create WHERE clause to filter only active records
164 let where_condition =
165 where_clause.WhereCondition(
166 eq: Some(Text("true")),
167 in_values: None,
168 contains: None,
169 gt: None,
170 gte: None,
171 lt: None,
172 lte: None,
173 is_null: None,
174 is_numeric: False,
175 )
176
177 let where_clause =
178 where_clause.WhereClause(
179 conditions: dict.from_list([#("active", where_condition)]),
180 and: None,
181 or: None,
182 )
183
184 // Aggregate by status with WHERE filter
185 let assert Ok(results) =
186 aggregates.get_aggregated_records(
187 exec,
188 "xyz.statusphere.status",
189 [types.SimpleField("status")],
190 Some(where_clause),
191 True,
192 10,
193 )
194
195 // Should have 2 groups, each with count 1 (only active records)
196 list.length(results) |> should.equal(2)
197 list.all(results, fn(r) { r.count == 1 }) |> should.be_true()
198}
199
200pub fn test_group_by_table_column() {
201 let exec = setup_test_db()
202
203 // Insert test data with different DIDs
204 insert_test_record(
205 exec,
206 "at://did1/xyz.statusphere.status/1",
207 "xyz.statusphere.status",
208 "{\"status\": \"👍\"}",
209 "did:plc:user1",
210 )
211 insert_test_record(
212 exec,
213 "at://did1/xyz.statusphere.status/2",
214 "xyz.statusphere.status",
215 "{\"status\": \"👍\"}",
216 "did:plc:user1",
217 )
218 insert_test_record(
219 exec,
220 "at://did2/xyz.statusphere.status/3",
221 "xyz.statusphere.status",
222 "{\"status\": \"👍\"}",
223 "did:plc:user2",
224 )
225
226 // Aggregate by DID (table column)
227 let assert Ok(results) =
228 aggregates.get_aggregated_records(
229 exec,
230 "xyz.statusphere.status",
231 [types.SimpleField("did")],
232 None,
233 True,
234 10,
235 )
236
237 // Should have 2 groups (2 different DIDs)
238 list.length(results) |> should.equal(2)
239
240 // First group should have count 2
241 let assert [first, ..] = results
242 first.count |> should.equal(2)
243}
244
245pub fn test_order_by_count_ascending() {
246 let exec = setup_test_db()
247
248 // Insert test data
249 insert_test_record(
250 exec,
251 "at://did1/xyz.statusphere.status/1",
252 "xyz.statusphere.status",
253 "{\"status\": \"👍\"}",
254 "did:plc:user1",
255 )
256 insert_test_record(
257 exec,
258 "at://did2/xyz.statusphere.status/2",
259 "xyz.statusphere.status",
260 "{\"status\": \"👎\"}",
261 "did:plc:user2",
262 )
263 insert_test_record(
264 exec,
265 "at://did3/xyz.statusphere.status/3",
266 "xyz.statusphere.status",
267 "{\"status\": \"👎\"}",
268 "did:plc:user3",
269 )
270
271 // Aggregate with ascending order
272 let assert Ok(results) =
273 aggregates.get_aggregated_records(
274 exec,
275 "xyz.statusphere.status",
276 [types.SimpleField("status")],
277 None,
278 False,
279 // order by count asc
280 10,
281 )
282
283 list.length(results) |> should.equal(2)
284
285 // First result should have count 1 (ascending order)
286 let assert [first, ..] = results
287 first.count |> should.equal(1)
288}
289
290pub fn test_limit() {
291 let exec = setup_test_db()
292
293 // Insert test data with many different statuses
294 insert_test_record(
295 exec,
296 "at://did1/xyz.statusphere.status/1",
297 "xyz.statusphere.status",
298 "{\"status\": \"A\"}",
299 "did:plc:user1",
300 )
301 insert_test_record(
302 exec,
303 "at://did2/xyz.statusphere.status/2",
304 "xyz.statusphere.status",
305 "{\"status\": \"B\"}",
306 "did:plc:user2",
307 )
308 insert_test_record(
309 exec,
310 "at://did3/xyz.statusphere.status/3",
311 "xyz.statusphere.status",
312 "{\"status\": \"C\"}",
313 "did:plc:user3",
314 )
315 insert_test_record(
316 exec,
317 "at://did4/xyz.statusphere.status/4",
318 "xyz.statusphere.status",
319 "{\"status\": \"D\"}",
320 "did:plc:user4",
321 )
322
323 // Aggregate with limit of 2
324 let assert Ok(results) =
325 aggregates.get_aggregated_records(
326 exec,
327 "xyz.statusphere.status",
328 [types.SimpleField("status")],
329 None,
330 True,
331 2,
332 )
333
334 // Should only return 2 results due to limit
335 list.length(results) |> should.equal(2)
336}
337
338pub fn test_date_truncation_day() {
339 let exec = setup_test_db()
340
341 // Insert records with indexed_at timestamps on different days
342 let assert Ok(_) =
343 executor.exec(
344 exec,
345 "INSERT INTO record (uri, cid, did, collection, json, indexed_at)
346 VALUES (?, 'cid1', 'did1', 'xyz.statusphere.status', '{}', '2024-01-15 10:30:00')",
347 [Text("at://did1/xyz.statusphere.status/1")],
348 )
349
350 let assert Ok(_) =
351 executor.exec(
352 exec,
353 "INSERT INTO record (uri, cid, did, collection, json, indexed_at)
354 VALUES (?, 'cid2', 'did2', 'xyz.statusphere.status', '{}', '2024-01-15 15:45:00')",
355 [Text("at://did2/xyz.statusphere.status/2")],
356 )
357
358 let assert Ok(_) =
359 executor.exec(
360 exec,
361 "INSERT INTO record (uri, cid, did, collection, json, indexed_at)
362 VALUES (?, 'cid3', 'did3', 'xyz.statusphere.status', '{}', '2024-01-16 09:00:00')",
363 [Text("at://did3/xyz.statusphere.status/3")],
364 )
365
366 // Aggregate by indexed_at truncated to day
367 let assert Ok(results) =
368 aggregates.get_aggregated_records(
369 exec,
370 "xyz.statusphere.status",
371 [types.TruncatedField("indexed_at", types.Day)],
372 None,
373 True,
374 10,
375 )
376
377 // Should have 2 groups (2 different days)
378 list.length(results) |> should.equal(2)
379
380 // One day should have 2 records
381 let assert [first, ..] = results
382 first.count |> should.equal(2)
383}
384
385pub fn test_empty_result() {
386 let exec = setup_test_db()
387
388 // No records inserted
389
390 // Try to aggregate
391 let assert Ok(results) =
392 aggregates.get_aggregated_records(
393 exec,
394 "xyz.statusphere.status",
395 [types.SimpleField("status")],
396 None,
397 True,
398 10,
399 )
400
401 // Should return empty list
402 list.length(results) |> should.equal(0)
403}