vespertide_query/sql/
modify_column_default.rs

1use sea_query::{Alias, Query, Table};
2
3use vespertide_core::{ColumnDef, TableDef};
4
5use super::helpers::{
6    build_sea_column_def_with_table, build_sqlite_temp_table_create, normalize_enum_default,
7    quote_ident, recreate_indexes_after_rebuild,
8};
9use super::rename_table::build_rename_table;
10use super::types::{BuiltQuery, DatabaseBackend, RawSql};
11use crate::error::QueryError;
12
13/// Build SQL for changing column default value.
14///
15/// When `backfill` is `Some(value)`, an `UPDATE` statement is appended after
16/// the schema-level change so every existing row is rewritten to the given
17/// value (F15 backfill option β). The update uses identifier quoting
18/// appropriate for the backend and treats `value` as a raw SQL expression
19/// (already-quoted literals for strings, bare expressions like `NOW()` for
20/// functions). When `backfill` is `None` the action behaves exactly as in
21/// v0.2.0 — only the schema is touched, existing rows keep their values.
22#[expect(
23    clippy::too_many_lines,
24    reason = "three-backend dispatch (PG / MySQL / SQLite) plus optional backfill UPDATE; splitting per-backend helpers scatters the read flow"
25)]
26pub fn build_modify_column_default(
27    backend: DatabaseBackend,
28    table: &str,
29    column: &str,
30    new_default: Option<&str>,
31    backfill: Option<&str>,
32    current_schema: &[TableDef],
33    pending_constraints: &[vespertide_core::TableConstraint],
34) -> Result<Vec<BuiltQuery>, QueryError> {
35    let mut queries = Vec::new();
36
37    match backend {
38        DatabaseBackend::Postgres => {
39            let quoted_table = quote_ident(table, backend);
40            let quoted_column = quote_ident(column, backend);
41            let alter_sql = if let Some(default_value) = new_default {
42                // Look up column type to properly quote enum defaults
43                let column_type = current_schema
44                    .iter()
45                    .find(|t| t.name == table)
46                    .and_then(|t| t.columns.iter().find(|c| c.name == column))
47                    .map(|c| &c.r#type);
48
49                let normalized_default = if let Some(col_type) = column_type {
50                    normalize_enum_default(col_type, default_value)
51                } else {
52                    default_value.to_string()
53                };
54
55                format!(
56                    "ALTER TABLE {quoted_table} ALTER COLUMN {quoted_column} SET DEFAULT {normalized_default}"
57                )
58            } else {
59                format!("ALTER TABLE {quoted_table} ALTER COLUMN {quoted_column} DROP DEFAULT")
60            };
61            queries.push(BuiltQuery::Raw(RawSql::uniform(alter_sql)));
62        }
63        DatabaseBackend::MySql => {
64            // MySQL requires the full column definition in ALTER COLUMN
65            let table_def = current_schema
66                .iter()
67                .find(|t| t.name == table)
68                .ok_or_else(|| {
69                    QueryError::SchemaError(format!("Table '{table}' not found in current schema."))
70                })?;
71
72            let column_def = table_def
73                .columns
74                .iter()
75                .find(|c| c.name == column)
76                .ok_or_else(|| {
77                    QueryError::SchemaError(format!(
78                        "Column '{column}' not found in table '{table}'."
79                    ))
80                })?;
81
82            // Create a modified column def with the new default
83            let modified_col_def = ColumnDef {
84                default: new_default.map(std::convert::Into::into),
85                ..column_def.clone()
86            };
87
88            let sea_col = build_sea_column_def_with_table(backend, table, &modified_col_def);
89
90            let stmt = Table::alter()
91                .table(Alias::new(table))
92                .modify_column(sea_col)
93                .to_owned();
94            queries.push(BuiltQuery::AlterTable(Box::new(stmt)));
95        }
96        DatabaseBackend::Sqlite => {
97            // SQLite doesn't support ALTER COLUMN for default changes
98            // Use temporary table approach
99            let table_def = current_schema
100                .iter()
101                .find(|t| t.name == table)
102                .ok_or_else(|| {
103                    QueryError::SchemaError(format!("Table '{table}' not found in current schema."))
104                })?;
105
106            // Create modified columns with the new default
107            let mut new_columns = table_def.columns.clone();
108            if let Some(col) = new_columns.iter_mut().find(|c| c.name == column) {
109                col.default = new_default.map(std::convert::Into::into);
110            }
111
112            // Generate temporary table name
113            let temp_table = format!("{table}_temp");
114
115            // 1. Create temporary table with modified column + CHECK constraints
116            let create_query = build_sqlite_temp_table_create(
117                backend,
118                &temp_table,
119                table,
120                &new_columns,
121                &table_def.constraints,
122            );
123            queries.push(create_query);
124
125            // 2. Copy data (all columns)
126            let column_aliases: Vec<Alias> = table_def
127                .columns
128                .iter()
129                .map(|c| Alias::new(&c.name))
130                .collect();
131            let mut select_query = Query::select();
132            for col_alias in &column_aliases {
133                select_query.column(col_alias.clone());
134            }
135            select_query.from(Alias::new(table));
136
137            let insert_stmt = Query::insert()
138                .into_table(Alias::new(&temp_table))
139                .columns(column_aliases.clone())
140                .select_from(select_query)
141                .unwrap()
142                .to_owned();
143            queries.push(BuiltQuery::Insert(Box::new(insert_stmt)));
144
145            // 3. Drop original table
146            let drop_table = Table::drop().table(Alias::new(table)).to_owned();
147            queries.push(BuiltQuery::DropTable(Box::new(drop_table)));
148
149            // 4. Rename temporary table to original name
150            queries.push(build_rename_table(&temp_table, table));
151
152            // 5. Recreate indexes (both regular and UNIQUE)
153            queries.extend(recreate_indexes_after_rebuild(
154                table,
155                &table_def.constraints,
156                pending_constraints,
157            ));
158        }
159    }
160
161    // F15 — backfill existing rows when the user explicitly opted in via
162    // the revision prompt. The schema-level change above only affects new
163    // rows; this UPDATE is what brings existing rows in line with the new
164    // default. Emitted *after* the ALTER so the new default is the one
165    // recorded in the catalog before we touch any row.
166    if let Some(value) = backfill {
167        let quoted_table = quote_ident(table, backend);
168        let quoted_column = quote_ident(column, backend);
169        let update_sql = format!("UPDATE {quoted_table} SET {quoted_column} = {value}");
170        queries.push(BuiltQuery::Raw(RawSql::uniform(update_sql)));
171    }
172
173    Ok(queries)
174}
175
176#[cfg(test)]
177mod tests {
178    use super::*;
179    use insta::{assert_snapshot, with_settings};
180    use rstest::rstest;
181    use vespertide_core::{ColumnDef, ColumnType, SimpleColumnType, TableConstraint};
182
183    fn col(name: &str, ty: ColumnType, nullable: bool) -> ColumnDef {
184        ColumnDef::new(name, ty, nullable)
185    }
186
187    fn table_def(
188        name: &str,
189        columns: Vec<ColumnDef>,
190        constraints: Vec<TableConstraint>,
191    ) -> TableDef {
192        TableDef {
193            name: name.into(),
194            description: None,
195            columns,
196            constraints,
197        }
198    }
199
200    #[rstest]
201    #[case::postgres_set_default(DatabaseBackend::Postgres, Some("'unknown'"))]
202    #[case::postgres_drop_default(DatabaseBackend::Postgres, None)]
203    #[case::mysql_set_default(DatabaseBackend::MySql, Some("'unknown'"))]
204    #[case::mysql_drop_default(DatabaseBackend::MySql, None)]
205    #[case::sqlite_set_default(DatabaseBackend::Sqlite, Some("'unknown'"))]
206    #[case::sqlite_drop_default(DatabaseBackend::Sqlite, None)]
207    fn test_build_modify_column_default(
208        #[case] backend: DatabaseBackend,
209        #[case] new_default: Option<&str>,
210    ) {
211        let schema = vec![table_def(
212            "users",
213            vec![
214                col("id", ColumnType::Simple(SimpleColumnType::Integer), false),
215                col("email", ColumnType::Simple(SimpleColumnType::Text), true),
216            ],
217            vec![],
218        )];
219
220        let result =
221            build_modify_column_default(backend, "users", "email", new_default, None, &schema, &[]);
222        assert!(result.is_ok());
223        let queries = result.unwrap();
224        let sql = queries
225            .iter()
226            .map(|q| q.build(backend))
227            .collect::<Vec<String>>()
228            .join("\n");
229
230        let suffix = format!(
231            "{}_{}_users",
232            match backend {
233                DatabaseBackend::Postgres => "postgres",
234                DatabaseBackend::MySql => "mysql",
235                DatabaseBackend::Sqlite => "sqlite",
236            },
237            if new_default.is_some() {
238                "set_default"
239            } else {
240                "drop_default"
241            }
242        );
243
244        with_settings!({ snapshot_suffix => suffix }, {
245            assert_snapshot!(sql);
246        });
247    }
248
249    /// Test table not found error
250    #[rstest]
251    #[case::postgres_table_not_found(DatabaseBackend::Postgres)]
252    #[case::mysql_table_not_found(DatabaseBackend::MySql)]
253    #[case::sqlite_table_not_found(DatabaseBackend::Sqlite)]
254    fn test_table_not_found(#[case] backend: DatabaseBackend) {
255        // Postgres doesn't need schema lookup for default changes
256        if backend == DatabaseBackend::Postgres {
257            return;
258        }
259
260        let result = build_modify_column_default(
261            backend,
262            "users",
263            "email",
264            Some("'default'"),
265            None,
266            &[],
267            &[],
268        );
269        assert!(result.is_err());
270        let err_msg = result.unwrap_err().to_string();
271        assert!(err_msg.contains("Table 'users' not found"));
272    }
273
274    /// Test column not found error
275    #[rstest]
276    #[case::postgres_column_not_found(DatabaseBackend::Postgres)]
277    #[case::mysql_column_not_found(DatabaseBackend::MySql)]
278    #[case::sqlite_column_not_found(DatabaseBackend::Sqlite)]
279    fn test_column_not_found(#[case] backend: DatabaseBackend) {
280        // Postgres doesn't need schema lookup for default changes
281        // SQLite doesn't validate column existence in modify_column_default
282        if backend == DatabaseBackend::Postgres || backend == DatabaseBackend::Sqlite {
283            return;
284        }
285
286        let schema = vec![table_def(
287            "users",
288            vec![col(
289                "id",
290                ColumnType::Simple(SimpleColumnType::Integer),
291                false,
292            )],
293            vec![],
294        )];
295
296        let result = build_modify_column_default(
297            backend,
298            "users",
299            "email",
300            Some("'default'"),
301            None,
302            &schema,
303            &[],
304        );
305        assert!(result.is_err());
306        let err_msg = result.unwrap_err().to_string();
307        assert!(err_msg.contains("Column 'email' not found"));
308    }
309
310    /// Test Postgres default change when column is not in schema
311    /// This covers the fallback path where `column_type` is None
312    #[test]
313    fn test_postgres_column_not_in_schema_uses_default_as_is() {
314        let schema = vec![table_def(
315            "users",
316            vec![col(
317                "id",
318                ColumnType::Simple(SimpleColumnType::Integer),
319                false,
320            )],
321            // Note: "status" column is NOT in the schema
322            vec![],
323        )];
324
325        // Postgres doesn't error when column isn't found - it just uses the default as-is
326        let result = build_modify_column_default(
327            DatabaseBackend::Postgres,
328            "users",
329            "status", // column not in schema
330            Some("'active'"),
331            None,
332            &schema,
333            &[],
334        );
335        assert!(result.is_ok());
336        let queries = result.unwrap();
337        let sql = queries
338            .iter()
339            .map(|q| q.build(DatabaseBackend::Postgres))
340            .collect::<Vec<String>>()
341            .join("\n");
342
343        // Should still generate valid SQL, using the default value as-is
344        assert!(sql.contains("ALTER TABLE \"users\" ALTER COLUMN \"status\" SET DEFAULT 'active'"));
345    }
346
347    /// Test with index - should recreate index after table rebuild (`SQLite`)
348    #[rstest]
349    #[case::postgres_with_index(DatabaseBackend::Postgres)]
350    #[case::mysql_with_index(DatabaseBackend::MySql)]
351    #[case::sqlite_with_index(DatabaseBackend::Sqlite)]
352    fn test_modify_default_with_index(#[case] backend: DatabaseBackend) {
353        let schema = vec![table_def(
354            "users",
355            vec![
356                col("id", ColumnType::Simple(SimpleColumnType::Integer), false),
357                col("email", ColumnType::Simple(SimpleColumnType::Text), true),
358            ],
359            vec![TableConstraint::Index {
360                name: Some("idx_users_email".into()),
361                columns: vec!["email".into()],
362            }],
363        )];
364
365        let result = build_modify_column_default(
366            backend,
367            "users",
368            "email",
369            Some("'default@example.com'"),
370            None,
371            &schema,
372            &[],
373        );
374        assert!(result.is_ok());
375        let queries = result.unwrap();
376        let sql = queries
377            .iter()
378            .map(|q| q.build(backend))
379            .collect::<Vec<String>>()
380            .join("\n");
381
382        // SQLite should recreate the index after table rebuild
383        if backend == DatabaseBackend::Sqlite {
384            assert!(sql.contains("CREATE INDEX"));
385            assert!(sql.contains("idx_users_email"));
386        }
387
388        let suffix = format!(
389            "{}_with_index",
390            match backend {
391                DatabaseBackend::Postgres => "postgres",
392                DatabaseBackend::MySql => "mysql",
393                DatabaseBackend::Sqlite => "sqlite",
394            }
395        );
396
397        with_settings!({ snapshot_suffix => suffix }, {
398            assert_snapshot!(sql);
399        });
400    }
401
402    /// Test changing default value from one to another
403    #[rstest]
404    #[case::postgres_change_default(DatabaseBackend::Postgres)]
405    #[case::mysql_change_default(DatabaseBackend::MySql)]
406    #[case::sqlite_change_default(DatabaseBackend::Sqlite)]
407    fn test_change_default_value(#[case] backend: DatabaseBackend) {
408        let mut email_col = col("email", ColumnType::Simple(SimpleColumnType::Text), true);
409        email_col.default = Some("'old@example.com'".into());
410
411        let schema = vec![table_def(
412            "users",
413            vec![
414                col("id", ColumnType::Simple(SimpleColumnType::Integer), false),
415                email_col,
416            ],
417            vec![],
418        )];
419
420        let result = build_modify_column_default(
421            backend,
422            "users",
423            "email",
424            Some("'new@example.com'"),
425            None,
426            &schema,
427            &[],
428        );
429        assert!(result.is_ok());
430        let queries = result.unwrap();
431        let sql = queries
432            .iter()
433            .map(|q| q.build(backend))
434            .collect::<Vec<String>>()
435            .join("\n");
436
437        let suffix = format!(
438            "{}_change_default",
439            match backend {
440                DatabaseBackend::Postgres => "postgres",
441                DatabaseBackend::MySql => "mysql",
442                DatabaseBackend::Sqlite => "sqlite",
443            }
444        );
445
446        with_settings!({ snapshot_suffix => suffix }, {
447            assert_snapshot!(sql);
448        });
449    }
450
451    /// Test with integer default value
452    #[rstest]
453    #[case::postgres_integer_default(DatabaseBackend::Postgres)]
454    #[case::mysql_integer_default(DatabaseBackend::MySql)]
455    #[case::sqlite_integer_default(DatabaseBackend::Sqlite)]
456    fn test_integer_default(#[case] backend: DatabaseBackend) {
457        let schema = vec![table_def(
458            "products",
459            vec![
460                col("id", ColumnType::Simple(SimpleColumnType::Integer), false),
461                col(
462                    "quantity",
463                    ColumnType::Simple(SimpleColumnType::Integer),
464                    false,
465                ),
466            ],
467            vec![],
468        )];
469
470        let result = build_modify_column_default(
471            backend,
472            "products",
473            "quantity",
474            Some("0"),
475            None,
476            &schema,
477            &[],
478        );
479        assert!(result.is_ok());
480        let queries = result.unwrap();
481        let sql = queries
482            .iter()
483            .map(|q| q.build(backend))
484            .collect::<Vec<String>>()
485            .join("\n");
486
487        let suffix = format!(
488            "{}_integer_default",
489            match backend {
490                DatabaseBackend::Postgres => "postgres",
491                DatabaseBackend::MySql => "mysql",
492                DatabaseBackend::Sqlite => "sqlite",
493            }
494        );
495
496        with_settings!({ snapshot_suffix => suffix }, {
497            assert_snapshot!(sql);
498        });
499    }
500
501    /// Test with boolean default value
502    #[rstest]
503    #[case::postgres_boolean_default(DatabaseBackend::Postgres)]
504    #[case::mysql_boolean_default(DatabaseBackend::MySql)]
505    #[case::sqlite_boolean_default(DatabaseBackend::Sqlite)]
506    fn test_boolean_default(#[case] backend: DatabaseBackend) {
507        let schema = vec![table_def(
508            "users",
509            vec![
510                col("id", ColumnType::Simple(SimpleColumnType::Integer), false),
511                col(
512                    "is_active",
513                    ColumnType::Simple(SimpleColumnType::Boolean),
514                    false,
515                ),
516            ],
517            vec![],
518        )];
519
520        let result = build_modify_column_default(
521            backend,
522            "users",
523            "is_active",
524            Some("true"),
525            None,
526            &schema,
527            &[],
528        );
529        assert!(result.is_ok());
530        let queries = result.unwrap();
531        let sql = queries
532            .iter()
533            .map(|q| q.build(backend))
534            .collect::<Vec<String>>()
535            .join("\n");
536
537        let suffix = format!(
538            "{}_boolean_default",
539            match backend {
540                DatabaseBackend::Postgres => "postgres",
541                DatabaseBackend::MySql => "mysql",
542                DatabaseBackend::Sqlite => "sqlite",
543            }
544        );
545
546        with_settings!({ snapshot_suffix => suffix }, {
547            assert_snapshot!(sql);
548        });
549    }
550
551    /// Test with function default (e.g., `NOW()`, `CURRENT_TIMESTAMP`)
552    #[rstest]
553    #[case::postgres_function_default(DatabaseBackend::Postgres)]
554    #[case::mysql_function_default(DatabaseBackend::MySql)]
555    #[case::sqlite_function_default(DatabaseBackend::Sqlite)]
556    fn test_function_default(#[case] backend: DatabaseBackend) {
557        let schema = vec![table_def(
558            "events",
559            vec![
560                col("id", ColumnType::Simple(SimpleColumnType::Integer), false),
561                col(
562                    "created_at",
563                    ColumnType::Simple(SimpleColumnType::Timestamp),
564                    false,
565                ),
566            ],
567            vec![],
568        )];
569
570        let default_value = match backend {
571            DatabaseBackend::Postgres => "NOW()",
572            DatabaseBackend::MySql | DatabaseBackend::Sqlite => "CURRENT_TIMESTAMP",
573        };
574
575        let result = build_modify_column_default(
576            backend,
577            "events",
578            "created_at",
579            Some(default_value),
580            None,
581            &schema,
582            &[],
583        );
584        assert!(result.is_ok());
585        let queries = result.unwrap();
586        let sql = queries
587            .iter()
588            .map(|q| q.build(backend))
589            .collect::<Vec<String>>()
590            .join("\n");
591
592        let suffix = format!(
593            "{}_function_default",
594            match backend {
595                DatabaseBackend::Postgres => "postgres",
596                DatabaseBackend::MySql => "mysql",
597                DatabaseBackend::Sqlite => "sqlite",
598            }
599        );
600
601        with_settings!({ snapshot_suffix => suffix }, {
602            assert_snapshot!(sql);
603        });
604    }
605
606    /// Test dropping default from column that had one
607    #[rstest]
608    #[case::postgres_drop_existing_default(DatabaseBackend::Postgres)]
609    #[case::mysql_drop_existing_default(DatabaseBackend::MySql)]
610    #[case::sqlite_drop_existing_default(DatabaseBackend::Sqlite)]
611    fn test_drop_existing_default(#[case] backend: DatabaseBackend) {
612        let mut status_col = col("status", ColumnType::Simple(SimpleColumnType::Text), false);
613        status_col.default = Some("'pending'".into());
614
615        let schema = vec![table_def(
616            "orders",
617            vec![
618                col("id", ColumnType::Simple(SimpleColumnType::Integer), false),
619                status_col,
620            ],
621            vec![],
622        )];
623
624        let result = build_modify_column_default(
625            backend,
626            "orders",
627            "status",
628            None, // Drop default
629            None, // No backfill
630            &schema,
631            &[],
632        );
633        assert!(result.is_ok());
634        let queries = result.unwrap();
635        let sql = queries
636            .iter()
637            .map(|q| q.build(backend))
638            .collect::<Vec<String>>()
639            .join("\n");
640
641        let suffix = format!(
642            "{}_drop_existing_default",
643            match backend {
644                DatabaseBackend::Postgres => "postgres",
645                DatabaseBackend::MySql => "mysql",
646                DatabaseBackend::Sqlite => "sqlite",
647            }
648        );
649
650        with_settings!({ snapshot_suffix => suffix }, {
651            assert_snapshot!(sql);
652        });
653    }
654
655    /// Test that `backfill = Some(value)` emits the trailing `UPDATE` that
656    /// rewrites every existing row. Covers the post-ALTER backfill block
657    /// (the `if let Some(value) = backfill { ... }` body) for all backends.
658    #[rstest]
659    #[case::postgres(DatabaseBackend::Postgres)]
660    #[case::mysql(DatabaseBackend::MySql)]
661    #[case::sqlite(DatabaseBackend::Sqlite)]
662    fn build_modify_column_default_with_backfill_emits_update_statement(
663        #[case] backend: DatabaseBackend,
664    ) {
665        let schema = vec![table_def(
666            "users",
667            vec![
668                col("id", ColumnType::Simple(SimpleColumnType::Integer), false),
669                col("status", ColumnType::Simple(SimpleColumnType::Text), false),
670            ],
671            vec![],
672        )];
673
674        let queries = build_modify_column_default(
675            backend,
676            "users",
677            "status",
678            Some("'active'"),
679            Some("'active'"),
680            &schema,
681            &[],
682        )
683        .expect("backfill path should succeed");
684        let sql = queries
685            .iter()
686            .map(|q| q.build(backend))
687            .collect::<Vec<_>>()
688            .join("\n");
689
690        // The trailing UPDATE was emitted exactly once.
691        let update_count = sql.matches("UPDATE").count();
692        assert!(update_count >= 1, "expected backfill UPDATE in: {sql}");
693        assert!(sql.contains("SET"));
694        assert!(sql.contains("status"));
695        assert!(sql.contains("'active'"));
696    }
697}
vespertide_query/sql/modify_column_default.rs

vespertide_query/sql/
modify_column_default.rs
