vespertide_query/sql/
add_constraint.rs

1use sea_query::{Alias, ForeignKey, Index, Query, Table};
2
3use vespertide_core::{TableConstraint, TableDef};
4
5use super::helpers::{
6    build_sqlite_temp_table_create, recreate_indexes_after_rebuild, to_sea_fk_action,
7};
8
9/// Build new_constraints by cloning `table_def.constraints` and adding the
10/// given `constraint`.  If an equivalent constraint already exists (e.g.
11/// promoted by AddColumn normalization), it is **replaced** so that the
12/// explicit AddConstraint version (which may carry ON DELETE / ON UPDATE)
13/// wins, without producing duplicates.
14fn merge_constraint(
15    existing: &[TableConstraint],
16    constraint: &TableConstraint,
17) -> Vec<TableConstraint> {
18    let mut out: Vec<TableConstraint> = Vec::with_capacity(existing.len() + 1);
19    let mut replaced = false;
20
21    for c in existing {
22        if constraints_overlap(c, constraint) {
23            // Replace the existing (possibly weaker) constraint with the new one.
24            if !replaced {
25                out.push(constraint.clone());
26                replaced = true;
27            }
28            // else: skip additional duplicates
29        } else {
30            out.push(c.clone());
31        }
32    }
33
34    if !replaced {
35        out.push(constraint.clone());
36    }
37    out
38}
39
40/// Two constraints "overlap" when they are the same variant and target the
41/// same columns, even if their details (name, on_delete, …) differ.
42fn constraints_overlap(a: &TableConstraint, b: &TableConstraint) -> bool {
43    match (a, b) {
44        (
45            TableConstraint::ForeignKey {
46                columns: a_cols, ..
47            },
48            TableConstraint::ForeignKey {
49                columns: b_cols, ..
50            },
51        ) => a_cols == b_cols,
52        (
53            TableConstraint::PrimaryKey {
54                columns: a_cols, ..
55            },
56            TableConstraint::PrimaryKey {
57                columns: b_cols, ..
58            },
59        ) => a_cols == b_cols,
60        (
61            TableConstraint::Check {
62                name: a_name,
63                expr: a_expr,
64            },
65            TableConstraint::Check {
66                name: b_name,
67                expr: b_expr,
68            },
69        ) => a_name == b_name && a_expr == b_expr,
70        _ => false,
71    }
72}
73use super::rename_table::build_rename_table;
74use super::types::{BuiltQuery, DatabaseBackend, RawSql};
75use crate::error::QueryError;
76
77pub fn build_add_constraint(
78    backend: &DatabaseBackend,
79    table: &str,
80    constraint: &TableConstraint,
81    current_schema: &[TableDef],
82    pending_constraints: &[TableConstraint],
83) -> Result<Vec<BuiltQuery>, QueryError> {
84    match constraint {
85        TableConstraint::PrimaryKey { columns, .. } => {
86            if *backend == DatabaseBackend::Sqlite {
87                // SQLite does not support ALTER TABLE ... ADD PRIMARY KEY
88                // Use temporary table approach
89                let table_def = current_schema.iter().find(|t| t.name == table).ok_or_else(|| QueryError::Other(format!("Table '{}' not found in current schema. SQLite requires current schema information to add constraints.", table)))?;
90
91                // Create new constraints with the added primary key constraint
92                let new_constraints = merge_constraint(&table_def.constraints, constraint);
93
94                let temp_table = format!("{}_temp", table);
95
96                // 1. Create temporary table with new constraints + CHECK constraints
97                let create_query = build_sqlite_temp_table_create(
98                    backend,
99                    &temp_table,
100                    table,
101                    &table_def.columns,
102                    &new_constraints,
103                );
104
105                // 2. Copy data
106                let column_aliases: Vec<Alias> = table_def
107                    .columns
108                    .iter()
109                    .map(|c| Alias::new(&c.name))
110                    .collect();
111                let mut select_query = Query::select();
112                for col_alias in &column_aliases {
113                    select_query = select_query.column(col_alias.clone()).to_owned();
114                }
115                select_query = select_query.from(Alias::new(table)).to_owned();
116
117                let insert_stmt = Query::insert()
118                    .into_table(Alias::new(&temp_table))
119                    .columns(column_aliases.clone())
120                    .select_from(select_query)
121                    .unwrap()
122                    .to_owned();
123                let insert_query = BuiltQuery::Insert(Box::new(insert_stmt));
124
125                // 3. Drop original table
126                let drop_table = Table::drop().table(Alias::new(table)).to_owned();
127                let drop_query = BuiltQuery::DropTable(Box::new(drop_table));
128
129                // 4. Rename temporary table
130                let rename_query = build_rename_table(&temp_table, table);
131
132                // 5. Recreate indexes (both regular and UNIQUE)
133                // Exclude pending constraints that will be created by future AddConstraint actions
134                let index_queries = recreate_indexes_after_rebuild(
135                    table,
136                    &table_def.constraints,
137                    pending_constraints,
138                );
139
140                let mut queries = vec![create_query, insert_query, drop_query, rename_query];
141                queries.extend(index_queries);
142                Ok(queries)
143            } else {
144                // sea_query lacks ALTER TABLE ADD PRIMARY KEY; emit backend SQL
145                let pg_cols = columns
146                    .iter()
147                    .map(|c| format!("\"{}\"", c))
148                    .collect::<Vec<_>>()
149                    .join(", ");
150                let mysql_cols = columns
151                    .iter()
152                    .map(|c| format!("`{}`", c))
153                    .collect::<Vec<_>>()
154                    .join(", ");
155                let pg_sql = format!("ALTER TABLE \"{}\" ADD PRIMARY KEY ({})", table, pg_cols);
156                let mysql_sql = format!("ALTER TABLE `{}` ADD PRIMARY KEY ({})", table, mysql_cols);
157                Ok(vec![BuiltQuery::Raw(RawSql::per_backend(
158                    pg_sql.clone(),
159                    mysql_sql,
160                    pg_sql,
161                ))])
162            }
163        }
164        TableConstraint::Unique { name, columns } => {
165            // Always generate a proper name: uq_{table}_{key} or uq_{table}_{columns}
166            let index_name =
167                super::helpers::build_unique_constraint_name(table, columns, name.as_deref());
168            let mut idx = Index::create()
169                .table(Alias::new(table))
170                .name(&index_name)
171                .unique()
172                .to_owned();
173            for col in columns {
174                idx = idx.col(Alias::new(col)).to_owned();
175            }
176            Ok(vec![BuiltQuery::CreateIndex(Box::new(idx))])
177        }
178        TableConstraint::ForeignKey {
179            name,
180            columns,
181            ref_table,
182            ref_columns,
183            on_delete,
184            on_update,
185        } => {
186            // SQLite does not support ALTER TABLE ... ADD CONSTRAINT FOREIGN KEY
187            if *backend == DatabaseBackend::Sqlite {
188                // Use temporary table approach for SQLite
189                let table_def = current_schema.iter().find(|t| t.name == table).ok_or_else(|| QueryError::Other(format!("Table '{}' not found in current schema. SQLite requires current schema information to add constraints.", table)))?;
190
191                // Create new constraints with the added foreign key constraint
192                let new_constraints = merge_constraint(&table_def.constraints, constraint);
193
194                let temp_table = format!("{}_temp", table);
195
196                // 1. Create temporary table with new constraints + CHECK constraints
197                let create_query = build_sqlite_temp_table_create(
198                    backend,
199                    &temp_table,
200                    table,
201                    &table_def.columns,
202                    &new_constraints,
203                );
204
205                // 2. Copy data (all columns)
206                let column_aliases: Vec<Alias> = table_def
207                    .columns
208                    .iter()
209                    .map(|c| Alias::new(&c.name))
210                    .collect();
211                let mut select_query = Query::select();
212                for col_alias in &column_aliases {
213                    select_query = select_query.column(col_alias.clone()).to_owned();
214                }
215                select_query = select_query.from(Alias::new(table)).to_owned();
216
217                let insert_stmt = Query::insert()
218                    .into_table(Alias::new(&temp_table))
219                    .columns(column_aliases.clone())
220                    .select_from(select_query)
221                    .unwrap()
222                    .to_owned();
223                let insert_query = BuiltQuery::Insert(Box::new(insert_stmt));
224
225                // 3. Drop original table
226                let drop_table = Table::drop().table(Alias::new(table)).to_owned();
227                let drop_query = BuiltQuery::DropTable(Box::new(drop_table));
228
229                // 4. Rename temporary table to original name
230                let rename_query = build_rename_table(&temp_table, table);
231
232                // 5. Recreate indexes (both regular and UNIQUE)
233                // Exclude pending constraints that will be created by future AddConstraint actions
234                let index_queries = recreate_indexes_after_rebuild(
235                    table,
236                    &table_def.constraints,
237                    pending_constraints,
238                );
239
240                let mut queries = vec![create_query, insert_query, drop_query, rename_query];
241                queries.extend(index_queries);
242                Ok(queries)
243            } else {
244                // Build foreign key using ForeignKey::create
245                let fk_name =
246                    vespertide_naming::build_foreign_key_name(table, columns, name.as_deref());
247                let mut fk = ForeignKey::create();
248                fk = fk.name(&fk_name).to_owned();
249                fk = fk.from_tbl(Alias::new(table)).to_owned();
250                for col in columns {
251                    fk = fk.from_col(Alias::new(col)).to_owned();
252                }
253                fk = fk.to_tbl(Alias::new(ref_table)).to_owned();
254                for col in ref_columns {
255                    fk = fk.to_col(Alias::new(col)).to_owned();
256                }
257                if let Some(action) = on_delete {
258                    fk = fk.on_delete(to_sea_fk_action(action)).to_owned();
259                }
260                if let Some(action) = on_update {
261                    fk = fk.on_update(to_sea_fk_action(action)).to_owned();
262                }
263                Ok(vec![BuiltQuery::CreateForeignKey(Box::new(fk))])
264            }
265        }
266        TableConstraint::Index { name, columns } => {
267            let index_name = vespertide_naming::build_index_name(table, columns, name.as_deref());
268            let mut idx = Index::create()
269                .table(Alias::new(table))
270                .name(&index_name)
271                .to_owned();
272            for col in columns {
273                idx = idx.col(Alias::new(col)).to_owned();
274            }
275            Ok(vec![BuiltQuery::CreateIndex(Box::new(idx))])
276        }
277        TableConstraint::Check { name, expr } => {
278            // SQLite does not support ALTER TABLE ... ADD CONSTRAINT CHECK
279            if *backend == DatabaseBackend::Sqlite {
280                // Use temporary table approach for SQLite
281                let table_def = current_schema.iter().find(|t| t.name == table).ok_or_else(|| QueryError::Other(format!("Table '{}' not found in current schema. SQLite requires current schema information to add constraints.", table)))?;
282
283                // Create new constraints with the added check constraint
284                let new_constraints = merge_constraint(&table_def.constraints, constraint);
285
286                let temp_table = format!("{}_temp", table);
287
288                // 1. Create temporary table with new constraints + CHECK constraints
289                let create_query = build_sqlite_temp_table_create(
290                    backend,
291                    &temp_table,
292                    table,
293                    &table_def.columns,
294                    &new_constraints,
295                );
296
297                // 2. Copy data (all columns)
298                let column_aliases: Vec<Alias> = table_def
299                    .columns
300                    .iter()
301                    .map(|c| Alias::new(&c.name))
302                    .collect();
303                let mut select_query = Query::select();
304                for col_alias in &column_aliases {
305                    select_query = select_query.column(col_alias.clone()).to_owned();
306                }
307                select_query = select_query.from(Alias::new(table)).to_owned();
308
309                let insert_stmt = Query::insert()
310                    .into_table(Alias::new(&temp_table))
311                    .columns(column_aliases.clone())
312                    .select_from(select_query)
313                    .unwrap()
314                    .to_owned();
315                let insert_query = BuiltQuery::Insert(Box::new(insert_stmt));
316
317                // 3. Drop original table
318                let drop_table = Table::drop().table(Alias::new(table)).to_owned();
319                let drop_query = BuiltQuery::DropTable(Box::new(drop_table));
320
321                // 4. Rename temporary table to original name
322                let rename_query = build_rename_table(&temp_table, table);
323
324                // 5. Recreate indexes (both regular and UNIQUE)
325                // Exclude pending constraints that will be created by future AddConstraint actions
326                let index_queries = recreate_indexes_after_rebuild(
327                    table,
328                    &table_def.constraints,
329                    pending_constraints,
330                );
331
332                let mut queries = vec![create_query, insert_query, drop_query, rename_query];
333                queries.extend(index_queries);
334                Ok(queries)
335            } else {
336                let pg_sql = format!(
337                    "ALTER TABLE \"{}\" ADD CONSTRAINT \"{}\" CHECK ({})",
338                    table, name, expr
339                );
340                let mysql_sql = format!(
341                    "ALTER TABLE `{}` ADD CONSTRAINT `{}` CHECK ({})",
342                    table, name, expr
343                );
344                Ok(vec![BuiltQuery::Raw(RawSql::per_backend(
345                    pg_sql.clone(),
346                    mysql_sql,
347                    pg_sql,
348                ))])
349            }
350        }
351    }
352}
353
354#[cfg(test)]
355mod tests {
356    use super::*;
357    use crate::sql::types::DatabaseBackend;
358    use insta::{assert_snapshot, with_settings};
359    use rstest::rstest;
360    use vespertide_core::{
361        ColumnDef, ColumnType, ReferenceAction, SimpleColumnType, TableConstraint, TableDef,
362    };
363
364    #[rstest]
365    #[case::add_constraint_primary_key_postgres(
366        "add_constraint_primary_key_postgres",
367        DatabaseBackend::Postgres,
368        &["ALTER TABLE \"users\" ADD PRIMARY KEY (\"id\")"]
369    )]
370    #[case::add_constraint_primary_key_mysql(
371        "add_constraint_primary_key_mysql",
372        DatabaseBackend::MySql,
373        &["ALTER TABLE `users` ADD PRIMARY KEY (`id`)"]
374    )]
375    #[case::add_constraint_primary_key_sqlite(
376        "add_constraint_primary_key_sqlite",
377        DatabaseBackend::Sqlite,
378        &["CREATE TABLE \"users_temp\""]
379    )]
380    #[case::add_constraint_unique_named_postgres(
381        "add_constraint_unique_named_postgres",
382        DatabaseBackend::Postgres,
383        &["CREATE UNIQUE INDEX \"uq_users__uq_email\" ON \"users\" (\"email\")"]
384    )]
385    #[case::add_constraint_unique_named_mysql(
386        "add_constraint_unique_named_mysql",
387        DatabaseBackend::MySql,
388        &["CREATE UNIQUE INDEX `uq_users__uq_email` ON `users` (`email`)"]
389    )]
390    #[case::add_constraint_unique_named_sqlite(
391        "add_constraint_unique_named_sqlite",
392        DatabaseBackend::Sqlite,
393        &["CREATE UNIQUE INDEX \"uq_users__uq_email\" ON \"users\" (\"email\")"]
394    )]
395    #[case::add_constraint_foreign_key_postgres(
396        "add_constraint_foreign_key_postgres",
397        DatabaseBackend::Postgres,
398        &["FOREIGN KEY (\"user_id\")", "REFERENCES \"users\" (\"id\")", "ON DELETE CASCADE", "ON UPDATE RESTRICT"]
399    )]
400    #[case::add_constraint_foreign_key_mysql(
401        "add_constraint_foreign_key_mysql",
402        DatabaseBackend::MySql,
403        &["FOREIGN KEY (`user_id`)", "REFERENCES `users` (`id`)", "ON DELETE CASCADE", "ON UPDATE RESTRICT"]
404    )]
405    #[case::add_constraint_foreign_key_sqlite(
406        "add_constraint_foreign_key_sqlite",
407        DatabaseBackend::Sqlite,
408        &["CREATE TABLE \"users_temp\""]
409    )]
410    #[case::add_constraint_check_named_postgres(
411        "add_constraint_check_named_postgres",
412        DatabaseBackend::Postgres,
413        &["ADD CONSTRAINT \"chk_age\" CHECK (age > 0)"]
414    )]
415    #[case::add_constraint_check_named_mysql(
416        "add_constraint_check_named_mysql",
417        DatabaseBackend::MySql,
418        &["ADD CONSTRAINT `chk_age` CHECK (age > 0)"]
419    )]
420    #[case::add_constraint_check_named_sqlite(
421        "add_constraint_check_named_sqlite",
422        DatabaseBackend::Sqlite,
423        &["CREATE TABLE \"users_temp\""]
424    )]
425    fn test_add_constraint(
426        #[case] title: &str,
427        #[case] backend: DatabaseBackend,
428        #[case] expected: &[&str],
429    ) {
430        let constraint = if title.contains("primary_key") {
431            TableConstraint::PrimaryKey {
432                columns: vec!["id".into()],
433                auto_increment: false,
434            }
435        } else if title.contains("unique") {
436            TableConstraint::Unique {
437                name: Some("uq_email".into()),
438                columns: vec!["email".into()],
439            }
440        } else if title.contains("foreign_key") {
441            TableConstraint::ForeignKey {
442                name: Some("fk_user".into()),
443                columns: vec!["user_id".into()],
444                ref_table: "users".into(),
445                ref_columns: vec!["id".into()],
446                on_delete: Some(ReferenceAction::Cascade),
447                on_update: Some(ReferenceAction::Restrict),
448            }
449        } else {
450            TableConstraint::Check {
451                name: "chk_age".into(),
452                expr: "age > 0".into(),
453            }
454        };
455
456        // For SQLite, we need to provide current schema
457        let current_schema = vec![TableDef {
458            name: "users".into(),
459            description: None,
460            columns: if title.contains("foreign_key") {
461                vec![
462                    ColumnDef {
463                        name: "id".into(),
464                        r#type: ColumnType::Simple(SimpleColumnType::Integer),
465                        nullable: false,
466                        default: None,
467                        comment: None,
468                        primary_key: None,
469                        unique: None,
470                        index: None,
471                        foreign_key: None,
472                    },
473                    ColumnDef {
474                        name: "user_id".into(),
475                        r#type: ColumnType::Simple(SimpleColumnType::Integer),
476                        nullable: true,
477                        default: None,
478                        comment: None,
479                        primary_key: None,
480                        unique: None,
481                        index: None,
482                        foreign_key: None,
483                    },
484                ]
485            } else {
486                vec![
487                    ColumnDef {
488                        name: "id".into(),
489                        r#type: ColumnType::Simple(SimpleColumnType::Integer),
490                        nullable: false,
491                        default: None,
492                        comment: None,
493                        primary_key: None,
494                        unique: None,
495                        index: None,
496                        foreign_key: None,
497                    },
498                    ColumnDef {
499                        name: if title.contains("check") {
500                            "age".into()
501                        } else {
502                            "email".into()
503                        },
504                        r#type: ColumnType::Simple(SimpleColumnType::Text),
505                        nullable: true,
506                        default: None,
507                        comment: None,
508                        primary_key: None,
509                        unique: None,
510                        index: None,
511                        foreign_key: None,
512                    },
513                ]
514            },
515            constraints: vec![],
516        }];
517
518        let result =
519            build_add_constraint(&backend, "users", &constraint, &current_schema, &[]).unwrap();
520        let sql = result[0].build(backend);
521        for exp in expected {
522            assert!(
523                sql.contains(exp),
524                "Expected SQL to contain '{}', got: {}",
525                exp,
526                sql
527            );
528        }
529
530        with_settings!({ snapshot_suffix => format!("add_constraint_{}", title) }, {
531            assert_snapshot!(result.iter().map(|q| q.build(backend)).collect::<Vec<String>>().join("\n"));
532        });
533    }
534
535    #[test]
536    fn test_add_constraint_primary_key_sqlite_table_not_found() {
537        let constraint = TableConstraint::PrimaryKey {
538            columns: vec!["id".into()],
539            auto_increment: false,
540        };
541        let current_schema = vec![]; // Empty schema - table not found
542        let result = build_add_constraint(
543            &DatabaseBackend::Sqlite,
544            "users",
545            &constraint,
546            &current_schema,
547            &[],
548        );
549        assert!(result.is_err());
550        let err_msg = result.unwrap_err().to_string();
551        assert!(err_msg.contains("Table 'users' not found in current schema"));
552    }
553
554    #[test]
555    fn test_add_constraint_primary_key_sqlite_with_check_constraints() {
556        let constraint = TableConstraint::PrimaryKey {
557            columns: vec!["id".into()],
558            auto_increment: false,
559        };
560        let current_schema = vec![TableDef {
561            name: "users".into(),
562            description: None,
563            columns: vec![ColumnDef {
564                name: "id".into(),
565                r#type: ColumnType::Simple(SimpleColumnType::Integer),
566                nullable: false,
567                default: None,
568                comment: None,
569                primary_key: None,
570                unique: None,
571                index: None,
572                foreign_key: None,
573            }],
574            constraints: vec![TableConstraint::Check {
575                name: "chk_id".into(),
576                expr: "id > 0".into(),
577            }],
578        }];
579        let result = build_add_constraint(
580            &DatabaseBackend::Sqlite,
581            "users",
582            &constraint,
583            &current_schema,
584            &[],
585        );
586        assert!(result.is_ok());
587        let queries = result.unwrap();
588        let sql = queries
589            .iter()
590            .map(|q| q.build(DatabaseBackend::Sqlite))
591            .collect::<Vec<String>>()
592            .join("\n");
593        // Should include CHECK constraint in CREATE TABLE
594        assert!(sql.contains("CONSTRAINT \"chk_id\" CHECK"));
595    }
596
597    #[test]
598    fn test_add_constraint_primary_key_sqlite_with_indexes() {
599        let constraint = TableConstraint::PrimaryKey {
600            columns: vec!["id".into()],
601            auto_increment: false,
602        };
603        let current_schema = vec![TableDef {
604            name: "users".into(),
605            description: None,
606            columns: vec![ColumnDef {
607                name: "id".into(),
608                r#type: ColumnType::Simple(SimpleColumnType::Integer),
609                nullable: false,
610                default: None,
611                comment: None,
612                primary_key: None,
613                unique: None,
614                index: None,
615                foreign_key: None,
616            }],
617            constraints: vec![TableConstraint::Index {
618                name: Some("idx_id".into()),
619                columns: vec!["id".into()],
620            }],
621        }];
622        let result = build_add_constraint(
623            &DatabaseBackend::Sqlite,
624            "users",
625            &constraint,
626            &current_schema,
627            &[],
628        );
629        assert!(result.is_ok());
630        let queries = result.unwrap();
631        let sql = queries
632            .iter()
633            .map(|q| q.build(DatabaseBackend::Sqlite))
634            .collect::<Vec<String>>()
635            .join("\n");
636        // Should recreate index
637        assert!(sql.contains("CREATE INDEX"));
638        assert!(sql.contains("idx_id"));
639    }
640
641    #[test]
642    fn test_add_constraint_primary_key_sqlite_with_unique_constraint() {
643        // Note: Unique indexes are now TableConstraint::Unique, not Index
644        // Index constraints don't have a unique flag - use Unique constraint instead
645        let constraint = TableConstraint::PrimaryKey {
646            columns: vec!["id".into()],
647            auto_increment: false,
648        };
649        let current_schema = vec![TableDef {
650            name: "users".into(),
651            description: None,
652            columns: vec![ColumnDef {
653                name: "id".into(),
654                r#type: ColumnType::Simple(SimpleColumnType::Integer),
655                nullable: false,
656                default: None,
657                comment: None,
658                primary_key: None,
659                unique: None,
660                index: None,
661                foreign_key: None,
662            }],
663            constraints: vec![TableConstraint::Unique {
664                name: Some("uq_email".into()),
665                columns: vec!["email".into()],
666            }],
667        }];
668        let result = build_add_constraint(
669            &DatabaseBackend::Sqlite,
670            "users",
671            &constraint,
672            &current_schema,
673            &[],
674        );
675        assert!(result.is_ok());
676        let queries = result.unwrap();
677        let sql = queries
678            .iter()
679            .map(|q| q.build(DatabaseBackend::Sqlite))
680            .collect::<Vec<String>>()
681            .join("\n");
682        // Unique constraint should be in CREATE TABLE statement (for SQLite temp table approach)
683        assert!(sql.contains("CREATE TABLE"));
684    }
685
686    #[test]
687    fn test_add_constraint_foreign_key_sqlite_table_not_found() {
688        let constraint = TableConstraint::ForeignKey {
689            name: Some("fk_user".into()),
690            columns: vec!["user_id".into()],
691            ref_table: "users".into(),
692            ref_columns: vec!["id".into()],
693            on_delete: None,
694            on_update: None,
695        };
696        let current_schema = vec![]; // Empty schema - table not found
697        let result = build_add_constraint(
698            &DatabaseBackend::Sqlite,
699            "posts",
700            &constraint,
701            &current_schema,
702            &[],
703        );
704        assert!(result.is_err());
705        let err_msg = result.unwrap_err().to_string();
706        assert!(err_msg.contains("Table 'posts' not found in current schema"));
707    }
708
709    #[test]
710    fn test_add_constraint_foreign_key_sqlite_with_check_constraints() {
711        let constraint = TableConstraint::ForeignKey {
712            name: Some("fk_user".into()),
713            columns: vec!["user_id".into()],
714            ref_table: "users".into(),
715            ref_columns: vec!["id".into()],
716            on_delete: None,
717            on_update: None,
718        };
719        let current_schema = vec![TableDef {
720            name: "posts".into(),
721            description: None,
722            columns: vec![ColumnDef {
723                name: "user_id".into(),
724                r#type: ColumnType::Simple(SimpleColumnType::Integer),
725                nullable: true,
726                default: None,
727                comment: None,
728                primary_key: None,
729                unique: None,
730                index: None,
731                foreign_key: None,
732            }],
733            constraints: vec![TableConstraint::Check {
734                name: "chk_user_id".into(),
735                expr: "user_id > 0".into(),
736            }],
737        }];
738        let result = build_add_constraint(
739            &DatabaseBackend::Sqlite,
740            "posts",
741            &constraint,
742            &current_schema,
743            &[],
744        );
745        assert!(result.is_ok());
746        let queries = result.unwrap();
747        let sql = queries
748            .iter()
749            .map(|q| q.build(DatabaseBackend::Sqlite))
750            .collect::<Vec<String>>()
751            .join("\n");
752        // Should include CHECK constraint in CREATE TABLE
753        assert!(sql.contains("CONSTRAINT \"chk_user_id\" CHECK"));
754    }
755
756    #[test]
757    fn test_add_constraint_foreign_key_sqlite_with_indexes() {
758        let constraint = TableConstraint::ForeignKey {
759            name: Some("fk_user".into()),
760            columns: vec!["user_id".into()],
761            ref_table: "users".into(),
762            ref_columns: vec!["id".into()],
763            on_delete: None,
764            on_update: None,
765        };
766        let current_schema = vec![TableDef {
767            name: "posts".into(),
768            description: None,
769            columns: vec![ColumnDef {
770                name: "user_id".into(),
771                r#type: ColumnType::Simple(SimpleColumnType::Integer),
772                nullable: true,
773                default: None,
774                comment: None,
775                primary_key: None,
776                unique: None,
777                index: None,
778                foreign_key: None,
779            }],
780            constraints: vec![TableConstraint::Index {
781                name: Some("idx_user_id".into()),
782                columns: vec!["user_id".into()],
783            }],
784        }];
785        let result = build_add_constraint(
786            &DatabaseBackend::Sqlite,
787            "posts",
788            &constraint,
789            &current_schema,
790            &[],
791        );
792        assert!(result.is_ok());
793        let queries = result.unwrap();
794        let sql = queries
795            .iter()
796            .map(|q| q.build(DatabaseBackend::Sqlite))
797            .collect::<Vec<String>>()
798            .join("\n");
799        // Should recreate index
800        assert!(sql.contains("CREATE INDEX"));
801        assert!(sql.contains("idx_user_id"));
802    }
803
804    #[test]
805    fn test_add_constraint_foreign_key_sqlite_with_unique_constraint() {
806        // Note: Unique indexes are now TableConstraint::Unique
807        let constraint = TableConstraint::ForeignKey {
808            name: Some("fk_user".into()),
809            columns: vec!["user_id".into()],
810            ref_table: "users".into(),
811            ref_columns: vec!["id".into()],
812            on_delete: None,
813            on_update: None,
814        };
815        let current_schema = vec![TableDef {
816            name: "posts".into(),
817            description: None,
818            columns: vec![ColumnDef {
819                name: "user_id".into(),
820                r#type: ColumnType::Simple(SimpleColumnType::Integer),
821                nullable: true,
822                default: None,
823                comment: None,
824                primary_key: None,
825                unique: None,
826                index: None,
827                foreign_key: None,
828            }],
829            constraints: vec![TableConstraint::Unique {
830                name: Some("uq_user_id".into()),
831                columns: vec!["user_id".into()],
832            }],
833        }];
834        let result = build_add_constraint(
835            &DatabaseBackend::Sqlite,
836            "posts",
837            &constraint,
838            &current_schema,
839            &[],
840        );
841        assert!(result.is_ok());
842        let queries = result.unwrap();
843        let sql = queries
844            .iter()
845            .map(|q| q.build(DatabaseBackend::Sqlite))
846            .collect::<Vec<String>>()
847            .join("\n");
848        // Unique constraint should be in CREATE TABLE statement
849        assert!(sql.contains("CREATE TABLE"));
850    }
851
852    #[test]
853    fn test_add_constraint_check_sqlite_table_not_found() {
854        let constraint = TableConstraint::Check {
855            name: "chk_age".into(),
856            expr: "age > 0".into(),
857        };
858        let current_schema = vec![]; // Empty schema - table not found
859        let result = build_add_constraint(
860            &DatabaseBackend::Sqlite,
861            "users",
862            &constraint,
863            &current_schema,
864            &[],
865        );
866        assert!(result.is_err());
867        let err_msg = result.unwrap_err().to_string();
868        assert!(err_msg.contains("Table 'users' not found in current schema"));
869    }
870
871    #[test]
872    fn test_add_constraint_check_sqlite_without_existing_check() {
873        // Test when there are no existing CHECK constraints (line 376)
874        let constraint = TableConstraint::Check {
875            name: "chk_age".into(),
876            expr: "age > 0".into(),
877        };
878        let current_schema = vec![TableDef {
879            name: "users".into(),
880            description: None,
881            columns: vec![ColumnDef {
882                name: "age".into(),
883                r#type: ColumnType::Simple(SimpleColumnType::Integer),
884                nullable: true,
885                default: None,
886                comment: None,
887                primary_key: None,
888                unique: None,
889                index: None,
890                foreign_key: None,
891            }],
892            constraints: vec![], // No existing CHECK constraints
893        }];
894        let result = build_add_constraint(
895            &DatabaseBackend::Sqlite,
896            "users",
897            &constraint,
898            &current_schema,
899            &[],
900        );
901        assert!(result.is_ok());
902        let queries = result.unwrap();
903        let sql = queries
904            .iter()
905            .map(|q| q.build(DatabaseBackend::Sqlite))
906            .collect::<Vec<String>>()
907            .join("\n");
908        // Should create table with CHECK constraint
909        assert!(sql.contains("CREATE TABLE"));
910        assert!(sql.contains("CONSTRAINT \"chk_age\" CHECK"));
911    }
912
913    #[test]
914    fn test_add_constraint_primary_key_sqlite_without_existing_check() {
915        // Test PrimaryKey addition when there are no existing CHECK constraints (line 84)
916        // This should hit the else branch: BuiltQuery::CreateTable(Box::new(create_temp_table))
917        let constraint = TableConstraint::PrimaryKey {
918            columns: vec!["id".into()],
919            auto_increment: false,
920        };
921        let current_schema = vec![TableDef {
922            name: "users".into(),
923            description: None,
924            columns: vec![ColumnDef {
925                name: "id".into(),
926                r#type: ColumnType::Simple(SimpleColumnType::Integer),
927                nullable: true,
928                default: None,
929                comment: None,
930                primary_key: None,
931                unique: None,
932                index: None,
933                foreign_key: None,
934            }],
935            constraints: vec![], // No existing CHECK constraints
936        }];
937        let result = build_add_constraint(
938            &DatabaseBackend::Sqlite,
939            "users",
940            &constraint,
941            &current_schema,
942            &[],
943        );
944        assert!(result.is_ok());
945        let queries = result.unwrap();
946        let sql = queries
947            .iter()
948            .map(|q| q.build(DatabaseBackend::Sqlite))
949            .collect::<Vec<String>>()
950            .join("\n");
951        // Should create table without CHECK constraints (using BuiltQuery::CreateTable)
952        assert!(sql.contains("CREATE TABLE"));
953        assert!(sql.contains("PRIMARY KEY"));
954    }
955
956    #[test]
957    fn test_add_constraint_foreign_key_sqlite_without_existing_check() {
958        // Test ForeignKey addition when there are no existing CHECK constraints (line 238)
959        // This should hit the else branch: BuiltQuery::CreateTable(Box::new(create_temp_table))
960        let constraint = TableConstraint::ForeignKey {
961            name: Some("fk_user".into()),
962            columns: vec!["user_id".into()],
963            ref_table: "users".into(),
964            ref_columns: vec!["id".into()],
965            on_delete: None,
966            on_update: None,
967        };
968        let current_schema = vec![TableDef {
969            name: "posts".into(),
970            description: None,
971            columns: vec![ColumnDef {
972                name: "user_id".into(),
973                r#type: ColumnType::Simple(SimpleColumnType::Integer),
974                nullable: true,
975                default: None,
976                comment: None,
977                primary_key: None,
978                unique: None,
979                index: None,
980                foreign_key: None,
981            }],
982            constraints: vec![], // No existing CHECK constraints
983        }];
984        let result = build_add_constraint(
985            &DatabaseBackend::Sqlite,
986            "posts",
987            &constraint,
988            &current_schema,
989            &[],
990        );
991        assert!(result.is_ok());
992        let queries = result.unwrap();
993        let sql = queries
994            .iter()
995            .map(|q| q.build(DatabaseBackend::Sqlite))
996            .collect::<Vec<String>>()
997            .join("\n");
998        // Should create table without CHECK constraints (using BuiltQuery::CreateTable)
999        assert!(sql.contains("CREATE TABLE"));
1000        assert!(sql.contains("FOREIGN KEY"));
1001    }
1002
1003    #[test]
1004    fn test_add_constraint_check_sqlite_with_indexes() {
1005        let constraint = TableConstraint::Check {
1006            name: "chk_age".into(),
1007            expr: "age > 0".into(),
1008        };
1009        let current_schema = vec![TableDef {
1010            name: "users".into(),
1011            description: None,
1012            columns: vec![ColumnDef {
1013                name: "age".into(),
1014                r#type: ColumnType::Simple(SimpleColumnType::Integer),
1015                nullable: true,
1016                default: None,
1017                comment: None,
1018                primary_key: None,
1019                unique: None,
1020                index: None,
1021                foreign_key: None,
1022            }],
1023            constraints: vec![TableConstraint::Index {
1024                name: Some("idx_age".into()),
1025                columns: vec!["age".into()],
1026            }],
1027        }];
1028        let result = build_add_constraint(
1029            &DatabaseBackend::Sqlite,
1030            "users",
1031            &constraint,
1032            &current_schema,
1033            &[],
1034        );
1035        assert!(result.is_ok());
1036        let queries = result.unwrap();
1037        let sql = queries
1038            .iter()
1039            .map(|q| q.build(DatabaseBackend::Sqlite))
1040            .collect::<Vec<String>>()
1041            .join("\n");
1042        // Should recreate index
1043        assert!(sql.contains("CREATE INDEX"));
1044        assert!(sql.contains("idx_age"));
1045    }
1046
1047    #[test]
1048    fn test_add_constraint_check_sqlite_with_unique_constraint() {
1049        // Note: Unique indexes are now TableConstraint::Unique
1050        let constraint = TableConstraint::Check {
1051            name: "chk_age".into(),
1052            expr: "age > 0".into(),
1053        };
1054        let current_schema = vec![TableDef {
1055            name: "users".into(),
1056            description: None,
1057            columns: vec![ColumnDef {
1058                name: "age".into(),
1059                r#type: ColumnType::Simple(SimpleColumnType::Integer),
1060                nullable: true,
1061                default: None,
1062                comment: None,
1063                primary_key: None,
1064                unique: None,
1065                index: None,
1066                foreign_key: None,
1067            }],
1068            constraints: vec![TableConstraint::Unique {
1069                name: Some("uq_age".into()),
1070                columns: vec!["age".into()],
1071            }],
1072        }];
1073        let result = build_add_constraint(
1074            &DatabaseBackend::Sqlite,
1075            "users",
1076            &constraint,
1077            &current_schema,
1078            &[],
1079        );
1080        assert!(result.is_ok());
1081        let queries = result.unwrap();
1082        let sql = queries
1083            .iter()
1084            .map(|q| q.build(DatabaseBackend::Sqlite))
1085            .collect::<Vec<String>>()
1086            .join("\n");
1087        // Unique constraint should be in CREATE TABLE statement
1088        assert!(sql.contains("CREATE TABLE"));
1089    }
1090
1091    #[test]
1092    fn test_add_constraint_composite_primary_key_postgres() {
1093        let constraint = TableConstraint::PrimaryKey {
1094            columns: vec!["user_id".into(), "role_id".into()],
1095            auto_increment: false,
1096        };
1097        let current_schema = vec![TableDef {
1098            name: "user_roles".into(),
1099            description: None,
1100            columns: vec![
1101                ColumnDef {
1102                    name: "user_id".into(),
1103                    r#type: ColumnType::Simple(SimpleColumnType::Integer),
1104                    nullable: false,
1105                    default: None,
1106                    comment: None,
1107                    primary_key: None,
1108                    unique: None,
1109                    index: None,
1110                    foreign_key: None,
1111                },
1112                ColumnDef {
1113                    name: "role_id".into(),
1114                    r#type: ColumnType::Simple(SimpleColumnType::Integer),
1115                    nullable: false,
1116                    default: None,
1117                    comment: None,
1118                    primary_key: None,
1119                    unique: None,
1120                    index: None,
1121                    foreign_key: None,
1122                },
1123            ],
1124            constraints: vec![],
1125        }];
1126        let result = build_add_constraint(
1127            &DatabaseBackend::Postgres,
1128            "user_roles",
1129            &constraint,
1130            &current_schema,
1131            &[],
1132        )
1133        .unwrap();
1134        let sql = result[0].build(DatabaseBackend::Postgres);
1135        assert!(sql.contains("ADD PRIMARY KEY"));
1136        assert!(sql.contains("\"user_id\""));
1137        assert!(sql.contains("\"role_id\""));
1138    }
1139
1140    #[test]
1141    fn test_add_constraint_composite_primary_key_mysql() {
1142        let constraint = TableConstraint::PrimaryKey {
1143            columns: vec!["user_id".into(), "role_id".into()],
1144            auto_increment: false,
1145        };
1146        let current_schema = vec![TableDef {
1147            name: "user_roles".into(),
1148            description: None,
1149            columns: vec![
1150                ColumnDef {
1151                    name: "user_id".into(),
1152                    r#type: ColumnType::Simple(SimpleColumnType::Integer),
1153                    nullable: false,
1154                    default: None,
1155                    comment: None,
1156                    primary_key: None,
1157                    unique: None,
1158                    index: None,
1159                    foreign_key: None,
1160                },
1161                ColumnDef {
1162                    name: "role_id".into(),
1163                    r#type: ColumnType::Simple(SimpleColumnType::Integer),
1164                    nullable: false,
1165                    default: None,
1166                    comment: None,
1167                    primary_key: None,
1168                    unique: None,
1169                    index: None,
1170                    foreign_key: None,
1171                },
1172            ],
1173            constraints: vec![],
1174        }];
1175        let result = build_add_constraint(
1176            &DatabaseBackend::MySql,
1177            "user_roles",
1178            &constraint,
1179            &current_schema,
1180            &[],
1181        )
1182        .unwrap();
1183        let sql = result[0].build(DatabaseBackend::MySql);
1184        assert!(sql.contains("ADD PRIMARY KEY"));
1185        assert!(sql.contains("`user_id`"));
1186        assert!(sql.contains("`role_id`"));
1187    }
1188
1189    #[test]
1190    fn test_constraints_overlap_primary_key_same_columns() {
1191        let a = TableConstraint::PrimaryKey {
1192            columns: vec!["id".into()],
1193            auto_increment: false,
1194        };
1195        let b = TableConstraint::PrimaryKey {
1196            columns: vec!["id".into()],
1197            auto_increment: true,
1198        };
1199        assert!(constraints_overlap(&a, &b));
1200    }
1201
1202    #[test]
1203    fn test_constraints_overlap_primary_key_different_columns() {
1204        let a = TableConstraint::PrimaryKey {
1205            columns: vec!["id".into()],
1206            auto_increment: false,
1207        };
1208        let b = TableConstraint::PrimaryKey {
1209            columns: vec!["uid".into()],
1210            auto_increment: false,
1211        };
1212        assert!(!constraints_overlap(&a, &b));
1213    }
1214
1215    #[test]
1216    fn test_constraints_overlap_check_same() {
1217        let a = TableConstraint::Check {
1218            name: "chk_age".into(),
1219            expr: "age > 0".into(),
1220        };
1221        let b = TableConstraint::Check {
1222            name: "chk_age".into(),
1223            expr: "age > 0".into(),
1224        };
1225        assert!(constraints_overlap(&a, &b));
1226    }
1227
1228    #[test]
1229    fn test_constraints_overlap_check_different_name() {
1230        let a = TableConstraint::Check {
1231            name: "chk_age".into(),
1232            expr: "age > 0".into(),
1233        };
1234        let b = TableConstraint::Check {
1235            name: "chk_age2".into(),
1236            expr: "age > 0".into(),
1237        };
1238        assert!(!constraints_overlap(&a, &b));
1239    }
1240
1241    #[test]
1242    fn test_constraints_overlap_check_different_expr() {
1243        let a = TableConstraint::Check {
1244            name: "chk_age".into(),
1245            expr: "age > 0".into(),
1246        };
1247        let b = TableConstraint::Check {
1248            name: "chk_age".into(),
1249            expr: "age > 10".into(),
1250        };
1251        assert!(!constraints_overlap(&a, &b));
1252    }
1253
1254    #[test]
1255    fn test_constraints_overlap_different_variants() {
1256        let a = TableConstraint::PrimaryKey {
1257            columns: vec!["id".into()],
1258            auto_increment: false,
1259        };
1260        let b = TableConstraint::Check {
1261            name: "chk".into(),
1262            expr: "id > 0".into(),
1263        };
1264        assert!(!constraints_overlap(&a, &b));
1265    }
1266
1267    #[test]
1268    fn test_constraints_overlap_fk_same_columns() {
1269        let a = TableConstraint::ForeignKey {
1270            name: None,
1271            columns: vec!["user_id".into()],
1272            ref_table: "users".into(),
1273            ref_columns: vec!["id".into()],
1274            on_delete: None,
1275            on_update: None,
1276        };
1277        let b = TableConstraint::ForeignKey {
1278            name: Some("fk".into()),
1279            columns: vec!["user_id".into()],
1280            ref_table: "other".into(),
1281            ref_columns: vec!["oid".into()],
1282            on_delete: Some(ReferenceAction::Cascade),
1283            on_update: None,
1284        };
1285        assert!(constraints_overlap(&a, &b));
1286    }
1287
1288    #[test]
1289    fn test_merge_constraint_replaces_overlapping() {
1290        let existing = vec![
1291            TableConstraint::PrimaryKey {
1292                columns: vec!["id".into()],
1293                auto_increment: false,
1294            },
1295            TableConstraint::Index {
1296                name: None,
1297                columns: vec!["email".into()],
1298            },
1299        ];
1300        let new_pk = TableConstraint::PrimaryKey {
1301            columns: vec!["id".into()],
1302            auto_increment: true,
1303        };
1304        let result = merge_constraint(&existing, &new_pk);
1305        assert_eq!(result.len(), 2); // replaced, not added
1306    }
1307
1308    #[test]
1309    fn test_merge_constraint_appends_non_overlapping() {
1310        let existing = vec![TableConstraint::Index {
1311            name: None,
1312            columns: vec!["email".into()],
1313        }];
1314        let new_pk = TableConstraint::PrimaryKey {
1315            columns: vec!["id".into()],
1316            auto_increment: false,
1317        };
1318        let result = merge_constraint(&existing, &new_pk);
1319        assert_eq!(result.len(), 2); // appended
1320    }
1321
1322    #[test]
1323    fn test_extract_check_clauses_with_mixed_constraints() {
1324        // Test that extract_check_clauses filters out non-Check constraints
1325        let constraints = vec![
1326            TableConstraint::Check {
1327                name: "chk1".into(),
1328                expr: "a > 0".into(),
1329            },
1330            TableConstraint::PrimaryKey {
1331                columns: vec!["id".into()],
1332                auto_increment: false,
1333            },
1334            TableConstraint::Check {
1335                name: "chk2".into(),
1336                expr: "b < 100".into(),
1337            },
1338            TableConstraint::Unique {
1339                name: Some("uq".into()),
1340                columns: vec!["email".into()],
1341            },
1342        ];
1343        let clauses = crate::sql::helpers::extract_check_clauses(&constraints);
1344        assert_eq!(clauses.len(), 2);
1345        assert!(clauses[0].contains("chk1"));
1346        assert!(clauses[1].contains("chk2"));
1347    }
1348
1349    #[test]
1350    fn test_extract_check_clauses_with_no_check_constraints() {
1351        let constraints = vec![
1352            TableConstraint::PrimaryKey {
1353                columns: vec!["id".into()],
1354                auto_increment: false,
1355            },
1356            TableConstraint::Unique {
1357                name: None,
1358                columns: vec!["email".into()],
1359            },
1360        ];
1361        let clauses = crate::sql::helpers::extract_check_clauses(&constraints);
1362        assert!(clauses.is_empty());
1363    }
1364
1365    #[test]
1366    fn test_build_create_with_checks_empty_clauses() {
1367        use crate::sql::create_table::build_create_table_for_backend;
1368
1369        let create_stmt = build_create_table_for_backend(
1370            &DatabaseBackend::Sqlite,
1371            "test_table",
1372            &[ColumnDef {
1373                name: "id".into(),
1374                r#type: ColumnType::Simple(SimpleColumnType::Integer),
1375                nullable: false,
1376                default: None,
1377                comment: None,
1378                primary_key: None,
1379                unique: None,
1380                index: None,
1381                foreign_key: None,
1382            }],
1383            &[],
1384        );
1385
1386        // Empty check_clauses should return CreateTable variant
1387        let result = crate::sql::helpers::build_create_with_checks(
1388            &DatabaseBackend::Sqlite,
1389            &create_stmt,
1390            &[],
1391        );
1392        let sql = result.build(DatabaseBackend::Sqlite);
1393        assert!(sql.contains("CREATE TABLE"));
1394    }
1395
1396    #[test]
1397    fn test_build_create_with_checks_with_clauses() {
1398        use crate::sql::create_table::build_create_table_for_backend;
1399
1400        let create_stmt = build_create_table_for_backend(
1401            &DatabaseBackend::Sqlite,
1402            "test_table",
1403            &[ColumnDef {
1404                name: "id".into(),
1405                r#type: ColumnType::Simple(SimpleColumnType::Integer),
1406                nullable: false,
1407                default: None,
1408                comment: None,
1409                primary_key: None,
1410                unique: None,
1411                index: None,
1412                foreign_key: None,
1413            }],
1414            &[],
1415        );
1416
1417        // Non-empty check_clauses should return Raw variant with embedded checks
1418        let check_clauses = vec!["CONSTRAINT \"chk1\" CHECK (id > 0)".to_string()];
1419        let result = crate::sql::helpers::build_create_with_checks(
1420            &DatabaseBackend::Sqlite,
1421            &create_stmt,
1422            &check_clauses,
1423        );
1424        let sql = result.build(DatabaseBackend::Sqlite);
1425        assert!(sql.contains("CREATE TABLE"));
1426        assert!(sql.contains("CONSTRAINT \"chk1\" CHECK (id > 0)"));
1427    }
1428}
vespertide_query/sql/add_constraint.rs

vespertide_query/sql/
add_constraint.rs
