vespertide_query/sql/
add_constraint.rs

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

vespertide_query/sql/
add_constraint.rs
