justintsus-experiment Public
// unit/search/sql (apps/web/src/search/sql.ts): toSql run against SQLite (node:sqlite) with the D1// tables it reads (issues, pull_index, pull_review_requests, agents, as the migrations make them):// what each term matches on each list, that a negated term matches exactly the rest (NULL owners// included), the scope that no query widens, values always bound, and the order.import { test } from "node:test";import assert from "node:assert/strict";import { DatabaseSync } from "node:sqlite";import { parseQuery, toSql, type SqlCtx, type Target } from "../../../apps/web/src/search/api.ts";const H1 = "11111111-1111-4111-8111-111111111111";const H2 = "22222222-2222-4222-8222-222222222222";const BOT = "aaaaaaaa-aaaa-4aaa-8aaa-aaaaaaaaaaaa";const OTHER_BOT = "bbbbbbbb-bbbb-4bbb-8bbb-bbbbbbbbbbbb";const DAY = 86_400_000;const T0 = Date.UTC(2026, 9, 1);const NOW = T0 + 30 * DAY;function db(): DatabaseSync { const d = new DatabaseSync(":memory:"); d.exec(` CREATE TABLE issues (repo_id TEXT NOT NULL, number INTEGER NOT NULL, org_id TEXT NOT NULL, author_id TEXT NOT NULL, title TEXT NOT NULL, body TEXT NOT NULL DEFAULT '', state TEXT NOT NULL DEFAULT 'open', labels TEXT NOT NULL DEFAULT '[]', comments INTEGER NOT NULL DEFAULT 0, created_at INTEGER NOT NULL, updated_at INTEGER NOT NULL, closed_at INTEGER, closed_by TEXT, ip_key TEXT, notified INTEGER NOT NULL DEFAULT 0, PRIMARY KEY (repo_id, number)); CREATE TABLE pull_index (repo_id TEXT NOT NULL, number INTEGER NOT NULL, org_id TEXT NOT NULL, author_id TEXT NOT NULL, owner_id TEXT, state TEXT NOT NULL, draft INTEGER NOT NULL DEFAULT 0, title TEXT NOT NULL, base_ref TEXT NOT NULL, head_ref TEXT NOT NULL, created_at INTEGER NOT NULL, updated_at INTEGER NOT NULL, merged_at INTEGER, closed_at INTEGER, PRIMARY KEY (repo_id, number)); CREATE TABLE pull_review_requests (repo_id TEXT NOT NULL, number INTEGER NOT NULL, user_id TEXT NOT NULL, requested_at INTEGER NOT NULL, PRIMARY KEY (repo_id, number, user_id)); CREATE TABLE agents (user_id TEXT PRIMARY KEY, org_id TEXT NOT NULL, slug TEXT NOT NULL, owner_user_id TEXT NOT NULL, UNIQUE (org_id, slug)); `); d.prepare("INSERT INTO agents VALUES (?, ?, ?, ?)").run(BOT, "o1", "bot", H1); d.prepare("INSERT INTO agents VALUES (?, ?, ?, ?)").run(OTHER_BOT, "o2", "other", H2); const issue = d.prepare("INSERT INTO issues (repo_id, number, org_id, author_id, title, body, state, labels, comments, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"); issue.run("r1", 1, "o1", H1, "Login fails", "on Safari 100% of the time", "open", '["bug"]', 3, T0, T0 + 20 * DAY); issue.run("r1", 2, "o1", BOT, "Docs typo", "", "closed", '["docs","needs review"]', 0, T0 + DAY, T0 + 2 * DAY); issue.run("r1", 3, "o1", H2, "a_b in names", "path x\\y", "open", "[]", 10, T0 + 2 * DAY + 5, T0 + 29 * DAY); issue.run("r1", 4, "o1", OTHER_BOT, "Stored labels are not JSON", "LOGIN", "open", "not json", 1, T0 + 3 * DAY, T0 + 3 * DAY); issue.run("r1", 5, "o1", H1, "100% broken", "", "closed", '"bug"', 1, T0 + 4 * DAY, T0 + 4 * DAY); // other repositories and another organization: never listed for r1 in o1 issue.run("r2", 1, "o1", H1, "Login fails", "", "open", '["bug"]', 0, T0, T0); issue.run("r1x", 1, "o2", H1, "Login fails", "", "open", '["bug"]', 0, T0, T0); const pull = d.prepare("INSERT INTO pull_index (repo_id, number, org_id, author_id, owner_id, state, draft, title, base_ref, head_ref, created_at, updated_at, merged_at, closed_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, 'refs/heads/main', 'refs/heads/x', ?, ?, ?, ?)"); pull.run("r1", 1, "o1", H1, null, "open", 0, "Fix login", T0, T0 + 5 * DAY, null, null); pull.run("r1", 2, "o1", BOT, H1, "open", 1, "Bot change", T0 + DAY, T0 + 28 * DAY, null, null); pull.run("r1", 3, "o1", H2, null, "merged", 0, "Merge me", T0 + 2 * DAY, T0 + 2 * DAY, T0 + 2 * DAY, null); pull.run("r1", 4, "o1", H2, null, "closed", 1, "Abandoned draft", T0 + 3 * DAY, T0 + 3 * DAY, null, T0 + 3 * DAY); pull.run("r1", 5, "o1", OTHER_BOT, null, "open", 0, "Other org's bot", T0 + 4 * DAY, T0 + 4 * DAY, null, null); pull.run("r2", 1, "o1", H1, null, "open", 0, "Fix login", T0, T0, null, null); const rr = d.prepare("INSERT INTO pull_review_requests VALUES (?, ?, ?, 0)"); rr.run("r1", 1, H2); rr.run("r1", 3, BOT); rr.run("r2", 2, H2); rr.run("r2", 4, H1); return d;}const DB = db();const ctx = (viewerId: string | null = H1): SqlCtx => ({ repoId: "r1", orgId: "o1", viewerId, now: NOW });function run(q: string, target: Target, c: SqlCtx = ctx()): number[] { const r = parseQuery(q, target); assert.ok(r.ok, `${q}: ${r.ok ? "" : JSON.stringify(r.errors)}`); const s = toSql(r.query, c); const table = target === "issues" ? "issues" : "pull_index"; const rows = DB.prepare(`SELECT repo_id, org_id, number FROM ${table} WHERE ${s.where} AND 1 = 1 ORDER BY ${s.orderBy} LIMIT ?`).all(...s.params, 100) as { repo_id: string; org_id: string; number: number }[]; for (const x of rows) assert.deepEqual([x.repo_id, x.org_id], ["r1", "o1"], `${q} left its scope`); return rows.map((x) => x.number);}const sorted = (a: number[]) => [...a].sort((x, y) => x - y);const ALL_ISSUES = [1, 2, 3, 4, 5];const ALL_PULLS = [1, 2, 3, 4, 5];test("issues: each term", () => { const i = (q: string, c?: SqlCtx) => sorted(run(q, "issues", c)); assert.deepEqual(i(""), ALL_ISSUES); assert.deepEqual(i("is:open"), [1, 3, 4]); assert.deepEqual(i("is:closed"), [2, 5]); assert.deepEqual(i("is:open is:closed"), []); assert.deepEqual(i("label:bug"), [1]); assert.deepEqual(i('label:"Needs Review"'), [2]); assert.deepEqual(i("no:label"), [3, 4, 5]); assert.deepEqual(i("author:@me"), [1, 5]); assert.deepEqual(i("author:@me", ctx(null)), []); assert.deepEqual(i("-author:@me", ctx(null)), ALL_ISSUES); assert.deepEqual(i(`author:${H2.toUpperCase()}`), [3]); assert.deepEqual(i("author:bot"), [2]); assert.deepEqual(i("author:other"), []); assert.deepEqual(i("author:nobody"), []); assert.deepEqual(i("#3"), [3]); assert.deepEqual(i("#99"), []); assert.deepEqual(i("login"), [1, 4]); assert.deepEqual(i("LOGIN fails"), [1]); assert.deepEqual(i('"login fails"'), [1]); assert.deepEqual(i('"fails login"'), []); assert.deepEqual(i("100%"), [1, 5]); assert.deepEqual(i("%"), [1, 5]); assert.deepEqual(i("_"), [3]); assert.deepEqual(i("x\\y"), [3]); assert.deepEqual(i("\\"), [3]); assert.deepEqual(i("created:2026-10-01"), [1]); assert.deepEqual(i("created:>2026-10-02"), [3, 4, 5]); assert.deepEqual(i("created:>=2026-10-02"), [2, 3, 4, 5]); assert.deepEqual(i("created:<2026-10-02"), [1]); assert.deepEqual(i("created:<=2026-10-02"), [1, 2]); assert.deepEqual(i("created:2026-10-02..2026-10-03"), [2, 3]); assert.deepEqual(i("created:2026-10-04..*"), [4, 5]); assert.deepEqual(i("created:*..2026-10-01"), [1]); assert.deepEqual(i("updated:<2d"), [3]); assert.deepEqual(i("updated:>=10d"), [1, 2, 4, 5]); assert.deepEqual(i("updated:<=1d"), [3]); assert.deepEqual(i("updated:>1d"), [1, 2, 4, 5]); assert.deepEqual(i("updated:<25h"), [3]);});test("pull requests: each term", () => { const p = (q: string, c?: SqlCtx) => sorted(run(q, "pulls", c)); assert.deepEqual(p(""), ALL_PULLS); assert.deepEqual(p("is:open"), [1, 2, 5]); assert.deepEqual(p("is:closed"), [3, 4]); assert.deepEqual(p("is:merged"), [3]); assert.deepEqual(p("is:draft"), [2, 4]); assert.deepEqual(p("is:open -is:draft"), [1, 5]); assert.deepEqual(p("author:@me"), [1, 2]); assert.deepEqual(p("-author:@me"), [3, 4, 5]); assert.deepEqual(p("author:bot"), [2]); assert.deepEqual(p("-author:bot"), [1, 3, 4, 5]); assert.deepEqual(p("author:other"), []); assert.deepEqual(p(`author:${H2}`), [3, 4]); assert.deepEqual(p("review-requested:@me"), []); assert.deepEqual(p(`review-requested:${H2}`), [1]); assert.deepEqual(p(`-review-requested:${H2}`), [2, 3, 4, 5]); assert.deepEqual(p("review-requested:bot"), [3]); assert.deepEqual(p("review-requested:@me", ctx(H2)), [1]); assert.deepEqual(p("review-requested:@me", ctx(null)), []); assert.deepEqual(p("-review-requested:@me", ctx(null)), ALL_PULLS); assert.deepEqual(p("login"), [1]); assert.deepEqual(p("#4"), [4]); assert.deepEqual(p("created:>=2026-10-03 updated:<2026-10-05"), [3, 4]); assert.deepEqual(p("created:>=2026-10-03 updated:<=2026-10-05"), [3, 4, 5]);});test("a negated term matches exactly the rows its positive form does not", () => { const terms: Record<Target, string[]> = { issues: ["is:open", "is:closed", "label:bug", "label:docs", "author:@me", "author:bot", "author:other", `author:${H2}`, "login", '"login fails"', "%", "zzz"], pulls: ["is:open", "is:closed", "is:merged", "is:draft", "author:@me", "author:bot", "author:other", `author:${H2}`, "review-requested:@me", `review-requested:${H2}`, "review-requested:bot", "login", "zzz"], }; for (const target of ["issues", "pulls"] as const) { for (const viewer of [H1, H2, null]) { for (const t of terms[target]) { const pos = run(t, target, ctx(viewer)); const neg = run(`-${t}`, target, ctx(viewer)); assert.deepEqual(sorted([...pos, ...neg]), target === "issues" ? ALL_ISSUES : ALL_PULLS, `${target} ${t} viewer ${viewer}`); } } }});test("sort: the column and the tie-break in the same direction", () => { assert.deepEqual(run("", "issues"), [5, 4, 3, 2, 1]); assert.deepEqual(run("sort:created-asc", "issues"), [1, 2, 3, 4, 5]); assert.deepEqual(run("sort:updated-desc", "issues"), [3, 1, 5, 4, 2]); assert.deepEqual(run("sort:comments-desc", "issues"), [3, 1, 5, 4, 2]); assert.deepEqual(run("sort:comments-asc", "issues"), [2, 4, 5, 1, 3]); assert.deepEqual(run("sort:updated-asc", "pulls"), [3, 4, 5, 1, 2]); const order = (q: string, t: Target) => { const r = parseQuery(q, t); assert.ok(r.ok); return toSql(r.query, ctx()).orderBy; }; assert.equal(order("", "pulls"), "created_at DESC, number DESC"); assert.equal(order("sort:comments-asc", "issues"), "comments ASC, number ASC"); assert.equal(order("sort:updated-desc", "pulls"), "updated_at DESC, number DESC");});test("every value is bound: the SQL text holds only its own constants, the scope comes first", () => { const nasty = [`"'; DROP TABLE issues; --"`, `"x' OR '1'='1"`, "label:\"a'b\"", "author:x-y", `"?" "?1" "$a" ":a" "@a"`, `"\\\\'"`, "is:open"]; for (const target of ["issues", "pulls"] as const) { for (const q of nasty) { const r = parseQuery(q, target); if (!r.ok) continue; const evil: SqlCtx = { repoId: "r1' OR 1=1 --", orgId: "o1' --", viewerId: "v' --", now: NOW }; const s = toSql(r.query, evil); assert.ok(s.where.startsWith("repo_id = ? AND org_id = ?"), s.where); const literals = s.where.replace(/'(open|closed|merged|array|\[\]|\\)'/g, ""); assert.doesNotMatch(literals, /'/, `${q}: ${s.where}`); assert.doesNotMatch(s.where, /DROP|1=1|\$a|:a\b|@a|\?1/); assert.equal((s.where.match(/\?/g) ?? []).length, s.params.length); assert.deepEqual(s.params.slice(0, 2), [evil.repoId, evil.orgId]); assert.match(s.orderBy, /^(created_at|updated_at|comments) (ASC|DESC), number (ASC|DESC)$/); // runs, and finds nothing outside a repository that does not exist const table = target === "issues" ? "issues" : "pull_index"; assert.deepEqual(DB.prepare(`SELECT number FROM ${table} WHERE ${s.where} ORDER BY ${s.orderBy} LIMIT ?`).all(...s.params, 10), []); } } // the most bound values a query can need stays under D1's 100 a statement const big = Array.from({ length: 32 }, (_, i) => `w${i}`).join(" "); const r = parseQuery(big, "issues"); assert.ok(r.ok); assert.ok(toSql(r.query, ctx()).params.length + 3 <= 100);});