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

vespertide_query/sql/
delete_column.rs
