vespertide_query/sql/
add_column.rs

1use sea_query::{Alias, Expr, Query, Table, TableAlterStatement};
2
3use vespertide_core::{ColumnDef, TableDef};
4
5use super::fill_with::convert_fill_with_for_backend;
6use super::helpers::{
7    build_create_enum_type_sql, build_sea_column_def_with_table, build_sqlite_temp_table_create,
8    convert_default_for_backend, normalize_enum_default, normalize_fill_with,
9    recreate_indexes_after_rebuild, require_table_in_schema,
10};
11use super::rename_table::build_rename_table;
12use super::types::{BuiltQuery, DatabaseBackend};
13use crate::error::QueryError;
14
15fn build_add_column_alter_for_backend(
16    backend: DatabaseBackend,
17    table: &str,
18    column: &ColumnDef,
19) -> TableAlterStatement {
20    let col_def = build_sea_column_def_with_table(backend, table, column);
21    Table::alter()
22        .table(Alias::new(table))
23        .add_column(col_def)
24        .to_owned()
25}
26
27/// Check if the column type is an enum
28fn is_enum_column(column: &ColumnDef) -> bool {
29    matches!(
30        column.r#type,
31        vespertide_core::ColumnType::Complex(vespertide_core::ComplexColumnType::Enum { .. })
32    )
33}
34
35pub fn build_add_column(
36    backend: DatabaseBackend,
37    table: &str,
38    column: &ColumnDef,
39    fill_with: Option<&str>,
40    current_schema: &[TableDef],
41    pending_constraints: &[vespertide_core::TableConstraint],
42) -> Result<Vec<BuiltQuery>, QueryError> {
43    // SQLite: NOT NULL additions or enum columns require table recreation
44    // (enum columns need CHECK constraint which requires table recreation in SQLite)
45    let sqlite_needs_recreation =
46        backend == DatabaseBackend::Sqlite && (!column.nullable || is_enum_column(column));
47
48    if sqlite_needs_recreation {
49        let table_def = require_table_in_schema(
50            current_schema,
51            table,
52            "SQLite requires current schema information to add columns",
53        )?;
54
55        let mut new_columns = table_def.columns.clone();
56        new_columns.push(column.clone());
57
58        let temp_table = format!("{table}_temp");
59
60        // 1. Create temporary table with all CHECK constraints (enum + explicit)
61        let create_query = build_sqlite_temp_table_create(
62            backend,
63            &temp_table,
64            table,
65            &new_columns,
66            &table_def.constraints,
67        );
68
69        // Copy existing data, filling new column. Build the existing-column
70        // aliases once and reuse them for both the SELECT column list and the
71        // INSERT column list (the new column's alias is appended for the INSERT
72        // only, since its value comes from `expr_as(fill_expr, ...)`).
73        let mut columns_alias: Vec<Alias> = Vec::with_capacity(table_def.columns.len() + 1);
74        let mut select_query = Query::select();
75        for col in &table_def.columns {
76            let alias = Alias::new(&col.name);
77            select_query.column(alias.clone());
78            columns_alias.push(alias);
79        }
80        let fill_expr = if let Some(fill) = normalize_fill_with(fill_with) {
81            let converted = convert_fill_with_for_backend(fill, backend);
82            Expr::cust(normalize_enum_default(&column.r#type, &converted))
83        } else if let Some(def) = &column.default {
84            let converted = convert_default_for_backend(&def.to_sql(), backend);
85            Expr::cust(normalize_enum_default(&column.r#type, &converted))
86        } else {
87            Expr::cust("NULL")
88        };
89        select_query
90            .expr_as(fill_expr, Alias::new(&column.name))
91            .from(Alias::new(table));
92
93        columns_alias.push(Alias::new(&column.name));
94        let insert_stmt = Query::insert()
95            .into_table(Alias::new(&temp_table))
96            .columns(columns_alias)
97            .select_from(select_query)
98            .expect("SQLite temp table copy SELECT should be valid")
99            .to_owned();
100        let insert_query = BuiltQuery::Insert(Box::new(insert_stmt));
101
102        let drop_query = super::delete_table::build_delete_table(table);
103        let rename_query = build_rename_table(&temp_table, table);
104
105        // Recreate indexes (both regular and UNIQUE)
106        // Skip pending constraints that will be created by future AddConstraint actions
107        let index_queries =
108            recreate_indexes_after_rebuild(table, &table_def.constraints, pending_constraints);
109
110        let mut stmts = vec![create_query, insert_query, drop_query, rename_query];
111        stmts.extend(index_queries);
112        return Ok(stmts);
113    }
114
115    let mut stmts: Vec<BuiltQuery> = Vec::new();
116
117    // If column type is an enum, create the type first (PostgreSQL only)
118    if let Some(create_type_sql) = build_create_enum_type_sql(table, &column.r#type) {
119        stmts.push(BuiltQuery::Raw(create_type_sql));
120    }
121
122    // If adding NOT NULL without default, we need special handling
123    let needs_backfill = !column.nullable && column.default.is_none() && fill_with.is_some();
124
125    if needs_backfill {
126        // Add as nullable first
127        let mut temp_col = column.clone();
128        temp_col.nullable = true;
129
130        stmts.push(BuiltQuery::AlterTable(Box::new(
131            build_add_column_alter_for_backend(backend, table, &temp_col),
132        )));
133
134        // Backfill with provided value
135        if let Some(fill) = normalize_fill_with(fill_with) {
136            let fill = convert_fill_with_for_backend(fill, backend);
137            let update_stmt = Query::update()
138                .table(Alias::new(table))
139                .value(Alias::new(&column.name), Expr::cust(fill))
140                .to_owned();
141            stmts.push(BuiltQuery::Update(Box::new(update_stmt)));
142        }
143
144        // Set NOT NULL
145        let not_null_col = build_sea_column_def_with_table(backend, table, column);
146        let alter_not_null = Table::alter()
147            .table(Alias::new(table))
148            .modify_column(not_null_col)
149            .to_owned();
150        stmts.push(BuiltQuery::AlterTable(Box::new(alter_not_null)));
151    } else {
152        stmts.push(BuiltQuery::AlterTable(Box::new(
153            build_add_column_alter_for_backend(backend, table, column),
154        )));
155    }
156
157    Ok(stmts)
158}
159
160#[cfg(test)]
161mod tests {
162    use super::*;
163    use crate::test_support::{backend_tag, joined_sql, joined_sql_semicolon};
164    use insta::{assert_snapshot, with_settings};
165    use rstest::rstest;
166    use vespertide_core::{ColumnType, SimpleColumnType, TableDef};
167
168    #[rstest]
169    #[case::add_column_with_backfill_postgres(
170        "add_column_with_backfill_postgres",
171        DatabaseBackend::Postgres,
172        &["ALTER TABLE \"users\" ADD COLUMN \"nickname\" text"]
173    )]
174    #[case::add_column_with_backfill_mysql(
175        "add_column_with_backfill_mysql",
176        DatabaseBackend::MySql,
177        &["ALTER TABLE `users` ADD COLUMN `nickname` text"]
178    )]
179    #[case::add_column_with_backfill_sqlite(
180        "add_column_with_backfill_sqlite",
181        DatabaseBackend::Sqlite,
182        &["CREATE TABLE \"users_temp\""]
183    )]
184    #[case::add_column_simple_postgres(
185        "add_column_simple_postgres",
186        DatabaseBackend::Postgres,
187        &["ALTER TABLE \"users\" ADD COLUMN \"nickname\""]
188    )]
189    #[case::add_column_simple_mysql(
190        "add_column_simple_mysql",
191        DatabaseBackend::MySql,
192        &["ALTER TABLE `users` ADD COLUMN `nickname` text"]
193    )]
194    #[case::add_column_simple_sqlite(
195        "add_column_simple_sqlite",
196        DatabaseBackend::Sqlite,
197        &["ALTER TABLE \"users\" ADD COLUMN \"nickname\""]
198    )]
199    #[case::add_column_nullable_postgres(
200        "add_column_nullable_postgres",
201        DatabaseBackend::Postgres,
202        &["ALTER TABLE \"users\" ADD COLUMN \"email\" text"]
203    )]
204    #[case::add_column_nullable_mysql(
205        "add_column_nullable_mysql",
206        DatabaseBackend::MySql,
207        &["ALTER TABLE `users` ADD COLUMN `email` text"]
208    )]
209    #[case::add_column_nullable_sqlite(
210        "add_column_nullable_sqlite",
211        DatabaseBackend::Sqlite,
212        &["ALTER TABLE \"users\" ADD COLUMN \"email\" text"]
213    )]
214    fn test_add_column(
215        #[case] title: &str,
216        #[case] backend: DatabaseBackend,
217        #[case] expected: &[&str],
218    ) {
219        let column = ColumnDef {
220            name: if title.contains("age") {
221                "age"
222            } else if title.contains("nullable") {
223                "email"
224            } else {
225                "nickname"
226            }
227            .into(),
228            r#type: if title.contains("age") {
229                ColumnType::Simple(SimpleColumnType::Integer)
230            } else {
231                ColumnType::Simple(SimpleColumnType::Text)
232            },
233            nullable: !title.contains("backfill"),
234            default: None,
235            comment: None,
236            primary_key: None,
237            unique: None,
238            index: None,
239            foreign_key: None,
240        };
241        let fill_with = if title.contains("backfill") {
242            Some("0")
243        } else {
244            None
245        };
246        let current_schema = vec![TableDef {
247            name: "users".into(),
248            description: None,
249            columns: vec![ColumnDef {
250                name: "id".into(),
251                r#type: ColumnType::Simple(SimpleColumnType::Integer),
252                nullable: false,
253                default: None,
254                comment: None,
255                primary_key: None,
256                unique: None,
257                index: None,
258                foreign_key: None,
259            }],
260            constraints: vec![],
261        }];
262        let result =
263            build_add_column(backend, "users", &column, fill_with, &current_schema, &[]).unwrap();
264        let sql = result[0].build(backend);
265        for exp in expected {
266            assert!(
267                sql.contains(exp),
268                "Expected SQL to contain '{exp}', got: {sql}"
269            );
270        }
271
272        with_settings!({ snapshot_suffix => format!("add_column_{}", title) }, {
273            assert_snapshot!(joined_sql(backend, &result));
274        });
275    }
276
277    #[test]
278    fn test_add_column_sqlite_table_not_found() {
279        let column = ColumnDef {
280            name: "nickname".into(),
281            r#type: ColumnType::Simple(SimpleColumnType::Text),
282            nullable: false,
283            default: None,
284            comment: None,
285            primary_key: None,
286            unique: None,
287            index: None,
288            foreign_key: None,
289        };
290        let current_schema = vec![]; // Empty schema - table not found
291        let result = build_add_column(
292            DatabaseBackend::Sqlite,
293            "users",
294            &column,
295            None,
296            &current_schema,
297            &[],
298        );
299        assert!(result.is_err());
300        let err_msg = result.unwrap_err().to_string();
301        assert!(err_msg.contains("Table 'users' not found in current schema"));
302    }
303
304    #[test]
305    fn test_add_column_sqlite_with_default() {
306        let column = ColumnDef {
307            name: "age".into(),
308            r#type: ColumnType::Simple(SimpleColumnType::Integer),
309            nullable: false,
310            default: Some("18".into()),
311            comment: None,
312            primary_key: None,
313            unique: None,
314            index: None,
315            foreign_key: None,
316        };
317        let current_schema = vec![TableDef {
318            name: "users".into(),
319            description: None,
320            columns: vec![ColumnDef {
321                name: "id".into(),
322                r#type: ColumnType::Simple(SimpleColumnType::Integer),
323                nullable: false,
324                default: None,
325                comment: None,
326                primary_key: None,
327                unique: None,
328                index: None,
329                foreign_key: None,
330            }],
331            constraints: vec![],
332        }];
333        let result = build_add_column(
334            DatabaseBackend::Sqlite,
335            "users",
336            &column,
337            None,
338            &current_schema,
339            &[],
340        );
341        assert!(result.is_ok());
342        let queries = result.unwrap();
343        let sql = joined_sql(DatabaseBackend::Sqlite, &queries);
344        // Should use default value (18) for fill
345        assert!(sql.contains("18"));
346    }
347
348    #[test]
349    fn test_add_column_sqlite_without_fill_or_default() {
350        let column = ColumnDef {
351            name: "age".into(),
352            r#type: ColumnType::Simple(SimpleColumnType::Integer),
353            nullable: false,
354            default: None,
355            comment: None,
356            primary_key: None,
357            unique: None,
358            index: None,
359            foreign_key: None,
360        };
361        let current_schema = vec![TableDef {
362            name: "users".into(),
363            description: None,
364            columns: vec![ColumnDef {
365                name: "id".into(),
366                r#type: ColumnType::Simple(SimpleColumnType::Integer),
367                nullable: false,
368                default: None,
369                comment: None,
370                primary_key: None,
371                unique: None,
372                index: None,
373                foreign_key: None,
374            }],
375            constraints: vec![],
376        }];
377        let result = build_add_column(
378            DatabaseBackend::Sqlite,
379            "users",
380            &column,
381            None,
382            &current_schema,
383            &[],
384        );
385        assert!(result.is_ok());
386        let queries = result.unwrap();
387        let sql = joined_sql(DatabaseBackend::Sqlite, &queries);
388        // Should use NULL for fill
389        assert!(sql.contains("NULL"));
390    }
391
392    #[test]
393    fn test_add_column_sqlite_with_indexes() {
394        use vespertide_core::TableConstraint;
395
396        let column = ColumnDef {
397            name: "nickname".into(),
398            r#type: ColumnType::Simple(SimpleColumnType::Text),
399            nullable: false,
400            default: None,
401            comment: None,
402            primary_key: None,
403            unique: None,
404            index: None,
405            foreign_key: None,
406        };
407        let current_schema = vec![TableDef {
408            name: "users".into(),
409            description: None,
410            columns: vec![ColumnDef {
411                name: "id".into(),
412                r#type: ColumnType::Simple(SimpleColumnType::Integer),
413                nullable: false,
414                default: None,
415                comment: None,
416                primary_key: None,
417                unique: None,
418                index: None,
419                foreign_key: None,
420            }],
421            constraints: vec![TableConstraint::Index {
422                name: Some("idx_id".into()),
423                columns: vec!["id".into()],
424            }],
425        }];
426        let result = build_add_column(
427            DatabaseBackend::Sqlite,
428            "users",
429            &column,
430            None,
431            &current_schema,
432            &[],
433        );
434        assert!(result.is_ok());
435        let queries = result.unwrap();
436        let sql = joined_sql(DatabaseBackend::Sqlite, &queries);
437        // Should recreate index
438        assert!(sql.contains("CREATE INDEX"));
439        assert!(sql.contains("idx_id"));
440    }
441
442    #[rstest]
443    #[case::add_column_with_enum_type_postgres(DatabaseBackend::Postgres)]
444    #[case::add_column_with_enum_type_mysql(DatabaseBackend::MySql)]
445    #[case::add_column_with_enum_type_sqlite(DatabaseBackend::Sqlite)]
446    fn test_add_column_with_enum_type(#[case] backend: DatabaseBackend) {
447        use insta::{assert_snapshot, with_settings};
448        use vespertide_core::{ComplexColumnType, EnumValues};
449
450        // Test that adding an enum column creates the enum type first (PostgreSQL only)
451        let column = ColumnDef {
452            name: "status".into(),
453            r#type: ColumnType::Complex(ComplexColumnType::Enum {
454                name: "status_type".into(),
455                values: EnumValues::String(vec!["active".into(), "inactive".into()]),
456            }),
457            nullable: true,
458            default: None,
459            comment: None,
460            primary_key: None,
461            unique: None,
462            index: None,
463            foreign_key: None,
464        };
465        let current_schema = vec![TableDef {
466            name: "users".into(),
467            description: None,
468            columns: vec![ColumnDef {
469                name: "id".into(),
470                r#type: ColumnType::Simple(SimpleColumnType::Integer),
471                nullable: false,
472                default: None,
473                comment: None,
474                primary_key: None,
475                unique: None,
476                index: None,
477                foreign_key: None,
478            }],
479            constraints: vec![],
480        }];
481        let result = build_add_column(backend, "users", &column, None, &current_schema, &[]);
482        assert!(result.is_ok());
483        let queries = result.unwrap();
484        let sql = joined_sql_semicolon(backend, &queries);
485
486        with_settings!({ snapshot_suffix => format!("add_column_with_enum_type_{:?}", backend) }, {
487            assert_snapshot!(sql);
488        });
489    }
490
491    #[rstest]
492    #[case::postgres(DatabaseBackend::Postgres)]
493    #[case::mysql(DatabaseBackend::MySql)]
494    #[case::sqlite(DatabaseBackend::Sqlite)]
495    fn test_add_column_enum_non_nullable_with_default(#[case] backend: DatabaseBackend) {
496        use insta::{assert_snapshot, with_settings};
497        use vespertide_core::{ComplexColumnType, EnumValues};
498
499        // Test adding an enum column that is non-nullable with a default value
500        let column = ColumnDef {
501            name: "status".into(),
502            r#type: ColumnType::Complex(ComplexColumnType::Enum {
503                name: "user_status".into(),
504                values: EnumValues::String(vec![
505                    "active".into(),
506                    "inactive".into(),
507                    "pending".into(),
508                ]),
509            }),
510            nullable: false,
511            default: Some("active".into()),
512            comment: None,
513            primary_key: None,
514            unique: None,
515            index: None,
516            foreign_key: None,
517        };
518        let current_schema = vec![TableDef {
519            name: "users".into(),
520            description: None,
521            columns: vec![ColumnDef {
522                name: "id".into(),
523                r#type: ColumnType::Simple(SimpleColumnType::Integer),
524                nullable: false,
525                default: None,
526                comment: None,
527                primary_key: None,
528                unique: None,
529                index: None,
530                foreign_key: None,
531            }],
532            constraints: vec![],
533        }];
534        let result = build_add_column(backend, "users", &column, None, &current_schema, &[]);
535        assert!(result.is_ok());
536        let queries = result.unwrap();
537        let sql = joined_sql_semicolon(backend, &queries);
538
539        with_settings!({ snapshot_suffix => format!("enum_non_nullable_with_default_{:?}", backend) }, {
540            assert_snapshot!(sql);
541        });
542    }
543
544    #[rstest]
545    #[case::postgres(DatabaseBackend::Postgres)]
546    #[case::mysql(DatabaseBackend::MySql)]
547    #[case::sqlite(DatabaseBackend::Sqlite)]
548    fn test_add_column_with_empty_string_default(#[case] backend: DatabaseBackend) {
549        use insta::{assert_snapshot, with_settings};
550
551        // Test adding a text column with empty string default
552        let column = ColumnDef {
553            name: "nickname".into(),
554            r#type: ColumnType::Simple(SimpleColumnType::Text),
555            nullable: false,
556            default: Some("".into()), // Empty string default
557            comment: None,
558            primary_key: None,
559            unique: None,
560            index: None,
561            foreign_key: None,
562        };
563        let current_schema = vec![TableDef {
564            name: "users".into(),
565            description: None,
566            columns: vec![ColumnDef {
567                name: "id".into(),
568                r#type: ColumnType::Simple(SimpleColumnType::Integer),
569                nullable: false,
570                default: None,
571                comment: None,
572                primary_key: None,
573                unique: None,
574                index: None,
575                foreign_key: None,
576            }],
577            constraints: vec![],
578        }];
579        let result = build_add_column(backend, "users", &column, None, &current_schema, &[]);
580        assert!(result.is_ok());
581        let queries = result.unwrap();
582        let sql = joined_sql_semicolon(backend, &queries);
583
584        // Verify empty string becomes ''
585        assert!(
586            sql.contains("''"),
587            "Expected SQL to contain empty string literal '', got: {sql}"
588        );
589
590        with_settings!({ snapshot_suffix => format!("empty_string_default_{:?}", backend) }, {
591            assert_snapshot!(sql);
592        });
593    }
594
595    /// Test adding NOT NULL column with '[]'`::json` default on `SQLite`
596    /// `SQLite` should strip the `::json` cast, `MySQL` should use CAST(... AS JSON)
597    #[rstest]
598    #[case::postgres(DatabaseBackend::Postgres)]
599    #[case::mysql(DatabaseBackend::MySql)]
600    #[case::sqlite(DatabaseBackend::Sqlite)]
601    fn test_add_column_with_pg_type_cast_default(#[case] backend: DatabaseBackend) {
602        let column = ColumnDef {
603            name: "story_index".into(),
604            r#type: ColumnType::Simple(SimpleColumnType::Json),
605            nullable: false,
606            default: Some("'[]'::json".into()),
607            comment: None,
608            primary_key: None,
609            unique: None,
610            index: None,
611            foreign_key: None,
612        };
613        let current_schema = vec![TableDef {
614            name: "project".into(),
615            description: None,
616            columns: vec![ColumnDef {
617                name: "id".into(),
618                r#type: ColumnType::Simple(SimpleColumnType::Integer),
619                nullable: false,
620                default: None,
621                comment: None,
622                primary_key: None,
623                unique: None,
624                index: None,
625                foreign_key: None,
626            }],
627            constraints: vec![],
628        }];
629        let result =
630            build_add_column(backend, "project", &column, None, &current_schema, &[]).unwrap();
631        let sql = joined_sql(backend, &result);
632
633        // SQLite must NOT contain ::json syntax
634        if backend == DatabaseBackend::Sqlite {
635            assert!(
636                !sql.contains("::json"),
637                "SQLite SQL should not contain ::json cast, got: {sql}"
638            );
639        }
640
641        // MySQL should use CAST syntax
642        if backend == DatabaseBackend::MySql {
643            assert!(
644                !sql.contains("::json"),
645                "MySQL SQL should not contain ::json cast, got: {sql}"
646            );
647        }
648
649        with_settings!({ snapshot_suffix => format!("pg_type_cast_default_{:?}", backend) }, {
650            assert_snapshot!(sql);
651        });
652    }
653
654    #[rstest]
655    #[case::postgres(DatabaseBackend::Postgres)]
656    #[case::mysql(DatabaseBackend::MySql)]
657    #[case::sqlite(DatabaseBackend::Sqlite)]
658    fn test_add_column_with_fill_with_empty_string(#[case] backend: DatabaseBackend) {
659        use insta::{assert_snapshot, with_settings};
660
661        // Test adding a column with fill_with as empty string
662        let column = ColumnDef {
663            name: "nickname".into(),
664            r#type: ColumnType::Simple(SimpleColumnType::Text),
665            nullable: false,
666            default: None,
667            comment: None,
668            primary_key: None,
669            unique: None,
670            index: None,
671            foreign_key: None,
672        };
673        let current_schema = vec![TableDef {
674            name: "users".into(),
675            description: None,
676            columns: vec![ColumnDef {
677                name: "id".into(),
678                r#type: ColumnType::Simple(SimpleColumnType::Integer),
679                nullable: false,
680                default: None,
681                comment: None,
682                primary_key: None,
683                unique: None,
684                index: None,
685                foreign_key: None,
686            }],
687            constraints: vec![],
688        }];
689        // fill_with empty string should become ''
690        let result = build_add_column(backend, "users", &column, Some(""), &current_schema, &[]);
691        assert!(result.is_ok());
692        let queries = result.unwrap();
693        let sql = joined_sql_semicolon(backend, &queries);
694
695        // Verify empty string becomes ''
696        assert!(
697            sql.contains("''"),
698            "Expected SQL to contain empty string literal '', got: {sql}"
699        );
700
701        with_settings!({ snapshot_suffix => format!("fill_with_empty_string_{:?}", backend) }, {
702            assert_snapshot!(sql);
703        });
704    }
705
706    fn backfill_sql(backend: DatabaseBackend, column: &ColumnDef, fill: &str) -> String {
707        use crate::test_support::{col_n, table_def};
708
709        let current_schema = vec![table_def(
710            "subscription",
711            vec![
712                col_n("id", ColumnType::Simple(SimpleColumnType::Integer), false),
713                col_n(
714                    "plan_key",
715                    ColumnType::Simple(SimpleColumnType::Text),
716                    false,
717                ),
718                col_n(
719                    "plan_tag",
720                    ColumnType::Simple(SimpleColumnType::Text),
721                    false,
722                ),
723                col_n(
724                    "device_os",
725                    ColumnType::Simple(SimpleColumnType::Text),
726                    false,
727                ),
728                col_n(
729                    "device_family",
730                    ColumnType::Simple(SimpleColumnType::Text),
731                    false,
732                ),
733            ],
734            vec![],
735        )];
736        let queries = build_add_column(
737            backend,
738            "subscription",
739            column,
740            Some(fill),
741            &current_schema,
742            &[],
743        )
744        .expect("add_column with fill_with should build");
745        joined_sql_semicolon(backend, &queries)
746    }
747
748    fn not_null_column(name: &str, r#type: ColumnType) -> ColumnDef {
749        ColumnDef {
750            name: name.into(),
751            r#type,
752            nullable: false,
753            default: None,
754            comment: None,
755            primary_key: None,
756            unique: None,
757            index: None,
758            foreign_key: None,
759        }
760    }
761
762    /// Regression: a `fill_with` CASE expression comparing a text-cast column
763    /// to the uppercase literal `API` and returning `MONTHLY_QUOTA` / `SEAT`,
764    /// wrapped in parens and cast to an enum type.
765    ///
766    /// Splitting at the *first* `::` and lower-casing the remainder produced
767    /// `'api'` / `'monthly_quota'` / `'seat'`: the comparison never matched, so
768    /// the backfill silently did nothing, and the lower-cased token was not a
769    /// valid enum label so the cast failed.
770    #[rstest]
771    #[case::postgres(DatabaseBackend::Postgres)]
772    #[case::mysql(DatabaseBackend::MySql)]
773    #[case::sqlite(DatabaseBackend::Sqlite)]
774    fn fill_with_enum_cast_case_expression_is_verbatim(#[case] backend: DatabaseBackend) {
775        use vespertide_core::{ComplexColumnType, EnumValues};
776
777        const FILL: &str = "(CASE WHEN plan_key::text = 'API' THEN 'MONTHLY_QUOTA' ELSE 'SEAT' END)::billing_metric";
778
779        let column = not_null_column(
780            "metric",
781            ColumnType::Complex(ComplexColumnType::Enum {
782                name: "billing_metric".into(),
783                values: EnumValues::String(vec!["MONTHLY_QUOTA".into(), "SEAT".into()]),
784            }),
785        );
786        let sql = backfill_sql(backend, &column, FILL);
787
788        assert!(
789            sql.contains(FILL),
790            "fill_with must survive byte-for-byte, got: {sql}"
791        );
792
793        with_settings!({ snapshot_suffix => format!("fill_with_enum_cast_verbatim_{}", backend_tag(backend)) }, {
794            assert_snapshot!(sql);
795        });
796    }
797
798    /// Regression: uppercase `WINDOWS` sits *before* the first cast operator
799    /// and survived, while the `ELSE` / `END` keywords *after* it were
800    /// lower-cased — the observation that pinpointed the first-`::` split.
801    #[rstest]
802    #[case::postgres(DatabaseBackend::Postgres)]
803    #[case::mysql(DatabaseBackend::MySql)]
804    #[case::sqlite(DatabaseBackend::Sqlite)]
805    fn fill_with_json_array_case_expression_is_verbatim(#[case] backend: DatabaseBackend) {
806        const FILL: &str = "CASE WHEN device_os = 'win' THEN json_build_array('WINDOWS', device_family::text) ELSE '[]'::json END";
807
808        let column = not_null_column("os_tags", ColumnType::Simple(SimpleColumnType::Json));
809        let sql = backfill_sql(backend, &column, FILL);
810
811        assert!(
812            sql.contains(FILL),
813            "fill_with must survive byte-for-byte, got: {sql}"
814        );
815
816        with_settings!({ snapshot_suffix => format!("fill_with_json_array_verbatim_{}", backend_tag(backend)) }, {
817            assert_snapshot!(sql);
818        });
819    }
820
821    /// Regression: the comparison literal itself contains a cast operator
822    /// inside single quotes, followed by a trailing cast to integer. Splitting
823    /// on the first `::` cut the statement open inside the string literal.
824    #[rstest]
825    #[case::postgres(DatabaseBackend::Postgres)]
826    #[case::mysql(DatabaseBackend::MySql)]
827    #[case::sqlite(DatabaseBackend::Sqlite)]
828    fn fill_with_cast_operator_inside_quotes_is_verbatim(#[case] backend: DatabaseBackend) {
829        const FILL: &str = "CASE WHEN plan_tag = 'legacy::v1' THEN 1 ELSE 2 END::integer";
830
831        let column = not_null_column("tier", ColumnType::Simple(SimpleColumnType::Integer));
832        let sql = backfill_sql(backend, &column, FILL);
833
834        assert!(
835            sql.contains(FILL),
836            "fill_with must survive byte-for-byte, got: {sql}"
837        );
838
839        with_settings!({ snapshot_suffix => format!("fill_with_quoted_cast_verbatim_{}", backend_tag(backend)) }, {
840            assert_snapshot!(sql);
841        });
842    }
843}
vespertide_query/sql/add_column.rs

vespertide_query/sql/
add_column.rs
