vespertide_query/sql/
delete_column.rs

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

vespertide_query/sql/
delete_column.rs
