vespertide_query/sql/delete_column/
mod.rs

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

vespertide_query/sql/delete_column/
mod.rs
