Rebasely Beta: data may be purged

justintsus-experiment Public

TypeScript · 257 lines · 14.1 KiB · at 8e57616

// toSql (apps/web/src/search/sql.tsus) against SQLite with the D1 tables it reads (issues, pull_index,// pull_review_requests, agents): each term's rows, negation as the exact complement (NULL owner_id// included), @me without a viewer, agents scoped to the organization, the date tables, LIKE escaping,// the order, and the scope that no term widens. Every value is bound.import assert from "node:assert/strict";import { DatabaseSync } from "node:sqlite";import test from "node:test";import { parseQuery, toSql, type SqlCtx, type Target } from "../../../apps/web/src/search/api.ts";const DAY = 86_400_000;const T0 = Date.UTC(2026, 9, 1); // 2026-10-01const NOW = Date.UTC(2026, 9, 16, 12);const ALICE = "aaaaaaaa-0000-4000-8000-000000000001";const BOB = "bbbbbbbb-0000-4000-8000-000000000002";const BOT = "cccccccc-0000-4000-8000-000000000003"; // agent "docs-bot" in org o1, owned by Aliceconst OTHER_BOT = "dddddddd-0000-4000-8000-000000000004"; // agent "other-bot" in org o2function 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, 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 (?, 'o1', 'docs-bot', ?)").run(BOT, ALICE);  d.prepare("INSERT INTO agents VALUES (?, 'o2', 'other-bot', ?)").run(OTHER_BOT, BOB);  const issue = d.prepare("INSERT INTO issues (repo_id, number, org_id, author_id, title, body, state, labels, comments, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");  // number, author, title, body, state, labels, comments, created, updated  const rows: [number, string, string, string, string, string[], number, number, number][] = [    [1, ALICE, "Crash on start", "out of memory", "open", ["bug"], 3, T0, T0 + 5 * DAY],    [2, BOB, "Docs typo", "", "closed", ["docs", "needs review"], 0, T0 + DAY, T0 + DAY],    [3, BOT, "100% CPU_usage", "a\\b", "open", [], 7, T0 + 2 * DAY, NOW - 3_600_000],    [4, BOB, "Unicode Éclair", "crash later", "open", ["bug", "docs"], 1, T0 - DAY, NOW - 10 * DAY],  ];  for (const [n, a, title, body, state, labels, comments, c, u] of rows) issue.run("r1", n, "o1", a, title, body, state, JSON.stringify(labels), comments, c, u);  // the same numbers in another repository and another organization: never in scope  for (const [n, a, title, body, state, labels, comments, c, u] of rows) issue.run("r2", n, "o2", a, title, body, state, JSON.stringify(labels), comments, c, u);  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) VALUES (?, ?, ?, ?, ?, ?, ?, ?, 'refs/heads/main', 'refs/heads/x', ?, ?)");  const pulls: [number, string, string | null, string, number, string, number, number][] = [    [1, ALICE, null, "open", 0, "Fix login", T0, T0],    [2, BOT, ALICE, "open", 1, "Draft from the bot", T0 + DAY, T0 + 2 * DAY],    [3, BOB, null, "merged", 0, "Merge me", T0 + 2 * DAY, T0 + 3 * DAY],    [4, BOB, null, "closed", 1, "Closed draft", T0 + 3 * DAY, T0 + 4 * DAY],    [5, OTHER_BOT, BOB, "open", 0, "Stranger bot", T0 + 4 * DAY, T0 + 4 * DAY],  ];  for (const [n, a, o, s, dr, title, c, u] of pulls) {    pull.run("r1", n, "o1", a, o, s, dr, title, c, u);    pull.run("r2", n, "o2", a, o, s, dr, title, c, u);  }  const rr = d.prepare("INSERT INTO pull_review_requests VALUES (?, ?, ?, 0)");  rr.run("r1", 1, BOB);  rr.run("r1", 3, ALICE);  rr.run("r1", 4, BOT);  rr.run("r2", 2, BOB);  return d;}const D = db();function ctx(viewerId: string | null = ALICE): SqlCtx {  return { repoId: "r1", orgId: "o1", viewerId, now: NOW };}function run(input: string, target: Target, c: SqlCtx = ctx()): number[] {  const r = parseQuery(input, target);  assert.ok(r.ok, `${input}: ${JSON.stringify(r)}`);  const s = toSql(r.query, c);  const table = target === "issues" ? "issues" : "pull_index";  const rows = D.prepare(`SELECT number FROM ${table} WHERE ${s.where} AND 1 = 1 ORDER BY ${s.orderBy} LIMIT ?`).all(...s.params, 100) as { number: number }[];  return rows.map((x) => x.number);}const sorted = (xs: number[]) => [...xs].sort((a, b) => a - b);const all = (t: Target) => (t === "issues" ? [1, 2, 3, 4] : [1, 2, 3, 4, 5]);// A negated term matches exactly the rows its positive form does not.function complement(term: string, target: Target, c: SqlCtx = ctx()) {  const yes = run(term, target, c);  const no = run(`-${term}`, target, c);  assert.deepEqual(sorted([...yes, ...no]), all(target), term);  assert.equal(yes.filter((n) => no.includes(n)).length, 0, term);}test("the empty query is every row in scope, newest first, and nothing else", () => {  assert.deepEqual(run("", "issues"), [3, 2, 1, 4]);  assert.deepEqual(run("", "pulls"), [5, 4, 3, 2, 1]);  const s = toSql((parseQuery("", "issues") as { ok: true; query: never }).query, ctx());  assert.match(s.where, /^issues\.repo_id = \? AND issues\.org_id = \?$/);  assert.deepEqual(s.params, ["r1", "o1"]);  assert.equal(s.orderBy, "created_at DESC, number DESC");});test("states", () => {  assert.deepEqual(sorted(run("is:open", "issues")), [1, 3, 4]);  assert.deepEqual(run("is:closed", "issues"), [2]);  assert.deepEqual(run("is:open is:closed", "issues"), []);  assert.deepEqual(sorted(run("is:open", "pulls")), [1, 2, 5]);  assert.deepEqual(sorted(run("is:closed", "pulls")), [3, 4]);  assert.deepEqual(run("is:merged", "pulls"), [3]);  assert.deepEqual(sorted(run("is:draft", "pulls")), [2, 4]);  for (const t of ["is:open", "is:closed"]) complement(t, "issues");  for (const t of ["is:open", "is:closed", "is:merged", "is:draft"]) complement(t, "pulls");});test("labels", () => {  assert.deepEqual(sorted(run("label:bug", "issues")), [1, 4]);  assert.deepEqual(run('label:"Needs Review"', "issues"), [2]);  assert.deepEqual(run("label:bug label:docs", "issues"), [4]);  assert.deepEqual(run("no:label", "issues"), [3]);  assert.deepEqual(run("label:nothing", "issues"), []);  complement("label:bug", "issues");});test("authors and review requests", () => {  assert.deepEqual(run("author:@me", "issues"), [1]);  assert.deepEqual(sorted(run(`author:${BOB}`, "issues")), [2, 4]);  assert.deepEqual(run("author:docs-bot", "issues"), [3]);  // an agent of another organization, or no agent, matches nothing  assert.deepEqual(run("author:other-bot", "issues"), []);  assert.deepEqual(run("author:nobody", "issues"), []);  // an agent's PR shows under its owner  assert.deepEqual(sorted(run("author:@me", "pulls")), [1, 2]);  assert.deepEqual(run("author:docs-bot", "pulls"), [2]);  assert.deepEqual(run("author:other-bot", "pulls"), []);  assert.deepEqual(sorted(run(`author:${BOB}`, "pulls")), [3, 4, 5]);  // -author: keeps the PRs with no owner whose author is someone else  assert.deepEqual(sorted(run("-author:@me", "pulls")), [3, 4, 5]);  for (const u of ["@me", BOB, "docs-bot", "other-bot"]) {    complement(`author:${u}`, "issues");    complement(`author:${u}`, "pulls");    complement(`review-requested:${u}`, "pulls");  }  assert.deepEqual(run("review-requested:@me", "pulls"), [3]);  assert.deepEqual(run(`review-requested:${BOB}`, "pulls"), [1]);  assert.deepEqual(run("review-requested:docs-bot", "pulls"), [4]);});test("@me with no one signed in matches nothing, and its negation everything", () => {  const anon = ctx(null);  assert.deepEqual(run("author:@me", "issues", anon), []);  assert.deepEqual(sorted(run("-author:@me", "issues", anon)), [1, 2, 3, 4]);  assert.deepEqual(run("review-requested:@me", "pulls", anon), []);  assert.deepEqual(sorted(run("-review-requested:@me", "pulls", anon)), [1, 2, 3, 4, 5]);  assert.deepEqual(run("author:@me", "pulls", anon), []);});test("numbers and text", () => {  assert.deepEqual(run("#3", "issues"), [3]);  assert.deepEqual(run("#9", "issues"), []);  assert.deepEqual(sorted(run("crash", "issues")), [1, 4]); // title or body, ASCII case-insensitive  assert.deepEqual(run('"out of memory"', "issues"), [1]);  assert.deepEqual(run("out memory", "issues"), [1]);  assert.deepEqual(run('"memory out"', "issues"), []);  assert.deepEqual(run("100%", "issues"), [3]);  assert.deepEqual(run("0%", "issues"), [3]);  assert.deepEqual(run("%", "issues"), [3]);  assert.deepEqual(run("_", "issues"), [3]);  assert.deepEqual(run("u_u", "issues"), [3]);  assert.deepEqual(run("x_x", "issues"), []);  assert.deepEqual(run("a\\b", "issues"), [3]);  assert.deepEqual(run("éclair", "issues"), []); // LIKE folds ASCII only  assert.deepEqual(run("Éclair", "issues"), [4]);  assert.deepEqual(run("login", "pulls"), [1]);  assert.deepEqual(run("memory", "pulls"), []);  complement("crash", "issues");  complement("draft", "pulls");});test("dates follow the tables", () => {  // created: 4 = T0-1d, 1 = T0, 2 = T0+1d, 3 = T0+2d  assert.deepEqual(run("created:2026-10-01", "issues"), [1]);  assert.deepEqual(sorted(run("created:>2026-10-01", "issues")), [2, 3]);  assert.deepEqual(sorted(run("created:>=2026-10-01", "issues")), [1, 2, 3]);  assert.deepEqual(run("created:<2026-10-01", "issues"), [4]);  assert.deepEqual(sorted(run("created:<=2026-10-01", "issues")), [1, 4]);  assert.deepEqual(sorted(run("created:2026-10-01..2026-10-02", "issues")), [1, 2]);  assert.deepEqual(sorted(run("created:2026-10-02..*", "issues")), [2, 3]);  assert.deepEqual(sorted(run("created:*..2026-10-01", "issues")), [1, 4]);  // updated: 3 = now-1h, 4 = now-10d  assert.deepEqual(run("updated:<2h", "issues"), [3]);  assert.deepEqual(run("updated:<1h", "issues"), []);  assert.deepEqual(run("updated:<=1h", "issues"), [3]);  assert.deepEqual(run("updated:>=1h", "issues").includes(3), true);  assert.deepEqual(run("updated:>1h", "issues").includes(3), false);  assert.deepEqual(run("updated:<1w", "issues"), [3]);  assert.deepEqual(sorted(run("updated:>1w", "issues")), [1, 2, 4]);});test("the date bounds as bound values", () => {  const sql = (v: string) => {    const r = parseQuery(`created:${v}`, "issues");    assert.ok(r.ok);    return toSql(r.query, ctx());  };  const D1 = Date.UTC(2026, 9, 1);  assert.deepEqual(sql("2026-10-01").params.slice(2), [D1, D1 + DAY]);  assert.deepEqual(sql(">2026-10-01").params.slice(2), [D1 + DAY]);  assert.match(sql(">2026-10-01").where, /created_at >= \?\)$/);  assert.match(sql("<=2026-10-01").where, /created_at < \?\)$/);  assert.deepEqual(sql("<=2026-10-01").params.slice(2), [D1 + DAY]);  assert.deepEqual(sql("<7d").params.slice(2), [NOW - 7 * DAY]);  assert.match(sql("<7d").where, /created_at > \?\)$/);  assert.match(sql(">=7d").where, /created_at <= \?\)$/);  assert.deepEqual(sql(">=3w").params.slice(2), [NOW - 21 * DAY]);});test("sort: picks the order, with the number as tie-break in the same direction", () => {  assert.deepEqual(run("sort:created-asc", "issues"), [4, 1, 2, 3]);  assert.deepEqual(run("sort:updated-desc", "issues"), [3, 4, 1, 2]);  assert.deepEqual(run("sort:comments-desc", "issues"), [3, 1, 4, 2]);  assert.deepEqual(run("sort:comments-asc", "issues"), [2, 4, 1, 3]);  assert.deepEqual(run("sort:updated-asc", "pulls"), [1, 2, 3, 4, 5]);  const r = parseQuery("sort:updated-asc", "pulls");  assert.ok(r.ok);  assert.equal(toSql(r.query, ctx()).orderBy, "updated_at ASC, number ASC");});test("nothing from the input or the context is written into the SQL text", () => {  const evil = "x' OR 1=1 --";  const inputs = [`"${evil}"`, `label:"${evil.toLowerCase().replace(/[^a-z0-9 ._-]/g, "")}"`, "author:@me", "author:docs-bot", `author:${BOB}`, "review-requested:docs-bot", "#42", "created:2026-10-01", "updated:<7d", "is:open"];  const c = { repoId: "repo'); DROP TABLE issues; --", orgId: "org' OR '1'='1", viewerId: "viewer'--", now: NOW };  for (const t of ["issues", "pulls"] as const) {    for (const x of inputs) {      const r = parseQuery(x, t);      if (!r.ok) continue;      const s = toSql(r.query, c);      const literals = s.where.match(/'[^']*'/g) ?? [];      for (const l of literals) assert.ok(["'open'", "'closed'", "'merged'", "'\\'"].includes(l), `${x}: ${l}`);      for (const v of [c.repoId, c.orgId, c.viewerId, BOB, "docs-bot", "42", String(NOW)]) assert.ok(!s.where.includes(v), `${x}: ${v}`);      assert.equal((s.where.match(/\?/g) ?? []).length, s.params.length, x);      assert.match(s.where, /^(issues|pull_index)\.repo_id = \? AND (issues|pull_index)\.org_id = \?( AND \(.*\))?$/);      assert.deepEqual(s.params.slice(0, 2), [c.repoId, c.orgId]);      // it runs, and finds nothing outside the scope      D.prepare(`SELECT number FROM ${t === "issues" ? "issues" : "pull_index"} WHERE ${s.where} AND 1 = 1 ORDER BY ${s.orderBy}`).all(...s.params);    }  }});test("no term widens the scope", () => {  const wide = ['"" OR 1', "-is:open", "-author:@me", "-label:bug", "-crash", "-review-requested:@me", "created:*..9999-12-31"];  for (const t of ["issues", "pulls"] as const) {    for (const x of wide) {      const r = parseQuery(x, t);      if (!r.ok) continue;      const s = toSql(r.query, { ...ctx(), repoId: "r2", orgId: "o1" });      const table = t === "issues" ? "issues" : "pull_index";      assert.deepEqual(D.prepare(`SELECT number FROM ${table} WHERE ${s.where} AND 1 = 1`).all(...s.params), [], `${t} ${x}`);    }  }});