vespertide_query/sql/
mod.rs

1pub mod add_column;
2pub mod add_constraint;
3pub mod create_table;
4pub mod delete_column;
5pub mod delete_table;
6pub mod helpers;
7pub mod modify_column_comment;
8pub mod modify_column_default;
9pub mod modify_column_nullable;
10pub mod modify_column_type;
11pub mod raw_sql;
12pub mod remove_constraint;
13pub mod rename_column;
14pub mod rename_table;
15pub mod replace_constraint;
16pub mod types;
17
18pub use helpers::*;
19pub use types::{BuiltQuery, DatabaseBackend, RawSql};
20
21use crate::error::QueryError;
22use vespertide_core::{MigrationAction, TableConstraint, TableDef};
23
24use self::{
25    add_column::build_add_column, add_constraint::build_add_constraint,
26    create_table::build_create_table, delete_column::build_delete_column,
27    delete_table::build_delete_table, modify_column_comment::build_modify_column_comment,
28    modify_column_default::build_modify_column_default,
29    modify_column_nullable::build_modify_column_nullable,
30    modify_column_type::build_modify_column_type, raw_sql::build_raw_sql,
31    remove_constraint::build_remove_constraint, rename_column::build_rename_column,
32    rename_table::build_rename_table, replace_constraint::build_replace_constraint,
33};
34
35pub fn build_action_queries(
36    backend: &DatabaseBackend,
37    action: &MigrationAction,
38    current_schema: &[TableDef],
39) -> Result<Vec<BuiltQuery>, QueryError> {
40    build_action_queries_with_pending(backend, action, current_schema, &[])
41}
42
43/// Build SQL queries for a migration action, with awareness of pending constraints.
44///
45/// `pending_constraints` are constraints that exist in the logical schema but haven't been
46/// physically created as database indexes yet. This is used by SQLite temp table rebuilds
47/// to avoid recreating indexes that will be created by future AddConstraint actions.
48pub fn build_action_queries_with_pending(
49    backend: &DatabaseBackend,
50    action: &MigrationAction,
51    current_schema: &[TableDef],
52    pending_constraints: &[TableConstraint],
53) -> Result<Vec<BuiltQuery>, QueryError> {
54    match action {
55        MigrationAction::CreateTable {
56            table,
57            columns,
58            constraints,
59        } => build_create_table(backend, table, columns, constraints),
60
61        MigrationAction::DeleteTable { table } => Ok(vec![build_delete_table(table)]),
62
63        MigrationAction::AddColumn {
64            table,
65            column,
66            fill_with,
67        } => build_add_column(
68            backend,
69            table,
70            column,
71            fill_with.as_deref(),
72            current_schema,
73            pending_constraints,
74        ),
75
76        MigrationAction::RenameColumn { table, from, to } => {
77            Ok(vec![build_rename_column(table, from, to)])
78        }
79
80        MigrationAction::DeleteColumn { table, column } => {
81            // Find the column type from current schema for enum DROP TYPE support
82            let column_type = current_schema
83                .iter()
84                .find(|t| t.name == *table)
85                .and_then(|t| t.columns.iter().find(|c| c.name == *column))
86                .map(|c| &c.r#type);
87            Ok(build_delete_column(
88                backend,
89                table,
90                column,
91                column_type,
92                current_schema,
93                pending_constraints,
94            ))
95        }
96
97        MigrationAction::ModifyColumnType {
98            table,
99            column,
100            new_type,
101            fill_with,
102        } => build_modify_column_type(
103            backend,
104            table,
105            column,
106            new_type,
107            fill_with.as_ref(),
108            current_schema,
109            pending_constraints,
110        ),
111
112        MigrationAction::ModifyColumnNullable {
113            table,
114            column,
115            nullable,
116            fill_with,
117            delete_null_rows,
118        } => build_modify_column_nullable(
119            backend,
120            table,
121            column,
122            *nullable,
123            fill_with.as_deref(),
124            delete_null_rows.unwrap_or(false),
125            current_schema,
126            pending_constraints,
127        ),
128
129        MigrationAction::ModifyColumnDefault {
130            table,
131            column,
132            new_default,
133        } => build_modify_column_default(
134            backend,
135            table,
136            column,
137            new_default.as_deref(),
138            current_schema,
139            pending_constraints,
140        ),
141
142        MigrationAction::ModifyColumnComment {
143            table,
144            column,
145            new_comment,
146        } => build_modify_column_comment(
147            backend,
148            table,
149            column,
150            new_comment.as_deref(),
151            current_schema,
152        ),
153
154        MigrationAction::RenameTable { from, to } => Ok(vec![build_rename_table(from, to)]),
155
156        MigrationAction::RawSql { sql } => Ok(vec![build_raw_sql(sql.clone())]),
157
158        MigrationAction::AddConstraint { table, constraint } => build_add_constraint(
159            backend,
160            table,
161            constraint,
162            current_schema,
163            pending_constraints,
164        ),
165
166        MigrationAction::RemoveConstraint { table, constraint } => build_remove_constraint(
167            backend,
168            table,
169            constraint,
170            current_schema,
171            pending_constraints,
172        ),
173
174        MigrationAction::ReplaceConstraint { table, from, to } => build_replace_constraint(
175            backend,
176            table,
177            from,
178            to,
179            current_schema,
180            pending_constraints,
181        ),
182    }
183}
184
185#[cfg(test)]
186mod tests {
187    use super::*;
188    use insta::{assert_snapshot, with_settings};
189    use rstest::rstest;
190    use vespertide_core::schema::primary_key::PrimaryKeySyntax;
191    use vespertide_core::{
192        ColumnDef, ColumnType, MigrationAction, ReferenceAction, SimpleColumnType, TableConstraint,
193    };
194
195    fn col(name: &str, ty: ColumnType) -> ColumnDef {
196        ColumnDef {
197            name: name.to_string(),
198            r#type: ty,
199            nullable: true,
200            default: None,
201            comment: None,
202            primary_key: None,
203            unique: None,
204            index: None,
205            foreign_key: None,
206        }
207    }
208
209    #[test]
210    fn test_backend_specific_quoting() {
211        let action = MigrationAction::CreateTable {
212            table: "users".into(),
213            columns: vec![col("id", ColumnType::Simple(SimpleColumnType::Integer))],
214            constraints: vec![],
215        };
216        let result = build_action_queries(&DatabaseBackend::Postgres, &action, &[]).unwrap();
217
218        // PostgreSQL uses double quotes
219        let pg_sql = result[0].build(DatabaseBackend::Postgres);
220        assert!(pg_sql.contains("\"users\""));
221
222        // MySQL uses backticks
223        let mysql_sql = result[0].build(DatabaseBackend::MySql);
224        assert!(mysql_sql.contains("`users`"));
225
226        // SQLite uses double quotes
227        let sqlite_sql = result[0].build(DatabaseBackend::Sqlite);
228        assert!(sqlite_sql.contains("\"users\""));
229    }
230
231    #[rstest]
232    #[case::create_table_with_default_postgres(
233        "create_table_with_default_postgres",
234        MigrationAction::CreateTable {
235            table: "users".into(),
236            columns: vec![ColumnDef {
237                name: "status".into(),
238                r#type: ColumnType::Simple(SimpleColumnType::Text),
239                nullable: true,
240                default: Some("'active'".into()),
241                comment: None,
242                primary_key: None,
243                unique: None,
244                index: None,
245                foreign_key: None,
246            }],
247            constraints: vec![],
248        },
249        DatabaseBackend::Postgres,
250        &["DEFAULT", "'active'"]
251    )]
252    #[case::create_table_with_default_mysql(
253        "create_table_with_default_mysql",
254        MigrationAction::CreateTable {
255            table: "users".into(),
256            columns: vec![ColumnDef {
257                name: "status".into(),
258                r#type: ColumnType::Simple(SimpleColumnType::Text),
259                nullable: true,
260                default: Some("'active'".into()),
261                comment: None,
262                primary_key: None,
263                unique: None,
264                index: None,
265                foreign_key: None,
266            }],
267            constraints: vec![],
268        },
269        DatabaseBackend::Postgres,
270        &["DEFAULT", "'active'"]
271    )]
272    #[case::create_table_with_default_sqlite(
273        "create_table_with_default_sqlite",
274        MigrationAction::CreateTable {
275            table: "users".into(),
276            columns: vec![ColumnDef {
277                name: "status".into(),
278                r#type: ColumnType::Simple(SimpleColumnType::Text),
279                nullable: true,
280                default: Some("'active'".into()),
281                comment: None,
282                primary_key: None,
283                unique: None,
284                index: None,
285                foreign_key: None,
286            }],
287            constraints: vec![],
288        },
289        DatabaseBackend::Postgres,
290        &["DEFAULT", "'active'"]
291    )]
292    #[case::create_table_with_inline_primary_key_postgres(
293        "create_table_with_inline_primary_key_postgres",
294        MigrationAction::CreateTable {
295            table: "users".into(),
296            columns: vec![ColumnDef {
297                name: "id".into(),
298                r#type: ColumnType::Simple(SimpleColumnType::Integer),
299                nullable: false,
300                default: None,
301                comment: None,
302                primary_key: Some(PrimaryKeySyntax::Bool(true)),
303                unique: None,
304                index: None,
305                foreign_key: None,
306            }],
307            constraints: vec![],
308        },
309        DatabaseBackend::Postgres,
310        &["PRIMARY KEY"]
311    )]
312    #[case::create_table_with_inline_primary_key_mysql(
313        "create_table_with_inline_primary_key_mysql",
314        MigrationAction::CreateTable {
315            table: "users".into(),
316            columns: vec![ColumnDef {
317                name: "id".into(),
318                r#type: ColumnType::Simple(SimpleColumnType::Integer),
319                nullable: false,
320                default: None,
321                comment: None,
322                primary_key: Some(PrimaryKeySyntax::Bool(true)),
323                unique: None,
324                index: None,
325                foreign_key: None,
326            }],
327            constraints: vec![],
328        },
329        DatabaseBackend::Postgres,
330        &["PRIMARY KEY"]
331    )]
332    #[case::create_table_with_inline_primary_key_sqlite(
333        "create_table_with_inline_primary_key_sqlite",
334        MigrationAction::CreateTable {
335            table: "users".into(),
336            columns: vec![ColumnDef {
337                name: "id".into(),
338                r#type: ColumnType::Simple(SimpleColumnType::Integer),
339                nullable: false,
340                default: None,
341                comment: None,
342                primary_key: Some(PrimaryKeySyntax::Bool(true)),
343                unique: None,
344                index: None,
345                foreign_key: None,
346            }],
347            constraints: vec![],
348        },
349        DatabaseBackend::Postgres,
350        &["PRIMARY KEY"]
351    )]
352    #[case::create_table_with_fk_postgres(
353        "create_table_with_fk_postgres",
354        MigrationAction::CreateTable {
355            table: "posts".into(),
356            columns: vec![
357                col("id", ColumnType::Simple(SimpleColumnType::Integer)),
358                col("user_id", ColumnType::Simple(SimpleColumnType::Integer)),
359            ],
360            constraints: vec![TableConstraint::ForeignKey {
361                name: Some("fk_user".into()),
362                columns: vec!["user_id".into()],
363                ref_table: "users".into(),
364                ref_columns: vec!["id".into()],
365                on_delete: Some(ReferenceAction::Cascade),
366                on_update: Some(ReferenceAction::Restrict),
367            }],
368        },
369        DatabaseBackend::Postgres,
370        &["REFERENCES \"users\" (\"id\")", "ON DELETE CASCADE", "ON UPDATE RESTRICT"]
371    )]
372    #[case::create_table_with_fk_mysql(
373        "create_table_with_fk_mysql",
374        MigrationAction::CreateTable {
375            table: "posts".into(),
376            columns: vec![
377                col("id", ColumnType::Simple(SimpleColumnType::Integer)),
378                col("user_id", ColumnType::Simple(SimpleColumnType::Integer)),
379            ],
380            constraints: vec![TableConstraint::ForeignKey {
381                name: Some("fk_user".into()),
382                columns: vec!["user_id".into()],
383                ref_table: "users".into(),
384                ref_columns: vec!["id".into()],
385                on_delete: Some(ReferenceAction::Cascade),
386                on_update: Some(ReferenceAction::Restrict),
387            }],
388        },
389        DatabaseBackend::Postgres,
390        &["REFERENCES \"users\" (\"id\")", "ON DELETE CASCADE", "ON UPDATE RESTRICT"]
391    )]
392    #[case::create_table_with_fk_sqlite(
393        "create_table_with_fk_sqlite",
394        MigrationAction::CreateTable {
395            table: "posts".into(),
396            columns: vec![
397                col("id", ColumnType::Simple(SimpleColumnType::Integer)),
398                col("user_id", ColumnType::Simple(SimpleColumnType::Integer)),
399            ],
400            constraints: vec![TableConstraint::ForeignKey {
401                name: Some("fk_user".into()),
402                columns: vec!["user_id".into()],
403                ref_table: "users".into(),
404                ref_columns: vec!["id".into()],
405                on_delete: Some(ReferenceAction::Cascade),
406                on_update: Some(ReferenceAction::Restrict),
407            }],
408        },
409        DatabaseBackend::Postgres,
410        &["REFERENCES \"users\" (\"id\")", "ON DELETE CASCADE", "ON UPDATE RESTRICT"]
411    )]
412    fn test_build_migration_action(
413        #[case] title: &str,
414        #[case] action: MigrationAction,
415        #[case] backend: DatabaseBackend,
416        #[case] expected: &[&str],
417    ) {
418        let result = build_action_queries(&backend, &action, &[]).unwrap();
419        let sql = result[0].build(backend);
420        for exp in expected {
421            assert!(
422                sql.contains(exp),
423                "Expected SQL to contain '{}', got: {}",
424                exp,
425                sql
426            );
427        }
428
429        with_settings!({ snapshot_suffix => format!("build_migration_action_{}", title) }, {
430            assert_snapshot!(result.iter().map(|q| q.build(backend)).collect::<Vec<String>>().join("\n"));
431        });
432    }
433
434    #[rstest]
435    #[case::rename_column_postgres(DatabaseBackend::Postgres)]
436    #[case::rename_column_mysql(DatabaseBackend::MySql)]
437    #[case::rename_column_sqlite(DatabaseBackend::Sqlite)]
438    fn test_build_action_queries_rename_column(#[case] backend: DatabaseBackend) {
439        // Test MigrationAction::RenameColumn (lines 51-52)
440        let action = MigrationAction::RenameColumn {
441            table: "users".into(),
442            from: "old_name".into(),
443            to: "new_name".into(),
444        };
445        let result = build_action_queries(&backend, &action, &[]).unwrap();
446        assert_eq!(result.len(), 1);
447        let sql = result[0].build(backend);
448        assert!(sql.contains("RENAME"));
449        assert!(sql.contains("old_name"));
450        assert!(sql.contains("new_name"));
451
452        with_settings!({ snapshot_suffix => format!("rename_column_{:?}", backend) }, {
453            assert_snapshot!(sql);
454        });
455    }
456
457    #[rstest]
458    #[case::delete_column_postgres(DatabaseBackend::Postgres)]
459    #[case::delete_column_mysql(DatabaseBackend::MySql)]
460    #[case::delete_column_sqlite(DatabaseBackend::Sqlite)]
461    fn test_build_action_queries_delete_column(#[case] backend: DatabaseBackend) {
462        // Test MigrationAction::DeleteColumn (lines 55-56)
463        let action = MigrationAction::DeleteColumn {
464            table: "users".into(),
465            column: "email".into(),
466        };
467        let result = build_action_queries(&backend, &action, &[]).unwrap();
468        assert_eq!(result.len(), 1);
469        let sql = result[0].build(backend);
470        assert!(sql.contains("DROP COLUMN"));
471        assert!(sql.contains("email"));
472
473        with_settings!({ snapshot_suffix => format!("delete_column_{:?}", backend) }, {
474            assert_snapshot!(sql);
475        });
476    }
477
478    #[rstest]
479    #[case::modify_column_type_postgres(DatabaseBackend::Postgres)]
480    #[case::modify_column_type_mysql(DatabaseBackend::MySql)]
481    #[case::modify_column_type_sqlite(DatabaseBackend::Sqlite)]
482    fn test_build_action_queries_modify_column_type(#[case] backend: DatabaseBackend) {
483        // Test MigrationAction::ModifyColumnType (lines 60-63)
484        let action = MigrationAction::ModifyColumnType {
485            table: "users".into(),
486            column: "age".into(),
487            new_type: ColumnType::Simple(SimpleColumnType::BigInt),
488            fill_with: None,
489        };
490        let current_schema = vec![TableDef {
491            name: "users".into(),
492            description: None,
493            columns: vec![ColumnDef {
494                name: "age".into(),
495                r#type: ColumnType::Simple(SimpleColumnType::Integer),
496                nullable: true,
497                default: None,
498                comment: None,
499                primary_key: None,
500                unique: None,
501                index: None,
502                foreign_key: None,
503            }],
504            constraints: vec![],
505        }];
506        let result = build_action_queries(&backend, &action, &current_schema).unwrap();
507        assert!(!result.is_empty());
508        let sql = result
509            .iter()
510            .map(|q| q.build(backend))
511            .collect::<Vec<String>>()
512            .join("\n");
513        assert!(sql.contains("ALTER TABLE"));
514
515        with_settings!({ snapshot_suffix => format!("modify_column_type_{:?}", backend) }, {
516            assert_snapshot!(sql);
517        });
518    }
519
520    #[rstest]
521    #[case::remove_index_constraint_postgres(DatabaseBackend::Postgres)]
522    #[case::remove_index_constraint_mysql(DatabaseBackend::MySql)]
523    #[case::remove_index_constraint_sqlite(DatabaseBackend::Sqlite)]
524    fn test_build_action_queries_remove_index_constraint(#[case] backend: DatabaseBackend) {
525        // Test MigrationAction::RemoveConstraint with Index variant
526        let action = MigrationAction::RemoveConstraint {
527            table: "users".into(),
528            constraint: TableConstraint::Index {
529                name: Some("idx_email".into()),
530                columns: vec!["email".into()],
531            },
532        };
533        let result = build_action_queries(&backend, &action, &[]).unwrap();
534        assert_eq!(result.len(), 1);
535        let sql = result[0].build(backend);
536        assert!(sql.contains("DROP INDEX"));
537        assert!(sql.contains("idx_email"));
538
539        with_settings!({ snapshot_suffix => format!("remove_index_constraint_{:?}", backend) }, {
540            assert_snapshot!(sql);
541        });
542    }
543
544    #[rstest]
545    #[case::rename_table_postgres(DatabaseBackend::Postgres)]
546    #[case::rename_table_mysql(DatabaseBackend::MySql)]
547    #[case::rename_table_sqlite(DatabaseBackend::Sqlite)]
548    fn test_build_action_queries_rename_table(#[case] backend: DatabaseBackend) {
549        // Test MigrationAction::RenameTable (line 69)
550        let action = MigrationAction::RenameTable {
551            from: "old_table".into(),
552            to: "new_table".into(),
553        };
554        let result = build_action_queries(&backend, &action, &[]).unwrap();
555        assert_eq!(result.len(), 1);
556        let sql = result[0].build(backend);
557        assert!(sql.contains("RENAME"));
558        assert!(sql.contains("old_table"));
559        assert!(sql.contains("new_table"));
560
561        with_settings!({ snapshot_suffix => format!("rename_table_{:?}", backend) }, {
562            assert_snapshot!(sql);
563        });
564    }
565
566    #[rstest]
567    #[case::add_constraint_postgres(DatabaseBackend::Postgres)]
568    #[case::add_constraint_mysql(DatabaseBackend::MySql)]
569    #[case::add_constraint_sqlite(DatabaseBackend::Sqlite)]
570    fn test_build_action_queries_add_constraint(#[case] backend: DatabaseBackend) {
571        // Test MigrationAction::AddConstraint (lines 73-74)
572        let action = MigrationAction::AddConstraint {
573            table: "users".into(),
574            constraint: TableConstraint::Unique {
575                name: Some("uq_email".into()),
576                columns: vec!["email".into()],
577            },
578        };
579        let current_schema = vec![TableDef {
580            name: "users".into(),
581            description: None,
582            columns: vec![
583                ColumnDef {
584                    name: "id".into(),
585                    r#type: ColumnType::Simple(SimpleColumnType::Integer),
586                    nullable: false,
587                    default: None,
588                    comment: None,
589                    primary_key: None,
590                    unique: None,
591                    index: None,
592                    foreign_key: None,
593                },
594                ColumnDef {
595                    name: "email".into(),
596                    r#type: ColumnType::Simple(SimpleColumnType::Text),
597                    nullable: true,
598                    default: None,
599                    comment: None,
600                    primary_key: None,
601                    unique: None,
602                    index: None,
603                    foreign_key: None,
604                },
605            ],
606            constraints: vec![],
607        }];
608        let result = build_action_queries(&backend, &action, &current_schema).unwrap();
609        assert!(!result.is_empty());
610        let sql = result
611            .iter()
612            .map(|q| q.build(backend))
613            .collect::<Vec<String>>()
614            .join("\n");
615        assert!(sql.contains("UNIQUE") || sql.contains("uq_email"));
616
617        with_settings!({ snapshot_suffix => format!("add_constraint_{:?}", backend) }, {
618            assert_snapshot!(sql);
619        });
620    }
621
622    #[rstest]
623    #[case::remove_constraint_postgres(DatabaseBackend::Postgres)]
624    #[case::remove_constraint_mysql(DatabaseBackend::MySql)]
625    #[case::remove_constraint_sqlite(DatabaseBackend::Sqlite)]
626    fn test_build_action_queries_remove_constraint(#[case] backend: DatabaseBackend) {
627        // Test MigrationAction::RemoveConstraint (lines 77-78)
628        let action = MigrationAction::RemoveConstraint {
629            table: "users".into(),
630            constraint: TableConstraint::Unique {
631                name: Some("uq_email".into()),
632                columns: vec!["email".into()],
633            },
634        };
635        let current_schema = vec![TableDef {
636            name: "users".into(),
637            description: None,
638            columns: vec![
639                ColumnDef {
640                    name: "id".into(),
641                    r#type: ColumnType::Simple(SimpleColumnType::Integer),
642                    nullable: false,
643                    default: None,
644                    comment: None,
645                    primary_key: None,
646                    unique: None,
647                    index: None,
648                    foreign_key: None,
649                },
650                ColumnDef {
651                    name: "email".into(),
652                    r#type: ColumnType::Simple(SimpleColumnType::Text),
653                    nullable: true,
654                    default: None,
655                    comment: None,
656                    primary_key: None,
657                    unique: None,
658                    index: None,
659                    foreign_key: None,
660                },
661            ],
662            constraints: vec![TableConstraint::Unique {
663                name: Some("uq_email".into()),
664                columns: vec!["email".into()],
665            }],
666        }];
667        let result = build_action_queries(&backend, &action, &current_schema).unwrap();
668        assert!(!result.is_empty());
669        let sql = result
670            .iter()
671            .map(|q| q.build(backend))
672            .collect::<Vec<String>>()
673            .join("\n");
674        assert!(sql.contains("DROP") || sql.contains("CONSTRAINT"));
675
676        with_settings!({ snapshot_suffix => format!("remove_constraint_{:?}", backend) }, {
677            assert_snapshot!(sql);
678        });
679    }
680
681    #[rstest]
682    #[case::add_column_postgres(DatabaseBackend::Postgres)]
683    #[case::add_column_mysql(DatabaseBackend::MySql)]
684    #[case::add_column_sqlite(DatabaseBackend::Sqlite)]
685    fn test_build_action_queries_add_column(#[case] backend: DatabaseBackend) {
686        // Test MigrationAction::AddColumn (lines 46-49)
687        let action = MigrationAction::AddColumn {
688            table: "users".into(),
689            column: Box::new(ColumnDef {
690                name: "email".into(),
691                r#type: ColumnType::Simple(SimpleColumnType::Text),
692                nullable: true,
693                default: None,
694                comment: None,
695                primary_key: None,
696                unique: None,
697                index: None,
698                foreign_key: None,
699            }),
700            fill_with: None,
701        };
702        let current_schema = vec![TableDef {
703            name: "users".into(),
704            description: None,
705            columns: vec![ColumnDef {
706                name: "id".into(),
707                r#type: ColumnType::Simple(SimpleColumnType::Integer),
708                nullable: false,
709                default: None,
710                comment: None,
711                primary_key: None,
712                unique: None,
713                index: None,
714                foreign_key: None,
715            }],
716            constraints: vec![],
717        }];
718        let result = build_action_queries(&backend, &action, &current_schema).unwrap();
719        assert!(!result.is_empty());
720        let sql = result
721            .iter()
722            .map(|q| q.build(backend))
723            .collect::<Vec<String>>()
724            .join("\n");
725        assert!(sql.contains("ALTER TABLE"));
726        assert!(sql.contains("ADD COLUMN") || sql.contains("ADD"));
727
728        with_settings!({ snapshot_suffix => format!("add_column_{:?}", backend) }, {
729            assert_snapshot!(sql);
730        });
731    }
732
733    #[rstest]
734    #[case::add_index_constraint_postgres(DatabaseBackend::Postgres)]
735    #[case::add_index_constraint_mysql(DatabaseBackend::MySql)]
736    #[case::add_index_constraint_sqlite(DatabaseBackend::Sqlite)]
737    fn test_build_action_queries_add_index_constraint(#[case] backend: DatabaseBackend) {
738        // Test MigrationAction::AddConstraint with Index variant
739        let action = MigrationAction::AddConstraint {
740            table: "users".into(),
741            constraint: TableConstraint::Index {
742                name: Some("idx_email".into()),
743                columns: vec!["email".into()],
744            },
745        };
746        let result = build_action_queries(&backend, &action, &[]).unwrap();
747        assert_eq!(result.len(), 1);
748        let sql = result[0].build(backend);
749        assert!(sql.contains("CREATE INDEX"));
750        assert!(sql.contains("idx_email"));
751
752        with_settings!({ snapshot_suffix => format!("add_index_constraint_{:?}", backend) }, {
753            assert_snapshot!(sql);
754        });
755    }
756
757    #[rstest]
758    #[case::raw_sql_postgres(DatabaseBackend::Postgres)]
759    #[case::raw_sql_mysql(DatabaseBackend::MySql)]
760    #[case::raw_sql_sqlite(DatabaseBackend::Sqlite)]
761    fn test_build_action_queries_raw_sql(#[case] backend: DatabaseBackend) {
762        // Test MigrationAction::RawSql (line 71)
763        let action = MigrationAction::RawSql {
764            sql: "SELECT 1;".into(),
765        };
766        let result = build_action_queries(&backend, &action, &[]).unwrap();
767        assert_eq!(result.len(), 1);
768        let sql = result[0].build(backend);
769        assert_eq!(sql, "SELECT 1;");
770
771        with_settings!({ snapshot_suffix => format!("raw_sql_{:?}", backend) }, {
772            assert_snapshot!(sql);
773        });
774    }
775
776    // Comprehensive index naming tests
777    #[rstest]
778    #[case::add_index_with_custom_name_postgres(
779        DatabaseBackend::Postgres,
780        "hello",
781        vec!["email", "password"]
782    )]
783    #[case::add_index_with_custom_name_mysql(
784        DatabaseBackend::MySql,
785        "hello",
786        vec!["email", "password"]
787    )]
788    #[case::add_index_with_custom_name_sqlite(
789        DatabaseBackend::Sqlite,
790        "hello",
791        vec!["email", "password"]
792    )]
793    #[case::add_index_single_column_postgres(
794        DatabaseBackend::Postgres,
795        "email_idx",
796        vec!["email"]
797    )]
798    #[case::add_index_single_column_mysql(
799        DatabaseBackend::MySql,
800        "email_idx",
801        vec!["email"]
802    )]
803    #[case::add_index_single_column_sqlite(
804        DatabaseBackend::Sqlite,
805        "email_idx",
806        vec!["email"]
807    )]
808    fn test_add_index_with_custom_name(
809        #[case] backend: DatabaseBackend,
810        #[case] index_name: &str,
811        #[case] columns: Vec<&str>,
812    ) {
813        // Test that custom index names follow ix_table__name pattern
814        let action = MigrationAction::AddConstraint {
815            table: "user".into(),
816            constraint: TableConstraint::Index {
817                name: Some(index_name.into()),
818                columns: columns.iter().map(|s| s.to_string()).collect(),
819            },
820        };
821        let result = build_action_queries(&backend, &action, &[]).unwrap();
822        let sql = result[0].build(backend);
823
824        // Should use ix_table__name pattern
825        let expected_name = format!("ix_user__{}", index_name);
826        assert!(
827            sql.contains(&expected_name),
828            "Expected index name '{}' in SQL: {}",
829            expected_name,
830            sql
831        );
832
833        with_settings!({ snapshot_suffix => format!("add_index_custom_{}_{:?}", index_name, backend) }, {
834            assert_snapshot!(sql);
835        });
836    }
837
838    #[rstest]
839    #[case::add_unnamed_index_single_column_postgres(
840        DatabaseBackend::Postgres,
841        vec!["email"]
842    )]
843    #[case::add_unnamed_index_single_column_mysql(
844        DatabaseBackend::MySql,
845        vec!["email"]
846    )]
847    #[case::add_unnamed_index_single_column_sqlite(
848        DatabaseBackend::Sqlite,
849        vec!["email"]
850    )]
851    #[case::add_unnamed_index_multiple_columns_postgres(
852        DatabaseBackend::Postgres,
853        vec!["email", "password"]
854    )]
855    #[case::add_unnamed_index_multiple_columns_mysql(
856        DatabaseBackend::MySql,
857        vec!["email", "password"]
858    )]
859    #[case::add_unnamed_index_multiple_columns_sqlite(
860        DatabaseBackend::Sqlite,
861        vec!["email", "password"]
862    )]
863    fn test_add_unnamed_index(#[case] backend: DatabaseBackend, #[case] columns: Vec<&str>) {
864        // Test that unnamed indexes follow ix_table__col1_col2 pattern
865        let action = MigrationAction::AddConstraint {
866            table: "user".into(),
867            constraint: TableConstraint::Index {
868                name: None,
869                columns: columns.iter().map(|s| s.to_string()).collect(),
870            },
871        };
872        let result = build_action_queries(&backend, &action, &[]).unwrap();
873        let sql = result[0].build(backend);
874
875        // Should use ix_table__col1_col2... pattern
876        let expected_name = format!("ix_user__{}", columns.join("_"));
877        assert!(
878            sql.contains(&expected_name),
879            "Expected index name '{}' in SQL: {}",
880            expected_name,
881            sql
882        );
883
884        with_settings!({ snapshot_suffix => format!("add_unnamed_index_{}_{:?}", columns.join("_"), backend) }, {
885            assert_snapshot!(sql);
886        });
887    }
888
889    #[rstest]
890    #[case::remove_index_with_custom_name_postgres(
891        DatabaseBackend::Postgres,
892        "hello",
893        vec!["email", "password"]
894    )]
895    #[case::remove_index_with_custom_name_mysql(
896        DatabaseBackend::MySql,
897        "hello",
898        vec!["email", "password"]
899    )]
900    #[case::remove_index_with_custom_name_sqlite(
901        DatabaseBackend::Sqlite,
902        "hello",
903        vec!["email", "password"]
904    )]
905    fn test_remove_index_with_custom_name(
906        #[case] backend: DatabaseBackend,
907        #[case] index_name: &str,
908        #[case] columns: Vec<&str>,
909    ) {
910        // Test that removing custom index uses ix_table__name pattern
911        let action = MigrationAction::RemoveConstraint {
912            table: "user".into(),
913            constraint: TableConstraint::Index {
914                name: Some(index_name.into()),
915                columns: columns.iter().map(|s| s.to_string()).collect(),
916            },
917        };
918        let result = build_action_queries(&backend, &action, &[]).unwrap();
919        let sql = result[0].build(backend);
920
921        // Should use ix_table__name pattern
922        let expected_name = format!("ix_user__{}", index_name);
923        assert!(
924            sql.contains(&expected_name),
925            "Expected index name '{}' in SQL: {}",
926            expected_name,
927            sql
928        );
929
930        with_settings!({ snapshot_suffix => format!("remove_index_custom_{}_{:?}", index_name, backend) }, {
931            assert_snapshot!(sql);
932        });
933    }
934
935    #[rstest]
936    #[case::remove_unnamed_index_single_column_postgres(
937        DatabaseBackend::Postgres,
938        vec!["email"]
939    )]
940    #[case::remove_unnamed_index_single_column_mysql(
941        DatabaseBackend::MySql,
942        vec!["email"]
943    )]
944    #[case::remove_unnamed_index_single_column_sqlite(
945        DatabaseBackend::Sqlite,
946        vec!["email"]
947    )]
948    #[case::remove_unnamed_index_multiple_columns_postgres(
949        DatabaseBackend::Postgres,
950        vec!["email", "password"]
951    )]
952    #[case::remove_unnamed_index_multiple_columns_mysql(
953        DatabaseBackend::MySql,
954        vec!["email", "password"]
955    )]
956    #[case::remove_unnamed_index_multiple_columns_sqlite(
957        DatabaseBackend::Sqlite,
958        vec!["email", "password"]
959    )]
960    fn test_remove_unnamed_index(#[case] backend: DatabaseBackend, #[case] columns: Vec<&str>) {
961        // Test that removing unnamed indexes uses ix_table__col1_col2 pattern
962        let action = MigrationAction::RemoveConstraint {
963            table: "user".into(),
964            constraint: TableConstraint::Index {
965                name: None,
966                columns: columns.iter().map(|s| s.to_string()).collect(),
967            },
968        };
969        let result = build_action_queries(&backend, &action, &[]).unwrap();
970        let sql = result[0].build(backend);
971
972        // Should use ix_table__col1_col2... pattern
973        let expected_name = format!("ix_user__{}", columns.join("_"));
974        assert!(
975            sql.contains(&expected_name),
976            "Expected index name '{}' in SQL: {}",
977            expected_name,
978            sql
979        );
980
981        with_settings!({ snapshot_suffix => format!("remove_unnamed_index_{}_{:?}", columns.join("_"), backend) }, {
982            assert_snapshot!(sql);
983        });
984    }
985
986    // Comprehensive unique constraint naming tests
987    #[rstest]
988    #[case::add_unique_with_custom_name_postgres(
989        DatabaseBackend::Postgres,
990        "email_unique",
991        vec!["email"]
992    )]
993    #[case::add_unique_with_custom_name_mysql(
994        DatabaseBackend::MySql,
995        "email_unique",
996        vec!["email"]
997    )]
998    #[case::add_unique_with_custom_name_sqlite(
999        DatabaseBackend::Sqlite,
1000        "email_unique",
1001        vec!["email"]
1002    )]
1003    fn test_add_unique_with_custom_name(
1004        #[case] backend: DatabaseBackend,
1005        #[case] constraint_name: &str,
1006        #[case] columns: Vec<&str>,
1007    ) {
1008        // Test that custom unique constraint names follow uq_table__name pattern
1009        let action = MigrationAction::AddConstraint {
1010            table: "user".into(),
1011            constraint: TableConstraint::Unique {
1012                name: Some(constraint_name.into()),
1013                columns: columns.iter().map(|s| s.to_string()).collect(),
1014            },
1015        };
1016
1017        let current_schema = vec![TableDef {
1018            name: "user".into(),
1019            description: None,
1020            columns: vec![ColumnDef {
1021                name: "email".into(),
1022                r#type: ColumnType::Simple(SimpleColumnType::Text),
1023                nullable: true,
1024                default: None,
1025                comment: None,
1026                primary_key: None,
1027                unique: None,
1028                index: None,
1029                foreign_key: None,
1030            }],
1031            constraints: vec![],
1032        }];
1033
1034        let result = build_action_queries(&backend, &action, &current_schema).unwrap();
1035        let sql = result
1036            .iter()
1037            .map(|q| q.build(backend))
1038            .collect::<Vec<String>>()
1039            .join("\n");
1040
1041        // Should use uq_table__name pattern
1042        let expected_name = format!("uq_user__{}", constraint_name);
1043        assert!(
1044            sql.contains(&expected_name),
1045            "Expected unique constraint name '{}' in SQL: {}",
1046            expected_name,
1047            sql
1048        );
1049
1050        with_settings!({ snapshot_suffix => format!("add_unique_custom_{}_{:?}", constraint_name, backend) }, {
1051            assert_snapshot!(sql);
1052        });
1053    }
1054
1055    #[rstest]
1056    #[case::add_unnamed_unique_single_column_postgres(
1057        DatabaseBackend::Postgres,
1058        vec!["email"]
1059    )]
1060    #[case::add_unnamed_unique_single_column_mysql(
1061        DatabaseBackend::MySql,
1062        vec!["email"]
1063    )]
1064    #[case::add_unnamed_unique_single_column_sqlite(
1065        DatabaseBackend::Sqlite,
1066        vec!["email"]
1067    )]
1068    #[case::add_unnamed_unique_multiple_columns_postgres(
1069        DatabaseBackend::Postgres,
1070        vec!["email", "username"]
1071    )]
1072    #[case::add_unnamed_unique_multiple_columns_mysql(
1073        DatabaseBackend::MySql,
1074        vec!["email", "username"]
1075    )]
1076    #[case::add_unnamed_unique_multiple_columns_sqlite(
1077        DatabaseBackend::Sqlite,
1078        vec!["email", "username"]
1079    )]
1080    fn test_add_unnamed_unique(#[case] backend: DatabaseBackend, #[case] columns: Vec<&str>) {
1081        // Test that unnamed unique constraints follow uq_table__col1_col2 pattern
1082        let action = MigrationAction::AddConstraint {
1083            table: "user".into(),
1084            constraint: TableConstraint::Unique {
1085                name: None,
1086                columns: columns.iter().map(|s| s.to_string()).collect(),
1087            },
1088        };
1089
1090        let schema_columns: Vec<ColumnDef> = columns
1091            .iter()
1092            .map(|col| ColumnDef {
1093                name: col.to_string(),
1094                r#type: ColumnType::Simple(SimpleColumnType::Text),
1095                nullable: true,
1096                default: None,
1097                comment: None,
1098                primary_key: None,
1099                unique: None,
1100                index: None,
1101                foreign_key: None,
1102            })
1103            .collect();
1104
1105        let current_schema = vec![TableDef {
1106            name: "user".into(),
1107            description: None,
1108            columns: schema_columns,
1109            constraints: vec![],
1110        }];
1111
1112        let result = build_action_queries(&backend, &action, &current_schema).unwrap();
1113        let sql = result
1114            .iter()
1115            .map(|q| q.build(backend))
1116            .collect::<Vec<String>>()
1117            .join("\n");
1118
1119        // Should use uq_table__col1_col2... pattern
1120        let expected_name = format!("uq_user__{}", columns.join("_"));
1121        assert!(
1122            sql.contains(&expected_name),
1123            "Expected unique constraint name '{}' in SQL: {}",
1124            expected_name,
1125            sql
1126        );
1127
1128        with_settings!({ snapshot_suffix => format!("add_unnamed_unique_{}_{:?}", columns.join("_"), backend) }, {
1129            assert_snapshot!(sql);
1130        });
1131    }
1132
1133    #[rstest]
1134    #[case::remove_unique_with_custom_name_postgres(
1135        DatabaseBackend::Postgres,
1136        "email_unique",
1137        vec!["email"]
1138    )]
1139    #[case::remove_unique_with_custom_name_mysql(
1140        DatabaseBackend::MySql,
1141        "email_unique",
1142        vec!["email"]
1143    )]
1144    #[case::remove_unique_with_custom_name_sqlite(
1145        DatabaseBackend::Sqlite,
1146        "email_unique",
1147        vec!["email"]
1148    )]
1149    fn test_remove_unique_with_custom_name(
1150        #[case] backend: DatabaseBackend,
1151        #[case] constraint_name: &str,
1152        #[case] columns: Vec<&str>,
1153    ) {
1154        // Test that removing custom unique constraint uses uq_table__name pattern
1155        let constraint = TableConstraint::Unique {
1156            name: Some(constraint_name.into()),
1157            columns: columns.iter().map(|s| s.to_string()).collect(),
1158        };
1159
1160        let current_schema = vec![TableDef {
1161            name: "user".into(),
1162            description: None,
1163            columns: vec![ColumnDef {
1164                name: "email".into(),
1165                r#type: ColumnType::Simple(SimpleColumnType::Text),
1166                nullable: true,
1167                default: None,
1168                comment: None,
1169                primary_key: None,
1170                unique: None,
1171                index: None,
1172                foreign_key: None,
1173            }],
1174            constraints: vec![constraint.clone()],
1175        }];
1176
1177        let action = MigrationAction::RemoveConstraint {
1178            table: "user".into(),
1179            constraint,
1180        };
1181
1182        let result = build_action_queries(&backend, &action, &current_schema).unwrap();
1183        let sql = result
1184            .iter()
1185            .map(|q| q.build(backend))
1186            .collect::<Vec<String>>()
1187            .join("\n");
1188
1189        // Should use uq_table__name pattern (for Postgres/MySQL, not SQLite which rebuilds table)
1190        if backend != DatabaseBackend::Sqlite {
1191            let expected_name = format!("uq_user__{}", constraint_name);
1192            assert!(
1193                sql.contains(&expected_name),
1194                "Expected unique constraint name '{}' in SQL: {}",
1195                expected_name,
1196                sql
1197            );
1198        }
1199
1200        with_settings!({ snapshot_suffix => format!("remove_unique_custom_{}_{:?}", constraint_name, backend) }, {
1201            assert_snapshot!(sql);
1202        });
1203    }
1204
1205    #[rstest]
1206    #[case::remove_unnamed_unique_single_column_postgres(
1207        DatabaseBackend::Postgres,
1208        vec!["email"]
1209    )]
1210    #[case::remove_unnamed_unique_single_column_mysql(
1211        DatabaseBackend::MySql,
1212        vec!["email"]
1213    )]
1214    #[case::remove_unnamed_unique_single_column_sqlite(
1215        DatabaseBackend::Sqlite,
1216        vec!["email"]
1217    )]
1218    #[case::remove_unnamed_unique_multiple_columns_postgres(
1219        DatabaseBackend::Postgres,
1220        vec!["email", "username"]
1221    )]
1222    #[case::remove_unnamed_unique_multiple_columns_mysql(
1223        DatabaseBackend::MySql,
1224        vec!["email", "username"]
1225    )]
1226    #[case::remove_unnamed_unique_multiple_columns_sqlite(
1227        DatabaseBackend::Sqlite,
1228        vec!["email", "username"]
1229    )]
1230    fn test_remove_unnamed_unique(#[case] backend: DatabaseBackend, #[case] columns: Vec<&str>) {
1231        // Test that removing unnamed unique constraints uses uq_table__col1_col2 pattern
1232        let constraint = TableConstraint::Unique {
1233            name: None,
1234            columns: columns.iter().map(|s| s.to_string()).collect(),
1235        };
1236
1237        let schema_columns: Vec<ColumnDef> = columns
1238            .iter()
1239            .map(|col| ColumnDef {
1240                name: col.to_string(),
1241                r#type: ColumnType::Simple(SimpleColumnType::Text),
1242                nullable: true,
1243                default: None,
1244                comment: None,
1245                primary_key: None,
1246                unique: None,
1247                index: None,
1248                foreign_key: None,
1249            })
1250            .collect();
1251
1252        let current_schema = vec![TableDef {
1253            name: "user".into(),
1254            description: None,
1255            columns: schema_columns,
1256            constraints: vec![constraint.clone()],
1257        }];
1258
1259        let action = MigrationAction::RemoveConstraint {
1260            table: "user".into(),
1261            constraint,
1262        };
1263
1264        let result = build_action_queries(&backend, &action, &current_schema).unwrap();
1265        let sql = result
1266            .iter()
1267            .map(|q| q.build(backend))
1268            .collect::<Vec<String>>()
1269            .join("\n");
1270
1271        // Should use uq_table__col1_col2... pattern (for Postgres/MySQL, not SQLite which rebuilds table)
1272        if backend != DatabaseBackend::Sqlite {
1273            let expected_name = format!("uq_user__{}", columns.join("_"));
1274            assert!(
1275                sql.contains(&expected_name),
1276                "Expected unique constraint name '{}' in SQL: {}",
1277                expected_name,
1278                sql
1279            );
1280        }
1281
1282        with_settings!({ snapshot_suffix => format!("remove_unnamed_unique_{}_{:?}", columns.join("_"), backend) }, {
1283            assert_snapshot!(sql);
1284        });
1285    }
1286
1287    /// Test build_action_queries for ModifyColumnNullable
1288    #[rstest]
1289    #[case::postgres_modify_nullable(DatabaseBackend::Postgres)]
1290    #[case::mysql_modify_nullable(DatabaseBackend::MySql)]
1291    #[case::sqlite_modify_nullable(DatabaseBackend::Sqlite)]
1292    fn test_build_action_queries_modify_column_nullable(#[case] backend: DatabaseBackend) {
1293        let action = MigrationAction::ModifyColumnNullable {
1294            table: "users".into(),
1295            column: "email".into(),
1296            nullable: false,
1297            fill_with: Some("'unknown'".into()),
1298            delete_null_rows: None,
1299        };
1300        let current_schema = vec![TableDef {
1301            name: "users".into(),
1302            description: None,
1303            columns: vec![ColumnDef {
1304                name: "email".into(),
1305                r#type: ColumnType::Simple(SimpleColumnType::Text),
1306                nullable: true,
1307                default: None,
1308                comment: None,
1309                primary_key: None,
1310                unique: None,
1311                index: None,
1312                foreign_key: None,
1313            }],
1314            constraints: vec![],
1315        }];
1316        let result = build_action_queries(&backend, &action, &current_schema).unwrap();
1317        assert!(!result.is_empty());
1318        let sql = result
1319            .iter()
1320            .map(|q| q.build(backend))
1321            .collect::<Vec<String>>()
1322            .join("\n");
1323
1324        // Should contain UPDATE for fill_with and ALTER for nullable change
1325        assert!(sql.contains("UPDATE"));
1326        assert!(sql.contains("unknown"));
1327
1328        let suffix = format!(
1329            "{}_modify_nullable",
1330            match backend {
1331                DatabaseBackend::Postgres => "postgres",
1332                DatabaseBackend::MySql => "mysql",
1333                DatabaseBackend::Sqlite => "sqlite",
1334            }
1335        );
1336
1337        with_settings!({ snapshot_suffix => suffix }, {
1338            assert_snapshot!(sql);
1339        });
1340    }
1341
1342    /// Test build_action_queries for ModifyColumnDefault
1343    #[rstest]
1344    #[case::postgres_modify_default(DatabaseBackend::Postgres)]
1345    #[case::mysql_modify_default(DatabaseBackend::MySql)]
1346    #[case::sqlite_modify_default(DatabaseBackend::Sqlite)]
1347    fn test_build_action_queries_modify_column_default(#[case] backend: DatabaseBackend) {
1348        let action = MigrationAction::ModifyColumnDefault {
1349            table: "users".into(),
1350            column: "status".into(),
1351            new_default: Some("'active'".into()),
1352        };
1353        let current_schema = vec![TableDef {
1354            name: "users".into(),
1355            description: None,
1356            columns: vec![ColumnDef {
1357                name: "status".into(),
1358                r#type: ColumnType::Simple(SimpleColumnType::Text),
1359                nullable: true,
1360                default: None,
1361                comment: None,
1362                primary_key: None,
1363                unique: None,
1364                index: None,
1365                foreign_key: None,
1366            }],
1367            constraints: vec![],
1368        }];
1369        let result = build_action_queries(&backend, &action, &current_schema).unwrap();
1370        assert!(!result.is_empty());
1371        let sql = result
1372            .iter()
1373            .map(|q| q.build(backend))
1374            .collect::<Vec<String>>()
1375            .join("\n");
1376
1377        // Should contain DEFAULT and 'active'
1378        assert!(sql.contains("DEFAULT") || sql.contains("active"));
1379
1380        let suffix = format!(
1381            "{}_modify_default",
1382            match backend {
1383                DatabaseBackend::Postgres => "postgres",
1384                DatabaseBackend::MySql => "mysql",
1385                DatabaseBackend::Sqlite => "sqlite",
1386            }
1387        );
1388
1389        with_settings!({ snapshot_suffix => suffix }, {
1390            assert_snapshot!(sql);
1391        });
1392    }
1393
1394    /// Test build_action_queries for ModifyColumnComment
1395    #[rstest]
1396    #[case::postgres_modify_comment(DatabaseBackend::Postgres)]
1397    #[case::mysql_modify_comment(DatabaseBackend::MySql)]
1398    #[case::sqlite_modify_comment(DatabaseBackend::Sqlite)]
1399    fn test_build_action_queries_modify_column_comment(#[case] backend: DatabaseBackend) {
1400        let action = MigrationAction::ModifyColumnComment {
1401            table: "users".into(),
1402            column: "email".into(),
1403            new_comment: Some("User email address".into()),
1404        };
1405        let current_schema = vec![TableDef {
1406            name: "users".into(),
1407            description: None,
1408            columns: vec![ColumnDef {
1409                name: "email".into(),
1410                r#type: ColumnType::Simple(SimpleColumnType::Text),
1411                nullable: true,
1412                default: None,
1413                comment: None,
1414                primary_key: None,
1415                unique: None,
1416                index: None,
1417                foreign_key: None,
1418            }],
1419            constraints: vec![],
1420        }];
1421        let result = build_action_queries(&backend, &action, &current_schema).unwrap();
1422        let sql = result
1423            .iter()
1424            .map(|q| q.build(backend))
1425            .collect::<Vec<String>>()
1426            .join("\n");
1427
1428        // Postgres and MySQL should have comment, SQLite returns empty
1429        if backend != DatabaseBackend::Sqlite {
1430            assert!(sql.contains("COMMENT") || sql.contains("User email address"));
1431        }
1432
1433        let suffix = format!(
1434            "{}_modify_comment",
1435            match backend {
1436                DatabaseBackend::Postgres => "postgres",
1437                DatabaseBackend::MySql => "mysql",
1438                DatabaseBackend::Sqlite => "sqlite",
1439            }
1440        );
1441
1442        with_settings!({ snapshot_suffix => suffix }, {
1443            assert_snapshot!(sql);
1444        });
1445    }
1446
1447    #[rstest]
1448    #[case::create_table_func_default_postgres(DatabaseBackend::Postgres)]
1449    #[case::create_table_func_default_mysql(DatabaseBackend::MySql)]
1450    #[case::create_table_func_default_sqlite(DatabaseBackend::Sqlite)]
1451    fn test_create_table_with_function_default(#[case] backend: DatabaseBackend) {
1452        // SQLite requires DEFAULT (expr) for function-call defaults.
1453        // This test ensures parentheses are added for SQLite.
1454        let action = MigrationAction::CreateTable {
1455            table: "users".into(),
1456            columns: vec![
1457                ColumnDef {
1458                    name: "id".into(),
1459                    r#type: ColumnType::Simple(SimpleColumnType::Uuid),
1460                    nullable: false,
1461                    default: Some("gen_random_uuid()".into()),
1462                    comment: None,
1463                    primary_key: None,
1464                    unique: None,
1465                    index: None,
1466                    foreign_key: None,
1467                },
1468                ColumnDef {
1469                    name: "created_at".into(),
1470                    r#type: ColumnType::Simple(SimpleColumnType::Timestamptz),
1471                    nullable: false,
1472                    default: Some("now()".into()),
1473                    comment: None,
1474                    primary_key: None,
1475                    unique: None,
1476                    index: None,
1477                    foreign_key: None,
1478                },
1479            ],
1480            constraints: vec![],
1481        };
1482        let result = build_action_queries(&backend, &action, &[]).unwrap();
1483        let sql = result
1484            .iter()
1485            .map(|q| q.build(backend))
1486            .collect::<Vec<_>>()
1487            .join(";\n");
1488
1489        with_settings!({ snapshot_suffix => format!("create_table_func_default_{:?}", backend) }, {
1490            assert_snapshot!(sql);
1491        });
1492    }
1493
1494    #[rstest]
1495    #[case::delete_enum_column_postgres(DatabaseBackend::Postgres)]
1496    #[case::delete_enum_column_mysql(DatabaseBackend::MySql)]
1497    #[case::delete_enum_column_sqlite(DatabaseBackend::Sqlite)]
1498    fn test_delete_column_with_enum_type(#[case] backend: DatabaseBackend) {
1499        // Deleting a column with an enum type — SQLite uses temp table approach,
1500        // Postgres drops the enum type, MySQL uses simple DROP COLUMN.
1501        let action = MigrationAction::DeleteColumn {
1502            table: "orders".into(),
1503            column: "status".into(),
1504        };
1505        let schema = vec![TableDef {
1506            name: "orders".into(),
1507            description: None,
1508            columns: vec![
1509                col("id", ColumnType::Simple(SimpleColumnType::Integer)),
1510                ColumnDef {
1511                    name: "status".into(),
1512                    r#type: ColumnType::Complex(vespertide_core::ComplexColumnType::Enum {
1513                        name: "order_status".into(),
1514                        values: vespertide_core::EnumValues::String(vec![
1515                            "pending".into(),
1516                            "shipped".into(),
1517                        ]),
1518                    }),
1519                    nullable: false,
1520                    default: None,
1521                    comment: None,
1522                    primary_key: None,
1523                    unique: None,
1524                    index: None,
1525                    foreign_key: None,
1526                },
1527            ],
1528            constraints: vec![],
1529        }];
1530        let result = build_action_queries(&backend, &action, &schema).unwrap();
1531        let sql = result
1532            .iter()
1533            .map(|q| q.build(backend))
1534            .collect::<Vec<_>>()
1535            .join(";\n");
1536
1537        with_settings!({ snapshot_suffix => format!("delete_enum_column_{:?}", backend) }, {
1538            assert_snapshot!(sql);
1539        });
1540    }
1541
1542    #[rstest]
1543    #[case::replace_fk_constraint_postgres(DatabaseBackend::Postgres)]
1544    #[case::replace_fk_constraint_mysql(DatabaseBackend::MySql)]
1545    #[case::replace_fk_constraint_sqlite(DatabaseBackend::Sqlite)]
1546    fn test_replace_fk_constraint(#[case] backend: DatabaseBackend) {
1547        let schema = vec![TableDef {
1548            name: "posts".into(),
1549            description: None,
1550            columns: vec![
1551                col("id", ColumnType::Simple(SimpleColumnType::Integer)),
1552                col("user_id", ColumnType::Simple(SimpleColumnType::Integer)),
1553            ],
1554            constraints: vec![
1555                TableConstraint::PrimaryKey {
1556                    auto_increment: false,
1557                    columns: vec!["id".into()],
1558                },
1559                TableConstraint::ForeignKey {
1560                    name: Some("fk_user".into()),
1561                    columns: vec!["user_id".into()],
1562                    ref_table: "users".into(),
1563                    ref_columns: vec!["id".into()],
1564                    on_delete: None,
1565                    on_update: None,
1566                },
1567            ],
1568        }];
1569        let action = MigrationAction::ReplaceConstraint {
1570            table: "posts".into(),
1571            from: TableConstraint::ForeignKey {
1572                name: Some("fk_user".into()),
1573                columns: vec!["user_id".into()],
1574                ref_table: "users".into(),
1575                ref_columns: vec!["id".into()],
1576                on_delete: None,
1577                on_update: None,
1578            },
1579            to: TableConstraint::ForeignKey {
1580                name: Some("fk_user".into()),
1581                columns: vec!["user_id".into()],
1582                ref_table: "users".into(),
1583                ref_columns: vec!["id".into()],
1584                on_delete: Some(ReferenceAction::Cascade),
1585                on_update: None,
1586            },
1587        };
1588        let result = build_action_queries(&backend, &action, &schema).unwrap();
1589        let sql = result
1590            .iter()
1591            .map(|q| q.build(backend))
1592            .collect::<Vec<_>>()
1593            .join(";\n");
1594
1595        let suffix = format!(
1596            "replace_fk_constraint_{}",
1597            match backend {
1598                DatabaseBackend::Postgres => "postgres",
1599                DatabaseBackend::MySql => "mysql",
1600                DatabaseBackend::Sqlite => "sqlite",
1601            }
1602        );
1603
1604        with_settings!({ snapshot_suffix => suffix }, {
1605            assert_snapshot!(sql);
1606        });
1607    }
1608}
vespertide_query/sql/mod.rs

vespertide_query/sql/
mod.rs
