vespertide_query/sql/
helpers.rs

1use sea_query::{
2    Alias, ColumnDef as SeaColumnDef, ForeignKeyAction, MysqlQueryBuilder, PostgresQueryBuilder,
3    QueryStatementWriter, SchemaStatementBuilder, SimpleExpr, SqliteQueryBuilder,
4};
5
6use vespertide_core::{
7    ColumnDef, ColumnType, ComplexColumnType, ReferenceAction, SimpleColumnType, TableConstraint,
8};
9
10use super::create_table::build_create_table_for_backend;
11use super::types::{BuiltQuery, DatabaseBackend, RawSql};
12
13/// Normalize fill_with value - empty string becomes '' (SQL empty string literal)
14pub fn normalize_fill_with(fill_with: Option<&str>) -> Option<String> {
15    fill_with.map(|s| {
16        if s.is_empty() {
17            "''".to_string()
18        } else {
19            s.to_string()
20        }
21    })
22}
23
24/// Helper function to convert a schema statement to SQL for a specific backend
25pub fn build_schema_statement<T: SchemaStatementBuilder>(
26    stmt: &T,
27    backend: DatabaseBackend,
28) -> String {
29    match backend {
30        DatabaseBackend::Postgres => stmt.to_string(PostgresQueryBuilder),
31        DatabaseBackend::MySql => stmt.to_string(MysqlQueryBuilder),
32        DatabaseBackend::Sqlite => stmt.to_string(SqliteQueryBuilder),
33    }
34}
35
36/// Helper function to convert a query statement (INSERT, SELECT, etc.) to SQL for a specific backend
37pub fn build_query_statement<T: QueryStatementWriter>(
38    stmt: &T,
39    backend: DatabaseBackend,
40) -> String {
41    match backend {
42        DatabaseBackend::Postgres => stmt.to_string(PostgresQueryBuilder),
43        DatabaseBackend::MySql => stmt.to_string(MysqlQueryBuilder),
44        DatabaseBackend::Sqlite => stmt.to_string(SqliteQueryBuilder),
45    }
46}
47
48/// Apply vespertide ColumnType to sea_query ColumnDef with table-aware enum type naming
49pub fn apply_column_type_with_table(col: &mut SeaColumnDef, ty: &ColumnType, table: &str) {
50    match ty {
51        ColumnType::Simple(simple) => match simple {
52            SimpleColumnType::SmallInt => {
53                col.small_integer();
54            }
55            SimpleColumnType::Integer => {
56                col.integer();
57            }
58            SimpleColumnType::BigInt => {
59                col.big_integer();
60            }
61            SimpleColumnType::Real => {
62                col.float();
63            }
64            SimpleColumnType::DoublePrecision => {
65                col.double();
66            }
67            SimpleColumnType::Text => {
68                col.text();
69            }
70            SimpleColumnType::Boolean => {
71                col.boolean();
72            }
73            SimpleColumnType::Date => {
74                col.date();
75            }
76            SimpleColumnType::Time => {
77                col.time();
78            }
79            SimpleColumnType::Timestamp => {
80                col.timestamp();
81            }
82            SimpleColumnType::Timestamptz => {
83                col.timestamp_with_time_zone();
84            }
85            SimpleColumnType::Interval => {
86                col.interval(None, None);
87            }
88            SimpleColumnType::Bytea => {
89                col.binary();
90            }
91            SimpleColumnType::Uuid => {
92                col.uuid();
93            }
94            SimpleColumnType::Json => {
95                col.json();
96            }
97            SimpleColumnType::Inet => {
98                col.custom(Alias::new("INET"));
99            }
100            SimpleColumnType::Cidr => {
101                col.custom(Alias::new("CIDR"));
102            }
103            SimpleColumnType::Macaddr => {
104                col.custom(Alias::new("MACADDR"));
105            }
106            SimpleColumnType::Xml => {
107                col.custom(Alias::new("XML"));
108            }
109        },
110        ColumnType::Complex(complex) => match complex {
111            ComplexColumnType::Varchar { length } => {
112                col.string_len(*length);
113            }
114            ComplexColumnType::Numeric { precision, scale } => {
115                col.decimal_len(*precision, *scale);
116            }
117            ComplexColumnType::Char { length } => {
118                col.char_len(*length);
119            }
120            ComplexColumnType::Custom { custom_type } => {
121                col.custom(Alias::new(custom_type));
122            }
123            ComplexColumnType::Enum { name, values } => {
124                // For integer enums, use INTEGER type instead of ENUM
125                if values.is_integer() {
126                    col.integer();
127                } else {
128                    // Use table-prefixed enum type name to avoid conflicts
129                    let type_name = build_enum_type_name(table, name);
130                    col.enumeration(
131                        Alias::new(&type_name),
132                        values
133                            .variant_names()
134                            .into_iter()
135                            .map(Alias::new)
136                            .collect::<Vec<Alias>>(),
137                    );
138                }
139            }
140        },
141    }
142}
143
144/// Convert vespertide ReferenceAction to sea_query ForeignKeyAction
145pub fn to_sea_fk_action(action: &ReferenceAction) -> ForeignKeyAction {
146    match action {
147        ReferenceAction::Cascade => ForeignKeyAction::Cascade,
148        ReferenceAction::Restrict => ForeignKeyAction::Restrict,
149        ReferenceAction::SetNull => ForeignKeyAction::SetNull,
150        ReferenceAction::SetDefault => ForeignKeyAction::SetDefault,
151        ReferenceAction::NoAction => ForeignKeyAction::NoAction,
152    }
153}
154
155/// Convert vespertide ReferenceAction to SQL string
156pub fn reference_action_sql(action: &ReferenceAction) -> &'static str {
157    match action {
158        ReferenceAction::Cascade => "CASCADE",
159        ReferenceAction::Restrict => "RESTRICT",
160        ReferenceAction::SetNull => "SET NULL",
161        ReferenceAction::SetDefault => "SET DEFAULT",
162        ReferenceAction::NoAction => "NO ACTION",
163    }
164}
165
166/// Convert a default value string to the appropriate backend-specific expression
167pub fn convert_default_for_backend(default: &str, backend: &DatabaseBackend) -> String {
168    let lower = default.to_lowercase();
169
170    // UUID generation functions
171    if lower == "gen_random_uuid()" || lower == "uuid()" || lower == "lower(hex(randomblob(16)))" {
172        return match backend {
173            DatabaseBackend::Postgres => "gen_random_uuid()".to_string(),
174            DatabaseBackend::MySql => "(UUID())".to_string(),
175            DatabaseBackend::Sqlite => "lower(hex(randomblob(16)))".to_string(),
176        };
177    }
178
179    // Timestamp functions (case-insensitive)
180    if lower == "current_timestamp()"
181        || lower == "now()"
182        || lower == "current_timestamp"
183        || lower == "getdate()"
184    {
185        return match backend {
186            DatabaseBackend::Postgres => "CURRENT_TIMESTAMP".to_string(),
187            DatabaseBackend::MySql => "CURRENT_TIMESTAMP".to_string(),
188            DatabaseBackend::Sqlite => "CURRENT_TIMESTAMP".to_string(),
189        };
190    }
191
192    // PostgreSQL-style type casts: 'value'::type or expr::type
193    if let Some((value, cast_type)) = parse_pg_type_cast(default) {
194        return convert_type_cast(&value, &cast_type, backend);
195    }
196
197    default.to_string()
198}
199
200/// Parse a PostgreSQL-style type cast expression (e.g., `'[]'::json`, `0::boolean`)
201/// Returns `(value, type)` if parsed, or None if not a type cast.
202fn parse_pg_type_cast(expr: &str) -> Option<(String, String)> {
203    let trimmed = expr.trim();
204
205    // Handle quoted values: 'value'::type
206    if let Some(after_open) = trimmed.strip_prefix('\'') {
207        // Find the closing quote (handle escaped quotes '')
208        let mut i = 0;
209        let bytes = after_open.as_bytes();
210        while i < bytes.len() {
211            if bytes[i] == b'\'' {
212                // Check for escaped quote ''
213                if i + 1 < bytes.len() && bytes[i + 1] == b'\'' {
214                    i += 2;
215                    continue;
216                }
217                // Found closing quote
218                let value_end = i + 1; // index in `after_open`
219                let rest = &after_open[value_end..];
220                if let Some(stripped) = rest.strip_prefix("::") {
221                    let cast_type = stripped.trim().to_lowercase();
222                    if !cast_type.is_empty() {
223                        let value = format!("'{}'", &after_open[..i]);
224                        return Some((value, cast_type));
225                    }
226                }
227                return None;
228            }
229            i += 1;
230        }
231        return None;
232    }
233
234    // Handle unquoted values: expr::type (e.g., 0::boolean, NULL::json)
235    if let Some(pos) = trimmed.find("::") {
236        let value = trimmed[..pos].trim().to_string();
237        let cast_type = trimmed[pos + 2..].trim().to_lowercase();
238        if !value.is_empty() && !cast_type.is_empty() {
239            return Some((value, cast_type));
240        }
241    }
242
243    None
244}
245
246/// Map PostgreSQL type name to MySQL CAST target type
247fn pg_type_to_mysql_cast(pg_type: &str) -> &'static str {
248    match pg_type {
249        "json" | "jsonb" => "JSON",
250        "text" | "varchar" | "char" | "character varying" => "CHAR",
251        "integer" | "int" | "int4" | "smallint" | "int2" => "SIGNED",
252        "bigint" | "int8" => "SIGNED",
253        "real" | "float4" | "double precision" | "float8" => "DECIMAL",
254        "boolean" | "bool" => "UNSIGNED",
255        "date" => "DATE",
256        "time" => "TIME",
257        "timestamp"
258        | "timestamptz"
259        | "timestamp with time zone"
260        | "timestamp without time zone" => "DATETIME",
261        "numeric" | "decimal" => "DECIMAL",
262        "bytea" => "BINARY",
263        _ => "CHAR",
264    }
265}
266
267/// Convert a type cast expression to the appropriate backend syntax
268fn convert_type_cast(value: &str, cast_type: &str, backend: &DatabaseBackend) -> String {
269    match backend {
270        // PostgreSQL: keep native :: syntax
271        DatabaseBackend::Postgres => format!("{}::{}", value, cast_type),
272        // MySQL: CAST(value AS type)
273        DatabaseBackend::MySql => {
274            let mysql_type = pg_type_to_mysql_cast(cast_type);
275            format!("CAST({} AS {})", value, mysql_type)
276        }
277        // SQLite: strip the cast, use raw value (SQLite is dynamically typed)
278        DatabaseBackend::Sqlite => value.to_string(),
279    }
280}
281
282/// Check if the column type is an enum type
283fn is_enum_type(column_type: &ColumnType) -> bool {
284    matches!(
285        column_type,
286        ColumnType::Complex(ComplexColumnType::Enum { .. })
287    )
288}
289
290/// Normalize a default value for enum columns - add quotes if needed
291/// This is used for SQL expressions (INSERT, UPDATE) where enum values need quoting
292pub fn normalize_enum_default(column_type: &ColumnType, value: &str) -> String {
293    if is_enum_type(column_type) && needs_quoting(value) {
294        format!("'{}'", value)
295    } else {
296        value.to_string()
297    }
298}
299
300/// Check if a string default value needs quoting (is a plain string literal without quotes/parens)
301fn needs_quoting(default_str: &str) -> bool {
302    let trimmed = default_str.trim();
303    // Empty string always needs quoting to become ''
304    if trimmed.is_empty() {
305        return true;
306    }
307    // Don't quote if already quoted
308    if trimmed.starts_with('\'') || trimmed.starts_with('"') {
309        return false;
310    }
311    // Don't quote if it's a function call
312    if trimmed.contains('(') || trimmed.contains(')') {
313        return false;
314    }
315    // Don't quote NULL
316    if trimmed.eq_ignore_ascii_case("null") {
317        return false;
318    }
319    // Don't quote special SQL keywords
320    if trimmed.eq_ignore_ascii_case("current_timestamp")
321        || trimmed.eq_ignore_ascii_case("current_date")
322        || trimmed.eq_ignore_ascii_case("current_time")
323    {
324        return false;
325    }
326    true
327}
328
329/// Build sea_query ColumnDef from vespertide ColumnDef for a specific backend with table-aware enum naming
330pub fn build_sea_column_def_with_table(
331    backend: &DatabaseBackend,
332    table: &str,
333    column: &ColumnDef,
334) -> SeaColumnDef {
335    let mut col = SeaColumnDef::new(Alias::new(&column.name));
336    apply_column_type_with_table(&mut col, &column.r#type, table);
337
338    if !column.nullable {
339        col.not_null();
340    }
341
342    if let Some(default) = &column.default {
343        let default_str = default.to_sql();
344        let converted = convert_default_for_backend(&default_str, backend);
345
346        // Auto-quote enum default values if the value is a string and needs quoting
347        let final_default =
348            if is_enum_type(&column.r#type) && default.is_string() && needs_quoting(&converted) {
349                format!("'{}'", converted)
350            } else {
351                converted
352            };
353
354        // SQLite requires DEFAULT (expr) for expressions containing function calls.
355        // Wrapping in parentheses is always safe for all backends.
356        let final_default = if *backend == DatabaseBackend::Sqlite
357            && final_default.contains('(')
358            && !final_default.starts_with('(')
359        {
360            format!("({})", final_default)
361        } else {
362            final_default
363        };
364
365        col.default(Into::<SimpleExpr>::into(sea_query::Expr::cust(
366            final_default,
367        )));
368    }
369
370    col
371}
372
373/// Generate CREATE TYPE SQL for an enum type (PostgreSQL only)
374/// Returns None for non-PostgreSQL backends or non-enum types
375///
376/// The enum type name will be prefixed with the table name to avoid conflicts
377/// across tables using the same enum name (e.g., "status", "gender").
378pub fn build_create_enum_type_sql(
379    table: &str,
380    column_type: &ColumnType,
381) -> Option<super::types::RawSql> {
382    if let ColumnType::Complex(ComplexColumnType::Enum { name, values }) = column_type {
383        // Integer enums don't need CREATE TYPE - they use INTEGER column
384        if values.is_integer() {
385            return None;
386        }
387
388        let values_sql = values.to_sql_values().join(", ");
389
390        // Generate unique type name with table prefix
391        let type_name = build_enum_type_name(table, name);
392
393        // PostgreSQL: CREATE TYPE {table}_{name} AS ENUM (...)
394        let pg_sql = format!("CREATE TYPE \"{}\" AS ENUM ({})", type_name, values_sql);
395
396        // MySQL: ENUMs are inline, no CREATE TYPE needed
397        // SQLite: Uses TEXT, no CREATE TYPE needed
398        Some(super::types::RawSql::per_backend(
399            pg_sql,
400            String::new(),
401            String::new(),
402        ))
403    } else {
404        None
405    }
406}
407
408/// Generate DROP TYPE SQL for an enum type (PostgreSQL only)
409/// Returns None for non-PostgreSQL backends or non-enum types
410///
411/// The enum type name will be prefixed with the table name to match the CREATE TYPE.
412pub fn build_drop_enum_type_sql(
413    table: &str,
414    column_type: &ColumnType,
415) -> Option<super::types::RawSql> {
416    if let ColumnType::Complex(ComplexColumnType::Enum { name, .. }) = column_type {
417        // Generate the same unique type name used in CREATE TYPE
418        let type_name = build_enum_type_name(table, name);
419
420        // PostgreSQL: DROP TYPE {table}_{name}
421        let pg_sql = format!("DROP TYPE \"{}\"", type_name);
422
423        // MySQL/SQLite: No action needed
424        Some(super::types::RawSql::per_backend(
425            pg_sql,
426            String::new(),
427            String::new(),
428        ))
429    } else {
430        None
431    }
432}
433
434// Re-export naming functions from vespertide-naming
435pub use vespertide_naming::{
436    build_check_constraint_name, build_enum_type_name, build_foreign_key_name, build_index_name,
437    build_unique_constraint_name,
438};
439
440/// Alias for build_check_constraint_name for SQLite enum columns
441pub fn build_sqlite_enum_check_name(table: &str, column: &str) -> String {
442    build_check_constraint_name(table, column)
443}
444
445/// Generate CHECK constraint expression for SQLite enum column
446/// Returns the constraint clause like: CONSTRAINT "chk_table_col" CHECK (col IN ('val1', 'val2'))
447pub fn build_sqlite_enum_check_clause(
448    table: &str,
449    column: &str,
450    column_type: &ColumnType,
451) -> Option<String> {
452    if let ColumnType::Complex(ComplexColumnType::Enum { values, .. }) = column_type {
453        let name = build_sqlite_enum_check_name(table, column);
454        let values_sql = values.to_sql_values().join(", ");
455        Some(format!(
456            "CONSTRAINT \"{}\" CHECK (\"{}\" IN ({}))",
457            name, column, values_sql
458        ))
459    } else {
460        None
461    }
462}
463
464/// Collect all CHECK constraints for enum columns in a table (for SQLite)
465pub fn collect_sqlite_enum_check_clauses(table: &str, columns: &[ColumnDef]) -> Vec<String> {
466    columns
467        .iter()
468        .filter_map(|col| build_sqlite_enum_check_clause(table, &col.name, &col.r#type))
469        .collect()
470}
471
472/// Extract CHECK constraint clauses from a list of table constraints.
473/// Returns SQL fragments like: `CONSTRAINT "chk_name" CHECK (expr)`
474pub fn extract_check_clauses(constraints: &[TableConstraint]) -> Vec<String> {
475    constraints
476        .iter()
477        .filter_map(|c| {
478            if let TableConstraint::Check { name, expr } = c {
479                Some(format!("CONSTRAINT \"{}\" CHECK ({})", name, expr))
480            } else {
481                None
482            }
483        })
484        .collect()
485}
486
487/// Collect ALL CHECK constraint clauses for a SQLite temp table.
488/// Combines both:
489/// - Enum-based CHECK constraints (from column types)
490/// - Explicit CHECK constraints (from `TableConstraint::Check`)
491///
492/// Returns deduplicated union of both.
493pub fn collect_all_check_clauses(
494    table: &str,
495    columns: &[ColumnDef],
496    constraints: &[TableConstraint],
497) -> Vec<String> {
498    let mut clauses = collect_sqlite_enum_check_clauses(table, columns);
499    let explicit = extract_check_clauses(constraints);
500    for clause in explicit {
501        if !clauses.contains(&clause) {
502            clauses.push(clause);
503        }
504    }
505    clauses
506}
507
508/// Build CREATE TABLE query with CHECK constraints properly embedded.
509/// sea-query doesn't support CHECK constraints natively, so we inject them
510/// by modifying the generated SQL string.
511pub fn build_create_with_checks(
512    backend: &DatabaseBackend,
513    create_stmt: &sea_query::TableCreateStatement,
514    check_clauses: &[String],
515) -> BuiltQuery {
516    if check_clauses.is_empty() {
517        BuiltQuery::CreateTable(Box::new(create_stmt.clone()))
518    } else {
519        let base_sql = build_schema_statement(create_stmt, *backend);
520        let mut modified_sql = base_sql;
521        if let Some(pos) = modified_sql.rfind(')') {
522            let check_sql = check_clauses.join(", ");
523            modified_sql.insert_str(pos, &format!(", {}", check_sql));
524        }
525        BuiltQuery::Raw(RawSql::per_backend(
526            modified_sql.clone(),
527            modified_sql.clone(),
528            modified_sql,
529        ))
530    }
531}
532
533/// Build the CREATE TABLE statement for a SQLite temp table, including all CHECK constraints.
534/// This combines `build_create_table_for_backend` with CHECK constraint injection.
535///
536/// `table` is the ORIGINAL table name (used for constraint naming).
537/// `temp_table` is the temporary table name.
538pub fn build_sqlite_temp_table_create(
539    backend: &DatabaseBackend,
540    temp_table: &str,
541    table: &str,
542    columns: &[ColumnDef],
543    constraints: &[TableConstraint],
544) -> BuiltQuery {
545    let create_stmt = build_create_table_for_backend(backend, temp_table, columns, constraints);
546    let check_clauses = collect_all_check_clauses(table, columns, constraints);
547    build_create_with_checks(backend, &create_stmt, &check_clauses)
548}
549
550/// Recreate all indexes (both regular and UNIQUE) after a SQLite temp table rebuild.
551/// After DROP TABLE + RENAME, all original indexes are gone, so plain CREATE INDEX is correct.
552///
553/// `pending_constraints` are constraints that exist in the logical schema but haven't been
554/// physically created yet (e.g., promoted from inline column definitions by AddColumn normalization).
555/// These will be created by separate AddConstraint actions later, so we must NOT recreate them here.
556pub fn recreate_indexes_after_rebuild(
557    table: &str,
558    constraints: &[TableConstraint],
559    pending_constraints: &[TableConstraint],
560) -> Vec<BuiltQuery> {
561    let mut queries = Vec::new();
562    for constraint in constraints {
563        // Skip constraints that will be created by future AddConstraint actions
564        if pending_constraints.contains(constraint) {
565            continue;
566        }
567        match constraint {
568            TableConstraint::Index { name, columns } => {
569                let index_name = build_index_name(table, columns, name.as_deref());
570                let cols_sql = columns
571                    .iter()
572                    .map(|c| format!("\"{}\"", c))
573                    .collect::<Vec<_>>()
574                    .join(", ");
575                let sql = format!(
576                    "CREATE INDEX \"{}\" ON \"{}\" ({})",
577                    index_name, table, cols_sql
578                );
579                queries.push(BuiltQuery::Raw(RawSql::per_backend(
580                    sql.clone(),
581                    sql.clone(),
582                    sql,
583                )));
584            }
585            TableConstraint::Unique { name, columns } => {
586                let index_name = build_unique_constraint_name(table, columns, name.as_deref());
587                let cols_sql = columns
588                    .iter()
589                    .map(|c| format!("\"{}\"", c))
590                    .collect::<Vec<_>>()
591                    .join(", ");
592                let sql = format!(
593                    "CREATE UNIQUE INDEX \"{}\" ON \"{}\" ({})",
594                    index_name, table, cols_sql
595                );
596                queries.push(BuiltQuery::Raw(RawSql::per_backend(
597                    sql.clone(),
598                    sql.clone(),
599                    sql,
600                )));
601            }
602            _ => {}
603        }
604    }
605    queries
606}
607
608/// Extract enum name from column type if it's an enum
609pub fn get_enum_name(column_type: &ColumnType) -> Option<&str> {
610    if let ColumnType::Complex(ComplexColumnType::Enum { name, .. }) = column_type {
611        Some(name.as_str())
612    } else {
613        None
614    }
615}
616
617#[cfg(test)]
618mod tests {
619    use super::*;
620    use rstest::rstest;
621    use sea_query::{Alias, ColumnDef as SeaColumnDef, ForeignKeyAction};
622    use vespertide_core::EnumValues;
623
624    #[rstest]
625    #[case(ColumnType::Simple(SimpleColumnType::Integer))]
626    #[case(ColumnType::Simple(SimpleColumnType::BigInt))]
627    #[case(ColumnType::Simple(SimpleColumnType::Text))]
628    #[case(ColumnType::Simple(SimpleColumnType::Boolean))]
629    #[case(ColumnType::Simple(SimpleColumnType::Timestamp))]
630    #[case(ColumnType::Simple(SimpleColumnType::Uuid))]
631    #[case(ColumnType::Complex(ComplexColumnType::Varchar { length: 255 }))]
632    #[case(ColumnType::Complex(ComplexColumnType::Numeric { precision: 10, scale: 2 }))]
633    fn test_column_type_conversion(#[case] ty: ColumnType) {
634        // Just ensure no panic - test by creating a column with this type
635        let mut col = SeaColumnDef::new(Alias::new("test"));
636        apply_column_type_with_table(&mut col, &ty, "test_table");
637    }
638
639    #[rstest]
640    #[case(SimpleColumnType::SmallInt)]
641    #[case(SimpleColumnType::Integer)]
642    #[case(SimpleColumnType::BigInt)]
643    #[case(SimpleColumnType::Real)]
644    #[case(SimpleColumnType::DoublePrecision)]
645    #[case(SimpleColumnType::Text)]
646    #[case(SimpleColumnType::Boolean)]
647    #[case(SimpleColumnType::Date)]
648    #[case(SimpleColumnType::Time)]
649    #[case(SimpleColumnType::Timestamp)]
650    #[case(SimpleColumnType::Timestamptz)]
651    #[case(SimpleColumnType::Interval)]
652    #[case(SimpleColumnType::Bytea)]
653    #[case(SimpleColumnType::Uuid)]
654    #[case(SimpleColumnType::Json)]
655    #[case(SimpleColumnType::Inet)]
656    #[case(SimpleColumnType::Cidr)]
657    #[case(SimpleColumnType::Macaddr)]
658    #[case(SimpleColumnType::Xml)]
659    fn test_all_simple_types_cover_branches(#[case] ty: SimpleColumnType) {
660        let mut col = SeaColumnDef::new(Alias::new("t"));
661        apply_column_type_with_table(&mut col, &ColumnType::Simple(ty), "test_table");
662    }
663
664    #[rstest]
665    #[case(ComplexColumnType::Varchar { length: 42 })]
666    #[case(ComplexColumnType::Numeric { precision: 8, scale: 3 })]
667    #[case(ComplexColumnType::Char { length: 3 })]
668    #[case(ComplexColumnType::Custom { custom_type: "GEOGRAPHY".into() })]
669    #[case(ComplexColumnType::Enum { name: "status".into(), values: EnumValues::String(vec!["active".into(), "inactive".into()]) })]
670    fn test_all_complex_types_cover_branches(#[case] ty: ComplexColumnType) {
671        let mut col = SeaColumnDef::new(Alias::new("t"));
672        apply_column_type_with_table(&mut col, &ColumnType::Complex(ty), "test_table");
673    }
674
675    #[rstest]
676    #[case::cascade(ReferenceAction::Cascade, ForeignKeyAction::Cascade)]
677    #[case::restrict(ReferenceAction::Restrict, ForeignKeyAction::Restrict)]
678    #[case::set_null(ReferenceAction::SetNull, ForeignKeyAction::SetNull)]
679    #[case::set_default(ReferenceAction::SetDefault, ForeignKeyAction::SetDefault)]
680    #[case::no_action(ReferenceAction::NoAction, ForeignKeyAction::NoAction)]
681    fn test_reference_action_conversion(
682        #[case] action: ReferenceAction,
683        #[case] expected: ForeignKeyAction,
684    ) {
685        // Just ensure the function doesn't panic and returns valid ForeignKeyAction
686        let result = to_sea_fk_action(&action);
687        assert!(
688            matches!(result, _expected),
689            "Expected {:?}, got {:?}",
690            expected,
691            result
692        );
693    }
694
695    #[rstest]
696    #[case(ReferenceAction::Cascade, "CASCADE")]
697    #[case(ReferenceAction::Restrict, "RESTRICT")]
698    #[case(ReferenceAction::SetNull, "SET NULL")]
699    #[case(ReferenceAction::SetDefault, "SET DEFAULT")]
700    #[case(ReferenceAction::NoAction, "NO ACTION")]
701    fn test_reference_action_sql_all_variants(
702        #[case] action: ReferenceAction,
703        #[case] expected: &str,
704    ) {
705        assert_eq!(reference_action_sql(&action), expected);
706    }
707
708    #[rstest]
709    #[case::gen_random_uuid_postgres(
710        "gen_random_uuid()",
711        DatabaseBackend::Postgres,
712        "gen_random_uuid()"
713    )]
714    #[case::gen_random_uuid_mysql("gen_random_uuid()", DatabaseBackend::MySql, "(UUID())")]
715    #[case::gen_random_uuid_sqlite(
716        "gen_random_uuid()",
717        DatabaseBackend::Sqlite,
718        "lower(hex(randomblob(16)))"
719    )]
720    #[case::current_timestamp_postgres(
721        "current_timestamp()",
722        DatabaseBackend::Postgres,
723        "CURRENT_TIMESTAMP"
724    )]
725    #[case::current_timestamp_mysql(
726        "current_timestamp()",
727        DatabaseBackend::MySql,
728        "CURRENT_TIMESTAMP"
729    )]
730    #[case::current_timestamp_sqlite(
731        "current_timestamp()",
732        DatabaseBackend::Sqlite,
733        "CURRENT_TIMESTAMP"
734    )]
735    #[case::now_postgres("now()", DatabaseBackend::Postgres, "CURRENT_TIMESTAMP")]
736    #[case::now_mysql("now()", DatabaseBackend::MySql, "CURRENT_TIMESTAMP")]
737    #[case::now_sqlite("now()", DatabaseBackend::Sqlite, "CURRENT_TIMESTAMP")]
738    #[case::now_upper_postgres("NOW()", DatabaseBackend::Postgres, "CURRENT_TIMESTAMP")]
739    #[case::now_upper_mysql("NOW()", DatabaseBackend::MySql, "CURRENT_TIMESTAMP")]
740    #[case::now_upper_sqlite("NOW()", DatabaseBackend::Sqlite, "CURRENT_TIMESTAMP")]
741    #[case::current_timestamp_upper_postgres(
742        "CURRENT_TIMESTAMP",
743        DatabaseBackend::Postgres,
744        "CURRENT_TIMESTAMP"
745    )]
746    #[case::current_timestamp_upper_mysql(
747        "CURRENT_TIMESTAMP",
748        DatabaseBackend::MySql,
749        "CURRENT_TIMESTAMP"
750    )]
751    #[case::current_timestamp_upper_sqlite(
752        "CURRENT_TIMESTAMP",
753        DatabaseBackend::Sqlite,
754        "CURRENT_TIMESTAMP"
755    )]
756    fn test_convert_default_for_backend(
757        #[case] default: &str,
758        #[case] backend: DatabaseBackend,
759        #[case] expected: &str,
760    ) {
761        let result = convert_default_for_backend(default, &backend);
762        assert_eq!(result, expected);
763    }
764
765    // --- PostgreSQL type cast conversion tests ---
766
767    #[rstest]
768    // JSON type cast: '[]'::json
769    #[case::json_cast_postgres("'[]'::json", DatabaseBackend::Postgres, "'[]'::json")]
770    #[case::json_cast_mysql("'[]'::json", DatabaseBackend::MySql, "CAST('[]' AS JSON)")]
771    #[case::json_cast_sqlite("'[]'::json", DatabaseBackend::Sqlite, "'[]'")]
772    // JSONB type cast: '{}'::jsonb
773    #[case::jsonb_cast_postgres("'{}'::jsonb", DatabaseBackend::Postgres, "'{}'::jsonb")]
774    #[case::jsonb_cast_mysql("'{}'::jsonb", DatabaseBackend::MySql, "CAST('{}' AS JSON)")]
775    #[case::jsonb_cast_sqlite("'{}'::jsonb", DatabaseBackend::Sqlite, "'{}'")]
776    // Text type cast: 'hello'::text
777    #[case::text_cast_postgres("'hello'::text", DatabaseBackend::Postgres, "'hello'::text")]
778    #[case::text_cast_mysql("'hello'::text", DatabaseBackend::MySql, "CAST('hello' AS CHAR)")]
779    #[case::text_cast_sqlite("'hello'::text", DatabaseBackend::Sqlite, "'hello'")]
780    // Integer type cast: 0::integer
781    #[case::int_cast_postgres("0::integer", DatabaseBackend::Postgres, "0::integer")]
782    #[case::int_cast_mysql("0::integer", DatabaseBackend::MySql, "CAST(0 AS SIGNED)")]
783    #[case::int_cast_sqlite("0::integer", DatabaseBackend::Sqlite, "0")]
784    // Boolean type cast: 0::boolean
785    #[case::bool_cast_postgres("0::boolean", DatabaseBackend::Postgres, "0::boolean")]
786    #[case::bool_cast_mysql("0::boolean", DatabaseBackend::MySql, "CAST(0 AS UNSIGNED)")]
787    #[case::bool_cast_sqlite("0::boolean", DatabaseBackend::Sqlite, "0")]
788    // Nested JSON object: '{"key":"value"}'::json
789    #[case::json_obj_cast_postgres(
790        "'{\"key\":\"value\"}'::json",
791        DatabaseBackend::Postgres,
792        "'{\"key\":\"value\"}'::json"
793    )]
794    #[case::json_obj_cast_mysql(
795        "'{\"key\":\"value\"}'::json",
796        DatabaseBackend::MySql,
797        "CAST('{\"key\":\"value\"}' AS JSON)"
798    )]
799    #[case::json_obj_cast_sqlite(
800        "'{\"key\":\"value\"}'::json",
801        DatabaseBackend::Sqlite,
802        "'{\"key\":\"value\"}'"
803    )]
804    // Timestamp type cast: '2024-01-01'::timestamp
805    #[case::timestamp_cast_postgres(
806        "'2024-01-01'::timestamp",
807        DatabaseBackend::Postgres,
808        "'2024-01-01'::timestamp"
809    )]
810    #[case::timestamp_cast_mysql(
811        "'2024-01-01'::timestamp",
812        DatabaseBackend::MySql,
813        "CAST('2024-01-01' AS DATETIME)"
814    )]
815    #[case::timestamp_cast_sqlite(
816        "'2024-01-01'::timestamp",
817        DatabaseBackend::Sqlite,
818        "'2024-01-01'"
819    )]
820    fn test_convert_default_for_backend_type_cast(
821        #[case] default: &str,
822        #[case] backend: DatabaseBackend,
823        #[case] expected: &str,
824    ) {
825        let result = convert_default_for_backend(default, &backend);
826        assert_eq!(result, expected);
827    }
828
829    #[test]
830    fn test_parse_pg_type_cast_no_cast() {
831        // Regular values should not be parsed as type casts
832        assert!(parse_pg_type_cast("'hello'").is_none());
833        assert!(parse_pg_type_cast("42").is_none());
834        assert!(parse_pg_type_cast("NOW()").is_none());
835        assert!(parse_pg_type_cast("CURRENT_TIMESTAMP").is_none());
836    }
837
838    #[test]
839    fn test_parse_pg_type_cast_valid() {
840        let (value, cast_type) = parse_pg_type_cast("'[]'::json").unwrap();
841        assert_eq!(value, "'[]'");
842        assert_eq!(cast_type, "json");
843
844        let (value, cast_type) = parse_pg_type_cast("0::boolean").unwrap();
845        assert_eq!(value, "0");
846        assert_eq!(cast_type, "boolean");
847    }
848
849    #[test]
850    fn test_parse_pg_type_cast_escaped_quotes() {
851        // Value with escaped quotes: 'it''s'::text
852        let (value, cast_type) = parse_pg_type_cast("'it''s'::text").unwrap();
853        assert_eq!(value, "'it''s'");
854        assert_eq!(cast_type, "text");
855    }
856
857    #[test]
858    fn test_parse_pg_type_cast_unterminated_quote() {
859        // Unterminated quoted string should return None (line 203)
860        assert!(parse_pg_type_cast("'unclosed").is_none());
861        assert!(parse_pg_type_cast("'no close quote::json").is_none());
862    }
863
864    #[rstest]
865    #[case::numeric("'0.5'::numeric", DatabaseBackend::MySql, "CAST('0.5' AS DECIMAL)")]
866    #[case::decimal("'1.23'::decimal", DatabaseBackend::MySql, "CAST('1.23' AS DECIMAL)")]
867    #[case::bytea("'\\xDE'::bytea", DatabaseBackend::MySql, "CAST('\\xDE' AS BINARY)")]
868    #[case::unknown("'x'::citext", DatabaseBackend::MySql, "CAST('x' AS CHAR)")]
869    fn test_convert_default_for_backend_type_cast_extra(
870        #[case] default: &str,
871        #[case] backend: DatabaseBackend,
872        #[case] expected: &str,
873    ) {
874        let result = convert_default_for_backend(default, &backend);
875        assert_eq!(result, expected);
876    }
877
878    #[test]
879    fn test_is_enum_type_true() {
880        use vespertide_core::EnumValues;
881
882        let enum_type = ColumnType::Complex(ComplexColumnType::Enum {
883            name: "status".into(),
884            values: EnumValues::String(vec!["active".into(), "inactive".into()]),
885        });
886        assert!(is_enum_type(&enum_type));
887    }
888
889    #[test]
890    fn test_is_enum_type_false() {
891        let text_type = ColumnType::Simple(SimpleColumnType::Text);
892        assert!(!is_enum_type(&text_type));
893    }
894
895    #[test]
896    fn test_get_enum_name_some() {
897        use vespertide_core::EnumValues;
898
899        let enum_type = ColumnType::Complex(ComplexColumnType::Enum {
900            name: "user_status".into(),
901            values: EnumValues::String(vec!["active".into(), "inactive".into()]),
902        });
903        assert_eq!(get_enum_name(&enum_type), Some("user_status"));
904    }
905
906    #[test]
907    fn test_get_enum_name_none() {
908        let text_type = ColumnType::Simple(SimpleColumnType::Text);
909        assert_eq!(get_enum_name(&text_type), None);
910    }
911
912    #[test]
913    fn test_apply_column_type_integer_enum() {
914        use vespertide_core::{EnumValues, NumValue};
915        let integer_enum = ColumnType::Complex(ComplexColumnType::Enum {
916            name: "color".into(),
917            values: EnumValues::Integer(vec![
918                NumValue {
919                    name: "Black".into(),
920                    value: 0,
921                },
922                NumValue {
923                    name: "White".into(),
924                    value: 1,
925                },
926            ]),
927        });
928        let mut col = SeaColumnDef::new(Alias::new("color"));
929        apply_column_type_with_table(&mut col, &integer_enum, "test_table");
930        // Integer enums should use INTEGER type, not ENUM
931    }
932
933    #[test]
934    fn test_build_create_enum_type_sql_integer_enum_returns_none() {
935        use vespertide_core::{EnumValues, NumValue};
936        let integer_enum = ColumnType::Complex(ComplexColumnType::Enum {
937            name: "priority".into(),
938            values: EnumValues::Integer(vec![
939                NumValue {
940                    name: "Low".into(),
941                    value: 0,
942                },
943                NumValue {
944                    name: "High".into(),
945                    value: 10,
946                },
947            ]),
948        });
949        // Integer enums should return None (no CREATE TYPE needed)
950        assert!(build_create_enum_type_sql("test_table", &integer_enum).is_none());
951    }
952
953    #[rstest]
954    // Empty strings need quoting
955    #[case::empty("", true)]
956    #[case::whitespace_only("   ", true)]
957    // Function calls should not be quoted
958    #[case::now_func("now()", false)]
959    #[case::coalesce_func("COALESCE(old_value, 'default')", false)]
960    #[case::uuid_func("gen_random_uuid()", false)]
961    // NULL keyword should not be quoted
962    #[case::null_upper("NULL", false)]
963    #[case::null_lower("null", false)]
964    #[case::null_mixed("Null", false)]
965    // SQL date/time keywords should not be quoted
966    #[case::current_timestamp_upper("CURRENT_TIMESTAMP", false)]
967    #[case::current_timestamp_lower("current_timestamp", false)]
968    #[case::current_date_upper("CURRENT_DATE", false)]
969    #[case::current_date_lower("current_date", false)]
970    #[case::current_time_upper("CURRENT_TIME", false)]
971    #[case::current_time_lower("current_time", false)]
972    // Already quoted strings should not be re-quoted
973    #[case::single_quoted("'active'", false)]
974    #[case::double_quoted("\"active\"", false)]
975    // Plain strings need quoting
976    #[case::plain_active("active", true)]
977    #[case::plain_pending("pending", true)]
978    #[case::plain_underscore("some_value", true)]
979    fn test_needs_quoting(#[case] input: &str, #[case] expected: bool) {
980        assert_eq!(needs_quoting(input), expected);
981    }
982
983    #[test]
984    fn test_recreate_indexes_after_rebuild_skips_pending() {
985        use vespertide_core::TableConstraint;
986        let idx1 = TableConstraint::Index {
987            name: Some("idx_a".into()),
988            columns: vec!["a".into()],
989        };
990        let idx2 = TableConstraint::Index {
991            name: Some("idx_b".into()),
992            columns: vec!["b".into()],
993        };
994        let uq1 = TableConstraint::Unique {
995            name: Some("uq_c".into()),
996            columns: vec!["c".into()],
997        };
998
999        // All three in table constraints, but idx1 and uq1 are pending
1000        let constraints = vec![idx1.clone(), idx2.clone(), uq1.clone()];
1001        let pending = vec![idx1.clone(), uq1.clone()];
1002
1003        let queries = recreate_indexes_after_rebuild("t", &constraints, &pending);
1004        // Only idx_b should be recreated
1005        assert_eq!(queries.len(), 1);
1006        let sql = queries[0].build(DatabaseBackend::Sqlite);
1007        assert!(sql.contains("idx_b"));
1008    }
1009
1010    #[test]
1011    fn test_recreate_indexes_after_rebuild_no_pending() {
1012        use vespertide_core::TableConstraint;
1013        let idx = TableConstraint::Index {
1014            name: Some("idx_a".into()),
1015            columns: vec!["a".into()],
1016        };
1017        let uq = TableConstraint::Unique {
1018            name: Some("uq_b".into()),
1019            columns: vec!["b".into()],
1020        };
1021
1022        let queries = recreate_indexes_after_rebuild("t", &[idx, uq], &[]);
1023        assert_eq!(queries.len(), 2);
1024    }
1025
1026    #[test]
1027    fn test_recreate_indexes_after_rebuild_skips_non_index_constraints() {
1028        use vespertide_core::TableConstraint;
1029        let pk = TableConstraint::PrimaryKey {
1030            columns: vec!["id".into()],
1031            auto_increment: false,
1032        };
1033        let fk = TableConstraint::ForeignKey {
1034            name: None,
1035            columns: vec!["uid".into()],
1036            ref_table: "u".into(),
1037            ref_columns: vec!["id".into()],
1038            on_delete: None,
1039            on_update: None,
1040        };
1041        let chk = TableConstraint::Check {
1042            name: "chk".into(),
1043            expr: "id > 0".into(),
1044        };
1045
1046        let queries = recreate_indexes_after_rebuild("t", &[pk, fk, chk], &[]);
1047        assert_eq!(queries.len(), 0);
1048    }
1049}
vespertide_query/sql/helpers.rs

vespertide_query/sql/
helpers.rs
