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

vespertide_query/sql/
mod.rs
