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。

17.6 KB
Raw Blame
import { createPinia, setActivePinia } from "pinia";
import { beforeEach, describe, expect, it, vi } from "vitest";

const mocks = vi.hoisted(() => ({
  executeMulti: vi.fn(),
  executeQuery: vi.fn(),
  getColumns: vi.fn(),
  listIndexes: vi.fn(),
  listObjects: vi.fn(),
  listTables: vi.fn(),
  getConfig: vi.fn(),
  lookupLocalCompletionTables: vi.fn(),
  analyze: vi.fn(),
  preparePlan: vi.fn(),
}));
vi.stubGlobal("localStorage", { getItem: () => null, setItem: () => {}, removeItem: () => {} });
vi.mock("@/lib/backend/api", () => ({
  analyzeEditableQueryEditability: mocks.analyze,
  executeMulti: mocks.executeMulti,
  executeQuery: mocks.executeQuery,
  getColumns: mocks.getColumns,
  listIndexes: mocks.listIndexes,
  listObjects: mocks.listObjects,
  listTables: mocks.listTables,
  prepareQueryPaginationExecutionPlan: mocks.preparePlan,
  closeClientConnectionSession: vi.fn().mockResolvedValue(undefined),
  closeQuerySession: vi.fn().mockResolvedValue(undefined),
  saveOpenTabsState: vi.fn(),
  cancelQuery: vi.fn(),
}));
vi.mock("@/stores/connectionStore", () => ({
  useConnectionStore: () => ({
    ensureConnected: vi.fn().mockResolvedValue(undefined),
    getConfig: mocks.getConfig,
    lookupLocalCompletionTables: mocks.lookupLocalCompletionTables,
    recordConnectionLostError: vi.fn(),
    metadataGenerationFor: () => 0,
  }),
}));
vi.mock("@/stores/settingsStore", () => ({
  useSettingsStore: () => ({
    editorSettings: { pageSize: 100, autoCalculateTotalRows: false },
  }),
}));

describe("OceanBase Oracle query ROWID preparation", () => {
  beforeEach(async () => {
    vi.clearAllMocks();
    setActivePinia(createPinia());
    const { clearTableMetadataCache } = await import("@/lib/metadata/tableMetadataCache");
    clearTableMetadataCache();
    mocks.getConfig.mockReturnValue({ id: "ob-1", name: "OceanBase", db_type: "oceanbase-oracle", database: "app", query_timeout_secs: 30 });
    mocks.getColumns.mockResolvedValue([{ name: "TASKNAME", data_type: "VARCHAR2(100)", is_nullable: true, column_default: null, is_primary_key: false, extra: null }]);
    mocks.listIndexes.mockResolvedValue([]);
    mocks.listTables.mockResolvedValue([]);
    mocks.listObjects.mockResolvedValue([]);
    mocks.lookupLocalCompletionTables.mockReturnValue([{ name: "T_SIPF_DEBUG_LOG", type: "table", schema: "SIPF" }]);
    mocks.analyze.mockResolvedValue({ editable: false, reason: "complex-source" });
    mocks.preparePlan.mockImplementation(async (options) => ({ sqlToExecute: options.sql, useAgentResultSession: false }));
    mocks.executeMulti.mockResolvedValue([{ columns: ["TASKNAME"], rows: [["sample"]], affected_rows: 0, execution_time_ms: 1 }]);
  });

  async function executedQuery(sql: string) {
    const { useQueryStore } = await import("@/stores/queryStore");
    const store = useQueryStore();
    const id = store.createTab("ob-1", "app", "Query");
    store.setAutoCommit(id, true);
    await store.executeTabSql(id, sql);
    expect(mocks.executeMulti).toHaveBeenCalled();
    return { store, id, sql: mocks.executeMulti.mock.calls[0]![2] as string };
  }

  async function emittedSql(sql: string) {
    return (await executedQuery(sql)).sql;
  }

  it.each(["PIVOT(SUM(TASKNAME) FOR KIND IN (1,2))", "UNPIVOT(TASKNAME FOR KIND IN (FIRST_NAME,SECOND_NAME))", "a UNPIVOT INCLUDE NULLS (TASKNAME FOR KIND IN (FIRST_NAME,SECOND_NAME))", "a UNKNOWN_SUFFIX(1)"])("keeps an unparsed source suffix unchanged and read-only: %s", async (suffix) => {
    for (const projection of ["*", "a.*", "ROWID AS RID, TASKNAME"]) {
      const originalSql = `select ${projection} from sipf.t_sipf_debug_log ${suffix}`;
      mocks.analyze.mockResolvedValue({ editable: true, analysis: { schema: "SIPF", tableName: "T_SIPF_DEBUG_LOG", selectStar: true, columns: [] } });
      mocks.executeMulti.mockClear();
      const { store, id, sql } = await executedQuery(originalSql);
      expect(sql).toBe(originalSql);
      const tab = store.tabs.find((item) => item.id === id)!;
      await vi.waitFor(() => expect(tab.queryEditabilityReason).toBe("complex-source"));
      expect(tab.queryAnalysis).toBeUndefined();
      expect(tab.querySourceColumns).toBeUndefined();
      expect(tab.result?.hidden_column_indexes ?? []).toEqual([]);
    }
  });

  it("appends a ROWID expression to a single-column projection of a keyless table", async () => {
    const sql = await emittedSql("select a.taskname from sipf.t_sipf_debug_log a");
    expect(sql).toContain('ROWIDTOCHAR(ROWID) AS "__DBX_PK_0"');
    expect(sql).not.toContain('"__DBX_ROWID"');
  });

  it.each(["select a.rowid, a.taskname from sipf.t_sipf_debug_log a", "select q.taskname from (select a.taskname from sipf.t_sipf_debug_log a) q"])("preserves queries not needing an extra ROWID: %s", async (sql) => {
    expect(await emittedSql(sql)).toBe(sql);
  });

  it.each([
    ["select * from sipf.t_sipf_debug_log a", "select a.*"],
    ["select a.* from sipf.t_sipf_debug_log a", "select a.*"],
    ['select * from sipf.t_sipf_debug_log "Log"', 'select "Log".*'],
    ['select "Log".* from sipf.t_sipf_debug_log "Log"', 'select "Log".*'],
    ["select * from sipf.t_sipf_debug_log", "select t_sipf_debug_log.*"],
  ])("adds a hidden identity to a safe star query: %s", async (originalSql, projection) => {
    const { analyzeEditableQueryEditability } = await import("@/lib/sql/sqlAnalysis");
    mocks.analyze.mockImplementation(async (sql: string) => analyzeEditableQueryEditability(sql));
    mocks.executeMulti.mockResolvedValue([
      {
        columns: ["TASKNAME", "__DBX_PK_0"],
        rows: [
          ["duplicate", "ROW-A"],
          ["duplicate", "ROW-B"],
        ],
        affected_rows: 0,
        execution_time_ms: 1,
      },
    ]);
    const { store, id, sql } = await executedQuery(originalSql);
    expect(sql).toBe(`${projection}, ROWIDTOCHAR(ROWID) AS "__DBX_PK_0"${originalSql.slice(originalSql.indexOf(" from "))}`);
    const tab = store.tabs.find((item) => item.id === id)!;
    expect(tab.result?.hidden_column_indexes).toEqual([1]);
    expect(tab.result?.rows).toEqual([
      ["duplicate", "ROW-A"],
      ["duplicate", "ROW-B"],
    ]);
    await vi.waitFor(() => expect(tab.querySourceColumns).toEqual(["TASKNAME", "__DBX_ROWID"]));
    expect(tab.tableMeta?.primaryKeys).toEqual(["__DBX_ROWID"]);
  });

  it.each(["*", "a.*"])("keeps physical rowid and hidden-alias columns visible for %s", async (projection) => {
    const { analyzeEditableQueryEditability } = await import("@/lib/sql/sqlAnalysis");
    mocks.analyze.mockImplementation(async (sql: string) => analyzeEditableQueryEditability(sql));
    mocks.getColumns.mockResolvedValue([
      { name: "rowid", data_type: "VARCHAR2(100)", is_nullable: true, is_primary_key: false },
      { name: "__DBX_PK_0", data_type: "VARCHAR2(100)", is_nullable: true, is_primary_key: false },
      { name: "TASKNAME", data_type: "VARCHAR2(100)", is_nullable: true, is_primary_key: false },
    ]);
    mocks.executeMulti.mockResolvedValue([{ columns: ["rowid", "__DBX_PK_0", "TASKNAME", "__DBX_PK_1"], rows: [["physical", "visible", "sample", "ROW-A"]], affected_rows: 0, execution_time_ms: 1 }]);
    const { store, id, sql } = await executedQuery(`select ${projection} from sipf.t_sipf_debug_log a`);
    expect(sql).toContain('ROWIDTOCHAR(ROWID) AS "__DBX_PK_1"');
    const tab = store.tabs.find((item) => item.id === id)!;
    expect(tab.result?.hidden_column_indexes).toEqual([3]);
    await vi.waitFor(() => expect(tab.querySourceColumns).toEqual(["rowid", "__DBX_PK_0", "TASKNAME", "__DBX_ROWID"]));
  });

  it.each(["*", "a.*"])("does not append ROWID when a star returns the physical primary key: %s", async (projection) => {
    mocks.getColumns.mockResolvedValue([
      { name: "ID", data_type: "NUMBER", is_nullable: false, is_primary_key: true },
      { name: "TASKNAME", data_type: "VARCHAR2(100)", is_nullable: true, is_primary_key: false },
    ]);
    const sql = `select ${projection} from sipf.t_sipf_debug_log a`;
    expect(await emittedSql(sql)).toBe(sql);
  });

  it.each(["view", "unknown"])("keeps star queries unchanged for a %s source", async (type) => {
    mocks.lookupLocalCompletionTables.mockReturnValue(type === "unknown" ? [] : [{ name: "T_SIPF_DEBUG_LOG", type, schema: "SIPF" }]);
    const sql = "select * from sipf.t_sipf_debug_log a";
    expect(await emittedSql(sql)).toBe(sql);
    expect(mocks.listTables).not.toHaveBeenCalled();
  });

  it.each(["view", "unknown"])("does not inject ROWID for a %s source", async (type) => {
    mocks.lookupLocalCompletionTables.mockReturnValue(type === "unknown" ? [] : [{ name: "T_SIPF_DEBUG_LOG", type, schema: "SIPF" }]);
    const sql = "select a.taskname from sipf.t_sipf_debug_log a";
    expect(await emittedSql(sql)).toBe(sql);
    expect(mocks.listTables).not.toHaveBeenCalled();
  });

  it.each(["view", "unknown"])("does not make a %s source editable when ROWID is explicitly returned", async (type) => {
    const { analyzeEditableQueryEditability } = await import("@/lib/sql/sqlAnalysis");
    mocks.analyze.mockImplementation(async (sql: string) => analyzeEditableQueryEditability(sql));
    mocks.lookupLocalCompletionTables.mockReturnValue(type === "unknown" ? [] : [{ name: "T_SIPF_DEBUG_LOG", type, schema: "SIPF" }]);
    mocks.executeMulti.mockResolvedValue([{ columns: ["RID", "TASKNAME"], rows: [["ROW-A", "sample"]], affected_rows: 0, execution_time_ms: 1 }]);
    const originalSql = "select a.rowid as rid, a.taskname from sipf.t_sipf_debug_log a";
    const { store, id, sql } = await executedQuery(originalSql);
    expect(sql).toBe(originalSql);
    const tab = store.tabs.find((item) => item.id === id)!;
    await vi.waitFor(() => expect(tab.queryEditabilityReason).toBeDefined());
    expect(tab.queryAnalysis).toBeUndefined();
    expect(tab.querySourceColumns).toBeUndefined();
    expect(tab.tableMeta?.primaryKeys ?? []).not.toContain("__DBX_ROWID");
    expect(mocks.listTables).not.toHaveBeenCalled();
  });

  it("does not borrow ROWID editability from a same-named table in another schema", async () => {
    const { analyzeEditableQueryEditability } = await import("@/lib/sql/sqlAnalysis");
    mocks.analyze.mockImplementation(async (sql: string) => analyzeEditableQueryEditability(sql));
    mocks.lookupLocalCompletionTables.mockReturnValue([{ name: "T_SIPF_DEBUG_LOG", type: "table", schema: "OTHER_OWNER" }]);
    mocks.executeMulti.mockResolvedValue([{ columns: ["RID", "TASKNAME"], rows: [["ROW-A", "sample"]], affected_rows: 0, execution_time_ms: 1 }]);
    const { store, id } = await executedQuery("select a.rowid as rid, a.taskname from sipf.t_sipf_debug_log a");
    const tab = store.tabs.find((item) => item.id === id)!;
    await vi.waitFor(() => expect(tab.queryEditabilityReason).toBeDefined());
    expect(tab.queryAnalysis).toBeUndefined();
    expect(mocks.lookupLocalCompletionTables).toHaveBeenCalledWith("ob-1", "app", "T_SIPF_DEBUG_LOG", 20, "SIPF", undefined);
  });

  it("prefers a physical primary key", async () => {
    mocks.getColumns.mockResolvedValue([
      { name: "ID", data_type: "NUMBER", is_nullable: false, is_primary_key: true },
      { name: "TASKNAME", data_type: "VARCHAR2(100)", is_nullable: true, is_primary_key: false },
    ]);
    const sql = await emittedSql("select a.taskname from sipf.t_sipf_debug_log a");
    expect(sql).toContain('"ID" AS "__DBX_PK_0"');
    expect(sql).not.toContain("ROWID");
  });

  it.each(["PARTITION(P0)", "SUBPARTITION(P0)", "PARTITION FOR (1)"])("executes table suffix %s without qualifying it as an alias", async (suffix) => {
    const originalSql = `select * from sipf.t_sipf_debug_log ${suffix}`;
    expect(await emittedSql(originalSql)).toBe(originalSql);
  });

  it("preserves a business rowid alias and keeps its locator in a distinct hidden column", async () => {
    const { analyzeEditableQueryEditability } = await import("@/lib/sql/sqlAnalysis");
    mocks.analyze.mockImplementation(async (sql: string) => analyzeEditableQueryEditability(sql));
    mocks.getColumns.mockResolvedValue([
      { name: "ID", data_type: "NUMBER", is_nullable: false, is_primary_key: false },
      { name: "BUSINESS_ROWID", data_type: "VARCHAR2(100)", is_nullable: true, is_primary_key: false },
    ]);
    mocks.executeMulti.mockResolvedValue([{ columns: ["ID", "rowid", "__DBX_PK_0", "__DBX_PK_1"], rows: [[6, "physical-6", "visible", "ROW-6"]], affected_rows: 0, execution_time_ms: 1 }]);
    const originalSql = 'select ID, BUSINESS_ROWID AS "rowid", \'visible\' AS "__DBX_PK_0" from sipf.t_sipf_debug_log order by ID';
    const { store, id, sql } = await executedQuery(originalSql);
    expect(sql).toBe('select ID, BUSINESS_ROWID AS "rowid", \'visible\' AS "__DBX_PK_0", ROWIDTOCHAR(ROWID) AS "__DBX_PK_1" from sipf.t_sipf_debug_log order by ID');
    expect(mocks.preparePlan.mock.calls[0]![0].sql).toBe(sql);
    const tab = store.tabs.find((item) => item.id === id)!;
    expect(tab.result?.hidden_column_indexes).toEqual([3]);
    expect(tab.result?.rows).toEqual([[6, "physical-6", "visible", "ROW-6"]]);
    await vi.waitFor(() => expect(tab.querySourceColumns).toEqual(["ID", "BUSINESS_ROWID", undefined, "__DBX_ROWID"]));
    expect(tab.tableMeta?.primaryKeys).toEqual(["__DBX_ROWID"]);
  });

  it("hides the projected ROWID while retaining two row identities for editing", async () => {
    const { analyzeEditableQueryEditability } = await import("@/lib/sql/sqlAnalysis");
    mocks.analyze.mockImplementation(async (sql: string) => analyzeEditableQueryEditability(sql));
    mocks.executeMulti.mockResolvedValue([
      {
        columns: ["TASKNAME", "__DBX_PK_0"],
        rows: [
          ["first", "AAAPr9AAEAAAACXAAA"],
          ["second", "AAAPr9AAEAAAACXAAB"],
        ],
        affected_rows: 0,
        execution_time_ms: 1,
      },
    ]);

    const { store, id } = await executedQuery("select a.taskname from sipf.t_sipf_debug_log a");
    const tab = store.tabs.find((item) => item.id === id)!;
    expect(tab.result?.hidden_column_indexes).toEqual([1]);
    expect(tab.result?.rows).toEqual([
      ["first", "AAAPr9AAEAAAACXAAA"],
      ["second", "AAAPr9AAEAAAACXAAB"],
    ]);
    await vi.waitFor(() => expect(tab.querySourceColumns).toEqual(["TASKNAME", "__DBX_ROWID"]));
    expect(tab.tableMeta?.primaryKeys).toEqual(["__DBX_ROWID"]);
    expect(tab.queryEditabilityReason).toBeUndefined();
  });

  it.each(["a.", ""])("does not confuse a quoted physical rowid column with the synthetic identity: %s", async (qualifier) => {
    mocks.getColumns.mockResolvedValue([
      { name: "rowid", data_type: "VARCHAR2(100)", is_nullable: true, is_primary_key: false },
      { name: "TASKNAME", data_type: "VARCHAR2(100)", is_nullable: true, is_primary_key: false },
    ]);
    const sql = await emittedSql(`select ${qualifier}"rowid", a.taskname from sipf.t_sipf_debug_log a`);
    expect(sql).toBe(`select ${qualifier}"rowid", a.taskname, ROWIDTOCHAR(ROWID) AS "__DBX_PK_0" from sipf.t_sipf_debug_log a`);
  });

  it.each(["select a.rowid as rid, a.taskname from sipf.t_sipf_debug_log a", "select rowid as rid, taskname from sipf.t_sipf_debug_log"])("uses an explicitly selected ROWID as the row identity without hiding it: %s", async (originalSql) => {
    const { analyzeEditableQueryEditability } = await import("@/lib/sql/sqlAnalysis");
    mocks.analyze.mockImplementation(async (sql: string) => analyzeEditableQueryEditability(sql));
    mocks.executeMulti.mockResolvedValue([{ columns: ["RID", "TASKNAME"], rows: [["AAAPr9AAEAAAACXAAA", "first"]], affected_rows: 0, execution_time_ms: 1 }]);
    const { store, id, sql } = await executedQuery(originalSql);
    expect(sql).toBe(originalSql);
    const tab = store.tabs.find((item) => item.id === id)!;
    expect(tab.result?.hidden_column_indexes ?? []).toEqual([]);
    await vi.waitFor(() => expect(tab.querySourceColumns).toEqual(["__DBX_ROWID", "TASKNAME"]));
    expect(tab.tableMeta?.primaryKeys).toEqual(["__DBX_ROWID"]);
  });

  it.each(["a.", ""])("retains the physical primary key when ROWID is also selected: %s", async (qualifier) => {
    const { analyzeEditableQueryEditability } = await import("@/lib/sql/sqlAnalysis");
    mocks.analyze.mockImplementation(async (sql: string) => analyzeEditableQueryEditability(sql));
    mocks.getColumns.mockResolvedValue([
      { name: "ID", data_type: "NUMBER", is_nullable: false, is_primary_key: true },
      { name: "TASKNAME", data_type: "VARCHAR2(100)", is_nullable: true, is_primary_key: false },
    ]);
    mocks.executeMulti.mockResolvedValue([{ columns: ["RID", "TASKNAME", "__DBX_PK_0"], rows: [["AAAPr9AAEAAAACXAAA", "first", 1]], affected_rows: 0, execution_time_ms: 1 }]);
    const { store, id, sql } = await executedQuery(`select ${qualifier}rowid as rid, a.taskname from sipf.t_sipf_debug_log a`);
    expect(sql).toContain('"ID" AS "__DBX_PK_0"');
    expect(sql).not.toContain("ROWIDTOCHAR");
    const tab = store.tabs.find((item) => item.id === id)!;
    await vi.waitFor(() => expect(tab.querySourceColumns).toEqual([undefined, "TASKNAME", "ID"]));
    expect(tab.tableMeta?.primaryKeys).toEqual(["ID"]);
  });

  it.each([
    "select distinct a.* from sipf.t_sipf_debug_log a",
    "select a.*, count(*) from sipf.t_sipf_debug_log a group by a.taskname",
    "select a.*, b.* from sipf.t_sipf_debug_log a join sipf.t_sipf_debug_log b on a.taskname = b.taskname",
    "select distinct a.taskname from sipf.t_sipf_debug_log a",
    "select a.taskname, count(*) from sipf.t_sipf_debug_log a group by a.taskname",
    "select a.taskname, b.taskname from sipf.t_sipf_debug_log a join sipf.t_sipf_debug_log b on a.taskname = b.taskname",
    "select rowid, a.taskname from sipf.t_sipf_debug_log a join sipf.t_sipf_debug_log b on a.taskname = b.taskname",
    "select a.taskname from sipf.t_sipf_debug_log a union select b.taskname from sipf.t_sipf_debug_log b",
  ])("does not inject ROWID into an unsafe query: %s", async (sql) => {
    expect(await emittedSql(sql)).toBe(sql);
  });
});