justintsus-experiment Public
// A parsed search query as SQL (search/api.ts): a WHERE expression and an ORDER BY list for the// target's D1 table (issues, or pull_index for pull requests), used as// SELECT ... FROM <table> WHERE ${where} AND <more> ORDER BY ${orderBy} LIMIT ?//// The contract, which nothing a person types can change:// - every value is bound: the text holds only the constants written here, each value (ids from ctx// included) is a ? placeholder, and params carries them in placeholder order;// - the scope comes first and stays: repo_id = ? AND org_id = ? at the top level, every term ANDed// after it in its own parentheses, so no OR reaches the top;// - a negated term matches exactly the rows its positive form does not: NOT IFNULL((...), 0), so a// NULL (an agent-less pull request's owner_id) counts as "does not match";// - ORDER BY comes from fixed column names and directions.import type { DateFilter, SearchQuery, SortValue, SqlCtx, SqlResult, SqlValue, Term, UserRef } from "./ast.ts";const HOUR_MS = 3_600_000;const DAY_MS = 86_400_000;const UNIT_MS = { h: HOUR_MS, d: DAY_MS, w: 7 * DAY_MS } as const;type Cond = { sql: string; params: SqlValue[] };const cond = (sql: string, ...params: SqlValue[]): Cond => ({ sql, params });const NOTHING = cond("0");// An issue's labels as a JSON array whatever is stored (json_each and json_array_length fail on text// that is not JSON; parseLabels reads such a row as no labels, and so does the search).const LABELS = "(CASE WHEN json_valid(issues.labels) THEN CASE json_type(issues.labels) WHEN 'array' THEN issues.labels ELSE '[]' END ELSE '[]' END)";export function likePattern(text: string): string { return `%${text.replace(/[\\%_]/g, (c) => `\\${c}`)}%`;}// Rows whose columns name this person. An agent's slug names the agent of the repository's own// organization only; an unknown slug, or @me signed out, names nobody.function userCond(u: UserRef, ctx: SqlCtx, table: "issues" | "pull_index", columns: readonly string[]): Cond { if (u.kind === "slug") { const cols = columns.map((c) => `${table}.${c}`).join(", "); return cond(`EXISTS (SELECT 1 FROM agents a WHERE a.org_id = ? AND a.slug = ? AND a.user_id IN (${cols}))`, ctx.orgId, u.slug); } const id = u.kind === "me" ? ctx.viewerId : u.id; if (typeof id !== "string") return NOTHING; return cond(columns.map((c) => `${table}.${c} = ?`).join(" OR "), ...columns.map(() => id));}function reviewCond(u: UserRef, ctx: SqlCtx): Cond { const base = "SELECT 1 FROM pull_review_requests r WHERE r.repo_id = pull_index.repo_id AND r.number = pull_index.number"; if (u.kind === "slug") return cond(`EXISTS (${base} AND r.user_id IN (SELECT a.user_id FROM agents a WHERE a.org_id = ? AND a.slug = ?))`, ctx.orgId, u.slug); const id = u.kind === "me" ? ctx.viewerId : u.id; if (typeof id !== "string") return NOTHING; return cond(`EXISTS (${base} AND r.user_id = ?)`, id);}function dateCond(column: string, f: DateFilter, now: number): Cond { switch (f.kind) { case "day": return cond(`${column} >= ? AND ${column} < ?`, f.day.start, f.day.start + DAY_MS); case "cmp": switch (f.op) { case ">": return cond(`${column} >= ?`, f.day.start + DAY_MS); case ">=": return cond(`${column} >= ?`, f.day.start); case "<": return cond(`${column} < ?`, f.day.start); case "<=": return cond(`${column} < ?`, f.day.start + DAY_MS); } break; case "range": { const parts: Cond[] = []; if (f.from) parts.push(cond(`${column} >= ?`, f.from.start)); if (f.to) parts.push(cond(`${column} < ?`, f.to.start + DAY_MS)); return cond(parts.map((p) => p.sql).join(" AND "), ...parts.flatMap((p) => p.params)); } case "age": { // the age (now - t) against N units: t against T = now - N units const t = now - f.n * UNIT_MS[f.unit]; const op = f.op === "<" ? ">" : f.op === "<=" ? ">=" : f.op === ">" ? "<" : "<="; return cond(`${column} ${op} ?`, t); } } return NOTHING;}function termCond(t: Term, q: SearchQuery, ctx: SqlCtx): Cond | null { const issues = q.target === "issues"; const table = issues ? "issues" : "pull_index"; switch (t.kind) { case "is": switch (t.value) { case "open": return cond("state = 'open'"); case "closed": return cond(issues ? "state = 'closed'" : "state IN ('closed', 'merged')"); case "merged": return issues ? NOTHING : cond("state = 'merged'"); case "draft": return issues ? NOTHING : cond("draft = 1"); } break; case "label": return issues ? cond(`EXISTS (SELECT 1 FROM json_each(${LABELS}) j WHERE j.value = ?)`, t.value) : NOTHING; case "no": return issues ? cond(`json_array_length(${LABELS}) = 0`) : NOTHING; case "author": return userCond(t.user, ctx, table, issues ? ["author_id"] : ["author_id", "owner_id"]); case "review-requested": return issues ? NOTHING : reviewCond(t.user, ctx); case "number": return cond("number = ?", t.value); case "text": { const p = likePattern(t.value); return issues ? cond("title LIKE ? ESCAPE '\\' OR body LIKE ? ESCAPE '\\'", p, p) : cond("title LIKE ? ESCAPE '\\'", p); } case "date": return dateCond(t.field === "created" ? "created_at" : "updated_at", t.filter, ctx.now); case "sort": return null; } return NOTHING;}const ORDER: Record<SortValue, string> = { "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 function sortOf(q: SearchQuery): SortValue { for (const t of q.terms) if (t.kind === "sort") return t.value; return "created-desc";}export function toSql(query: SearchQuery, ctx: SqlCtx): SqlResult { const parts = ["repo_id = ?", "org_id = ?"]; const params: SqlValue[] = [String(ctx.repoId), String(ctx.orgId)]; for (const t of query.terms) { const c = termCond(t, query, ctx); if (!c) continue; parts.push("neg" in t && t.neg ? `NOT IFNULL((${c.sql}), 0)` : `(${c.sql})`); params.push(...c.params); } let order = ORDER[sortOf(query)]; // comments is an issues column; a pulls query never parses with it, and never gets it here if (query.target === "pulls" && order.startsWith("comments")) order = ORDER["created-desc"]; return { where: parts.join(" AND "), params, orderBy: order };}