Skip to content

armgit / dbx

Public Imported from GitHub
0

25 MB lightweight cross-platform database client for 100+ databases, including MySQL, PostgreSQL, SQLite, Redis, MongoDB, DuckDB, SQL Server, and Dameng. Built-in AI, MCP Server, CLI, desktop and Docker. | 轻量级跨平台数据库管理工具,支持 MySQL、PostgreSQL、SQLite、Redis、MongoDB、达梦等 100+ 数据库,提供桌面端、Docker、CLI、内置 AI 助手和 MCP。

37.9 KB
Raw Blame
import { beforeEach, describe, expect, it, vi } from "vitest";

const apiMock = vi.hoisted(() => ({
  getColumns: vi.fn(),
  getTableComment: vi.fn(),
  listIndexes: vi.fn(),
  listForeignKeys: vi.fn(),
  listTriggers: vi.fn(),
  buildCreateTableSql: vi.fn(),
  buildDuplicateTableStructureSql: vi.fn(),
  buildMysqlAutoIncrementSql: vi.fn(),
  buildVacuumTableSql: vi.fn(),
}));

vi.mock("@/lib/backend/api", () => apiMock);

import {
  buildDuplicateTableStructurePlan,
  buildVacuumTableSql,
  buildMysqlAutoIncrementSql,
  collectDuplicateTableColumnComments,
  damengDropSchemaExecutionSchema,
  damengDuplicateTableCreateOptions,
  duplicateTableStructureRequiresScript,
  oracleDuplicateTableCreateOptions,
  sqlServerClonePrimaryKeyConstraintName,
  SQLSERVER_IDENTIFIER_MAX_LENGTH,
  supportsMysqlAutoIncrement,
} from "@/lib/database/dbAdminSql";

beforeEach(() => {
  vi.clearAllMocks();
});

describe("MySQL AUTO_INCREMENT administration", () => {
  it("is available for supported MySQL-family connections", () => {
    for (const connection of [
      { db_type: "mysql", driver_profile: undefined },
      { db_type: "mysql", driver_profile: "" },
      { db_type: "mysql", driver_profile: " MySQL " },
      { db_type: "mysql", driver_profile: "mariadb" },
      { db_type: "mysql", driver_profile: "tidb" },
      { db_type: "mysql", driver_profile: "oceanbase" },
      { db_type: "mysql", driver_profile: "tdsql" },
      { db_type: "mysql", driver_profile: "polardb" },
      { db_type: "mysql", driver_profile: "greatsql" },
      { db_type: "mysql", driver_profile: "custom_mysql" },
      { db_type: "mysql", driver_profile: "dolt" },
      { db_type: "mysql", driver_profile: "goldendb" },
      { db_type: "goldendb", driver_profile: "goldendb" },
    ] as const) {
      expect(supportsMysqlAutoIncrement(connection as any), JSON.stringify(connection)).toBe(true);
    }

    for (const connection of [
      { db_type: "jdbc", driver_profile: "mysql" },
      { db_type: "mysql", driver_profile: "unknown" },
      { db_type: "doris", driver_profile: "doris" },
      { db_type: "mysql", driver_profile: "doris" },
      { db_type: "mysql", driver_profile: "selectdb" },
      { db_type: "mysql", driver_profile: "starrocks" },
      { db_type: "mysql", driver_profile: "manticoresearch" },
      { db_type: "doris", driver_profile: "selectdb" },
      { db_type: "starrocks", driver_profile: "starrocks" },
    ] as const) {
      expect(supportsMysqlAutoIncrement(connection as any), JSON.stringify(connection)).toBe(false);
    }
  });

  it("keeps the counter as a decimal string across the frontend boundary", async () => {
    apiMock.buildMysqlAutoIncrementSql.mockResolvedValue("ALTER TABLE `sales`.`events` AUTO_INCREMENT = 18446744073709551615;");

    await expect(
      buildMysqlAutoIncrementSql({
        databaseType: "mysql",
        driverProfile: "mysql",
        schema: "sales",
        tableName: "events",
        value: "18446744073709551615",
      }),
    ).resolves.toContain("18446744073709551615");
    expect(apiMock.buildMysqlAutoIncrementSql).toHaveBeenCalledWith(expect.objectContaining({ value: "18446744073709551615" }));
  });
});

describe("PostgreSQL-family VACUUM administration", () => {
  it("forwards independent FULL and ANALYZE options", async () => {
    apiMock.buildVacuumTableSql.mockResolvedValue('VACUUM FULL ANALYZE "public"."events";');

    await expect(
      buildVacuumTableSql({
        databaseType: "postgres",
        schema: "public",
        tableName: "events",
        full: true,
        analyze: true,
      }),
    ).resolves.toBe('VACUUM FULL ANALYZE "public"."events";');
    expect(apiMock.buildVacuumTableSql).toHaveBeenCalledWith({
      databaseType: "postgres",
      schema: "public",
      tableName: "events",
      full: true,
      analyze: true,
    });
  });
});

describe("collectDuplicateTableColumnComments", () => {
  it("preserves meaningful whitespace and excludes whitespace-only comments", () => {
    expect(
      collectDuplicateTableColumnComments([
        { name: "LEADING", comment: "  leading" },
        { name: "TRAILING", comment: "trailing  " },
        { name: "BOTH", comment: "  Owner's; display name  " },
        { name: "WHITESPACE_ONLY", comment: " \t\n" },
        { name: "EMPTY", comment: "" },
        { name: "NULL", comment: null },
      ]),
    ).toEqual([
      { name: "LEADING", comment: "  leading" },
      { name: "TRAILING", comment: "trailing  " },
      { name: "BOTH", comment: "  Owner's; display name  " },
    ]);
  });
});

describe("duplicateTableStructureRequiresScript", () => {
  it("detects generated table and column comment statements", () => {
    expect(duplicateTableStructureRequiresScript('CREATE TABLE "copy" (LIKE "source" INCLUDING ALL);\nCOMMENT ON TABLE "copy" IS \'orders\';')).toBe(true);
    expect(duplicateTableStructureRequiresScript('CREATE TABLE "copy" AS SELECT * FROM "source" WHERE 1=0;\nCOMMENT ON COLUMN "copy"."id" IS \'identifier\';')).toBe(true);
  });

  it("keeps single-statement structure copies on the query path", () => {
    expect(duplicateTableStructureRequiresScript('CREATE TABLE "copy" (LIKE "source" INCLUDING ALL);')).toBe(false);
  });
});

describe("oracleDuplicateTableCreateOptions", () => {
  it("preserves metadata while assigning non-conflicting dependent object names", () => {
    const options = oracleDuplicateTableCreateOptions({
      schema: "HR",
      targetName: "CUSTOMER_ORDERS_ARCHIVE_COPY",
      tableComment: "orders archive",
      columns: [
        {
          name: "ID",
          data_type: "NUMBER",
          is_nullable: false,
          column_default: "42",
          is_primary_key: true,
          comment: "identifier",
        },
      ] as any,
      indexes: [
        { name: "PK_CUSTOMER_ORDERS", columns: ["ID"], is_unique: true, is_primary: true },
        { name: "IDX_CUSTOMER_ORDERS_ID", columns: ["ID"], is_unique: false, is_primary: false },
      ] as any,
      foreignKeys: [{ name: "FK_CUSTOMER", column: "ID", ref_table: "CUSTOMERS", ref_column: "ID" }] as any,
      triggers: [{ name: "TRG_CUSTOMER_ORDERS", timing: "BEFORE EACH ROW", event: "INSERT", statement: "BEGIN NULL; END;" }] as any,
    });

    expect(options.tableName).toBe("CUSTOMER_ORDERS_ARCHIVE_COPY");
    expect(options.tableComment).toBe("orders archive");
    expect(options.columns[0]).toMatchObject({ name: "ID", defaultValue: "42", isPrimaryKey: true, comment: "identifier", original: undefined });
    expect(options.indexes).toHaveLength(1);
    expect(options.indexes[0]?.name).toMatch(/_IDX1$/);
    expect(options.indexes[0]?.name.length).toBeLessThanOrEqual(30);
    expect(options.foreignKeys?.[0]?.name).toMatch(/_FK1$/);
    expect(options.triggers?.[0]?.name).toMatch(/_TRG1$/);
    expect(options.foreignKeys?.[0]?.original).toBeUndefined();
    expect(options.triggers?.[0]?.original).toBeUndefined();
  });
});

describe("damengDuplicateTableCreateOptions", () => {
  it("preserves columns, comments, primary keys, and independent supported indexes", () => {
    const options = damengDuplicateTableCreateOptions({
      schema: "APP",
      targetName: "orders_copy",
      tableComment: "orders clone",
      columns: [
        {
          name: "ID",
          data_type: "INT",
          is_nullable: false,
          column_default: "42",
          is_primary_key: true,
          extra: "IDENTITY(10, 2)",
          comment: "identifier",
        },
        {
          name: "TENANT_ID",
          data_type: "INT",
          is_nullable: false,
          column_default: null,
          is_primary_key: false,
          extra: null,
          comment: null,
        },
        {
          name: "EMAIL",
          data_type: "VARCHAR(255)",
          is_nullable: true,
          column_default: "'unknown'",
          is_primary_key: false,
          extra: null,
          comment: "email address",
        },
      ] as any,
      indexes: [
        { name: "PK_ORDERS", columns: ["ID"], is_unique: true, is_primary: true, index_type: "NORMAL" },
        { name: "IDX_ORDERS_TENANT_EMAIL", columns: ["TENANT_ID", "EMAIL"], is_unique: false, is_primary: false, index_type: "NORMAL" },
        { name: "UX_ORDERS_EMAIL_TENANT", columns: ["EMAIL", "TENANT_ID"], is_unique: true, is_primary: false, index_type: "NORMAL" },
        { name: "BMX_ORDERS_TENANT", columns: ["TENANT_ID"], is_unique: false, is_primary: false, index_type: "BITMAP" },
        { name: "SYS_INNER_ORDERS", columns: ["ID"], is_unique: false, is_primary: false, index_type: "INNER CLUSTER INDEX" },
        { name: "IDX_ORDERS_DOMAIN", columns: ["EMAIL"], is_unique: false, is_primary: false, index_type: "DOMAIN" },
      ] as any,
    });

    expect(options).toMatchObject({ databaseType: "dameng", schema: "APP", tableName: "ORDERS_COPY", tableComment: "orders clone" });
    expect(options.columns).toHaveLength(3);
    expect(options.columns[0]).toMatchObject({
      name: "ID",
      defaultValue: "42",
      isNullable: false,
      isPrimaryKey: true,
      comment: "identifier",
      extra: { autoIncrement: true, identity: { seed: 10, increment: 2 } },
      original: undefined,
      originalPosition: undefined,
    });
    expect(options.columns[2]).toMatchObject({ name: "EMAIL", defaultValue: "'unknown'", isNullable: true, comment: "email address" });
    expect(options.indexes).toHaveLength(3);
    expect(options.indexes.map((index) => index.columns)).toEqual([["TENANT_ID", "EMAIL"], ["EMAIL", "TENANT_ID"], ["TENANT_ID"]]);
    expect(options.indexes.map((index) => index.isUnique)).toEqual([false, true, false]);
    expect(options.indexes.map((index) => index.indexType)).toEqual(["NORMAL", "NORMAL", "BITMAP"]);
    expect(options.indexes.map((index) => index.name)).toEqual(["ORDERS_COPY_IDX1", "ORDERS_COPY_IDX2", "ORDERS_COPY_IDX3"]);
    expect(options.indexes.every((index) => index.original === undefined && index.isPrimary === false)).toBe(true);
    expect(options.foreignKeys).toEqual([]);
    expect(options.triggers).toEqual([]);
  });

  it("keeps deterministic index names stable when unsupported metadata is interleaved", () => {
    const columns = [{ name: "ID", data_type: "INT", is_nullable: false, column_default: null, is_primary_key: false, extra: null }] as any;
    const supportedIndexes = [
      { name: "IDX_ONE", columns: ["ID"], is_unique: false, is_primary: false, index_type: "NORMAL" },
      { name: "IDX_TWO", columns: ["ID"], is_unique: true, is_primary: false, index_type: "BITMAP" },
    ] as any;
    const interleavedIndexes = [supportedIndexes[0], { name: "SYS_INNER", columns: ["ID"], is_unique: false, is_primary: false, index_type: "INTERNAL INDEX" }, { name: "IDX_DOMAIN", columns: ["ID"], is_unique: false, is_primary: false, index_type: "DOMAIN" }, supportedIndexes[1]] as any;

    const expected = damengDuplicateTableCreateOptions({ targetName: "ORDERS_COPY", columns, indexes: supportedIndexes });
    const actual = damengDuplicateTableCreateOptions({ targetName: "ORDERS_COPY", columns, indexes: interleavedIndexes });

    expect(actual.indexes.map((index) => index.name)).toEqual(expected.indexes.map((index) => index.name));
    expect(actual.indexes.map((index) => index.indexType)).toEqual(["NORMAL", "BITMAP"]);
  });

  it("generates bounded, distinct names for long, quoted, and mixed-case targets", () => {
    const targetName = `Order"Archive_${"副本".repeat(70)}_mixedCase`;
    const options = damengDuplicateTableCreateOptions({
      targetName,
      columns: [{ name: "ID", data_type: "INT", is_nullable: false, column_default: null, is_primary_key: false, extra: null }] as any,
      indexes: [
        { name: "IDX_ONE", columns: ["ID"], is_unique: false, is_primary: false, index_type: "NORMAL" },
        { name: "IDX_TWO", columns: ["ID"], is_unique: false, is_primary: false, index_type: null },
      ] as any,
    });

    expect(options.tableName).toBe(targetName);
    expect(options.indexes).toHaveLength(2);
    expect(new Set(options.indexes.map((index) => index.name)).size).toBe(2);
    expect(options.indexes.every((index) => Array.from(index.name).length <= 128)).toBe(true);
    expect(options.indexes[0]?.name).toMatch(/_IDX1$/);
    expect(options.indexes[1]?.name).toMatch(/_IDX2$/);
  });

  it("keeps a no-index clone as a structured empty-table definition", () => {
    const options = damengDuplicateTableCreateOptions({
      targetName: "EMPTY_COPY",
      tableComment: "empty clone",
      columns: [{ name: "ID", data_type: "INT", is_nullable: true, column_default: null, is_primary_key: false, extra: null, comment: "identifier" }] as any,
      indexes: [],
    });

    expect(options.columns[0]).toMatchObject({ name: "ID", comment: "identifier" });
    expect(options.indexes).toEqual([]);
    expect(options.tableComment).toBe("empty clone");
  });

  it("keeps unquoted Dameng target folding compatible with the data-copy path", () => {
    const columns = [{ name: "ID", data_type: "INT", is_nullable: false, column_default: null, is_primary_key: false, extra: null }] as any;

    expect(damengDuplicateTableCreateOptions({ targetName: "orders_copy", columns, indexes: [] }).tableName).toBe("ORDERS_COPY");
    expect(damengDuplicateTableCreateOptions({ targetName: "OrdersCopy", columns, indexes: [] }).tableName).toBe("OrdersCopy");
  });
});

describe("buildDuplicateTableStructurePlan", () => {
  it("refuses OceanBase structure cloning when source column metadata cannot be read", async () => {
    apiMock.getColumns.mockRejectedValueOnce(new Error("source columns inaccessible"));
    apiMock.buildDuplicateTableStructureSql.mockResolvedValue('CREATE TABLE "APP"."COPY" AS SELECT * FROM "APP"."SOURCE" WHERE 1=0;');
    await expect(buildDuplicateTableStructurePlan({ connectionId: "ob-clone", database: "APP", databaseType: "oceanbase-oracle", schema: "APP", sourceName: "SOURCE", targetName: "COPY" })).rejects.toThrow("source columns inaccessible");
    expect(apiMock.buildDuplicateTableStructureSql).not.toHaveBeenCalled();
  });

  it("loads Oracle metadata through the list APIs and builds a script", async () => {
    const columns = [{ name: "ID", data_type: "NUMBER", is_nullable: false, column_default: "42", is_primary_key: true }];
    const indexes = [{ name: "IDX_ORDERS_ID", columns: ["ID"], is_unique: false, is_primary: false }];
    const foreignKeys = [{ name: "FK_ORDERS_CUSTOMER", column: "ID", ref_schema: "CRM", ref_table: "CUSTOMERS", ref_column: "ID", on_delete: "CASCADE" }];
    const triggers = [{ name: "TRG_ORDERS", timing: "BEFORE EACH ROW", event: "INSERT", statement: "BEGIN NULL; END;" }];
    apiMock.getColumns.mockResolvedValue(columns);
    apiMock.getTableComment.mockResolvedValue("orders");
    apiMock.listIndexes.mockResolvedValue(indexes);
    apiMock.listForeignKeys.mockResolvedValue(foreignKeys);
    apiMock.listTriggers.mockResolvedValue(triggers);
    apiMock.buildCreateTableSql.mockResolvedValue({ statements: ["CREATE TABLE ...;", "CREATE INDEX ...;"], warnings: [] });

    const plan = await buildDuplicateTableStructurePlan({
      connectionId: "oracle-1",
      database: "XEPDB1",
      databaseType: "oracle",
      schema: "HR",
      sourceName: "ORDERS",
      targetName: "ORDERS_COPY",
    });

    expect(apiMock.getColumns).toHaveBeenCalledWith("oracle-1", "XEPDB1", "HR", "ORDERS", undefined);
    expect(apiMock.getTableComment).toHaveBeenCalledWith("oracle-1", "XEPDB1", "HR", "ORDERS", undefined);
    expect(apiMock.listIndexes).toHaveBeenCalledWith("oracle-1", "XEPDB1", "HR", "ORDERS", undefined);
    expect(apiMock.listForeignKeys).toHaveBeenCalledWith("oracle-1", "XEPDB1", "HR", "ORDERS", undefined);
    expect(apiMock.listTriggers).toHaveBeenCalledWith("oracle-1", "XEPDB1", "HR", "ORDERS", undefined);
    expect(apiMock.buildCreateTableSql).toHaveBeenCalledWith(
      expect.objectContaining({
        databaseType: "oracle",
        schema: "HR",
        tableName: "ORDERS_COPY",
        tableComment: "orders",
      }),
    );
    expect(plan).toEqual({ sql: "CREATE TABLE ...;\nCREATE INDEX ...;", sourceColumns: columns, executeAsScript: true });
  });

  it("loads Dameng columns and indexes through the structured DDL builder", async () => {
    const columns = [{ name: "ID", data_type: "INT", is_nullable: false, column_default: null, is_primary_key: true }];
    const indexes = [{ name: "IDX_ORDERS_ID", columns: ["ID"], is_unique: false, is_primary: false, index_type: "NORMAL" }];
    apiMock.getColumns.mockResolvedValue(columns);
    apiMock.listIndexes.mockResolvedValue(indexes);
    apiMock.buildCreateTableSql.mockResolvedValue({ statements: ["CREATE TABLE ...;", "CREATE INDEX ...;"], warnings: [] });
    apiMock.buildDuplicateTableStructureSql.mockResolvedValue('CREATE TABLE "SYSDBA".ORDERS_COPY AS SELECT * FROM "SYSDBA"."ORDERS" WHERE 1=0;');

    const plan = await buildDuplicateTableStructurePlan({
      connectionId: "dameng-1",
      database: "DAMENG",
      databaseType: "dameng",
      schema: "SYSDBA",
      sourceName: "ORDERS",
      targetName: "orders_copy",
    });

    expect(apiMock.getColumns).toHaveBeenCalledWith("dameng-1", "DAMENG", "SYSDBA", "ORDERS", undefined);
    expect(apiMock.listIndexes).toHaveBeenCalledWith("dameng-1", "DAMENG", "SYSDBA", "ORDERS", undefined);
    expect(apiMock.buildCreateTableSql).toHaveBeenCalledWith(
      expect.objectContaining({
        databaseType: "dameng",
        schema: "SYSDBA",
        tableName: "ORDERS_COPY",
      }),
    );
    expect(apiMock.buildDuplicateTableStructureSql).not.toHaveBeenCalled();
    expect(plan).toEqual({ sql: "CREATE TABLE ...;\nCREATE INDEX ...;", sourceColumns: columns, executeAsScript: true });
  });

  it("fails before DDL generation when Dameng column metadata loading fails", async () => {
    apiMock.getColumns.mockRejectedValue(new Error("metadata unavailable"));
    apiMock.listIndexes.mockResolvedValue([]);

    await expect(
      buildDuplicateTableStructurePlan({
        connectionId: "dameng-1",
        database: "DAMENG",
        databaseType: "dameng",
        schema: "SYSDBA",
        sourceName: "SOURCE",
        targetName: "COPY",
      }),
    ).rejects.toThrow("metadata unavailable");

    expect(apiMock.buildCreateTableSql).not.toHaveBeenCalled();
    expect(apiMock.buildDuplicateTableStructureSql).not.toHaveBeenCalled();
  });

  it("fails before DDL generation when Dameng index metadata loading fails", async () => {
    apiMock.getColumns.mockResolvedValue([{ name: "ID", data_type: "INT", is_nullable: false, column_default: null, is_primary_key: false }]);
    apiMock.listIndexes.mockRejectedValue(new Error("index metadata unavailable"));

    await expect(
      buildDuplicateTableStructurePlan({
        connectionId: "dameng-1",
        database: "DAMENG",
        databaseType: "dameng",
        schema: "SYSDBA",
        sourceName: "SOURCE",
        targetName: "COPY",
      }),
    ).rejects.toThrow("index metadata unavailable");

    expect(apiMock.buildCreateTableSql).not.toHaveBeenCalled();
    expect(apiMock.buildDuplicateTableStructureSql).not.toHaveBeenCalled();
  });

  it("fails rather than execute partial Dameng DDL when generation warns", async () => {
    apiMock.getColumns.mockResolvedValue([{ name: "ID", data_type: "INT", is_nullable: false, column_default: null, is_primary_key: false }]);
    apiMock.listIndexes.mockResolvedValue([]);
    apiMock.buildCreateTableSql.mockResolvedValue({ statements: ["CREATE TABLE ...;"], warnings: ["unsupported metadata"] });

    await expect(
      buildDuplicateTableStructurePlan({
        connectionId: "dameng-1",
        database: "DAMENG",
        databaseType: "dameng",
        schema: "SYSDBA",
        sourceName: "SOURCE",
        targetName: "COPY",
      }),
    ).rejects.toThrow("unsupported metadata");

    expect(apiMock.buildDuplicateTableStructureSql).not.toHaveBeenCalled();
  });

  it("loads Vastbase source comments without rebuilding constraints or defaults", async () => {
    const columns = [
      { name: '备"注', comment: "  客户's;备注  ", data_type: "text", is_nullable: true, column_default: "'default'", is_primary_key: false },
      ...[null, "", " \t\n ", undefined].map((comment, index) => ({ name: `empty_${index}`, comment, data_type: "text", is_nullable: true, column_default: null, is_primary_key: false })),
    ];
    apiMock.getColumns.mockResolvedValue(columns);
    const sql = 'CREATE TABLE "copy" AS SELECT * FROM "source" WHERE 1=0;\nCOMMENT ON TABLE "copy" IS \'订单\';\nCOMMENT ON COLUMN "copy"."note" IS \'备注\';';
    apiMock.buildDuplicateTableStructureSql.mockResolvedValue(sql);

    const plan = await buildDuplicateTableStructurePlan({
      connectionId: "vastbase-1",
      database: "app",
      catalog: "catalog",
      databaseType: "vastbase",
      schema: '业"务',
      sourceName: '订"单',
      targetName: '订"单_副本',
      tableComment: "  客户's;订单  ",
      identifierQuote: '"',
    });

    expect(apiMock.getColumns).toHaveBeenCalledExactlyOnceWith("vastbase-1", "app", '业"务', '订"单', "catalog");
    expect(apiMock.buildDuplicateTableStructureSql).toHaveBeenCalledExactlyOnceWith({
      databaseType: "vastbase",
      schema: '业"务',
      sourceName: '订"单',
      targetName: '订"单_副本',
      tableComment: "  客户's;订单  ",
      columnComments: [{ name: '备"注', comment: "  客户's;备注  " }],
      identifierQuote: '"',
    });
    expect(plan).toEqual({ sql, sourceColumns: columns, executeAsScript: true });
    expect(apiMock.listIndexes).not.toHaveBeenCalled();
    expect(apiMock.buildCreateTableSql).not.toHaveBeenCalled();
    expect(apiMock.getTableComment).not.toHaveBeenCalled();
  });

  it.each([null, "", " \t\n "])("reuses Vastbase columns and keeps absent comments optional: %j", async (comment) => {
    const columns = [{ name: "note", comment, data_type: "text", is_nullable: true, column_default: null, is_primary_key: false }];
    const sql = 'CREATE TABLE "copy" AS SELECT * FROM "source" WHERE 1=0;';
    apiMock.buildDuplicateTableStructureSql.mockResolvedValue(sql);

    const plan = await buildDuplicateTableStructurePlan({ connectionId: "vastbase-1", database: "app", databaseType: "vastbase", sourceName: "source", targetName: "copy", tableComment: comment, sourceColumns: columns });

    expect(apiMock.getColumns).not.toHaveBeenCalled();
    expect(apiMock.buildDuplicateTableStructureSql).toHaveBeenCalledWith(expect.objectContaining({ tableComment: comment, columnComments: [] }));
    expect(plan).toEqual({ sql, sourceColumns: columns, executeAsScript: false });
  });

  it("does not silently clone Vastbase without comments when column metadata fails", async () => {
    const error = new Error("column metadata unavailable");
    apiMock.getColumns.mockRejectedValueOnce(error);

    await expect(buildDuplicateTableStructurePlan({ connectionId: "vastbase-1", database: "app", databaseType: "vastbase", sourceName: "source", targetName: "copy" })).rejects.toBe(error);
    expect(apiMock.getColumns).toHaveBeenCalledExactlyOnceWith("vastbase-1", "app", "", "source", undefined);
    expect(apiMock.buildDuplicateTableStructureSql).not.toHaveBeenCalled();
  });

  it("loads Oscar source column comments and keeps table and column comments in the clone plan", async () => {
    const columns = [
      { name: "ID", comment: null, data_type: "int", is_nullable: false, column_default: null, is_primary_key: true },
      { name: "NAME", comment: "  用户's;姓名  ", data_type: "varchar(50)", is_nullable: true, column_default: null, is_primary_key: false },
      { name: "BLANK", comment: " \t\n ", data_type: "varchar(50)", is_nullable: true, column_default: null, is_primary_key: false },
    ];
    const sql = 'CREATE TABLE "SYSDBA"."USERS_COPY" AS SELECT * FROM "SYSDBA"."USERS" WHERE 1=0;\nCOMMENT ON TABLE "SYSDBA"."USERS_COPY" IS \'  用户\'\'s;表  \';\nCOMMENT ON COLUMN "SYSDBA"."USERS_COPY"."NAME" IS \'  用户\'\'s;姓名  \';';
    apiMock.getColumns.mockResolvedValue(columns);
    apiMock.buildDuplicateTableStructureSql.mockResolvedValue(sql);

    const plan = await buildDuplicateTableStructurePlan({
      connectionId: "oscar-1",
      database: "osrdb",
      catalog: "catalog",
      databaseType: "oscar",
      schema: "SYSDBA",
      sourceName: "USERS",
      targetName: "USERS_COPY",
      tableComment: "  用户's;表  ",
      identifierQuote: '"',
    });

    expect(apiMock.getColumns).toHaveBeenCalledExactlyOnceWith("oscar-1", "osrdb", "SYSDBA", "USERS", "catalog");
    expect(apiMock.buildDuplicateTableStructureSql).toHaveBeenCalledExactlyOnceWith({
      databaseType: "oscar",
      schema: "SYSDBA",
      sourceName: "USERS",
      targetName: "USERS_COPY",
      tableComment: "  用户's;表  ",
      columnComments: [{ name: "NAME", comment: "  用户's;姓名  " }],
      identifierQuote: '"',
    });
    expect(plan).toEqual({ sql, sourceColumns: columns, executeAsScript: true });
    expect(apiMock.listIndexes).not.toHaveBeenCalled();
    expect(apiMock.buildCreateTableSql).not.toHaveBeenCalled();
    expect(apiMock.getTableComment).not.toHaveBeenCalled();
  });

  it.each([null, "", " \t\n "])("reuses Oscar columns and keeps absent comments optional: %j", async (comment) => {
    const columns = [{ name: "note", comment, data_type: "text", is_nullable: true, column_default: null, is_primary_key: false }];
    const sql = 'CREATE TABLE "copy" AS SELECT * FROM "source" WHERE 1=0;';
    apiMock.buildDuplicateTableStructureSql.mockResolvedValue(sql);

    const plan = await buildDuplicateTableStructurePlan({
      connectionId: "oscar-1",
      database: "osrdb",
      databaseType: "oscar",
      sourceName: "source",
      targetName: "copy",
      tableComment: comment,
      sourceColumns: columns,
    });

    expect(apiMock.getColumns).not.toHaveBeenCalled();
    expect(apiMock.buildDuplicateTableStructureSql).toHaveBeenCalledWith(expect.objectContaining({ tableComment: comment, columnComments: [] }));
    expect(plan).toEqual({ sql, sourceColumns: columns, executeAsScript: false });
  });

  it("does not silently clone Oscar without comments when column metadata fails", async () => {
    const error = new Error("column metadata unavailable");
    apiMock.getColumns.mockRejectedValueOnce(error);

    await expect(buildDuplicateTableStructurePlan({ connectionId: "oscar-1", database: "osrdb", databaseType: "oscar", sourceName: "source", targetName: "copy" })).rejects.toBe(error);
    expect(apiMock.getColumns).toHaveBeenCalledExactlyOnceWith("oscar-1", "osrdb", "", "source", undefined);
    expect(apiMock.buildDuplicateTableStructureSql).not.toHaveBeenCalled();
  });

  it.each(["postgres", "mysql", "highgo", "kingbase", undefined] as const)("keeps %s clones on the generic path", async (databaseType) => {
    apiMock.buildDuplicateTableStructureSql.mockResolvedValue('CREATE TABLE "copy" (LIKE "source" INCLUDING ALL);');

    const plan = await buildDuplicateTableStructurePlan({
      connectionId: "postgres-1",
      database: "app",
      databaseType,
      schema: "public",
      sourceName: "source",
      targetName: "copy",
    });

    expect(apiMock.buildDuplicateTableStructureSql).toHaveBeenCalledWith(expect.objectContaining({ databaseType, sourceName: "source", targetName: "copy", columnComments: [] }));
    expect(apiMock.getColumns).not.toHaveBeenCalled();
    expect(apiMock.listIndexes).not.toHaveBeenCalled();
    expect(apiMock.buildCreateTableSql).not.toHaveBeenCalled();
    expect(plan).toEqual({ sql: 'CREATE TABLE "copy" (LIKE "source" INCLUDING ALL);', sourceColumns: undefined, executeAsScript: false });
  });

  it("recreates the SQL Server primary key that SELECT INTO drops", async () => {
    apiMock.getColumns.mockResolvedValue([]);
    apiMock.getTableComment.mockResolvedValue(null);
    apiMock.listIndexes.mockResolvedValue([
      { name: "PK_ORDERS", columns: ["ID", "SEQ"], is_unique: true, is_primary: true },
      { name: "IDX_ORDERS_CUSTOMER", columns: ["CUSTOMER"], is_unique: false, is_primary: false },
    ]);
    apiMock.buildDuplicateTableStructureSql.mockResolvedValue("SELECT TOP 0 * INTO [dbo].[orders_copy] FROM [dbo].[orders];\nALTER TABLE [dbo].[orders_copy] ADD CONSTRAINT [PK_orders_copy] PRIMARY KEY ([ID], [SEQ]);");

    const plan = await buildDuplicateTableStructurePlan({
      connectionId: "mssql-1",
      database: "app",
      databaseType: "sqlserver",
      schema: "dbo",
      sourceName: "orders",
      targetName: "orders_copy",
    });

    expect(apiMock.listIndexes).toHaveBeenCalledWith("mssql-1", "app", "dbo", "orders", undefined);
    expect(apiMock.buildDuplicateTableStructureSql).toHaveBeenCalledWith(expect.objectContaining({ databaseType: "sqlserver", primaryKeyColumns: ["ID", "SEQ"], primaryKeyConstraintName: "PK_orders_copy" }));
    expect(plan.executeAsScript).toBe(true);
  });

  it("renames the SQL Server clone PK when it would collide with a source index", async () => {
    apiMock.getColumns.mockResolvedValue([]);
    apiMock.getTableComment.mockResolvedValue(null);
    apiMock.listIndexes.mockResolvedValue([
      { name: "PK_ORDERS", columns: ["ID"], is_unique: true, is_primary: true },
      { name: "PK_orders_copy", columns: ["FLAG"], is_unique: false, is_primary: false },
    ]);
    apiMock.buildDuplicateTableStructureSql.mockResolvedValue("");

    await buildDuplicateTableStructurePlan({
      connectionId: "mssql-1",
      database: "app",
      databaseType: "sqlserver",
      schema: "dbo",
      sourceName: "orders",
      targetName: "orders_copy",
    });

    expect(apiMock.buildDuplicateTableStructureSql).toHaveBeenCalledWith(expect.objectContaining({ primaryKeyConstraintName: "PK_orders_copy_2" }));
  });

  it("caps the SQL Server clone PK name at the 128-character identifier limit", () => {
    const indexes = [{ name: "IDX", columns: ["ID"], is_unique: false, is_primary: false }];
    const longTarget = `orders_${"x".repeat(200)}`;
    const name = sqlServerClonePrimaryKeyConstraintName(indexes, longTarget);

    expect(name.length).toBeLessThanOrEqual(SQLSERVER_IDENTIFIER_MAX_LENGTH);
    expect(name.startsWith("PK_orders_")).toBe(true);
  });

  it("keeps the dedup suffix within the identifier limit for long targets", () => {
    const longTarget = `orders_${"x".repeat(200)}`;
    const colliding = `PK_${longTarget}`.slice(0, SQLSERVER_IDENTIFIER_MAX_LENGTH);
    const indexes = [
      { name: "IDX", columns: ["ID"], is_unique: false, is_primary: false },
      { name: colliding, columns: ["FLAG"], is_unique: false, is_primary: false },
    ];

    const name = sqlServerClonePrimaryKeyConstraintName(indexes as never, longTarget);

    expect(name.length).toBeLessThanOrEqual(SQLSERVER_IDENTIFIER_MAX_LENGTH);
    expect(name.endsWith("_2")).toBe(true);
  });

  it("keeps primary-key-free SQL Server clones on a single statement", async () => {
    apiMock.getColumns.mockResolvedValue([]);
    apiMock.getTableComment.mockResolvedValue(null);
    apiMock.listIndexes.mockResolvedValue([{ name: "IDX_ORDERS_CUSTOMER", columns: ["CUSTOMER"], is_unique: false, is_primary: false }]);
    apiMock.buildDuplicateTableStructureSql.mockResolvedValue("SELECT TOP 0 * INTO [orders_copy] FROM [orders];");

    const plan = await buildDuplicateTableStructurePlan({
      connectionId: "mssql-1",
      database: "app",
      databaseType: "sqlserver",
      schema: undefined,
      sourceName: "orders",
      targetName: "orders_copy",
    });

    expect(apiMock.buildDuplicateTableStructureSql).toHaveBeenCalledWith(expect.objectContaining({ databaseType: "sqlserver", primaryKeyColumns: [] }));
    expect(plan).toEqual({ sql: "SELECT TOP 0 * INTO [orders_copy] FROM [orders];", sourceColumns: [], executeAsScript: false });
  });

  it("loads SQL Server table and column comments and runs a comment-only clone as a script", async () => {
    const columns = [
      { name: "value field", comment: "  字段 O'Brien;注释  ", data_type: "nvarchar", is_nullable: true, column_default: null, is_primary_key: false },
      { name: "id", comment: null, data_type: "int", is_nullable: false, column_default: null, is_primary_key: false },
    ];
    const sql = "SELECT TOP 0 * INTO [dbo].[copy] FROM [dbo].[source];\nEXEC sys.sp_addextendedproperty ...;";
    apiMock.getColumns.mockResolvedValue(columns);
    apiMock.getTableComment.mockResolvedValue("  表 O'Brien;注释  ");
    apiMock.listIndexes.mockResolvedValue([]);
    apiMock.buildDuplicateTableStructureSql.mockResolvedValue(sql);

    const plan = await buildDuplicateTableStructurePlan({ connectionId: "mssql-1", database: "app", catalog: "catalog", databaseType: "sqlserver", schema: "dbo", sourceName: "source", targetName: "copy" });

    expect(apiMock.getColumns).toHaveBeenCalledExactlyOnceWith("mssql-1", "app", "dbo", "source", "catalog");
    expect(apiMock.getTableComment).toHaveBeenCalledExactlyOnceWith("mssql-1", "app", "dbo", "source", "catalog");
    expect(apiMock.buildDuplicateTableStructureSql).toHaveBeenCalledWith(expect.objectContaining({ tableComment: "  表 O'Brien;注释  ", columnComments: [{ name: "value field", comment: "  字段 O'Brien;注释  " }], primaryKeyColumns: [] }));
    expect(plan).toEqual({ sql, sourceColumns: columns, executeAsScript: true });
  });

  it("reuses supplied SQL Server column and table comments for data-copy callers", async () => {
    const columns = [{ name: "id", comment: "编号", data_type: "int", is_nullable: false, column_default: null, is_primary_key: true }];
    apiMock.listIndexes.mockResolvedValue([{ name: "PK_source", columns: ["id"], is_unique: true, is_primary: true }]);
    apiMock.buildDuplicateTableStructureSql.mockResolvedValue("SELECT TOP 0 * INTO [copy] FROM [source];");
    const plan = await buildDuplicateTableStructurePlan({ connectionId: "mssql-1", database: "app", databaseType: "sqlserver", sourceName: "source", targetName: "copy", sourceColumns: columns, tableComment: "表注释" });
    expect(apiMock.getColumns).not.toHaveBeenCalled();
    expect(apiMock.getTableComment).not.toHaveBeenCalled();
    expect(apiMock.buildDuplicateTableStructureSql).toHaveBeenCalledWith(expect.objectContaining({ tableComment: "表注释", columnComments: [{ name: "id", comment: "编号" }], primaryKeyColumns: ["id"] }));
    expect(plan.sourceColumns).toBe(columns);
    expect(plan.executeAsScript).toBe(true);
  });

  it("does not create a SQL Server clone when column metadata cannot be loaded", async () => {
    apiMock.listIndexes.mockResolvedValue([]);
    apiMock.getColumns.mockResolvedValue([]);
    apiMock.getTableComment.mockResolvedValue(null);
    const error = new Error("comment metadata unavailable");
    apiMock.getColumns.mockRejectedValueOnce(error);
    await expect(buildDuplicateTableStructurePlan({ connectionId: "mssql-1", database: "app", databaseType: "sqlserver", sourceName: "source", targetName: "copy" })).rejects.toBe(error);
    expect(apiMock.buildDuplicateTableStructureSql).not.toHaveBeenCalled();
  });

  it("keeps SQL Server cloning available when optional table comment loading fails", async () => {
    const warning = vi.spyOn(console, "warn").mockImplementation(() => {});
    apiMock.listIndexes.mockResolvedValue([]);
    apiMock.getColumns.mockResolvedValue([]);
    apiMock.getTableComment.mockRejectedValue(new Error("Table comment lookup is not available in the web backend"));
    apiMock.buildDuplicateTableStructureSql.mockResolvedValue("SELECT TOP 0 * INTO [copy] FROM [source];");

    const plan = await buildDuplicateTableStructurePlan({ connectionId: "mssql-web", database: "app", databaseType: "sqlserver", sourceName: "source", targetName: "copy" });

    expect(apiMock.buildDuplicateTableStructureSql).toHaveBeenCalledWith(expect.objectContaining({ tableComment: null, columnComments: [], primaryKeyColumns: [] }));
    expect(plan).toEqual({ sql: "SELECT TOP 0 * INTO [copy] FROM [source];", sourceColumns: [], executeAsScript: false });
    expect(warning).toHaveBeenCalledOnce();
    warning.mockRestore();
  });

  it("forwards the connection identifier quote so dual-dialect clones stay executable", async () => {
    // Cloud Spanner's PostgreSQL dialect quotes with double quotes while GoogleSQL uses backticks,
    // and only the connected agent knows which. Dropping the quote here would make the backend fall
    // back to the static per-type mapping and emit a syntax error for one of the two dialects.
    apiMock.buildDuplicateTableStructureSql.mockResolvedValue('CREATE TABLE "copy" AS SELECT * FROM "source" WHERE false;');

    await buildDuplicateTableStructurePlan({
      connectionId: "spanner-1",
      database: "projects/p/instances/i/databases/d",
      databaseType: "spanner",
      schema: "public",
      sourceName: "source",
      targetName: "copy",
      identifierQuote: '"',
    });

    expect(apiMock.buildDuplicateTableStructureSql).toHaveBeenCalledWith(expect.objectContaining({ identifierQuote: '"' }));
  });

  it("keeps Oracle cloning available when optional table comment loading fails", async () => {
    const warning = vi.spyOn(console, "warn").mockImplementation(() => {});
    const columns = [{ name: "ID", data_type: "NUMBER", is_nullable: false, column_default: null, is_primary_key: true }];
    apiMock.getTableComment.mockRejectedValue(new Error("not available"));
    apiMock.listIndexes.mockResolvedValue([]);
    apiMock.listForeignKeys.mockResolvedValue([]);
    apiMock.listTriggers.mockResolvedValue([]);
    apiMock.buildCreateTableSql.mockResolvedValue({ statements: ["CREATE TABLE ...;"], warnings: [] });

    const plan = await buildDuplicateTableStructurePlan({
      connectionId: "oracle-web",
      database: "XEPDB1",
      databaseType: "oracle",
      schema: "HR",
      sourceName: "ORDERS",
      targetName: "ORDERS_COPY",
      sourceColumns: columns as any,
    });

    expect(apiMock.buildCreateTableSql).toHaveBeenCalledWith(expect.objectContaining({ tableComment: undefined }));
    expect(plan.sql).toBe("CREATE TABLE ...;");
    expect(warning).toHaveBeenCalledOnce();
    warning.mockRestore();
  });
});

describe("damengDropSchemaExecutionSchema", () => {
  it("uses the login schema when dropping a different schema", () => {
    expect(damengDropSchemaExecutionSchema("APP", "TARGET")).toBe("APP");
  });

  it("fails closed when dropping the login schema", () => {
    expect(damengDropSchemaExecutionSchema("APP", "APP")).toBeNull();
  });

  it("fails closed when the username is missing", () => {
    expect(damengDropSchemaExecutionSchema(undefined, "TARGET")).toBeNull();
  });
});