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 { formatSqlText } from "@/lib/sql/sqlFormatter";
import { DEFAULT_SQL_FORMATTER_SETTINGS, type SqlFormatterSettings } from "@/lib/sql/sqlFormatterConfig";
/**
* Golden tests for DBX's default layout.
* The expectations for the queries in issues #827, #4850 and #5170 pin the
* real-world shapes those reports asked for; the rest pin the rules those
* examples exercise — the collapse of short statements, the wrap of long ones,
* element alignment, and column alignment in `CREATE TABLE`.
*/
type Dialect = Parameters<typeof formatSqlText>[1];
async function format(sql: string, overrides: Partial<SqlFormatterSettings> = {}, dialect: Dialect = "mysql") {
return formatSqlText(sql, dialect, { ...DEFAULT_SQL_FORMATTER_SETTINGS, ...overrides });
}
const lines = (...rows: string[]) => rows.join("\n");
describe("sql layout", () => {
it.each([
["SELECT 1\nSELECT 2", "SELECT 1\nSELECT 2"],
["SELECT a FROM t\nSELECT b FROM u", "SELECT a\nFROM t\nSELECT b\nFROM u"],
["SELECT 1\nUPDATE t SET a = 2", "SELECT 1\nUPDATE t\nSET a = 2"],
["UPDATE t SET a = 1\nUPDATE t SET a = 2", "UPDATE t\nSET a = 1\nUPDATE t\nSET a = 2"],
["SET @a = 1\nSELECT @a", "SET @a = 1\nSELECT @a"],
["DELETE FROM t\nINSERT INTO t VALUES (1)", "DELETE FROM t\nINSERT INTO t\nVALUES (1)"],
["SELECT 1\nSHOW TABLES", "SELECT 1\nSHOW TABLES"],
])("keeps unseparated statements on different lines (#12523): %s", async (sql, expected) => {
const formatted = await format(sql);
expect(formatted).toBe(expected);
expect(await format(formatted)).toBe(formatted);
expect(formatted).not.toContain(";");
});
it.each([
["SELECT (SELECT 1) AS value", "SELECT (SELECT 1) AS value"],
["WITH t AS (SELECT 1 AS a) SELECT a FROM t", "WITH t AS (SELECT 1 AS a) SELECT a FROM t"],
["SELECT 'SELECT UPDATE' AS value", "SELECT 'SELECT UPDATE' AS value"],
["UPDATE t SET a = 1 WHERE id = 2", "UPDATE t SET a = 1 WHERE id = 2"],
])("keeps a single short statement compact: %s", async (sql, expected) => {
expect(await format(sql)).toBe(expected);
});
it("formats a query with a derived table (#827)", async () => {
const sql = lines(
"SELECT loc_id, loc_name, loc_type, delivery_emp_num, update_time",
"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;",
);
// The subquery's clauses align under the `(` that opens it, and the closing
// `)` stays on the last clause's line.
//
// Expanded subqueries use the same one-field-per-line rule as the outer
// SELECT, while their clauses remain aligned under the opening parenthesis.
expect(await format(sql)).toBe(
lines(
"SELECT loc_id,",
" loc_name,",
" loc_type,",
" delivery_emp_num,",
" update_time",
"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("keeps a long statement's clauses apart (#4850)", async () => {
const sql = lines(
"SELECT HIMMS_XDSBDJ.HIMMS_XDSBDJ_ID, HIMMS_XDSBDJ.YNKE, himms_zwxsysj_sysj(V_HIMMS_XDSBDJ.HIMMS_XDSBDJ_ID) AS SJSYSJ",
"FROM HIMMS_XDSBDJ",
"LEFT JOIN (SELECT himms_xdsbdj_id, MAX(SJ) SJ FROM himms_zwxqdjc GROUP BY himms_xdsbdj_id) SUB_QDJC ON HIMMS_XDSBDJ.HIMMS_XDSBDJ_ID = SUB_QDJC.himms_xdsbdj_id",
"WHERE HIMMS_XDSBDJ.tybz IS NULL;",
);
// The user-defined function is written without a space, and the join's
// subquery expands rather than pushing `ON` onto a line of its own.
expect(await format(sql)).toBe(
lines(
"SELECT HIMMS_XDSBDJ.HIMMS_XDSBDJ_ID,",
" HIMMS_XDSBDJ.YNKE,",
" himms_zwxsysj_sysj(V_HIMMS_XDSBDJ.HIMMS_XDSBDJ_ID) AS SJSYSJ",
"FROM HIMMS_XDSBDJ",
" LEFT JOIN (SELECT himms_xdsbdj_id,",
" MAX(SJ) SJ",
" FROM himms_zwxqdjc",
" GROUP BY himms_xdsbdj_id) SUB_QDJC ON HIMMS_XDSBDJ.HIMMS_XDSBDJ_ID = SUB_QDJC.himms_xdsbdj_id",
"WHERE HIMMS_XDSBDJ.tybz IS NULL;",
),
);
});
it("collapses a statement that fits on one line (#5170)", async () => {
const sql = lines("SELECT", " *", "FROM", " task", "ORDER BY", " created_at DESC;");
expect(await format(sql)).toBe("SELECT * FROM task ORDER BY created_at DESC;");
});
it("aligns CREATE TABLE columns", async () => {
const sql = lines(
"CREATE TABLE IF NOT EXISTS `delivery_emp_info`",
"(",
" `id` varchar(20) PRIMARY KEY NOT NULL COMMENT '主键',",
" `emp_id` varchar(20) NOT NULL COMMENT '员工id',",
" `delivery_type_ids` varchar(512) DEFAULT NULL COMMENT '负责的运送类型id集合',",
" `update_time` datetime NOT NULL COMMENT '更新时间',",
" CONSTRAINT idx_emp_phone UNIQUE (emp_id, emp_phone_num) COMMENT '运送员id电话唯一'",
") ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin COMMENT '运达员信息表';",
);
// Name, type and modifiers each get their own column, so the definitions'
// `COMMENT`s line up; the constraint's `(` keeps its space.
expect(await format(sql)).toBe(
lines(
"CREATE TABLE IF NOT EXISTS `delivery_emp_info`",
"(",
" `id` varchar(20) PRIMARY KEY NOT NULL COMMENT '主键',",
" `emp_id` varchar(20) NOT NULL COMMENT '员工id',",
" `delivery_type_ids` varchar(512) DEFAULT NULL COMMENT '负责的运送类型id集合',",
" `update_time` datetime NOT NULL COMMENT '更新时间',",
" CONSTRAINT idx_emp_phone UNIQUE (emp_id, emp_phone_num) COMMENT '运送员id电话唯一'",
") ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin COMMENT '运达员信息表';",
),
);
});
it("keeps a call shaped like a call at any nesting depth", async () => {
const sql = "INSERT INTO t (a, b) VALUES (f(x), g(y)) ON DUPLICATE KEY UPDATE b = f(g(b));";
// The insert target's column list is spaced; the calls inside the values,
// and inside another call's arguments, are not.
expect(await format(sql)).toBe("INSERT INTO t (a, b) VALUES (f(x), g(y)) ON DUPLICATE KEY UPDATE b = f(g(b));");
});
it("breaks a long condition before each logical operator", async () => {
const sql = "SELECT a, b FROM t WHERE alpha = 1 AND beta = 2 AND gamma = 3 AND delta = 4 AND epsilon = 5 AND zeta = 6 AND eta = 7 AND theta = 8;";
// The statement is too long to collapse, and the default
// `logicalOperatorNewline: "before"` starts each continuation line with the
// operator, aligned under the condition's first operand like any other
// clause item.
expect(await format(sql)).toBe(lines("SELECT a,", " b", "FROM t", "WHERE alpha = 1", " AND beta = 2", " AND gamma = 3", " AND delta = 4", " AND epsilon = 5", " AND zeta = 6", " AND eta = 7", " AND theta = 8;"));
});
it("puts a wrapped condition's operators where the setting asks", async () => {
const sql = "SELECT a, b FROM t WHERE alpha = 1 AND beta = 2 AND gamma = 3 AND delta = 4 AND epsilon = 5 AND zeta = 6 AND eta = 7 AND theta = 8;";
// `after` ends the previous line with the operator instead.
expect(await format(sql, { logicalOperatorNewline: "after" })).toContain("WHERE alpha = 1 AND\n beta = 2 AND");
// `none` keeps the condition on the clause's line, however long it gets.
expect(await format(sql, { logicalOperatorNewline: "none" })).toBe(lines("SELECT a,", " b", "FROM t", "WHERE alpha = 1 AND beta = 2 AND gamma = 3 AND delta = 4 AND epsilon = 5 AND zeta = 6 AND eta = 7 AND theta = 8;"));
});
it("expands a parenthesized list that does not fit, aligned under its first item", async () => {
const sql = `SELECT * FROM t WHERE id IN (${Array.from({ length: 16 }, (_, index) => 100000 + index).join(", ")});`;
expect(await format(sql)).toBe(
lines(
"SELECT *",
"FROM t",
"WHERE id IN (100000,",
" 100001,",
" 100002,",
" 100003,",
" 100004,",
" 100005,",
" 100006,",
" 100007,",
" 100008,",
" 100009,",
" 100010,",
" 100011,",
" 100012,",
" 100013,",
" 100014,",
" 100015);",
),
);
});
it("keeps a small subquery inline", async () => {
expect(await format("SELECT * FROM t WHERE id IN (SELECT id FROM u WHERE a = 1 AND b = 2);")).toBe("SELECT * FROM t WHERE id IN (SELECT id FROM u WHERE a = 1 AND b = 2);");
});
it("collapses each clause independently", async () => {
const sql = "SELECT (SELECT count(*) FROM orders o WHERE o.customer_id = c.id AND o.status = 'paid') AS order_count, c.name FROM customers c;";
// The scalar subquery stays compact within its own SELECT field.
expect(await format(sql)).toBe(lines("SELECT (SELECT count(*) FROM orders o WHERE o.customer_id = c.id AND o.status = 'paid') AS order_count,", " c.name", "FROM customers c;"));
});
it("keeps a CASE expression compact (#10783)", async () => {
expect(await format("SELECT CASE WHEN a = 1 THEN 'one' WHEN a = 2 THEN 'two' ELSE 'other' END AS label FROM t;")).toBe("SELECT CASE WHEN a = 1 THEN 'one' WHEN a = 2 THEN 'two' ELSE 'other' END AS label FROM t;");
});
it("puts each SELECT field on its own line even in a short query (#10783)", async () => {
expect(await format("SELECT id, CASE WHEN a = 1 THEN 'one' ELSE 'other' END AS label, coalesce(a, b) FROM t;")).toBe(lines("SELECT id,", " CASE WHEN a = 1 THEN 'one' ELSE 'other' END AS label,", " coalesce(a, b)", "FROM t;"));
});
it("puts fields on separate lines when SELECT has a modifier (#10783)", async () => {
expect(await format("SELECT DISTINCT a, b FROM t;")).toBe(lines("SELECT DISTINCT a,", " b", "FROM t;"));
expect(await format("SELECT DISTINCT ON (key) a, b FROM t;")).toBe(lines("SELECT DISTINCT ON (KEY) a,", " b", "FROM t;"));
});
it("keeps nested and simple CASE expressions compact", async () => {
const sql = "SELECT SUM(CASE WHEN a = 1 AND b = 2 THEN 1 ELSE 0 END) AS total, CASE a WHEN 1 THEN CASE b WHEN 2 THEN 'x y' END ELSE 'other' END AS label FROM t;";
const expected = lines("SELECT SUM(CASE WHEN a = 1 AND b = 2 THEN 1 ELSE 0 END) AS total,", " CASE a WHEN 1 THEN CASE b WHEN 2 THEN 'x y' END ELSE 'other' END AS label", "FROM t;");
expect(await format(sql)).toBe(expected);
expect(await format(expected)).toBe(expected);
});
it("falls back to unaligned fields when an aligned alias would overflow the line width", async () => {
// Alignment pads every alias to one column past the widest expression, so
// the short expression's long alias lands exactly at the 120-column width;
// one more character and the whole list keeps the plain one-space layout.
const fitting = `SELECT ${"a".repeat(60)} AS total, ${"b".repeat(30)} AS ${"y".repeat(49)} FROM t;`;
expect(await format(fitting)).toBe(lines(`SELECT ${"a".repeat(60)} AS total,`, ` ${"b".repeat(30)}${" ".repeat(31)}AS ${"y".repeat(49)}`, "FROM t;"));
const overflowing = `SELECT ${"a".repeat(60)} AS total, ${"b".repeat(30)} AS ${"y".repeat(50)} FROM t;`;
expect(await format(overflowing)).toBe(lines(`SELECT ${"a".repeat(60)} AS total,`, ` ${"b".repeat(30)} AS ${"y".repeat(50)}`, "FROM t;"));
});
it("keeps an unaliased field plain while the aliased ones align", async () => {
expect(await format("SELECT a, b AS x, c AS yyy FROM t;")).toBe(lines("SELECT a,", " b AS x,", " c AS yyy", "FROM t;"));
// The alignment column comes from the aliased expressions only, so the
// bare field stays untouched beside the padded aliases.
expect(await format("SELECT a, bb AS x, c AS yyy FROM t;")).toBe(lines("SELECT a,", " bb AS x,", " c AS yyy", "FROM t;"));
});
it("keeps DBX JOIN and condition layout while aligning aliases and FROM", async () => {
const sql = lines(
"SELECT js.start_time AS jobslipstarttime, js.end_time AS jobslipendtime, js.job_area_ids AS jobareaids, jsm.job_slip_id, jsm.flag, upi.worker_basic_info_id AS workerid",
"FROM loc_job_slip js LEFT JOIN loc_job_slip_audit jsa ON js.id = jsa.job_slip_id LEFT JOIN loc_job_slip_manager jsm ON js.id = jsm.job_slip_id LEFT JOIN loc_user_proj_info upi ON upi.bp_user_id = jsm.user_id",
"WHERE js.deleted = 0 AND jsa.deleted = 0 AND jsm.deleted = 0;",
);
const expected = lines(
"SELECT js.start_time AS jobslipstarttime,",
" js.end_time AS jobslipendtime,",
" js.job_area_ids AS jobareaids,",
" jsm.job_slip_id,",
" jsm.flag,",
" upi.worker_basic_info_id AS workerid",
"FROM loc_job_slip js",
" LEFT JOIN loc_job_slip_audit jsa ON js.id = jsa.job_slip_id",
" LEFT JOIN loc_job_slip_manager jsm ON js.id = jsm.job_slip_id",
" LEFT JOIN loc_user_proj_info upi ON upi.bp_user_id = jsm.user_id",
"WHERE js.deleted = 0",
" AND jsa.deleted = 0",
" AND jsm.deleted = 0;",
);
const formatted = await format(sql);
expect(formatted).toBe(expected);
expect(await formatSqlText(sql, "mysql")).toBe(expected);
const projection = formatted.split("\n").slice(0, 6);
const fieldStartColumns = projection.map((line, index) => (index === 0 ? line.indexOf("js.start_time") : line.search(/\S/)));
expect(new Set(fieldStartColumns)).toEqual(new Set([7]));
expect(new Set(projection.filter((line) => line.includes(" AS ")).map((line) => line.indexOf("AS"))).size).toBe(1);
});
it("preserves comments and width wrapping inside CASE expressions", async () => {
const commented = await format(lines("SELECT id, CASE WHEN a = 1 THEN 'one' -- retain this", "ELSE 'other' END AS label FROM t;"));
expect(commented).toContain("-- retain this\n");
expect(commented).toContain("ELSE 'other'");
expect(await format(commented)).toBe(commented);
const longCase = await format(`SELECT id, CASE WHEN a = 1 THEN '${"x".repeat(100)}' ELSE 'other' END AS label FROM t;`);
expect(longCase).toContain("CASE\n");
expect(longCase).toContain("END AS label");
expect(await format(longCase)).toBe(longCase);
});
it("gives a set operation its own line", async () => {
expect(await format("SELECT a FROM t UNION ALL SELECT b FROM u;")).toBe(lines("SELECT a", "FROM t", "UNION ALL", "SELECT b", "FROM u;"));
});
it("keeps a trailing comment on its line without swallowing the next item", async () => {
const sql = lines("-- heading", "SELECT a, -- why", " b", "FROM t; /* trailing */");
expect(await format(sql)).toBe(lines("-- heading", "SELECT a, -- why", " b", "FROM t;", "", "/* trailing */"));
});
it("keeps PostgreSQL casts tight", async () => {
expect(await format("select coalesce(a, b)::text, (x + y)::int from t;", {}, "postgres")).toBe(lines("SELECT coalesce(a, b)::text,", " (x + y)::int", "FROM t;"));
});
it("keeps the comma of a MySQL LIMIT offset", async () => {
expect(await format("SELECT a FROM t LIMIT 5, 10;", {}, "mysql")).toBe("SELECT a FROM t LIMIT 5, 10;");
});
it("indents with tabs when asked", async () => {
const sql = "SELECT alpha_column, beta_column, gamma_column, delta_column FROM some_really_long_table_name WHERE first_condition = 1 AND second_condition = 2 AND third_condition = 3;";
// Only the leading indentation becomes a tab; the alignment padding stays
// spaces so a continuation column lands where it was measured.
expect(await format(sql, { useTabs: true, tabWidth: 4 })).toBe(["SELECT alpha_column,", "\t beta_column,", "\t gamma_column,", "\t delta_column", "FROM some_really_long_table_name", "WHERE first_condition = 1", "\t AND second_condition = 2", "\t AND third_condition = 3;"].join("\n"));
});
it("moves a FROM source to its own line when configured", async () => {
const sql = "SELECT alpha_column, beta_column, gamma_column, delta_column FROM some_really_long_table_name WHERE first_condition = 1 AND second_condition = 2 AND third_condition = 3;";
expect(await format(sql, { fromClauseLayout: "newLine" })).toBe(
lines("SELECT alpha_column,", " beta_column,", " gamma_column,", " delta_column", "FROM", " some_really_long_table_name", "WHERE first_condition = 1", " AND second_condition = 2", " AND third_condition = 3;"),
);
});
it("honours the blank lines between queries", async () => {
const sql = "SELECT 1;\nSELECT 2;";
expect(await format(sql, { linesBetweenQueries: 0 })).toBe(lines("SELECT 1;", "SELECT 2;"));
expect(await format(sql, { linesBetweenQueries: 1 })).toBe(lines("SELECT 1;", "", "SELECT 2;"));
expect(await format(sql, { linesBetweenQueries: 2 })).toBe(lines("SELECT 1;", "", "", "SELECT 2;"));
});
it("keeps a blank source line when the setting is on", async () => {
expect(await format(lines("-- heading", "", "SELECT a FROM t;"), { preserveEmptyLines: true })).toBe(lines("-- heading", "", "SELECT a", "FROM t;"));
});
it("falls back to sql-formatter for the tabular indent styles", async () => {
// The tabular layouts pad every clause to a fixed column; the default
// layout rules do not reproduce them, so those settings keep the upstream
// formatter.
expect(await format("SELECT a, b FROM t WHERE x = 1;", { indentStyle: "tabularLeft" })).toBe(lines("SELECT a,", " b", "FROM t", "WHERE x = 1;"));
});
it("keeps every branch of a union whose operands are parenthesized (#10472)", async () => {
const sql = lines(
"SELECT a.id, a.starttime, t.name AS taskName",
"FROM ( (SELECT l.id, l.starttime FROM `log_1_4` l WHERE l.taskid = 'abc' ORDER BY l.starttime DESC LIMIT 0, 10)",
"UNION ALL (SELECT l.id, l.starttime FROM `log_2_4` l WHERE l.taskid = 'abc' ORDER BY l.starttime DESC LIMIT 0, 10)",
"UNION ALL (SELECT l.id, l.starttime FROM `log_3_4` l WHERE l.taskid = 'abc' ORDER BY l.starttime DESC LIMIT 0, 10) ) a",
"INNER JOIN task t ON a.taskid = t.id WHERE 1 = 1 ORDER BY a.starttime DESC LIMIT 0, 10",
);
// A bare `SELECT` after the operator becomes a sibling clause, but a
// parenthesized branch stays inside the set-operation node. Printing the
// keyword alone deleted every branch after the first one.
expect(await format(sql)).toBe(
lines(
"SELECT a.id,",
" a.starttime,",
" t.name AS taskName",
"FROM ((SELECT l.id, l.starttime FROM `log_1_4` l WHERE l.taskid = 'abc' ORDER BY l.starttime DESC LIMIT 0, 10)",
" UNION ALL",
" (SELECT l.id, l.starttime FROM `log_2_4` l WHERE l.taskid = 'abc' ORDER BY l.starttime DESC LIMIT 0, 10)",
" UNION ALL",
" (SELECT l.id, l.starttime FROM `log_3_4` l WHERE l.taskid = 'abc' ORDER BY l.starttime DESC LIMIT 0, 10)) a",
" INNER JOIN task t ON a.taskid = t.id",
"WHERE 1 = 1",
"ORDER BY a.starttime DESC",
"LIMIT 0, 10",
),
);
});
it("drops no branch of a long parenthesized union chain (#10472)", async () => {
const branches = Array.from({ length: 12 }, (_, index) => `SELECT l.id, l.payload FROM log_${index + 1}_4 l WHERE l.taskid = 'abc' AND l.ytenant_id = '0000MEJRT61ACN8QOZ0000' ORDER BY l.starttime DESC LIMIT 0, 10`);
const sql = `SELECT a.id FROM ( ${branches.map((branch) => `(${branch})`).join(" UNION ALL ")} ) a`;
const formatted = await format(sql);
for (let index = 0; index < branches.length; index += 1) expect(formatted).toContain(`log_${index + 1}_4`);
expect(formatted.match(/UNION ALL/g)).toHaveLength(branches.length - 1);
expect(formatted.match(/0000MEJRT61ACN8QOZ0000/g)).toHaveLength(branches.length);
});
it("formats a dialect without its own grammar", async () => {
expect(await format("select * from t where a = 1 and b = 2;", {}, "generic")).toBe("SELECT * FROM t WHERE a = 1 AND b = 2;");
});
it("formats a query with leading comma position (#7723, #5110)", async () => {
const sql = lines("SELECT loc_id, loc_name, loc_type, delivery_emp_num, update_time", "FROM tbl", "WHERE rn = 1;");
expect(await format(sql, { commaPosition: "before" })).toBe(lines("SELECT loc_id", " , loc_name", " , loc_type", " , delivery_emp_num", " , update_time", "FROM tbl", "WHERE rn = 1;"));
});
it("formats aligned projection aliases with leading commas", async () => {
const sql = lines("SELECT id AS emp_id, department_name AS dept, active_status AS status", "FROM employees;");
expect(await format(sql, { commaPosition: "before" })).toBe(lines("SELECT id AS emp_id", " , department_name AS dept", " , active_status AS status", "FROM employees;"));
});
it("formats CREATE TABLE with leading commas in DDL", async () => {
const sql = lines(
"CREATE TABLE IF NOT EXISTS `delivery_emp_info`",
"(",
" `id` varchar(20) PRIMARY KEY NOT NULL COMMENT '主键',",
" `emp_id` varchar(20) NOT NULL COMMENT '员工id',",
" `delivery_type_ids` varchar(512) DEFAULT NULL COMMENT '负责的运送类型id集合',",
" `update_time` datetime NOT NULL COMMENT '更新时间',",
" CONSTRAINT idx_emp_phone UNIQUE (emp_id, emp_phone_num) COMMENT '运送员id电话唯一'",
") ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin COMMENT '运达员信息表';",
);
expect(await format(sql, { commaPosition: "before" })).toBe(
lines(
"CREATE TABLE IF NOT EXISTS `delivery_emp_info`",
"(",
" `id` varchar(20) PRIMARY KEY NOT NULL COMMENT '主键'",
" , `emp_id` varchar(20) NOT NULL COMMENT '员工id'",
" , `delivery_type_ids` varchar(512) DEFAULT NULL COMMENT '负责的运送类型id集合'",
" , `update_time` datetime NOT NULL COMMENT '更新时间'",
" , CONSTRAINT idx_emp_phone UNIQUE (emp_id, emp_phone_num) COMMENT '运送员id电话唯一'",
") ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin COMMENT '运达员信息表';",
),
);
});
it("preserves keyword case when keywordCase is preserve (#11382)", async () => {
const sql = lines("select id as emp_id, name as emp_name", "from employees", "where status = 1 and dept = 'sales'", "order by id desc", "limit 10;");
expect(await format(sql, { keywordCase: "preserve" })).toBe(lines("select id as emp_id,", " name as emp_name", "from employees", "where status = 1", " and dept = 'sales'", "order by id desc", "limit 10;"));
});
it("converts keyword case to lower when keywordCase is lower (#11382)", async () => {
const sql = lines("SELECT id AS emp_id, name AS emp_name", "FROM employees", "WHERE status = 1 AND dept = 'sales'", "ORDER BY id DESC", "LIMIT 10;");
expect(await format(sql, { keywordCase: "lower" })).toBe(lines("select id as emp_id,", " name as emp_name", "from employees", "where status = 1", " and dept = 'sales'", "order by id desc", "limit 10;"));
});
it("preserves keyword case in join, union, case expression and ddl (#11382)", async () => {
const joinSql = "select a.id from t1 left join t2 on t1.id = t2.id;";
expect(await format(joinSql, { keywordCase: "preserve" })).toBe(lines("select a.id", "from t1", " left join t2 on t1.id = t2.id;"));
const unionSql = lines("select 1", "union all", "select 2;");
expect(await format(unionSql, { keywordCase: "preserve" })).toBe(lines("select 1", "union all", "select 2;"));
const caseSql = "select case when a = 1 then 'one' else 'other' end as val from t;";
expect(await format(caseSql, { keywordCase: "preserve" })).toBe("select case when a = 1 then 'one' else 'other' end as val from t;");
const ddlSql = "create table test (id int, name varchar(255));";
expect(await format(ddlSql, { keywordCase: "preserve" })).toBe("create table test (id int, name varchar(255));");
});
});