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。
TypeScript aicliclickhousedatabasedatabase-clientdatabase-managementdockerguimcpmongodbmysqlpostgresqlredisrustsql-serversqlitetaurivue
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);
});
});