Sign in

flagon-io/g1t

public

Where people and agents ship software together. The open-source git platform for the whole job: issues, agents, checks and deploys to the edge.

gitagentscloudflarerustforgemcp

g1t/services/search/src/sql.rs

580 lines22,497 bytesCodeBlame
1//! The SQL each search runs, built from a parsed query and its reader.
2//! Pure: what is asked of D1 is tested here, including that every query
3//! of repository content carries the visibility check.
4
5use serde::Serialize;
6
7use g1t_contracts::search::COUNT_CAP;
8
9use crate::query::{Filter, ItemKind, ItemState, Query, Visibility, path_pattern};
10use crate::visibility::{Reader, clause};
11
12/// A value bound to a `?`.
13#[derive(Clone, Debug, PartialEq, Serialize)]
14#[serde(untagged)]
15pub enum Param {
16 Text(String),
17 Int(i64),
18 Null,
19}
20
21impl Param {
22 /// Text, or null when there is none.
23 pub fn opt(value: Option<&str>) -> Param {
24 value.map_or(Param::Null, |text| Param::Text(text.to_owned()))
25 }
26}
27
28impl From<&str> for Param {
29 fn from(value: &str) -> Param {
30 Param::Text(value.to_owned())
31 }
32}
33
34impl From<String> for Param {
35 fn from(value: String) -> Param {
36 Param::Text(value)
37 }
38}
39
40/// A statement and its values, in order.
41#[derive(Clone, Debug, Default, PartialEq, Serialize)]
42pub struct Sql {
43 pub text: String,
44 pub params: Vec<Param>,
45}
46
47/// Conditions joined with AND, each with its values.
48#[derive(Default)]
49struct Where {
50 parts: Vec<String>,
51 params: Vec<Param>,
52}
53
54impl Where {
55 fn add(&mut self, condition: impl Into<String>, params: Vec<Param>) {
56 self.parts.push(condition.into());
57 self.params.extend(params);
58 }
59
60 fn text(&self) -> String {
61 if self.parts.is_empty() { "1".to_owned() } else { self.parts.join(" AND ") }
62 }
63}
64
65fn json(values: &[String]) -> Param {
66 Param::Text(serde_json::to_string(values).unwrap_or_else(|_| "[]".into()))
67}
68
69/// `column IN (…)` for kept values, `NOT IN` for left-out ones.
70fn in_list(filter: &Filter, column: &str, out: &mut Where) {
71 if !filter.include.is_empty() {
72 out.add(format!("{column} IN (SELECT value FROM json_each(?))"), vec![json(&filter.include)]);
73 }
74 if !filter.exclude.is_empty() {
75 out.add(format!("COALESCE({column}, '') NOT IN (SELECT value FROM json_each(?))"), vec![json(&filter.exclude)]);
76 }
77}
78
79/// What every query of repository content starts with: the visibility
80/// check, then `repo:`, `org:` and `is:public`/`is:private`.
81fn scope(query: &Query, reader: &Reader, alias: &str) -> Where {
82 let mut out = Where::default();
83 out.add(clause(alias), vec![Param::Text(reader.members_json())]);
84 in_list(&query.repos, &format!("({alias}.namespace || '/' || {alias}.name)"), &mut out);
85 in_list(&query.owners, &format!("{alias}.namespace"), &mut out);
86 match query.visibility {
87 Some(Visibility::Public) => out.add(format!("{alias}.private = 0"), vec![]),
88 Some(Visibility::Private) => out.add(format!("{alias}.private = 1"), vec![]),
89 None => {}
90 }
91 out
92}
93
94/// A page: `LIMIT ? OFFSET ?`.
95#[derive(Clone, Copy, Debug)]
96pub struct Page {
97 pub limit: u32,
98 pub offset: u32,
99}
100
101fn paged(mut sql: Sql, page: Page) -> Sql {
102 sql.text.push_str(" LIMIT ? OFFSET ?");
103 sql.params.push(Param::Int(i64::from(page.limit)));
104 sql.params.push(Param::Int(i64::from(page.offset)));
105 sql
106}
107
108/// How many rows `select` returns, stopping at one past [`COUNT_CAP`].
109pub fn count(select: &Sql) -> Sql {
110 Sql {
111 text: format!("SELECT count(*) AS n FROM ({} LIMIT {})", select.text, COUNT_CAP + 1),
112 params: select.params.clone(),
113 }
114}
115
116/// The words of a query as one lowercase string, for an exact-name boost.
117fn exact(query: &Query) -> String {
118 query.positive().map(|term| term.text.to_lowercase()).collect::<Vec<_>>().join(" ")
119}
120
121// ---- Repositories ----------------------------------------------------------
122
123const REPO_COLUMNS: &str = "r.repo_id, r.namespace, r.name, r.description, r.topics, r.language, r.private, r.pushed_at, r.created_at";
124
125/// Repositories matching the query, without order or page.
126fn repos_select(query: &Query, reader: &Reader) -> (Sql, bool) {
127 let mut filters = scope(query, reader, "r");
128 in_list(&query.languages, "r.language", &mut filters);
129 match query.prose_match() {
130 Some(expression) => {
131 let mut params = vec![Param::Text(expression)];
132 params.extend(filters.params.clone());
133 (
134 Sql {
135 text: format!(
136 "SELECT {REPO_COLUMNS}, snippet(repos_fts, 4, '', '', '…', 24) AS readme \
137 FROM repos_fts JOIN repos r ON r.rid = repos_fts.rowid \
138 WHERE repos_fts MATCH ? AND {}",
139 filters.text()
140 ),
141 params,
142 },
143 true,
144 )
145 }
146 None => (
147 Sql {
148 text: format!("SELECT {REPO_COLUMNS}, NULL AS readme FROM repos r WHERE {}", filters.text()),
149 params: filters.params,
150 },
151 false,
152 ),
153 }
154}
155
156pub fn repos_count(query: &Query, reader: &Reader) -> Sql {
157 count(&repos_select(query, reader).0)
158}
159
160pub fn repos_page(query: &Query, reader: &Reader, page: Page) -> Sql {
161 let (mut sql, text) = repos_select(query, reader);
162 if text {
163 let name = exact(query);
164 sql.text.push_str(
165 " ORDER BY (r.name = ? OR (r.namespace || '/' || r.name) = ?) DESC, \
166 bm25(repos_fts, 3.0, 10.0, 4.0, 6.0, 1.0), r.pushed_at DESC",
167 );
168 sql.params.push(Param::Text(name.clone()));
169 sql.params.push(Param::Text(name));
170 } else {
171 sql.text.push_str(" ORDER BY COALESCE(r.pushed_at, r.created_at) DESC, r.rid DESC");
172 }
173 paged(sql, page)
174}
175
176// ---- Code ------------------------------------------------------------------
177
178/// How a code search reads the index.
179#[derive(Clone, Debug, PartialEq)]
180pub enum CodePlan {
181 /// Through the trigram index; `short` words are matched directly.
182 Index { expression: String, short: Vec<String> },
183 /// Words too short for the index, in named repositories only.
184 Scan { short: Vec<String> },
185 /// No words: the files of named repositories, by path.
186 Files,
187}
188
189/// How to search code for a query, or why it cannot be.
190pub fn code_plan(query: &Query) -> Result<CodePlan, &'static str> {
191 let (expression, short) = query.code_match();
192 let scoped = !query.repos.include.is_empty();
193 match expression {
194 Some(expression) => Ok(CodePlan::Index { expression, short }),
195 None if !scoped => Err("Code search needs a word of at least three characters, or repo:owner/name."),
196 None if short.is_empty() => Ok(CodePlan::Files),
197 None => Ok(CodePlan::Scan { short }),
198 }
199}
200
201fn code_filters(query: &Query, reader: &Reader) -> Where {
202 let mut filters = scope(query, reader, "r");
203 in_list(&query.languages, "fl.language", &mut filters);
204 if !query.paths.include.is_empty() {
205 let any = vec!["fl.path LIKE ? ESCAPE '\\'"; query.paths.include.len()].join(" OR ");
206 filters.add(format!("({any})"), query.paths.include.iter().map(|p| Param::Text(path_pattern(p))).collect());
207 }
208 for path in &query.paths.exclude {
209 filters.add("fl.path NOT LIKE ? ESCAPE '\\'", vec![Param::Text(path_pattern(path))]);
210 }
211 filters
212}
213
214fn short_words(short: &[String], column: &str, out: &mut Where) {
215 for word in short {
216 out.add(format!("instr(lower({column}), ?) > 0"), vec![Param::Text(word.to_lowercase())]);
217 }
218}
219
220/// Matching files, one row each (`fid`, `score`), without order or page.
221fn code_select(plan: &CodePlan, query: &Query, reader: &Reader) -> Sql {
222 let mut filters = code_filters(query, reader);
223 match plan {
224 CodePlan::Index { expression, short } => {
225 short_words(short, "c.content", &mut filters);
226 let mut params = vec![Param::Text(expression.clone())];
227 params.extend(filters.params.clone());
228 Sql {
229 // bm25() cannot be called inside an aggregate; the rank
230 // column can, weighted as the migration sets it.
231 text: format!(
232 "SELECT c.fid AS fid, MIN(chunks_fts.rank) AS score, fl.path AS path FROM chunks_fts JOIN chunks c ON c.cid = chunks_fts.rowid JOIN files fl ON fl.fid = c.fid JOIN repos r ON r.repo_id = fl.repo_id WHERE chunks_fts MATCH ? AND {} GROUP BY c.fid",
233 filters.text()
234 ),
235 params,
236 }
237 }
238 CodePlan::Scan { short } => {
239 short_words(short, "c.content", &mut filters);
240 Sql {
241 text: format!(
242 "SELECT c.fid AS fid, 0 AS score, fl.path AS path \
243 FROM chunks c JOIN files fl ON fl.fid = c.fid JOIN repos r ON r.repo_id = fl.repo_id \
244 WHERE {} GROUP BY c.fid",
245 filters.text()
246 ),
247 params: filters.params,
248 }
249 }
250 CodePlan::Files => {
251 filters.add("fl.skipped IS NULL", vec![]);
252 Sql {
253 text: format!(
254 "SELECT fl.fid AS fid, 0 AS score, fl.path AS path \
255 FROM files fl JOIN repos r ON r.repo_id = fl.repo_id WHERE {}",
256 filters.text()
257 ),
258 params: filters.params,
259 }
260 }
261 }
262}
263
264pub fn code_count(plan: &CodePlan, query: &Query, reader: &Reader) -> Sql {
265 count(&code_select(plan, query, reader))
266}
267
268pub fn code_page(plan: &CodePlan, query: &Query, reader: &Reader, page: Page) -> Sql {
269 let mut sql = code_select(plan, query, reader);
270 sql.text.push_str(" ORDER BY score, path");
271 paged(sql, page)
272}
273
274/// The files of a page: where each is and in which repository.
275pub fn code_files(fids: &[i64]) -> Sql {
276 Sql {
277 text: "SELECT fl.fid, fl.path, fl.language, r.repo_id, r.namespace, r.name, r.default_branch, r.private \
278 FROM files fl JOIN repos r ON r.repo_id = fl.repo_id \
279 WHERE fl.fid IN (SELECT value FROM json_each(?))"
280 .to_owned(),
281 params: vec![Param::Text(serde_json::to_string(fids).unwrap_or_else(|_| "[]".into()))],
282 }
283}
284
285/// The pieces of a page's files that matched, at most `limit`.
286pub fn code_pieces(plan: &CodePlan, fids: &[i64], limit: u32) -> Sql {
287 let ids = Param::Text(serde_json::to_string(fids).unwrap_or_else(|_| "[]".into()));
288 match plan {
289 CodePlan::Index { expression, .. } => Sql {
290 text: "SELECT c.fid, c.start_line, c.content FROM chunks_fts JOIN chunks c ON c.cid = chunks_fts.rowid \
291 WHERE chunks_fts MATCH ? AND c.fid IN (SELECT value FROM json_each(?)) \
292 ORDER BY c.fid, c.start_line LIMIT ?"
293 .to_owned(),
294 params: vec![Param::Text(expression.clone()), ids, Param::Int(i64::from(limit))],
295 },
296 CodePlan::Scan { short } => {
297 let mut filters = Where::default();
298 filters.add("c.fid IN (SELECT value FROM json_each(?))", vec![ids]);
299 short_words(short, "c.content", &mut filters);
300 let mut params = filters.params;
301 params.push(Param::Int(i64::from(limit)));
302 Sql {
303 text: format!(
304 "SELECT c.fid, c.start_line, c.content FROM chunks c WHERE {} ORDER BY c.fid, c.start_line LIMIT ?",
305 filters.parts.join(" AND ")
306 ),
307 params,
308 }
309 }
310 CodePlan::Files => first_pieces(fids),
311 }
312}
313
314/// The first piece of each file, for files that matched by name.
315pub fn first_pieces(fids: &[i64]) -> Sql {
316 Sql {
317 text: "SELECT c.fid, c.start_line, c.content FROM chunks c \
318 WHERE c.fid IN (SELECT value FROM json_each(?)) AND c.start_line = 1"
319 .to_owned(),
320 params: vec![Param::Text(serde_json::to_string(fids).unwrap_or_else(|_| "[]".into()))],
321 }
322}
323
324// ---- Issues and pull requests ----------------------------------------------
325
326const ITEM_COLUMNS: &str = "i.kind, i.number, i.title, i.state, i.status, i.author, i.labels, i.updated_at, \
327 r.repo_id, r.namespace, r.name, r.private";
328
329fn items_select(query: &Query, reader: &Reader, kind: ItemKind) -> (Sql, bool) {
330 let mut filters = scope(query, reader, "r");
331 filters.add("i.kind = ?", vec![Param::Text(if kind == ItemKind::Issue { "issue" } else { "pull" }.into())]);
332 match query.state {
333 Some(ItemState::Open) => filters.add("i.state = 'open'", vec![]),
334 Some(ItemState::Closed) => filters.add("i.state = 'closed'", vec![]),
335 Some(ItemState::Merged) => filters.add("i.status = 'merged'", vec![]),
336 Some(ItemState::Draft) => filters.add("i.status = 'draft'", vec![]),
337 None => {}
338 }
339 in_list(&query.authors, "i.author", &mut filters);
340 for label in &query.labels.include {
341 filters.add("instr(i.labels, ?) > 0", vec![Param::Text(format!("|{label}|"))]);
342 }
343 for label in &query.labels.exclude {
344 filters.add("instr(i.labels, ?) = 0", vec![Param::Text(format!("|{label}|"))]);
345 }
346 match query.prose_match() {
347 Some(expression) => {
348 let mut params = vec![Param::Text(expression)];
349 params.extend(filters.params.clone());
350 (
351 Sql {
352 text: format!(
353 "SELECT {ITEM_COLUMNS}, snippet(items_fts, 1, '', '', '…', 40) AS body \
354 FROM items_fts JOIN items i ON i.iid = items_fts.rowid JOIN repos r ON r.repo_id = i.repo_id \
355 WHERE items_fts MATCH ? AND {}",
356 filters.text()
357 ),
358 params,
359 },
360 true,
361 )
362 }
363 None => (
364 Sql {
365 text: format!(
366 "SELECT {ITEM_COLUMNS}, substr(i.body, 1, 400) AS body \
367 FROM items i JOIN repos r ON r.repo_id = i.repo_id WHERE {}",
368 filters.text()
369 ),
370 params: filters.params,
371 },
372 false,
373 ),
374 }
375}
376
377pub fn items_count(query: &Query, reader: &Reader, kind: ItemKind) -> Sql {
378 count(&items_select(query, reader, kind).0)
379}
380
381pub fn items_page(query: &Query, reader: &Reader, kind: ItemKind, page: Page) -> Sql {
382 let (mut sql, text) = items_select(query, reader, kind);
383 sql.text.push_str(if text {
384 " ORDER BY bm25(items_fts, 4.0, 1.0), i.updated_at DESC"
385 } else {
386 " ORDER BY i.updated_at DESC, i.iid DESC"
387 });
388 paged(sql, page)
389}
390
391// ---- People ----------------------------------------------------------------
392
393/// People and workspaces; only words narrow them.
394pub fn people_select(query: &Query) -> Option<Sql> {
395 let expression = query.prose_match()?;
396 Some(Sql {
397 text: "SELECT p.kind, p.slug, p.name, p.bio, p.avatar FROM people_fts JOIN people p ON p.pid = people_fts.rowid \
398 WHERE people_fts MATCH ?"
399 .to_owned(),
400 params: vec![Param::Text(expression)],
401 })
402}
403
404pub fn people_page(query: &Query, page: Page) -> Option<Sql> {
405 let mut sql = people_select(query)?;
406 sql.text.push_str(" ORDER BY (p.slug = ?) DESC, bm25(people_fts, 10.0, 5.0, 1.0), p.kind DESC");
407 sql.params.push(Param::Text(exact(query)));
408 Some(paged(sql, page))
409}
410
411// ---- Explore ---------------------------------------------------------------
412
413/// Public repositories, recently active or newly made.
414pub fn explore(newest: bool, language: Option<&str>, topic: Option<&str>, page: Page) -> Sql {
415 let mut filters = Where::default();
416 filters.add("r.private = 0", vec![]);
417 if let Some(language) = language {
418 filters.add("r.language = ?", vec![Param::Text(language.to_owned())]);
419 }
420 if let Some(topic) = topic {
421 filters.add("r.repo_id IN (SELECT repo_id FROM repo_topics WHERE topic = ?)", vec![Param::Text(topic.to_owned())]);
422 }
423 let order = if newest { "r.created_at DESC" } else { "COALESCE(r.pushed_at, r.created_at) DESC" };
424 paged(
425 Sql {
426 text: format!("SELECT {REPO_COLUMNS} FROM repos r WHERE {} ORDER BY {order}, r.rid DESC", filters.text()),
427 params: filters.params,
428 },
429 page,
430 )
431}
432
433pub const LANGUAGE_FACETS: &str = "SELECT language AS name, count(*) AS count FROM repos \
434 WHERE private = 0 AND language IS NOT NULL GROUP BY language ORDER BY count DESC, name LIMIT 24";
435
436pub const TOPIC_FACETS: &str = "SELECT t.topic AS name, count(*) AS count FROM repo_topics t \
437 JOIN repos r ON r.repo_id = t.repo_id WHERE r.private = 0 GROUP BY t.topic ORDER BY count DESC, name LIMIT 30";
438
439#[cfg(test)]
440mod tests {
441 use g1t_contracts::{Membership, User};
442
443 use super::*;
444
445 fn reader(workspaces: &[&str]) -> Reader {
446 Reader::of(&Some(User {
447 workspaces: workspaces.iter().map(|w| Membership::member(*w)).collect(),
448 ..User::default()
449 }))
450 }
451
452 const PAGE: Page = Page { limit: 20, offset: 0 };
453
454 fn placeholders(sql: &Sql) -> usize {
455 sql.text.matches('?').count()
456 }
457
458 #[test]
459 fn every_query_of_repository_content_checks_visibility() {
460 let query = Query::parse("parse repo:acme/web");
461 let reader = reader(&["acme"]);
462 let plan = code_plan(&query).unwrap();
463 for sql in [
464 repos_page(&query, &reader, PAGE),
465 repos_count(&query, &reader),
466 code_page(&plan, &query, &reader, PAGE),
467 code_count(&plan, &query, &reader),
468 items_page(&query, &reader, ItemKind::Issue, PAGE),
469 items_count(&query, &reader, ItemKind::Pull),
470 ] {
471 assert!(sql.text.contains("r.private = 0 OR r.namespace IN"), "{}", sql.text);
472 assert!(sql.params.contains(&Param::Text(r#"["acme"]"#.into())));
473 assert_eq!(placeholders(&sql), sql.params.len(), "{}", sql.text);
474 }
475 }
476
477 #[test]
478 fn signed_out_readers_bind_no_workspaces() {
479 let sql = repos_page(&Query::parse("x"), &Reader::of(&None), PAGE);
480 assert!(sql.params.contains(&Param::Text("[]".into())));
481 }
482
483 #[test]
484 fn qualifiers_become_conditions() {
485 let query = Query::parse("parse language:rust path:*.rs -path:tests/ org:acme");
486 let plan = code_plan(&query).unwrap();
487 let sql = code_page(&plan, &query, &reader(&[]), PAGE);
488 assert!(sql.text.contains("fl.language IN"));
489 assert!(sql.text.contains("fl.path LIKE ? ESCAPE"));
490 assert!(sql.text.contains("fl.path NOT LIKE ?"));
491 assert!(sql.text.contains("r.namespace IN"));
492 assert!(sql.params.contains(&Param::Text("%.rs".into())));
493 assert!(sql.params.contains(&Param::Text("%tests/%".into())));
494 assert_eq!(placeholders(&sql), sql.params.len());
495 }
496
497 #[test]
498 fn issue_qualifiers_become_conditions() {
499 let query = Query::parse("crash is:closed author:ana label:bug -label:wontfix");
500 let sql = items_page(&query, &reader(&[]), ItemKind::Issue, PAGE);
501 assert!(sql.text.contains("i.state = 'closed'"));
502 assert!(sql.text.contains("i.author IN"));
503 assert!(sql.params.contains(&Param::Text("|bug|".into())));
504 assert!(sql.text.contains("instr(i.labels, ?) = 0"));
505 assert_eq!(placeholders(&sql), sql.params.len());
506 }
507
508 #[test]
509 fn code_needs_three_characters_unless_scoped() {
510 assert!(code_plan(&Query::parse("fn")).is_err());
511 assert_eq!(code_plan(&Query::parse("fn repo:a/b")).unwrap(), CodePlan::Scan { short: vec!["fn".into()] });
512 assert_eq!(code_plan(&Query::parse("repo:a/b path:src")).unwrap(), CodePlan::Files);
513 assert!(matches!(code_plan(&Query::parse("fn main")).unwrap(), CodePlan::Index { .. }));
514 }
515
516 #[test]
517 fn every_plan_binds_what_it_asks() {
518 let reader = reader(&[]);
519 for text in ["fn repo:a/b", "repo:a/b path:src", "fn main"] {
520 let query = Query::parse(text);
521 let plan = code_plan(&query).unwrap();
522 for sql in [code_page(&plan, &query, &reader, PAGE), code_count(&plan, &query, &reader), code_pieces(&plan, &[1, 2], 10)] {
523 assert_eq!(placeholders(&sql), sql.params.len(), "{text}: {}", sql.text);
524 }
525 }
526 }
527
528 #[test]
529 fn counts_stop_past_the_cap() {
530 let sql = repos_count(&Query::parse("x"), &reader(&[]));
531 assert!(sql.text.ends_with(&format!("LIMIT {})", COUNT_CAP + 1)));
532 }
533
534 #[test]
535 fn explore_is_public_only() {
536 let sql = explore(false, Some("rust"), Some("cli"), PAGE);
537 assert!(sql.text.contains("r.private = 0"));
538 assert_eq!(placeholders(&sql), sql.params.len());
539 }
540
541 /// Writes every kind of statement to stdout as JSON, to run against
542 /// SQLite with the migrations: `cargo test -p g1t-search dump_sql --
543 /// --ignored --nocapture`.
544 #[test]
545 #[ignore]
546 fn dump_sql() {
547 let reader = reader(&["acme"]);
548 let mut out = Vec::new();
549 for text in [
550 "parse",
551 "parse repo:acme/web",
552 "\"fn main\" language:rust path:src -path:tests",
553 "fn repo:acme/web",
554 "repo:acme/web path:*.rs",
555 "crash is:open label:bug author:ana",
556 "language:rust",
557 "ana",
558 ] {
559 let query = Query::parse(text);
560 out.push(repos_page(&query, &reader, PAGE));
561 out.push(repos_count(&query, &reader));
562 if let Ok(plan) = code_plan(&query) {
563 out.push(code_page(&plan, &query, &reader, PAGE));
564 out.push(code_count(&plan, &query, &reader));
565 out.push(code_pieces(&plan, &[1, 2], 10));
566 }
567 out.push(items_page(&query, &reader, ItemKind::Issue, PAGE));
568 out.push(items_count(&query, &reader, ItemKind::Pull));
569 if let Some(sql) = people_page(&query, PAGE) {
570 out.push(sql);
571 }
572 }
573 out.push(code_files(&[1, 2]));
574 out.push(first_pieces(&[1, 2]));
575 out.push(explore(true, Some("rust"), Some("cli"), PAGE));
576 out.push(Sql { text: LANGUAGE_FACETS.into(), params: vec![] });
577 out.push(Sql { text: TOPIC_FACETS.into(), params: vec![] });
578 println!("{}", serde_json::to_string(&out).unwrap());
579 }
580}