justintsus-experiment Public
// A query as SQL for D1: a WHERE expression over the target's table (issues or pull_index, no alias)// and an ORDER BY list. Every value is bound: the SQL text holds only column names, operators and// the constants written here, and params carries the rest in placeholder order. The scope (the// repository and its organization) leads the expression at its top level, ANDed with every term, so// no term can widen it.//// Each term's positive form is an expression that is never NULL (EXISTS, IS, NOT NULL columns), so// NOT of it matches exactly the rows it does not: -author:x on pulls keeps a PR whose owner_id is NULL.import { Some, None, type Option } from "@tsus/runtime";import type { DateFilter, DateField, DateOp, SearchQuery, SortValue, StateValue, Target, Term, UserRef } from "./ast.tsus";import { DAY_MS, unitMs } from "./values.tsus";export type Param = string | number;export type SqlCtx = { readonly repoId: string; readonly orgId: string; readonly viewer: Option<string>; readonly now: number;};export type Sql = { readonly where: string; readonly params: readonly Param[]; readonly orderBy: string };type Frag = { readonly sql: string; readonly params: readonly Param[] };const frag = (sql: string, params: readonly Param[]): Frag => ({ sql, params });const NOTHING = frag("0", []);const EVERYTHING = frag("1", []);const negate = (f: Frag, negated: boolean): Frag => (negated ? frag(`NOT (${f.sql})`, f.params) : f);const tableOf = (t: Target): string => match (t) { "issues" => "issues", "pulls" => "pull_index", };// --- people// A user reference compared with the column `col` (NOT NULL), or None when it names nobody: @me with// no one signed in. An agent's slug counts only in the repository's organization.const userIs = (u: UserRef, col: string, ctx: SqlCtx): Option<Frag> => match (u) { Me(_m) => ctx.viewer.map((id) => frag(`${col} = ?`, [id])), User(x) => Some(frag(`${col} = ?`, [x.id])), Agent(a) => Some(frag(`EXISTS (SELECT 1 FROM agents WHERE agents.org_id = ? AND agents.slug = ? AND agents.user_id = ${col})`, [ctx.orgId, a.slug])), };// On pulls an agent's PR also counts for its owner: author_id or owner_id (which may be NULL; IS keeps// the comparison 0 or 1).const pullAuthor = (u: UserRef, ctx: SqlCtx): Option<Frag> => match (u) { Me(_m) => ctx.viewer.map((id) => frag("(pull_index.author_id = ? OR pull_index.owner_id IS ?)", [id, id])), User(x) => Some(frag("(pull_index.author_id = ? OR pull_index.owner_id IS ?)", [x.id, x.id])), Agent(a) => Some(frag( "EXISTS (SELECT 1 FROM agents WHERE agents.org_id = ? AND agents.slug = ? AND (agents.user_id = pull_index.author_id OR agents.user_id IS pull_index.owner_id))", [ctx.orgId, a.slug], )), };const reviewRequested = (u: UserRef, ctx: SqlCtx): Option<Frag> => userIs(u, "pull_review_requests.user_id", ctx).map((who) => frag( `EXISTS (SELECT 1 FROM pull_review_requests WHERE pull_review_requests.repo_id = pull_index.repo_id AND pull_review_requests.number = pull_index.number AND ${who.sql})`, who.params, ));// Nobody matches nothing, and its negation everything.const person = (f: Option<Frag>, negated: boolean): Frag => negate(f.unwrapOr(NOTHING), negated);// --- datesconst cmp = (col: string, op: string, at: number): Frag => frag(`${col} ${op} ?`, [at]);const both = (a: Frag, b: Frag): Frag => frag(`(${a.sql} AND ${b.sql})`, [...a.params, ...b.params]);// An absolute comparison with a day D: > and <= move to the end of D.const dayCompare = (col: string, op: DateOp, start: number): Frag => match (op) { ">" => cmp(col, ">=", start + DAY_MS), ">=" => cmp(col, ">=", start), "<" => cmp(col, "<", start), "<=" => cmp(col, "<", start + DAY_MS), };// An age of N units against T = now - N units: younger (<) is after T, older (>) before it.const ageCompare = (col: string, op: DateOp, at: number): Frag => match (op) { "<" => cmp(col, ">", at), "<=" => cmp(col, ">=", at), ">" => cmp(col, "<", at), ">=" => cmp(col, "<=", at), };const dateSql = (col: string, f: DateFilter, ctx: SqlCtx): Frag => match (f) { On(x) => both(cmp(col, ">=", x.day.start), cmp(col, "<", x.day.start + DAY_MS)), Compare(x) => dayCompare(col, x.op, x.day.start), Between(x) => match (x.from) { Some(a) => match (x.to) { Some(b) => both(cmp(col, ">=", a.start), cmp(col, "<", b.start + DAY_MS)), None => cmp(col, ">=", a.start), }, None => match (x.to) { Some(b) => cmp(col, "<", b.start + DAY_MS), None => EVERYTHING, }, }, Age(x) => ageCompare(col, x.op, ctx.now - x.count * unitMs(x.unit)), };const dateColumn = (table: string, f: DateField): string => match (f) { "created" => `${table}.created_at`, "updated" => `${table}.updated_at`, };// --- text// A substring with LIKE's wildcards and the escape character itself matched literally.const likePattern = (s: string): string => `%${s.replace(/[\\%_]/g, (c) => `\\${c}`)}%`;const textSql = (t: Target, s: string): Frag => match (t) { "issues" => frag("(issues.title LIKE ? ESCAPE '\\' OR issues.body LIKE ? ESCAPE '\\')", [likePattern(s), likePattern(s)]), "pulls" => frag("pull_index.title LIKE ? ESCAPE '\\'", [likePattern(s)]), };// --- terms// is:closed on pulls is closed or merged, as on GitHub; is:draft is a draft in any state.const stateSql = (t: Target, s: StateValue): Frag => match (s) { "open" => frag(`${tableOf(t)}.state = 'open'`, []), "closed" => t === "pulls" ? frag("pull_index.state IN ('closed', 'merged')", []) : frag("issues.state = 'closed'", []), "merged" => frag(`${tableOf(t)}.state = 'merged'`, []), "draft" => frag(`${tableOf(t)}.draft = 1`, []), };const condition = (t: Target, term: Term, ctx: SqlCtx): Frag => match (term) { Is(x) => negate(stateSql(t, x.state), x.negated), Label(x) => negate(frag("EXISTS (SELECT 1 FROM json_each(issues.labels) WHERE json_each.value = ?)", [x.label]), x.negated), NoLabel(_x) => frag("json_array_length(issues.labels) = 0", []), Author(x) => person(t === "pulls" ? pullAuthor(x.user, ctx) : userIs(x.user, "issues.author_id", ctx), x.negated), ReviewRequested(x) => person(reviewRequested(x.user, ctx), x.negated), Sort(_x) => EVERYTHING, Date(x) => dateSql(dateColumn(tableOf(t), x.field), x.filter, ctx), Number(x) => frag(`${tableOf(t)}.number = ?`, [x.number]), Text(x) => negate(textSql(t, x.text), x.negated), };// A term's condition, or None for one that only orders (sort:).const termSql = (t: Target, term: Term, ctx: SqlCtx): Option<Frag> => match (term) { Sort(_x) => None, _ => Some(condition(t, term, ctx)), };const sortTerm = (term: Term): Option<SortValue> => match (term) { Sort(x) => Some(x.sort), _ => None, };// The query's sort: (the parser keeps at most one), created-desc by default.const sortOf = (terms: readonly Term[]): SortValue => { for (const term of terms) { const s = sortTerm(term); if (s.isSome()) return s.value; } return "created-desc";};// The tie-break on number runs in the same direction.const orderOf = (s: SortValue): string => match (s) { "created-desc" => "created_at DESC, number DESC", "created-asc" => "created_at ASC, number ASC", "updated-desc" => "updated_at DESC, number DESC", "updated-asc" => "updated_at ASC, number ASC", "comments-desc" => "comments DESC, number DESC", "comments-asc" => "comments ASC, number ASC", };export const toSql = (q: SearchQuery, ctx: SqlCtx): Sql => { const table = tableOf(q.target); const terms: Frag[] = []; for (const term of q.terms) { const f = termSql(q.target, term, ctx); if (f.isSome()) terms.push(f.value); } const scope = `${table}.repo_id = ? AND ${table}.org_id = ?`; return { where: [scope, ...terms.map((f) => `(${f.sql})`)].join(" AND "), params: [ctx.repoId, ctx.orgId, ...terms.flatMap((f) => f.params)], orderBy: orderOf(sortOf(q.terms)), };};