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    default.to_string()
193}
194
195/// Check if the column type is an enum type
196fn is_enum_type(column_type: &ColumnType) -> bool {
197    matches!(
198        column_type,
199        ColumnType::Complex(ComplexColumnType::Enum { .. })
200    )
201}
202
203/// Normalize a default value for enum columns - add quotes if needed
204/// This is used for SQL expressions (INSERT, UPDATE) where enum values need quoting
205pub fn normalize_enum_default(column_type: &ColumnType, value: &str) -> String {
206    if is_enum_type(column_type) && needs_quoting(value) {
207        format!("'{}'", value)
208    } else {
209        value.to_string()
210    }
211}
212
213/// Check if a string default value needs quoting (is a plain string literal without quotes/parens)
214fn needs_quoting(default_str: &str) -> bool {
215    let trimmed = default_str.trim();
216    // Empty string always needs quoting to become ''
217    if trimmed.is_empty() {
218        return true;
219    }
220    // Don't quote if already quoted
221    if trimmed.starts_with('\'') || trimmed.starts_with('"') {
222        return false;
223    }
224    // Don't quote if it's a function call
225    if trimmed.contains('(') || trimmed.contains(')') {
226        return false;
227    }
228    // Don't quote NULL
229    if trimmed.eq_ignore_ascii_case("null") {
230        return false;
231    }
232    // Don't quote special SQL keywords
233    if trimmed.eq_ignore_ascii_case("current_timestamp")
234        || trimmed.eq_ignore_ascii_case("current_date")
235        || trimmed.eq_ignore_ascii_case("current_time")
236    {
237        return false;
238    }
239    true
240}
241
242/// Build sea_query ColumnDef from vespertide ColumnDef for a specific backend with table-aware enum naming
243pub fn build_sea_column_def_with_table(
244    backend: &DatabaseBackend,
245    table: &str,
246    column: &ColumnDef,
247) -> SeaColumnDef {
248    let mut col = SeaColumnDef::new(Alias::new(&column.name));
249    apply_column_type_with_table(&mut col, &column.r#type, table);
250
251    if !column.nullable {
252        col.not_null();
253    }
254
255    if let Some(default) = &column.default {
256        let default_str = default.to_sql();
257        let converted = convert_default_for_backend(&default_str, backend);
258
259        // Auto-quote enum default values if the value is a string and needs quoting
260        let final_default =
261            if is_enum_type(&column.r#type) && default.is_string() && needs_quoting(&converted) {
262                format!("'{}'", converted)
263            } else {
264                converted
265            };
266
267        // SQLite requires DEFAULT (expr) for expressions containing function calls.
268        // Wrapping in parentheses is always safe for all backends.
269        let final_default = if *backend == DatabaseBackend::Sqlite
270            && final_default.contains('(')
271            && !final_default.starts_with('(')
272        {
273            format!("({})", final_default)
274        } else {
275            final_default
276        };
277
278        col.default(Into::<SimpleExpr>::into(sea_query::Expr::cust(
279            final_default,
280        )));
281    }
282
283    col
284}
285
286/// Generate CREATE TYPE SQL for an enum type (PostgreSQL only)
287/// Returns None for non-PostgreSQL backends or non-enum types
288///
289/// The enum type name will be prefixed with the table name to avoid conflicts
290/// across tables using the same enum name (e.g., "status", "gender").
291pub fn build_create_enum_type_sql(
292    table: &str,
293    column_type: &ColumnType,
294) -> Option<super::types::RawSql> {
295    if let ColumnType::Complex(ComplexColumnType::Enum { name, values }) = column_type {
296        // Integer enums don't need CREATE TYPE - they use INTEGER column
297        if values.is_integer() {
298            return None;
299        }
300
301        let values_sql = values.to_sql_values().join(", ");
302
303        // Generate unique type name with table prefix
304        let type_name = build_enum_type_name(table, name);
305
306        // PostgreSQL: CREATE TYPE {table}_{name} AS ENUM (...)
307        let pg_sql = format!("CREATE TYPE \"{}\" AS ENUM ({})", type_name, values_sql);
308
309        // MySQL: ENUMs are inline, no CREATE TYPE needed
310        // SQLite: Uses TEXT, no CREATE TYPE needed
311        Some(super::types::RawSql::per_backend(
312            pg_sql,
313            String::new(),
314            String::new(),
315        ))
316    } else {
317        None
318    }
319}
320
321/// Generate DROP TYPE SQL for an enum type (PostgreSQL only)
322/// Returns None for non-PostgreSQL backends or non-enum types
323///
324/// The enum type name will be prefixed with the table name to match the CREATE TYPE.
325pub fn build_drop_enum_type_sql(
326    table: &str,
327    column_type: &ColumnType,
328) -> Option<super::types::RawSql> {
329    if let ColumnType::Complex(ComplexColumnType::Enum { name, .. }) = column_type {
330        // Generate the same unique type name used in CREATE TYPE
331        let type_name = build_enum_type_name(table, name);
332
333        // PostgreSQL: DROP TYPE {table}_{name}
334        let pg_sql = format!("DROP TYPE \"{}\"", type_name);
335
336        // MySQL/SQLite: No action needed
337        Some(super::types::RawSql::per_backend(
338            pg_sql,
339            String::new(),
340            String::new(),
341        ))
342    } else {
343        None
344    }
345}
346
347// Re-export naming functions from vespertide-naming
348pub use vespertide_naming::{
349    build_check_constraint_name, build_enum_type_name, build_foreign_key_name, build_index_name,
350    build_unique_constraint_name,
351};
352
353/// Alias for build_check_constraint_name for SQLite enum columns
354pub fn build_sqlite_enum_check_name(table: &str, column: &str) -> String {
355    build_check_constraint_name(table, column)
356}
357
358/// Generate CHECK constraint expression for SQLite enum column
359/// Returns the constraint clause like: CONSTRAINT "chk_table_col" CHECK (col IN ('val1', 'val2'))
360pub fn build_sqlite_enum_check_clause(
361    table: &str,
362    column: &str,
363    column_type: &ColumnType,
364) -> Option<String> {
365    if let ColumnType::Complex(ComplexColumnType::Enum { values, .. }) = column_type {
366        let name = build_sqlite_enum_check_name(table, column);
367        let values_sql = values.to_sql_values().join(", ");
368        Some(format!(
369            "CONSTRAINT \"{}\" CHECK (\"{}\" IN ({}))",
370            name, column, values_sql
371        ))
372    } else {
373        None
374    }
375}
376
377/// Collect all CHECK constraints for enum columns in a table (for SQLite)
378pub fn collect_sqlite_enum_check_clauses(table: &str, columns: &[ColumnDef]) -> Vec<String> {
379    columns
380        .iter()
381        .filter_map(|col| build_sqlite_enum_check_clause(table, &col.name, &col.r#type))
382        .collect()
383}
384
385/// Extract CHECK constraint clauses from a list of table constraints.
386/// Returns SQL fragments like: `CONSTRAINT "chk_name" CHECK (expr)`
387pub fn extract_check_clauses(constraints: &[TableConstraint]) -> Vec<String> {
388    constraints
389        .iter()
390        .filter_map(|c| {
391            if let TableConstraint::Check { name, expr } = c {
392                Some(format!("CONSTRAINT \"{}\" CHECK ({})", name, expr))
393            } else {
394                None
395            }
396        })
397        .collect()
398}
399
400/// Collect ALL CHECK constraint clauses for a SQLite temp table.
401/// Combines both:
402/// - Enum-based CHECK constraints (from column types)
403/// - Explicit CHECK constraints (from `TableConstraint::Check`)
404///
405/// Returns deduplicated union of both.
406pub fn collect_all_check_clauses(
407    table: &str,
408    columns: &[ColumnDef],
409    constraints: &[TableConstraint],
410) -> Vec<String> {
411    let mut clauses = collect_sqlite_enum_check_clauses(table, columns);
412    let explicit = extract_check_clauses(constraints);
413    for clause in explicit {
414        if !clauses.contains(&clause) {
415            clauses.push(clause);
416        }
417    }
418    clauses
419}
420
421/// Build CREATE TABLE query with CHECK constraints properly embedded.
422/// sea-query doesn't support CHECK constraints natively, so we inject them
423/// by modifying the generated SQL string.
424pub fn build_create_with_checks(
425    backend: &DatabaseBackend,
426    create_stmt: &sea_query::TableCreateStatement,
427    check_clauses: &[String],
428) -> BuiltQuery {
429    if check_clauses.is_empty() {
430        BuiltQuery::CreateTable(Box::new(create_stmt.clone()))
431    } else {
432        let base_sql = build_schema_statement(create_stmt, *backend);
433        let mut modified_sql = base_sql;
434        if let Some(pos) = modified_sql.rfind(')') {
435            let check_sql = check_clauses.join(", ");
436            modified_sql.insert_str(pos, &format!(", {}", check_sql));
437        }
438        BuiltQuery::Raw(RawSql::per_backend(
439            modified_sql.clone(),
440            modified_sql.clone(),
441            modified_sql,
442        ))
443    }
444}
445
446/// Build the CREATE TABLE statement for a SQLite temp table, including all CHECK constraints.
447/// This combines `build_create_table_for_backend` with CHECK constraint injection.
448///
449/// `table` is the ORIGINAL table name (used for constraint naming).
450/// `temp_table` is the temporary table name.
451pub fn build_sqlite_temp_table_create(
452    backend: &DatabaseBackend,
453    temp_table: &str,
454    table: &str,
455    columns: &[ColumnDef],
456    constraints: &[TableConstraint],
457) -> BuiltQuery {
458    let create_stmt = build_create_table_for_backend(backend, temp_table, columns, constraints);
459    let check_clauses = collect_all_check_clauses(table, columns, constraints);
460    build_create_with_checks(backend, &create_stmt, &check_clauses)
461}
462
463/// Recreate all indexes (both regular and UNIQUE) after a SQLite temp table rebuild.
464/// After DROP TABLE + RENAME, all original indexes are gone, so plain CREATE INDEX is correct.
465///
466/// `pending_constraints` are constraints that exist in the logical schema but haven't been
467/// physically created yet (e.g., promoted from inline column definitions by AddColumn normalization).
468/// These will be created by separate AddConstraint actions later, so we must NOT recreate them here.
469pub fn recreate_indexes_after_rebuild(
470    table: &str,
471    constraints: &[TableConstraint],
472    pending_constraints: &[TableConstraint],
473) -> Vec<BuiltQuery> {
474    let mut queries = Vec::new();
475    for constraint in constraints {
476        // Skip constraints that will be created by future AddConstraint actions
477        if pending_constraints.contains(constraint) {
478            continue;
479        }
480        match constraint {
481            TableConstraint::Index { name, columns } => {
482                let index_name = build_index_name(table, columns, name.as_deref());
483                let cols_sql = columns
484                    .iter()
485                    .map(|c| format!("\"{}\"", c))
486                    .collect::<Vec<_>>()
487                    .join(", ");
488                let sql = format!(
489                    "CREATE INDEX \"{}\" ON \"{}\" ({})",
490                    index_name, table, cols_sql
491                );
492                queries.push(BuiltQuery::Raw(RawSql::per_backend(
493                    sql.clone(),
494                    sql.clone(),
495                    sql,
496                )));
497            }
498            TableConstraint::Unique { name, columns } => {
499                let index_name = build_unique_constraint_name(table, columns, name.as_deref());
500                let cols_sql = columns
501                    .iter()
502                    .map(|c| format!("\"{}\"", c))
503                    .collect::<Vec<_>>()
504                    .join(", ");
505                let sql = format!(
506                    "CREATE UNIQUE INDEX \"{}\" ON \"{}\" ({})",
507                    index_name, table, cols_sql
508                );
509                queries.push(BuiltQuery::Raw(RawSql::per_backend(
510                    sql.clone(),
511                    sql.clone(),
512                    sql,
513                )));
514            }
515            _ => {}
516        }
517    }
518    queries
519}
520
521/// Extract enum name from column type if it's an enum
522pub fn get_enum_name(column_type: &ColumnType) -> Option<&str> {
523    if let ColumnType::Complex(ComplexColumnType::Enum { name, .. }) = column_type {
524        Some(name.as_str())
525    } else {
526        None
527    }
528}
529
530#[cfg(test)]
531mod tests {
532    use super::*;
533    use rstest::rstest;
534    use sea_query::{Alias, ColumnDef as SeaColumnDef, ForeignKeyAction};
535    use vespertide_core::EnumValues;
536
537    #[rstest]
538    #[case(ColumnType::Simple(SimpleColumnType::Integer))]
539    #[case(ColumnType::Simple(SimpleColumnType::BigInt))]
540    #[case(ColumnType::Simple(SimpleColumnType::Text))]
541    #[case(ColumnType::Simple(SimpleColumnType::Boolean))]
542    #[case(ColumnType::Simple(SimpleColumnType::Timestamp))]
543    #[case(ColumnType::Simple(SimpleColumnType::Uuid))]
544    #[case(ColumnType::Complex(ComplexColumnType::Varchar { length: 255 }))]
545    #[case(ColumnType::Complex(ComplexColumnType::Numeric { precision: 10, scale: 2 }))]
546    fn test_column_type_conversion(#[case] ty: ColumnType) {
547        // Just ensure no panic - test by creating a column with this type
548        let mut col = SeaColumnDef::new(Alias::new("test"));
549        apply_column_type_with_table(&mut col, &ty, "test_table");
550    }
551
552    #[rstest]
553    #[case(SimpleColumnType::SmallInt)]
554    #[case(SimpleColumnType::Integer)]
555    #[case(SimpleColumnType::BigInt)]
556    #[case(SimpleColumnType::Real)]
557    #[case(SimpleColumnType::DoublePrecision)]
558    #[case(SimpleColumnType::Text)]
559    #[case(SimpleColumnType::Boolean)]
560    #[case(SimpleColumnType::Date)]
561    #[case(SimpleColumnType::Time)]
562    #[case(SimpleColumnType::Timestamp)]
563    #[case(SimpleColumnType::Timestamptz)]
564    #[case(SimpleColumnType::Interval)]
565    #[case(SimpleColumnType::Bytea)]
566    #[case(SimpleColumnType::Uuid)]
567    #[case(SimpleColumnType::Json)]
568    #[case(SimpleColumnType::Inet)]
569    #[case(SimpleColumnType::Cidr)]
570    #[case(SimpleColumnType::Macaddr)]
571    #[case(SimpleColumnType::Xml)]
572    fn test_all_simple_types_cover_branches(#[case] ty: SimpleColumnType) {
573        let mut col = SeaColumnDef::new(Alias::new("t"));
574        apply_column_type_with_table(&mut col, &ColumnType::Simple(ty), "test_table");
575    }
576
577    #[rstest]
578    #[case(ComplexColumnType::Varchar { length: 42 })]
579    #[case(ComplexColumnType::Numeric { precision: 8, scale: 3 })]
580    #[case(ComplexColumnType::Char { length: 3 })]
581    #[case(ComplexColumnType::Custom { custom_type: "GEOGRAPHY".into() })]
582    #[case(ComplexColumnType::Enum { name: "status".into(), values: EnumValues::String(vec!["active".into(), "inactive".into()]) })]
583    fn test_all_complex_types_cover_branches(#[case] ty: ComplexColumnType) {
584        let mut col = SeaColumnDef::new(Alias::new("t"));
585        apply_column_type_with_table(&mut col, &ColumnType::Complex(ty), "test_table");
586    }
587
588    #[rstest]
589    #[case::cascade(ReferenceAction::Cascade, ForeignKeyAction::Cascade)]
590    #[case::restrict(ReferenceAction::Restrict, ForeignKeyAction::Restrict)]
591    #[case::set_null(ReferenceAction::SetNull, ForeignKeyAction::SetNull)]
592    #[case::set_default(ReferenceAction::SetDefault, ForeignKeyAction::SetDefault)]
593    #[case::no_action(ReferenceAction::NoAction, ForeignKeyAction::NoAction)]
594    fn test_reference_action_conversion(
595        #[case] action: ReferenceAction,
596        #[case] expected: ForeignKeyAction,
597    ) {
598        // Just ensure the function doesn't panic and returns valid ForeignKeyAction
599        let result = to_sea_fk_action(&action);
600        assert!(
601            matches!(result, _expected),
602            "Expected {:?}, got {:?}",
603            expected,
604            result
605        );
606    }
607
608    #[rstest]
609    #[case(ReferenceAction::Cascade, "CASCADE")]
610    #[case(ReferenceAction::Restrict, "RESTRICT")]
611    #[case(ReferenceAction::SetNull, "SET NULL")]
612    #[case(ReferenceAction::SetDefault, "SET DEFAULT")]
613    #[case(ReferenceAction::NoAction, "NO ACTION")]
614    fn test_reference_action_sql_all_variants(
615        #[case] action: ReferenceAction,
616        #[case] expected: &str,
617    ) {
618        assert_eq!(reference_action_sql(&action), expected);
619    }
620
621    #[rstest]
622    #[case::gen_random_uuid_postgres(
623        "gen_random_uuid()",
624        DatabaseBackend::Postgres,
625        "gen_random_uuid()"
626    )]
627    #[case::gen_random_uuid_mysql("gen_random_uuid()", DatabaseBackend::MySql, "(UUID())")]
628    #[case::gen_random_uuid_sqlite(
629        "gen_random_uuid()",
630        DatabaseBackend::Sqlite,
631        "lower(hex(randomblob(16)))"
632    )]
633    #[case::current_timestamp_postgres(
634        "current_timestamp()",
635        DatabaseBackend::Postgres,
636        "CURRENT_TIMESTAMP"
637    )]
638    #[case::current_timestamp_mysql(
639        "current_timestamp()",
640        DatabaseBackend::MySql,
641        "CURRENT_TIMESTAMP"
642    )]
643    #[case::current_timestamp_sqlite(
644        "current_timestamp()",
645        DatabaseBackend::Sqlite,
646        "CURRENT_TIMESTAMP"
647    )]
648    #[case::now_postgres("now()", DatabaseBackend::Postgres, "CURRENT_TIMESTAMP")]
649    #[case::now_mysql("now()", DatabaseBackend::MySql, "CURRENT_TIMESTAMP")]
650    #[case::now_sqlite("now()", DatabaseBackend::Sqlite, "CURRENT_TIMESTAMP")]
651    #[case::now_upper_postgres("NOW()", DatabaseBackend::Postgres, "CURRENT_TIMESTAMP")]
652    #[case::now_upper_mysql("NOW()", DatabaseBackend::MySql, "CURRENT_TIMESTAMP")]
653    #[case::now_upper_sqlite("NOW()", DatabaseBackend::Sqlite, "CURRENT_TIMESTAMP")]
654    #[case::current_timestamp_upper_postgres(
655        "CURRENT_TIMESTAMP",
656        DatabaseBackend::Postgres,
657        "CURRENT_TIMESTAMP"
658    )]
659    #[case::current_timestamp_upper_mysql(
660        "CURRENT_TIMESTAMP",
661        DatabaseBackend::MySql,
662        "CURRENT_TIMESTAMP"
663    )]
664    #[case::current_timestamp_upper_sqlite(
665        "CURRENT_TIMESTAMP",
666        DatabaseBackend::Sqlite,
667        "CURRENT_TIMESTAMP"
668    )]
669    fn test_convert_default_for_backend(
670        #[case] default: &str,
671        #[case] backend: DatabaseBackend,
672        #[case] expected: &str,
673    ) {
674        let result = convert_default_for_backend(default, &backend);
675        assert_eq!(result, expected);
676    }
677
678    #[test]
679    fn test_is_enum_type_true() {
680        use vespertide_core::EnumValues;
681
682        let enum_type = ColumnType::Complex(ComplexColumnType::Enum {
683            name: "status".into(),
684            values: EnumValues::String(vec!["active".into(), "inactive".into()]),
685        });
686        assert!(is_enum_type(&enum_type));
687    }
688
689    #[test]
690    fn test_is_enum_type_false() {
691        let text_type = ColumnType::Simple(SimpleColumnType::Text);
692        assert!(!is_enum_type(&text_type));
693    }
694
695    #[test]
696    fn test_get_enum_name_some() {
697        use vespertide_core::EnumValues;
698
699        let enum_type = ColumnType::Complex(ComplexColumnType::Enum {
700            name: "user_status".into(),
701            values: EnumValues::String(vec!["active".into(), "inactive".into()]),
702        });
703        assert_eq!(get_enum_name(&enum_type), Some("user_status"));
704    }
705
706    #[test]
707    fn test_get_enum_name_none() {
708        let text_type = ColumnType::Simple(SimpleColumnType::Text);
709        assert_eq!(get_enum_name(&text_type), None);
710    }
711
712    #[test]
713    fn test_apply_column_type_integer_enum() {
714        use vespertide_core::{EnumValues, NumValue};
715        let integer_enum = ColumnType::Complex(ComplexColumnType::Enum {
716            name: "color".into(),
717            values: EnumValues::Integer(vec![
718                NumValue {
719                    name: "Black".into(),
720                    value: 0,
721                },
722                NumValue {
723                    name: "White".into(),
724                    value: 1,
725                },
726            ]),
727        });
728        let mut col = SeaColumnDef::new(Alias::new("color"));
729        apply_column_type_with_table(&mut col, &integer_enum, "test_table");
730        // Integer enums should use INTEGER type, not ENUM
731    }
732
733    #[test]
734    fn test_build_create_enum_type_sql_integer_enum_returns_none() {
735        use vespertide_core::{EnumValues, NumValue};
736        let integer_enum = ColumnType::Complex(ComplexColumnType::Enum {
737            name: "priority".into(),
738            values: EnumValues::Integer(vec![
739                NumValue {
740                    name: "Low".into(),
741                    value: 0,
742                },
743                NumValue {
744                    name: "High".into(),
745                    value: 10,
746                },
747            ]),
748        });
749        // Integer enums should return None (no CREATE TYPE needed)
750        assert!(build_create_enum_type_sql("test_table", &integer_enum).is_none());
751    }
752
753    #[rstest]
754    // Empty strings need quoting
755    #[case::empty("", true)]
756    #[case::whitespace_only("   ", true)]
757    // Function calls should not be quoted
758    #[case::now_func("now()", false)]
759    #[case::coalesce_func("COALESCE(old_value, 'default')", false)]
760    #[case::uuid_func("gen_random_uuid()", false)]
761    // NULL keyword should not be quoted
762    #[case::null_upper("NULL", false)]
763    #[case::null_lower("null", false)]
764    #[case::null_mixed("Null", false)]
765    // SQL date/time keywords should not be quoted
766    #[case::current_timestamp_upper("CURRENT_TIMESTAMP", false)]
767    #[case::current_timestamp_lower("current_timestamp", false)]
768    #[case::current_date_upper("CURRENT_DATE", false)]
769    #[case::current_date_lower("current_date", false)]
770    #[case::current_time_upper("CURRENT_TIME", false)]
771    #[case::current_time_lower("current_time", false)]
772    // Already quoted strings should not be re-quoted
773    #[case::single_quoted("'active'", false)]
774    #[case::double_quoted("\"active\"", false)]
775    // Plain strings need quoting
776    #[case::plain_active("active", true)]
777    #[case::plain_pending("pending", true)]
778    #[case::plain_underscore("some_value", true)]
779    fn test_needs_quoting(#[case] input: &str, #[case] expected: bool) {
780        assert_eq!(needs_quoting(input), expected);
781    }
782
783    #[test]
784    fn test_recreate_indexes_after_rebuild_skips_pending() {
785        use vespertide_core::TableConstraint;
786        let idx1 = TableConstraint::Index {
787            name: Some("idx_a".into()),
788            columns: vec!["a".into()],
789        };
790        let idx2 = TableConstraint::Index {
791            name: Some("idx_b".into()),
792            columns: vec!["b".into()],
793        };
794        let uq1 = TableConstraint::Unique {
795            name: Some("uq_c".into()),
796            columns: vec!["c".into()],
797        };
798
799        // All three in table constraints, but idx1 and uq1 are pending
800        let constraints = vec![idx1.clone(), idx2.clone(), uq1.clone()];
801        let pending = vec![idx1.clone(), uq1.clone()];
802
803        let queries = recreate_indexes_after_rebuild("t", &constraints, &pending);
804        // Only idx_b should be recreated
805        assert_eq!(queries.len(), 1);
806        let sql = queries[0].build(DatabaseBackend::Sqlite);
807        assert!(sql.contains("idx_b"));
808    }
809
810    #[test]
811    fn test_recreate_indexes_after_rebuild_no_pending() {
812        use vespertide_core::TableConstraint;
813        let idx = TableConstraint::Index {
814            name: Some("idx_a".into()),
815            columns: vec!["a".into()],
816        };
817        let uq = TableConstraint::Unique {
818            name: Some("uq_b".into()),
819            columns: vec!["b".into()],
820        };
821
822        let queries = recreate_indexes_after_rebuild("t", &[idx, uq], &[]);
823        assert_eq!(queries.len(), 2);
824    }
825
826    #[test]
827    fn test_recreate_indexes_after_rebuild_skips_non_index_constraints() {
828        use vespertide_core::TableConstraint;
829        let pk = TableConstraint::PrimaryKey {
830            columns: vec!["id".into()],
831            auto_increment: false,
832        };
833        let fk = TableConstraint::ForeignKey {
834            name: None,
835            columns: vec!["uid".into()],
836            ref_table: "u".into(),
837            ref_columns: vec!["id".into()],
838            on_delete: None,
839            on_update: None,
840        };
841        let chk = TableConstraint::Check {
842            name: "chk".into(),
843            expr: "id > 0".into(),
844        };
845
846        let queries = recreate_indexes_after_rebuild("t", &[pk, fk, chk], &[]);
847        assert_eq!(queries.len(), 0);
848    }
849}
vespertide_query/sql/helpers.rs

vespertide_query/sql/
helpers.rs
