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

vespertide_query/sql/
mod.rs
