vespertide_query/sql/
delete_column.rs

1use sea_query::{Alias, Index, Query, Table};
2
3use vespertide_core::{ColumnType, TableConstraint, TableDef};
4
5use super::helpers::{
6    build_drop_enum_type_sql, build_sqlite_temp_table_create, recreate_indexes_after_rebuild,
7};
8use super::rename_table::build_rename_table;
9use super::types::{BuiltQuery, DatabaseBackend};
10
11/// Build SQL to delete a column, optionally with DROP TYPE for enum columns (PostgreSQL)
12///
13/// For SQLite: Handles constraint removal before dropping the column:
14/// - Unique/Index constraints: Dropped via DROP INDEX
15/// - ForeignKey/PrimaryKey constraints: Uses temp table approach (recreate table without column)
16///
17/// SQLite doesn't cascade constraint drops when a column is dropped.
18pub fn build_delete_column(
19    backend: &DatabaseBackend,
20    table: &str,
21    column: &str,
22    column_type: Option<&ColumnType>,
23    current_schema: &[TableDef],
24) -> Vec<BuiltQuery> {
25    let mut stmts = Vec::new();
26
27    // SQLite: Check if we need special handling for constraints
28    if *backend == DatabaseBackend::Sqlite
29        && let Some(table_def) = current_schema.iter().find(|t| t.name == table)
30    {
31        // If the column has an enum type, SQLite embeds a CHECK constraint in CREATE TABLE.
32        // ALTER TABLE DROP COLUMN fails if the column is referenced by any CHECK.
33        // Must use temp table approach.
34        if let Some(col_def) = table_def.columns.iter().find(|c| c.name == column)
35            && let ColumnType::Complex(vespertide_core::ComplexColumnType::Enum { .. }) =
36                &col_def.r#type
37        {
38            return build_delete_column_sqlite_temp_table(table, column, table_def, column_type);
39        }
40
41        // Handle constraints referencing the deleted column
42        for constraint in &table_def.constraints {
43            match constraint {
44                // Check constraints may reference the column in their expression.
45                // SQLite can't DROP COLUMN if a CHECK references it — use temp table.
46                TableConstraint::Check { expr, .. } => {
47                    // Check if the expression references the column (e.g. "status" IN (...))
48                    if expr.contains(&format!("\"{}\"", column)) || expr.contains(column) {
49                        return build_delete_column_sqlite_temp_table(
50                            table,
51                            column,
52                            table_def,
53                            column_type,
54                        );
55                    }
56                    continue;
57                }
58                // For column-based constraints, check if they reference the deleted column
59                _ if !constraint.columns().iter().any(|c| c == column) => continue,
60                // FK/PK require temp table approach - return immediately
61                TableConstraint::ForeignKey { .. } | TableConstraint::PrimaryKey { .. } => {
62                    return build_delete_column_sqlite_temp_table(
63                        table,
64                        column,
65                        table_def,
66                        column_type,
67                    );
68                }
69                // Unique/Index: drop the index first, then drop column below
70                TableConstraint::Unique { name, columns } => {
71                    let index_name = vespertide_naming::build_unique_constraint_name(
72                        table,
73                        columns,
74                        name.as_deref(),
75                    );
76                    let drop_idx = Index::drop()
77                        .name(&index_name)
78                        .table(Alias::new(table))
79                        .to_owned();
80                    stmts.push(BuiltQuery::DropIndex(Box::new(drop_idx)));
81                }
82                TableConstraint::Index { name, columns } => {
83                    let index_name =
84                        vespertide_naming::build_index_name(table, columns, name.as_deref());
85                    let drop_idx = Index::drop()
86                        .name(&index_name)
87                        .table(Alias::new(table))
88                        .to_owned();
89                    stmts.push(BuiltQuery::DropIndex(Box::new(drop_idx)));
90                }
91            }
92        }
93    }
94
95    // Drop the column
96    let stmt = Table::alter()
97        .table(Alias::new(table))
98        .drop_column(Alias::new(column))
99        .to_owned();
100    stmts.push(BuiltQuery::AlterTable(Box::new(stmt)));
101
102    // If column type is an enum, drop the type after (PostgreSQL only)
103    // Note: Only drop if this is the last column using this enum type
104    if let Some(col_type) = column_type
105        && let Some(drop_type_sql) = build_drop_enum_type_sql(table, col_type)
106    {
107        stmts.push(BuiltQuery::Raw(drop_type_sql));
108    }
109
110    stmts
111}
112
113/// SQLite temp table approach for deleting a column that has FK or PK constraints.
114///
115/// Steps:
116/// 1. Create temp table without the column (and without constraints referencing it)
117/// 2. Copy data (excluding the deleted column)
118/// 3. Drop original table
119/// 4. Rename temp table to original name
120/// 5. Recreate indexes that don't reference the deleted column
121fn build_delete_column_sqlite_temp_table(
122    table: &str,
123    column: &str,
124    table_def: &TableDef,
125    column_type: Option<&ColumnType>,
126) -> Vec<BuiltQuery> {
127    let mut stmts = Vec::new();
128    let temp_table = format!("{}_temp", table);
129
130    // Build new columns list without the deleted column
131    let new_columns: Vec<_> = table_def
132        .columns
133        .iter()
134        .filter(|c| c.name != column)
135        .cloned()
136        .collect();
137
138    // Build new constraints list without constraints referencing the deleted column
139    let new_constraints: Vec<_> = table_def
140        .constraints
141        .iter()
142        .filter(|c| {
143            // For CHECK constraints, check if expression references the column
144            if let TableConstraint::Check { expr, .. } = c {
145                return !expr.contains(&format!("\"{}\"", column)) && !expr.contains(column);
146            }
147            !c.columns().iter().any(|col| col == column)
148        })
149        .cloned()
150        .collect();
151
152    // 1. Create temp table without the column + CHECK constraints
153    let create_query = build_sqlite_temp_table_create(
154        &DatabaseBackend::Sqlite,
155        &temp_table,
156        table,
157        &new_columns,
158        &new_constraints,
159    );
160    stmts.push(create_query);
161
162    // 2. Copy data (excluding the deleted column)
163    let column_aliases: Vec<Alias> = new_columns.iter().map(|c| Alias::new(&c.name)).collect();
164    let mut select_query = Query::select();
165    for col_alias in &column_aliases {
166        select_query = select_query.column(col_alias.clone()).to_owned();
167    }
168    select_query = select_query.from(Alias::new(table)).to_owned();
169
170    let insert_stmt = Query::insert()
171        .into_table(Alias::new(&temp_table))
172        .columns(column_aliases.clone())
173        .select_from(select_query)
174        .unwrap()
175        .to_owned();
176    stmts.push(BuiltQuery::Insert(Box::new(insert_stmt)));
177
178    // 3. Drop original table
179    let drop_table = Table::drop().table(Alias::new(table)).to_owned();
180    stmts.push(BuiltQuery::DropTable(Box::new(drop_table)));
181
182    // 4. Rename temp table to original name
183    stmts.push(build_rename_table(&temp_table, table));
184
185    // 5. Recreate indexes (both regular and UNIQUE) that don't reference the deleted column
186    stmts.extend(recreate_indexes_after_rebuild(table, &new_constraints, &[]));
187
188    // If column type is an enum, drop the type after (PostgreSQL only, but include for completeness)
189    if let Some(col_type) = column_type
190        && let Some(drop_type_sql) = build_drop_enum_type_sql(table, col_type)
191    {
192        stmts.push(BuiltQuery::Raw(drop_type_sql));
193    }
194
195    stmts
196}
197
198#[cfg(test)]
199mod tests {
200    use super::*;
201    use crate::sql::types::DatabaseBackend;
202    use insta::{assert_snapshot, with_settings};
203    use rstest::rstest;
204    use vespertide_core::{ColumnDef, ComplexColumnType, SimpleColumnType};
205
206    fn col(name: &str, ty: ColumnType) -> ColumnDef {
207        ColumnDef {
208            name: name.to_string(),
209            r#type: ty,
210            nullable: true,
211            default: None,
212            comment: None,
213            primary_key: None,
214            unique: None,
215            index: None,
216            foreign_key: None,
217        }
218    }
219
220    #[rstest]
221    #[case::delete_column_postgres(
222        "delete_column_postgres",
223        DatabaseBackend::Postgres,
224        &["ALTER TABLE \"users\" DROP COLUMN \"email\""]
225    )]
226    #[case::delete_column_mysql(
227        "delete_column_mysql",
228        DatabaseBackend::MySql,
229        &["ALTER TABLE `users` DROP COLUMN `email`"]
230    )]
231    #[case::delete_column_sqlite(
232        "delete_column_sqlite",
233        DatabaseBackend::Sqlite,
234        &["ALTER TABLE \"users\" DROP COLUMN \"email\""]
235    )]
236    fn test_delete_column(
237        #[case] title: &str,
238        #[case] backend: DatabaseBackend,
239        #[case] expected: &[&str],
240    ) {
241        let result = build_delete_column(&backend, "users", "email", None, &[]);
242        let sql = result[0].build(backend);
243        for exp in expected {
244            assert!(
245                sql.contains(exp),
246                "Expected SQL to contain '{}', got: {}",
247                exp,
248                sql
249            );
250        }
251
252        with_settings!({ snapshot_suffix => format!("delete_column_{}", title) }, {
253            assert_snapshot!(sql);
254        });
255    }
256
257    #[test]
258    fn test_delete_enum_column_postgres() {
259        use vespertide_core::EnumValues;
260
261        let enum_type = ColumnType::Complex(ComplexColumnType::Enum {
262            name: "status".into(),
263            values: EnumValues::String(vec!["active".into(), "inactive".into()]),
264        });
265        let result = build_delete_column(
266            &DatabaseBackend::Postgres,
267            "users",
268            "status",
269            Some(&enum_type),
270            &[],
271        );
272
273        // Should have 2 statements: ALTER TABLE and DROP TYPE
274        assert_eq!(result.len(), 2);
275
276        let alter_sql = result[0].build(DatabaseBackend::Postgres);
277        assert!(alter_sql.contains("DROP COLUMN"));
278
279        let drop_type_sql = result[1].build(DatabaseBackend::Postgres);
280        assert!(drop_type_sql.contains("DROP TYPE \"users_status\""));
281
282        // MySQL and SQLite should have empty DROP TYPE
283        let drop_type_mysql = result[1].build(DatabaseBackend::MySql);
284        assert!(drop_type_mysql.is_empty());
285    }
286
287    #[test]
288    fn test_delete_non_enum_column_no_drop_type() {
289        let text_type = ColumnType::Simple(SimpleColumnType::Text);
290        let result = build_delete_column(
291            &DatabaseBackend::Postgres,
292            "users",
293            "name",
294            Some(&text_type),
295            &[],
296        );
297
298        // Should only have 1 statement: ALTER TABLE
299        assert_eq!(result.len(), 1);
300    }
301
302    #[test]
303    fn test_delete_column_sqlite_drops_unique_constraint_first() {
304        // SQLite should drop unique constraint index before dropping the column
305        let schema = vec![TableDef {
306            name: "gift".into(),
307            description: None,
308            columns: vec![
309                col("id", ColumnType::Simple(SimpleColumnType::Integer)),
310                col("gift_code", ColumnType::Simple(SimpleColumnType::Text)),
311            ],
312            constraints: vec![TableConstraint::Unique {
313                name: None,
314                columns: vec!["gift_code".into()],
315            }],
316        }];
317
318        let result =
319            build_delete_column(&DatabaseBackend::Sqlite, "gift", "gift_code", None, &schema);
320
321        // Should have 2 statements: DROP INDEX then ALTER TABLE DROP COLUMN
322        assert_eq!(result.len(), 2);
323
324        let drop_index_sql = result[0].build(DatabaseBackend::Sqlite);
325        assert!(
326            drop_index_sql.contains("DROP INDEX"),
327            "Expected DROP INDEX, got: {}",
328            drop_index_sql
329        );
330        assert!(
331            drop_index_sql.contains("uq_gift__gift_code"),
332            "Expected index name uq_gift__gift_code, got: {}",
333            drop_index_sql
334        );
335
336        let drop_column_sql = result[1].build(DatabaseBackend::Sqlite);
337        assert!(
338            drop_column_sql.contains("DROP COLUMN"),
339            "Expected DROP COLUMN, got: {}",
340            drop_column_sql
341        );
342    }
343
344    #[test]
345    fn test_delete_column_sqlite_drops_index_constraint_first() {
346        // SQLite should drop index before dropping the column
347        let schema = vec![TableDef {
348            name: "users".into(),
349            description: None,
350            columns: vec![
351                col("id", ColumnType::Simple(SimpleColumnType::Integer)),
352                col("email", ColumnType::Simple(SimpleColumnType::Text)),
353            ],
354            constraints: vec![TableConstraint::Index {
355                name: None,
356                columns: vec!["email".into()],
357            }],
358        }];
359
360        let result = build_delete_column(&DatabaseBackend::Sqlite, "users", "email", None, &schema);
361
362        // Should have 2 statements: DROP INDEX then ALTER TABLE DROP COLUMN
363        assert_eq!(result.len(), 2);
364
365        let drop_index_sql = result[0].build(DatabaseBackend::Sqlite);
366        assert!(drop_index_sql.contains("DROP INDEX"));
367        assert!(drop_index_sql.contains("ix_users__email"));
368
369        let drop_column_sql = result[1].build(DatabaseBackend::Sqlite);
370        assert!(drop_column_sql.contains("DROP COLUMN"));
371    }
372
373    #[test]
374    fn test_delete_column_postgres_does_not_drop_constraints() {
375        // PostgreSQL cascades constraint drops, so we shouldn't emit extra DROP INDEX
376        let schema = vec![TableDef {
377            name: "gift".into(),
378            description: None,
379            columns: vec![
380                col("id", ColumnType::Simple(SimpleColumnType::Integer)),
381                col("gift_code", ColumnType::Simple(SimpleColumnType::Text)),
382            ],
383            constraints: vec![TableConstraint::Unique {
384                name: None,
385                columns: vec!["gift_code".into()],
386            }],
387        }];
388
389        let result = build_delete_column(
390            &DatabaseBackend::Postgres,
391            "gift",
392            "gift_code",
393            None,
394            &schema,
395        );
396
397        // Should have only 1 statement: ALTER TABLE DROP COLUMN
398        assert_eq!(result.len(), 1);
399
400        let drop_column_sql = result[0].build(DatabaseBackend::Postgres);
401        assert!(drop_column_sql.contains("DROP COLUMN"));
402    }
403
404    #[test]
405    fn test_delete_column_sqlite_with_named_unique_constraint() {
406        // Test with a named unique constraint
407        let schema = vec![TableDef {
408            name: "gift".into(),
409            description: None,
410            columns: vec![
411                col("id", ColumnType::Simple(SimpleColumnType::Integer)),
412                col("gift_code", ColumnType::Simple(SimpleColumnType::Text)),
413            ],
414            constraints: vec![TableConstraint::Unique {
415                name: Some("gift_code".into()),
416                columns: vec!["gift_code".into()],
417            }],
418        }];
419
420        let result =
421            build_delete_column(&DatabaseBackend::Sqlite, "gift", "gift_code", None, &schema);
422
423        assert_eq!(result.len(), 2);
424
425        let drop_index_sql = result[0].build(DatabaseBackend::Sqlite);
426        // Named constraint: uq_gift__gift_code (name is "gift_code")
427        assert!(
428            drop_index_sql.contains("uq_gift__gift_code"),
429            "Expected uq_gift__gift_code, got: {}",
430            drop_index_sql
431        );
432    }
433
434    #[test]
435    fn test_delete_column_sqlite_with_fk_uses_temp_table() {
436        // SQLite should use temp table approach when deleting a column with FK constraint
437        let schema = vec![TableDef {
438            name: "gift".into(),
439            description: None,
440            columns: vec![
441                col("id", ColumnType::Simple(SimpleColumnType::Integer)),
442                col("sender_id", ColumnType::Simple(SimpleColumnType::BigInt)),
443                col("message", ColumnType::Simple(SimpleColumnType::Text)),
444            ],
445            constraints: vec![TableConstraint::ForeignKey {
446                name: None,
447                columns: vec!["sender_id".into()],
448                ref_table: "user".into(),
449                ref_columns: vec!["id".into()],
450                on_delete: None,
451                on_update: None,
452            }],
453        }];
454
455        let result =
456            build_delete_column(&DatabaseBackend::Sqlite, "gift", "sender_id", None, &schema);
457
458        // Should use temp table approach:
459        // 1. CREATE TABLE gift_temp (without sender_id column)
460        // 2. INSERT INTO gift_temp SELECT ... FROM gift
461        // 3. DROP TABLE gift
462        // 4. ALTER TABLE gift_temp RENAME TO gift
463        assert!(
464            result.len() >= 4,
465            "Expected at least 4 statements for temp table approach, got: {}",
466            result.len()
467        );
468
469        let all_sql: Vec<String> = result
470            .iter()
471            .map(|q| q.build(DatabaseBackend::Sqlite))
472            .collect();
473        let combined_sql = all_sql.join("\n");
474
475        // Verify temp table creation
476        assert!(
477            combined_sql.contains("CREATE TABLE") && combined_sql.contains("gift_temp"),
478            "Expected CREATE TABLE gift_temp, got: {}",
479            combined_sql
480        );
481
482        // Verify the new table doesn't have sender_id column
483        assert!(
484            !combined_sql.contains("\"sender_id\"") || combined_sql.contains("DROP TABLE"),
485            "New table should not contain sender_id column"
486        );
487
488        // Verify data copy (INSERT ... SELECT)
489        assert!(
490            combined_sql.contains("INSERT INTO"),
491            "Expected INSERT INTO for data copy, got: {}",
492            combined_sql
493        );
494
495        // Verify original table drop
496        assert!(
497            combined_sql.contains("DROP TABLE") && combined_sql.contains("\"gift\""),
498            "Expected DROP TABLE gift, got: {}",
499            combined_sql
500        );
501
502        // Verify rename
503        assert!(
504            combined_sql.contains("RENAME"),
505            "Expected RENAME for temp table, got: {}",
506            combined_sql
507        );
508    }
509
510    #[test]
511    fn test_delete_column_sqlite_with_fk_preserves_other_columns() {
512        // When using temp table approach, other columns should be preserved
513        let schema = vec![TableDef {
514            name: "gift".into(),
515            description: None,
516            columns: vec![
517                col("id", ColumnType::Simple(SimpleColumnType::Integer)),
518                col("sender_id", ColumnType::Simple(SimpleColumnType::BigInt)),
519                col("receiver_id", ColumnType::Simple(SimpleColumnType::BigInt)),
520                col("message", ColumnType::Simple(SimpleColumnType::Text)),
521            ],
522            constraints: vec![
523                TableConstraint::ForeignKey {
524                    name: None,
525                    columns: vec!["sender_id".into()],
526                    ref_table: "user".into(),
527                    ref_columns: vec!["id".into()],
528                    on_delete: None,
529                    on_update: None,
530                },
531                TableConstraint::Index {
532                    name: None,
533                    columns: vec!["receiver_id".into()],
534                },
535            ],
536        }];
537
538        let result =
539            build_delete_column(&DatabaseBackend::Sqlite, "gift", "sender_id", None, &schema);
540
541        let all_sql: Vec<String> = result
542            .iter()
543            .map(|q| q.build(DatabaseBackend::Sqlite))
544            .collect();
545        let combined_sql = all_sql.join("\n");
546
547        // Should preserve other columns
548        assert!(combined_sql.contains("\"id\""), "Should preserve id column");
549        assert!(
550            combined_sql.contains("\"receiver_id\""),
551            "Should preserve receiver_id column"
552        );
553        assert!(
554            combined_sql.contains("\"message\""),
555            "Should preserve message column"
556        );
557
558        // Should recreate index on receiver_id (not on sender_id)
559        assert!(
560            combined_sql.contains("CREATE INDEX") && combined_sql.contains("ix_gift__receiver_id"),
561            "Should recreate index on receiver_id, got: {}",
562            combined_sql
563        );
564    }
565
566    #[test]
567    fn test_delete_column_postgres_with_fk_does_not_use_temp_table() {
568        // PostgreSQL should NOT use temp table - just drop column directly
569        let schema = vec![TableDef {
570            name: "gift".into(),
571            description: None,
572            columns: vec![
573                col("id", ColumnType::Simple(SimpleColumnType::Integer)),
574                col("sender_id", ColumnType::Simple(SimpleColumnType::BigInt)),
575            ],
576            constraints: vec![TableConstraint::ForeignKey {
577                name: None,
578                columns: vec!["sender_id".into()],
579                ref_table: "user".into(),
580                ref_columns: vec!["id".into()],
581                on_delete: None,
582                on_update: None,
583            }],
584        }];
585
586        let result = build_delete_column(
587            &DatabaseBackend::Postgres,
588            "gift",
589            "sender_id",
590            None,
591            &schema,
592        );
593
594        // Should have only 1 statement: ALTER TABLE DROP COLUMN
595        assert_eq!(
596            result.len(),
597            1,
598            "PostgreSQL should only have 1 statement, got: {}",
599            result.len()
600        );
601
602        let sql = result[0].build(DatabaseBackend::Postgres);
603        assert!(
604            sql.contains("DROP COLUMN"),
605            "Expected DROP COLUMN, got: {}",
606            sql
607        );
608        assert!(
609            !sql.contains("gift_temp"),
610            "PostgreSQL should not use temp table"
611        );
612    }
613
614    #[test]
615    fn test_delete_column_sqlite_with_pk_uses_temp_table() {
616        // SQLite should use temp table approach when deleting a column that's part of PK
617        let schema = vec![TableDef {
618            name: "order_items".into(),
619            description: None,
620            columns: vec![
621                col("order_id", ColumnType::Simple(SimpleColumnType::Integer)),
622                col("product_id", ColumnType::Simple(SimpleColumnType::Integer)),
623                col("quantity", ColumnType::Simple(SimpleColumnType::Integer)),
624            ],
625            constraints: vec![TableConstraint::PrimaryKey {
626                auto_increment: false,
627                columns: vec!["order_id".into(), "product_id".into()],
628            }],
629        }];
630
631        let result = build_delete_column(
632            &DatabaseBackend::Sqlite,
633            "order_items",
634            "product_id",
635            None,
636            &schema,
637        );
638
639        // Should use temp table approach
640        assert!(
641            result.len() >= 4,
642            "Expected at least 4 statements for temp table approach, got: {}",
643            result.len()
644        );
645
646        let all_sql: Vec<String> = result
647            .iter()
648            .map(|q| q.build(DatabaseBackend::Sqlite))
649            .collect();
650        let combined_sql = all_sql.join("\n");
651
652        assert!(
653            combined_sql.contains("order_items_temp"),
654            "Should use temp table approach for PK column deletion"
655        );
656    }
657
658    #[test]
659    fn test_delete_column_sqlite_unique_on_different_column_not_dropped() {
660        // When deleting a column in SQLite, UNIQUE constraints on OTHER columns should NOT be dropped
661        // This tests line 46's condition: only drop constraints that reference the deleted column
662        let schema = vec![TableDef {
663            name: "users".into(),
664            description: None,
665            columns: vec![
666                col("id", ColumnType::Simple(SimpleColumnType::Integer)),
667                col("email", ColumnType::Simple(SimpleColumnType::Text)),
668                col("nickname", ColumnType::Simple(SimpleColumnType::Text)),
669            ],
670            constraints: vec![
671                // UNIQUE on email (the column we're NOT deleting)
672                TableConstraint::Unique {
673                    name: None,
674                    columns: vec!["email".into()],
675                },
676            ],
677        }];
678
679        // Delete nickname, which does NOT have the unique constraint
680        let result =
681            build_delete_column(&DatabaseBackend::Sqlite, "users", "nickname", None, &schema);
682
683        // Should only have 1 statement: ALTER TABLE DROP COLUMN (no DROP INDEX needed)
684        assert_eq!(
685            result.len(),
686            1,
687            "Should not drop UNIQUE on email when deleting nickname, got: {} statements",
688            result.len()
689        );
690
691        let sql = result[0].build(DatabaseBackend::Sqlite);
692        assert!(
693            sql.contains("DROP COLUMN"),
694            "Expected DROP COLUMN, got: {}",
695            sql
696        );
697        assert!(
698            !sql.contains("DROP INDEX"),
699            "Should NOT drop the email UNIQUE constraint when deleting nickname"
700        );
701    }
702
703    #[test]
704    fn test_delete_column_sqlite_temp_table_filters_constraints_correctly() {
705        // When using temp table approach, constraints referencing the deleted column should be excluded,
706        // but constraints on OTHER columns should be preserved
707        // This tests lines 122-124: filter constraints by column reference
708        let schema = vec![TableDef {
709            name: "orders".into(),
710            description: None,
711            columns: vec![
712                col("id", ColumnType::Simple(SimpleColumnType::Integer)),
713                col("user_id", ColumnType::Simple(SimpleColumnType::BigInt)),
714                col("status", ColumnType::Simple(SimpleColumnType::Text)),
715                col(
716                    "created_at",
717                    ColumnType::Simple(SimpleColumnType::Timestamp),
718                ),
719            ],
720            constraints: vec![
721                // FK on user_id (column we're deleting) - should be excluded
722                TableConstraint::ForeignKey {
723                    name: None,
724                    columns: vec!["user_id".into()],
725                    ref_table: "users".into(),
726                    ref_columns: vec!["id".into()],
727                    on_delete: None,
728                    on_update: None,
729                },
730                // Index on created_at (different column) - should be preserved and recreated
731                TableConstraint::Index {
732                    name: None,
733                    columns: vec!["created_at".into()],
734                },
735                // Another FK on a different column - should be preserved
736                TableConstraint::ForeignKey {
737                    name: None,
738                    columns: vec!["status".into()],
739                    ref_table: "statuses".into(),
740                    ref_columns: vec!["code".into()],
741                    on_delete: None,
742                    on_update: None,
743                },
744            ],
745        }];
746
747        let result =
748            build_delete_column(&DatabaseBackend::Sqlite, "orders", "user_id", None, &schema);
749
750        let all_sql: Vec<String> = result
751            .iter()
752            .map(|q| q.build(DatabaseBackend::Sqlite))
753            .collect();
754        let combined_sql = all_sql.join("\n");
755
756        // Should use temp table approach (FK triggers it)
757        assert!(
758            combined_sql.contains("orders_temp"),
759            "Should use temp table approach for FK column deletion"
760        );
761
762        // Index on created_at should be recreated after rename
763        assert!(
764            combined_sql.contains("ix_orders__created_at"),
765            "Index on created_at should be recreated, got: {}",
766            combined_sql
767        );
768
769        // The FK on user_id should NOT appear (deleted column)
770        // But the FK on status should be preserved
771        assert!(
772            combined_sql.contains("REFERENCES \"statuses\""),
773            "FK on status should be preserved, got: {}",
774            combined_sql
775        );
776
777        // Count FK references - should only be 1 (status FK, not user_id FK)
778        let fk_patterns = combined_sql.matches("REFERENCES").count();
779        assert_eq!(
780            fk_patterns, 1,
781            "Only the FK on status should exist (not the one on user_id), got: {}",
782            combined_sql
783        );
784    }
785
786    // ==================== Snapshot Tests ====================
787
788    fn build_sql_snapshot(result: &[BuiltQuery], backend: DatabaseBackend) -> String {
789        result
790            .iter()
791            .map(|q| q.build(backend))
792            .collect::<Vec<_>>()
793            .join(";\n")
794    }
795
796    #[rstest]
797    #[case::postgres("postgres", DatabaseBackend::Postgres)]
798    #[case::mysql("mysql", DatabaseBackend::MySql)]
799    #[case::sqlite("sqlite", DatabaseBackend::Sqlite)]
800    fn test_delete_column_with_unique_constraint(
801        #[case] title: &str,
802        #[case] backend: DatabaseBackend,
803    ) {
804        let schema = vec![TableDef {
805            name: "users".into(),
806            description: None,
807            columns: vec![
808                col("id", ColumnType::Simple(SimpleColumnType::Integer)),
809                col("email", ColumnType::Simple(SimpleColumnType::Text)),
810                col("name", ColumnType::Simple(SimpleColumnType::Text)),
811            ],
812            constraints: vec![TableConstraint::Unique {
813                name: None,
814                columns: vec!["email".into()],
815            }],
816        }];
817
818        let result = build_delete_column(&backend, "users", "email", None, &schema);
819        let sql = build_sql_snapshot(&result, backend);
820
821        with_settings!({ snapshot_suffix => format!("delete_column_with_unique_{}", title) }, {
822            assert_snapshot!(sql);
823        });
824    }
825
826    #[rstest]
827    #[case::postgres("postgres", DatabaseBackend::Postgres)]
828    #[case::mysql("mysql", DatabaseBackend::MySql)]
829    #[case::sqlite("sqlite", DatabaseBackend::Sqlite)]
830    fn test_delete_column_with_index_constraint(
831        #[case] title: &str,
832        #[case] backend: DatabaseBackend,
833    ) {
834        let schema = vec![TableDef {
835            name: "posts".into(),
836            description: None,
837            columns: vec![
838                col("id", ColumnType::Simple(SimpleColumnType::Integer)),
839                col(
840                    "created_at",
841                    ColumnType::Simple(SimpleColumnType::Timestamp),
842                ),
843                col("title", ColumnType::Simple(SimpleColumnType::Text)),
844            ],
845            constraints: vec![TableConstraint::Index {
846                name: None,
847                columns: vec!["created_at".into()],
848            }],
849        }];
850
851        let result = build_delete_column(&backend, "posts", "created_at", None, &schema);
852        let sql = build_sql_snapshot(&result, backend);
853
854        with_settings!({ snapshot_suffix => format!("delete_column_with_index_{}", title) }, {
855            assert_snapshot!(sql);
856        });
857    }
858
859    #[rstest]
860    #[case::postgres("postgres", DatabaseBackend::Postgres)]
861    #[case::mysql("mysql", DatabaseBackend::MySql)]
862    #[case::sqlite("sqlite", DatabaseBackend::Sqlite)]
863    fn test_delete_column_with_fk_constraint(
864        #[case] title: &str,
865        #[case] backend: DatabaseBackend,
866    ) {
867        let schema = vec![TableDef {
868            name: "orders".into(),
869            description: None,
870            columns: vec![
871                col("id", ColumnType::Simple(SimpleColumnType::Integer)),
872                col("user_id", ColumnType::Simple(SimpleColumnType::BigInt)),
873                col("total", ColumnType::Simple(SimpleColumnType::Integer)),
874            ],
875            constraints: vec![TableConstraint::ForeignKey {
876                name: None,
877                columns: vec!["user_id".into()],
878                ref_table: "users".into(),
879                ref_columns: vec!["id".into()],
880                on_delete: None,
881                on_update: None,
882            }],
883        }];
884
885        let result = build_delete_column(&backend, "orders", "user_id", None, &schema);
886        let sql = build_sql_snapshot(&result, backend);
887
888        with_settings!({ snapshot_suffix => format!("delete_column_with_fk_{}", title) }, {
889            assert_snapshot!(sql);
890        });
891    }
892
893    #[rstest]
894    #[case::postgres("postgres", DatabaseBackend::Postgres)]
895    #[case::mysql("mysql", DatabaseBackend::MySql)]
896    #[case::sqlite("sqlite", DatabaseBackend::Sqlite)]
897    fn test_delete_column_with_pk_constraint(
898        #[case] title: &str,
899        #[case] backend: DatabaseBackend,
900    ) {
901        let schema = vec![TableDef {
902            name: "order_items".into(),
903            description: None,
904            columns: vec![
905                col("order_id", ColumnType::Simple(SimpleColumnType::Integer)),
906                col("product_id", ColumnType::Simple(SimpleColumnType::Integer)),
907                col("quantity", ColumnType::Simple(SimpleColumnType::Integer)),
908            ],
909            constraints: vec![TableConstraint::PrimaryKey {
910                auto_increment: false,
911                columns: vec!["order_id".into(), "product_id".into()],
912            }],
913        }];
914
915        let result = build_delete_column(&backend, "order_items", "product_id", None, &schema);
916        let sql = build_sql_snapshot(&result, backend);
917
918        with_settings!({ snapshot_suffix => format!("delete_column_with_pk_{}", title) }, {
919            assert_snapshot!(sql);
920        });
921    }
922
923    #[rstest]
924    #[case::postgres("postgres", DatabaseBackend::Postgres)]
925    #[case::mysql("mysql", DatabaseBackend::MySql)]
926    #[case::sqlite("sqlite", DatabaseBackend::Sqlite)]
927    fn test_delete_column_with_fk_and_index_constraints(
928        #[case] title: &str,
929        #[case] backend: DatabaseBackend,
930    ) {
931        // Complex case: FK on the deleted column + Index on another column
932        let schema = vec![TableDef {
933            name: "orders".into(),
934            description: None,
935            columns: vec![
936                col("id", ColumnType::Simple(SimpleColumnType::Integer)),
937                col("user_id", ColumnType::Simple(SimpleColumnType::BigInt)),
938                col(
939                    "created_at",
940                    ColumnType::Simple(SimpleColumnType::Timestamp),
941                ),
942                col("total", ColumnType::Simple(SimpleColumnType::Integer)),
943            ],
944            constraints: vec![
945                TableConstraint::ForeignKey {
946                    name: None,
947                    columns: vec!["user_id".into()],
948                    ref_table: "users".into(),
949                    ref_columns: vec!["id".into()],
950                    on_delete: None,
951                    on_update: None,
952                },
953                TableConstraint::Index {
954                    name: None,
955                    columns: vec!["created_at".into()],
956                },
957            ],
958        }];
959
960        let result = build_delete_column(&backend, "orders", "user_id", None, &schema);
961        let sql = build_sql_snapshot(&result, backend);
962
963        with_settings!({ snapshot_suffix => format!("delete_column_with_fk_and_index_{}", title) }, {
964            assert_snapshot!(sql);
965        });
966    }
967
968    #[test]
969    fn test_delete_column_sqlite_temp_table_with_enum_column() {
970        // SQLite temp table approach with enum column type
971        // This tests lines 122-124: enum type drop in temp table function
972        use vespertide_core::EnumValues;
973
974        let enum_type = ColumnType::Complex(ComplexColumnType::Enum {
975            name: "order_status".into(),
976            values: EnumValues::String(vec![
977                "pending".into(),
978                "shipped".into(),
979                "delivered".into(),
980            ]),
981        });
982
983        let schema = vec![TableDef {
984            name: "orders".into(),
985            description: None,
986            columns: vec![
987                col("id", ColumnType::Simple(SimpleColumnType::Integer)),
988                col("user_id", ColumnType::Simple(SimpleColumnType::BigInt)),
989                col("status", enum_type.clone()),
990            ],
991            constraints: vec![TableConstraint::ForeignKey {
992                name: None,
993                columns: vec!["user_id".into()],
994                ref_table: "users".into(),
995                ref_columns: vec!["id".into()],
996                on_delete: None,
997                on_update: None,
998            }],
999        }];
1000
1001        // Delete the FK column (user_id) with an enum type - triggers temp table AND enum drop
1002        let result = build_delete_column(
1003            &DatabaseBackend::Sqlite,
1004            "orders",
1005            "user_id",
1006            Some(&enum_type),
1007            &schema,
1008        );
1009
1010        // Should use temp table approach (FK triggers it) + DROP TYPE at end
1011        assert!(
1012            result.len() >= 4,
1013            "Expected at least 4 statements for temp table approach, got: {}",
1014            result.len()
1015        );
1016
1017        let all_sql: Vec<String> = result
1018            .iter()
1019            .map(|q| q.build(DatabaseBackend::Sqlite))
1020            .collect();
1021        let combined_sql = all_sql.join("\n");
1022
1023        // Verify temp table approach
1024        assert!(
1025            combined_sql.contains("orders_temp"),
1026            "Should use temp table approach"
1027        );
1028
1029        // The DROP TYPE statement should be empty for SQLite (only applies to PostgreSQL)
1030        // but the code path should still be executed
1031        let last_stmt = result.last().unwrap();
1032        let last_sql = last_stmt.build(DatabaseBackend::Sqlite);
1033        // SQLite doesn't have DROP TYPE, so it should be empty string
1034        assert!(
1035            last_sql.is_empty() || !last_sql.contains("DROP TYPE"),
1036            "SQLite should not emit DROP TYPE"
1037        );
1038
1039        // Verify it DOES emit DROP TYPE for PostgreSQL
1040        let pg_last_sql = last_stmt.build(DatabaseBackend::Postgres);
1041        assert!(
1042            pg_last_sql.contains("DROP TYPE"),
1043            "PostgreSQL should emit DROP TYPE, got: {}",
1044            pg_last_sql
1045        );
1046    }
1047
1048    #[test]
1049    fn test_delete_column_sqlite_with_check_constraint_referencing_column() {
1050        // When a CHECK constraint references the column being deleted,
1051        // SQLite can't use ALTER TABLE DROP COLUMN — must use temp table approach.
1052        let schema = vec![TableDef {
1053            name: "orders".into(),
1054            description: None,
1055            columns: vec![
1056                col("id", ColumnType::Simple(SimpleColumnType::Integer)),
1057                col("amount", ColumnType::Simple(SimpleColumnType::Integer)),
1058            ],
1059            constraints: vec![TableConstraint::Check {
1060                name: "check_positive".into(),
1061                expr: "amount > 0".into(),
1062            }],
1063        }];
1064
1065        // Delete amount column — CHECK references it, so temp table is needed
1066        let result =
1067            build_delete_column(&DatabaseBackend::Sqlite, "orders", "amount", None, &schema);
1068
1069        // Should use temp table approach (CREATE temp, INSERT, DROP, RENAME)
1070        assert!(
1071            result.len() >= 4,
1072            "Expected temp table approach (>=4 stmts), got: {} statements",
1073            result.len()
1074        );
1075
1076        let sql = result[0].build(DatabaseBackend::Sqlite);
1077        assert!(
1078            sql.contains("orders_temp"),
1079            "Expected temp table creation, got: {}",
1080            sql
1081        );
1082        // The CHECK constraint referencing "amount" should NOT be in the temp table
1083        assert!(
1084            !sql.contains("check_positive"),
1085            "CHECK referencing deleted column should be removed, got: {}",
1086            sql
1087        );
1088    }
1089
1090    #[test]
1091    fn test_delete_column_sqlite_with_check_constraint_not_referencing_column() {
1092        // When a CHECK constraint does NOT reference the column being deleted,
1093        // simple DROP COLUMN should work.
1094        let schema = vec![TableDef {
1095            name: "orders".into(),
1096            description: None,
1097            columns: vec![
1098                col("id", ColumnType::Simple(SimpleColumnType::Integer)),
1099                col("amount", ColumnType::Simple(SimpleColumnType::Integer)),
1100                col("note", ColumnType::Simple(SimpleColumnType::Text)),
1101            ],
1102            constraints: vec![TableConstraint::Check {
1103                name: "check_positive".into(),
1104                expr: "amount > 0".into(),
1105            }],
1106        }];
1107
1108        // Delete "note" column — CHECK only references "amount", not "note"
1109        let result = build_delete_column(&DatabaseBackend::Sqlite, "orders", "note", None, &schema);
1110
1111        assert_eq!(
1112            result.len(),
1113            1,
1114            "Unrelated CHECK should be skipped, got: {} statements",
1115            result.len()
1116        );
1117
1118        let sql = result[0].build(DatabaseBackend::Sqlite);
1119        assert!(
1120            sql.contains("DROP COLUMN"),
1121            "Expected DROP COLUMN, got: {}",
1122            sql
1123        );
1124    }
1125}
vespertide_query/sql/delete_column.rs

vespertide_query/sql/
delete_column.rs
