Codex
076c9742c5
Add catalog-owned logical relationships and runtime snapshots, extend the database-management UI and validation coverage, and document the updated operational workflow. Keep active sensitive-generation status in a tooltip and indicator, and update the layout E2E to follow the history action in its new database-scoped location.
208 lines
9.3 KiB
TypeScript
208 lines
9.3 KiB
TypeScript
import { expect, test } from "vitest";
|
|
import {
|
|
CatalogLogicalRelationshipService,
|
|
LogicalRelationshipDuplicateError,
|
|
LogicalRelationshipSchemaStaleError,
|
|
LogicalRelationshipTargetNotUniqueError,
|
|
LogicalRelationshipTypeIncompatibleError,
|
|
} from "../src/catalog/logical-relationship-service.js";
|
|
import { MemoryCatalogRepository } from "../src/catalog/memory-repository.js";
|
|
import type { ObservedSchemaSnapshot } from "../src/catalog/types.js";
|
|
|
|
const column = (
|
|
tableName: string,
|
|
name: string,
|
|
ordinalPosition: number,
|
|
dataType: string,
|
|
primaryKeyPosition: number | null,
|
|
) => ({
|
|
tableName, name, ordinalPosition, dataType, primaryKeyPosition,
|
|
isNullable: false, defaultExpression: null, sourceComment: null,
|
|
});
|
|
|
|
function schema(
|
|
tableNames: string[],
|
|
columns: ObservedSchemaSnapshot["columns"],
|
|
relationships: ObservedSchemaSnapshot["relationships"] = [],
|
|
): ObservedSchemaSnapshot {
|
|
return {
|
|
schemaVersion: 1,
|
|
capabilities: { tables: "available", columns: "available", relationships: "available" },
|
|
tables: tableNames.map((name) => ({ name, sourceComment: null })),
|
|
columns,
|
|
relationships,
|
|
};
|
|
}
|
|
|
|
async function setup(snapshot: ObservedSchemaSnapshot) {
|
|
const repository = new MemoryCatalogRepository();
|
|
const database = await repository.create({
|
|
workspaceId: "relationships", engine: "postgres", databaseName: "warehouse", schema: "public",
|
|
binding: { transport: "postgres_direct", host: "db.internal", port: 5432, username: "reader" },
|
|
});
|
|
await repository.applySchemaSync(database.id, database.version, "all", [], snapshot);
|
|
const service = new CatalogLogicalRelationshipService(repository);
|
|
const context = (await repository.getLogicalRelationshipContext(database.id))!;
|
|
const endpoint = (tableName: string, columnName: string) => context.endpoints.find((item) => (
|
|
item.tableName === tableName && item.columnName === columnName
|
|
))!;
|
|
return { repository, database, service, endpoint };
|
|
}
|
|
|
|
test("keeps excluded relationships across rebuild and recreates hard-deleted relationships", async () => {
|
|
const { database, service, endpoint } = await setup(schema(
|
|
["users", "orders"],
|
|
[column("users", "id", 1, "bigint", 1), column("orders", "id", 1, "bigint", 1),
|
|
column("orders", "user_id", 2, "bigint", null)],
|
|
));
|
|
const source = endpoint("orders", "user_id");
|
|
const target = endpoint("users", "id");
|
|
const manual = await service.addManual(database.id, source.columnId, target.columnId);
|
|
expect(manual).toMatchObject({ origin: "manual", status: "active" });
|
|
|
|
expect(await service.setStatus(database.id, manual.id, "excluded"))
|
|
.toMatchObject({ status: "excluded" });
|
|
await expect(service.rebuildGenerated(database.id)).resolves.toEqual({
|
|
added: 0, alreadyPresent: 0, excluded: 1, ambiguous: 0,
|
|
});
|
|
expect((await service.list(database.id)).filter((item) => item.origin !== "physical"))
|
|
.toMatchObject([{ id: manual.id, origin: "manual", status: "excluded" }]);
|
|
|
|
await service.deletePermanently(database.id, manual.id);
|
|
await expect(service.rebuildGenerated(database.id)).resolves.toEqual({
|
|
added: 1, alreadyPresent: 0, excluded: 0, ambiguous: 0,
|
|
});
|
|
expect((await service.list(database.id)).filter((item) => item.origin !== "physical"))
|
|
.toMatchObject([{ origin: "generated", status: "active" }]);
|
|
});
|
|
|
|
test("normalizes snake, kebab, and camel names without fuzzy matching", async () => {
|
|
const { database, service } = await setup(schema(
|
|
["users", "events"],
|
|
[column("users", "id", 1, "bigint", 1), column("events", "id", 1, "bigint", 1),
|
|
column("events", "user_id", 2, "bigint", null),
|
|
column("events", "user-id", 3, "bigint", null),
|
|
column("events", "userId", 4, "bigint", null),
|
|
column("events", "userid", 5, "bigint", null),
|
|
column("events", "unrelated", 6, "bigint", null)],
|
|
));
|
|
await expect(service.rebuildGenerated(database.id)).resolves.toEqual({
|
|
added: 4, alreadyPresent: 0, excluded: 0, ambiguous: 0,
|
|
});
|
|
});
|
|
|
|
test("skips generic bare primary-key names while retaining table-qualified matches", async () => {
|
|
const { database, service } = await setup(schema(
|
|
["users", "accounts", "events"],
|
|
[column("users", "id", 1, "bigint", 1), column("accounts", "id", 1, "bigint", 1),
|
|
column("events", "event_key", 1, "bigint", 1), column("events", "id", 2, "bigint", null),
|
|
column("events", "user_id", 3, "bigint", null)],
|
|
));
|
|
await expect(service.rebuildGenerated(database.id)).resolves.toEqual({
|
|
added: 1, alreadyPresent: 0, excluded: 0, ambiguous: 0,
|
|
});
|
|
});
|
|
|
|
test("infers foreign keys from composite-primary-key sources", async () => {
|
|
const { database, service } = await setup(schema(
|
|
["users", "groups", "memberships"],
|
|
[column("users", "id", 1, "bigint", 1), column("groups", "id", 1, "bigint", 1),
|
|
column("memberships", "user_id", 1, "bigint", 1),
|
|
column("memberships", "group_id", 2, "bigint", 2)],
|
|
));
|
|
await expect(service.rebuildGenerated(database.id)).resolves.toEqual({
|
|
added: 2, alreadyPresent: 0, excluded: 0, ambiguous: 0,
|
|
});
|
|
});
|
|
|
|
test("maps time-key columns to the single primary key of dim_time", async () => {
|
|
const { database, service } = await setup(schema(
|
|
["dim_time", "admissions"],
|
|
[column("dim_time", "day_key", 1, "integer", 1),
|
|
column("admissions", "id", 1, "bigint", 1),
|
|
column("admissions", "admission_time_key", 2, "integer", null),
|
|
column("admissions", "discharge_time_key", 3, "integer", null)],
|
|
));
|
|
await expect(service.rebuildGenerated(database.id)).resolves.toEqual({
|
|
added: 2, alreadyPresent: 0, excluded: 0, ambiguous: 0,
|
|
});
|
|
});
|
|
|
|
test("skips ambiguous targets and physical foreign-key pairs", async () => {
|
|
const ambiguous = await setup(schema(
|
|
["user", "users", "events"],
|
|
[column("user", "id", 1, "bigint", 1), column("users", "id", 1, "bigint", 1),
|
|
column("events", "id", 1, "bigint", 1), column("events", "user_id", 2, "bigint", null)],
|
|
));
|
|
await expect(ambiguous.service.rebuildGenerated(ambiguous.database.id)).resolves.toEqual({
|
|
added: 0, alreadyPresent: 0, excluded: 0, ambiguous: 1,
|
|
});
|
|
|
|
const physical = await setup(schema(
|
|
["users", "orders"],
|
|
[column("users", "id", 1, "bigint", 1), column("orders", "id", 1, "bigint", 1),
|
|
column("orders", "user_id", 2, "bigint", null)],
|
|
[{
|
|
constraintName: "orders_user_id_fkey", sourceTableName: "orders", targetTableName: "users",
|
|
updateRule: "NO ACTION", deleteRule: "NO ACTION", deferrable: false, initiallyDeferred: false,
|
|
columns: [{ position: 1, sourceColumnName: "user_id", targetColumnName: "id" }],
|
|
}],
|
|
));
|
|
await expect(physical.service.rebuildGenerated(physical.database.id)).resolves.toEqual({
|
|
added: 0, alreadyPresent: 1, excluded: 0, ambiguous: 0,
|
|
});
|
|
await expect(physical.service.addManual(
|
|
physical.database.id,
|
|
physical.endpoint("orders", "user_id").columnId,
|
|
physical.endpoint("users", "id").columnId,
|
|
)).rejects.toBeInstanceOf(LogicalRelationshipDuplicateError);
|
|
});
|
|
|
|
test("validates target uniqueness and canonical type compatibility for manual relationships", async () => {
|
|
const { database, service, endpoint } = await setup(schema(
|
|
["users", "composite", "events"],
|
|
[column("users", "id", 1, "bigint", 1),
|
|
column("composite", "left_id", 1, "bigint", 1), column("composite", "right_id", 2, "bigint", 2),
|
|
column("events", "id", 1, "bigint", 1), column("events", "user_id", 2, "integer", null),
|
|
column("events", "composite_id", 3, "bigint", null)],
|
|
));
|
|
await expect(service.addManual(
|
|
database.id, endpoint("events", "composite_id").columnId, endpoint("composite", "left_id").columnId,
|
|
)).rejects.toBeInstanceOf(LogicalRelationshipTargetNotUniqueError);
|
|
await expect(service.addManual(
|
|
database.id, endpoint("events", "user_id").columnId, endpoint("users", "id").columnId,
|
|
)).rejects.toBeInstanceOf(LogicalRelationshipTypeIncompatibleError);
|
|
});
|
|
|
|
test("rebuild is additive when an existing generated relationship stops matching", async () => {
|
|
const initial = schema(
|
|
["users", "orders"],
|
|
[column("users", "id", 1, "bigint", 1), column("orders", "id", 1, "bigint", 1),
|
|
column("orders", "user_id", 2, "bigint", null)],
|
|
);
|
|
const { repository, database, service } = await setup(initial);
|
|
await service.rebuildGenerated(database.id);
|
|
const changed = structuredClone(initial);
|
|
changed.columns.find((item) => item.tableName === "orders" && item.name === "user_id")!.dataType = "text";
|
|
await repository.applySchemaSync(database.id, database.version, "columns", [], changed);
|
|
await expect(service.rebuildGenerated(database.id)).resolves.toEqual({
|
|
added: 0, alreadyPresent: 0, excluded: 0, ambiguous: 0,
|
|
});
|
|
expect((await service.list(database.id)).filter((item) => item.origin === "generated")).toHaveLength(1);
|
|
});
|
|
|
|
test("refuses inference until the current database version has a full schema sync", async () => {
|
|
const repository = new MemoryCatalogRepository();
|
|
const database = await repository.create({
|
|
workspaceId: "unsynced-relationships",
|
|
engine: "postgres",
|
|
databaseName: "warehouse",
|
|
schema: "public",
|
|
binding: { transport: "postgres_direct", host: "db.internal", port: 5432, username: "reader" },
|
|
});
|
|
const service = new CatalogLogicalRelationshipService(repository);
|
|
|
|
await expect(service.rebuildGenerated(database.id))
|
|
.rejects.toBeInstanceOf(LogicalRelationshipSchemaStaleError);
|
|
});
|