Skip to content

Database governance built for humans and agents — controlling changes and access across every major database.

19.9 KB
Raw Blame
// @vitest-environment node
import { create } from "@bufbuild/protobuf";
import { describe, expect, test, vi } from "vitest";
import {
  CheckConstraintMetadataSchema,
  ColumnMetadataSchema,
  DatabaseMetadataSchema,
  DatabaseSchema$,
  DependencyColumnSchema,
  ExternalTableMetadataSchema,
  ForeignKeyMetadataSchema,
  FunctionMetadataSchema,
  IndexMetadataSchema,
  PackageMetadataSchema,
  ProcedureMetadataSchema,
  SchemaMetadataSchema,
  SequenceMetadataSchema,
  TableMetadataSchema,
  TablePartitionMetadataSchema,
  TriggerMetadataSchema,
  ViewMetadataSchema,
} from "@/types/proto-es/v1/database_service_pb";
import {
  buildDatabaseSchemaTree,
  ExpandableNodeTypes,
  keyForNodeTarget,
  LeafNodeTypes,
  mapTreeNodeByType,
  type NodeTarget,
  type NodeType,
  readableTextForNodeTarget,
  type TextTarget,
  type TreeNode,
} from "./schemaTree";

vi.mock("@/lib/i18n", () => ({
  default: { t: (key: string) => key },
}));

const DATABASE = "instances/i1/databases/db1";

describe("keyForNodeTarget", () => {
  test("database key is the database resource name", () => {
    expect(keyForNodeTarget("database", { database: DATABASE })).toBe(DATABASE);
  });

  test("schema key joins under the database", () => {
    expect(
      keyForNodeTarget("schema", { database: DATABASE, schema: "public" })
    ).toBe(`${DATABASE}/schemas/public`);
  });

  test("table key joins under the schema", () => {
    expect(
      keyForNodeTarget("table", {
        database: DATABASE,
        schema: "public",
        table: "users",
      })
    ).toBe(`${DATABASE}/schemas/public/tables/users`);
  });

  test("external-table key joins under the schema", () => {
    expect(
      keyForNodeTarget("external-table", {
        database: DATABASE,
        schema: "ext",
        externalTable: "logs",
      })
    ).toBe(`${DATABASE}/schemas/ext/externalTables/logs`);
  });

  test("view key joins under the schema", () => {
    expect(
      keyForNodeTarget("view", {
        database: DATABASE,
        schema: "public",
        view: "active_users",
      })
    ).toBe(`${DATABASE}/schemas/public/views/active_users`);
  });

  test("procedure key uses keyWithPosition under the schema", () => {
    expect(
      keyForNodeTarget("procedure", {
        database: DATABASE,
        schema: "public",
        procedure: "do_thing",
        position: 0,
      })
    ).toBe(`${DATABASE}/schemas/public/procedures/do_thing###0`);
  });

  test("package key uses keyWithPosition under the schema", () => {
    expect(
      keyForNodeTarget("package", {
        database: DATABASE,
        schema: "public",
        package: "pkg",
        position: 1,
      })
    ).toBe(`${DATABASE}/schemas/public/packages/pkg###1`);
  });

  test("function key uses keyWithPosition under the schema", () => {
    expect(
      keyForNodeTarget("function", {
        database: DATABASE,
        schema: "public",
        function: "fn",
        position: 2,
      })
    ).toBe(`${DATABASE}/schemas/public/functions/fn###2`);
  });

  test("sequence key uses keyWithPosition under the schema", () => {
    expect(
      keyForNodeTarget("sequence", {
        database: DATABASE,
        schema: "public",
        sequence: "seq",
        position: 3,
      })
    ).toBe(`${DATABASE}/schemas/public/sequences/seq###3`);
  });

  test("trigger key uses keyWithPosition under the table", () => {
    expect(
      keyForNodeTarget("trigger", {
        database: DATABASE,
        schema: "public",
        table: "users",
        trigger: "trg",
        position: 4,
      })
    ).toBe(`${DATABASE}/schemas/public/tables/users/triggers/trg###4`);
  });

  test("index key joins under the table", () => {
    expect(
      keyForNodeTarget("index", {
        database: DATABASE,
        schema: "public",
        table: "users",
        index: "ix_email",
      })
    ).toBe(`${DATABASE}/schemas/public/tables/users/indexes/ix_email`);
  });

  test("foreign-key joins under the table", () => {
    expect(
      keyForNodeTarget("foreign-key", {
        database: DATABASE,
        schema: "public",
        table: "users",
        foreignKey: "fk_org",
      })
    ).toBe(`${DATABASE}/schemas/public/tables/users/foreignKeys/fk_org`);
  });

  test("check joins under the table", () => {
    expect(
      keyForNodeTarget("check", {
        database: DATABASE,
        schema: "public",
        table: "users",
        check: "ck_age",
      })
    ).toBe(`${DATABASE}/schemas/public/tables/users/checks/ck_age`);
  });

  test("partition-table joins under the table", () => {
    expect(
      keyForNodeTarget("partition-table", {
        database: DATABASE,
        schema: "public",
        table: "events",
        partition: "p2026",
      })
    ).toBe(`${DATABASE}/schemas/public/tables/events/partitionTables/p2026`);
  });

  test("dependency-column joins under the view", () => {
    const dep = create(DependencyColumnSchema, {
      schema: "public",
      table: "users",
      column: "id",
    });
    expect(
      keyForNodeTarget("dependency-column", {
        database: DATABASE,
        schema: "public",
        view: "active_users",
        dependencyColumn: dep,
      })
    ).toBe(
      `${DATABASE}/schemas/public/views/active_users/dependencyColumns/public/users/id`
    );
  });

  test("column under a table joins under the table", () => {
    expect(
      keyForNodeTarget("column", {
        database: DATABASE,
        schema: "public",
        table: "users",
        column: "id",
      })
    ).toBe(`${DATABASE}/schemas/public/tables/users/columns/id`);
  });

  test("column under an external table joins under the external table", () => {
    expect(
      keyForNodeTarget("column", {
        database: DATABASE,
        schema: "public",
        externalTable: "remote_users",
        column: "id",
      })
    ).toBe(`${DATABASE}/schemas/public/externalTables/remote_users/columns/id`);
  });

  test("column under a view joins under the view", () => {
    expect(
      keyForNodeTarget("column", {
        database: DATABASE,
        schema: "public",
        view: "active_users",
        column: "id",
      })
    ).toBe(`${DATABASE}/schemas/public/views/active_users/columns/id`);
  });

  test("expandable-text + error keys join type/id", () => {
    expect(
      keyForNodeTarget("expandable-text", {
        id: "x/y/tables",
        expandable: true,
        text: () => "Tables",
      })
    ).toBe("expandable-text/x/y/tables");
    expect(
      keyForNodeTarget("error", {
        id: "x/y/dummy-table-0",
        expandable: false,
        text: () => "",
      })
    ).toBe("error/x/y/dummy-table-0");
  });
});

describe("readableTextForNodeTarget", () => {
  test("error nodes are empty (so search ignores them)", () => {
    expect(
      readableTextForNodeTarget("error", {
        id: "x",
        expandable: false,
        text: () => "anything",
      })
    ).toBe("");
  });

  test("non-searchable expandable-text returns empty", () => {
    expect(
      readableTextForNodeTarget("expandable-text", {
        id: "x",
        expandable: true,
        text: () => "Tables",
      })
    ).toBe("");
  });

  test("searchable expandable-text returns the text() value", () => {
    expect(
      readableTextForNodeTarget("expandable-text", {
        id: "x",
        expandable: true,
        text: () => "Tables",
        searchable: true,
      })
    ).toBe("Tables");
  });

  test("dependency-column joins schema.table.column", () => {
    const dep = create(DependencyColumnSchema, {
      schema: "public",
      table: "users",
      column: "id",
    });
    expect(
      readableTextForNodeTarget("dependency-column", {
        database: DATABASE,
        schema: "public",
        view: "v",
        dependencyColumn: dep,
      })
    ).toBe("public.users.id");
  });

  test("dependency-column without schema joins table.column only", () => {
    const dep = create(DependencyColumnSchema, {
      table: "users",
      column: "id",
    });
    expect(
      readableTextForNodeTarget("dependency-column", {
        database: DATABASE,
        schema: "public",
        view: "v",
        dependencyColumn: dep,
      })
    ).toBe("users.id");
  });

  test("default case returns the last path segment", () => {
    expect(
      readableTextForNodeTarget("table", {
        database: DATABASE,
        schema: "public",
        table: "users",
      })
    ).toBe("users");
  });
});

describe("LeafNodeTypes / ExpandableNodeTypes", () => {
  test("type unions partition cleanly", () => {
    const allTypes: NodeType[] = [
      "database",
      "schema",
      "table",
      "external-table",
      "view",
      "procedure",
      "function",
      "sequence",
      "trigger",
      "package",
      "partition-table",
      "column",
      "index",
      "foreign-key",
      "check",
      "dependency-column",
      "expandable-text",
      "error",
    ];
    for (const t of allTypes) {
      expect(
        LeafNodeTypes.includes(t) ||
          ExpandableNodeTypes.includes(t) ||
          [
            "database",
            "schema",
            "table",
            "view",
            "external-table",
            "partition-table",
            "expandable-text",
          ].includes(t) ||
          LeafNodeTypes.includes(t)
      ).toBe(true);
    }
  });

  test("mapTreeNodeByType marks leaves correctly", () => {
    const colNode = mapTreeNodeByType("column", {
      database: DATABASE,
      schema: "public",
      table: "users",
      column: "id",
    });
    expect(colNode.isLeaf).toBe(true);

    const schemaNode = mapTreeNodeByType("schema", {
      database: DATABASE,
      schema: "public",
    });
    expect(schemaNode.isLeaf).toBe(false);
  });
});

// ---------- buildDatabaseSchemaTree ----------

const makeDatabase = () => create(DatabaseSchema$, { name: DATABASE });

const makeColumn = (name: string) => create(ColumnMetadataSchema, { name });

const makeIndex = (name: string) => create(IndexMetadataSchema, { name });

const makeForeignKey = (name: string) =>
  create(ForeignKeyMetadataSchema, { name });

const makeCheck = (name: string) =>
  create(CheckConstraintMetadataSchema, { name });

const makePartition = (name: string, subs: string[] = []) =>
  create(TablePartitionMetadataSchema, {
    name,
    subpartitions: subs.map((n) =>
      create(TablePartitionMetadataSchema, { name: n })
    ),
  });

const makeTable = (
  name: string,
  opts: Partial<{
    columns: string[];
    indexes: string[];
    foreignKeys: string[];
    checks: string[];
    triggers: string[];
    partitions: ReturnType<typeof makePartition>[];
  }> = {}
) =>
  create(TableMetadataSchema, {
    name,
    columns: (opts.columns ?? []).map(makeColumn),
    indexes: (opts.indexes ?? []).map(makeIndex),
    foreignKeys: (opts.foreignKeys ?? []).map(makeForeignKey),
    checkConstraints: (opts.checks ?? []).map(makeCheck),
    triggers: (opts.triggers ?? []).map((n) =>
      create(TriggerMetadataSchema, { name: n })
    ),
    partitions: opts.partitions ?? [],
  });

const makeView = (name: string, columns: string[] = []) =>
  create(ViewMetadataSchema, {
    name,
    columns: columns.map(makeColumn),
  });

const makeExternalTable = (name: string, columns: string[] = []) =>
  create(ExternalTableMetadataSchema, {
    name,
    columns: columns.map(makeColumn),
  });

const makeSchema = (
  name: string,
  opts: Partial<{
    tables: ReturnType<typeof makeTable>[];
    externalTables: ReturnType<typeof makeExternalTable>[];
    views: ReturnType<typeof makeView>[];
    procedures: string[];
    functions: string[];
    sequences: string[];
    packages: string[];
  }> = {}
) =>
  create(SchemaMetadataSchema, {
    name,
    tables: opts.tables ?? [],
    externalTables: opts.externalTables ?? [],
    views: opts.views ?? [],
    procedures: (opts.procedures ?? []).map((n) =>
      create(ProcedureMetadataSchema, { name: n })
    ),
    functions: (opts.functions ?? []).map((n) =>
      create(FunctionMetadataSchema, { name: n })
    ),
    sequences: (opts.sequences ?? []).map((n) =>
      create(SequenceMetadataSchema, { name: n })
    ),
    packages: (opts.packages ?? []).map((n) =>
      create(PackageMetadataSchema, { name: n })
    ),
  });

const makeMetadata = (schemas: ReturnType<typeof makeSchema>[]) =>
  create(DatabaseMetadataSchema, { schemas });

// Walk a TreeNode tree and yield (key, type) for every node.
function* walk(
  nodes: TreeNode[]
): Generator<{ key: string; type: NodeType; label: string | undefined }> {
  for (const n of nodes) {
    yield { key: n.key, type: n.meta.type, label: n.label };
    if (n.children) yield* walk(n.children);
  }
}

const findKey = (nodes: TreeNode[], predicate: (k: string) => boolean) =>
  [...walk(nodes)].find((n) => predicate(n.key));

describe("buildDatabaseSchemaTree", () => {
  test("empty metadata returns a dummy 'empty' placeholder", () => {
    const tree = buildDatabaseSchemaTree(makeDatabase(), makeMetadata([]));
    expect(tree).toHaveLength(1);
    expect(tree[0].meta.type).toBe("error");
    expect(tree[0].disabled).toBe(true);
  });

  test("single anonymous schema (MySQL-style) flattens schema's children to root", () => {
    const md = makeMetadata([
      makeSchema("", {
        tables: [makeTable("users", { columns: ["id", "email"] })],
      }),
    ]);
    const tree = buildDatabaseSchemaTree(makeDatabase(), md);
    // Top-level should be the "Tables" expandable folder, not a schema node.
    expect(tree[0].meta.type).toBe("expandable-text");
    const tablesFolder = tree[0];
    expect(tablesFolder.children?.[0].meta.type).toBe("table");
    expect(tablesFolder.children?.[0].key).toBe(
      `${DATABASE}/schemas//tables/users`
    );
  });

  test("multi-schema (Postgres-style) wraps children in schema nodes", () => {
    const md = makeMetadata([
      makeSchema("public", { tables: [makeTable("users")] }),
      makeSchema("audit", { tables: [makeTable("events")] }),
    ]);
    const tree = buildDatabaseSchemaTree(makeDatabase(), md);
    expect(tree).toHaveLength(2);
    expect(tree[0].meta.type).toBe("schema");
    expect(tree[0].key).toBe(`${DATABASE}/schemas/public`);
    expect(tree[1].key).toBe(`${DATABASE}/schemas/audit`);
  });

  test("a table builds Columns / Indexes / FKs / Checks / Triggers / Partitions folders only when populated", () => {
    const md = makeMetadata([
      makeSchema("public", {
        tables: [
          makeTable("users", {
            columns: ["id"],
            indexes: ["ix_id"],
            foreignKeys: ["fk_org"],
            checks: ["ck_age"],
            triggers: ["trg_audit"],
            partitions: [makePartition("p1")],
          }),
          makeTable("plain", { columns: ["x"] }),
        ],
      }),
    ]);
    const tree = buildDatabaseSchemaTree(makeDatabase(), md);
    const usersTable = findKey(
      tree,
      (k) => k === `${DATABASE}/schemas/public/tables/users`
    );
    expect(usersTable).toBeDefined();

    const plainTable = [...walk(tree)].filter(
      (n) => n.key === `${DATABASE}/schemas/public/tables/plain`
    );
    expect(plainTable).toHaveLength(1);

    // Plain table has only the Columns folder.
    const plainNode = tree[0].children![0].children!.find(
      (c) => c.key === `${DATABASE}/schemas/public/tables/plain`
    );
    expect(plainNode!.children).toHaveLength(1);
    expect(plainNode!.children![0].meta.type).toBe("expandable-text");
  });

  test("nested partitions recurse and reuse the table-level key prefix", () => {
    // keyForNodeTarget("partition-table", ...) always keys directly under
    // the table — not under the parent partition. Sub-partitions
    // therefore share the same `<table>/partitionTables/<name>` prefix as
    // top-level partitions; preserving this behavior keeps persisted
    // treeState keys valid.
    const md = makeMetadata([
      makeSchema("public", {
        tables: [
          makeTable("events", {
            columns: ["id"],
            partitions: [makePartition("p2026", ["q1", "q2"])],
          }),
        ],
      }),
    ]);
    const tree = buildDatabaseSchemaTree(makeDatabase(), md);
    const parent = findKey(
      tree,
      (k) =>
        k === `${DATABASE}/schemas/public/tables/events/partitionTables/p2026`
    );
    expect(parent).toBeDefined();
    expect(parent!.type).toBe("partition-table");

    // The sub-partitions are children of `parent` but key directly under
    // the table (not nested under `p2026`).
    const q1 = findKey(
      tree,
      (k) => k === `${DATABASE}/schemas/public/tables/events/partitionTables/q1`
    );
    expect(q1).toBeDefined();
    expect(q1!.type).toBe("partition-table");
  });

  test("empty Tables folder still emits an <Empty> child", () => {
    const md = makeMetadata([
      makeSchema("public", {
        tables: [],
        views: [makeView("v")],
      }),
    ]);
    const tree = buildDatabaseSchemaTree(makeDatabase(), md);
    const tablesFolder = tree[0].children!.find(
      (c) =>
        c.meta.type === "expandable-text" &&
        (c.meta.target as TextTarget).id === `${DATABASE}/schemas/public/table`
    );
    expect(tablesFolder).toBeDefined();
    expect(tablesFolder!.children![0].meta.type).toBe("error");
  });

  test("every node key is non-empty and unique (react-arborist throws on falsy ids)", () => {
    const md = makeMetadata([
      makeSchema("public", {
        tables: [
          makeTable("users", {
            columns: ["id"],
            indexes: ["ix"],
            foreignKeys: ["fk"],
            checks: ["ck"],
            triggers: ["trg"],
            partitions: [makePartition("p1", ["sp1"])],
          }),
        ],
        externalTables: [makeExternalTable("ext", ["c1", "c2"])],
        views: [makeView("v", ["vc"])],
        procedures: ["proc"],
        functions: ["fn"],
        sequences: ["seq"],
        packages: ["pkg"],
      }),
    ]);
    const tree = buildDatabaseSchemaTree(makeDatabase(), md);
    const seen = new Set<string>();
    for (const { key, type } of walk(tree)) {
      expect(key, `falsy key on node type=${type}`).toBeTruthy();
      expect(seen.has(key), `duplicate key: ${key}`).toBe(false);
      seen.add(key);
    }
  });

  test("external tables / views / procedures / functions / packages / sequences only appear when populated", () => {
    const md = makeMetadata([
      makeSchema("public", {
        tables: [makeTable("t")],
        externalTables: [makeExternalTable("ext_t", ["c"])],
        views: [makeView("v", ["c"])],
        procedures: ["p"],
        functions: ["f"],
        packages: ["pk"],
        sequences: ["s"],
      }),
    ]);
    const tree = buildDatabaseSchemaTree(makeDatabase(), md);
    const folderTypes = tree[0].children!.map(
      (c) => (c.meta.target as TextTarget).mockType
    );
    expect(folderTypes).toEqual([
      "table",
      "external-table",
      "view",
      "procedure",
      "package",
      "function",
      "sequence",
    ]);
  });

  test("dependency-column children would key under the view (synthetic check)", () => {
    // dependency-column is built externally (lineage feature) but the key
    // path must remain stable. Verify keyForNodeTarget output directly.
    const dep = create(DependencyColumnSchema, {
      schema: "ext",
      table: "raw_users",
      column: "id",
    });
    expect(
      keyForNodeTarget("dependency-column", {
        database: DATABASE,
        schema: "public",
        view: "active",
        dependencyColumn: dep,
      })
    ).toBe(
      `${DATABASE}/schemas/public/views/active/dependencyColumns/ext/raw_users/id`
    );
  });
});

// Compile-time check that NodeTarget specializations remain assignable.
const _typeCheckTargets: {
  database: NodeTarget<"database">;
  schema: NodeTarget<"schema">;
  trigger: NodeTarget<"trigger">;
  expandableText: NodeTarget<"expandable-text">;
} = {
  database: { database: DATABASE },
  schema: { database: DATABASE, schema: "public" },
  trigger: {
    database: DATABASE,
    schema: "public",
    table: "t",
    trigger: "trg",
    position: 0,
  },
  expandableText: {
    id: "x",
    expandable: true,
    text: () => "x",
  },
};
void _typeCheckTargets;