vespertide_query/sql/add_constraint/
mod.rs

1mod check;
2mod foreign_key;
3mod index;
4mod primary_key;
5#[cfg(test)]
6mod tests {
7    use super::*;
8    use crate::sql::types::DatabaseBackend;
9    use insta::{assert_snapshot, with_settings};
10    use rstest::rstest;
11    use vespertide_core::{
12        ColumnDef, ColumnType, ReferenceAction, SimpleColumnType, TableConstraint, TableDef,
13    };
14    #[rstest]
15    #[case::add_constraint_primary_key_postgres(
16        "add_constraint_primary_key_postgres",
17        DatabaseBackend::Postgres,
18        &["ALTER TABLE \"users\" ADD PRIMARY KEY (\"id\")"]
19    )]
20    #[case::add_constraint_primary_key_mysql(
21        "add_constraint_primary_key_mysql",
22        DatabaseBackend::MySql,
23        &["ALTER TABLE `users` ADD PRIMARY KEY (`id`)"]
24    )]
25    #[case::add_constraint_primary_key_sqlite(
26        "add_constraint_primary_key_sqlite",
27        DatabaseBackend::Sqlite,
28        &["CREATE TABLE \"users_temp\""]
29    )]
30    #[case::add_constraint_unique_named_postgres(
31        "add_constraint_unique_named_postgres",
32        DatabaseBackend::Postgres,
33        &["CREATE UNIQUE INDEX \"uq_users__uq_email\" ON \"users\" (\"email\")"]
34    )]
35    #[case::add_constraint_unique_named_mysql(
36        "add_constraint_unique_named_mysql",
37        DatabaseBackend::MySql,
38        &["CREATE UNIQUE INDEX `uq_users__uq_email` ON `users` (`email`)"]
39    )]
40    #[case::add_constraint_unique_named_sqlite(
41        "add_constraint_unique_named_sqlite",
42        DatabaseBackend::Sqlite,
43        &["CREATE UNIQUE INDEX \"uq_users__uq_email\" ON \"users\" (\"email\")"]
44    )]
45    #[case::add_constraint_foreign_key_postgres(
46        "add_constraint_foreign_key_postgres",
47        DatabaseBackend::Postgres,
48        &["FOREIGN KEY (\"user_id\")", "REFERENCES \"users\" (\"id\")", "ON DELETE CASCADE", "ON UPDATE RESTRICT"]
49    )]
50    #[case::add_constraint_foreign_key_mysql(
51        "add_constraint_foreign_key_mysql",
52        DatabaseBackend::MySql,
53        &["FOREIGN KEY (`user_id`)", "REFERENCES `users` (`id`)", "ON DELETE CASCADE", "ON UPDATE RESTRICT"]
54    )]
55    #[case::add_constraint_foreign_key_sqlite(
56        "add_constraint_foreign_key_sqlite",
57        DatabaseBackend::Sqlite,
58        &["CREATE TABLE \"users_temp\""]
59    )]
60    #[case::add_constraint_check_named_postgres(
61        "add_constraint_check_named_postgres",
62        DatabaseBackend::Postgres,
63        &["ADD CONSTRAINT \"chk_age\" CHECK (age > 0)"]
64    )]
65    #[case::add_constraint_check_named_mysql(
66        "add_constraint_check_named_mysql",
67        DatabaseBackend::MySql,
68        &["ADD CONSTRAINT `chk_age` CHECK (age > 0)"]
69    )]
70    #[case::add_constraint_check_named_sqlite(
71        "add_constraint_check_named_sqlite",
72        DatabaseBackend::Sqlite,
73        &["CREATE TABLE \"users_temp\""]
74    )]
75    fn test_add_constraint(
76        #[case] title: &str,
77        #[case] backend: DatabaseBackend,
78        #[case] expected: &[&str],
79    ) {
80        let constraint = if title.contains("primary_key") {
81            TableConstraint::PrimaryKey {
82                columns: vec!["id".into()],
83                auto_increment: false,
84                strategy: vespertide_core::PrimaryKeyAdditionStrategy::default(),
85            }
86        } else if title.contains("unique") {
87            TableConstraint::Unique {
88                name: Some("uq_email".into()),
89                columns: vec!["email".into()],
90                strategy: vespertide_core::UniqueConstraintStrategy::DeleteDuplicates {
91                    keep: vespertide_core::KeepPolicy::First,
92                },
93            }
94        } else if title.contains("foreign_key") {
95            TableConstraint::ForeignKey {
96                name: Some("fk_user".into()),
97                columns: vec!["user_id".into()],
98                ref_table: "users".into(),
99                ref_columns: vec!["id".into()],
100                on_delete: Some(ReferenceAction::Cascade),
101                on_update: Some(ReferenceAction::Restrict),
102                orphan_strategy: vespertide_core::ForeignKeyOrphanStrategy::default(),
103            }
104        } else {
105            TableConstraint::Check {
106                name: "chk_age".into(),
107                expr: "age > 0".into(),
108                strategy: vespertide_core::CheckViolationStrategy::default(),
109            }
110        };
111        let current_schema = vec![TableDef {
112            name: "users".into(),
113            description: None,
114            columns: if title.contains("foreign_key") {
115                vec![
116                    ColumnDef {
117                        name: "id".into(),
118                        r#type: ColumnType::Simple(SimpleColumnType::Integer),
119                        nullable: false,
120                        default: None,
121                        comment: None,
122                        primary_key: None,
123                        unique: None,
124                        index: None,
125                        foreign_key: None,
126                    },
127                    ColumnDef {
128                        name: "user_id".into(),
129                        r#type: ColumnType::Simple(SimpleColumnType::Integer),
130                        nullable: true,
131                        default: None,
132                        comment: None,
133                        primary_key: None,
134                        unique: None,
135                        index: None,
136                        foreign_key: None,
137                    },
138                ]
139            } else {
140                vec![
141                    ColumnDef {
142                        name: "id".into(),
143                        r#type: ColumnType::Simple(SimpleColumnType::Integer),
144                        nullable: false,
145                        default: None,
146                        comment: None,
147                        primary_key: None,
148                        unique: None,
149                        index: None,
150                        foreign_key: None,
151                    },
152                    ColumnDef {
153                        name: if title.contains("check") {
154                            "age".into()
155                        } else {
156                            "email".into()
157                        },
158                        r#type: ColumnType::Simple(SimpleColumnType::Text),
159                        nullable: true,
160                        default: None,
161                        comment: None,
162                        primary_key: None,
163                        unique: None,
164                        index: None,
165                        foreign_key: None,
166                    },
167                ]
168            },
169            constraints: vec![],
170        }];
171        let result =
172            build_add_constraint(backend, "users", &constraint, &current_schema, &[]).unwrap();
173        // F3 introduced multi-statement output for FK additions (pre-cleanup +
174        // ADD CONSTRAINT). Search across every emitted statement so the
175        // assertion still locates the constraint SQL regardless of position.
176        let sql = result
177            .iter()
178            .map(|q| q.build(backend))
179            .collect::<Vec<_>>()
180            .join("\n");
181        for exp in expected {
182            assert!(
183                sql.contains(exp),
184                "Expected SQL to contain '{exp}', got: {sql}"
185            );
186        }
187        with_settings!({ snapshot_path => "../snapshots", snapshot_suffix => format!("add_constraint_{}", title) }, {
188            assert_snapshot!(result.iter().map(|q| q.build(backend)).collect::<Vec<String>>().join("\n"));
189        });
190    }
191    #[test]
192    fn test_add_constraint_primary_key_sqlite_table_not_found() {
193        let constraint = TableConstraint::PrimaryKey {
194            columns: vec!["id".into()],
195            auto_increment: false,
196            strategy: vespertide_core::PrimaryKeyAdditionStrategy::default(),
197        };
198        let current_schema = vec![]; // Empty schema - table not found
199        let result = build_add_constraint(
200            DatabaseBackend::Sqlite,
201            "users",
202            &constraint,
203            &current_schema,
204            &[],
205        );
206        assert!(result.is_err());
207        let err_msg = result.unwrap_err().to_string();
208        assert!(err_msg.contains("Table 'users' not found in current schema"));
209    }
210
211    #[test]
212    fn add_check_constraint_escapes_adversarial_identifiers() {
213        let constraint = TableConstraint::Check {
214            name: "chk_age\"quote".into(),
215            expr: "age > 0".into(),
216            strategy: vespertide_core::CheckViolationStrategy::default(),
217        };
218        let current_schema = vec![TableDef {
219            name: "users\"archive".into(),
220            description: None,
221            columns: vec![ColumnDef {
222                name: "age".into(),
223                r#type: ColumnType::Simple(SimpleColumnType::Integer),
224                nullable: false,
225                default: None,
226                comment: None,
227                primary_key: None,
228                unique: None,
229                index: None,
230                foreign_key: None,
231            }],
232            constraints: vec![],
233        }];
234
235        let pg_results = build_add_constraint(
236            DatabaseBackend::Postgres,
237            "users\"archive",
238            &constraint,
239            &current_schema,
240            &[],
241        )
242        .unwrap();
243        // F4 introduced a pre-cleanup statement and F11 split the PG path
244        // into NOT VALID + VALIDATE. Validate identifier escaping appears in
245        // both statements by joining the full result and asserting on every
246        // distinct emitted form.
247        let pg_sql = pg_results
248            .iter()
249            .map(|q| q.build(DatabaseBackend::Postgres))
250            .collect::<Vec<_>>()
251            .join("\n");
252        assert!(pg_sql.contains("ALTER TABLE \"users\"\"archive\" ADD CONSTRAINT \"chk_age\"\"quote\" CHECK (age > 0) NOT VALID"), "PG NOT VALID statement missing or mis-escaped, got: {pg_sql}");
253        assert!(
254            pg_sql.contains(
255                "ALTER TABLE \"users\"\"archive\" VALIDATE CONSTRAINT \"chk_age\"\"quote\""
256            ),
257            "PG VALIDATE statement missing or mis-escaped, got: {pg_sql}"
258        );
259
260        let mysql_constraint = TableConstraint::Check {
261            name: "chk_age`quote".into(),
262            expr: "age > 0".into(),
263            strategy: vespertide_core::CheckViolationStrategy::default(),
264        };
265        let mysql_results = build_add_constraint(
266            DatabaseBackend::MySql,
267            "users`archive",
268            &mysql_constraint,
269            &current_schema,
270            &[],
271        )
272        .unwrap();
273        let mysql_sql = mysql_results
274            .last()
275            .expect("at least one query emitted")
276            .build(DatabaseBackend::MySql);
277        assert_eq!(
278            mysql_sql,
279            "ALTER TABLE `users``archive` ADD CONSTRAINT `chk_age``quote` CHECK (age > 0)"
280        );
281    }
282
283    #[test]
284    fn test_add_constraint_primary_key_sqlite_with_check_constraints() {
285        let constraint = TableConstraint::PrimaryKey {
286            columns: vec!["id".into()],
287            auto_increment: false,
288            strategy: vespertide_core::PrimaryKeyAdditionStrategy::default(),
289        };
290        let current_schema = vec![TableDef {
291            name: "users".into(),
292            description: None,
293            columns: vec![ColumnDef {
294                name: "id".into(),
295                r#type: ColumnType::Simple(SimpleColumnType::Integer),
296                nullable: false,
297                default: None,
298                comment: None,
299                primary_key: None,
300                unique: None,
301                index: None,
302                foreign_key: None,
303            }],
304            constraints: vec![TableConstraint::Check {
305                name: "chk_id".into(),
306                expr: "id > 0".into(),
307                strategy: vespertide_core::CheckViolationStrategy::default(),
308            }],
309        }];
310        let result = build_add_constraint(
311            DatabaseBackend::Sqlite,
312            "users",
313            &constraint,
314            &current_schema,
315            &[],
316        );
317        assert!(result.is_ok());
318        let queries = result.unwrap();
319        let sql = queries
320            .iter()
321            .map(|q| q.build(DatabaseBackend::Sqlite))
322            .collect::<Vec<String>>()
323            .join("\n");
324        assert!(sql.contains("CONSTRAINT \"chk_id\" CHECK"));
325    }
326    #[test]
327    fn test_add_constraint_primary_key_sqlite_with_indexes() {
328        let constraint = TableConstraint::PrimaryKey {
329            columns: vec!["id".into()],
330            auto_increment: false,
331            strategy: vespertide_core::PrimaryKeyAdditionStrategy::default(),
332        };
333        let current_schema = vec![TableDef {
334            name: "users".into(),
335            description: None,
336            columns: vec![ColumnDef {
337                name: "id".into(),
338                r#type: ColumnType::Simple(SimpleColumnType::Integer),
339                nullable: false,
340                default: None,
341                comment: None,
342                primary_key: None,
343                unique: None,
344                index: None,
345                foreign_key: None,
346            }],
347            constraints: vec![TableConstraint::Index {
348                name: Some("idx_id".into()),
349                columns: vec!["id".into()],
350            }],
351        }];
352        let result = build_add_constraint(
353            DatabaseBackend::Sqlite,
354            "users",
355            &constraint,
356            &current_schema,
357            &[],
358        );
359        assert!(result.is_ok());
360        let queries = result.unwrap();
361        let sql = queries
362            .iter()
363            .map(|q| q.build(DatabaseBackend::Sqlite))
364            .collect::<Vec<String>>()
365            .join("\n");
366        assert!(sql.contains("CREATE INDEX"));
367        assert!(sql.contains("idx_id"));
368    }
369    #[test]
370    fn test_add_constraint_primary_key_sqlite_with_unique_constraint() {
371        let constraint = TableConstraint::PrimaryKey {
372            columns: vec!["id".into()],
373            auto_increment: false,
374            strategy: vespertide_core::PrimaryKeyAdditionStrategy::default(),
375        };
376        let current_schema = vec![TableDef {
377            name: "users".into(),
378            description: None,
379            columns: vec![ColumnDef {
380                name: "id".into(),
381                r#type: ColumnType::Simple(SimpleColumnType::Integer),
382                nullable: false,
383                default: None,
384                comment: None,
385                primary_key: None,
386                unique: None,
387                index: None,
388                foreign_key: None,
389            }],
390            constraints: vec![TableConstraint::Unique {
391                name: Some("uq_email".into()),
392                columns: vec!["email".into()],
393                strategy: vespertide_core::UniqueConstraintStrategy::DeleteDuplicates {
394                    keep: vespertide_core::KeepPolicy::First,
395                },
396            }],
397        }];
398        let result = build_add_constraint(
399            DatabaseBackend::Sqlite,
400            "users",
401            &constraint,
402            &current_schema,
403            &[],
404        );
405        assert!(result.is_ok());
406        let queries = result.unwrap();
407        let sql = queries
408            .iter()
409            .map(|q| q.build(DatabaseBackend::Sqlite))
410            .collect::<Vec<String>>()
411            .join("\n");
412        assert!(sql.contains("CREATE TABLE"));
413    }
414    #[test]
415    fn test_add_constraint_check_sqlite_table_not_found() {
416        let constraint = TableConstraint::Check {
417            name: "chk_age".into(),
418            expr: "age > 0".into(),
419            strategy: vespertide_core::CheckViolationStrategy::default(),
420        };
421        let current_schema = vec![]; // Empty schema - table not found
422        let result = build_add_constraint(
423            DatabaseBackend::Sqlite,
424            "users",
425            &constraint,
426            &current_schema,
427            &[],
428        );
429        assert!(result.is_err());
430        let err_msg = result.unwrap_err().to_string();
431        assert!(err_msg.contains("Table 'users' not found in current schema"));
432    }
433    #[test]
434    fn test_add_constraint_check_sqlite_without_existing_check() {
435        let constraint = TableConstraint::Check {
436            name: "chk_age".into(),
437            expr: "age > 0".into(),
438            strategy: vespertide_core::CheckViolationStrategy::default(),
439        };
440        let current_schema = vec![TableDef {
441            name: "users".into(),
442            description: None,
443            columns: vec![ColumnDef {
444                name: "age".into(),
445                r#type: ColumnType::Simple(SimpleColumnType::Integer),
446                nullable: true,
447                default: None,
448                comment: None,
449                primary_key: None,
450                unique: None,
451                index: None,
452                foreign_key: None,
453            }],
454            constraints: vec![], // No existing CHECK constraints
455        }];
456        let result = build_add_constraint(
457            DatabaseBackend::Sqlite,
458            "users",
459            &constraint,
460            &current_schema,
461            &[],
462        );
463        assert!(result.is_ok());
464        let queries = result.unwrap();
465        let sql = queries
466            .iter()
467            .map(|q| q.build(DatabaseBackend::Sqlite))
468            .collect::<Vec<String>>()
469            .join("\n");
470        assert!(sql.contains("CREATE TABLE"));
471        assert!(sql.contains("CONSTRAINT \"chk_age\" CHECK"));
472    }
473    #[test]
474    fn test_add_constraint_primary_key_sqlite_without_existing_check() {
475        let constraint = TableConstraint::PrimaryKey {
476            columns: vec!["id".into()],
477            auto_increment: false,
478            strategy: vespertide_core::PrimaryKeyAdditionStrategy::default(),
479        };
480        let current_schema = vec![TableDef {
481            name: "users".into(),
482            description: None,
483            columns: vec![ColumnDef {
484                name: "id".into(),
485                r#type: ColumnType::Simple(SimpleColumnType::Integer),
486                nullable: true,
487                default: None,
488                comment: None,
489                primary_key: None,
490                unique: None,
491                index: None,
492                foreign_key: None,
493            }],
494            constraints: vec![], // No existing CHECK constraints
495        }];
496        let result = build_add_constraint(
497            DatabaseBackend::Sqlite,
498            "users",
499            &constraint,
500            &current_schema,
501            &[],
502        );
503        assert!(result.is_ok());
504        let queries = result.unwrap();
505        let sql = queries
506            .iter()
507            .map(|q| q.build(DatabaseBackend::Sqlite))
508            .collect::<Vec<String>>()
509            .join("\n");
510        assert!(sql.contains("CREATE TABLE"));
511        assert!(sql.contains("PRIMARY KEY"));
512    }
513
514    #[test]
515    fn test_add_constraint_check_sqlite_with_indexes() {
516        let constraint = TableConstraint::Check {
517            name: "chk_age".into(),
518            expr: "age > 0".into(),
519            strategy: vespertide_core::CheckViolationStrategy::default(),
520        };
521        let current_schema = vec![TableDef {
522            name: "users".into(),
523            description: None,
524            columns: vec![ColumnDef {
525                name: "age".into(),
526                r#type: ColumnType::Simple(SimpleColumnType::Integer),
527                nullable: true,
528                default: None,
529                comment: None,
530                primary_key: None,
531                unique: None,
532                index: None,
533                foreign_key: None,
534            }],
535            constraints: vec![TableConstraint::Index {
536                name: Some("idx_age".into()),
537                columns: vec!["age".into()],
538            }],
539        }];
540        let result = build_add_constraint(
541            DatabaseBackend::Sqlite,
542            "users",
543            &constraint,
544            &current_schema,
545            &[],
546        );
547        assert!(result.is_ok());
548        let queries = result.unwrap();
549        let sql = queries
550            .iter()
551            .map(|q| q.build(DatabaseBackend::Sqlite))
552            .collect::<Vec<String>>()
553            .join("\n");
554        assert!(sql.contains("CREATE INDEX"));
555        assert!(sql.contains("idx_age"));
556    }
557    #[test]
558    fn test_add_constraint_check_sqlite_with_unique_constraint() {
559        let constraint = TableConstraint::Check {
560            name: "chk_age".into(),
561            expr: "age > 0".into(),
562            strategy: vespertide_core::CheckViolationStrategy::default(),
563        };
564        let current_schema = vec![TableDef {
565            name: "users".into(),
566            description: None,
567            columns: vec![ColumnDef {
568                name: "age".into(),
569                r#type: ColumnType::Simple(SimpleColumnType::Integer),
570                nullable: true,
571                default: None,
572                comment: None,
573                primary_key: None,
574                unique: None,
575                index: None,
576                foreign_key: None,
577            }],
578            constraints: vec![TableConstraint::Unique {
579                name: Some("uq_age".into()),
580                columns: vec!["age".into()],
581                strategy: vespertide_core::UniqueConstraintStrategy::DeleteDuplicates {
582                    keep: vespertide_core::KeepPolicy::First,
583                },
584            }],
585        }];
586        let result = build_add_constraint(
587            DatabaseBackend::Sqlite,
588            "users",
589            &constraint,
590            &current_schema,
591            &[],
592        );
593        assert!(result.is_ok());
594        let queries = result.unwrap();
595        let sql = queries
596            .iter()
597            .map(|q| q.build(DatabaseBackend::Sqlite))
598            .collect::<Vec<String>>()
599            .join("\n");
600        assert!(sql.contains("CREATE TABLE"));
601    }
602    #[test]
603    fn test_add_constraint_composite_primary_key_postgres() {
604        let constraint = TableConstraint::PrimaryKey {
605            columns: vec!["user_id".into(), "role_id".into()],
606            auto_increment: false,
607            strategy: vespertide_core::PrimaryKeyAdditionStrategy::default(),
608        };
609        let current_schema = vec![TableDef {
610            name: "user_roles".into(),
611            description: None,
612            columns: vec![
613                ColumnDef {
614                    name: "user_id".into(),
615                    r#type: ColumnType::Simple(SimpleColumnType::Integer),
616                    nullable: false,
617                    default: None,
618                    comment: None,
619                    primary_key: None,
620                    unique: None,
621                    index: None,
622                    foreign_key: None,
623                },
624                ColumnDef {
625                    name: "role_id".into(),
626                    r#type: ColumnType::Simple(SimpleColumnType::Integer),
627                    nullable: false,
628                    default: None,
629                    comment: None,
630                    primary_key: None,
631                    unique: None,
632                    index: None,
633                    foreign_key: None,
634                },
635            ],
636            constraints: vec![],
637        }];
638        let result = build_add_constraint(
639            DatabaseBackend::Postgres,
640            "user_roles",
641            &constraint,
642            &current_schema,
643            &[],
644        )
645        .unwrap();
646        let sql = result[0].build(DatabaseBackend::Postgres);
647        assert!(sql.contains("ADD PRIMARY KEY"));
648        assert!(sql.contains("\"user_id\""));
649        assert!(sql.contains("\"role_id\""));
650    }
651    #[test]
652    fn test_add_constraint_composite_primary_key_mysql() {
653        let constraint = TableConstraint::PrimaryKey {
654            columns: vec!["user_id".into(), "role_id".into()],
655            auto_increment: false,
656            strategy: vespertide_core::PrimaryKeyAdditionStrategy::default(),
657        };
658        let current_schema = vec![TableDef {
659            name: "user_roles".into(),
660            description: None,
661            columns: vec![
662                ColumnDef {
663                    name: "user_id".into(),
664                    r#type: ColumnType::Simple(SimpleColumnType::Integer),
665                    nullable: false,
666                    default: None,
667                    comment: None,
668                    primary_key: None,
669                    unique: None,
670                    index: None,
671                    foreign_key: None,
672                },
673                ColumnDef {
674                    name: "role_id".into(),
675                    r#type: ColumnType::Simple(SimpleColumnType::Integer),
676                    nullable: false,
677                    default: None,
678                    comment: None,
679                    primary_key: None,
680                    unique: None,
681                    index: None,
682                    foreign_key: None,
683                },
684            ],
685            constraints: vec![],
686        }];
687        let result = build_add_constraint(
688            DatabaseBackend::MySql,
689            "user_roles",
690            &constraint,
691            &current_schema,
692            &[],
693        )
694        .unwrap();
695        let sql = result[0].build(DatabaseBackend::MySql);
696        assert!(sql.contains("ADD PRIMARY KEY"));
697        assert!(sql.contains("`user_id`"));
698        assert!(sql.contains("`role_id`"));
699    }
700    #[test]
701    fn test_constraints_overlap_primary_key_same_columns() {
702        let a = TableConstraint::PrimaryKey {
703            columns: vec!["id".into()],
704            auto_increment: false,
705            strategy: vespertide_core::PrimaryKeyAdditionStrategy::default(),
706        };
707        let b = TableConstraint::PrimaryKey {
708            columns: vec!["id".into()],
709            auto_increment: true,
710            strategy: vespertide_core::PrimaryKeyAdditionStrategy::default(),
711        };
712        assert!(constraints_overlap(&a, &b));
713    }
714    #[test]
715    fn test_constraints_overlap_primary_key_different_columns() {
716        let a = TableConstraint::PrimaryKey {
717            columns: vec!["id".into()],
718            auto_increment: false,
719            strategy: vespertide_core::PrimaryKeyAdditionStrategy::default(),
720        };
721        let b = TableConstraint::PrimaryKey {
722            columns: vec!["uid".into()],
723            auto_increment: false,
724            strategy: vespertide_core::PrimaryKeyAdditionStrategy::default(),
725        };
726        assert!(!constraints_overlap(&a, &b));
727    }
728    #[test]
729    fn test_constraints_overlap_check_same() {
730        let a = TableConstraint::Check {
731            name: "chk_age".into(),
732            expr: "age > 0".into(),
733            strategy: vespertide_core::CheckViolationStrategy::default(),
734        };
735        let b = TableConstraint::Check {
736            name: "chk_age".into(),
737            expr: "age > 0".into(),
738            strategy: vespertide_core::CheckViolationStrategy::default(),
739        };
740        assert!(constraints_overlap(&a, &b));
741    }
742    #[test]
743    fn test_constraints_overlap_check_different_name() {
744        let a = TableConstraint::Check {
745            name: "chk_age".into(),
746            expr: "age > 0".into(),
747            strategy: vespertide_core::CheckViolationStrategy::default(),
748        };
749        let b = TableConstraint::Check {
750            name: "chk_age2".into(),
751            expr: "age > 0".into(),
752            strategy: vespertide_core::CheckViolationStrategy::default(),
753        };
754        assert!(!constraints_overlap(&a, &b));
755    }
756    #[test]
757    fn test_constraints_overlap_check_different_expr() {
758        let a = TableConstraint::Check {
759            name: "chk_age".into(),
760            expr: "age > 0".into(),
761            strategy: vespertide_core::CheckViolationStrategy::default(),
762        };
763        let b = TableConstraint::Check {
764            name: "chk_age".into(),
765            expr: "age > 10".into(),
766            strategy: vespertide_core::CheckViolationStrategy::default(),
767        };
768        assert!(!constraints_overlap(&a, &b));
769    }
770    #[test]
771    fn test_constraints_overlap_different_variants() {
772        let a = TableConstraint::PrimaryKey {
773            columns: vec!["id".into()],
774            auto_increment: false,
775            strategy: vespertide_core::PrimaryKeyAdditionStrategy::default(),
776        };
777        let b = TableConstraint::Check {
778            name: "chk".into(),
779            expr: "id > 0".into(),
780            strategy: vespertide_core::CheckViolationStrategy::default(),
781        };
782        assert!(!constraints_overlap(&a, &b));
783    }
784    #[test]
785    fn test_constraints_overlap_fk_same_columns() {
786        let a = TableConstraint::ForeignKey {
787            name: None,
788            columns: vec!["user_id".into()],
789            ref_table: "users".into(),
790            ref_columns: vec!["id".into()],
791            on_delete: None,
792            on_update: None,
793            orphan_strategy: vespertide_core::ForeignKeyOrphanStrategy::default(),
794        };
795        let b = TableConstraint::ForeignKey {
796            name: Some("fk".into()),
797            columns: vec!["user_id".into()],
798            ref_table: "other".into(),
799            ref_columns: vec!["oid".into()],
800            on_delete: Some(ReferenceAction::Cascade),
801            on_update: None,
802            orphan_strategy: vespertide_core::ForeignKeyOrphanStrategy::default(),
803        };
804        assert!(constraints_overlap(&a, &b));
805    }
806    #[test]
807    fn test_merge_constraint_replaces_overlapping() {
808        let existing = vec![
809            TableConstraint::PrimaryKey {
810                columns: vec!["id".into()],
811                auto_increment: false,
812                strategy: vespertide_core::PrimaryKeyAdditionStrategy::default(),
813            },
814            TableConstraint::Index {
815                name: None,
816                columns: vec!["email".into()],
817            },
818        ];
819        let new_pk = TableConstraint::PrimaryKey {
820            columns: vec!["id".into()],
821            auto_increment: true,
822            strategy: vespertide_core::PrimaryKeyAdditionStrategy::default(),
823        };
824        let result = merge_constraint(&existing, &new_pk);
825        assert_eq!(result.len(), 2); // replaced, not added
826    }
827    /// Mutant target: `merge_constraint` `if !replaced` dedup guards.
828    /// Mixes a non-overlapping `Index` between two overlapping
829    /// `PrimaryKey`s so inner/outer guard-removal mutations produce
830    /// distinct positional/cardinal results from the original.
831    #[test]
832    fn merge_constraint_dedups_when_multiple_existing_overlap() {
833        let pk = TableConstraint::PrimaryKey {
834            columns: vec!["id".into()],
835            auto_increment: false,
836            strategy: vespertide_core::PrimaryKeyAdditionStrategy::default(),
837        };
838        let idx = TableConstraint::Index {
839            name: Some("idx_email".into()),
840            columns: vec!["email".into()],
841        };
842        let existing = vec![pk.clone(), idx.clone(), pk.clone()];
843        let new_pk = TableConstraint::PrimaryKey {
844            columns: vec!["id".into()],
845            auto_increment: true,
846            strategy: vespertide_core::PrimaryKeyAdditionStrategy::default(),
847        };
848        let result = merge_constraint(&existing, &new_pk);
849
850        // Pins OUTER `if !replaced` guard: without it, the trailing
851        // fallback push adds a duplicate copy of the new PK → len 3.
852        assert_eq!(
853            result.len(),
854            2,
855            "merge_constraint must dedupe (one new PK + one preserved \
856             index); got: {result:?}"
857        );
858
859        // Pins INNER `if !replaced` guard: without it, the new PK is
860        // never pushed inside the loop body; the trailing fallback
861        // pushes it at the END, so result[0] becomes the Index.
862        let TableConstraint::PrimaryKey { auto_increment, .. } = &result[0] else {
863            panic!(
864                "result[0] must be the replacement PrimaryKey (pushed at \
865                 the position of the FIRST overlap), not appended at the \
866                 end via the trailing fallback; got: {:?}",
867                result[0]
868            );
869        };
870        assert!(
871            *auto_increment,
872            "the kept PK must be the new one (auto_increment = true); \
873             got: {result:?}"
874        );
875
876        // Cardinality: exactly one PrimaryKey survives — direct
877        // expression of "the second overlap was deduped".
878        let pk_count = result
879            .iter()
880            .filter(|c| matches!(c, TableConstraint::PrimaryKey { .. }))
881            .count();
882        assert_eq!(
883            pk_count, 1,
884            "exactly one PrimaryKey must remain after merging away two \
885             overlapping existing PKs; got {pk_count} in: {result:?}"
886        );
887
888        // Non-overlapping index must survive the merge.
889        assert!(
890            result.iter().any(|c| matches!(
891                c,
892                TableConstraint::Index { name: Some(n), .. } if n == "idx_email"
893            )),
894            "non-overlapping idx_email must be preserved; got: {result:?}"
895        );
896    }
897
898    #[test]
899    fn test_merge_constraint_appends_non_overlapping() {
900        let existing = vec![TableConstraint::Index {
901            name: None,
902            columns: vec!["email".into()],
903        }];
904        let new_pk = TableConstraint::PrimaryKey {
905            columns: vec!["id".into()],
906            auto_increment: false,
907            strategy: vespertide_core::PrimaryKeyAdditionStrategy::default(),
908        };
909        let result = merge_constraint(&existing, &new_pk);
910        assert_eq!(result.len(), 2); // appended
911    }
912    #[test]
913    fn test_extract_check_clauses_with_mixed_constraints() {
914        let constraints = vec![
915            TableConstraint::Check {
916                name: "chk1".into(),
917                expr: "a > 0".into(),
918                strategy: vespertide_core::CheckViolationStrategy::default(),
919            },
920            TableConstraint::PrimaryKey {
921                columns: vec!["id".into()],
922                auto_increment: false,
923                strategy: vespertide_core::PrimaryKeyAdditionStrategy::default(),
924            },
925            TableConstraint::Check {
926                name: "chk2".into(),
927                expr: "b < 100".into(),
928                strategy: vespertide_core::CheckViolationStrategy::default(),
929            },
930            TableConstraint::Unique {
931                name: Some("uq".into()),
932                columns: vec!["email".into()],
933                strategy: vespertide_core::UniqueConstraintStrategy::DeleteDuplicates {
934                    keep: vespertide_core::KeepPolicy::First,
935                },
936            },
937        ];
938        let clauses = crate::sql::helpers::extract_check_clauses(&constraints);
939        assert_eq!(clauses.len(), 2);
940        assert!(clauses[0].contains("chk1"));
941        assert!(clauses[1].contains("chk2"));
942    }
943    #[test]
944    fn test_extract_check_clauses_with_no_check_constraints() {
945        let constraints = vec![
946            TableConstraint::PrimaryKey {
947                columns: vec!["id".into()],
948                auto_increment: false,
949                strategy: vespertide_core::PrimaryKeyAdditionStrategy::default(),
950            },
951            TableConstraint::Unique {
952                name: None,
953                columns: vec!["email".into()],
954                strategy: vespertide_core::UniqueConstraintStrategy::DeleteDuplicates {
955                    keep: vespertide_core::KeepPolicy::First,
956                },
957            },
958        ];
959        let clauses = crate::sql::helpers::extract_check_clauses(&constraints);
960        assert!(clauses.is_empty());
961    }
962}
963
964mod unique;
965
966use sea_query::{Alias, Query, Table};
967use vespertide_core::{TableConstraint, TableDef};
968
969use super::helpers::{build_sqlite_temp_table_create, recreate_indexes_after_rebuild};
970use super::rename_table::build_rename_table;
971use super::types::{BuiltQuery, DatabaseBackend};
972use crate::error::QueryError;
973
974pub fn build_add_constraint(
975    backend: DatabaseBackend,
976    table: &str,
977    constraint: &TableConstraint,
978    current_schema: &[TableDef],
979    pending_constraints: &[TableConstraint],
980) -> Result<Vec<BuiltQuery>, QueryError> {
981    if let TableConstraint::PrimaryKey {
982        columns, strategy, ..
983    } = constraint
984    {
985        return primary_key::build_primary_key(
986            backend,
987            table,
988            columns,
989            strategy,
990            constraint,
991            current_schema,
992            pending_constraints,
993        );
994    }
995
996    match constraint {
997        TableConstraint::Unique {
998            name,
999            columns,
1000            strategy,
1001        } => unique::build_unique(
1002            backend,
1003            table,
1004            name.as_deref(),
1005            columns,
1006            strategy,
1007            current_schema,
1008        ),
1009        TableConstraint::ForeignKey {
1010            name,
1011            columns,
1012            ref_table,
1013            ref_columns,
1014            on_delete,
1015            on_update,
1016            orphan_strategy,
1017        } => foreign_key::build_foreign_key(
1018            backend,
1019            table,
1020            name.as_deref(),
1021            columns,
1022            ref_table,
1023            ref_columns,
1024            on_delete.as_ref(),
1025            on_update.as_ref(),
1026            *orphan_strategy,
1027            constraint,
1028            current_schema,
1029            pending_constraints,
1030        ),
1031        TableConstraint::Index { name, columns } => {
1032            Ok(index::build_index(table, name.as_deref(), columns))
1033        }
1034        TableConstraint::Check {
1035            name,
1036            expr,
1037            strategy,
1038        } => check::build_check(
1039            backend,
1040            table,
1041            name,
1042            expr,
1043            strategy,
1044            constraint,
1045            current_schema,
1046            pending_constraints,
1047        ),
1048        _ => unreachable!("TableConstraint is #[non_exhaustive]; all variants are matched above"),
1049    }
1050}
1051
1052pub(super) fn merge_constraint(
1053    existing: &[TableConstraint],
1054    constraint: &TableConstraint,
1055) -> Vec<TableConstraint> {
1056    let mut out = Vec::with_capacity(existing.len() + 1);
1057    let mut replaced = false;
1058    for c in existing {
1059        if constraints_overlap(c, constraint) {
1060            if !replaced {
1061                out.push(constraint.clone());
1062                replaced = true;
1063            }
1064        } else {
1065            out.push(c.clone());
1066        }
1067    }
1068    if !replaced {
1069        out.push(constraint.clone());
1070    }
1071    out
1072}
1073
1074pub(super) fn constraints_overlap(a: &TableConstraint, b: &TableConstraint) -> bool {
1075    match (a, b) {
1076        (
1077            TableConstraint::ForeignKey {
1078                columns: a_cols, ..
1079            },
1080            TableConstraint::ForeignKey {
1081                columns: b_cols, ..
1082            },
1083        )
1084        | (
1085            TableConstraint::PrimaryKey {
1086                columns: a_cols, ..
1087            },
1088            TableConstraint::PrimaryKey {
1089                columns: b_cols, ..
1090            },
1091        ) => a_cols == b_cols,
1092        (
1093            TableConstraint::Check {
1094                name: a_name,
1095                expr: a_expr,
1096                ..
1097            },
1098            TableConstraint::Check {
1099                name: b_name,
1100                expr: b_expr,
1101                ..
1102            },
1103        ) => a_name == b_name && a_expr == b_expr,
1104        _ => false,
1105    }
1106}
1107
1108pub(super) fn rebuild_sqlite_table_with_added_constraint(
1109    backend: DatabaseBackend,
1110    table: &str,
1111    constraint: &TableConstraint,
1112    current_schema: &[TableDef],
1113    pending_constraints: &[TableConstraint],
1114) -> Result<Vec<BuiltQuery>, QueryError> {
1115    let table_def = current_schema.iter().find(|t| t.name == table).ok_or_else(|| QueryError::SchemaError(format!("Table '{table}' not found in current schema. SQLite requires current schema information to add constraints.")))?;
1116    let new_constraints = merge_constraint(&table_def.constraints, constraint);
1117    let temp_table = format!("{table}_temp");
1118    let create_query = build_sqlite_temp_table_create(
1119        backend,
1120        &temp_table,
1121        table,
1122        &table_def.columns,
1123        &new_constraints,
1124    );
1125    let column_aliases: Vec<Alias> = table_def
1126        .columns
1127        .iter()
1128        .map(|c| Alias::new(&c.name))
1129        .collect();
1130    let mut select_query = Query::select();
1131    for col_alias in &column_aliases {
1132        select_query.column(col_alias.clone());
1133    }
1134    select_query.from(Alias::new(table));
1135    let insert_stmt = Query::insert()
1136        .into_table(Alias::new(&temp_table))
1137        .columns(column_aliases)
1138        .select_from(select_query)
1139        .unwrap()
1140        .to_owned();
1141    let insert_query = BuiltQuery::Insert(Box::new(insert_stmt));
1142    let drop_table = Table::drop().table(Alias::new(table)).to_owned();
1143    let drop_query = BuiltQuery::DropTable(Box::new(drop_table));
1144    let rename_query = build_rename_table(&temp_table, table);
1145    let index_queries =
1146        recreate_indexes_after_rebuild(table, &table_def.constraints, pending_constraints);
1147    let mut queries = vec![create_query, insert_query, drop_query, rename_query];
1148    queries.extend(index_queries);
1149    Ok(queries)
1150}
vespertide_query/sql/add_constraint/mod.rs

vespertide_query/sql/add_constraint/
mod.rs
