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 { describe, expect, it } from "vitest";
import { buildSqlCompletionItemsFromContext, getSqlCompletionContext, type SqlCompletionColumn, type SqlCompletionProviderInput } from "@/lib/sql/sqlCompletion";
import { sqlCompletionContextFromSemantic, sqlSemanticLocalColumnsByTable } from "@/lib/sql/semantic/completion";
import { buildSqlSemanticModel } from "@/lib/sql/semantic/model";
import { sqlFixtureCursor } from "@/lib/sql/semantic/fixtures";
import type { DatabaseType } from "@/types/database";
function mergeColumns(...maps: Array<Map<string, SqlCompletionColumn[]> | undefined>): Map<string, SqlCompletionColumn[]> {
const merged = new Map<string, SqlCompletionColumn[]>();
for (const map of maps) {
for (const [key, columns] of map ?? []) merged.set(key, columns);
}
return merged;
}
function semanticCompletion(markedSql: string, input: Partial<SqlCompletionProviderInput> = {}, options: { databaseType?: DatabaseType; dialect?: "mysql" | "postgres" | "sqlserver" } = {}) {
const { sql, cursor } = sqlFixtureCursor(markedSql);
const model = buildSqlSemanticModel(sql, cursor, options);
const context = sqlCompletionContextFromSemantic(model, getSqlCompletionContext(sql, cursor, options));
const columnsByTable = mergeColumns(sqlSemanticLocalColumnsByTable(model), input.columnsByTable);
const items = buildSqlCompletionItemsFromContext(context, {
tables: input.tables ?? [],
objects: input.objects ?? [],
columnsByTable,
foreignKeysByTable: input.foreignKeysByTable,
schemas: input.schemas,
translations: input.translations,
snippets: input.snippets,
dialect: options.dialect,
databaseType: options.databaseType,
keywordCase: input.keywordCase,
autoAliasTables: input.autoAliasTables,
tableCompletionSchemaQualification: input.tableCompletionSchemaQualification,
});
return { sql, cursor, model, context, items };
}
describe("semantic SQL completion candidates", () => {
it("does not suggest alias snippets when completing a table name (#13750)", () => {
const markedSql = `SELECT *
FROM dbo.Dh_Workcenter b
INNER JOIN dbo.Dh_WorkcenterProcess c ON c.Workcenter_ID = b.Workcenter_ID
INNER JOIN dbo.DH_MODEL_CAP_SUB dmcs ON c.Process_ID = dmcs.Process_ID
INNER JOIN dbo.Dh_Process a ON c.Process_ID = a.Process_ID AND a.Process_Type = '02'
LEFT JOIN dbo.Db_ModelSetTemplat|
WHERE b.Workcenter_ID = :workcenter_ID`;
const { items } = semanticCompletion(
markedSql,
{
tables: [{ name: "Db_ModelSetTemplate", schema: "dbo", type: "table" }],
autoAliasTables: true,
},
{ databaseType: "sqlserver", dialect: "sqlserver" },
);
expect(items.some((item) => item.type === "snippet" && item.detail?.includes("alias for"))).toBe(false);
expect(items.some((item) => item.label.includes("dmst") && item.type === "snippet")).toBe(false);
expect(items.some((item) => item.type === "table" && item.label === "Db_ModelSetTemplate")).toBe(true);
// When the cursor is after the table name and a space, alias snippets should be offered
const afterTableSpaceSql = `SELECT *
FROM dbo.Dh_Workcenter b
LEFT JOIN dbo.Db_ModelSetTemplate |
WHERE b.Workcenter_ID = :workcenter_ID`;
const afterTable = semanticCompletion(
afterTableSpaceSql,
{
tables: [{ name: "Db_ModelSetTemplate", schema: "dbo", type: "table" }],
autoAliasTables: false,
},
{ databaseType: "sqlserver", dialect: "sqlserver" },
);
const aliasSnippet = afterTable.items.find((item) => item.type === "snippet" && item.detail?.includes("alias for Db_ModelSetTemplate"));
expect(aliasSnippet).toBeDefined();
expect(aliasSnippet?.apply).toBe("dmst ");
});
it("does not mix SELECT aliases into a following UPDATE statement", () => {
const columnsByTable = new Map<string, SqlCompletionColumn[]>([
["codex_completion_a", [{ name: "id", table: "codex_completion_a", schema: "public" }]],
["codex_completion_b", [{ name: "id", table: "codex_completion_b", schema: "public" }]],
]);
const { context, items } = semanticCompletion("SELECT ph.id FROM codex_completion_a AS ph;\n\nUPDATE codex_completion_b\nSET status = 0\nWHERE id|", { columnsByTable }, { databaseType: "postgres", dialect: "postgres" });
expect(context.referencedTables).toEqual([expect.objectContaining({ name: "codex_completion_b" })]);
expect(items.filter((item) => item.type === "column").map((item) => item.label)).toEqual(["id"]);
});
it("treats PostgreSQL hash operators as part of the preceding statement", () => {
const columnsByTable = new Map<string, SqlCompletionColumn[]>([
["codex_completion_a", [{ name: "legacy_id", table: "codex_completion_a", schema: "public" }]],
["codex_completion_b", [{ name: "current_id", table: "codex_completion_b", schema: "public" }]],
]);
const { context, items } = semanticCompletion("SELECT ph.legacy_id # 1 FROM codex_completion_a AS ph;\nUPDATE codex_completion_b SET current_id = 0 WHERE ph.|", { columnsByTable }, { databaseType: "postgres", dialect: "postgres" });
expect(context.statementKind).toBe("update");
expect(context.referencedTables).toEqual([expect.objectContaining({ name: "codex_completion_b" })]);
expect(items.filter((item) => item.type === "column").map((item) => item.label)).not.toContain("legacy_id");
});
it("shows the column comment inline in the completion detail", () => {
const columnsByTable = new Map<string, SqlCompletionColumn[]>([["users", [{ name: "nickname", table: "users", schema: "public", dataType: "text", comment: "用户昵称" }]]]);
const { items } = semanticCompletion("SELECT nick| FROM users", { columnsByTable }, { databaseType: "postgres", dialect: "postgres" });
const nickname = items.find((item) => item.type === "column" && item.label === "nickname");
expect(nickname?.detail).toContain("-- 用户昵称");
expect(nickname?.comment).toBe("用户昵称");
});
it("ignores line-comment semicolons after a real statement boundary", () => {
const columnsByTable = new Map<string, SqlCompletionColumn[]>([
["codex_completion_a", [{ name: "legacy_id", table: "codex_completion_a", schema: "public" }]],
["codex_completion_b", [{ name: "current_id", table: "codex_completion_b", schema: "public" }]],
]);
const { context, items } = semanticCompletion("SELECT ph.legacy_id FROM codex_completion_a AS ph; -- separator ; trailing words\nUPDATE codex_completion_b SET current_id = 0 WHERE current_|", { columnsByTable }, { databaseType: "postgres", dialect: "postgres" });
expect(context.statementKind).toBe("update");
expect(context.referencedTables).toEqual([expect.objectContaining({ name: "codex_completion_b" })]);
expect(items.filter((item) => item.type === "column").map((item) => item.label)).toEqual(["current_id"]);
});
it("ignores block-comment semicolons after a real statement boundary", () => {
const columnsByTable = new Map<string, SqlCompletionColumn[]>([
["codex_completion_a", [{ name: "legacy_id", table: "codex_completion_a", schema: "public" }]],
["codex_completion_b", [{ name: "current_id", table: "codex_completion_b", schema: "public" }]],
]);
const { context, items } = semanticCompletion("SELECT ph.legacy_id FROM codex_completion_a AS ph; /* separator ; trailing words */\nUPDATE codex_completion_b SET current_id = 0 WHERE current_|", { columnsByTable }, { databaseType: "postgres", dialect: "postgres" });
expect(context.statementKind).toBe("update");
expect(context.referencedTables).toEqual([expect.objectContaining({ name: "codex_completion_b" })]);
expect(items.filter((item) => item.type === "column").map((item) => item.label)).toEqual(["current_id"]);
});
it("loads nested alias columns through the database-qualified metadata key", () => {
const { context, items } = semanticCompletion(
"SELECT * FROM aa.tb t WHERE EXISTS (SELECT 1 FROM aa.tb1 t1, aa.tb2 t2 WHERE t1.|)",
{
columnsByTable: new Map([["aa.tb1", [{ name: "id", table: "tb1", schema: "aa" }]]]),
},
{ databaseType: "mysql", dialect: "mysql" },
);
expect(context.referencedTables).toEqual(expect.arrayContaining([expect.objectContaining({ name: "tb1", schema: "aa", alias: "t1" })]));
expect(items.filter((item) => item.type === "column").map((item) => item.label)).toEqual(["id"]);
});
it("suggests nested tables after a qualified comma", () => {
const { context, items } = semanticCompletion("SELECT * FROM aa.tb t WHERE EXISTS (SELECT 1 FROM aa.tb1 t1, aa.|)", { tables: [{ name: "tb2", schema: "aa", type: "table" }] }, { databaseType: "mysql", dialect: "mysql" });
expect(context.contextKind).toBe("table");
expect(items).toEqual(expect.arrayContaining([expect.objectContaining({ label: "tb2", type: "table" })]));
});
it.each([
["ordinary lowercase", "SELECT * FROM orders_alias a WHERE a.|", "ORDERS_ALIAS", false],
["quoted lowercase", 'SELECT * FROM "orders_alias" a WHERE a.|', "orders_alias", true],
["quoted mixed case", 'SELECT * FROM "Orders_Alias" a WHERE a.|', "Orders_Alias", true],
] as const)("preserves Oracle identifier semantics for %s aliases", (_label, markedSql, expectedName, expectedQuoted) => {
const { context } = semanticCompletion(markedSql, {}, { databaseType: "oracle" });
expect(context.referencedTables).toEqual([expect.objectContaining({ name: expectedName, nameQuoted: expectedQuoted, alias: "A" })]);
});
it("isolates SQL Server columns for database-qualified tables with the same schema and name", () => {
const columnsByTable = new Map<string, SqlCompletionColumn[]>([
["DatabaseA.OUT.orders", [{ name: "source_marker", table: "orders", schema: "OUT" }]],
["DatabaseB.OUT.orders", [{ name: "target_marker", table: "orders", schema: "OUT" }]],
]);
const { context, items } = semanticCompletion("SELECT * FROM [DatabaseA].[OUT].[orders] a LEFT JOIN [DatabaseB].[OUT].[orders] b ON b.|", { columnsByTable }, { databaseType: "sqlserver", dialect: "sqlserver" });
expect(context.referencedTables).toEqual(expect.arrayContaining([expect.objectContaining({ name: "orders", database: "DatabaseB", schema: "OUT", alias: "b" })]));
expect(items.filter((item) => item.type === "column").map((item) => item.label)).toEqual(["target_marker"]);
});
it("resolves columns after a full SQL Server database.schema.table qualifier", () => {
const columnsByTable = new Map<string, SqlCompletionColumn[]>([["DatabaseB.OUT.orders", [{ name: "target_marker", table: "orders", schema: "OUT" }]]]);
const { context, items } = semanticCompletion("SELECT * FROM [DatabaseB].[OUT].[orders] WHERE [DatabaseB].[OUT].[orders].|", { columnsByTable }, { databaseType: "sqlserver", dialect: "sqlserver" });
expect(context.qualifierParts).toEqual(["DatabaseB", "OUT", "orders"]);
expect(items.filter((item) => item.type === "column").map((item) => item.label)).toEqual(["target_marker"]);
});
it("completes SQL Server tables from the database dbo schema after a double dot", () => {
const { context, items } = semanticCompletion(
"SELECT * FROM BarDB..|",
{
tables: [{ name: "orders", database: "BarDB", schema: "dbo", type: "table" }],
},
{ databaseType: "sqlserver", dialect: "sqlserver" },
);
expect(context).toMatchObject({
prefix: "",
qualifier: "BarDB.dbo",
qualifierParts: ["BarDB", "dbo"],
});
expect(items.filter((item) => item.type === "table")).toEqual([expect.objectContaining({ label: "orders", apply: "orders" })]);
});
it("completes SQL Server alias columns from the exact double-dot metadata target", () => {
const columnsByTable = new Map<string, SqlCompletionColumn[]>([
["FooDB.dbo.orders", [{ name: "wrong_database", table: "orders", schema: "dbo" }]],
["BarDB.sales.orders", [{ name: "wrong_schema", table: "orders", schema: "sales" }]],
["BarDB.dbo.orders", [{ name: "target_marker", table: "orders", schema: "dbo" }]],
]);
const { model, context, items } = semanticCompletion("SELECT * FROM BarDB..orders AS o WHERE o.|", { columnsByTable }, { databaseType: "sqlserver", dialect: "sqlserver" });
expect(model.rowSources).toEqual([
expect.objectContaining({
name: "orders",
qualifierParts: ["BarDB", "dbo"],
alias: "o",
metadataTarget: { database: "BarDB", schema: "dbo", table: "orders" },
}),
]);
expect(context.referencedTables).toEqual([expect.objectContaining({ name: "orders", database: "BarDB", schema: "dbo", alias: "o" })]);
expect(items.filter((item) => item.type === "column").map((item) => item.label)).toEqual(["target_marker"]);
});
it("completes unqualified SQL Server columns from the exact double-dot metadata target", () => {
const columnsByTable = new Map<string, SqlCompletionColumn[]>([
["FooDB.dbo.orders", [{ name: "wrong_database", table: "orders", schema: "dbo" }]],
["BarDB.sales.orders", [{ name: "wrong_schema", table: "orders", schema: "sales" }]],
["BarDB.dbo.orders", [{ name: "target_marker", table: "orders", schema: "dbo" }]],
]);
const { context, items } = semanticCompletion("SELECT * FROM BarDB..orders WHERE tar|", { columnsByTable }, { databaseType: "sqlserver", dialect: "sqlserver" });
expect(context.referencedTables).toEqual([expect.objectContaining({ name: "orders", database: "BarDB", schema: "dbo" })]);
expect(items.filter((item) => item.type === "column").map((item) => item.label)).toEqual(["target_marker"]);
});
it.each([
["MySQL ORDER BY", "SELECT * FROM t LIMIT 100 or|", "mysql", "mysql", "ORDER BY"],
["PostgreSQL ON CONFLICT", "INSERT INTO t VALUES (1) on|", "postgres", "postgres", "ON CONFLICT"],
["Oracle EXECUTE IMMEDIATE", "exec|", "oracle", undefined, "EXECUTE IMMEDIATE"],
] as const)("keeps the longer %s keyword available before the current token is committed", (_label, sql, databaseType, dialect, expectedKeyword) => {
const { context, items } = semanticCompletion(sql, {}, { databaseType, dialect });
expect(context.suggestKeywords).toBe(false);
expect(items).toEqual(expect.arrayContaining([expect.objectContaining({ label: expectedKeyword, type: "keyword" })]));
});
it.each([
["UPDATE", "update|", "sqlserver"],
["FROM", "select * from|", "mysql"],
["JOIN", "select * from t1 join|", "mysql"],
] as const)("keeps a fully typed table introducer keyword (%s) in the candidate list", (keyword, sql, databaseType) => {
const { context, items } = semanticCompletion(sql, {}, { databaseType });
expect(context.suggestKeywords).toBe(true);
expect(items).toEqual(expect.arrayContaining([expect.objectContaining({ label: keyword, type: "keyword" })]));
});
it("offers tables once a table introducer keyword is committed with whitespace", () => {
const { context, items } = semanticCompletion("update |", { tables: [{ name: "orders" }] }, { databaseType: "sqlserver" });
expect(context.exclusiveTableSuggestions).toBe(true);
expect(items).toEqual(expect.arrayContaining([expect.objectContaining({ label: "orders", type: "table" })]));
});
it("does not offer keyword continuations for qualified column prefixes", () => {
const columnsByTable = new Map<string, SqlCompletionColumn[]>([["t", [{ name: "order_number", table: "t" }]]]);
const { context, items } = semanticCompletion("SELECT * FROM t WHERE t.or|", { columnsByTable }, { databaseType: "mysql", dialect: "mysql" });
expect(context.qualifier).toBe("t");
expect(items.some((item) => item.label === "ORDER BY")).toBe(false);
});
it("stops offering ORDER BY as a prefix continuation after OR is committed with whitespace", () => {
const columnsByTable = new Map<string, SqlCompletionColumn[]>([["t", [{ name: "id", table: "t" }]]]);
const { context, items } = semanticCompletion("SELECT * FROM t WHERE id = 1 OR |", { columnsByTable }, { databaseType: "mysql", dialect: "mysql" });
expect(context.prefix).toBe("");
expect(items.some((item) => item.label === "ORDER BY")).toBe(false);
expect(items.some((item) => item.label === "id" && item.type === "column")).toBe(true);
});
it("keeps matching functions available in column expressions", () => {
const columnsByTable = new Map<string, SqlCompletionColumn[]>([
[
"routes",
[
{ name: "start_sid", table: "routes" },
{ name: "start_dept", table: "routes" },
],
],
]);
const { context, items } = semanticCompletion(
"SELECT * FROM routes WHERE st_|",
{
columnsByTable,
objects: [
{ name: "st_area", type: "function", dataType: "double precision" },
{ name: "st_refresh", type: "procedure" },
],
},
{ databaseType: "postgres", dialect: "postgres" },
);
expect(context.contextKind).toBe("column");
expect(context.suggestColumns).toBe(true);
expect(context.suggestRoutines).toBe(true);
expect(context.exclusiveRoutineSuggestions).toBe(false);
expect(items.some((item) => item.label === "st_area" && item.type === "function")).toBe(true);
expect(items.some((item) => item.label === "st_refresh")).toBe(false);
});
it("keeps alias-qualified column completion scoped to one row source", () => {
const columnsByTable = new Map<string, SqlCompletionColumn[]>([
["users", ["id", "name", "email"].map((name) => ({ name, table: "users" }))],
["orders", ["id", "total"].map((name) => ({ name, table: "orders" }))],
]);
const { items } = semanticCompletion("SELECT * FROM users u JOIN orders o ON o.user_id = u.id WHERE u.|", { columnsByTable });
expect(items.filter((item) => item.type === "column").map((item) => item.label)).toEqual(["id", "name", "email"]);
});
it.each([
["table name", "SELECT orders.| FROM orders"],
["schema-qualified table name", "SELECT public.orders.| FROM public.orders"],
] as const)("completes PostgreSQL columns through a %s qualifier", (_label, markedSql) => {
const columnsByTable = new Map<string, SqlCompletionColumn[]>([["orders", ["id", "customer_name", "total_amount"].map((name) => ({ name, table: "orders", schema: "public" }))]]);
const { context, items } = semanticCompletion(markedSql, { columnsByTable }, { databaseType: "postgres", dialect: "postgres" });
expect(context.contextKind).toBe("alias_column");
expect(context.exclusiveColumnSuggestions).toBe(true);
expect(items.filter((item) => item.type === "column").map((item) => item.label)).toEqual(["id", "customer_name", "total_amount"]);
});
it.each([
["PostgreSQL", "postgres", "postgres"],
["SQL Server", "sqlserver", "sqlserver"],
] as const)("uses row-source aliases for %s self-join column collisions", (_label, databaseType, dialect) => {
const columnsByTable = new Map<string, SqlCompletionColumn[]>([["users", ["id", "name"].map((name) => ({ name, table: "users" }))]]);
const { items } = semanticCompletion("SELECT * FROM users u JOIN users v ON u.id = v.id WHERE |", { columnsByTable }, { databaseType, dialect });
const columns = items.filter((item) => item.type === "column");
expect(columns.map((item) => item.label)).toEqual(expect.arrayContaining(["u.id", "u.name", "v.id", "v.name"]));
expect(columns.find((item) => item.label === "u.id")?.apply).toBe("u.id");
expect(columns.find((item) => item.label === "v.id")?.apply).toBe("v.id");
});
it("prioritizes matching table aliases over matching columns", () => {
const columnsByTable = new Map<string, SqlCompletionColumn[]>([["test_tb", ["title", "type"].map((name) => ({ name, table: "test_tb" }))]]);
const { items } = semanticCompletion("SELECT * FROM test_tb AS tt WHERE t|", { columnsByTable });
expect(items[0]).toMatchObject({ label: "tt", type: "text", apply: "tt" });
expect(items.filter((item) => item.type === "column").map((item) => item.label)).toEqual(expect.arrayContaining(["title", "type"]));
});
it("qualifies both unique and duplicate columns in multi-table queries", () => {
const columnsByTable = new Map<string, SqlCompletionColumn[]>([
["tVillage", ["villageId", "villageName"].map((name) => ({ name, table: "tVillage" }))],
["tland", ["villageId", "landName"].map((name) => ({ name, table: "tland" }))],
]);
const { items } = semanticCompletion("SELECT vill| FROM tVillage tV INNER JOIN tland tl ON tV.villageId = tl.villageId", { columnsByTable });
const columns = items.filter((item) => item.type === "column");
expect(columns.map((item) => item.label)).toEqual(expect.arrayContaining(["tV.villageId", "tV.villageName", "tl.villageId"]));
expect(columns.find((item) => item.label === "tV.villageName")).toMatchObject({ filterText: "villageName", apply: "tV.villageName" });
expect(columns.find((item) => item.label === "tl.villageId")).toMatchObject({ filterText: "villageId", apply: "tl.villageId" });
});
it("marks SELECT projection columns as batch-selectable", () => {
const columnsByTable = new Map<string, SqlCompletionColumn[]>([["users", ["id", "name"].map((name) => ({ name, table: "users" }))]]);
const { items } = semanticCompletion("SELECT | FROM users", { columnsByTable });
expect(items.filter((item) => item.type === "column")).toEqual(expect.arrayContaining([expect.objectContaining({ label: "id", apply: "id", batchSelectionMode: "select" }), expect.objectContaining({ label: "name", apply: "name", batchSelectionMode: "select" })]));
});
it("retains a typed table alias for batch-selected projection columns", () => {
const columnsByTable = new Map<string, SqlCompletionColumn[]>([["users", ["id", "name"].map((name) => ({ name, table: "users" }))]]);
const { items } = semanticCompletion("SELECT u.| FROM users u", { columnsByTable });
expect(items.filter((item) => item.type === "column")).toEqual(
expect.arrayContaining([expect.objectContaining({ label: "id", apply: "id", batchSelectionMode: "select", batchSelectionQualifier: "u" }), expect.objectContaining({ label: "name", apply: "name", batchSelectionMode: "select", batchSelectionQualifier: "u" })]),
);
});
it("retains a typed table name when the referenced table also has an alias", () => {
const columnsByTable = new Map<string, SqlCompletionColumn[]>([["users", ["id", "name"].map((name) => ({ name, table: "users" }))]]);
const { items } = semanticCompletion("SELECT users.| FROM users u", { columnsByTable });
expect(items.filter((item) => item.type === "column")).toEqual(
expect.arrayContaining([expect.objectContaining({ label: "id", apply: "id", batchSelectionMode: "select", batchSelectionQualifier: "users" }), expect.objectContaining({ label: "name", apply: "name", batchSelectionMode: "select", batchSelectionQualifier: "users" })]),
);
});
it("completes columns for aliases in comma-separated table lists", () => {
const columnsByTable = new Map<string, SqlCompletionColumn[]>([
["table_a", ["id", "name"].map((name) => ({ name, table: "table_a" }))],
["table_b", ["id", "status"].map((name) => ({ name, table: "table_b" }))],
]);
const { context, items } = semanticCompletion("SELECT * FROM table_a a, table_b b WHERE a.id = b.|", { columnsByTable });
expect(context.referencedTables).toEqual(expect.arrayContaining([expect.objectContaining({ name: "table_b", alias: "b" })]));
expect(items.filter((item) => item.type === "column").map((item) => item.label)).toEqual(["id", "status"]);
});
it("keeps a SELECT-list alias scoped when comma-separated sources follow the cursor", () => {
const columnsByTable = new Map<string, SqlCompletionColumn[]>([
["tb_kpi_set_score", ["id", "score_name"].map((name) => ({ name, table: "tb_kpi_set_score" }))],
["tb_kpi_set_score_detail", ["id", "fk_kpi_set_score_id", "detail_score"].map((name) => ({ name, table: "tb_kpi_set_score_detail" }))],
["tb_kpi_set_score_relationship", ["priority", "exclude_users_account"].map((name) => ({ name, table: "tb_kpi_set_score_relationship" }))],
]);
const { context, items } = semanticCompletion(
`SELECT
b.|
FROM tb_kpi_set_score a,
tb_kpi_set_score_detail b
WHERE a.id = b.fk_kpi_set_score_id`,
{ columnsByTable },
{ databaseType: "mysql", dialect: "mysql" },
);
expect(context.referencedTables).toEqual(expect.arrayContaining([expect.objectContaining({ name: "tb_kpi_set_score_detail", alias: "b" })]));
expect(items.filter((item) => item.type === "column").map((item) => item.label)).toEqual(["id", "fk_kpi_set_score_id", "detail_score"]);
});
it("completes correlation columns for generic PostgreSQL table functions", () => {
const { context, items } = semanticCompletion("SELECT * FROM generate_series(1, 3) g(value) WHERE g.|", {}, { databaseType: "postgres", dialect: "postgres" });
expect(context.referencedTables).toEqual(expect.arrayContaining([expect.objectContaining({ name: "g", alias: "g" })]));
expect(items.filter((item) => item.type === "column").map((item) => item.label)).toEqual(["value"]);
});
it("completes correlation columns after PostgreSQL WITH ORDINALITY", () => {
const { items } = semanticCompletion("SELECT * FROM generate_series(1, 3) WITH ORDINALITY AS g(value, ord), orders o WHERE g.|", {}, { databaseType: "postgres", dialect: "postgres" });
expect(items.filter((item) => item.type === "column").map((item) => item.label)).toEqual(["value", "ord"]);
});
it("completes later comma-separated sources after a joined table", () => {
const columnsByTable = new Map<string, SqlCompletionColumn[]>([["audit_log", ["event_id", "action"].map((name) => ({ name, table: "audit_log" }))]]);
const { context, items } = semanticCompletion("SELECT * FROM users u JOIN orders o ON o.user_id = u.id, audit_log a WHERE a.|", { columnsByTable }, { databaseType: "postgres", dialect: "postgres" });
expect(context.referencedTables).toEqual(expect.arrayContaining([expect.objectContaining({ name: "audit_log", alias: "a" })]));
expect(items.filter((item) => item.type === "column").map((item) => item.label)).toEqual(["event_id", "action"]);
});
it("completes correlation columns for aliased table sources", () => {
const columnsByTable = new Map<string, SqlCompletionColumn[]>([["table_a", ["source_id", "source_label"].map((name) => ({ name, table: "table_a" }))]]);
const { items } = semanticCompletion("SELECT * FROM table_a a(id, label), table_b b WHERE a.|", { columnsByTable }, { databaseType: "postgres", dialect: "postgres" });
expect(items.filter((item) => item.type === "column").map((item) => item.label)).toEqual(["id", "label"]);
});
it("loads real SQL Server columns after aliased table hints", () => {
const columnsByTable = new Map<string, SqlCompletionColumn[]>([["users", ["id", "name", "email"].map((name) => ({ name, table: "users" }))]]);
const { items } = semanticCompletion("SELECT * FROM users u (NOLOCK) WHERE u.|", { columnsByTable }, { databaseType: "sqlserver", dialect: "sqlserver" });
expect(items.filter((item) => item.type === "column").map((item) => item.label)).toEqual(["id", "name", "email"]);
});
it("merges partial PostgreSQL correlation names with metadata positionally", () => {
const columnsByTable = new Map<string, SqlCompletionColumn[]>([["users", ["id", "name", "email"].map((name) => ({ name, table: "users" }))]]);
const { context, items } = semanticCompletion("SELECT * FROM users u(user_id) WHERE u.|", { columnsByTable }, { databaseType: "postgres", dialect: "postgres" });
expect(context.referencedTables).toEqual(expect.arrayContaining([expect.objectContaining({ name: "users", alias: "u", columns: undefined, columnAliases: ["user_id"] })]));
expect(items.filter((item) => item.type === "column").map((item) => item.label)).toEqual(["user_id", "name", "email"]);
});
it("completes an unquoted SQL Server table named lateral", () => {
const columnsByTable = new Map<string, SqlCompletionColumn[]>([["lateral", ["id", "value"].map((name) => ({ name, table: "lateral" }))]]);
const { context, items } = semanticCompletion("SELECT * FROM lateral l WHERE l.|", { columnsByTable }, { databaseType: "sqlserver", dialect: "sqlserver" });
expect(context.referencedTables).toEqual(expect.arrayContaining([expect.objectContaining({ name: "lateral", alias: "l" })]));
expect(items.filter((item) => item.type === "column").map((item) => item.label)).toEqual(["id", "value"]);
});
it("uses CTE projected columns without remote metadata", () => {
const { items, context } = semanticCompletion("WITH recent_orders(id, total) AS (SELECT id, total FROM orders) SELECT * FROM recent_orders ro WHERE ro.|");
expect(context.exclusiveColumnSuggestions).toBe(true);
expect(items.filter((item) => item.type === "column").map((item) => item.label)).toEqual(["id", "total"]);
});
it("uses subquery projected columns without remote metadata", () => {
const { items } = semanticCompletion("SELECT * FROM (SELECT id, name AS user_name FROM users) sq WHERE sq.|");
expect(items.filter((item) => item.type === "column").map((item) => item.label)).toEqual(["id", "user_name"]);
});
it("keeps projected aliases from a multiline derived table", () => {
const { items } = semanticCompletion(
`SELECT
t.DEPTNO,
t.|
FROM (
SELECT
DEPTNO,
AVG(SAL) avg_sal,
RANK() OVER (ORDER BY AVG(SAL) DESC) AS rnk
FROM emp
GROUP BY DEPTNO
) AS t`,
{},
{ databaseType: "mysql", dialect: "mysql" },
);
expect(items.filter((item) => item.type === "column").map((item) => item.label)).toEqual(["DEPTNO", "avg_sal", "rnk"]);
});
it("expands alias star from only the qualified row source", () => {
const columnsByTable = new Map<string, SqlCompletionColumn[]>([
["users", ["id", "name"].map((name) => ({ name, table: "users" }))],
["orders", ["id", "total"].map((name) => ({ name, table: "orders" }))],
]);
const { context, items } = semanticCompletion("SELECT u.*| FROM users u JOIN orders o ON o.user_id = u.id", { columnsByTable });
const star = items.find((item) => item.label === "* \u2192 columns");
expect(context.qualifier).toBe("u");
expect(star?.apply).toBe("id, u.name");
});
it.each([
["Oracle", "oracle", "mysql", 'ID, o."created at", o."SELECT", o."safe_name", o."OrderId", o."order_id"'],
["MySQL", "mysql", "mysql", "`ID`, o.`created at`, o.`SELECT`, o.safe_name, o.`OrderId`, o.order_id"],
["ClickHouse", "clickhouse", "mysql", "ID, o.`created at`, o.`SELECT`, o.safe_name, o.OrderId, o.order_id"],
["PostgreSQL", "postgres", "postgres", '"ID", o."created at", o."SELECT", o.safe_name, o."OrderId", o.order_id'],
["SQL Server", "sqlserver", "sqlserver", "[ID], o.[created at], o.[SELECT], o.safe_name, o.[OrderId], o.order_id"],
["dialect fallback", undefined, "mysql", "`ID`, o.`created at`, o.`SELECT`, o.safe_name, o.`OrderId`, o.order_id"],
] as const)("uses %s identifier quoting in qualified star completion items", (_label, databaseType, dialect, expected) => {
const columnsByTable = new Map<string, SqlCompletionColumn[]>([["orders", ["ID", "created at", "SELECT", "safe_name", "OrderId", "order_id"].map((name) => ({ name, table: "orders" }))]]);
const starItems = semanticCompletion("SELECT o.*| FROM orders o", { columnsByTable }, { databaseType, dialect }).items;
const selectAllItems = semanticCompletion("SELECT o.| FROM orders o", { columnsByTable }, { databaseType, dialect }).items;
expect(starItems.find((item) => item.label === "* \u2192 columns")?.apply).toBe(expected);
expect(selectAllItems.find((item) => item.label === "o.*")?.apply).toBe(expected);
});
it("quotes non-ASCII/Chinese and reserved columns with backticks while leaving bare English identifiers unquoted for ClickHouse", () => {
const columnsByTable = new Map<string, SqlCompletionColumn[]>([["orders", ["id", "order_id", "姓名", "时间", "prewhere", "final", "status"].map((name) => ({ name, table: "orders" }))]]);
const { items: columnItems } = semanticCompletion("SELECT o.| FROM orders o", { columnsByTable }, { databaseType: "clickhouse", dialect: "mysql" });
expect(columnItems.find((item) => item.label === "姓名")?.apply).toBe("`姓名`");
expect(columnItems.find((item) => item.label === "时间")?.apply).toBe("`时间`");
expect(columnItems.find((item) => item.label === "prewhere")?.apply).toBe("`prewhere`");
expect(columnItems.find((item) => item.label === "final")?.apply).toBe("`final`");
expect(columnItems.find((item) => item.label === "id")?.apply).toBe("id");
expect(columnItems.find((item) => item.label === "order_id")?.apply).toBe("order_id");
expect(columnItems.find((item) => item.label === "status")?.apply).toBe("status");
const star = columnItems.find((item) => item.label === "o.*");
expect(star?.apply).toBe("id, o.order_id, o.`姓名`, o.`时间`, o.`prewhere`, o.`final`, o.status");
const { items: tableItems } = semanticCompletion(
"SELECT * FROM |",
{
tables: [
{ name: "用户表", type: "table" },
{ name: "normal_table", type: "table" },
{ name: "order", type: "table" },
],
},
{ databaseType: "clickhouse", dialect: "mysql" },
);
expect(tableItems.find((item) => item.label === "用户表")?.apply).toBe("`用户表`");
expect(tableItems.find((item) => item.label === "normal_table")?.apply).toBe("normal_table");
expect(tableItems.find((item) => item.label === "order")?.apply).toBe("`order`");
});
it("uses Oracle quoting for an unqualified multi-table star completion item", () => {
const columnsByTable = new Map<string, SqlCompletionColumn[]>([
["ORDERS", ["ID", "created at", "OrderId"].map((name) => ({ name, table: "ORDERS" }))],
["AUDIT", ["ID", "SELECT", "order_id"].map((name) => ({ name, table: "AUDIT" }))],
]);
const { items } = semanticCompletion("SELECT *| FROM ORDERS o JOIN AUDIT a ON a.ID = o.ID", { columnsByTable }, { databaseType: "oracle", dialect: "mysql" });
expect(items.find((item) => item.label === "* \u2192 columns")?.apply).toBe('o.ID, o."created at", o."OrderId", a.ID, a."SELECT", a."order_id"');
});
it("generates collision-free table aliases from semantic row sources", () => {
const { items } = semanticCompletion("SELECT * FROM order_items oi JOIN ord|", {
tables: [{ name: "order_items", type: "table" }],
autoAliasTables: true,
});
expect(items.find((item) => item.label === "order_items")?.apply).toBe("order_items oi2");
});
it("omits generated aliases on a DELETE target table (issue #9186)", () => {
const { items } = semanticCompletion("DELETE FROM DH|_MODEL_CAP", {
tables: [{ name: "DH_MODEL_CAP", schema: "DH", type: "table" }],
autoAliasTables: true,
});
expect(items.filter((item) => item.type === "table").map((item) => item.apply)).toEqual(["DH_MODEL_CAP"]);
});
it("omits generated aliases on a schema-qualified DELETE target table", () => {
const { items } = semanticCompletion("DELETE FROM DH.DH_MODEL_CAP|", {
tables: [{ name: "DH_MODEL_CAP", schema: "DH", type: "table" }],
autoAliasTables: true,
});
expect(items.filter((item) => item.type === "table").map((item) => item.apply)).toEqual(["DH_MODEL_CAP"]);
});
it("omits generated aliases while the DELETE target schema is being typed", () => {
const { items } = semanticCompletion("DELETE FROM DH.DH|", {
tables: [{ name: "DH_MODEL_CAP", schema: "DH", type: "table" }],
autoAliasTables: true,
});
// The qualifier resolves to the delete target itself, so no alias is offered.
expect(items.filter((item) => item.type === "table").map((item) => item.apply)).toEqual(["DH_MODEL_CAP"]);
});
it("omits generated aliases on an empty-prefix DELETE target (manual trigger)", () => {
const { items } = semanticCompletion("DELETE FROM |", {
tables: [{ name: "DH_MODEL_CAP", schema: "DH", type: "table" }],
autoAliasTables: true,
});
expect(items.filter((item) => item.type === "table").map((item) => item.apply)).toEqual(["DH_MODEL_CAP"]);
});
it("omits generated aliases on an empty-prefix schema-qualified DELETE target", () => {
const { items } = semanticCompletion("DELETE FROM DH.|", {
tables: [{ name: "DH_MODEL_CAP", schema: "DH", type: "table" }],
autoAliasTables: true,
});
expect(items.filter((item) => item.type === "table").map((item) => item.apply)).toEqual(["DH_MODEL_CAP"]);
});
it("omits generated aliases on empty-prefix UPDATE and INSERT targets", () => {
const updated = semanticCompletion("UPDATE |", {
tables: [{ name: "DH_MODEL_CAP", schema: "DH", type: "table" }],
autoAliasTables: true,
});
const inserted = semanticCompletion("INSERT INTO |", {
tables: [{ name: "DH_MODEL_CAP", schema: "DH", type: "table" }],
autoAliasTables: true,
});
expect(updated.items.filter((item) => item.type === "table").map((item) => item.apply)).toEqual(["DH_MODEL_CAP"]);
expect(inserted.items.filter((item) => item.type === "table").map((item) => item.apply)).toEqual(["DH_MODEL_CAP"]);
});
it("keeps generated aliases on JOIN sources inside a DELETE statement", () => {
const { items } = semanticCompletion("DELETE t1 FROM t1 JOIN ord|", {
tables: [{ name: "order_items", type: "table" }],
autoAliasTables: true,
});
expect(items.find((item) => item.label === "order_items")?.apply).toBe("order_items oi");
});
it("keeps generated aliases after a multi-table DELETE target list", () => {
const { items } = semanticCompletion("DELETE t1 FROM |", {
tables: [{ name: "order_items", type: "table" }],
autoAliasTables: true,
});
expect(items.find((item) => item.label === "order_items")?.apply).toBe("order_items oi");
});
it("keeps generated aliases on FROM and JOIN sources", () => {
const joined = semanticCompletion("SELECT * FROM DH_MODEL_CAP JOIN ord|", {
tables: [{ name: "order_items", type: "table" }],
autoAliasTables: true,
});
const queried = semanticCompletion("SELECT * FROM DH|_MODEL_CAP", {
tables: [{ name: "DH_MODEL_CAP", schema: "DH", type: "table" }],
autoAliasTables: true,
});
expect(joined.items.find((item) => item.label === "order_items")?.apply).toBe("order_items oi");
expect(queried.items.find((item) => item.label === "DH_MODEL_CAP")?.apply).toBe("DH_MODEL_CAP dmc");
});
it("preserves dialect-aware identifier quoting in apply text", () => {
const columnsByTable = new Map<string, SqlCompletionColumn[]>([["Order Details", [{ name: "User Name", table: "Order Details" }]]]);
const { items } = semanticCompletion('SELECT od."User| FROM "Order Details" od', { columnsByTable }, { databaseType: "postgres", dialect: "postgres" });
expect(items.find((item) => item.label === "User Name")?.apply).toBe('"User Name"');
});
it("suggests all target columns for insert column lists", () => {
const columnsByTable = new Map<string, SqlCompletionColumn[]>([["users", ["id", "name", "email"].map((name) => ({ name, table: "users" }))]]);
const { context, items } = semanticCompletion("INSERT INTO users (|", { columnsByTable });
expect(context.insertTable).toBe("users");
const allColumns = items.find((item) => item.type === "snippet" && item.label === "users.*");
expect(allColumns?.apply).toBe("id, name, email) VALUES (${1:value}, ${2:value}, ${3:value})");
expect(allColumns?.detail).toBe("3 columns: id, name, email) VALUES (value, value, value)");
});
it("marks INSERT target columns as batch-selectable", () => {
const columnsByTable = new Map<string, SqlCompletionColumn[]>([["users", ["id", "name"].map((name) => ({ name, table: "users" }))]]);
const { items } = semanticCompletion("INSERT INTO users (|", { columnsByTable });
expect(items.filter((item) => item.type === "column")).toEqual(expect.arrayContaining([expect.objectContaining({ label: "id", apply: "id", batchSelectionMode: "insert" }), expect.objectContaining({ label: "name", apply: "name", batchSelectionMode: "insert" })]));
});
it("uses the configured keyword case for INSERT all-column snippets", () => {
const columnsByTable = new Map<string, SqlCompletionColumn[]>([["users", ["id", "name"].map((name) => ({ name, table: "users" }))]]);
const { items } = semanticCompletion("insert into users (|", { columnsByTable, keywordCase: "lower" });
expect(items.find((item) => item.type === "snippet" && item.label === "users.*")?.apply).toBe("id, name) values (${1:value}, ${2:value})");
});
it("keeps partial INSERT INTO targets in table completion context", () => {
const { context, items } = semanticCompletion("INSERT INTO ex|", {
tables: [
{ name: "express", type: "table" },
{ name: "orders", type: "table" },
],
});
expect(context.suggestTables).toBe(true);
expect(context.exclusiveTableSuggestions).toBe(true);
expect(context.suggestColumns).toBe(false);
expect(context.referencedTables).toEqual([]);
expect(items.filter((item) => item.type === "table").map((item) => item.label)).toEqual(["express"]);
});
it("keeps partial SELECT FROM targets in table completion context", () => {
const { context, items } = semanticCompletion("SELECT * FROM ex|", {
tables: [
{ name: "express", type: "table" },
{ name: "orders", type: "table" },
],
});
expect(context.suggestTables).toBe(true);
expect(context.exclusiveTableSuggestions).toBe(true);
expect(context.qualifier).toBeUndefined();
expect(context.qualifierParts).toBeUndefined();
expect(items.filter((item) => item.type === "table").map((item) => item.label)).toEqual(["express"]);
});
it("keeps JOIN modifier completion in keyword context", () => {
const { context, items } = semanticCompletion("SELECT * FROM users left |", {
tables: [{ name: "orders", type: "table" }],
});
expect(context.suggestTables).toBe(false);
expect(context.preferredKeywords).toContain("JOIN");
expect(items[0]?.label).toBe("JOIN");
expect(items.some((item) => item.type === "table")).toBe(false);
});
it("keeps table completion after completed LEFT JOIN", () => {
const { context, items } = semanticCompletion("SELECT * FROM users left join |", {
tables: [{ name: "orders", type: "table" }],
});
expect(context.suggestTables).toBe(true);
expect(items.filter((item) => item.type === "table").map((item) => item.label)).toEqual(["orders"]);
});
it.each([
["PostgreSQL quoted table", 'SELECT * FROM "users" wh|', "postgres", "postgres"],
["PostgreSQL quoted schema.table", 'SELECT * FROM "public"."users" wh|', "postgres", "postgres"],
["PostgreSQL quoted keyword table", 'SELECT * FROM "from" wh|', "postgres", "postgres"],
["PostgreSQL quoted schema.keyword table", 'SELECT * FROM "public"."from" wh|', "postgres", "postgres"],
["MySQL backtick table", "SELECT * FROM `users` wh|", "mysql", "mysql"],
["MySQL backtick keyword table", "SELECT * FROM `join` wh|", "mysql", "mysql"],
["SQL Server bracket table", "SELECT * FROM [users] wh|", "sqlserver", "sqlserver"],
["SQL Server bracket keyword table", "SELECT * FROM [update] wh|", "sqlserver", "sqlserver"],
] as const)("offers WHERE keyword completion after a quoted prefilled table (%s)", (_label, markedSql, databaseType, dialect) => {
const { context, items } = semanticCompletion(markedSql, {}, { databaseType, dialect });
expect(context.suggestKeywords).toBe(true);
expect(items.filter((item) => item.type === "keyword").map((item) => item.label)).toEqual(expect.arrayContaining(["WHERE", "WHEN", "WITH"]));
});
});