vespertide_query/sql/add_constraint/
mod.rs

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

vespertide_query/sql/add_constraint/
mod.rs
