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

vespertide_query/sql/
delete_column.rs
