sql.test.ts

import { readFileSync } from "node:fs";
import { PGlite } from "@electric-sql/pglite";
import { afterAll, beforeAll, describe, expect, it } from "vitest";
import { decodeId, encodeId, mintId, uuidv7 } from "./index.js";
import { ID758_SQL, ID758_SQL_STATEMENTS } from "./sql.js";

// The Postgres port must agree byte-for-byte with the reference codec. The
// cross-impl vectors are the contract; the fuzz round-trip is the belt and
// braces.
const VECTORS: Record<string, string> = {
	"00000000000000000000000000000000": "1".repeat(22),
	"00000000000000000000000000000001": `${"1".repeat(21)}2`,
	"000000000000000000000000000000ff": `${"1".repeat(20)}5Q`,
	"0192f8a17c3e7b9a8c1d4e5f6a7b8c9d": "1CGtMb233ezidDvSwDLNBn",
	ffffffffffffffffffffffffffffffff: "YcVfxkQb6JRzqk5kF2tNLv",
};

const hexToUuid = (hex: string): string =>
	`${hex.slice(0, 8)}-${hex.slice(8, 12)}-${hex.slice(12, 16)}-${hex.slice(16, 20)}-${hex.slice(20)}`;

let db: PGlite;
const one = async <T>(sql: string, params: unknown[] = []): Promise<T> => {
	const { rows } = await db.query<{ v: T }>(`select (${sql}) as v`, params);
	const row = rows[0];
	if (!row) throw new Error("no row");
	return row.v;
};

beforeAll(async () => {
	db = await PGlite.create();
	await db.exec(ID758_SQL);
});
afterAll(() => db.close());

describe("id758.sql", () => {
	it("is the checked-in file", () => {
		expect(readFileSync(new URL("../id758.sql", import.meta.url), "utf8")).toBe(
			ID758_SQL,
		);
	});

	it("is one statement per entry, applicable individually and idempotent", async () => {
		for (const statement of ID758_SQL_STATEMENTS) await db.exec(statement);
	});

	it("is immutable / strict / parallel safe", async () => {
		const { rows } = await db.query<{
			proname: string;
			provolatile: string;
			proisstrict: boolean;
			proparallel: string;
		}>(
			"select proname, provolatile, proisstrict, proparallel from pg_proc where proname like 'id758_%' order by 1",
		);
		expect(rows.map((r) => r.proname)).toEqual([
			"id758_decode",
			"id758_decode",
			"id758_encode",
			"id758_prefix",
		]);
		for (const row of rows) {
			expect(row.provolatile).toBe("i");
			expect(row.proisstrict).toBe(true);
			expect(row.proparallel).toBe("s");
		}
	});
});

describe("id758_encode", () => {
	it("matches the cross-impl vectors", async () => {
		for (const [hex, body] of Object.entries(VECTORS)) {
			expect(await one("id758_encode('x', $1::uuid)", [hexToUuid(hex)])).toBe(
				`x_${body}`,
			);
		}
	});

	it("rejects a prefix that is not a plain identifier", async () => {
		await expect(one("id758_encode('a_b', $1::uuid)", [uuidv7()])).rejects.toThrow(
			"invalid id prefix: a_b",
		);
		await expect(one("id758_encode('', $1::uuid)", [uuidv7()])).rejects.toThrow(
			"invalid id prefix: ",
		);
	});

	it("is null on a null argument", async () => {
		expect(await one("id758_encode('x', null)")).toBeNull();
		expect(await one("id758_encode(null, $1::uuid)", [uuidv7()])).toBeNull();
	});
});

describe("id758_decode", () => {
	it("matches the cross-impl vectors", async () => {
		for (const [hex, body] of Object.entries(VECTORS)) {
			expect(await one("id758_decode($1)", [`x_${body}`])).toBe(hexToUuid(hex));
		}
	});

	it("rejects a missing prefix, a short body, a raw uuid, or a bad char", async () => {
		for (const bad of [
			"1CGtMb233ezidDvSwDLNBn",
			"x_short",
			uuidv7(),
			`x_${"0".repeat(22)}`,
		]) {
			await expect(one("id758_decode($1)", [bad])).rejects.toThrow(
				`not a prefixed id: ${bad}`,
			);
		}
	});

	it("rejects a 22-char body that overflows 128 bits instead of aliasing", async () => {
		await expect(one("id758_decode($1)", [`x_${"z".repeat(22)}`])).rejects.toThrow(
			"base58 value out of uuid range",
		);
	});

	it("with a prefix, also requires that prefix", async () => {
		const id = mintId("inv");
		expect(await one("id758_decode($1, 'inv')", [id])).toBe(decodeId(id));
		await expect(one("id758_decode($1, 'org')", [id])).rejects.toThrow(
			`not a org_ id: ${id}`,
		);
		await expect(one("id758_decode($1, 'inv')", ["garbage"])).rejects.toThrow(
			"not a inv_ id: garbage",
		);
	});

	it("is null on a null argument", async () => {
		expect(await one("id758_decode(null)")).toBeNull();
		expect(await one("id758_decode(null, 'inv')")).toBeNull();
	});
});

describe("id758_prefix", () => {
	it("names the prefix or is null", async () => {
		expect(await one("id758_prefix($1)", [mintId("inv")])).toBe("inv");
		expect(await one("id758_prefix($1)", [uuidv7()])).toBeNull();
		expect(await one("id758_prefix('inv_short')")).toBeNull();
	});
});

describe("agreement with the reference codec", () => {
	it("round-trips random uuids through both directions, in both implementations", async () => {
		const uuids: string[] = Array.from({ length: 64 }, () => uuidv7());
		// A v4-shaped and an all-ones uuid stress the high bits.
		uuids.push(
			"ffffffff-ffff-4fff-bfff-ffffffffffff",
			"00000000-0000-4000-8000-000000000000",
		);
		const { rows } = await db.query<{ u: string; e: string; d: string }>(
			`select u, id758_encode('t', u::uuid) as e, id758_decode(id758_encode('t', u::uuid)) as d
			 from unnest($1::text[]) as u`,
			[uuids],
		);
		expect(rows).toHaveLength(uuids.length);
		for (const row of rows) {
			expect(row.e).toBe(encodeId(row.u, "t"));
			expect(row.d).toBe(row.u);
			expect(decodeId(row.e)).toBe(row.u);
		}
	});

	it("is usable as an index expression and folds to a constant in a where clause", async () => {
		await db.exec(`create table t (id uuid primary key);`);
		const id = mintId("t");
		await db.query("insert into t values (id758_decode($1))", [id]);
		await db.exec(`create unique index t_public_id on t (id758_encode('t', id));`);
		const { rows } = await db.query<{ id: string }>(
			"select id from t where id = id758_decode($1)",
			[id],
		);
		expect(rows).toEqual([{ id: decodeId(id) }]);
		const { rows: byPublic } = await db.query<{ id: string }>(
			"select id from t where id758_encode('t', id) = $1",
			[id],
		);
		expect(byPublic).toEqual([{ id: decodeId(id) }]);
	});
});