Rebasely Beta: data may be purged

justintsus-experiment Public

TypeScript · 203 lines · 11.6 KiB · at 8e57616

// 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);});