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 { formatSqlForDisplay, formatSqlText } from "@/lib/sql/sqlFormatter";
import { normalizeSqlFormatterSettings, parseSqlFormatterConfig, serializeSqlFormatterConfig, type SqlFormatterSettings } from "@/lib/sql/sqlFormatterConfig";
import { tokenizeSqlSemantic } from "@/lib/sql/semantic/tokens";
const rules = (overrides: Partial<SqlFormatterSettings> = {}): Partial<SqlFormatterSettings> => ({ customRulesEnabled: true, ...overrides });
describe("configurable SQL formatting rules (issue #8570)", () => {
it("keeps custom rules disabled when loading existing settings", () => {
expect(normalizeSqlFormatterSettings({ keywordCase: "lower", expressionWidth: 80 })).toMatchObject({
keywordCase: "lower",
expressionWidth: 80,
customRulesEnabled: false,
});
});
it("round-trips independent rules and the reference width through the production config parser", () => {
const settings = {
customRulesEnabled: true,
listLayout: "wrap",
selectListLayout: "vertical",
insertListLayout: "singleLine",
updateListLayout: "fit",
selectCommaPosition: "before",
insertCommaPosition: "after",
updateCommaPosition: "inherit",
alignSelectAliases: false,
alignUpdateAssignments: true,
whereLogicalOperatorNewline: "before",
onLogicalOperatorNewline: "after",
expressionLogicalOperatorNewline: "none",
newlineBeforeJoinOn: true,
collapseShortStatements: false,
emptyLineMode: "single",
listItemAlignment: "indent",
expressionWidth: 76,
};
const parsed = parseSqlFormatterConfig(JSON.stringify({ version: 2, formatter: "sql-formatter", options: settings }));
expect(parsed.ok).toBe(true);
if (!parsed.ok) return;
expect(parsed.settings).toMatchObject(settings);
expect(parseSqlFormatterConfig(serializeSqlFormatterConfig(parsed.settings))).toEqual(parsed);
});
it("can keep a SELECT list on one line independently of INSERT and UPDATE", async () => {
const result = await formatSqlText("select deptno as department_number, dname as department_name, loc as department_location from dept;", "oracle", rules({ selectListLayout: "singleLine" }));
expect(result).toContain("SELECT deptno AS department_number, dname AS department_name, loc AS department_location");
});
it("can expand INSERT target columns and each VALUES tuple independently", async () => {
const result = await formatSqlText("insert into dept (deptno,dname,loc) values (10,'Accounting','New York'),(20,'Sales','Boston');", "oracle", rules({ insertListLayout: "vertical" }));
expect(result).toMatch(/dept\s*\(\s*deptno,\n\s*dname,\n\s*loc\s*\)/i);
expect(result).toMatch(/\(\s*10,\n\s*'Accounting',\n\s*'New York'\s*\)/);
expect(result).toMatch(/\(\s*20,\n\s*'Sales',\n\s*'Boston'\s*\)/);
});
it("can expand and align UPDATE assignments without collapsing the whole statement", async () => {
const result = await formatSqlText("update dept set dname='Accounting',loc='New York' where deptno=10;", "oracle", rules({ updateListLayout: "vertical", alignUpdateAssignments: true }));
expect(result).toMatch(/SET dname = 'Accounting',\n\s*loc\s+= 'New York'/);
});
it("combines right-aligned keywords with independent list rules", async () => {
const result = await formatSqlText("select a,b from dept; insert into dept (a,b) values (1,2);", "oracle", rules({ indentStyle: "tabularRight", selectListLayout: "singleLine", insertListLayout: "vertical" }));
expect(result).toMatch(/SELECT +a, b/);
expect(result).toMatch(/\(\s*a,\n\s*b\s*\)/);
});
it("gives JOIN ON and WHERE their own logical operator placement", async () => {
const result = await formatSqlText(
"select a.id from a join b on a.id=b.id and a.kind=b.kind where a.x=1 and b.y=2;",
"oracle",
rules({
selectListLayout: "singleLine",
newlineBeforeJoinOn: true,
onLogicalOperatorNewline: "after",
whereLogicalOperatorNewline: "before",
}),
);
expect(result).toMatch(/\n\s*ON a\.id = b\.id AND\n\s*a\.kind = b\.kind/);
expect(result).toMatch(/WHERE a\.x = 1\n\s*AND b\.y = 2/);
});
it("restores the original classic layout when custom rules are disabled", async () => {
const sql = "select a,b from dept where a=1 and b=2;";
const baseline = await formatSqlText(sql, "oracle", { layoutStyle: "classic" });
const disabled = await formatSqlText(sql, "oracle", { layoutStyle: "classic", customRulesEnabled: false, selectListLayout: "singleLine", whereLogicalOperatorNewline: "none" });
expect(disabled).toBe(baseline);
});
it.each(["singleLine", "fit", "vertical", "wrap"] as const)("implements the %s list mode rather than just changing the option", async (selectListLayout) => {
const options = rules({ selectListLayout, expressionWidth: 36, collapseShortStatements: false });
const result = await formatSqlText("select alpha_col,bravo_col,charlie_col from t;", "oracle", options);
if (selectListLayout === "singleLine") expect(result).toContain("SELECT alpha_col, bravo_col, charlie_col");
else if (selectListLayout === "wrap") expect(result).toMatch(/SELECT alpha_col, bravo_col,\n\s*charlie_col/);
else expect(result).toMatch(/SELECT alpha_col,\n\s*bravo_col,\n\s*charlie_col/);
expect(await formatSqlText(result, "oracle", options)).toBe(result);
});
it("honours general list rules recursively in functions, IN and a nested subquery", async () => {
const options = rules({ listLayout: "vertical", selectListLayout: "singleLine", collapseShortStatements: true });
const result = await formatSqlText("select coalesce(a,b) as x from t where id in (1,2,3) and k in (select coalesce(c,d) from u);", "oracle", options);
expect(result).toMatch(/coalesce\(a,\n\s*b\)/i);
expect(result).toMatch(/IN \(1,\n\s*2,\n\s*3\)/);
expect(result).toMatch(/coalesce\(c,\n\s*d\)/i);
expect(await formatSqlText(result, "oracle", options)).toBe(result);
});
it("does not treat a function inside an INSERT value as another INSERT tuple", async () => {
const result = await formatSqlText("insert into t (a,b) values (f(1,2),3);", "oracle", rules({ insertListLayout: "vertical", listLayout: "singleLine" }));
expect(result).toContain("f(1, 2)");
expect(result).toMatch(/f\(1, 2\),\n\s*3/);
});
it("applies expression rules inside CASE without splitting BETWEEN's AND", async () => {
const options = rules({ selectListLayout: "singleLine", expressionLogicalOperatorNewline: "after", whereLogicalOperatorNewline: "before" });
const result = await formatSqlText("select case when a=1 and b=2 then coalesce(c,d) else e end as x from t where id between 1 and 2 and k=3;", "oracle", options);
expect(result).toMatch(/WHEN a = 1 AND\n\s*b = 2 THEN/);
expect(result).toMatch(/BETWEEN 1 AND 2\n\s*AND k = 3/);
expect(await formatSqlText(result, "oracle", options)).toBe(result);
});
it("applies nested list rules inside BETWEEN bounds and composite property access", async () => {
const options = rules({ listLayout: "vertical", selectListLayout: "singleLine" });
const result = await formatSqlText("select (get_row(a,b)).value from t where n between coalesce(low_a,low_b) and coalesce(high_a,high_b);", "postgres", options);
expect(result).toMatch(/get_row\(a,\n\s*b\)/);
expect(result).toMatch(/coalesce\(low_a,\n\s*low_b\)/);
expect(result).toMatch(/coalesce\(high_a,\n\s*high_b\)/);
expect(await formatSqlText(result, "postgres", options)).toBe(result);
});
it("honours nested list rules in LIMIT and set-operation operands", async () => {
const options = rules({ listLayout: "vertical", selectListLayout: "singleLine", collapseShortStatements: false, indentStyle: "tabularRight" });
const result = await formatSqlText("select a from t limit coalesce(10,20); select a from t union all values (coalesce(1,2));", "postgres", options);
expect(result).toMatch(/LIMIT coalesce\(10,\n\s*20\)/i);
expect(result).toMatch(/coalesce\(1,\n\s*2\)/i);
expect(await formatSqlText(result, "postgres", options)).toBe(result);
});
it.each(["fit", "wrap"] as const)("keeps nested %s widths independent of the SELECT single-line rule", async (listLayout) => {
const options = rules({ listLayout, selectListLayout: "singleLine", expressionWidth: 44, collapseShortStatements: false });
const result = await formatSqlText("select coalesce(alpha_col,bravo_col,charlie_col,delta_col) from t;", "postgres", options);
if (listLayout === "fit") expect(result).toMatch(/coalesce\(alpha_col,\n\s*bravo_col,\n\s*charlie_col,\n\s*delta_col\)/);
else expect(result).toMatch(/coalesce\(alpha_col, bravo_col,\n\s*charlie_col, delta_col\)/);
expect(result.split("\n").every((line) => line.length <= 44)).toBe(true);
expect(await formatSqlText(result, "postgres", options)).toBe(result);
});
it("keeps single-line function arguments and scalar CASE items allowed to exceed the width", async () => {
const options = rules({ listLayout: "singleLine", selectListLayout: "singleLine", expressionWidth: 20, collapseShortStatements: false });
const result = await formatSqlText("select coalesce(alpha_col,bravo_col),case when a=1 then 'a long literal' else 'another long literal' end as label from t;", "postgres", options);
expect(result).toContain("SELECT coalesce(alpha_col, bravo_col), CASE WHEN a = 1 THEN 'a long literal' ELSE 'another long literal' END AS label");
expect(await formatSqlText(result, "postgres", options)).toBe(result);
});
it.each(["before", "after"] as const)("keeps SELECT annotations safe with %s commas", async (selectCommaPosition) => {
const sql = "select a as x, -- first field\n long_name as y, /* next field */\n c as z from t;";
const options = rules({ selectListLayout: "vertical", selectCommaPosition });
const result = await formatSqlText(sql, "oracle", options);
expect(result).toContain("-- first field");
expect(result).toContain("/* next field */");
const tokens = (value: string) =>
tokenizeSqlSemantic(value, "oracle")
.filter((token) => token.kind !== "comment")
.map((token) => token.normalized);
expect(tokens(result)).toEqual(tokens(sql));
expect(await formatSqlText(result, "oracle", options)).toBe(result);
});
it("allows the three DML comma and alignment rules to differ", async () => {
const options = rules({ selectListLayout: "vertical", selectCommaPosition: "before", updateListLayout: "vertical", updateCommaPosition: "after", alignSelectAliases: false, alignUpdateAssignments: true });
const result = await formatSqlText("select a as x,long_name as y from t; update t set a=1,long_name=2;", "oracle", options);
expect(result).toMatch(/SELECT a AS x\n\s*, long_name AS y/);
expect(result).toMatch(/SET a\s+= 1,\n\s*long_name = 2/);
expect(await formatSqlText(result, "oracle", options)).toBe(result);
});
it("keeps inline commas adjacent to their DML items when leading commas are selected", async () => {
const options = rules({ selectListLayout: "singleLine", insertListLayout: "singleLine", updateListLayout: "singleLine", selectCommaPosition: "before", insertCommaPosition: "before", updateCommaPosition: "before", collapseShortStatements: false });
const result = await formatSqlText("select a,b from t; insert into t (a,b) values(1,2); update t set a=1,b=2;", "oracle", options);
expect(result).toContain("SELECT a, b");
expect(result).toContain("t (a, b)");
expect(result).toContain("VALUES (1, 2)");
expect(result).toContain("SET a = 1, b = 2");
expect(await formatSqlText(result, "oracle", options)).toBe(result);
});
it("measures leading commas at the wrap boundary without adding a space before them", async () => {
const options = rules({ selectListLayout: "wrap", selectCommaPosition: "before", expressionWidth: 20, collapseShortStatements: false });
const result = await formatSqlText("select aaaaa,bbbbbb,ccc from t;", "oracle", options);
expect(result).toMatch(/SELECT aaaaa, bbbbbb\n\s*, ccc/);
expect(result.split("\n").every((line) => line.length <= 20)).toBe(true);
expect(await formatSqlText(result, "oracle", options)).toBe(result);
});
it("keeps VALUES set operands on the existing DML list rules", async () => {
const sql = "select 0 union all values (coalesce(1,2),3),(4,5);";
const options = rules({ listLayout: "vertical", insertListLayout: "inherit", selectListLayout: "singleLine", commaPosition: "before", collapseShortStatements: false });
const diagnostics: string[] = [];
const result = await formatSqlText(sql, "postgres", options, (value) => diagnostics.push(value));
expect(result).toMatch(/coalesce\(1\n\s*, 2\)\n\s*, 3\)/);
expect(result).toMatch(/\(4\n\s*, 5\)/);
expect(tokenizeSqlSemantic(result, "postgres").map((token) => token.normalized)).toEqual(tokenizeSqlSemantic(sql, "postgres").map((token) => token.normalized));
expect(diagnostics).toEqual([]);
expect(await formatSqlText(result, "postgres", options)).toBe(result);
});
it.each([
["oracle", `select coalesce("a,b", q'[x, AND 'y like z']') as note, :p as p from t where id between 1 and 2 and label='OR'; -- tail`],
["mysql", "select coalesce(`a,b`, 'x, AND y') as note, :p as p from t where id between 1 and 2 and label='OR'; -- tail"],
["postgres", `select coalesce("a,b", $tag$x,\n\nAND 'y'$tag$) as note, :p as p from t where id between 1 and 2 and label='OR'; /* tail */`],
["sqlserver", "select coalesce([a,b], 'x, AND y') as note, @p as p from t where id between 1 and 2 and label='OR'; -- tail"],
] as const)("preserves protected %s tokens and is idempotent", async (dialect, sql) => {
const options = rules({ listLayout: "vertical", selectCommaPosition: "before", emptyLineMode: "single", expressionWidth: 76 });
const protectedTokens = (value: string) =>
tokenizeSqlSemantic(value, dialect)
.filter((token) => ["comment", "string", "quoted_identifier", "parameter"].includes(token.kind))
.map((token) => token.text);
const result = await formatSqlText(sql, dialect, options);
expect(protectedTokens(result)).toEqual(protectedTokens(sql));
expect(await formatSqlText(result, dialect, options)).toBe(result);
});
it("collapses code-only empty lines without rewriting literal or block-comment paragraphs", async () => {
const sql = "select 'first\n\n\nlast' as text, a\n\n\n\nfrom t /* first\n\n\nlast */ where id=1;";
const result = await formatSqlText(sql, "oracle", rules({ emptyLineMode: "single" }));
expect(result).toContain("'first\n\n\nlast'");
expect(result).toContain("/* first\n\n\nlast */");
expect(result.replace("'first\n\n\nlast'", "literal").replace("/* first\n\n\nlast */", "comment")).not.toMatch(/\n(?:[ \t]*\n){2}/);
expect(result).not.toContain("__DBX_PRESERVE_EMPTY_LINE_");
});
it.each(["remove", "single", "preserve"] as const)("keeps alternative quoted literals intact with %s rules", async (emptyLineMode) => {
const literal = "nQ'{first 'quoted'\r\n\r\n-- AND is data\r\nlast}'";
const source = `select ${literal} as note, q'!second 'quoted'\nlast!' as other from dual;\n\nselect :id from dual;`;
const options = rules({ emptyLineMode, selectListLayout: "vertical", preserveEmptyLines: false });
const result = await formatSqlText(source, "oracle", options);
expect(result).toContain(literal);
expect(tokenizeSqlSemantic(result, "oracle").map((token) => token.normalized)).toEqual(tokenizeSqlSemantic(source, "oracle").map((token) => token.normalized));
expect(await formatSqlText(result, "oracle", options)).toBe(result);
});
it("keeps a line comment boundary even when WHERE rules request no logical breaks", async () => {
const source = "select a,b from t where x=1 -- note\r\nand y=2;";
const options = rules({ whereLogicalOperatorNewline: "none" });
const result = await formatSqlText(source, "postgres", options);
expect(result).toMatch(/-- note\n\s*AND y = 2/);
expect(tokenizeSqlSemantic(result, "postgres").map((token) => token.normalized)).toEqual(tokenizeSqlSemantic(source, "postgres").map((token) => token.normalized));
expect(await formatSqlText(result, "postgres", options)).toBe(result);
});
it.each(["preserve", "single", "inherit"] as const)("does not mistake quoted or commented marker-like lines for %s empty lines", async (emptyLineMode) => {
const sql = "select 'first\n-- __DBX_PRESERVE_EMPTY_LINE_0_0__\nlast' as note from t;\n\nselect 2; /* first\n-- __DBX_PRESERVE_EMPTY_LINE_0_0__\nlast */";
const options = rules({ emptyLineMode, preserveEmptyLines: true });
const protectedTokens = (value: string) =>
tokenizeSqlSemantic(value, "oracle")
.filter((token) => ["string", "comment"].includes(token.kind))
.map((token) => token.text);
const result = await formatSqlText(sql, "oracle", options);
expect(protectedTokens(result)).toEqual(protectedTokens(sql));
expect(await formatSqlText(result, "oracle", options)).toBe(result);
});
it("keeps complete PL/SQL blocks on the established base formatter and reports the fallback", async () => {
const sql = "begin select a,b into x,y from t; x:=1; end;";
const base = await formatSqlText(sql, "oracle", { layoutStyle: "classic" });
const diagnostics: string[] = [];
const result = await formatSqlText(sql, "oracle", rules({ layoutStyle: "classic" }), (diagnostic) => diagnostics.push(diagnostic));
expect(result).toBe(base);
expect(diagnostics).toEqual(["custom-rules-fallback"]);
});
it.each(["remove", "single"] as const)("restores saved base spacing when %s rules decline a procedure script", async (emptyLineMode) => {
const sql = "-- heading\n\nbegin select 1 into x from dual; x:=1; end; select 2 from dual;";
const saved = { layoutStyle: "classic", preserveEmptyLines: true, linesBetweenQueries: 2 } as const;
const base = await formatSqlText(sql, "oracle", saved);
const diagnostics: string[] = [];
const result = await formatSqlText(sql, "oracle", rules({ ...saved, emptyLineMode }), (value) => diagnostics.push(value));
expect(result).toBe(base);
expect(diagnostics).toEqual(["custom-rules-fallback"]);
});
it("falls back for a complete FUNCTION while its independent SQL selection uses the rules", async () => {
const query = "select deptno,dname into v_id,v_name from dept where deptno=p_id and loc='Boston';";
const sql = `create or replace function department_label(p_id number) return varchar2 is\n v_id number;\n v_name varchar2(100);\nbegin\n ${query}\n v_name := trim(v_name);\n return v_name;\nexception when no_data_found then return 'unknown';\nend;`;
const options = rules({ layoutStyle: "classic", selectListLayout: "vertical" });
const diagnostics: string[] = [];
const base = await formatSqlText(sql, "oracle", { layoutStyle: "classic" });
expect(await formatSqlText(sql, "oracle", options, (value) => diagnostics.push(value))).toBe(base);
expect(diagnostics).toEqual(["custom-rules-fallback"]);
diagnostics.length = 0;
expect(await formatSqlText(query, "oracle", options, (value) => diagnostics.push(value))).toMatch(/SELECT deptno,\n\s*dname/);
expect(diagnostics).toEqual([]);
});
it("retains the source if neither rule nor base formatting can parse the input", async () => {
const sql = "declare v_name varchar2(100) := 'unfinished";
const diagnostics: string[] = [];
await expect(formatSqlText(sql, "oracle", rules(), (value) => diagnostics.push(value))).rejects.toThrow();
expect(diagnostics).toEqual([]);
expect(await formatSqlForDisplay(sql, "oracle", rules())).toBe(sql);
});
it.each(["firstItem", "indent"] as const)("positions list continuations with %s alignment", async (listItemAlignment) => {
const result = await formatSqlText("select a,b from t;", "oracle", rules({ listItemAlignment, selectListLayout: "vertical", tabWidth: 4 }));
expect(result).toContain(`SELECT a,\n${" ".repeat(listItemAlignment === "firstItem" ? 7 : 4)}b`);
});
it("lets ON none override the global operator break without changing WHERE", async () => {
const options = rules({ newlineBeforeJoinOn: true, onLogicalOperatorNewline: "none", logicalOperatorNewline: "before", selectListLayout: "singleLine" });
const result = await formatSqlText("select a.id from a join b on a.id=b.id and a.x=b.x where a.y=1 and b.z=2;", "oracle", options);
expect(result).toMatch(/ON a\.id = b\.id AND a\.x = b\.x/);
expect(result).toMatch(/WHERE a\.y = 1\n\s*AND b\.z = 2/);
expect(await formatSqlText(result, "oracle", options)).toBe(result);
});
it("keeps DBX placeholders and an indivisible long literal intact at a narrow width", async () => {
const literal = "'a comma, AND a very long literal that must not be split'";
const sql = "select ${like} as x, #{Id} as y, " + literal + " as z from t;";
const options = rules({ expressionWidth: 20, selectListLayout: "wrap", selectCommaPosition: "before", functionCase: "lower" });
const result = await formatSqlText(sql, "mysql", options);
expect(result).toContain("${like}");
expect(result).toContain("#{Id}");
expect(result).toContain(literal);
expect(await formatSqlText(result, "mysql", options)).toBe(result);
});
it.each(["standard", "tabularLeft", "tabularRight"] as const)("uses %s keyword columns together with list rules", async (indentStyle) => {
const options = rules({ indentStyle, selectListLayout: "vertical", collapseShortStatements: false });
const result = await formatSqlText("select a,b from t where id=1;", "oracle", options);
const lines = result.split("\n");
const select = lines.find((line) => /\bSELECT\b/.test(line))!;
const from = lines.find((line) => /\bFROM\b/.test(line))!;
const where = lines.find((line) => /\bWHERE\b/.test(line))!;
if (indentStyle === "tabularLeft") expect([select.indexOf("a"), from.indexOf("t"), where.indexOf("id")]).toEqual([7, 7, 7]);
else if (indentStyle === "tabularRight") expect([select.indexOf("SELECT") + 6, from.indexOf("FROM") + 4, where.indexOf("WHERE") + 5]).toEqual([6, 6, 6]);
else expect([select.indexOf("SELECT"), from.indexOf("FROM"), where.indexOf("WHERE")]).toEqual([0, 0, 0]);
expect(await formatSqlText(result, "oracle", options)).toBe(result);
});
it("keeps the first keyword's right padding when a later keyword is longer", async () => {
const options = rules({ indentStyle: "tabularRight", selectListLayout: "vertical", collapseShortStatements: false });
const result = await formatSqlText("select a,b from t order by a,b;", "oracle", options);
expect(result).toMatch(/^ SELECT a,/);
expect(result).toMatch(/\n FROM t/);
expect(result).toMatch(/\nORDER BY a, b/);
expect(await formatSqlText(result, "oracle", options)).toBe(result);
});
it("lets all DML lists inherit the general rule without losing their individual values", async () => {
const options = rules({ listLayout: "singleLine", selectListLayout: "inherit", insertListLayout: "inherit", updateListLayout: "inherit", collapseShortStatements: false });
const result = await formatSqlText("select a,b from t; insert into t (a,b) values(1,2); update t set a=1,b=2;", "oracle", options);
expect(result).toContain("SELECT a, b");
expect(result).toContain("t (a, b)");
expect(result).toContain("VALUES (1, 2)");
expect(result).toContain("SET a = 1, b = 2");
});
it.each(["remove", "single", "preserve", "inherit"] as const)("applies the %s empty-line mode to code paragraphs", async (emptyLineMode) => {
const result = await formatSqlText("-- heading\n\n\nSELECT a,b FROM t;", "oracle", rules({ emptyLineMode, preserveEmptyLines: true }));
const breaks = emptyLineMode === "remove" ? "\n" : emptyLineMode === "single" ? "\n\n" : "\n\n\n";
expect(result).toContain(`-- heading${breaks}SELECT`);
expect(result).not.toContain("__DBX_PRESERVE_EMPTY_LINE_");
});
it.each([
["remove", 0],
["single", 1],
["preserve", 2],
["inherit", 2],
] as const)("limits generated query spacing with emptyLineMode=%s", async (emptyLineMode, blankLines) => {
const options = rules({ emptyLineMode, linesBetweenQueries: 2 });
const result = await formatSqlText("select 1; select 2;", "oracle", options);
expect(result).toBe(`SELECT 1;${"\n".repeat(blankLines + 1)}SELECT 2;`);
expect(await formatSqlText(result, "oracle", options)).toBe(result);
});
it("preserves the saved query spacing when custom rules are disabled", async () => {
expect(await formatSqlText("select 1; select 2;", "oracle", { customRulesEnabled: false, emptyLineMode: "remove", linesBetweenQueries: 2 })).toBe("SELECT 1;\n\n\nSELECT 2;");
});
it.each(["preserve", "upper", "lower"] as const)("keeps functionCase=%s when arguments must expand", async (functionCase) => {
const result = await formatSqlText("select CoAlEsCe(a,b) from t;", "postgres", rules({ listLayout: "vertical", selectListLayout: "singleLine", functionCase }));
const name = functionCase === "preserve" ? "CoAlEsCe" : functionCase === "upper" ? "COALESCE" : "coalesce";
expect(result).toMatch(new RegExp(`${name}\\(a,\\n\\s*b\\)`));
});
});
describe("Oracle formatting regressions #13650", () => {
const settings = { customRulesEnabled: true, listLayout: "vertical", selectListLayout: "vertical" } as const;
it.each([false, true])("aligns fields after standalone comments (tabs=%s)", async (useTabs) => {
const sql = "select a,\n-- h2.f_get_busnorgshortname(max(t.d_busno)) as busno,\nmax(t.bargainno) as bargainno,max(t.bargainid) as bargainid from t;";
const options = { ...settings, useTabs };
const result = await formatSqlText(sql, "oracle", options);
const lines = result.split("\n");
const comment = lines.find((line) => line.includes("-- h2."))!;
const field = lines.find((line) => line.includes("max(t.bargainno)"))!;
const next = lines.find((line) => line.includes("max(t.bargainid)"))!;
expect(comment.trimStart()).toBe("-- h2.f_get_busnorgshortname(max(t.d_busno)) as busno,");
expect(field.match(/^\s*/)?.[0]).toBe(next.match(/^\s*/)?.[0]);
expect(comment.match(/^\s*/)?.[0]).toBe(next.match(/^\s*/)?.[0]);
expect(result).not.toContain("\n\n--");
expect(tokenizeSqlSemantic(result, "oracle").map((token) => token.normalized)).toEqual(tokenizeSqlSemantic(sql, "oracle").map((token) => token.normalized));
expect(await formatSqlText(result, "oracle", options)).toBe(result);
});
it.each(["oracle", "postgres", "mysql"] as const)("aligns multiline CASE and END with the field in %s", async (dialect) => {
const sql = "select a.entryid,case when employeename like '测试%' then employeename else employeename end as result_label from employee_special_tl a;";
const result = await formatSqlText(sql, dialect, settings);
expect(result).toContain("\n CASE\n WHEN employeename LIKE '测试%' THEN\n employeename\n ELSE\n employeename\n END AS result_label");
expect(tokenizeSqlSemantic(result, dialect).map((token) => token.normalized)).toEqual(tokenizeSqlSemantic(sql, dialect).map((token) => token.normalized));
expect(await formatSqlText(result, dialect, settings)).toBe(result);
});
});