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。

56.8 KB
Raw Blame
import { describe, expect, it } from "vitest";
import {
  canFormatSqlForDatabaseType,
  compressSqlText,
  formatSqlForDisplay as formatSqlForDisplayWithBuildDefaults,
  formatSqlForEditing as formatSqlForEditingWithBuildDefaults,
  formatSqlText as formatSqlTextWithBuildDefaults,
  MAX_SQL_FORMAT_CHARS,
  sqlFormatDialectForDbType,
  UnsupportedStructuredInputError,
} from "@/lib/sql/sqlFormatter";
import type { SqlFormatDialect } from "@/lib/sql/sqlFormatter";
import type { SqlFormatterSettings } from "@/lib/sql/sqlFormatterConfig";
import { extractSqlParameters } from "@/lib/sql/sqlParameters";
import { tokenizeSqlSemantic } from "@/lib/sql/semantic/tokens";

// Most cases in this regression suite pin the former compact layout while
// checking dialect-specific token handling. New default-layout behavior is
// exercised explicitly with the build-default aliases below.
function withDbxLayout(settings: Partial<SqlFormatterSettings> = {}): Partial<SqlFormatterSettings> {
  return { layoutStyle: "dbx", ...settings };
}

const formatSqlText = (sql: string, dialect: SqlFormatDialect = "generic", settings: Partial<SqlFormatterSettings> = {}) => formatSqlTextWithBuildDefaults(sql, dialect, withDbxLayout(settings));
const formatSqlForEditing = (sql: string, dialect: SqlFormatDialect = "generic", settings: Partial<SqlFormatterSettings> = {}) => formatSqlForEditingWithBuildDefaults(sql, dialect, withDbxLayout(settings));
const formatSqlForDisplay = (sql: string, dialect: SqlFormatDialect = "generic", settings: Partial<SqlFormatterSettings> = {}) => formatSqlForDisplayWithBuildDefaults(sql, dialect, withDbxLayout(settings));

describe("Neo4j Cypher formatting", () => {
  const reproduction = `MATCH (a:LibraryDomainOrganization) WHERE a.id= $id CALL apoc.path.expandConfig(a, {uniqueness:'RELATIONSHIP_GLOBAL', minLevel:0, maxLevel:-1, relationshipFilter:'HasLibrary>|HasLibraryParameter>' }) YIELD path UNWIND relationships(path) as r WITH DISTINCT r WITH r, startNode(r) as s, endNode(r) as e OPTIONAL MATCH (s)-[:panda_data_master_version_rel]-(so) OPTIONAL MATCH (e)-[:panda_data_master_version_rel]-(eo) WITH r, CASE WHEN s.classType = 'COMMON' OR s.classType = 'INTERFACE' THEN s WHEN type(r) = 'panda_data_master_version_rel' THEN null ELSE so END as sm OPTIONAL MATCH (sm)-[permission:panda_dataPermission]->(target) WHERE target:panda_member AND target.id IN $memberIds AND permission.permissionType IN ['OWNER','ACTIVE','PASSIVE'] RETURN type(r) as relationType, sm, [s, e] as endpoints`;

  it("uses the Cypher path for Neo4j and formats the reported query shape", async () => {
    expect(canFormatSqlForDatabaseType("neo4j")).toBe(true);
    expect(sqlFormatDialectForDbType("neo4j")).toBe("cypher");

    const formatted = await formatSqlForEditing(reproduction, sqlFormatDialectForDbType("neo4j"));

    expect(formatted).not.toBe(reproduction);
    expect(formatted).toContain("MATCH (a:LibraryDomainOrganization)");
    expect(formatted).toContain("CALL apoc.path.expandConfig(a, {uniqueness: 'RELATIONSHIP_GLOBAL'");
    expect(formatted).toContain("relationshipFilter: 'HasLibrary>|HasLibraryParameter>'");
    expect(formatted).toContain("OPTIONAL MATCH (s)-[:panda_data_master_version_rel]-(so)");
    expect(formatted).toContain("target:panda_member");
    expect(formatted).toContain("IN ['OWNER', 'ACTIVE', 'PASSIVE']");
    expect(formatted).toContain("$memberIds");
    expect(formatted).toContain("RETURN type(r) AS relationType");
    expect(formatted.split("\n").length).toBeGreaterThan(8);
  });

  it("keeps comments, strings, labels, map keys, parameters, and nested values opaque", async () => {
    const source = `// MATCH and RETURN in this comment\nMATCH (n:CaseSensitive {match: 'RETURN WHERE', values: ['CALL', '$id']}) WHERE n.note = "WITH RETURN" RETURN n`;
    const formatted = await formatSqlForEditing(source, "cypher");

    expect(formatted).toContain("// MATCH and RETURN in this comment");
    expect(formatted).toContain("(n:CaseSensitive {match: 'RETURN WHERE', values: ['CALL', '$id']})");
    expect(formatted).toContain('n.note = "WITH RETURN"');
  });

  it("keeps block comments opaque", async () => {
    const source = "/* RETURN MATCH {not: 'code'} */ MATCH (n:Person) RETURN n";
    const formatted = await formatSqlForEditing(source, "cypher");

    expect(formatted).toContain("/* RETURN MATCH {not: 'code'} */");
    expect(formatted).toContain("MATCH (n:Person)");
  });

  it("formats a selected Cypher fragment with the same entry point", async () => {
    const selected = "OPTIONAL MATCH (n:Person)-[:KNOWS]->(m) RETURN n, m";
    const formatted = await formatSqlForEditing(selected, "cypher");

    expect(formatted).toContain("OPTIONAL MATCH (n:Person)-[:KNOWS]->(m)");
    expect(formatted).toContain("RETURN n,\n  m");
  });

  it("does not let a Cypher line comment swallow compressed query text", () => {
    expect(compressSqlText("// keep the note\nMATCH (n:Person) RETURN n", "cypher")).toBe("MATCH (n:Person) RETURN n");
  });

  it("returns unsupported or incomplete Cypher unchanged", async () => {
    const unsupported = "MATCH (n) RETURN n ≠ 1";
    const incomplete = "MATCH (n RETURN n";
    const incompleteParameter = "MATCH (n) RETURN ${id";

    await expect(formatSqlForEditing(unsupported, "cypher")).resolves.toBe(unsupported);
    await expect(formatSqlForEditing(incomplete, "cypher")).resolves.toBe(incomplete);
    await expect(formatSqlForEditing(incompleteParameter, "cypher")).resolves.toBe(incompleteParameter);
  });
});

describe("sqlFormatter", () => {
  it("disables SQL formatting for Redis and VictoriaMetrics queries", () => {
    expect(canFormatSqlForDatabaseType("redis")).toBe(false);
    expect(canFormatSqlForDatabaseType("victoriametrics")).toBe(false);
    expect(canFormatSqlForDatabaseType("mysql")).toBe(true);
  });

  it("preserves source empty lines only when configured", async () => {
    const sql = "-- tstt\n\nSELECT * FROM AUX_TABLE AS au LIMIT 100;";
    const consecutiveEmptyLines = "-- section\n\n\nSELECT 1;";
    const queriesWithEmptyLine = "SELECT 1;\n\nSELECT 2;";
    const queriesWithTwoEmptyLines = "SELECT 1;\n\n\nSELECT 2;";

    const defaultFormatted = await formatSqlForEditing(sql, "generic");
    const preserved = await formatSqlForEditing(sql, "generic", { preserveEmptyLines: true });
    const preservedConsecutive = await formatSqlForEditing(consecutiveEmptyLines, "generic", { preserveEmptyLines: true });
    const preservedQueriesNoSpacing = await formatSqlForEditing(queriesWithEmptyLine, "generic", { preserveEmptyLines: true, linesBetweenQueries: 0 });
    const preservedQueries = await formatSqlForEditing(queriesWithEmptyLine, "generic", { preserveEmptyLines: true, linesBetweenQueries: 1 });
    const preservedQueriesWideSpacing = await formatSqlForEditing(queriesWithEmptyLine, "generic", { preserveEmptyLines: true, linesBetweenQueries: 2 });
    const preservedQueriesWithTwoEmptyLines = await formatSqlForEditing(queriesWithTwoEmptyLines, "generic", { preserveEmptyLines: true, linesBetweenQueries: 1 });

    expect(defaultFormatted).toContain("-- tstt\nSELECT");
    expect(preserved).toContain("-- tstt\n\nSELECT");
    expect(preservedConsecutive).toContain("-- section\n\n\nSELECT");
    expect(preservedQueriesNoSpacing).toBe("SELECT 1;\n\nSELECT 2;");
    expect(preservedQueries).toBe("SELECT 1;\n\nSELECT 2;");
    expect(preservedQueriesWideSpacing).toBe("SELECT 1;\n\n\nSELECT 2;");
    expect(preservedQueriesWithTwoEmptyLines).toBe("SELECT 1;\n\n\nSELECT 2;");
    expect(preserved).not.toContain("__DBX_PRESERVE_EMPTY_LINE_");
  });

  it("maps PostgreSQL-compatible database types to the postgres formatter dialect", () => {
    for (const dbType of ["postgres", "kwdb", "gaussdb", "opengauss", "questdb", "kingbase", "highgo", "vastbase", "redshift"]) {
      expect(sqlFormatDialectForDbType(dbType)).toBe("postgres");
    }
  });

  it("maps SQLite-compatible database types to the sqlite formatter dialect", () => {
    for (const dbType of ["sqlite", "rqlite", "turso", "cloudflare-d1"]) {
      expect(sqlFormatDialectForDbType(dbType)).toBe("sqlite");
    }
  });

  it("maps Dameng to its scoped formatter dialect", () => {
    expect(sqlFormatDialectForDbType("dameng")).toBe("dameng");
  });

  it("maps DuckDB to its scoped formatter dialect", () => {
    expect(sqlFormatDialectForDbType("duckdb")).toBe("duckdb");
  });

  it("maps Oracle and OceanBase Oracle mode to the PL/SQL formatter dialect", () => {
    expect(sqlFormatDialectForDbType("oracle")).toBe("oracle");
    // OceanBase Oracle mode speaks Oracle SQL — issue #7540: it must reuse the
    // Oracle dialect so view DDL previews are formatted instead of one line.
    expect(sqlFormatDialectForDbType("oceanbase-oracle")).toBe("oracle");
  });

  it("formats an OceanBase Oracle single-line view DDL into readable multi-line SQL (issue #7540)", async () => {
    const singleLine = `create or replace view "APP"."ACTIVE_USERS" as select ID, NAME, EMAIL, CREATED_AT from USERS where STATUS = 'ACTIVE' and DELETED_AT is null order by CREATED_AT desc`;
    const formatted = await formatSqlForDisplay(singleLine, sqlFormatDialectForDbType("oceanbase-oracle"));

    expect(formatted).not.toBe(singleLine);
    expect(formatted.split("\n").length).toBeGreaterThan(1);
    expect(formatted).toContain("CREATE OR REPLACE VIEW");
    expect(formatted).toMatch(/\bSELECT\b/);
    expect(formatted).toMatch(/\bFROM\b/);
    expect(formatted).toMatch(/\bWHERE\b/);
    // String literals and quoted identifiers must survive formatting untouched.
    expect(formatted).toContain("'ACTIVE'");
    expect(formatted).toContain('"ACTIVE_USERS"');
  });

  it("keeps a view's trailing line comment from swallowing the next column (issue #10278)", async () => {
    // A `--` comment consumes the rest of its line. Keeping it on the previous
    // field's line is safe only when the following column starts a new line,
    // otherwise the column would silently disappear from the DDL shown to the user.
    const viewDdl = `CREATE VIEW "TEMP_TEST_VIEW" AS SELECT trunc(sysdate) AS dates, -- 测试\n(SELECT sysdate FROM dual t) AS nows\nFROM dual;`;
    const formatted = await formatSqlForDisplay(viewDdl, sqlFormatDialectForDbType("oracle"));

    expect(formatted).toBe('CREATE VIEW "TEMP_TEST_VIEW" AS\nSELECT trunc(sysdate) AS dates, -- 测试\n       (SELECT sysdate FROM dual t) AS nows\nFROM dual;');
  });

  it("keeps a short OceanBase Oracle view projection readable (issue #7540)", async () => {
    // A view body still keeps each projected field on its own line while its
    // surrounding clauses retain the default layout.
    const singleLine = `create or replace view "APP"."ACTIVE_USERS" as select ID, NAME from USERS where STATUS = 'ACTIVE'`;
    const formatted = await formatSqlForDisplay(singleLine, sqlFormatDialectForDbType("oceanbase-oracle"));

    expect(formatted).toBe(`CREATE OR REPLACE VIEW "APP"."ACTIVE_USERS" AS
SELECT ID,
       NAME
FROM USERS
WHERE STATUS = 'ACTIVE'`);
    expect(formatted).toContain("'ACTIVE'");
    expect(formatted).toContain('"ACTIVE_USERS"');
  });

  it("formats a bare OceanBase Oracle view source wrapped as CREATE VIEW (issue #7540)", async () => {
    // Mirrors the backend build_view_ddl_sql output for a fallback ALL_VIEWS.TEXT
    // body: `CREATE VIEW <name> AS <single-line SELECT>`.
    const wrapped = `create view "APP"."ACTIVE_USERS" as
select ID, NAME, EMAIL, CREATED_AT from USERS where STATUS = 'ACTIVE' and DELETED_AT is null order by CREATED_AT desc;`;
    const formatted = await formatSqlForDisplay(wrapped, sqlFormatDialectForDbType("oceanbase-oracle"));

    expect(formatted.split("\n").length).toBeGreaterThan(2);
    expect(formatted).toMatch(/\bSELECT\b/);
    expect(formatted).toMatch(/\bFROM\b/);
    expect(formatted).toMatch(/\bWHERE\b/);
    expect(formatted).toContain("'ACTIVE'");
  });

  it("does not split string literals when formatting an OceanBase Oracle view (issue #7540)", async () => {
    const singleLine = `CREATE OR REPLACE VIEW "APP"."V" AS SELECT 'SELECT X FROM Y' AS TXT FROM DUAL`;
    const formatted = await formatSqlForDisplay(singleLine, sqlFormatDialectForDbType("oceanbase-oracle"));

    expect(formatted).toContain("'SELECT X FROM Y'");
  });

  it("leaves an already multi-line OceanBase Oracle view DDL semantically intact (issue #7540)", async () => {
    const multiLine = `CREATE OR REPLACE VIEW "APP"."ACTIVE_USERS" AS
SELECT
  ID,
  NAME
FROM USERS
WHERE STATUS = 'ACTIVE';`;
    const formatted = await formatSqlForDisplay(multiLine, sqlFormatDialectForDbType("oceanbase-oracle"));

    expect(formatted).toContain('"ACTIVE_USERS"');
    expect(formatted).toContain("'ACTIVE'");
    expect(formatted).toMatch(/\bID\b/);
    expect(formatted).toMatch(/\bNAME\b/);
    expect(formatted).toMatch(/\bWHERE\b/);
  });

  it("keeps issue #7138 Oracle hierarchy clauses intact", async () => {
    const sql = "SELECT ctt.u_dm, ctt.u_mc, ctt.parent_id, ctt.level_num, ctt.create_time FROM cte_test ctt WHERE ctt.status = 'A' AND ctt.deleted = 0 START WITH ctt.su_dm IN ('16','17','18') CONNECT BY PRIOR ctt.u_dm = ctt.su_dm;";

    // `START WITH` and `CONNECT BY PRIOR` are clauses of their own, not part of
    // the WHERE condition: each opens a line, and the hierarchy is never dropped.
    await expect(formatSqlForEditing(sql, sqlFormatDialectForDbType("oracle"))).resolves.toBe(`SELECT ctt.u_dm,
       ctt.u_mc,
       ctt.parent_id,
       ctt.level_num,
       ctt.create_time
FROM cte_test ctt
WHERE ctt.status = 'A'
      AND ctt.deleted = 0
START WITH ctt.su_dm IN ('16', '17', '18')
CONNECT BY PRIOR ctt.u_dm = ctt.su_dm;`);
  });

  it("collapses a short Oracle hierarchy query onto one line", async () => {
    const sql = "SELECT ctt.U_DM FROM cte_test ctt START WITH ctt.SU_DM IN ('16','17','18','19') CONNECT BY PRIOR ctt.U_DM = HY.SU_DM;";

    await expect(formatSqlForEditing(sql, sqlFormatDialectForDbType("oracle"))).resolves.toBe(`SELECT ctt.U_DM FROM cte_test ctt START WITH ctt.SU_DM IN ('16', '17', '18', '19') CONNECT BY PRIOR ctt.U_DM = HY.SU_DM;`);
  });

  it("formats valid Oracle hierarchy clauses with the same alias", async () => {
    const sql = "select ctt.u_dm, ctt.u_mc, ctt.parent_id, ctt.level_num, ctt.create_time from cte_test ctt where ctt.status = 'A' and ctt.deleted = 0 start with ctt.su_dm = '16' connect by prior ctt.u_dm = ctt.su_dm;";

    const formatted = await formatSqlForEditing(sql, sqlFormatDialectForDbType("oracle"));

    expect(formatted).toContain("FROM cte_test ctt");
    expect(formatted).toContain("\nSTART WITH ctt.su_dm = '16'");
    expect(formatted).toContain("\nCONNECT BY PRIOR ctt.u_dm = ctt.su_dm;");
  });

  it("formats ordinary Oracle SQL and anonymous PL/SQL", async () => {
    await expect(formatSqlForEditing("select employee_id from employees where department_id = 10;", sqlFormatDialectForDbType("oracle"))).resolves.toBe("SELECT employee_id FROM employees WHERE department_id = 10;");
    await expect(formatSqlForEditing("declare v_count number := 1; begin v_count := v_count + 1; end;", sqlFormatDialectForDbType("oracle"))).resolves.toBe("DECLARE v_count number := 1;\n\nBEGIN v_count := v_count + 1;\n\nEND;");
  });

  it("keeps incomplete Oracle editor SQL unchanged", async () => {
    const sql = "select *\nfrom dbname.\n;";

    await expect(formatSqlForEditing(sql, sqlFormatDialectForDbType("oracle"))).resolves.toBe(sql);
  });

  it("keeps DuckDB prefix aliases out of formatted named parameters", async () => {
    const cases = [
      ["select 日期:date(订单日期) from 订单;", []],
      ['select total:price * quantity, "order":sum(amount) from sales;', []],
      ["from r:range(:row_count) select total:r.range + :offset;", ["row_count", "offset"]],
      ["select res: col1 + col2, root: sqrt(col1) from tbl;", []],
    ] as const;

    for (const [sql, expectedParameters] of cases) {
      const formatted = await formatSqlText(sql, sqlFormatDialectForDbType("duckdb"));

      expect(formatted).not.toBe(sql);
      expect(extractSqlParameters(formatted, { databaseType: "duckdb" })).toEqual(expectedParameters);
      expect(formatted).not.toContain("__DBX_DUCKDB_PREFIX_ALIAS_COLON_");
    }
  });

  it("keeps ordinary DuckDB named parameters enabled independently of prefix aliases", async () => {
    const formatted = await formatSqlText("select :outside, total:price from sales;", sqlFormatDialectForDbType("duckdb"));

    expect(extractSqlParameters(formatted, { databaseType: "duckdb" })).toEqual(["outside"]);
    expect(extractSqlParameters(formatted, { databaseType: "duckdb", enabledSyntaxes: [] })).toEqual([]);
  });

  it("does not change generic formatting of compact colon syntax", async () => {
    const formatted = await formatSqlText("select total:price from sales;", "generic");

    expect(extractSqlParameters(formatted, { databaseType: "postgres" })).toEqual(["price"]);
  });

  it("preserves DuckDB strings and comments while protecting prefix aliases", async () => {
    const sql = `select total:price, 'literal:date', $$dollar:date
AND inside
OR inside$$ as note
      from sales /* alias:date */ /*__DBX_DUCKDB_PREFIX_ALIAS_COLON_0__*/ -- trailing:date`;
    const formatted = await formatSqlText(sql, sqlFormatDialectForDbType("duckdb"), { logicalOperatorNewline: "none" });

    expect(extractSqlParameters(formatted, { databaseType: "duckdb" })).toEqual([]);
    expect(formatted).toContain("'literal:date'");
    expect(formatted).toContain("$$dollar:date\nAND inside\nOR inside$$");
    expect(formatted).toContain("/* alias:date */");
    expect(formatted).toContain("/*__DBX_DUCKDB_PREFIX_ALIAS_COLON_0__*/");
    expect(formatted).toContain("-- trailing:date");
  });

  it("keeps DuckDB casts, named arguments, and their real parameters intact", async () => {
    const sql = "select struct_pack(key := :value), total:price, value::integer;";
    const formatted = await formatSqlText(sql, sqlFormatDialectForDbType("duckdb"));

    expect(extractSqlParameters(formatted, { databaseType: "duckdb" })).toEqual(["value"]);
    expect(formatted).toContain("value::integer");
  });

  it("returns unsupported DuckDB struct literals unchanged without leaking formatter markers", async () => {
    const sql = "select {'key': value, nested: {'inner': inner_value}}, total:price;";
    const formatted = await formatSqlForEditing(sql, sqlFormatDialectForDbType("duckdb"));

    expect(formatted).toBe(sql);
    expect(extractSqlParameters(formatted, { databaseType: "duckdb" })).toEqual([]);
    expect(formatted).not.toContain("__DBX_DUCKDB_PREFIX_ALIAS_COLON_");
  });

  it("returns malformed DuckDB SQL unchanged without leaking formatter markers", async () => {
    const sql = "select total:price from dbname.\n;";

    const formatted = await formatSqlForEditing(sql, sqlFormatDialectForDbType("duckdb"));

    expect(formatted).toBe(sql);
    expect(formatted).not.toContain("__DBX_DUCKDB_PREFIX_ALIAS_COLON_");
  });

  it("preserves ClickHouse lambda arrows when formatting issue #3573 SQL", async () => {
    const sql = `
      WITH industry_code_donghua_id_RYCzfD AS (SELECT id
      FROM cd.industry_code_donghua
      WHERE cd.industry_code_donghua.code IN ('INB0709', 'INB0004'))
      SELECT id,ent_short,arrayMap(x->dictGet(cd.industry_donghua_dict,'name',x),prefer_industry) as prefer_industry_name,org_type,company_id,arrayCount(\`investment.be_company_id\` -> 1, \`investment.be_company_id\`) as be_company_count
      FROM search_donghua.investor
      WHERE arrayExists(x -> x IN industry_code_donghua_id_RYCzfD, prefer_industry)
      ORDER BY be_company_count DESC,id ASC
      LIMIT 0,10
    `;

    const formatted = await formatSqlText(sql, sqlFormatDialectForDbType("clickhouse"));

    expect(formatted).toContain("x -> dictGet");
    expect(formatted).not.toContain("- >");
  });

  it("preserves the ClickHouse table alias from issue #7079", async () => {
    const formatted = await formatSqlText("SELECT *\nFROM MATERIAL m\nLIMIT 100;", sqlFormatDialectForDbType("clickhouse"));

    expect(formatted).toBe("SELECT * FROM MATERIAL m LIMIT 100;");
  });

  it.each(["d", "dd", "h", "hh", "m", "mcs", "mi", "mm", "ms", "n", "ns", "q", "qq", "s", "ss", "wk", "ww", "yy", "yyyy"])("preserves ClickHouse identifier-like date part %s while formatting keywords", async (identifier) => {
    const formatted = await formatSqlText(`select ${identifier}, t.${identifier} from material ${identifier} limit 100;`, sqlFormatDialectForDbType("clickhouse"));

    // Alias casing is the tokenizer's business, not the keyword casing's: the
    // date part stays an identifier, so `keywordCase: "upper"` leaves it alone.
    expect(formatted).toBe(`SELECT ${identifier},
       t.${identifier}
FROM material ${identifier}
LIMIT 100;`);
    expect(formatted).toContain("SELECT");
    expect(formatted).toContain("FROM");
    expect(formatted).toContain("LIMIT");
  });

  it("keeps ClickHouse identifier casing independent from keyword casing", async () => {
    const sql = "SELECT * FROM MATERIAL M LIMIT 100;";

    const lowerKeywords = await formatSqlText(sql, sqlFormatDialectForDbType("clickhouse"), { keywordCase: "lower", identifierCase: "preserve" });
    const lowerIdentifiers = await formatSqlText(sql, sqlFormatDialectForDbType("clickhouse"), { keywordCase: "upper", identifierCase: "lower" });

    expect(lowerKeywords).toBe("select * from MATERIAL M limit 100;");
    expect(lowerIdentifiers).toBe("SELECT * FROM material m LIMIT 100;");
  });

  it("still formats unambiguous ClickHouse interval keywords", async () => {
    const formatted = await formatSqlText("select now() + interval 1 minutes from source_table m;", sqlFormatDialectForDbType("clickhouse"));

    expect(formatted).toContain("INTERVAL 1 MINUTES");
    expect(formatted).toContain("FROM source_table m");
  });

  it("preserves DBX brace placeholders in generic and MySQL SQL", async () => {
    const sql = "SELECT ${x} AS shell_value, #{x} AS mybatis_value, '${date}' AS quoted_value";

    for (const dialect of ["generic", "mysql"] as const) {
      const formatted = await formatSqlText(sql, dialect);

      expect(formatted).toContain("${x}");
      expect(formatted).toContain("#{x}");
      expect(formatted).toContain("'${date}'");
    }
  });

  it.each(["mysql", "sqlite"] as const)("applies keyword case to LIKE operators in the %s dialect", async (dialect) => {
    const sql = "select * from users where name like '%like%' and note not like 'LIKE'";

    const upper = await formatSqlText(sql, dialect, { keywordCase: "upper", functionCase: "preserve" });
    const lower = await formatSqlText(sql.toUpperCase(), dialect, { keywordCase: "lower", functionCase: "preserve", identifierCase: "lower" });

    expect(upper).toContain("name LIKE '%like%'");
    expect(upper).toContain("note NOT LIKE 'LIKE'");
    expect(lower).toContain("name like '%LIKE%'");
    expect(lower).toContain("note not like 'LIKE'");
  });

  it("keeps SQLite LIKE functions and qualified identifiers under their own case settings", async () => {
    const formatted = await formatSqlText("select like('%a%', name), filters.like from users", "sqlite", {
      keywordCase: "upper",
      functionCase: "preserve",
      identifierCase: "preserve",
    });

    expect(formatted).toContain("like(");
    expect(formatted).toContain("filters.like");
  });

  it.each(["mysql", "sqlite"] as const)("does not rewrite LIKE inside DBX placeholders in the %s dialect", async (dialect) => {
    for (const [lowerPlaceholder, upperPlaceholder] of [
      ["${like}", "${LIKE}"],
      ["#{like}", "#{LIKE}"],
      [":like", ":LIKE"],
      ["@like", "@LIKE"],
    ]) {
      const upper = await formatSqlText(`select * from users where name like 'a';\nselect ${lowerPlaceholder} as marker`, dialect, {
        keywordCase: "upper",
        functionCase: "preserve",
      });
      const lower = await formatSqlText(`SELECT * FROM users WHERE name LIKE 'a';\nSELECT ${upperPlaceholder} AS marker`, dialect, {
        keywordCase: "lower",
        functionCase: "preserve",
        identifierCase: "lower",
      });

      expect(upper).toContain(lowerPlaceholder);
      expect(upper).toContain("name LIKE 'a'");
      expect(lower).toContain(upperPlaceholder);
      expect(lower).toContain("name like 'a'");
    }
  });

  it("falls back to the postgres formatter when the generic dialect cannot parse SQL", async () => {
    const formatted = await formatSqlText("SELECT 1::int AS id;", "generic");

    expect(formatted).toContain("1::int");
    expect(formatted).toContain("AS id");
  });

  it("formats complete backtick-quoted spans with the PostgreSQL dialect", async () => {
    const formatted = await formatSqlText("select `schema`.`odd``name` from user;", "postgres");

    expect(formatted).toBe("SELECT `schema`.`odd``name` FROM user;");
  });

  it("keeps PostgreSQL double-quoted identifiers and casts unchanged", async () => {
    const formatted = await formatSqlText('select "display""name" from records where payload::jsonb is not null;', "postgres");

    expect(formatted).toBe('SELECT "display""name" FROM records WHERE payload::jsonb IS NOT NULL;');
  });

  it("keeps MySQL backtick formatting unchanged", async () => {
    const formatted = await formatSqlText("select `schema`.`odd``name` from `user`;", "mysql");

    expect(formatted).toBe("SELECT `schema`.`odd``name` FROM `user`;");
  });

  it("keeps malformed PostgreSQL backtick input unchanged while editing", async () => {
    const sql = "select `id from user;";

    await expect(formatSqlText(sql, "postgres")).rejects.toThrow("Parse error: Unexpected");
    await expect(formatSqlForEditing(sql, "postgres")).resolves.toBe(sql);
  });

  it("formats Dameng SQL with a standalone trailing dot without changing the invalid token", async () => {
    const sql = `SELECT JS1.REC_CREATOR as "recCreator", JS1.REC_CREATOR_JOB_ID as "recCreatorJobId" FROM APSSC.TMPJS01 JS1 WHERE 1=1 AND JS1.SUBSTR(REC_CREATE_TIME,1,8) = ? ORDER BY DECODE(JS1.STATUS,'DRAFT',1,'PENDING_APPROVAL',2,'APPROVED',3,'POSTED',4,'REJECTED',5,'DELETED',6), JS1.REC_CREATE_TIME DESC .`;

    const formatted = await formatSqlForEditing(sql, sqlFormatDialectForDbType("dameng"));

    const aliasLines = formatted.split("\n").filter((line) => line.includes(' AS "recCreator'));
    expect(aliasLines).toHaveLength(2);
    expect(aliasLines[0]!.indexOf(" AS ")).toBe(aliasLines[1]!.indexOf(" AS "));
    // Known and unknown functions alike are written without a space before the
    // parenthesis.
    expect(formatted).toContain("DECODE(");
    expect(formatted.endsWith("JS1.REC_CREATE_TIME DESC .")).toBe(true);
  });

  it("only recovers a whitespace-separated final dot", async () => {
    await expect(formatSqlText("SELECT schema.", "dameng")).rejects.toThrow();
    await expect(formatSqlText("SELECT 1..", "dameng")).rejects.toThrow();
    await expect(formatSqlText("SELECT 'value .'", "dameng")).resolves.toContain("'value .'");
  });

  it("preserves whitespace after a recovered trailing dot", async () => {
    await expect(formatSqlForEditing("SELECT 1 .\n", "dameng")).resolves.toBe("SELECT 1 .\n");
  });

  it("keeps trailing SELECT comments on the field line (#12615)", async () => {
    const source = `select a, -- first field\n b, -- second field\n c -- third field\nfrom t;`;
    const formatted = await formatSqlForEditing(source, "postgres");

    expect(formatted).toContain("SELECT a, -- first field\n");
    expect(formatted).toContain("       b, -- second field\n");
    expect(formatted).toContain("       c -- third field\nFROM t;");
  });

  it("keeps source-standalone SELECT comments on their own line", async () => {
    const source = `select a,\n-- heading for b\n b\nfrom t;`;
    const formatted = await formatSqlForEditing(source, "postgres");

    expect(formatted).toContain("SELECT a,\n       -- heading for b\n       b\nFROM t;");
  });

  it("preserves the newline before a trailing dot after a line comment", async () => {
    await expect(formatSqlForEditing("DELETE FROM accounts -- comment\n .", "dameng")).resolves.toBe("DELETE FROM accounts -- comment\n .");
  });

  it("does not change trailing-dot formatting for other databases", async () => {
    await expect(formatSqlText("SELECT 1 .", "generic")).rejects.toThrow();
    await expect(formatSqlForEditing("SELECT 1 .", "generic")).resolves.toBe("SELECT 1 .");
  });

  it("keeps incomplete editor SQL unchanged when the formatter cannot parse it", async () => {
    const sql = "select *\nfrom dbname.\n;";

    await expect(formatSqlText(sql, "mysql")).rejects.toThrow("Parse error at token:");
    await expect(formatSqlForEditing(sql, "mysql")).resolves.toBe(sql);
  });

  it("keeps editor SQL unchanged when it contains full-width characters the tokenizer can't parse", async () => {
    const sql = "update t set a=concat(t.入池时间(审核通过时间),' 00:00:00') where t.入池时间(审核通过时间) ≠ '';";

    await expect(formatSqlText(sql, "mysql")).rejects.toThrow("Parse error: Unexpected");
    await expect(formatSqlForEditing(sql, "mysql")).resolves.toBe(sql);
  });

  it("keeps non-parse editor formatting failures visible", async () => {
    const oversizedSql = "x".repeat(MAX_SQL_FORMAT_CHARS + 1);

    await expect(formatSqlForEditing(oversizedSql, "mysql")).rejects.toThrow("SQL is too large to format safely.");
  });

  it("returns the original SQL for display when formatting fails", async () => {
    const oversizedSql = "x".repeat(MAX_SQL_FORMAT_CHARS + 1);

    await expect(formatSqlText(oversizedSql, "postgres")).rejects.toThrow("SQL is too large to format safely.");
    await expect(formatSqlForDisplay(oversizedSql, "postgres")).resolves.toBe(oversizedSql);
  });

  it("refuses to format XML-looking input instead of corrupting it (regression: silent rewrite)", async () => {
    const xml = `<root><item id="1">value</item></root>`;

    // The SQL formatter previously accepted this and rewrote it into corrupted
    // output (`< root > < item id = "1" > ...`). It must now be refused so no
    // caller can ever write sql-formatter output back over the user's text.
    await expect(formatSqlText(xml, "generic")).rejects.toBeInstanceOf(UnsupportedStructuredInputError);
    await expect(formatSqlText(xml, "postgres")).rejects.toBeInstanceOf(UnsupportedStructuredInputError);
  });

  it("still formats selected SQL Server bracket-quoted identifiers", async () => {
    await expect(formatSqlText(`[dbo].[orders]`, "sqlserver")).resolves.toBe(`[dbo].[orders]`);
  });

  it("still formats selected SQL comparison fragments", async () => {
    await expect(formatSqlText(`< 10`, "postgres")).resolves.toBe(`< 10`);
    // A short fragment collapses like any other short statement.
    await expect(formatSqlText(`< 10 AND score > 2`, "postgres")).resolves.toBe(`< 10 AND score > 2`);
    // A wider one has no clause to lay out, so it falls back to sql-formatter's
    // own operand-per-line rendering of a fragment rather than the clause rules.
    await expect(formatSqlText(`< 10 AND score > 2 AND status = 'active' AND deleted_at IS NULL AND created_at > now() - interval '30 days' AND owner_id = 42`, "postgres")).resolves.toBe(
      `< 10
AND score > 2
AND status = 'active'
AND deleted_at IS NULL
AND created_at > now() - interval '30 days'
AND owner_id = 42`,
    );
  });

  it("keeps logical conditions on one line when configured", async () => {
    const formatted = await formatSqlText("SELECT * FROM t WHERE a = 1 AND b = 2", "mysql", { logicalOperatorNewline: "none" });

    expect(formatted).toContain("a = 1 AND b = 2");
    expect(formatted).not.toMatch(/\n\s*AND\b/i);
  });

  it("does not collapse AND/OR line breaks inside block comments (regression: comment reformatting)", async () => {
    // sql-formatter preserves newlines inside /* ... */ verbatim, so the
    // keepLogicalOperatorsOnSameLine post-pass used to fold the comment's
    // internal `AND`/`OR` onto one line along with the real clause `AND`.
    // The comment body must stay multi-line; only the clause-level AND gets
    // pulled back onto the previous (comment-closing) line instead of sitting
    // alone on its own line.
    const sql = "SELECT * FROM t WHERE 1 = 1 /* note:\nAND is a keyword here\nOR also */ AND b = 2";

    for (const dialect of ["mysql", "postgres", "generic"] as const) {
      const formatted = await formatSqlText(sql, dialect, { logicalOperatorNewline: "none" });

      // 注释内部多行结构必须保留
      expect(formatted).toContain("/* note:");
      expect(formatted).toContain("AND is a keyword here");
      expect(formatted).toContain("OR also */");
      // 注释内部 AND/OR 仍各自独占一行(前面是换行)
      expect(formatted).toMatch(/note:\n\s*AND is a keyword here/);
      expect(formatted).toMatch(/keyword here\n\s*OR also/);
      // 真正子句间的 AND 换行应被折叠:AND b = 2 不再独占行首
      expect(formatted).toContain("*/ AND b = 2");
      expect(formatted).not.toMatch(/\n\s*AND b = 2/);
    }
  });

  it("does not collapse AND/OR inside single-quoted string literals", async () => {
    // 字符串字面量内的 AND/OR 也不应被当作逻辑算子折叠。这里用一个含换行的
    // 字符串(虽然 sql-formatter 通常会把字符串单行化,但遮罩应防御性覆盖)。
    const sql = "SELECT 'a\nAND b\nOR c' AS s WHERE x = 1 AND y = 2";

    const formatted = await formatSqlText(sql, "postgres", { logicalOperatorNewline: "none" });

    expect(formatted).toContain("'a\nAND b\nOR c'");
    expect(formatted).toContain("x = 1 AND y = 2");
  });

  it("can keep FROM and the first table on the same line", async () => {
    const formatted = await formatSqlText("SELECT * FROM tVillage AS tv INNER JOIN tLand AS tl ON tv.villageId = tl.villageId AND 1 = 1", "sqlserver", {
      fromClauseLayout: "sameLine",
      logicalOperatorNewline: "none",
      useTabs: true,
      tabWidth: 4,
    });

    // The join keeps `FROM` and its source together (the setting's whole point),
    // indents by one tab width, and the `ON` condition stays on the join's line.
    expect(formatted).toBe("SELECT *\nFROM tVillage AS tv\n\tINNER JOIN tLand AS tl ON tv.villageId = tl.villageId AND 1 = 1");
  });

  it("keeps derived tables multiline with FROM same-line layout", async () => {
    const sql = "SELECT * FROM (SELECT villageId, villageName, townshipId, createdAt, updatedAt, deletedAt FROM tVillage WHERE deletedAt IS NULL AND villageName LIKE '%x%') AS tv";

    const formatted = await formatSqlText(sql, "sqlserver", { fromClauseLayout: "sameLine" });

    // The derived table still expands — `FROM (` stays on the line it opens and
    // the subquery's clauses align under that parenthesis, so the same-line
    // setting only decides whether the group starts on the keyword's line.
    expect(formatted).toContain("FROM (SELECT villageId,");
    expect(formatted).toContain("\n      FROM tVillage");
    expect(formatted.endsWith(") AS tv")).toBe(true);
  });

  it("keeps display formatting lossless for XML/JSON-looking input", async () => {
    const xml = `<root><item id="1">value</item></root>`;
    const json = `{"a":1}`;

    await expect(formatSqlForDisplay(xml, "generic")).resolves.toBe(xml);
    await expect(formatSqlForDisplay(json, "generic")).resolves.toBe(json);
  });

  it("still formats genuine SQL that starts with a non-structured token", async () => {
    const formatted = await formatSqlText(`SELECT '{"a":1}'::jsonb AS j FROM t`, "postgres");

    expect(formatted).toContain("SELECT");
    expect(formatted).toContain("::jsonb");
  });

  it("formats multiline items with leading comma position", async () => {
    const sql = "SELECT col1, col2, col3 FROM tbl WHERE a = 1;";
    const formatted = await formatSqlText(sql, "generic", { commaPosition: "before", indentStyle: "tabularLeft" });

    expect(formatted).toContain(", col2");
    expect(formatted).toContain(", col3");
    expect(formatted).not.toMatch(/col1,/);
  });

  it("preserves line comments when formatting with leading comma position", async () => {
    const sql = "SELECT\n  col1, -- first column\n  col2 -- second column\nFROM tbl;";
    const formatted = await formatSqlText(sql, "generic", { commaPosition: "before", indentStyle: "tabularLeft" });

    expect(formatted).toContain("col1 -- first column");
    expect(formatted).toMatch(/,\s*col2\s*-- second column/);
  });

  describe("dialect content boundaries", () => {
    it.each([
      ["postgres", "-- note", "AND", "\n", "dbx", "standard"],
      ["postgres", "-- note", "OR", "\r\n", "classic", "standard"],
      ["mysql", "# note", "XOR", "\n", "dbx", "standard"],
      ["mysql", "# note", "AND", "\r\n", "classic", "standard"],
      ["sqlserver", "-- note", "OR", "\r\n", "dbx", "tabularRight"],
      ["mysql", "-- note", "AND", "\n", "dbx", "tabularLeft"],
    ] as const)("keeps %s %s before %s as a code boundary (%j, %s, %s)", async (dialect, comment, operator, lineBreak, layoutStyle, indentStyle) => {
      const source = `select a from t where x=1 ${comment}${lineBreak}${operator.toLowerCase()} y=2;`;
      const settings = { layoutStyle, indentStyle, logicalOperatorNewline: "none" } as const;
      const formatted = await formatSqlText(source, dialect, settings);
      const tokens = (sql: string) => tokenizeSqlSemantic(sql, dialect).map((token) => token.normalized);
      expect(formatted).toContain(`${comment}\n`);
      expect(formatted).toMatch(new RegExp(`\\n\\s*${operator}\\s+y = 2;`));
      expect(tokens(formatted)).toEqual(tokens(source));
      expect(await formatSqlText(formatted, dialect, settings)).toBe(formatted);
    });

    it.each(["dbx", "classic"] as const)("still joins ordinary conditions and treats comment symbols in strings as data (%s)", async (layoutStyle) => {
      const source = "select '-- not a comment' as note, '# not a comment' as other from t where x=1 and y=2 or z=3;";
      const settings = { layoutStyle, logicalOperatorNewline: "none" } as const;
      const formatted = await formatSqlText(source, "mysql", settings);
      expect(formatted).toContain("'-- not a comment'");
      expect(formatted).toContain("'# not a comment'");
      expect(formatted).toContain("x = 1 AND y = 2 OR z = 3;");
      expect(await formatSqlText(formatted, "mysql", settings)).toBe(formatted);
    });

    describe.each(["dbx", "classic"] as const)("Oracle alternative quoted literals (%s)", (layoutStyle) => {
      it.each([
        ["q", "[", "]", "\n"],
        ["Q", "{", "}", "\r\n"],
        ["nq", "(", ")", "\n"],
        ["NQ", "<", ">", "\r\n"],
        ["nQ", "!", "!", "\n"],
        ["Nq", "~", "~", "\r\n"],
      ] as const)("preserves %s with %s%s delimiters and %j newlines", async (prefix, open, close, lineBreak) => {
        const literal = `${prefix}'${open}first 'quoted'${lineBreak}${lineBreak}-- AND is data${lineBreak}/* OR is data */ last${close}'`;
        const adjacent = "q'~second 'quoted'\n\nlast~'";
        const source = `select ${literal} as note, 'tail' as label, ${adjacent} as other from dual;\n\nselect :id from dual;`;
        const settings = { layoutStyle, preserveEmptyLines: true, logicalOperatorNewline: "none" } as const;
        const formatted = await formatSqlText(source, "oracle", settings);
        const tokens = (sql: string) => tokenizeSqlSemantic(sql, "oracle").map((token) => token.normalized);
        expect(formatted).toContain(literal);
        expect(formatted).toContain(adjacent);
        expect(formatted).toContain("'tail'");
        expect(tokens(formatted)).toEqual(tokens(source));
        expect(await formatSqlText(formatted, "oracle", settings)).toBe(formatted);
      });
    });

    it("preserves a multiline q literal without empty-line preservation and leaves ordinary strings separate", async () => {
      const literal = "q'[first 'quoted'\n\nlast]'";
      const source = `select ${literal}, 'ordinary', q'!single 'quoted'!' from dual;`;
      const formatted = await formatSqlText(source, "oracle", { preserveEmptyLines: false });
      expect(formatted).toContain(literal);
      expect(
        tokenizeSqlSemantic(formatted, "oracle")
          .filter((token) => token.kind === "string")
          .map((token) => token.text),
      ).toEqual([literal, "'ordinary'", "q'!single 'quoted'!'"]);
    });

    it.each(["dbx", "classic"] as const)("preserves spaces and tabs on a q literal's blank line in the base layout (%s)", async (layoutStyle) => {
      const literal = "q'[first 'quoted'\r\n  \t\r\nlast]'";
      const source = `select ${literal} as note from dual;\n\nselect 2;`;
      const settings = { layoutStyle, preserveEmptyLines: true } as const;
      const formatted = await formatSqlText(source, "oracle", settings);
      expect(formatted).toContain(literal);
      expect(tokenizeSqlSemantic(formatted, "oracle").map((token) => token.normalized)).toEqual(tokenizeSqlSemantic(source, "oracle").map((token) => token.normalized));
      expect(await formatSqlText(formatted, "oracle", settings)).toBe(formatted);
    });

    it.each(["dbx", "classic"] as const)("does not apply FROM same-line layout inside protected contents (%s)", async (layoutStyle) => {
      const literal = "q'[first 'quoted'\nFROM\n  literal_source\nlast]'";
      const source = `select ${literal} as note from actual_source;`;
      const settings = { layoutStyle, fromClauseLayout: "sameLine" } as const;
      const formatted = await formatSqlText(source, "oracle", settings);
      expect(formatted).toContain(literal);
      expect(formatted).toContain("FROM actual_source");
      expect(await formatSqlText(formatted, "oracle", settings)).toBe(formatted);
    });

    it.each([
      ["postgres", "/* leading */"],
      ["postgres", "-- leading"],
      ["mysql", "# leading"],
    ] as const)("keeps a leading %s comment below FROM (%s)", async (dialect, comment) => {
      const source = `select a from\n ${comment}\n source_table;`;
      const settings = { layoutStyle: "classic", fromClauseLayout: "sameLine" } as const;
      const formatted = await formatSqlText(source, dialect, settings);
      expect(formatted).toMatch(/FROM\n/);
      expect(formatted).toContain(comment);
      expect(tokenizeSqlSemantic(formatted, dialect).map((token) => token.normalized)).toEqual(tokenizeSqlSemantic(source, dialect).map((token) => token.normalized));
      expect(await formatSqlText(formatted, dialect, settings)).toBe(formatted);
    });

    it("keeps quoted table names eligible for FROM same-line layout", async () => {
      expect(await formatSqlText('select a from "Case Table";', "postgres", { layoutStyle: "classic", fromClauseLayout: "sameLine" })).toContain('FROM "Case Table"');
    });

    it.each([false, true])("does not normalize literal or block-comment line endings while restoring code paragraphs (rules=%s)", async (customRulesEnabled) => {
      const literal = "$tag$first\r\n\r\nlast$tag$";
      const comment = "/* first\r\n\r\nlast */";
      const source = `select ${literal} as note from t;\n\n${comment}\n\nselect 2;`;
      const settings = { customRulesEnabled, emptyLineMode: "preserve", preserveEmptyLines: true } as const;
      const formatted = await formatSqlText(source, "postgres", settings);
      expect(formatted).toContain(literal);
      expect(formatted).toContain(comment);
      expect(await formatSqlText(formatted, "postgres", settings)).toBe(formatted);
    });

    it.each(["", "\n"])("does not add a trailing empty line while restoring paragraphs at EOF %j", async (ending) => {
      expect(await formatSqlText(`-- heading\n\nselect 1;${ending}`, "postgres", { preserveEmptyLines: true })).toBe("-- heading\n\nSELECT 1;");
    });

    it.each([
      ["postgres", "/* first\r\n\r\nlast */", false],
      ["postgres", "/* outer\r\n /* inner */\r\nlast */", true],
      ["mysql", "/*+ MAX_EXECUTION_TIME(1000)\r\n NO_RANGE_OPTIMIZATION(t) */", false],
      ["mysql", "/*!40101 SET @note = 'first\r\nlast' */", true],
    ] as const)("preserves %s CR block comments and marker-like data (rules=%s)", async (dialect, comment, customRulesEnabled) => {
      const markerLikeLiteral = "'/*__DBX_PRESERVE_BLOCK_COMMENT_0_0__*/'";
      const otherComment = "/* another\r\ncomment */";
      const source = `select a ${comment},b from t where x=1 and y=2;\n\n${otherComment}\nselect ${markerLikeLiteral} as note;`;
      const settings = { customRulesEnabled, layoutStyle: "classic", logicalOperatorNewline: "none", commaPosition: "before", preserveEmptyLines: true } as const;
      const formatted = await formatSqlText(source, dialect, settings);
      expect(formatted).toContain(comment);
      expect(formatted).toContain(otherComment);
      expect(formatted).toContain(markerLikeLiteral);
      expect(formatted.replace(markerLikeLiteral, "")).not.toContain("__DBX_PRESERVE_BLOCK_COMMENT_");
      expect(tokenizeSqlSemantic(formatted, dialect).map((token) => token.normalized)).toEqual(tokenizeSqlSemantic(source, dialect).map((token) => token.normalized));
      expect(await formatSqlText(formatted, dialect, settings)).toBe(formatted);
    });

    it("does not complete an unterminated CR block comment", async () => {
      const source = "select a /* unfinished\r\n still unfinished";
      const formatted = await formatSqlForEditing(source, "postgres", { preserveEmptyLines: true });
      expect(formatted).not.toContain("*/");
      expect(formatted).not.toContain("__DBX_PRESERVE_BLOCK_COMMENT_");
    });

    it.each([
      ["postgres", "select * from t where c@?'$.x';", "c @? '$.x'"],
      ["sqlserver", "select @myvar from t;", "@myvar"],
      ["mysql", "select 'D d\\'D D' as note from t;", "'D d\\'D D'"],
    ] as const)("preserves the %s operator, variable or escaped string boundary", async (dialect, source, expected) => {
      const settings = { customRulesEnabled: true };
      const formatted = await formatSqlText(source, dialect, settings);
      expect(formatted).toContain(expected);
      expect(await formatSqlText(formatted, dialect, settings)).toBe(formatted);
    });

    it.each(["\n", "\r\n"])("does not extend an empty line comment across %j", async (lineBreak) => {
      const source = `--${lineBreak}select a,b from t;`;
      const formatted = await formatSqlText(source, "postgres", { customRulesEnabled: true });
      expect(formatted).toMatch(/^--\nSELECT/);
      expect(tokenizeSqlSemantic(formatted, "postgres").map((token) => token.normalized)).toEqual(tokenizeSqlSemantic(source, "postgres").map((token) => token.normalized));
    });
  });
});

/**
 * `layoutStyle: "classic"` selects sql-formatter's own layout — the default
 * before v0.6.16 replaced it with DBX's printer — so these expectations are the
 * byte-for-byte output of the verbatim v0.6.15 `formatSqlText` (issue #11228).
 * The layout engine alone is not a v0.6.15 reproduction: v0.6.16 also changed
 * the `expressionWidth` and `fromClauseLayout` defaults, so both are set back
 * explicitly. The remaining options already match the v0.6.15 defaults.
 *
 * Regenerate with `git show 455117b8c5^:apps/desktop/src/lib/sql/sqlFormatter.ts`
 * imported into a throwaway spec and called with these settings; the goldens
 * below are literals on purpose so the spec never depends on the old source.
 */
describe("classic layout style (v0.6.15 reproduction)", () => {
  const classic: Partial<SqlFormatterSettings> = { layoutStyle: "classic", expressionWidth: 50, fromClauseLayout: "newLine" };
  const lines = (...rows: string[]) => rows.join("\n");

  it("reproduces a MySQL SELECT with a JOIN", async () => {
    const sql = "select a.id, a.name, b.total, case when a.status = 1 then 'active' else 'inactive' end as status_label from users a left join orders b on a.id = b.user_id where a.created_at >= '2024-01-01' and b.total > 100 order by a.created_at desc limit 50;";

    expect(await formatSqlText(sql, "mysql", classic)).toBe(
      lines(
        "SELECT",
        "  a.id,",
        "  a.name,",
        "  b.total,",
        "  CASE",
        "    WHEN a.status = 1 THEN 'active'",
        "    ELSE 'inactive'",
        "  END AS status_label",
        "FROM",
        "  users a",
        "  LEFT JOIN orders b ON a.id = b.user_id",
        "WHERE",
        "  a.created_at >= '2024-01-01'",
        "  AND b.total > 100",
        "ORDER BY",
        "  a.created_at DESC",
        "LIMIT",
        "  50;",
      ),
    );
  });

  it("reproduces a MySQL CTE", async () => {
    const sql = "with t as (select id, count(*) c from logs group by id) select id, c from t where c > 10";

    expect(await formatSqlText(sql, "mysql", classic)).toBe(lines("WITH", "  t AS (", "    SELECT", "      id,", "      count(*) c", "    FROM", "      logs", "    GROUP BY", "      id", "  )", "SELECT", "  id,", "  c", "FROM", "  t", "WHERE", "  c > 10"));
  });

  it("reproduces a MySQL CREATE TABLE", async () => {
    const sql =
      "CREATE TABLE IF NOT EXISTS `delivery_emp_info` (`id` varchar(20) NOT NULL comment 'pk', `emp_id` varchar(20) NOT NULL comment 'emp id', `delivery_type_ids` varchar(512) default NULL comment 'types', `update_time` datetime NOT NULL comment 'updated', CONSTRAINT idx_emp_phone UNIQUE (emp_id)) engine = innodb default charset = utf8mb4 comment 'emp info';";

    // `default` / `comment` staying lowercase is genuine upstream behaviour
    // (they are not keyword-cased in these positions), not a DBX bug to fix.
    expect(await formatSqlText(sql, "mysql", classic)).toBe(
      lines(
        "CREATE TABLE IF NOT EXISTS `delivery_emp_info` (",
        "  `id` varchar(20) NOT NULL comment 'pk',",
        "  `emp_id` varchar(20) NOT NULL comment 'emp id',",
        "  `delivery_type_ids` varchar(512) default NULL comment 'types',",
        "  `update_time` datetime NOT NULL comment 'updated',",
        "  CONSTRAINT idx_emp_phone UNIQUE (emp_id)",
        ") engine = innodb default charset = utf8mb4 comment 'emp info';",
      ),
    );
  });

  it("reproduces comments and blank lines", async () => {
    const sql = "-- 头部说明\nselect id, name from users where status = 1;\n\n/* 第二段 */\nselect count(*) from orders where total > 100 and status = 'paid';";

    expect(await formatSqlText(sql, "mysql", classic)).toBe(lines("-- 头部说明", "SELECT", "  id,", "  name", "FROM", "  users", "WHERE", "  status = 1;", "", "/* 第二段 */", "SELECT", "  count(*)", "FROM", "  orders", "WHERE", "  total > 100", "  AND status = 'paid';"));
  });

  it("reproduces a Doris/generic query", async () => {
    // Doris and StarRocks have no dedicated formatter dialect, so they exercise
    // the generic grammar plus its PostgreSQL retry.
    const sql = "select `db`.`t`.`k`, sum(`db`.`t`.`v`) as total from `db`.`t` where `db`.`t`.`dt` = '2024-01-01' group by `db`.`t`.`k` having total > 10 order by total desc limit 20;";

    expect(await formatSqlText(sql, "generic", classic)).toBe(lines("SELECT", "  `db`.`t`.`k`,", "  sum(`db`.`t`.`v`) AS total", "FROM", "  `db`.`t`", "WHERE", "  `db`.`t`.`dt` = '2024-01-01'", "GROUP BY", "  `db`.`t`.`k`", "HAVING", "  total > 10", "ORDER BY", "  total DESC", "LIMIT", "  20;"));
  });

  it("reproduces a short statement without collapsing it", async () => {
    const sql = "select * from task order by created_at desc;";

    expect(await formatSqlText(sql, "mysql", classic)).toBe(lines("SELECT", "  *", "FROM", "  task", "ORDER BY", "  created_at DESC;"));
  });

  it("reproduces a nested subquery", async () => {
    const sql = "select loc_id, loc_name from (select *, row_number() over (partition by loc_id order by update_time desc) as rn from responsible_area_info where date_format(update_time, '%Y-%m') = date_format(now(), '%Y-%m')) t where rn = 1;";

    expect(await formatSqlText(sql, "mysql", classic)).toBe(
      lines(
        "SELECT",
        "  loc_id,",
        "  loc_name",
        "FROM",
        "  (",
        "    SELECT",
        "      *,",
        "      row_number() OVER (",
        "        PARTITION BY",
        "          loc_id",
        "        ORDER BY",
        "          update_time DESC",
        "      ) AS rn",
        "    FROM",
        "      responsible_area_info",
        "    WHERE",
        "      date_format(update_time, '%Y-%m') = date_format(now(), '%Y-%m')",
        "  ) t",
        "WHERE",
        "  rn = 1;",
      ),
    );
  });

  it("normalizes the Oracle SUBSTRB family under classic (intentional divergence from v0.6.15)", async () => {
    // v0.6.15 emitted `substrb (NAME, 1, 3)` — the byte/multibyte variants were
    // unknown to sql-formatter's plsql grammar, so they read as identifiers.
    // #11251 registers them as function names, and the classic path keeps that
    // fix: these assertions pin the new behaviour, they must not be reverted.
    const sql = "select substrb(name, 1, 3), instr2(name, 'x'), lengthb(name) from t;";

    await expect(formatSqlText(sql, "oracle", { ...classic, functionCase: "upper" })).resolves.toBe(lines("SELECT", "  SUBSTRB(NAME, 1, 3),", "  INSTR2(NAME, 'x'),", "  LENGTHB(NAME)", "FROM", "  t;"));
  });

  it("uses the DBX layout by default and keeps classic selectable", async () => {
    const sql = "select * from task order by created_at desc;";
    const collapsed = "SELECT * FROM task ORDER BY created_at DESC;";

    expect(await formatSqlTextWithBuildDefaults(sql, "mysql")).toBe(collapsed);
    expect(await formatSqlTextWithBuildDefaults(sql, "mysql", {} as Partial<SqlFormatterSettings>)).toBe(collapsed);
    expect(await formatSqlTextWithBuildDefaults(sql, "mysql", { layoutStyle: "classic" })).toBe("SELECT\n  *\nFROM task\nORDER BY\n  created_at DESC;");
  });

  it("formats multiple statements on separate blocks by default", async () => {
    const sql = "select id, name from users; select count(*) from orders;";

    await expect(formatSqlTextWithBuildDefaults(sql, "mysql")).resolves.toBe("SELECT id,\n       name\nFROM users;\n\nSELECT count(*) FROM orders;");
  });

  it("merges a classic FROM clause onto its source with a single space under the standard indent", async () => {
    // `sameLine` (the default) merges the clause line with the source line, and
    // the merge must not reuse the source's indent as the gap: that is the
    // tabular styles' alignment column, but under the standard indent it would
    // read `FROM  t`. This explicitly exercises sql-formatter's upstream layout.
    expect(await formatSqlText("select * from t", "mysql", classic)).toBe(lines("SELECT", "  *", "FROM", "  t"));
    expect(await formatSqlText("select * from t", "mysql", { ...classic, fromClauseLayout: "sameLine" })).toBe(lines("SELECT", "  *", "FROM t"));
  });
});

describe("PostgreSQL MERGE formatting (#12592)", () => {
  // sql-formatter 15's postgresql grammar predates PG15 MERGE, so the DBX
  // layout printer used to emit one token per line for these statements.
  const merge = "MERGE INTO target t USING source s ON t.id = s.id WHEN MATCHED THEN UPDATE SET name = s.name WHEN NOT MATCHED THEN INSERT (id, name) VALUES (s.id, s.name);";

  it("lays out the UPDATE and INSERT actions as clauses", async () => {
    const formatted = await formatSqlText(merge, "postgres");

    expect(formatted).toBe(["MERGE INTO target t USING source s ON t.id = s.id", "WHEN MATCHED THEN", "UPDATE SET name = s.name", "WHEN NOT MATCHED THEN", "INSERT (id, name)", "VALUES (s.id, s.name);"].join("\n"));
    // Formatting is idempotent: the clause keywords stay clause boundaries.
    await expect(formatSqlText(formatted, "postgres")).resolves.toBe(formatted);
  });

  it("keeps conditions, DELETE actions and RETURNING intact", async () => {
    const sql = "MERGE INTO products p USING new_products np ON p.product_id = np.product_id WHEN MATCHED AND np.price > p.price THEN UPDATE SET price = np.price WHEN NOT MATCHED THEN INSERT (product_id, product_name, price) VALUES (np.product_id, np.product_name, np.price) RETURNING p.*;";

    await expect(formatSqlText(sql, "postgres")).resolves.toBe(
      [
        "MERGE INTO products p USING new_products np ON p.product_id = np.product_id",
        "WHEN MATCHED AND np.price > p.price THEN",
        "UPDATE SET price = np.price",
        "WHEN NOT MATCHED THEN",
        "INSERT (product_id, product_name, price)",
        "VALUES (np.product_id, np.product_name, np.price)",
        "RETURNING p.*;",
      ].join("\n"),
    );

    const deleteAction = "MERGE INTO t USING s ON t.id = s.id WHEN MATCHED THEN DELETE WHEN NOT MATCHED THEN INSERT (id) VALUES (s.id);";
    await expect(formatSqlText(deleteAction, "postgres")).resolves.toBe(["MERGE INTO t USING s ON t.id = s.id", "WHEN MATCHED THEN DELETE", "WHEN NOT MATCHED THEN", "INSERT (id)", "VALUES (s.id);"].join("\n"));
  });

  it("does not change regular INSERT, UPDATE or JOIN formatting", async () => {
    await expect(formatSqlText("insert into t (a, b) values (1, 2) on conflict (a) do update set b = excluded.b;", "postgres")).resolves.toBe("INSERT INTO t (a, b)\nVALUES (1, 2)\nON CONFLICT (a) DO UPDATE\nSET b = excluded.b;");
    await expect(formatSqlText("update t set a = 1, b = 2 where id = 3;", "postgres")).resolves.toBe("UPDATE t SET a = 1, b = 2 WHERE id = 3;");
    await expect(formatSqlText("select a.id, b.name from a join b on a.id = b.id where a.x = 1;", "postgres")).resolves.toBe("SELECT a.id,\n       b.name\nFROM a\n    JOIN b ON a.id = b.id\nWHERE a.x = 1;");
  });

  it("applies to PostgreSQL-compatible connections too", async () => {
    // GaussDB / Kingbase / HighGo / openGauss reuse the postgres grammar.
    expect(sqlFormatDialectForDbType("gaussdb")).toBe("postgres");
    await expect(formatSqlText(merge, sqlFormatDialectForDbType("kingbase"))).resolves.toContain("WHEN MATCHED THEN\nUPDATE SET name = s.name");
  });
});