sz_orm_core/
dialect.rs

1//! 不同数据库的方言抽象
2//!
3//! 为数据库特定的 SQL 语法提供统一接口
4
5use crate::db_type::DbType;
6use crate::error::DbError;
7use std::fmt;
8
9/// L-4 修复：SQL 标识符最大长度（取所有主流数据库最严格值）
10///
11/// - PostgreSQL: 63 chars (NAMEDATALEN default 64, minus 1)
12/// - MySQL: 64 chars
13/// - Oracle: 30 chars (12.2R2 之前) / 128 chars (12.2R2+)
14/// - SQL Server: 128 chars
15/// - SQLite: 实际无限制（但建议遵守 63）
16///
17/// 取 63 作为最严格值，覆盖所有主流数据库。
18pub const MAX_IDENTIFIER_LEN: usize = 63;
19
20/// 数据库方言 trait
21///
22/// 实现者负责处理各数据库特有的 SQL 语法差异
23pub trait Dialect: Send + Sync {
24    /// 返回该方言对应的数据库类型
25    fn db_type(&self) -> DbType;
26
27    /// 引用标识符（表名、列名等）
28    fn quote(&self, identifier: &str) -> String;
29
30    /// L-4 修复：带校验的引用标识符
31    ///
32    /// 与 `quote()` 不同，此方法会先校验标识符：
33    /// - 非空
34    /// - 长度 ≤ `MAX_IDENTIFIER_LEN` (63 chars)
35    /// - 不含 SQL 元字符（引号、分号、空格、注释等）
36    ///
37    /// 校验失败时返回 `DbError::InvalidInput`。
38    ///
39    /// 建议在调用方不可信的场景（如用户输入的表名/列名）使用此方法替代 `quote()`。
40    fn quote_checked(&self, identifier: &str) -> Result<String, DbError> {
41        crate::sql_safety::validate_identifier(identifier, "identifier")?;
42        Ok(self.quote(identifier))
43    }
44
45    /// 转义字符串字面量，确保可安全嵌入 SQL
46    fn escape_string(&self, s: &str) -> String;
47
48    /// 该方言是否支持 RETURNING 子句
49    fn supports_returning(&self) -> bool;
50
51    /// 生成分页 SQL
52    fn build_pagination(&self, sql: &str, page: u64, limit: u64) -> String;
53
54    /// 获取 JSON 类型的 SQL 类型名
55    fn json_type(&self) -> &'static str;
56
57    /// 生成 JSON_EXTRACT 函数调用
58    fn json_extract(&self, column: &str, path: &str) -> String;
59
60    /// 生成全文检索 SQL
61    fn full_text_search(&self, columns: &[&str], keyword: &str) -> String;
62
63    /// 将布尔表达式转换为整型存储
64    fn bool_to_int(&self, expr: &str) -> String;
65
66    /// 生成 CONCAT 函数调用
67    fn concat(&self, parts: &[&str]) -> String;
68
69    /// 该方言是否支持 IF EXISTS
70    fn supports_if_exists(&self) -> bool;
71
72    /// 该方言是否支持 IF NOT EXISTS
73    fn supports_if_not_exists(&self) -> bool;
74
75    /// 获取自增列关键字
76    fn auto_increment_keyword(&self) -> &'static str;
77
78    /// 获取最近插入 ID 的 SQL（独立可执行语句）
79    ///
80    /// 返回 `None` 表示该方言不支持以独立 SQL 获取最后插入 ID（如 Oracle 只能通过
81    /// 在 INSERT 语句末尾附加 `RETURNING ... INTO :bind` 子句的方式获取，无法独立执行）。
82    /// 调用方在拿到 `None` 时必须改用 `supports_returning()` + 在 INSERT 后追加 RETURNING。
83    fn last_insert_id_sql(&self) -> Option<&'static str>;
84
85    /// 生成 CREATE TABLE 语句
86    fn build_create_table(&self, table: &str, columns: &[ColumnDef]) -> String;
87
88    /// 生成 ALTER TABLE 语句
89    fn build_alter_table(&self, table: &str, changes: &[TableChange]) -> String;
90
91    /// 生成 DROP TABLE 语句
92    ///
93    /// 默认实现生成标准 `DROP TABLE [IF EXISTS] <table>` 语法。
94    /// 不支持 `IF EXISTS` 的方言（如 DB2）应覆盖此方法。
95    fn build_drop_table(&self, table: &str, if_exists: bool) -> String {
96        if if_exists && self.supports_if_exists() {
97            format!("DROP TABLE IF EXISTS {}", self.quote(table))
98        } else {
99            format!("DROP TABLE {}", self.quote(table))
100        }
101    }
102}
103
104/// 建表时的列定义
105#[derive(Debug, Clone)]
106pub struct ColumnDef {
107    pub name: String,
108    pub sql_type: String,
109    pub nullable: bool,
110    pub default: Option<String>,
111    pub auto_increment: bool,
112    pub primary_key: bool,
113}
114
115/// ALTER TABLE 的变更操作
116#[derive(Debug, Clone)]
117pub enum TableChange {
118    AddColumn(ColumnDef),
119    DropColumn(String),
120    ModifyColumn(ColumnDef),
121    AddIndex(String, Vec<String>),
122    DropIndex(String),
123    AddForeignKey {
124        columns: Vec<String>,
125        reference_table: String,
126        reference_columns: Vec<String>,
127    },
128}
129
130/// MySQL 方言实现
131pub struct MySqlDialect;
132
133impl Dialect for MySqlDialect {
134    fn db_type(&self) -> DbType {
135        DbType::MySQL
136    }
137
138    fn quote(&self, identifier: &str) -> String {
139        format!("`{}`", identifier.replace('`', "``"))
140    }
141
142    fn escape_string(&self, s: &str) -> String {
143        let mut escaped = String::with_capacity(s.len() * 2);
144        for c in s.chars() {
145            match c {
146                '\\' => escaped.push_str("\\\\"),
147                '\'' => escaped.push_str("\\'"),
148                '\0' => escaped.push_str("\\0"),
149                '\n' => escaped.push_str("\\n"),
150                '\r' => escaped.push_str("\\r"),
151                '\t' => escaped.push_str("\\t"),
152                '\x1a' => escaped.push_str("\\Z"),
153                _ => escaped.push(c),
154            }
155        }
156        escaped
157    }
158
159    fn supports_returning(&self) -> bool {
160        false
161    }
162
163    fn build_pagination(&self, sql: &str, page: u64, limit: u64) -> String {
164        // M-2 修复说明：
165        //
166        // 历史上 MySQL 支持 `SQL_CALC_FOUND_ROWS` 提示配合 `FOUND_ROWS()` 函数
167        // 在不分页情况下获取总行数，但该特性在 MySQL 8.0.17 中被弃用并在后续版本移除。
168        // 官方推荐使用独立的 `COUNT(*)` 查询。
169        //
170        // 因此本实现不使用 `SQL_CALC_FOUND_ROWS`，调用方如需总数应单独执行 COUNT 查询。
171        let offset = page.saturating_sub(1).saturating_mul(limit);
172        format!("{} LIMIT {} OFFSET {}", sql, limit, offset)
173    }
174
175    fn json_type(&self) -> &'static str {
176        "JSON"
177    }
178
179    fn json_extract(&self, column: &str, path: &str) -> String {
180        // 规范化 path：确保以 $. 开头
181        let normalized = if path.starts_with('$') {
182            path.to_string()
183        } else {
184            format!("$.{}", path)
185        };
186        format!(
187            "JSON_EXTRACT({}, '{}')",
188            column,
189            self.escape_string(&normalized)
190        )
191    }
192
193    fn full_text_search(&self, columns: &[&str], keyword: &str) -> String {
194        let cols = columns.join(", ");
195        let escaped = self.escape_string(keyword);
196        format!(
197            "MATCH({}) AGAINST('{}' IN NATURAL LANGUAGE MODE)",
198            cols, escaped
199        )
200    }
201
202    fn bool_to_int(&self, expr: &str) -> String {
203        // 将布尔表达式转换为整数（0/1）用于存储
204        format!("IF({}, 1, 0)", expr)
205    }
206
207    fn concat(&self, parts: &[&str]) -> String {
208        if parts.is_empty() {
209            return "NULL".to_string();
210        }
211        let concat_parts: Vec<String> = parts
212            .iter()
213            .map(|p| format!("CAST({} AS CHAR)", p))
214            .collect();
215        format!("CONCAT({})", concat_parts.join(", "))
216    }
217
218    fn supports_if_exists(&self) -> bool {
219        true
220    }
221
222    fn supports_if_not_exists(&self) -> bool {
223        true
224    }
225
226    fn auto_increment_keyword(&self) -> &'static str {
227        "AUTO_INCREMENT"
228    }
229
230    fn last_insert_id_sql(&self) -> Option<&'static str> {
231        Some("LAST_INSERT_ID()")
232    }
233
234    fn build_create_table(&self, table: &str, columns: &[ColumnDef]) -> String {
235        let cols: Vec<String> = columns
236            .iter()
237            .map(|col| {
238                let mut sql = format!("{} {}", self.quote(&col.name), col.sql_type);
239                if !col.nullable {
240                    sql.push_str(" NOT NULL");
241                }
242                if let Some(default) = &col.default {
243                    sql.push_str(&format!(" DEFAULT {}", default));
244                }
245                if col.auto_increment {
246                    sql.push_str(&format!(" {}", self.auto_increment_keyword()));
247                }
248                if col.primary_key {
249                    sql.push_str(" PRIMARY KEY");
250                }
251                sql
252            })
253            .collect();
254
255        format!("CREATE TABLE {} ({})", self.quote(table), cols.join(", "))
256    }
257
258    fn build_alter_table(&self, table: &str, changes: &[TableChange]) -> String {
259        let stmts: Vec<String> = changes.iter().map(|change| {
260            match change {
261                TableChange::AddColumn(col) => {
262                    let mut sql = format!("ALTER TABLE {} ADD {}", self.quote(table), self.quote(&col.name));
263                    sql.push_str(&format!(" {}", col.sql_type));
264                    if !col.nullable {
265                        sql.push_str(" NOT NULL");
266                    }
267                    if let Some(default) = &col.default {
268                        sql.push_str(&format!(" DEFAULT {}", default));
269                    }
270                    sql
271                }
272                TableChange::DropColumn(name) => {
273                    format!("ALTER TABLE {} DROP COLUMN {}", self.quote(table), self.quote(name))
274                }
275                TableChange::ModifyColumn(col) => {
276                    // MySQL 使用 MODIFY COLUMN
277                    let mut sql = format!("ALTER TABLE {} MODIFY COLUMN {} {}", self.quote(table), self.quote(&col.name), col.sql_type);
278                    if !col.nullable {
279                        sql.push_str(" NOT NULL");
280                    }
281                    if let Some(default) = &col.default {
282                        sql.push_str(&format!(" DEFAULT {}", default));
283                    }
284                    sql
285                }
286                TableChange::AddIndex(name, cols) => {
287                    format!("ALTER TABLE {} ADD INDEX {} ({})", self.quote(table), name, cols.join(", "))
288                }
289                TableChange::DropIndex(name) => {
290                    format!("ALTER TABLE {} DROP INDEX {}", self.quote(table), name)
291                }
292                TableChange::AddForeignKey { columns, reference_table, reference_columns } => {
293                    format!("ALTER TABLE {} ADD CONSTRAINT fk_{}_{} FOREIGN KEY ({}) REFERENCES {} ({})",
294                        self.quote(table),
295                        table,
296                        columns.join("_"),
297                        columns.iter().map(|c| self.quote(c)).collect::<Vec<_>>().join(", "),
298                        self.quote(reference_table),
299                        reference_columns.iter().map(|c| self.quote(c)).collect::<Vec<_>>().join(", "))
300                }
301            }
302        }).collect();
303
304        stmts.join("; ")
305    }
306}
307
308/// PostgreSQL 方言实现
309pub struct PostgreSqlDialect;
310
311impl Dialect for PostgreSqlDialect {
312    fn db_type(&self) -> DbType {
313        DbType::PostgreSQL
314    }
315
316    fn quote(&self, identifier: &str) -> String {
317        format!("\"{}\"", identifier.replace('"', "\"\""))
318    }
319
320    fn escape_string(&self, s: &str) -> String {
321        // PostgreSQL 标准：使用双单引号转义单引号（standard_conforming_strings=on 默认）
322        // 反斜杠在 standard_conforming_strings=on 时不是转义字符
323        let mut escaped = String::with_capacity(s.len() * 2);
324        for c in s.chars() {
325            match c {
326                '\'' => escaped.push_str("''"),
327                _ => escaped.push(c),
328            }
329        }
330        escaped
331    }
332
333    fn supports_returning(&self) -> bool {
334        true
335    }
336
337    fn build_pagination(&self, sql: &str, page: u64, limit: u64) -> String {
338        let offset = page.saturating_sub(1).saturating_mul(limit);
339        format!("{} LIMIT {} OFFSET {}", sql, limit, offset)
340    }
341
342    fn json_type(&self) -> &'static str {
343        "JSONB"
344    }
345
346    fn json_extract(&self, column: &str, path: &str) -> String {
347        // PostgreSQL 使用 #>> 提取文本，path 以字符串数组形式
348        // 支持 $.a.b 或 a.b 格式
349        // 输出形式：column#>>'{a,b,c}'
350        // 路径组件中的特殊字符（逗号、花括号、双引号、反斜杠）需用双引号包裹并转义
351        let normalized = path.trim_start_matches("$.");
352        let parts: Vec<&str> = normalized.split('.').filter(|s| !s.is_empty()).collect();
353        let path_lit = parts
354            .iter()
355            .map(|p| {
356                // 如果包含特殊字符，用双引号包裹并转义内部双引号和反斜杠
357                let needs_quoting = p.chars().any(|c| matches!(c, ',' | '{' | '}' | '"' | '\\'));
358                if needs_quoting {
359                    let escaped = p.replace('\\', "\\\\").replace('"', "\\\"");
360                    format!("\"{}\"", escaped)
361                } else {
362                    p.to_string()
363                }
364            })
365            .collect::<Vec<_>>()
366            .join(",");
367        // 转义 SQL 字符串字面量中的单引号（PG 使用双单引号转义）
368        let path_lit_escaped = path_lit.replace('\'', "''");
369        format!("{}#>>'{{{}}}'", column, path_lit_escaped)
370    }
371
372    fn full_text_search(&self, columns: &[&str], keyword: &str) -> String {
373        let cols = columns
374            .iter()
375            .map(|c| format!("{}::text", c))
376            .collect::<Vec<_>>()
377            .join(" || ' ' || ");
378        let escaped = self.escape_string(keyword);
379        format!("to_tsvector({}) @@ to_tsquery('{}')", cols, escaped)
380    }
381
382    fn bool_to_int(&self, expr: &str) -> String {
383        format!("(CASE WHEN {} THEN 1 ELSE 0 END)", expr)
384    }
385
386    fn concat(&self, parts: &[&str]) -> String {
387        if parts.is_empty() {
388            return "NULL".to_string();
389        }
390        format!("CONCAT({})", parts.join(", "))
391    }
392
393    fn supports_if_exists(&self) -> bool {
394        true
395    }
396
397    fn supports_if_not_exists(&self) -> bool {
398        true
399    }
400
401    fn auto_increment_keyword(&self) -> &'static str {
402        "GENERATED BY DEFAULT AS IDENTITY"
403    }
404
405    fn last_insert_id_sql(&self) -> Option<&'static str> {
406        Some("lastval()")
407    }
408
409    fn build_create_table(&self, table: &str, columns: &[ColumnDef]) -> String {
410        let cols: Vec<String> = columns
411            .iter()
412            .map(|col| {
413                let mut sql = format!("{} {}", self.quote(&col.name), col.sql_type);
414                if !col.nullable {
415                    sql.push_str(" NOT NULL");
416                }
417                if let Some(default) = &col.default {
418                    sql.push_str(&format!(" DEFAULT {}", default));
419                }
420                if col.primary_key {
421                    sql.push_str(" PRIMARY KEY");
422                }
423                sql
424            })
425            .collect();
426
427        format!("CREATE TABLE {} ({})", self.quote(table), cols.join(", "))
428    }
429
430    fn build_alter_table(&self, table: &str, changes: &[TableChange]) -> String {
431        let stmts: Vec<String> = changes.iter().map(|change| {
432            match change {
433                TableChange::AddColumn(col) => {
434                    let mut sql = format!("ALTER TABLE {} ADD COLUMN {} {}", self.quote(table), self.quote(&col.name), col.sql_type);
435                    if !col.nullable {
436                        sql.push_str(" NOT NULL");
437                    }
438                    if let Some(default) = &col.default {
439                        sql.push_str(&format!(" DEFAULT {}", default));
440                    }
441                    sql
442                }
443                TableChange::DropColumn(name) => {
444                    format!("ALTER TABLE {} DROP COLUMN {}", self.quote(table), self.quote(name))
445                }
446                TableChange::ModifyColumn(col) => {
447                    // PostgreSQL 使用 ALTER COLUMN TYPE
448                    let mut sql = format!("ALTER TABLE {} ALTER COLUMN {} TYPE {}", self.quote(table), self.quote(&col.name), col.sql_type);
449                    if !col.nullable {
450                        sql.push_str(&format!(", ALTER COLUMN {} SET NOT NULL", self.quote(&col.name)));
451                    }
452                    if let Some(default) = &col.default {
453                        sql.push_str(&format!(", ALTER COLUMN {} SET DEFAULT {}", self.quote(&col.name), default));
454                    }
455                    sql
456                }
457                TableChange::AddIndex(name, cols) => {
458                    format!("CREATE INDEX {} ON {} ({})", name, self.quote(table), cols.join(", "))
459                }
460                TableChange::DropIndex(name) => {
461                    format!("DROP INDEX {}", name)
462                }
463                TableChange::AddForeignKey { columns, reference_table, reference_columns } => {
464                    format!("ALTER TABLE {} ADD CONSTRAINT fk_{}_{} FOREIGN KEY ({}) REFERENCES {} ({})",
465                        self.quote(table),
466                        table,
467                        columns.join("_"),
468                        columns.iter().map(|c| self.quote(c)).collect::<Vec<_>>().join(", "),
469                        self.quote(reference_table),
470                        reference_columns.iter().map(|c| self.quote(c)).collect::<Vec<_>>().join(", "))
471                }
472            }
473        }).collect();
474
475        stmts.join("; ")
476    }
477}
478
479/// SQLite 方言实现
480pub struct SqliteDialect;
481
482impl Dialect for SqliteDialect {
483    fn db_type(&self) -> DbType {
484        DbType::Sqlite
485    }
486
487    fn quote(&self, identifier: &str) -> String {
488        format!("\"{}\"", identifier.replace('"', "\"\""))
489    }
490
491    fn escape_string(&self, s: &str) -> String {
492        let mut escaped = String::with_capacity(s.len() * 2);
493        for c in s.chars() {
494            match c {
495                '\'' => escaped.push_str("''"),
496                _ => escaped.push(c),
497            }
498        }
499        escaped
500    }
501
502    fn supports_returning(&self) -> bool {
503        true
504    }
505
506    fn build_pagination(&self, sql: &str, page: u64, limit: u64) -> String {
507        let offset = page.saturating_sub(1).saturating_mul(limit);
508        format!("{} LIMIT {} OFFSET {}", sql, limit, offset)
509    }
510
511    fn json_type(&self) -> &'static str {
512        "TEXT"
513    }
514
515    fn json_extract(&self, column: &str, path: &str) -> String {
516        // SQLite 使用 json_extract(column, '$.path')
517        let normalized = if path.starts_with('$') {
518            path.to_string()
519        } else {
520            format!("$.{}", path)
521        };
522        format!(
523            "json_extract({}, '{}')",
524            column,
525            self.escape_string(&normalized)
526        )
527    }
528
529    fn full_text_search(&self, columns: &[&str], keyword: &str) -> String {
530        // SQLite FTS 的 MATCH 操作符必须作用于 FTS 虚拟表本身（`tbl MATCH 'query'`），
531        // 不能用于单个列；本接口仅传入列名，没有 FTS 表名，因此降级使用 LIKE。
532        // 这样既能避免生成错误的 MATCH 语法，又能在普通表上工作（不依赖 FTS 索引）。
533        if columns.is_empty() {
534            return "0".to_string();
535        }
536        let escaped = self.escape_string(keyword);
537        columns
538            .iter()
539            .map(|c| format!("{} LIKE '%{}%'", c.trim(), escaped))
540            .collect::<Vec<_>>()
541            .join(" OR ")
542    }
543
544    fn bool_to_int(&self, expr: &str) -> String {
545        expr.to_string()
546    }
547
548    fn concat(&self, parts: &[&str]) -> String {
549        if parts.is_empty() {
550            return "NULL".to_string();
551        }
552        // SQLite 的 || 操作符在任意参数为 NULL 时整体结果为 NULL，
553        // 需要先用 COALESCE 把每个参数替换为空串，才能保证拼接结果非 NULL。
554        let coalesced: Vec<String> = parts
555            .iter()
556            .map(|p| format!("COALESCE({}, '')", p))
557            .collect();
558        coalesced.join(" || ")
559    }
560
561    fn supports_if_exists(&self) -> bool {
562        true
563    }
564
565    fn supports_if_not_exists(&self) -> bool {
566        true
567    }
568
569    fn auto_increment_keyword(&self) -> &'static str {
570        "AUTOINCREMENT"
571    }
572
573    fn last_insert_id_sql(&self) -> Option<&'static str> {
574        Some("last_insert_rowid()")
575    }
576
577    fn build_create_table(&self, table: &str, columns: &[ColumnDef]) -> String {
578        let cols: Vec<String> = columns
579            .iter()
580            .map(|col| {
581                let mut sql = format!("{} {}", self.quote(&col.name), col.sql_type);
582                if !col.nullable {
583                    sql.push_str(" NOT NULL");
584                }
585                if let Some(default) = &col.default {
586                    sql.push_str(&format!(" DEFAULT {}", default));
587                }
588                if col.auto_increment {
589                    sql.push_str(" PRIMARY KEY AUTOINCREMENT");
590                } else if col.primary_key {
591                    sql.push_str(" PRIMARY KEY");
592                }
593                sql
594            })
595            .collect();
596
597        format!("CREATE TABLE {} ({})", self.quote(table), cols.join(", "))
598    }
599
600    fn build_alter_table(&self, table: &str, changes: &[TableChange]) -> String {
601        // SQLite 支持的 ALTER 操作：ADD COLUMN, RENAME COLUMN, DROP COLUMN (3.35+), RENAME TABLE
602        // 不支持：MODIFY COLUMN, ADD INDEX (需用 CREATE INDEX), ADD FOREIGN KEY (语法层面不支持)
603        let stmts: Vec<String> = changes
604            .iter()
605            .map(|change| {
606                match change {
607                    TableChange::AddColumn(col) => {
608                        let mut sql = format!(
609                            "ALTER TABLE {} ADD COLUMN {} {}",
610                            self.quote(table),
611                            self.quote(&col.name),
612                            col.sql_type
613                        );
614                        if !col.nullable {
615                            sql.push_str(" NOT NULL");
616                        }
617                        if let Some(default) = &col.default {
618                            sql.push_str(&format!(" DEFAULT {}", default));
619                        }
620                        sql
621                    }
622                    TableChange::DropColumn(name) => {
623                        // SQLite 3.35.0+ 支持 DROP COLUMN
624                        format!(
625                            "ALTER TABLE {} DROP COLUMN {}",
626                            self.quote(table),
627                            self.quote(name)
628                        )
629                    }
630                    TableChange::ModifyColumn(col) => {
631                        // SQLite 不直接支持 MODIFY COLUMN，需要用 12 步流程
632                        // 这里生成注释 SQL，提示用户需手动处理
633                        format!(
634                            "-- SQLite 不支持 MODIFY COLUMN（{} {}），需重建表",
635                            col.name, col.sql_type
636                        )
637                    }
638                    TableChange::AddIndex(name, cols) => {
639                        format!(
640                            "CREATE INDEX {} ON {} ({})",
641                            name,
642                            self.quote(table),
643                            cols.join(", ")
644                        )
645                    }
646                    TableChange::DropIndex(name) => {
647                        format!("DROP INDEX {}", name)
648                    }
649                    TableChange::AddForeignKey {
650                        columns,
651                        reference_table,
652                        reference_columns: _,
653                    } => {
654                        // SQLite 不支持 ALTER TABLE ADD FOREIGN KEY，需重建表
655                        format!(
656                            "-- SQLite 不支持 ADD FOREIGN KEY（{} -> {}），需重建表",
657                            columns.join(","),
658                            reference_table
659                        )
660                    }
661                }
662            })
663            .collect();
664
665        stmts.join("; ")
666    }
667}
668
669/// 将通用 SQL 类型映射为 Oracle 23ai 类型
670///
671/// Oracle 类型与常见类型的对应关系：
672/// - BIGINT → NUMBER(19)
673/// - INT/INTEGER → NUMBER(10)
674/// - VARCHAR(n) → VARCHAR2(n)
675/// - TEXT 系列 → CLOB
676/// - BOOLEAN/BOOL → NUMBER(1)
677fn map_to_oracle_type(sql_type: &str) -> String {
678    let upper = sql_type.to_uppercase();
679    let trimmed = upper.trim();
680
681    if trimmed.starts_with("BIGINT") {
682        sql_type.replacen("BIGINT", "NUMBER(19)", 1)
683    } else if trimmed.starts_with("VARCHAR2") {
684        sql_type.to_string()
685    } else if trimmed.starts_with("VARCHAR") {
686        sql_type.replacen("VARCHAR", "VARCHAR2", 1)
687    } else if matches!(trimmed, "TEXT" | "MEDIUMTEXT" | "LONGTEXT" | "TINYTEXT") {
688        "CLOB".to_string()
689    } else if matches!(trimmed, "BOOLEAN" | "BOOL") {
690        "NUMBER(1)".to_string()
691    } else if trimmed == "INTEGER" {
692        "NUMBER(10)".to_string()
693    } else if trimmed.starts_with("INT") {
694        sql_type.replacen("INT", "NUMBER(10)", 1)
695    } else {
696        sql_type.to_string()
697    }
698}
699
700/// Oracle 方言实现（Oracle 23ai）
701pub struct OracleDialect;
702
703impl Dialect for OracleDialect {
704    fn db_type(&self) -> DbType {
705        DbType::Oracle
706    }
707
708    fn quote(&self, identifier: &str) -> String {
709        // Oracle 标准使用双引号包裹标识符，内部双引号双写
710        format!("\"{}\"", identifier.replace('"', "\"\""))
711    }
712
713    fn escape_string(&self, s: &str) -> String {
714        // Oracle 标准转义：单引号双写（'O''Brien'），反斜杠不转义
715        let mut escaped = String::with_capacity(s.len() * 2);
716        for c in s.chars() {
717            match c {
718                '\'' => escaped.push_str("''"),
719                _ => escaped.push(c),
720            }
721        }
722        escaped
723    }
724
725    fn supports_returning(&self) -> bool {
726        // Oracle 12c+ 支持 RETURNING，23ai 当然支持
727        true
728    }
729
730    fn build_pagination(&self, sql: &str, page: u64, limit: u64) -> String {
731        // Oracle 12c+ 使用 OFFSET/FETCH NEXT 语法
732        // 与其他方言保持一致：page=1 为第一页（offset=0）
733        let offset = page.saturating_sub(1).saturating_mul(limit);
734        format!(
735            "{} OFFSET {} ROWS FETCH NEXT {} ROWS ONLY",
736            sql, offset, limit
737        )
738    }
739
740    fn json_type(&self) -> &'static str {
741        // Oracle 21+ 原生支持 JSON 类型，23ai 完整支持
742        "JSON"
743    }
744
745    fn json_extract(&self, column: &str, path: &str) -> String {
746        // Oracle 使用 JSON_VALUE 提取标量值，path 需以 $. 开头
747        let normalized = if path.starts_with('$') {
748            path.to_string()
749        } else {
750            format!("$.{}", path)
751        };
752        format!(
753            "JSON_VALUE({}, '{}')",
754            column,
755            self.escape_string(&normalized)
756        )
757    }
758
759    fn full_text_search(&self, columns: &[&str], keyword: &str) -> String {
760        // Oracle 使用 CONTAINS 函数（需要 CONTEXT 索引）
761        // CONTAINS(column, keyword, 1) > 0
762        if columns.is_empty() {
763            return "0".to_string();
764        }
765        let escaped = self.escape_string(keyword);
766        let parts: Vec<String> = columns
767            .iter()
768            .map(|c| format!("CONTAINS({}, '{}', 1) > 0", c, escaped))
769            .collect();
770        parts.join(" OR ")
771    }
772
773    fn bool_to_int(&self, expr: &str) -> String {
774        // Oracle 没有原生 BOOL 类型，使用 CASE WHEN 转换为 0/1
775        format!("(CASE WHEN {} THEN 1 ELSE 0 END)", expr)
776    }
777
778    fn concat(&self, parts: &[&str]) -> String {
779        // Oracle 使用 || 操作符进行字符串拼接
780        if parts.is_empty() {
781            return "NULL".to_string();
782        }
783        parts.join(" || ")
784    }
785
786    fn supports_if_exists(&self) -> bool {
787        // Oracle 23ai 支持 DROP TABLE IF EXISTS
788        true
789    }
790
791    fn supports_if_not_exists(&self) -> bool {
792        // Oracle 23ai 支持 CREATE TABLE IF NOT EXISTS
793        true
794    }
795
796    fn auto_increment_keyword(&self) -> &'static str {
797        // Oracle 12c+ 使用 IDENTITY 列
798        "GENERATED BY DEFAULT AS IDENTITY"
799    }
800
801    fn last_insert_id_sql(&self) -> Option<&'static str> {
802        // Oracle 没有独立可执行的"获取最后插入 ID"语句。
803        // `RETURNING {pk} INTO :bind` 是 PL/SQL 子句，必须附加在 INSERT 之后，
804        // 不能作为独立 SQL 执行。调用方应改用 `supports_returning()` 在 INSERT
805        // 末尾追加 RETURNING 子句获取自增值。
806        None
807    }
808
809    fn build_create_table(&self, table: &str, columns: &[ColumnDef]) -> String {
810        let cols: Vec<String> = columns
811            .iter()
812            .map(|col| {
813                let oracle_type = map_to_oracle_type(&col.sql_type);
814                let mut sql = format!("{} {}", self.quote(&col.name), oracle_type);
815                // Oracle IDENTITY 列隐式 NOT NULL，不允许显式 NOT NULL（ORA-03076）
816                if !col.nullable && !col.auto_increment {
817                    sql.push_str(" NOT NULL");
818                }
819                if let Some(default) = &col.default {
820                    sql.push_str(&format!(" DEFAULT {}", default));
821                }
822                if col.auto_increment {
823                    sql.push_str(&format!(" {}", self.auto_increment_keyword()));
824                }
825                if col.primary_key {
826                    sql.push_str(" PRIMARY KEY");
827                }
828                sql
829            })
830            .collect();
831
832        format!("CREATE TABLE {} ({})", self.quote(table), cols.join(", "))
833    }
834
835    fn build_alter_table(&self, table: &str, changes: &[TableChange]) -> String {
836        let stmts: Vec<String> = changes
837            .iter()
838            .map(|change| match change {
839                TableChange::AddColumn(col) => {
840                    let oracle_type = map_to_oracle_type(&col.sql_type);
841                    let mut sql = format!(
842                        "ALTER TABLE {} ADD {} {}",
843                        self.quote(table),
844                        self.quote(&col.name),
845                        oracle_type
846                    );
847                    if !col.nullable {
848                        sql.push_str(" NOT NULL");
849                    }
850                    if let Some(default) = &col.default {
851                        sql.push_str(&format!(" DEFAULT {}", default));
852                    }
853                    sql
854                }
855                TableChange::DropColumn(name) => {
856                    format!(
857                        "ALTER TABLE {} DROP COLUMN {}",
858                        self.quote(table),
859                        self.quote(name)
860                    )
861                }
862                TableChange::ModifyColumn(col) => {
863                    // Oracle 使用 MODIFY 关键字（不需 COLUMN）
864                    let oracle_type = map_to_oracle_type(&col.sql_type);
865                    let mut sql = format!(
866                        "ALTER TABLE {} MODIFY {} {}",
867                        self.quote(table),
868                        self.quote(&col.name),
869                        oracle_type
870                    );
871                    if !col.nullable {
872                        sql.push_str(" NOT NULL");
873                    }
874                    if let Some(default) = &col.default {
875                        sql.push_str(&format!(" DEFAULT {}", default));
876                    }
877                    sql
878                }
879                TableChange::AddIndex(name, cols) => {
880                    format!(
881                        "CREATE INDEX {} ON {} ({})",
882                        name,
883                        self.quote(table),
884                        cols.join(", ")
885                    )
886                }
887                TableChange::DropIndex(name) => {
888                    format!("DROP INDEX {}", name)
889                }
890                TableChange::AddForeignKey {
891                    columns,
892                    reference_table,
893                    reference_columns,
894                } => {
895                    format!(
896                        "ALTER TABLE {} ADD CONSTRAINT fk_{}_{} FOREIGN KEY ({}) REFERENCES {} ({})",
897                        self.quote(table),
898                        table,
899                        columns.join("_"),
900                        columns.iter().map(|c| self.quote(c)).collect::<Vec<_>>().join(", "),
901                        self.quote(reference_table),
902                        reference_columns.iter().map(|c| self.quote(c)).collect::<Vec<_>>().join(", ")
903                    )
904                }
905            })
906            .collect();
907
908        stmts.join("; ")
909    }
910}
911
912/// 将通用 SQL 类型映射为 SQL Server 类型
913///
914/// - BIGINT → BIGINT（保持）
915/// - INT/INTEGER → INT
916/// - VARCHAR(n) → NVARCHAR(n)（统一使用 Unicode）
917/// - TEXT 系列 → NVARCHAR(MAX)
918/// - BOOLEAN/BOOL → BIT
919/// - 其他类型保持不变
920fn map_to_sqlserver_type(sql_type: &str) -> String {
921    let upper = sql_type.to_uppercase();
922    let trimmed = upper.trim();
923
924    if trimmed.starts_with("BIGINT") {
925        sql_type.to_string()
926    } else if matches!(trimmed, "INT" | "INTEGER") {
927        "INT".to_string()
928    } else if trimmed.starts_with("NVARCHAR") {
929        sql_type.to_string()
930    } else if trimmed.starts_with("VARCHAR") {
931        sql_type.replacen("VARCHAR", "NVARCHAR", 1)
932    } else if matches!(trimmed, "TEXT" | "MEDIUMTEXT" | "LONGTEXT" | "TINYTEXT") {
933        "NVARCHAR(MAX)".to_string()
934    } else if matches!(trimmed, "BOOLEAN" | "BOOL") {
935        "BIT".to_string()
936    } else {
937        sql_type.to_string()
938    }
939}
940
941/// SQL Server 方言实现（SQL Server 2012+ / T-SQL）
942pub struct SqlServerDialect;
943
944impl Dialect for SqlServerDialect {
945    fn db_type(&self) -> DbType {
946        DbType::SqlServer
947    }
948
949    fn quote(&self, identifier: &str) -> String {
950        // SQL Server 使用 [name] 包裹标识符，内部 ] 双写为 ]]
951        format!("[{}]", identifier.replace(']', "]]"))
952    }
953
954    fn escape_string(&self, s: &str) -> String {
955        // SQL Server 标准：单引号双写（'O''Brien'），反斜杠不转义
956        let mut escaped = String::with_capacity(s.len() * 2);
957        for c in s.chars() {
958            match c {
959                '\'' => escaped.push_str("''"),
960                _ => escaped.push(c),
961            }
962        }
963        escaped
964    }
965
966    fn supports_returning(&self) -> bool {
967        // SQL Server 使用 OUTPUT 子句，语义上等价于 RETURNING
968        true
969    }
970
971    fn build_pagination(&self, sql: &str, page: u64, limit: u64) -> String {
972        // SQL Server 2012+ 使用 OFFSET ... ROWS FETCH NEXT ... ROWS ONLY
973        let offset = page.saturating_sub(1).saturating_mul(limit);
974        format!(
975            "{} OFFSET {} ROWS FETCH NEXT {} ROWS ONLY",
976            sql, offset, limit
977        )
978    }
979
980    fn json_type(&self) -> &'static str {
981        // SQL Server 2016+ 使用 NVARCHAR(MAX) 存储 JSON
982        "NVARCHAR(MAX)"
983    }
984
985    fn json_extract(&self, column: &str, path: &str) -> String {
986        // SQL Server 2016+ 使用 JSON_VALUE 提取标量
987        let normalized = if path.starts_with('$') {
988            path.to_string()
989        } else {
990            format!("$.{}", path)
991        };
992        format!(
993            "JSON_VALUE({}, '{}')",
994            column,
995            self.escape_string(&normalized)
996        )
997    }
998
999    fn full_text_search(&self, columns: &[&str], keyword: &str) -> String {
1000        // SQL Server 使用 CONTAINS（需要 FULLTEXT 索引）
1001        if columns.is_empty() {
1002            return "0".to_string();
1003        }
1004        let escaped = self.escape_string(keyword);
1005        let cols = columns.join(", ");
1006        format!("CONTAINS({}, '{}')", cols, escaped)
1007    }
1008
1009    fn bool_to_int(&self, expr: &str) -> String {
1010        // SQL Server 使用 BIT 类型，CASE WHEN 转换为 0/1
1011        format!("(CASE WHEN {} THEN 1 ELSE 0 END)", expr)
1012    }
1013
1014    fn concat(&self, parts: &[&str]) -> String {
1015        if parts.is_empty() {
1016            return "NULL".to_string();
1017        }
1018        format!("CONCAT({})", parts.join(", "))
1019    }
1020
1021    fn supports_if_exists(&self) -> bool {
1022        // SQL Server 2016+ 支持 IF EXISTS
1023        true
1024    }
1025
1026    fn supports_if_not_exists(&self) -> bool {
1027        // SQL Server 2016+ 支持 IF NOT EXISTS
1028        true
1029    }
1030
1031    fn auto_increment_keyword(&self) -> &'static str {
1032        // SQL Server 使用 IDENTITY(1,1) 列属性
1033        "IDENTITY(1,1)"
1034    }
1035
1036    fn last_insert_id_sql(&self) -> Option<&'static str> {
1037        // SCOPE_IDENTITY() 返回当前作用域内最后生成的标识值
1038        Some("SCOPE_IDENTITY()")
1039    }
1040
1041    fn build_create_table(&self, table: &str, columns: &[ColumnDef]) -> String {
1042        let cols: Vec<String> = columns
1043            .iter()
1044            .map(|col| {
1045                let sqlserver_type = map_to_sqlserver_type(&col.sql_type);
1046                let mut sql = format!("{} {}", self.quote(&col.name), sqlserver_type);
1047                if !col.nullable {
1048                    sql.push_str(" NOT NULL");
1049                }
1050                if let Some(default) = &col.default {
1051                    sql.push_str(&format!(" DEFAULT {}", default));
1052                }
1053                if col.auto_increment {
1054                    sql.push_str(&format!(" {}", self.auto_increment_keyword()));
1055                }
1056                if col.primary_key {
1057                    sql.push_str(" PRIMARY KEY");
1058                }
1059                sql
1060            })
1061            .collect();
1062
1063        format!("CREATE TABLE {} ({})", self.quote(table), cols.join(", "))
1064    }
1065
1066    fn build_alter_table(&self, table: &str, changes: &[TableChange]) -> String {
1067        let stmts: Vec<String> = changes
1068            .iter()
1069            .map(|change| match change {
1070                TableChange::AddColumn(col) => {
1071                    let sqlserver_type = map_to_sqlserver_type(&col.sql_type);
1072                    let mut sql = format!(
1073                        "ALTER TABLE {} ADD {} {}",
1074                        self.quote(table),
1075                        self.quote(&col.name),
1076                        sqlserver_type
1077                    );
1078                    if !col.nullable {
1079                        sql.push_str(" NOT NULL");
1080                    }
1081                    if let Some(default) = &col.default {
1082                        sql.push_str(&format!(" DEFAULT {}", default));
1083                    }
1084                    sql
1085                }
1086                TableChange::DropColumn(name) => {
1087                    format!(
1088                        "ALTER TABLE {} DROP COLUMN {}",
1089                        self.quote(table),
1090                        self.quote(name)
1091                    )
1092                }
1093                TableChange::ModifyColumn(col) => {
1094                    // SQL Server 使用 ALTER COLUMN（不是 MODIFY）
1095                    let sqlserver_type = map_to_sqlserver_type(&col.sql_type);
1096                    let mut sql = format!(
1097                        "ALTER TABLE {} ALTER COLUMN {} {}",
1098                        self.quote(table),
1099                        self.quote(&col.name),
1100                        sqlserver_type
1101                    );
1102                    if !col.nullable {
1103                        sql.push_str(" NOT NULL");
1104                    }
1105                    if let Some(default) = &col.default {
1106                        sql.push_str(&format!(" DEFAULT {}", default));
1107                    }
1108                    sql
1109                }
1110                TableChange::AddIndex(name, cols) => {
1111                    format!(
1112                        "CREATE INDEX {} ON {} ({})",
1113                        name,
1114                        self.quote(table),
1115                        cols.join(", ")
1116                    )
1117                }
1118                TableChange::DropIndex(name) => {
1119                    // SQL Server 的 DROP INDEX 必须指定表名
1120                    format!("DROP INDEX {} ON {}", name, self.quote(table))
1121                }
1122                TableChange::AddForeignKey {
1123                    columns,
1124                    reference_table,
1125                    reference_columns,
1126                } => {
1127                    format!(
1128                        "ALTER TABLE {} ADD CONSTRAINT fk_{}_{} FOREIGN KEY ({}) REFERENCES {} ({})",
1129                        self.quote(table),
1130                        table,
1131                        columns.join("_"),
1132                        columns.iter().map(|c| self.quote(c)).collect::<Vec<_>>().join(", "),
1133                        self.quote(reference_table),
1134                        reference_columns.iter().map(|c| self.quote(c)).collect::<Vec<_>>().join(", ")
1135                    )
1136                }
1137            })
1138            .collect();
1139
1140        stmts.join("; ")
1141    }
1142}
1143
1144// 架构说明：build_create_table / build_alter_table / build_drop_table 在四个方言中
1145// 存在部分重复代码。当前选择保持各方言独立实现以确保 SQL 方言差异的显式性，
1146// 避免过度抽象导致维护复杂度上升。如需重构，可抽出公共构建器（参见 dialect 重构 RFC）。
1147
1148// ============================================================================
1149// 兼容方言（基于现有方言委派实现）
1150//
1151// 以下方言在 SQL 语法上与某个基础方言完全兼容，仅在 db_type() 上有区别。
1152// 使用宏减少重复代码，保持维护性。
1153// ============================================================================
1154
1155/// 将方言实现委派给基础方言的宏
1156///
1157/// `$wrapper`：新方言结构体名
1158/// `$base`：基础方言结构体名（如 MySqlDialect）
1159/// `$db_type`：返回的 DbType 变体
1160macro_rules! delegate_dialect_to {
1161    ($wrapper:ident, $base:ident, $db_type:expr) => {
1162        /// 兼容方言（委派给基础方言实现）
1163        pub struct $wrapper;
1164
1165        impl Dialect for $wrapper {
1166            fn db_type(&self) -> DbType {
1167                $db_type
1168            }
1169            fn quote(&self, identifier: &str) -> String {
1170                $base.quote(identifier)
1171            }
1172            fn escape_string(&self, s: &str) -> String {
1173                $base.escape_string(s)
1174            }
1175            fn supports_returning(&self) -> bool {
1176                $base.supports_returning()
1177            }
1178            fn build_pagination(&self, sql: &str, page: u64, limit: u64) -> String {
1179                $base.build_pagination(sql, page, limit)
1180            }
1181            fn json_type(&self) -> &'static str {
1182                $base.json_type()
1183            }
1184            fn json_extract(&self, column: &str, path: &str) -> String {
1185                $base.json_extract(column, path)
1186            }
1187            fn full_text_search(&self, columns: &[&str], keyword: &str) -> String {
1188                $base.full_text_search(columns, keyword)
1189            }
1190            fn bool_to_int(&self, expr: &str) -> String {
1191                $base.bool_to_int(expr)
1192            }
1193            fn concat(&self, parts: &[&str]) -> String {
1194                $base.concat(parts)
1195            }
1196            fn supports_if_exists(&self) -> bool {
1197                $base.supports_if_exists()
1198            }
1199            fn supports_if_not_exists(&self) -> bool {
1200                $base.supports_if_not_exists()
1201            }
1202            fn auto_increment_keyword(&self) -> &'static str {
1203                $base.auto_increment_keyword()
1204            }
1205            fn last_insert_id_sql(&self) -> Option<&'static str> {
1206                $base.last_insert_id_sql()
1207            }
1208            fn build_create_table(&self, table: &str, columns: &[ColumnDef]) -> String {
1209                $base.build_create_table(table, columns)
1210            }
1211            fn build_alter_table(&self, table: &str, changes: &[TableChange]) -> String {
1212                $base.build_alter_table(table, changes)
1213            }
1214            fn build_drop_table(&self, table: &str, if_exists: bool) -> String {
1215                $base.build_drop_table(table, if_exists)
1216            }
1217        }
1218    };
1219}
1220
1221// MariaDB：MySQL 兼容方言
1222delegate_dialect_to!(MariaDbDialect, MySqlDialect, DbType::MariaDB);
1223
1224// TiDB：MySQL 兼容分布式数据库
1225delegate_dialect_to!(TiDbDialect, MySqlDialect, DbType::TiDB);
1226
1227// KingbaseES：人大金仓，PostgreSQL 兼容方言
1228delegate_dialect_to!(KingbaseDialect, PostgreSqlDialect, DbType::Kingbase);
1229
1230// PolarDB：阿里云，PostgreSQL 兼容（PG 版本）
1231delegate_dialect_to!(PolarDbDialect, PostgreSqlDialect, DbType::PolarDB);
1232
1233// GaussDB：华为云，PostgreSQL 兼容分布式数据库
1234delegate_dialect_to!(GaussDbDialect, PostgreSqlDialect, DbType::GaussDB);
1235
1236// Dameng：达梦 DM8，Oracle 兼容方言
1237delegate_dialect_to!(DamengDialect, OracleDialect, DbType::Dameng);
1238
1239// Sybase ASE：与 SQL Server T-SQL 高度兼容
1240delegate_dialect_to!(SybaseDialect, SqlServerDialect, DbType::Sybase);
1241
1242// GBase 8s：南大通用，Informix 兼容方言，SQL 语法接近 T-SQL
1243delegate_dialect_to!(GBaseDialect, SqlServerDialect, DbType::GBase);
1244
1245// ============================================================================
1246// ClickHouse 方言（独立实现）
1247//
1248// ClickHouse 是列式 OLAP 数据库，有独特的类型系统和函数：
1249// - 使用 backtick 标识符（与 MySQL 一致）
1250// - 字符串字面量单引号转义为 \'（与 MySQL 一致）
1251// - 不支持事务、不支持 RETURNING
1252// - 分页使用 LIMIT offset, limit 语法（与 MySQL 一致）
1253// - 自增列不支持（使用 UUID 或物化列）
1254// - 类型系统：String/UInt64/Int64/Float64/DateTime 等
1255// ============================================================================
1256
1257/// ClickHouse 方言实现（列式 OLAP 数据库）
1258pub struct ClickHouseDialect;
1259
1260impl Dialect for ClickHouseDialect {
1261    fn db_type(&self) -> DbType {
1262        DbType::ClickHouse
1263    }
1264
1265    fn quote(&self, identifier: &str) -> String {
1266        // ClickHouse 使用 backquote 包裹标识符（与 MySQL 一致）
1267        format!("`{}`", identifier.replace('`', "``"))
1268    }
1269
1270    fn escape_string(&self, s: &str) -> String {
1271        // ClickHouse 使用反斜杠转义（与 MySQL 一致）
1272        let mut escaped = String::with_capacity(s.len() * 2);
1273        for c in s.chars() {
1274            match c {
1275                '\'' => escaped.push_str("\\'"),
1276                '\\' => escaped.push_str("\\\\"),
1277                '\n' => escaped.push_str("\\n"),
1278                '\r' => escaped.push_str("\\r"),
1279                '\t' => escaped.push_str("\\t"),
1280                _ => escaped.push(c),
1281            }
1282        }
1283        escaped
1284    }
1285
1286    fn supports_returning(&self) -> bool {
1287        // ClickHouse 不支持 RETURNING 子句
1288        false
1289    }
1290
1291    fn build_pagination(&self, sql: &str, page: u64, limit: u64) -> String {
1292        // ClickHouse 使用 LIMIT offset, limit 语法
1293        let offset = page.saturating_sub(1).saturating_mul(limit);
1294        format!("{} LIMIT {}, {}", sql, offset, limit)
1295    }
1296
1297    fn json_type(&self) -> &'static str {
1298        // ClickHouse 使用 String 存储 JSON，或使用 JSON 类型（实验性）
1299        "String"
1300    }
1301
1302    fn json_extract(&self, column: &str, path: &str) -> String {
1303        // ClickHouse 使用 JSONExtractString 函数
1304        let normalized = if path.starts_with('$') {
1305            path.to_string()
1306        } else {
1307            format!("$.{}", path)
1308        };
1309        format!(
1310            "JSONExtractString({}, '{}')",
1311            column,
1312            self.escape_string(&normalized)
1313        )
1314    }
1315
1316    fn full_text_search(&self, columns: &[&str], keyword: &str) -> String {
1317        // ClickHouse 使用 position() + like 进行全文检索（无原生 FTS）
1318        if columns.is_empty() {
1319            return "0".to_string();
1320        }
1321        let escaped = self.escape_string(keyword);
1322        let parts: Vec<String> = columns
1323            .iter()
1324            .map(|c| format!("position({}, '{}') > 0", c, escaped))
1325            .collect();
1326        parts.join(" OR ")
1327    }
1328
1329    fn bool_to_int(&self, expr: &str) -> String {
1330        // ClickHouse 支持 toUInt8 转换
1331        format!("toUInt8({})", expr)
1332    }
1333
1334    fn concat(&self, parts: &[&str]) -> String {
1335        // ClickHouse 使用 concat() 函数
1336        if parts.is_empty() {
1337            return "''".to_string();
1338        }
1339        format!("concat({})", parts.join(", "))
1340    }
1341
1342    fn supports_if_exists(&self) -> bool {
1343        true
1344    }
1345
1346    fn supports_if_not_exists(&self) -> bool {
1347        true
1348    }
1349
1350    fn auto_increment_keyword(&self) -> &'static str {
1351        // ClickHouse 不支持自增列，使用 UUID 默认值
1352        ""
1353    }
1354
1355    fn last_insert_id_sql(&self) -> Option<&'static str> {
1356        // ClickHouse 不支持 last_insert_id
1357        None
1358    }
1359
1360    fn build_create_table(&self, table: &str, columns: &[ColumnDef]) -> String {
1361        let cols: Vec<String> = columns
1362            .iter()
1363            .map(|col| {
1364                let ch_type = map_to_clickhouse_type(&col.sql_type);
1365                let mut sql = format!("{} {}", self.quote(&col.name), ch_type);
1366                if let Some(default) = &col.default {
1367                    sql.push_str(&format!(" DEFAULT {}", default));
1368                }
1369                if col.primary_key {
1370                    sql.push_str(" PRIMARY KEY");
1371                }
1372                sql
1373            })
1374            .collect();
1375
1376        // ClickHouse 必须指定 Engine，默认使用 MergeTree
1377        format!(
1378            "CREATE TABLE {} ({}) ENGINE = MergeTree()",
1379            self.quote(table),
1380            cols.join(", ")
1381        )
1382    }
1383
1384    fn build_alter_table(&self, table: &str, changes: &[TableChange]) -> String {
1385        let stmts: Vec<String> = changes
1386            .iter()
1387            .map(|change| match change {
1388                TableChange::AddColumn(col) => {
1389                    let ch_type = map_to_clickhouse_type(&col.sql_type);
1390                    format!(
1391                        "ALTER TABLE {} ADD COLUMN {} {}",
1392                        self.quote(table),
1393                        self.quote(&col.name),
1394                        ch_type
1395                    )
1396                }
1397                TableChange::DropColumn(name) => {
1398                    format!(
1399                        "ALTER TABLE {} DROP COLUMN {}",
1400                        self.quote(table),
1401                        self.quote(name)
1402                    )
1403                }
1404                TableChange::ModifyColumn(col) => {
1405                    let ch_type = map_to_clickhouse_type(&col.sql_type);
1406                    format!(
1407                        "ALTER TABLE {} MODIFY COLUMN {} {}",
1408                        self.quote(table),
1409                        self.quote(&col.name),
1410                        ch_type
1411                    )
1412                }
1413                TableChange::AddIndex(name, cols) => {
1414                    format!(
1415                        "ALTER TABLE {} ADD INDEX {} ({})",
1416                        self.quote(table),
1417                        name,
1418                        cols.join(", ")
1419                    )
1420                }
1421                TableChange::DropIndex(name) => {
1422                    format!("ALTER TABLE {} DROP INDEX {}", self.quote(table), name)
1423                }
1424                TableChange::AddForeignKey { .. } => {
1425                    // ClickHouse 不支持外键，跳过
1426                    String::new()
1427                }
1428            })
1429            .filter(|s| !s.is_empty())
1430            .collect();
1431
1432        stmts.join("; ")
1433    }
1434}
1435
1436/// 将通用 SQL 类型映射为 ClickHouse 类型
1437///
1438/// - BIGINT → Int64
1439/// - INT/INTEGER → Int32
1440/// - VARCHAR(n)/TEXT → String
1441/// - BOOLEAN/BOOL → UInt8
1442/// - FLOAT → Float32
1443/// - DOUBLE → Float64
1444/// - DATETIME/TIMESTAMP → DateTime
1445fn map_to_clickhouse_type(sql_type: &str) -> String {
1446    let upper = sql_type.to_uppercase();
1447    let trimmed = upper.trim();
1448
1449    if trimmed.starts_with("BIGINT") {
1450        "Int64".to_string()
1451    } else if matches!(trimmed, "INT" | "INTEGER") {
1452        "Int32".to_string()
1453    } else if matches!(trimmed, "TINYINT" | "SMALLINT") {
1454        "Int16".to_string()
1455    } else if trimmed.starts_with("VARCHAR")
1456        || trimmed.starts_with("CHAR")
1457        || matches!(trimmed, "TEXT" | "MEDIUMTEXT" | "LONGTEXT" | "TINYTEXT")
1458    {
1459        "String".to_string()
1460    } else if matches!(trimmed, "BOOLEAN" | "BOOL") {
1461        "UInt8".to_string()
1462    } else if matches!(trimmed, "FLOAT" | "REAL") {
1463        "Float32".to_string()
1464    } else if matches!(trimmed, "DOUBLE" | "DOUBLE PRECISION") {
1465        "Float64".to_string()
1466    } else if matches!(trimmed, "DATETIME" | "TIMESTAMP") {
1467        "DateTime".to_string()
1468    } else if matches!(trimmed, "DATE") {
1469        "Date".to_string()
1470    } else if trimmed.starts_with("DECIMAL") || trimmed.starts_with("NUMERIC") {
1471        "Decimal(38, 4)".to_string()
1472    } else {
1473        sql_type.to_string()
1474    }
1475}
1476
1477// ============================================================================
1478// IBM DB2 方言（独立实现）
1479//
1480// DB2 LUW 语法特性：
1481// - 双引号标识符（标准 SQL 风格）
1482// - 字符串字面量单引号转义为 ''（标准 SQL 风格）
1483// - 支持 RETURNING（DB2 11.5+）
1484// - 分页使用 OFFSET x ROWS FETCH NEXT y ROWS ONLY（标准 SQL:2008）
1485// - 自增列使用 GENERATED ALWAYS AS IDENTITY
1486// - 类型系统：VARCHAR/INTEGER/BIGINT/TIMESTAMP/DECFLOAT 等
1487// ============================================================================
1488
1489/// IBM DB2 LUW 方言实现
1490pub struct Db2Dialect;
1491
1492impl Dialect for Db2Dialect {
1493    fn db_type(&self) -> DbType {
1494        DbType::Db2
1495    }
1496
1497    fn quote(&self, identifier: &str) -> String {
1498        // DB2 使用双引号包裹标识符（标准 SQL 风格）
1499        format!("\"{}\"", identifier.replace('"', "\"\""))
1500    }
1501
1502    fn escape_string(&self, s: &str) -> String {
1503        // DB2 标准转义：单引号双写（'O''Brien'）
1504        let mut escaped = String::with_capacity(s.len() * 2);
1505        for c in s.chars() {
1506            match c {
1507                '\'' => escaped.push_str("''"),
1508                _ => escaped.push(c),
1509            }
1510        }
1511        escaped
1512    }
1513
1514    fn supports_returning(&self) -> bool {
1515        // DB2 11.5+ 支持 RETURNING（实际使用 SELECT FROM FINAL TABLE 代替）
1516        false
1517    }
1518
1519    fn build_pagination(&self, sql: &str, page: u64, limit: u64) -> String {
1520        // DB2 使用 OFFSET ... ROWS FETCH NEXT ... ROWS ONLY（SQL:2008 标准）
1521        let offset = page.saturating_sub(1).saturating_mul(limit);
1522        format!(
1523            "{} OFFSET {} ROWS FETCH NEXT {} ROWS ONLY",
1524            sql, offset, limit
1525        )
1526    }
1527
1528    fn json_type(&self) -> &'static str {
1529        // DB2 11.5+ 原生支持 JSON 类型
1530        "JSON"
1531    }
1532
1533    fn json_extract(&self, column: &str, path: &str) -> String {
1534        // DB2 使用 JSON_VALUE 函数
1535        let normalized = if path.starts_with('$') {
1536            path.to_string()
1537        } else {
1538            format!("$.{}", path)
1539        };
1540        format!(
1541            "JSON_VALUE({}, '{}')",
1542            column,
1543            self.escape_string(&normalized)
1544        )
1545    }
1546
1547    fn full_text_search(&self, columns: &[&str], keyword: &str) -> String {
1548        // DB2 使用 CONTAINS 函数（需 DB2TEXT 索引）
1549        if columns.is_empty() {
1550            return "0".to_string();
1551        }
1552        let escaped = self.escape_string(keyword);
1553        let parts: Vec<String> = columns
1554            .iter()
1555            .map(|c| format!("CONTAINS({}, '{}') > 0", c, escaped))
1556            .collect();
1557        parts.join(" OR ")
1558    }
1559
1560    fn bool_to_int(&self, expr: &str) -> String {
1561        // DB2 没有原生 BOOL（11.5+ 有 BOOLEAN），使用 CASE WHEN 转换
1562        format!("(CASE WHEN {} THEN 1 ELSE 0 END)", expr)
1563    }
1564
1565    fn concat(&self, parts: &[&str]) -> String {
1566        // DB2 使用 || 操作符进行字符串拼接
1567        if parts.is_empty() {
1568            return "''".to_string();
1569        }
1570        parts.join(" || ")
1571    }
1572
1573    fn supports_if_exists(&self) -> bool {
1574        // DB2 不支持 DROP TABLE IF EXISTS（直到 11.5）
1575        false
1576    }
1577
1578    fn supports_if_not_exists(&self) -> bool {
1579        // DB2 不支持 CREATE TABLE IF NOT EXISTS
1580        false
1581    }
1582
1583    fn auto_increment_keyword(&self) -> &'static str {
1584        // DB2 使用 GENERATED ALWAYS AS IDENTITY
1585        "GENERATED ALWAYS AS IDENTITY"
1586    }
1587
1588    fn last_insert_id_sql(&self) -> Option<&'static str> {
1589        // DB2 使用 IDENTITY_VAL_LOCAL() 函数获取最后插入的 IDENTITY 值
1590        Some("SELECT IDENTITY_VAL_LOCAL() FROM SYSIBM.SYSDUMMY1")
1591    }
1592
1593    fn build_create_table(&self, table: &str, columns: &[ColumnDef]) -> String {
1594        let cols: Vec<String> = columns
1595            .iter()
1596            .map(|col| {
1597                let db2_type = map_to_db2_type(&col.sql_type);
1598                let mut sql = format!("{} {}", self.quote(&col.name), db2_type);
1599                if !col.nullable && !col.auto_increment {
1600                    sql.push_str(" NOT NULL");
1601                }
1602                if let Some(default) = &col.default {
1603                    sql.push_str(&format!(" DEFAULT {}", default));
1604                }
1605                if col.auto_increment {
1606                    sql.push_str(&format!(" {}", self.auto_increment_keyword()));
1607                }
1608                if col.primary_key {
1609                    sql.push_str(" PRIMARY KEY");
1610                }
1611                sql
1612            })
1613            .collect();
1614
1615        format!("CREATE TABLE {} ({})", self.quote(table), cols.join(", "))
1616    }
1617
1618    fn build_alter_table(&self, table: &str, changes: &[TableChange]) -> String {
1619        let stmts: Vec<String> = changes
1620            .iter()
1621            .map(|change| match change {
1622                TableChange::AddColumn(col) => {
1623                    let db2_type = map_to_db2_type(&col.sql_type);
1624                    let mut sql = format!(
1625                        "ALTER TABLE {} ADD COLUMN {} {}",
1626                        self.quote(table),
1627                        self.quote(&col.name),
1628                        db2_type
1629                    );
1630                    if !col.nullable {
1631                        sql.push_str(" NOT NULL");
1632                    }
1633                    if let Some(default) = &col.default {
1634                        sql.push_str(&format!(" DEFAULT {}", default));
1635                    }
1636                    sql
1637                }
1638                TableChange::DropColumn(name) => {
1639                    format!(
1640                        "ALTER TABLE {} DROP COLUMN {}",
1641                        self.quote(table),
1642                        self.quote(name)
1643                    )
1644                }
1645                TableChange::ModifyColumn(col) => {
1646                    let db2_type = map_to_db2_type(&col.sql_type);
1647                    format!(
1648                        "ALTER TABLE {} ALTER COLUMN {} SET DATA TYPE {}",
1649                        self.quote(table),
1650                        self.quote(&col.name),
1651                        db2_type
1652                    )
1653                }
1654                TableChange::AddIndex(name, cols) => {
1655                    format!(
1656                        "CREATE INDEX {} ON {} ({})",
1657                        name,
1658                        self.quote(table),
1659                        cols.join(", ")
1660                    )
1661                }
1662                TableChange::DropIndex(name) => {
1663                    format!("DROP INDEX {}", name)
1664                }
1665                TableChange::AddForeignKey {
1666                    columns,
1667                    reference_table,
1668                    reference_columns,
1669                } => {
1670                    format!(
1671                        "ALTER TABLE {} ADD CONSTRAINT fk_{}_{} FOREIGN KEY ({}) REFERENCES {} ({})",
1672                        self.quote(table),
1673                        table,
1674                        columns.join("_"),
1675                        columns.iter().map(|c| self.quote(c)).collect::<Vec<_>>().join(", "),
1676                        self.quote(reference_table),
1677                        reference_columns.iter().map(|c| self.quote(c)).collect::<Vec<_>>().join(", ")
1678                    )
1679                }
1680            })
1681            .collect();
1682
1683        stmts.join("; ")
1684    }
1685
1686    fn build_drop_table(&self, table: &str, if_exists: bool) -> String {
1687        // DB2 不支持 IF EXISTS，但为兼容性保留参数
1688        let _ = if_exists;
1689        format!("DROP TABLE {}", self.quote(table))
1690    }
1691}
1692
1693/// 将通用 SQL 类型映射为 IBM DB2 类型
1694///
1695/// - BIGINT → BIGINT
1696/// - INT/INTEGER → INTEGER
1697/// - VARCHAR(n) → VARCHAR(n)
1698/// - TEXT 系列 → CLOB(2G)
1699/// - BOOLEAN/BOOL → SMALLINT（DB2 11.5+ 才有 BOOLEAN）
1700/// - DATETIME/TIMESTAMP → TIMESTAMP
1701fn map_to_db2_type(sql_type: &str) -> String {
1702    let upper = sql_type.to_uppercase();
1703    let trimmed = upper.trim();
1704
1705    if trimmed.starts_with("BIGINT") {
1706        "BIGINT".to_string()
1707    } else if matches!(trimmed, "INT" | "INTEGER") {
1708        "INTEGER".to_string()
1709    } else if matches!(trimmed, "TINYINT" | "SMALLINT") {
1710        "SMALLINT".to_string()
1711    } else if trimmed.starts_with("VARCHAR") || trimmed.starts_with("CHAR") {
1712        sql_type.to_string()
1713    } else if matches!(trimmed, "TEXT" | "MEDIUMTEXT" | "LONGTEXT" | "TINYTEXT") {
1714        "CLOB(2G)".to_string()
1715    } else if matches!(trimmed, "BOOLEAN" | "BOOL") {
1716        "SMALLINT".to_string()
1717    } else if matches!(trimmed, "FLOAT" | "REAL") {
1718        "REAL".to_string()
1719    } else if matches!(trimmed, "DOUBLE" | "DOUBLE PRECISION") {
1720        "DOUBLE".to_string()
1721    } else if matches!(trimmed, "DATETIME" | "TIMESTAMP") {
1722        "TIMESTAMP".to_string()
1723    } else if matches!(trimmed, "DATE") {
1724        "DATE".to_string()
1725    } else {
1726        // DECIMAL/NUMERIC 和其他未匹配类型保持原样
1727        sql_type.to_string()
1728    }
1729}
1730
1731/// 根据数据库类型获取对应的方言实例
1732///
1733/// L-5 修复：补充示例文档
1734///
1735/// 返回 `Box<dyn Dialect>`，可用于与 `QueryBuilder`、`Schema` 等组件配合。
1736/// 对于不支持 SQL 方言的数据库类型（如 Redis、MongoDB、向量库），返回
1737/// `DbError::Unsupported`。
1738///
1739/// # 示例
1740///
1741/// ```ignore
1742/// use sz_orm_core::db_type::DbType;
1743/// use sz_orm_core::dialect::{get_dialect, Dialect};
1744///
1745/// let dialect = get_dialect(DbType::MySQL).unwrap();
1746/// assert_eq!(dialect.quote("user"), "`user`");
1747/// assert_eq!(dialect.db_type(), DbType::MySQL);
1748///
1749/// // 不支持的类型返回错误
1750/// let err = get_dialect(DbType::Redis).unwrap_err();
1751/// assert!(matches!(err, sz_orm_core::DbError::Unsupported(_)));
1752/// ```
1753pub fn get_dialect(db_type: DbType) -> Result<Box<dyn Dialect>, DbError> {
1754    match db_type {
1755        DbType::MySQL => Ok(Box::new(MySqlDialect)),
1756        DbType::PostgreSQL => Ok(Box::new(PostgreSqlDialect)),
1757        DbType::Sqlite => Ok(Box::new(SqliteDialect)),
1758        DbType::Redis => Err(DbError::Unsupported(
1759            "Redis does not support standard SQL dialect".to_string(),
1760        )),
1761        DbType::MongoDB => Err(DbError::Unsupported(
1762            "MongoDB uses different query syntax".to_string(),
1763        )),
1764        DbType::ClickHouse => Ok(Box::new(ClickHouseDialect)),
1765        DbType::Oracle => Ok(Box::new(OracleDialect)),
1766        DbType::OceanBase => Ok(Box::new(MySqlDialect)),
1767        DbType::SqlServer => Ok(Box::new(SqlServerDialect)),
1768        DbType::VectorDb => Err(DbError::Unsupported(
1769            "Vector databases have specific APIs".to_string(),
1770        )),
1771        DbType::PureJsDb => Err(DbError::Unsupported(
1772            "PureJS database uses JavaScript".to_string(),
1773        )),
1774        // 国产数据库 & 兼容方言
1775        DbType::Dameng => Ok(Box::new(DamengDialect)),
1776        DbType::Kingbase => Ok(Box::new(KingbaseDialect)),
1777        DbType::Db2 => Ok(Box::new(Db2Dialect)),
1778        DbType::MariaDB => Ok(Box::new(MariaDbDialect)),
1779        DbType::TiDB => Ok(Box::new(TiDbDialect)),
1780        DbType::PolarDB => Ok(Box::new(PolarDbDialect)),
1781        DbType::GaussDB => Ok(Box::new(GaussDbDialect)),
1782        DbType::GBase => Ok(Box::new(GBaseDialect)),
1783        DbType::Sybase => Ok(Box::new(SybaseDialect)),
1784    }
1785}
1786
1787impl fmt::Display for dyn Dialect {
1788    fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result {
1789        write!(f, "Dialect({})", self.db_type())
1790    }
1791}
1792
1793#[cfg(test)]
1794mod tests {
1795    use super::*;
1796
1797    #[test]
1798    fn test_mysql_quote() {
1799        let dialect = MySqlDialect;
1800        assert_eq!(dialect.quote("users"), "`users`");
1801        assert_eq!(dialect.quote("user`id"), "`user``id`");
1802    }
1803
1804    #[test]
1805    fn test_mysql_escape() {
1806        let dialect = MySqlDialect;
1807        assert_eq!(dialect.escape_string("hello"), "hello");
1808        assert_eq!(dialect.escape_string("it's"), "it\\'s");
1809        assert_eq!(dialect.escape_string("line\nbreak"), "line\\nbreak");
1810    }
1811
1812    #[test]
1813    fn test_mysql_pagination() {
1814        let dialect = MySqlDialect;
1815        let sql = dialect.build_pagination("SELECT * FROM users", 2, 10);
1816        assert_eq!(sql, "SELECT * FROM users LIMIT 10 OFFSET 10");
1817    }
1818
1819    #[test]
1820    fn test_postgres_quote() {
1821        let dialect = PostgreSqlDialect;
1822        assert_eq!(dialect.quote("users"), "\"users\"");
1823        assert_eq!(dialect.quote("user\"id"), "\"user\"\"id\"");
1824    }
1825
1826    #[test]
1827    fn test_postgres_pagination() {
1828        let dialect = PostgreSqlDialect;
1829        let sql = dialect.build_pagination("SELECT * FROM users", 3, 20);
1830        assert_eq!(sql, "SELECT * FROM users LIMIT 20 OFFSET 40");
1831    }
1832
1833    #[test]
1834    fn test_postgres_returning() {
1835        let dialect = PostgreSqlDialect;
1836        assert!(dialect.supports_returning());
1837    }
1838
1839    #[test]
1840    fn test_sqlite_quote() {
1841        let dialect = SqliteDialect;
1842        assert_eq!(dialect.quote("users"), "\"users\"");
1843        assert_eq!(dialect.quote("user\"id"), "\"user\"\"id\"");
1844    }
1845
1846    #[test]
1847    fn test_sqlite_escape() {
1848        let dialect = SqliteDialect;
1849        assert_eq!(dialect.escape_string("hello"), "hello");
1850        assert_eq!(dialect.escape_string("it's"), "it''s");
1851    }
1852
1853    #[test]
1854    fn test_get_dialect() {
1855        let dialect = get_dialect(DbType::MySQL);
1856        assert!(dialect.is_ok());
1857
1858        let dialect = get_dialect(DbType::Redis);
1859        assert!(dialect.is_err());
1860    }
1861
1862    #[test]
1863    fn test_bool_to_int() {
1864        let mysql = MySqlDialect;
1865        assert_eq!(mysql.bool_to_int("active"), "IF(active, 1, 0)");
1866
1867        let pg = PostgreSqlDialect;
1868        assert_eq!(
1869            pg.bool_to_int("active"),
1870            "(CASE WHEN active THEN 1 ELSE 0 END)"
1871        );
1872    }
1873
1874    #[test]
1875    fn test_json_extract_with_path() {
1876        let mysql = MySqlDialect;
1877        let sql = mysql.json_extract("data", "$.user.name");
1878        assert!(sql.contains("$.user.name"));
1879        assert!(sql.contains("JSON_EXTRACT"));
1880
1881        let pg = PostgreSqlDialect;
1882        let sql = pg.json_extract("data", "user.name");
1883        assert!(sql.contains("#>>"));
1884
1885        let sqlite = SqliteDialect;
1886        let sql = sqlite.json_extract("data", "$.user.name");
1887        assert!(sql.contains("$.user.name"));
1888        assert!(sql.contains("json_extract"));
1889    }
1890
1891    #[test]
1892    fn test_sqlite_full_text_search() {
1893        let sqlite = SqliteDialect;
1894        let sql = sqlite.full_text_search(&["title", "content"], "hello");
1895        // SQLite 接口只传入列名，无 FTS 表名，因此降级使用 LIKE
1896        assert!(sql.contains("LIKE"));
1897        assert!(sql.contains("title LIKE '%hello%'"));
1898        assert!(sql.contains("content LIKE '%hello%'"));
1899        assert!(sql.contains(" OR "));
1900
1901        // 空列列表返回 "0"（短路避免空 IN/OR）
1902        assert_eq!(sqlite.full_text_search(&[], "hello"), "0");
1903
1904        // 含单引号的关键字需正确转义（SQLite 双单引号）
1905        let sql = sqlite.full_text_search(&["title"], "it's");
1906        assert!(sql.contains("title LIKE '%it''s%'"));
1907    }
1908
1909    #[test]
1910    fn test_alter_table_modify_column() {
1911        let mysql = MySqlDialect;
1912        let col = ColumnDef {
1913            name: "name".to_string(),
1914            sql_type: "VARCHAR(255)".to_string(),
1915            nullable: false,
1916            default: None,
1917            auto_increment: false,
1918            primary_key: false,
1919        };
1920        let sql = mysql.build_alter_table("users", &[TableChange::ModifyColumn(col)]);
1921        assert!(sql.contains("MODIFY COLUMN"));
1922
1923        let pg = PostgreSqlDialect;
1924        let col = ColumnDef {
1925            name: "name".to_string(),
1926            sql_type: "VARCHAR(255)".to_string(),
1927            nullable: false,
1928            default: None,
1929            auto_increment: false,
1930            primary_key: false,
1931        };
1932        let sql = pg.build_alter_table("users", &[TableChange::ModifyColumn(col)]);
1933        assert!(sql.contains("ALTER COLUMN"));
1934        assert!(sql.contains("TYPE"));
1935    }
1936
1937    #[test]
1938    fn test_alter_table_add_foreign_key() {
1939        let mysql = MySqlDialect;
1940        let sql = mysql.build_alter_table(
1941            "orders",
1942            &[TableChange::AddForeignKey {
1943                columns: vec!["user_id".to_string()],
1944                reference_table: "users".to_string(),
1945                reference_columns: vec!["id".to_string()],
1946            }],
1947        );
1948        assert!(sql.contains("FOREIGN KEY"));
1949        assert!(sql.contains("REFERENCES"));
1950
1951        let sqlite = SqliteDialect;
1952        let sql = sqlite.build_alter_table(
1953            "orders",
1954            &[TableChange::AddForeignKey {
1955                columns: vec!["user_id".to_string()],
1956                reference_table: "users".to_string(),
1957                reference_columns: vec!["id".to_string()],
1958            }],
1959        );
1960        // SQLite 不支持，应返回注释
1961        assert!(sql.starts_with("--"));
1962    }
1963
1964    #[test]
1965    fn test_sqlite_alter_table_add_column() {
1966        let sqlite = SqliteDialect;
1967        let col = ColumnDef {
1968            name: "email".to_string(),
1969            sql_type: "TEXT".to_string(),
1970            nullable: true,
1971            default: None,
1972            auto_increment: false,
1973            primary_key: false,
1974        };
1975        let sql = sqlite.build_alter_table("users", &[TableChange::AddColumn(col)]);
1976        assert!(sql.contains("ADD COLUMN"));
1977        assert!(sql.contains("email"));
1978    }
1979
1980    // ===================== Oracle 方言测试 =====================
1981
1982    #[test]
1983    fn test_oracle_quote_and_escape() {
1984        let dialect = OracleDialect;
1985        // 标识符使用双引号包裹，内部双引号双写
1986        assert_eq!(dialect.quote("users"), "\"users\"");
1987        assert_eq!(dialect.quote("user\"id"), "\"user\"\"id\"");
1988        assert_eq!(dialect.quote("column_name"), "\"column_name\"");
1989
1990        // 字符串字面量转义：单引号双写，反斜杠不转义
1991        assert_eq!(dialect.escape_string("hello"), "hello");
1992        assert_eq!(dialect.escape_string("it's"), "it''s");
1993        assert_eq!(dialect.escape_string("O'Brien"), "O''Brien");
1994        assert_eq!(dialect.escape_string("a'b'c"), "a''b''c");
1995        // 反斜杠按原样保留（Oracle 标准行为）
1996        assert_eq!(dialect.escape_string("path\\to"), "path\\to");
1997    }
1998
1999    #[test]
2000    fn test_oracle_pagination() {
2001        let dialect = OracleDialect;
2002        // page=1 为第一页（offset=0），与其他方言保持一致
2003        let sql = dialect.build_pagination("SELECT * FROM users", 1, 10);
2004        assert_eq!(
2005            sql,
2006            "SELECT * FROM users OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY"
2007        );
2008        // page=3, limit=20 → offset=40
2009        let sql = dialect.build_pagination("SELECT * FROM users", 3, 20);
2010        assert_eq!(
2011            sql,
2012            "SELECT * FROM users OFFSET 40 ROWS FETCH NEXT 20 ROWS ONLY"
2013        );
2014        // page=0（边界）→ offset=0
2015        let sql = dialect.build_pagination("SELECT * FROM users", 0, 10);
2016        assert_eq!(
2017            sql,
2018            "SELECT * FROM users OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY"
2019        );
2020    }
2021
2022    #[test]
2023    fn test_oracle_json_extract() {
2024        let dialect = OracleDialect;
2025        // 标准 $.path 格式
2026        let sql = dialect.json_extract("data", "$.user.name");
2027        assert!(sql.contains("JSON_VALUE"));
2028        assert!(sql.contains("$.user.name"));
2029        assert!(sql.starts_with("JSON_VALUE(data, '$.user.name')"));
2030
2031        // 自动补全 $. 前缀
2032        let sql = dialect.json_extract("data", "user.name");
2033        assert!(sql.contains("$.user.name"));
2034        assert!(sql.contains("JSON_VALUE"));
2035
2036        // 单引号转义
2037        let sql = dialect.json_extract("data", "$.key's");
2038        assert!(sql.contains("$.key''s"));
2039    }
2040
2041    #[test]
2042    fn test_oracle_create_table() {
2043        let dialect = OracleDialect;
2044        let columns = vec![
2045            ColumnDef {
2046                name: "id".to_string(),
2047                sql_type: "BIGINT".to_string(),
2048                nullable: false,
2049                default: None,
2050                auto_increment: true,
2051                primary_key: true,
2052            },
2053            ColumnDef {
2054                name: "name".to_string(),
2055                sql_type: "VARCHAR(255)".to_string(),
2056                nullable: false,
2057                default: None,
2058                auto_increment: false,
2059                primary_key: false,
2060            },
2061            ColumnDef {
2062                name: "bio".to_string(),
2063                sql_type: "TEXT".to_string(),
2064                nullable: true,
2065                default: None,
2066                auto_increment: false,
2067                primary_key: false,
2068            },
2069            ColumnDef {
2070                name: "is_active".to_string(),
2071                sql_type: "BOOLEAN".to_string(),
2072                nullable: false,
2073                default: Some("1".to_string()),
2074                auto_increment: false,
2075                primary_key: false,
2076            },
2077        ];
2078        let sql = dialect.build_create_table("users", &columns);
2079        // Oracle 类型映射
2080        assert!(
2081            sql.contains("NUMBER(19)"),
2082            "BIGINT should map to NUMBER(19): {}",
2083            sql
2084        );
2085        assert!(
2086            sql.contains("VARCHAR2(255)"),
2087            "VARCHAR should map to VARCHAR2: {}",
2088            sql
2089        );
2090        assert!(sql.contains("CLOB"), "TEXT should map to CLOB: {}", sql);
2091        assert!(
2092            sql.contains("NUMBER(1)"),
2093            "BOOLEAN should map to NUMBER(1): {}",
2094            sql
2095        );
2096        // 自增与主键
2097        assert!(sql.contains("GENERATED BY DEFAULT AS IDENTITY"));
2098        assert!(sql.contains("PRIMARY KEY"));
2099        assert!(sql.contains("NOT NULL"));
2100        assert!(sql.contains("DEFAULT 1"));
2101        // 标识符使用双引号
2102        assert!(sql.contains("\"users\""));
2103        assert!(sql.contains("\"id\""));
2104    }
2105
2106    #[test]
2107    fn test_oracle_bool_to_int_and_concat() {
2108        let dialect = OracleDialect;
2109        // bool_to_int 使用 CASE WHEN
2110        assert_eq!(
2111            dialect.bool_to_int("active"),
2112            "(CASE WHEN active THEN 1 ELSE 0 END)"
2113        );
2114        assert_eq!(
2115            dialect.bool_to_int("x > 0"),
2116            "(CASE WHEN x > 0 THEN 1 ELSE 0 END)"
2117        );
2118        // concat 使用 || 操作符
2119        assert_eq!(dialect.concat(&["a", "b", "c"]), "a || b || c");
2120        assert_eq!(
2121            dialect.concat(&["first_name", "last_name"]),
2122            "first_name || last_name"
2123        );
2124        // 空列表返回 NULL
2125        assert_eq!(dialect.concat(&[]), "NULL");
2126    }
2127
2128    #[test]
2129    fn test_oracle_misc_dialect_methods() {
2130        let dialect = OracleDialect;
2131        // db_type
2132        assert_eq!(dialect.db_type(), DbType::Oracle);
2133        // supports_returning: Oracle 12c+ 支持
2134        assert!(dialect.supports_returning());
2135        // supports_if_exists / supports_if_not_exists: Oracle 23ai 支持
2136        assert!(dialect.supports_if_exists());
2137        assert!(dialect.supports_if_not_exists());
2138        // auto_increment_keyword
2139        assert_eq!(
2140            dialect.auto_increment_keyword(),
2141            "GENERATED BY DEFAULT AS IDENTITY"
2142        );
2143        // last_insert_id_sql: Oracle 不支持独立可执行的获取最后插入 ID 语句
2144        // RETURNING ... INTO 是 PL/SQL 子句，必须附加在 INSERT 之后
2145        assert_eq!(dialect.last_insert_id_sql(), None);
2146        // json_type
2147        assert_eq!(dialect.json_type(), "JSON");
2148    }
2149
2150    #[test]
2151    fn test_oracle_get_dialect() {
2152        // get_dialect 应返回 OracleDialect 而非 MySqlDialect
2153        let dialect = get_dialect(DbType::Oracle);
2154        assert!(dialect.is_ok(), "Oracle dialect should be available");
2155        let dialect = dialect.unwrap();
2156        assert_eq!(dialect.db_type(), DbType::Oracle);
2157        // 验证不是 MySqlDialect 的回退：Oracle 使用双引号，MySQL 使用反引号
2158        assert_eq!(dialect.quote("users"), "\"users\"");
2159        // 验证 Oracle 特有功能
2160        assert!(dialect.supports_returning());
2161        // Oracle 没有 standalone 的 last_insert_id SQL
2162        assert_eq!(dialect.last_insert_id_sql(), None);
2163    }
2164
2165    #[test]
2166    fn test_oracle_drop_table() {
2167        let dialect = OracleDialect;
2168        // IF EXISTS
2169        let sql = dialect.build_drop_table("users", true);
2170        assert_eq!(sql, "DROP TABLE IF EXISTS \"users\"");
2171        // 不带 IF EXISTS
2172        let sql = dialect.build_drop_table("users", false);
2173        assert_eq!(sql, "DROP TABLE \"users\"");
2174    }
2175
2176    #[test]
2177    fn test_oracle_alter_table() {
2178        let dialect = OracleDialect;
2179        // MODIFY COLUMN: Oracle 使用 MODIFY（不带 COLUMN 关键字）
2180        let col = ColumnDef {
2181            name: "name".to_string(),
2182            sql_type: "VARCHAR(255)".to_string(),
2183            nullable: false,
2184            default: None,
2185            auto_increment: false,
2186            primary_key: false,
2187        };
2188        let sql = dialect.build_alter_table("users", &[TableChange::ModifyColumn(col)]);
2189        assert!(sql.contains("MODIFY"));
2190        assert!(sql.contains("VARCHAR2(255)"));
2191        assert!(!sql.contains("MODIFY COLUMN")); // Oracle 不使用 COLUMN 关键字
2192
2193        // ADD COLUMN
2194        let col = ColumnDef {
2195            name: "email".to_string(),
2196            sql_type: "VARCHAR(255)".to_string(),
2197            nullable: true,
2198            default: None,
2199            auto_increment: false,
2200            primary_key: false,
2201        };
2202        let sql = dialect.build_alter_table("users", &[TableChange::AddColumn(col)]);
2203        assert!(sql.contains("ADD \"email\""));
2204        assert!(sql.contains("VARCHAR2(255)"));
2205
2206        // DROP COLUMN
2207        let sql =
2208            dialect.build_alter_table("users", &[TableChange::DropColumn("email".to_string())]);
2209        assert!(sql.contains("DROP COLUMN"));
2210        assert!(sql.contains("\"email\""));
2211    }
2212
2213    // ===================== SQLite concat 修复测试 =====================
2214
2215    #[test]
2216    fn test_sqlite_concat_handles_null() {
2217        let sqlite = SqliteDialect;
2218        // 修复前：COALESCE(a || b) 在 a 为 NULL 时整体已 NULL，COALESCE 失效
2219        // 修复后：每个参数 COALESCE 为空串，保证拼接结果非 NULL
2220        let sql = sqlite.concat(&["a", "b"]);
2221        assert_eq!(sql, "COALESCE(a, '') || COALESCE(b, '')");
2222        // 单参数
2223        let sql = sqlite.concat(&["a"]);
2224        assert_eq!(sql, "COALESCE(a, '')");
2225        // 空列表
2226        assert_eq!(sqlite.concat(&[]), "NULL");
2227    }
2228
2229    // ===================== SQL Server 方言测试 =====================
2230
2231    #[test]
2232    fn test_sqlserver_quote_and_escape() {
2233        let dialect = SqlServerDialect;
2234        // 标识符使用 [name] 包裹，内部 ] 双写
2235        assert_eq!(dialect.quote("users"), "[users]");
2236        assert_eq!(dialect.quote("col]name"), "[col]]name]");
2237        // 字符串字面量：单引号双写，反斜杠不转义
2238        assert_eq!(dialect.escape_string("hello"), "hello");
2239        assert_eq!(dialect.escape_string("it's"), "it''s");
2240        assert_eq!(dialect.escape_string("O'Brien"), "O''Brien");
2241        assert_eq!(dialect.escape_string("path\\to"), "path\\to");
2242    }
2243
2244    #[test]
2245    fn test_sqlserver_pagination() {
2246        let dialect = SqlServerDialect;
2247        // SQL Server 2012+ 使用 OFFSET ... ROWS FETCH NEXT ... ROWS ONLY
2248        let sql = dialect.build_pagination("SELECT * FROM users", 1, 10);
2249        assert_eq!(
2250            sql,
2251            "SELECT * FROM users OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY"
2252        );
2253        let sql = dialect.build_pagination("SELECT * FROM users", 3, 20);
2254        assert_eq!(
2255            sql,
2256            "SELECT * FROM users OFFSET 40 ROWS FETCH NEXT 20 ROWS ONLY"
2257        );
2258        // page=0 边界
2259        let sql = dialect.build_pagination("SELECT * FROM users", 0, 10);
2260        assert_eq!(
2261            sql,
2262            "SELECT * FROM users OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY"
2263        );
2264    }
2265
2266    #[test]
2267    fn test_sqlserver_misc_dialect_methods() {
2268        let dialect = SqlServerDialect;
2269        assert_eq!(dialect.db_type(), DbType::SqlServer);
2270        // supports_returning: SQL Server 使用 OUTPUT，语义等价
2271        assert!(dialect.supports_returning());
2272        // supports_if_exists / supports_if_not_exists: SQL Server 2016+
2273        assert!(dialect.supports_if_exists());
2274        assert!(dialect.supports_if_not_exists());
2275        // auto_increment_keyword
2276        assert_eq!(dialect.auto_increment_keyword(), "IDENTITY(1,1)");
2277        // last_insert_id_sql
2278        assert_eq!(dialect.last_insert_id_sql(), Some("SCOPE_IDENTITY()"));
2279        // json_type
2280        assert_eq!(dialect.json_type(), "NVARCHAR(MAX)");
2281    }
2282
2283    #[test]
2284    fn test_sqlserver_json_extract() {
2285        let dialect = SqlServerDialect;
2286        let sql = dialect.json_extract("data", "$.user.name");
2287        assert!(sql.starts_with("JSON_VALUE(data, '$.user.name')"));
2288        // 自动补 $. 前缀
2289        let sql = dialect.json_extract("data", "user.name");
2290        assert!(sql.contains("$.user.name"));
2291        assert!(sql.contains("JSON_VALUE"));
2292        // 单引号转义
2293        let sql = dialect.json_extract("data", "$.key's");
2294        assert!(sql.contains("$.key''s"));
2295    }
2296
2297    #[test]
2298    fn test_sqlserver_full_text_search() {
2299        let dialect = SqlServerDialect;
2300        let sql = dialect.full_text_search(&["title", "content"], "hello");
2301        assert!(sql.starts_with("CONTAINS(title, content, 'hello')"));
2302        // 空列列表
2303        assert_eq!(dialect.full_text_search(&[], "hello"), "0");
2304        // 单引号转义
2305        let sql = dialect.full_text_search(&["title"], "it's");
2306        assert!(sql.contains("it''s"));
2307    }
2308
2309    #[test]
2310    fn test_sqlserver_bool_to_int_and_concat() {
2311        let dialect = SqlServerDialect;
2312        assert_eq!(
2313            dialect.bool_to_int("active"),
2314            "(CASE WHEN active THEN 1 ELSE 0 END)"
2315        );
2316        assert_eq!(dialect.concat(&["a", "b", "c"]), "CONCAT(a, b, c)");
2317        assert_eq!(dialect.concat(&[]), "NULL");
2318    }
2319
2320    #[test]
2321    fn test_sqlserver_create_table() {
2322        let dialect = SqlServerDialect;
2323        let columns = vec![
2324            ColumnDef {
2325                name: "id".to_string(),
2326                sql_type: "BIGINT".to_string(),
2327                nullable: false,
2328                default: None,
2329                auto_increment: true,
2330                primary_key: true,
2331            },
2332            ColumnDef {
2333                name: "name".to_string(),
2334                sql_type: "VARCHAR(255)".to_string(),
2335                nullable: false,
2336                default: None,
2337                auto_increment: false,
2338                primary_key: false,
2339            },
2340            ColumnDef {
2341                name: "bio".to_string(),
2342                sql_type: "TEXT".to_string(),
2343                nullable: true,
2344                default: None,
2345                auto_increment: false,
2346                primary_key: false,
2347            },
2348            ColumnDef {
2349                name: "is_active".to_string(),
2350                sql_type: "BOOLEAN".to_string(),
2351                nullable: false,
2352                default: Some("1".to_string()),
2353                auto_increment: false,
2354                primary_key: false,
2355            },
2356        ];
2357        let sql = dialect.build_create_table("users", &columns);
2358        // 标识符使用方括号
2359        assert!(sql.contains("[users]"));
2360        assert!(sql.contains("[id]"));
2361        // 类型映射
2362        assert!(sql.contains("IDENTITY(1,1)"));
2363        assert!(
2364            sql.contains("NVARCHAR(255)"),
2365            "VARCHAR should map to NVARCHAR: {}",
2366            sql
2367        );
2368        assert!(
2369            sql.contains("NVARCHAR(MAX)"),
2370            "TEXT should map to NVARCHAR(MAX): {}",
2371            sql
2372        );
2373        assert!(sql.contains("BIT"), "BOOLEAN should map to BIT: {}", sql);
2374        assert!(sql.contains("PRIMARY KEY"));
2375        assert!(sql.contains("NOT NULL"));
2376        assert!(sql.contains("DEFAULT 1"));
2377    }
2378
2379    #[test]
2380    fn test_sqlserver_drop_table() {
2381        let dialect = SqlServerDialect;
2382        assert_eq!(
2383            dialect.build_drop_table("users", true),
2384            "DROP TABLE IF EXISTS [users]"
2385        );
2386        assert_eq!(
2387            dialect.build_drop_table("users", false),
2388            "DROP TABLE [users]"
2389        );
2390    }
2391
2392    #[test]
2393    fn test_sqlserver_alter_table() {
2394        let dialect = SqlServerDialect;
2395        // MODIFY COLUMN: SQL Server 使用 ALTER COLUMN（不是 MODIFY）
2396        let col = ColumnDef {
2397            name: "name".to_string(),
2398            sql_type: "VARCHAR(255)".to_string(),
2399            nullable: false,
2400            default: None,
2401            auto_increment: false,
2402            primary_key: false,
2403        };
2404        let sql = dialect.build_alter_table("users", &[TableChange::ModifyColumn(col)]);
2405        assert!(sql.contains("ALTER COLUMN"));
2406        assert!(sql.contains("NVARCHAR(255)"));
2407        assert!(!sql.contains("MODIFY"));
2408
2409        // ADD COLUMN
2410        let col = ColumnDef {
2411            name: "email".to_string(),
2412            sql_type: "VARCHAR(255)".to_string(),
2413            nullable: true,
2414            default: None,
2415            auto_increment: false,
2416            primary_key: false,
2417        };
2418        let sql = dialect.build_alter_table("users", &[TableChange::AddColumn(col)]);
2419        assert!(sql.contains("ADD [email]"));
2420        assert!(sql.contains("NVARCHAR(255)"));
2421
2422        // DROP COLUMN
2423        let sql =
2424            dialect.build_alter_table("users", &[TableChange::DropColumn("email".to_string())]);
2425        assert!(sql.contains("DROP COLUMN"));
2426        assert!(sql.contains("[email]"));
2427
2428        // DROP INDEX 必须指定表名
2429        let sql =
2430            dialect.build_alter_table("users", &[TableChange::DropIndex("idx_name".to_string())]);
2431        assert!(sql.contains("DROP INDEX idx_name ON [users]"));
2432    }
2433
2434    #[test]
2435    fn test_sqlserver_get_dialect() {
2436        // get_dialect 应返回 SqlServerDialect，而不是 MySqlDialect 回退
2437        let dialect = get_dialect(DbType::SqlServer);
2438        assert!(dialect.is_ok(), "SqlServer dialect should be available");
2439        let dialect = dialect.unwrap();
2440        assert_eq!(dialect.db_type(), DbType::SqlServer);
2441        // 验证不是 MySqlDialect 的回退：SQL Server 使用方括号，MySQL 使用反引号
2442        assert_eq!(dialect.quote("users"), "[users]");
2443        // SQL Server 特有功能
2444        assert_eq!(dialect.last_insert_id_sql(), Some("SCOPE_IDENTITY()"));
2445        assert_eq!(dialect.auto_increment_keyword(), "IDENTITY(1,1)");
2446    }
2447
2448    #[test]
2449    fn test_clickhouse_get_dialect_unsupported() {
2450        // ClickHouse 现已支持独立方言（不再回退到 MySqlDialect）
2451        let dialect = get_dialect(DbType::ClickHouse);
2452        assert!(dialect.is_ok(), "ClickHouse should be supported");
2453        let dialect = dialect.unwrap();
2454        assert_eq!(dialect.db_type(), DbType::ClickHouse);
2455        // ClickHouse 使用 backquote 标识符（与 MySQL 一致）
2456        assert_eq!(dialect.quote("users"), "`users`");
2457        // ClickHouse 不支持 RETURNING
2458        assert!(!dialect.supports_returning());
2459        // ClickHouse 使用 LIMIT offset, limit 分页
2460        let sql = dialect.build_pagination("SELECT * FROM t", 2, 10);
2461        assert_eq!(sql, "SELECT * FROM t LIMIT 10, 10");
2462        // ClickHouse 自增列为空字符串
2463        assert_eq!(dialect.auto_increment_keyword(), "");
2464    }
2465
2466    #[test]
2467    fn test_get_dialect_all_supported_types() {
2468        // 所有声称为支持的方言应正确返回，不支持的应返回 Err
2469        assert!(get_dialect(DbType::MySQL).is_ok());
2470        assert!(get_dialect(DbType::PostgreSQL).is_ok());
2471        assert!(get_dialect(DbType::Sqlite).is_ok());
2472        assert!(get_dialect(DbType::Oracle).is_ok());
2473        assert!(get_dialect(DbType::SqlServer).is_ok());
2474        assert!(get_dialect(DbType::OceanBase).is_ok());
2475        assert!(get_dialect(DbType::ClickHouse).is_ok());
2476        // 国产数据库 & 兼容方言
2477        assert!(get_dialect(DbType::Dameng).is_ok());
2478        assert!(get_dialect(DbType::Kingbase).is_ok());
2479        assert!(get_dialect(DbType::Db2).is_ok());
2480        assert!(get_dialect(DbType::MariaDB).is_ok());
2481        assert!(get_dialect(DbType::TiDB).is_ok());
2482        assert!(get_dialect(DbType::PolarDB).is_ok());
2483        assert!(get_dialect(DbType::GaussDB).is_ok());
2484        assert!(get_dialect(DbType::GBase).is_ok());
2485        assert!(get_dialect(DbType::Sybase).is_ok());
2486        // 不支持标准 SQL 的
2487        assert!(get_dialect(DbType::Redis).is_err());
2488        assert!(get_dialect(DbType::MongoDB).is_err());
2489        assert!(get_dialect(DbType::VectorDb).is_err());
2490        assert!(get_dialect(DbType::PureJsDb).is_err());
2491    }
2492
2493    // ===== 国产数据库兼容方言测试 =====
2494
2495    #[test]
2496    fn test_mariadb_dialect() {
2497        let dialect = get_dialect(DbType::MariaDB).unwrap();
2498        assert_eq!(dialect.db_type(), DbType::MariaDB);
2499        // MariaDB 兼容 MySQL 语法：backquote 标识符 + 反斜杠转义
2500        assert_eq!(dialect.quote("users"), "`users`");
2501        assert_eq!(dialect.escape_string("it's"), "it\\'s");
2502        assert_eq!(dialect.auto_increment_keyword(), "AUTO_INCREMENT");
2503        // MySQL 家族不支持 RETURNING（MySQL 协议层限制）
2504        assert!(!dialect.supports_returning());
2505    }
2506
2507    #[test]
2508    fn test_tidb_dialect() {
2509        let dialect = get_dialect(DbType::TiDB).unwrap();
2510        assert_eq!(dialect.db_type(), DbType::TiDB);
2511        // TiDB 兼容 MySQL 语法
2512        assert_eq!(dialect.quote("users"), "`users`");
2513        assert_eq!(dialect.escape_string("it's"), "it\\'s");
2514        assert_eq!(dialect.auto_increment_keyword(), "AUTO_INCREMENT");
2515    }
2516
2517    #[test]
2518    fn test_dameng_dialect() {
2519        let dialect = get_dialect(DbType::Dameng).unwrap();
2520        assert_eq!(dialect.db_type(), DbType::Dameng);
2521        // 达梦兼容 Oracle 语法：双引号标识符 + 单引号双写
2522        assert_eq!(dialect.quote("users"), "\"users\"");
2523        assert_eq!(dialect.escape_string("it's"), "it''s");
2524        // Oracle 兼容：使用 IDENTITY 列
2525        assert_eq!(
2526            dialect.auto_increment_keyword(),
2527            "GENERATED BY DEFAULT AS IDENTITY"
2528        );
2529        // Oracle 兼容：支持 RETURNING
2530        assert!(dialect.supports_returning());
2531    }
2532
2533    #[test]
2534    fn test_kingbase_dialect() {
2535        let dialect = get_dialect(DbType::Kingbase).unwrap();
2536        assert_eq!(dialect.db_type(), DbType::Kingbase);
2537        // 人大金仓兼容 PostgreSQL 语法：双引号标识符 + 单引号双写
2538        assert_eq!(dialect.quote("users"), "\"users\"");
2539        assert_eq!(dialect.escape_string("it's"), "it''s");
2540        // PG 兼容：支持 RETURNING
2541        assert!(dialect.supports_returning());
2542        // PG 兼容：使用 GENERATED BY DEFAULT AS IDENTITY（PG 10+ 标准）
2543        assert_eq!(
2544            dialect.auto_increment_keyword(),
2545            "GENERATED BY DEFAULT AS IDENTITY"
2546        );
2547    }
2548
2549    #[test]
2550    fn test_polardb_dialect() {
2551        let dialect = get_dialect(DbType::PolarDB).unwrap();
2552        assert_eq!(dialect.db_type(), DbType::PolarDB);
2553        // PolarDB-PG 兼容 PostgreSQL
2554        assert_eq!(dialect.quote("users"), "\"users\"");
2555        assert!(dialect.supports_returning());
2556    }
2557
2558    #[test]
2559    fn test_gaussdb_dialect() {
2560        let dialect = get_dialect(DbType::GaussDB).unwrap();
2561        assert_eq!(dialect.db_type(), DbType::GaussDB);
2562        // GaussDB 兼容 PostgreSQL
2563        assert_eq!(dialect.quote("users"), "\"users\"");
2564        assert!(dialect.supports_returning());
2565    }
2566
2567    #[test]
2568    fn test_gbase_dialect() {
2569        let dialect = get_dialect(DbType::GBase).unwrap();
2570        assert_eq!(dialect.db_type(), DbType::GBase);
2571        // GBase 8s 兼容 T-SQL（使用方括号）
2572        assert_eq!(dialect.quote("users"), "[users]");
2573    }
2574
2575    #[test]
2576    fn test_sybase_dialect() {
2577        let dialect = get_dialect(DbType::Sybase).unwrap();
2578        assert_eq!(dialect.db_type(), DbType::Sybase);
2579        // Sybase ASE 兼容 T-SQL
2580        assert_eq!(dialect.quote("users"), "[users]");
2581    }
2582
2583    // ===== DB2 独立方言测试 =====
2584
2585    #[test]
2586    fn test_db2_dialect_basic() {
2587        let dialect = get_dialect(DbType::Db2).unwrap();
2588        assert_eq!(dialect.db_type(), DbType::Db2);
2589        // DB2 使用双引号标识符
2590        assert_eq!(dialect.quote("users"), "\"users\"");
2591        // DB2 单引号双写
2592        assert_eq!(dialect.escape_string("it's"), "it''s");
2593        // DB2 使用 IDENTITY
2594        assert_eq!(
2595            dialect.auto_increment_keyword(),
2596            "GENERATED ALWAYS AS IDENTITY"
2597        );
2598        // DB2 不支持 IF EXISTS / IF NOT EXISTS（11.5 之前）
2599        assert!(!dialect.supports_if_exists());
2600        assert!(!dialect.supports_if_not_exists());
2601        // DB2 不支持 RETURNING（使用 SELECT FROM FINAL TABLE 代替）
2602        assert!(!dialect.supports_returning());
2603    }
2604
2605    #[test]
2606    fn test_db2_pagination() {
2607        let dialect = Db2Dialect;
2608        // DB2 使用 OFFSET x ROWS FETCH NEXT y ROWS ONLY
2609        let sql = dialect.build_pagination("SELECT * FROM users", 2, 10);
2610        assert_eq!(
2611            sql,
2612            "SELECT * FROM users OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY"
2613        );
2614    }
2615
2616    #[test]
2617    fn test_db2_last_insert_id() {
2618        let dialect = Db2Dialect;
2619        // DB2 使用 IDENTITY_VAL_LOCAL() 获取最后插入的 IDENTITY
2620        assert_eq!(
2621            dialect.last_insert_id_sql(),
2622            Some("SELECT IDENTITY_VAL_LOCAL() FROM SYSIBM.SYSDUMMY1")
2623        );
2624    }
2625
2626    #[test]
2627    fn test_db2_concat() {
2628        let dialect = Db2Dialect;
2629        // DB2 使用 || 拼接
2630        assert_eq!(dialect.concat(&["a", "b", "c"]), "a || b || c");
2631        assert_eq!(dialect.concat(&[]), "''");
2632    }
2633
2634    #[test]
2635    fn test_db2_create_table() {
2636        let dialect = Db2Dialect;
2637        let cols = vec![ColumnDef {
2638            name: "id".to_string(),
2639            sql_type: "BIGINT".to_string(),
2640            nullable: false,
2641            default: None,
2642            auto_increment: true,
2643            primary_key: true,
2644        }];
2645        let sql = dialect.build_create_table("users", &cols);
2646        assert!(sql.contains("\"id\" BIGINT"));
2647        assert!(sql.contains("GENERATED ALWAYS AS IDENTITY"));
2648        assert!(sql.contains("PRIMARY KEY"));
2649    }
2650
2651    #[test]
2652    fn test_db2_type_mapping() {
2653        // 验证通用类型到 DB2 类型的映射
2654        assert_eq!(map_to_db2_type("BIGINT"), "BIGINT");
2655        assert_eq!(map_to_db2_type("INT"), "INTEGER");
2656        assert_eq!(map_to_db2_type("INTEGER"), "INTEGER");
2657        assert_eq!(map_to_db2_type("TINYINT"), "SMALLINT");
2658        assert_eq!(map_to_db2_type("SMALLINT"), "SMALLINT");
2659        assert_eq!(map_to_db2_type("TEXT"), "CLOB(2G)");
2660        assert_eq!(map_to_db2_type("LONGTEXT"), "CLOB(2G)");
2661        assert_eq!(map_to_db2_type("BOOLEAN"), "SMALLINT");
2662        assert_eq!(map_to_db2_type("BOOL"), "SMALLINT");
2663        assert_eq!(map_to_db2_type("DATETIME"), "TIMESTAMP");
2664        assert_eq!(map_to_db2_type("TIMESTAMP"), "TIMESTAMP");
2665        assert_eq!(map_to_db2_type("DATE"), "DATE");
2666        assert_eq!(map_to_db2_type("VARCHAR(255)"), "VARCHAR(255)");
2667    }
2668
2669    // ===== ClickHouse 独立方言测试 =====
2670
2671    #[test]
2672    fn test_clickhouse_dialect_basic() {
2673        let dialect = get_dialect(DbType::ClickHouse).unwrap();
2674        assert_eq!(dialect.db_type(), DbType::ClickHouse);
2675        // ClickHouse 使用 backquote 标识符（与 MySQL 一致）
2676        assert_eq!(dialect.quote("users"), "`users`");
2677        // ClickHouse 反斜杠转义（与 MySQL 一致）
2678        assert_eq!(dialect.escape_string("it's"), "it\\'s");
2679        // ClickHouse 不支持 RETURNING
2680        assert!(!dialect.supports_returning());
2681        // ClickHouse 不支持自增列
2682        assert_eq!(dialect.auto_increment_keyword(), "");
2683        // ClickHouse 支持 IF EXISTS / IF NOT EXISTS
2684        assert!(dialect.supports_if_exists());
2685        assert!(dialect.supports_if_not_exists());
2686    }
2687
2688    #[test]
2689    fn test_clickhouse_type_mapping() {
2690        assert_eq!(map_to_clickhouse_type("BIGINT"), "Int64");
2691        assert_eq!(map_to_clickhouse_type("INT"), "Int32");
2692        assert_eq!(map_to_clickhouse_type("INTEGER"), "Int32");
2693        assert_eq!(map_to_clickhouse_type("TINYINT"), "Int16");
2694        assert_eq!(map_to_clickhouse_type("SMALLINT"), "Int16");
2695        assert_eq!(map_to_clickhouse_type("VARCHAR(255)"), "String");
2696        assert_eq!(map_to_clickhouse_type("TEXT"), "String");
2697        assert_eq!(map_to_clickhouse_type("BOOLEAN"), "UInt8");
2698        assert_eq!(map_to_clickhouse_type("BOOL"), "UInt8");
2699        assert_eq!(map_to_clickhouse_type("FLOAT"), "Float32");
2700        assert_eq!(map_to_clickhouse_type("DOUBLE"), "Float64");
2701        assert_eq!(map_to_clickhouse_type("DATETIME"), "DateTime");
2702        assert_eq!(map_to_clickhouse_type("TIMESTAMP"), "DateTime");
2703        assert_eq!(map_to_clickhouse_type("DATE"), "Date");
2704    }
2705
2706    #[test]
2707    fn test_clickhouse_create_table() {
2708        let dialect = ClickHouseDialect;
2709        let cols = vec![ColumnDef {
2710            name: "id".to_string(),
2711            sql_type: "BIGINT".to_string(),
2712            nullable: false,
2713            default: None,
2714            auto_increment: false, // ClickHouse 不支持自增
2715            primary_key: true,
2716        }];
2717        let sql = dialect.build_create_table("users", &cols);
2718        // 必须包含 ENGINE = MergeTree()
2719        assert!(
2720            sql.contains("ENGINE = MergeTree()"),
2721            "ClickHouse CREATE TABLE 必须指定 ENGINE: {}",
2722            sql
2723        );
2724        assert!(sql.contains("`id` Int64"));
2725        assert!(sql.contains("PRIMARY KEY"));
2726    }
2727
2728    #[test]
2729    fn test_clickhouse_json_extract() {
2730        let dialect = ClickHouseDialect;
2731        let sql = dialect.json_extract("data", "$.name");
2732        assert!(
2733            sql.contains("JSONExtractString"),
2734            "ClickHouse 应使用 JSONExtractString: {}",
2735            sql
2736        );
2737    }
2738
2739    #[test]
2740    fn test_clickhouse_concat() {
2741        let dialect = ClickHouseDialect;
2742        // ClickHouse 使用 concat() 函数
2743        assert_eq!(dialect.concat(&["a", "b", "c"]), "concat(a, b, c)");
2744        assert_eq!(dialect.concat(&[]), "''");
2745    }
2746
2747    // ===== DbType 国产数据库变体测试 =====
2748
2749    #[test]
2750    fn test_db_type_dameng_str() {
2751        assert_eq!(DbType::Dameng.as_str(), "dameng");
2752        assert_eq!(DbType::from_str("dameng"), Some(DbType::Dameng));
2753        assert_eq!(DbType::from_str("DM"), Some(DbType::Dameng));
2754        assert_eq!(DbType::from_str("dm8"), Some(DbType::Dameng));
2755        assert_eq!(DbType::Dameng.default_port(), 5236);
2756    }
2757
2758    #[test]
2759    fn test_db_type_kingbase_str() {
2760        assert_eq!(DbType::Kingbase.as_str(), "kingbase");
2761        assert_eq!(DbType::from_str("kingbase"), Some(DbType::Kingbase));
2762        assert_eq!(DbType::Kingbase.default_port(), 54321);
2763    }
2764
2765    #[test]
2766    fn test_db_type_db2_str() {
2767        assert_eq!(DbType::Db2.as_str(), "db2");
2768        assert_eq!(DbType::from_str("db2"), Some(DbType::Db2));
2769        assert_eq!(DbType::Db2.default_port(), 50000);
2770    }
2771
2772    #[test]
2773    fn test_db_type_mariadb_str() {
2774        assert_eq!(DbType::MariaDB.as_str(), "mariadb");
2775        assert_eq!(DbType::from_str("mariadb"), Some(DbType::MariaDB));
2776        assert_eq!(DbType::MariaDB.default_port(), 3306);
2777    }
2778
2779    #[test]
2780    fn test_db_type_tidb_str() {
2781        assert_eq!(DbType::TiDB.as_str(), "tidb");
2782        assert_eq!(DbType::from_str("tidb"), Some(DbType::TiDB));
2783        assert_eq!(DbType::TiDB.default_port(), 4000);
2784    }
2785
2786    #[test]
2787    fn test_db_type_polardb_str() {
2788        assert_eq!(DbType::PolarDB.as_str(), "polardb");
2789        assert_eq!(DbType::from_str("polardb"), Some(DbType::PolarDB));
2790        assert_eq!(DbType::PolarDB.default_port(), 5432);
2791    }
2792
2793    #[test]
2794    fn test_db_type_gaussdb_str() {
2795        assert_eq!(DbType::GaussDB.as_str(), "gaussdb");
2796        assert_eq!(DbType::from_str("gaussdb"), Some(DbType::GaussDB));
2797        assert_eq!(DbType::GaussDB.default_port(), 25308);
2798    }
2799
2800    #[test]
2801    fn test_db_type_gbase_str() {
2802        assert_eq!(DbType::GBase.as_str(), "gbase");
2803        assert_eq!(DbType::from_str("gbase"), Some(DbType::GBase));
2804        assert_eq!(DbType::GBase.default_port(), 9088);
2805    }
2806
2807    #[test]
2808    fn test_db_type_sybase_str() {
2809        assert_eq!(DbType::Sybase.as_str(), "sybase");
2810        assert_eq!(DbType::from_str("sybase"), Some(DbType::Sybase));
2811        assert_eq!(DbType::Sybase.default_port(), 5000);
2812    }
2813
2814    #[test]
2815    fn test_db_type_family_classification() {
2816        // MySQL 家族
2817        assert!(DbType::MySQL.is_mysql_family());
2818        assert!(DbType::MariaDB.is_mysql_family());
2819        assert!(DbType::TiDB.is_mysql_family());
2820        assert!(DbType::OceanBase.is_mysql_family());
2821        assert!(!DbType::PostgreSQL.is_mysql_family());
2822
2823        // PostgreSQL 家族
2824        assert!(DbType::PostgreSQL.is_postgres_family());
2825        assert!(DbType::Kingbase.is_postgres_family());
2826        assert!(DbType::GaussDB.is_postgres_family());
2827        assert!(!DbType::MySQL.is_postgres_family());
2828
2829        // Oracle 家族
2830        assert!(DbType::Oracle.is_oracle_family());
2831        assert!(DbType::Dameng.is_oracle_family());
2832        assert!(!DbType::MySQL.is_oracle_family());
2833    }
2834
2835    #[test]
2836    fn test_db_type_supports_stored_procedure_extended() {
2837        // 所有 SQL 数据库应支持存储过程
2838        assert!(DbType::Dameng.supports_stored_procedure());
2839        assert!(DbType::Kingbase.supports_stored_procedure());
2840        assert!(DbType::Db2.supports_stored_procedure());
2841        assert!(DbType::MariaDB.supports_stored_procedure());
2842        assert!(DbType::TiDB.supports_stored_procedure());
2843        assert!(DbType::PolarDB.supports_stored_procedure());
2844        assert!(DbType::GaussDB.supports_stored_procedure());
2845        assert!(DbType::GBase.supports_stored_procedure());
2846        assert!(DbType::Sybase.supports_stored_procedure());
2847    }
2848
2849    // ===== L-4 修复：表名/列名长度校验 =====
2850
2851    #[test]
2852    fn test_l4_max_identifier_len_constant() {
2853        // MAX_IDENTIFIER_LEN 应为 63（PostgreSQL 最严格值）
2854        assert_eq!(MAX_IDENTIFIER_LEN, 63);
2855    }
2856
2857    #[test]
2858    fn test_l4_quote_checked_valid_identifier() {
2859        let dialect = MySqlDialect;
2860        assert_eq!(dialect.quote_checked("users").unwrap(), "`users`");
2861        assert_eq!(dialect.quote_checked("user_id").unwrap(), "`user_id`");
2862        // 边界：恰好 63 字符
2863        let name_63 = "a".repeat(63);
2864        assert!(dialect.quote_checked(&name_63).is_ok());
2865    }
2866
2867    #[test]
2868    fn test_l4_quote_checked_rejects_too_long() {
2869        let dialect = MySqlDialect;
2870        let long_name = "a".repeat(64); // 64 > 63
2871        let result = dialect.quote_checked(&long_name);
2872        assert!(result.is_err());
2873        match result {
2874            Err(DbError::InvalidInput(msg)) => {
2875                assert!(
2876                    msg.contains("too long"),
2877                    "expected 'too long' error, got: {}",
2878                    msg
2879                );
2880            }
2881            _ => panic!("Expected DbError::InvalidInput"),
2882        }
2883    }
2884
2885    #[test]
2886    fn test_l4_quote_checked_rejects_empty() {
2887        let dialect = MySqlDialect;
2888        let result = dialect.quote_checked("");
2889        assert!(result.is_err());
2890    }
2891
2892    #[test]
2893    fn test_l4_quote_checked_rejects_sql_injection() {
2894        let dialect = MySqlDialect;
2895        // 含分号
2896        assert!(dialect.quote_checked("users; DROP TABLE users").is_err());
2897        // 含引号
2898        assert!(dialect.quote_checked("user'name").is_err());
2899        // 含空格
2900        assert!(dialect.quote_checked("user name").is_err());
2901        // 数字开头
2902        assert!(dialect.quote_checked("1users").is_err());
2903        // 含点号
2904        assert!(dialect.quote_checked("schema.table").is_err());
2905    }
2906
2907    #[test]
2908    fn test_l4_quote_checked_postgres() {
2909        let dialect = PostgreSqlDialect;
2910        assert_eq!(dialect.quote_checked("users").unwrap(), "\"users\"");
2911        assert!(dialect.quote_checked(&"a".repeat(64)).is_err());
2912    }
2913
2914    #[test]
2915    fn test_l4_quote_checked_sqlite() {
2916        let dialect = SqliteDialect;
2917        assert_eq!(dialect.quote_checked("users").unwrap(), "\"users\"");
2918        assert!(dialect.quote_checked(&"a".repeat(64)).is_err());
2919    }
2920
2921    #[test]
2922    fn test_l4_quote_checked_oracle() {
2923        let dialect = OracleDialect;
2924        assert_eq!(dialect.quote_checked("users").unwrap(), "\"users\"");
2925        assert!(dialect.quote_checked(&"a".repeat(64)).is_err());
2926    }
2927
2928    #[test]
2929    fn test_l4_quote_checked_sql_server() {
2930        let dialect = SqlServerDialect;
2931        assert_eq!(dialect.quote_checked("users").unwrap(), "[users]");
2932        assert!(dialect.quote_checked(&"a".repeat(64)).is_err());
2933    }
2934}
sz_orm_core/dialect.rs

sz_orm_core/
dialect.rs
