1mod mysql;
2mod postgres;
3mod sqlite;
4
5use sea_query::Alias;
6
7use vespertide_core::{TableConstraint, TableDef};
8
9use super::types::{BuiltQuery, DatabaseBackend};
10use crate::error::QueryError;
11
12pub fn build_remove_constraint(
13 backend: DatabaseBackend,
14 table: &str,
15 constraint: &TableConstraint,
16 current_schema: &[TableDef],
17 pending_constraints: &[TableConstraint],
18) -> Result<Vec<BuiltQuery>, QueryError> {
19 if backend == DatabaseBackend::Sqlite && sqlite::requires_rebuild(constraint) {
20 return sqlite::build_remove_constraint(
21 table,
22 constraint,
23 current_schema,
24 pending_constraints,
25 );
26 }
27
28 if backend == DatabaseBackend::Postgres {
29 Ok(postgres::build_remove_constraint(table, constraint))
30 } else if backend == DatabaseBackend::MySql {
31 Ok(mysql::build_remove_constraint(table, constraint))
32 } else {
33 build_drop_index(table, constraint)
34 }
35}
36
37fn build_drop_index(
38 table: &str,
39 constraint: &TableConstraint,
40) -> Result<Vec<BuiltQuery>, QueryError> {
41 let TableConstraint::Index { name, columns } = constraint else {
42 return Err(QueryError::BackendError {
43 backend: DatabaseBackend::Sqlite,
44 message: format!(
45 "SQLite constraint '{}' requires a table rebuild",
46 constraint_kind(constraint)
47 ),
48 });
49 };
50
51 let index_name = vespertide_naming::build_index_name(table, columns, name.as_deref());
52 let idx_drop = sea_query::Index::drop()
53 .table(Alias::new(table))
54 .name(&index_name)
55 .to_owned();
56 Ok(vec![BuiltQuery::DropIndex(Box::new(idx_drop))])
57}
58
59fn constraint_kind(constraint: &TableConstraint) -> &'static str {
60 match constraint {
61 TableConstraint::PrimaryKey { .. } => "primary key",
62 TableConstraint::Unique { .. } => "unique",
63 TableConstraint::ForeignKey { .. } => "foreign key",
64 TableConstraint::Index { .. } => "index",
65 TableConstraint::Check { .. } => "check",
66 _ => unreachable!("TableConstraint is #[non_exhaustive]; all variants are matched above"),
67 }
68}
69
70#[cfg(test)]
71mod tests {
72 use super::*;
73 use crate::sql::types::DatabaseBackend;
74 use insta::{assert_snapshot, with_settings};
75 use rstest::rstest;
76 use vespertide_core::{
77 ColumnDef, ColumnType, SimpleColumnType, StrOrBoolOrArray, TableConstraint,
78 };
79
80 fn int_col(name: &str) -> ColumnDef {
81 col(name, SimpleColumnType::Integer)
82 }
83
84 fn text_col(name: &str) -> ColumnDef {
85 col(name, SimpleColumnType::Text)
86 }
87
88 fn col(name: &str, ty: SimpleColumnType) -> ColumnDef {
89 ColumnDef {
90 name: name.into(),
91 r#type: ColumnType::Simple(ty),
92 nullable: name != "id",
93 default: None,
94 comment: None,
95 primary_key: None,
96 unique: None,
97 index: None,
98 foreign_key: None,
99 }
100 }
101
102 fn table(name: &str, columns: Vec<ColumnDef>, constraints: Vec<TableConstraint>) -> TableDef {
103 TableDef {
104 name: name.into(),
105 description: None,
106 columns,
107 constraints,
108 }
109 }
110
111 fn pk() -> TableConstraint {
112 TableConstraint::PrimaryKey {
113 columns: vec!["id".into()],
114 auto_increment: false,
115 strategy: vespertide_core::PrimaryKeyAdditionStrategy::default(),
116 }
117 }
118
119 fn unique(name: Option<&str>, columns: &[&str]) -> TableConstraint {
120 TableConstraint::Unique {
121 name: name.map(Into::into),
122 columns: columns.iter().copied().map(Into::into).collect(),
123 strategy: vespertide_core::UniqueConstraintStrategy::DeleteDuplicates {
124 keep: vespertide_core::KeepPolicy::First,
125 },
126 }
127 }
128
129 fn fk(name: Option<&str>) -> TableConstraint {
130 TableConstraint::ForeignKey {
131 name: name.map(Into::into),
132 columns: vec!["user_id".into()],
133 ref_table: "users".into(),
134 ref_columns: vec!["id".into()],
135 on_delete: None,
136 on_update: None,
137 orphan_strategy: vespertide_core::ForeignKeyOrphanStrategy::default(),
138 }
139 }
140
141 fn check(name: &str) -> TableConstraint {
142 TableConstraint::Check {
143 name: name.into(),
144 expr: "age > 0".into(),
145 strategy: vespertide_core::CheckViolationStrategy::default(),
146 }
147 }
148
149 fn index(name: &str, columns: &[&str]) -> TableConstraint {
150 TableConstraint::Index {
151 name: Some(name.into()),
152 columns: columns.iter().copied().map(Into::into).collect(),
153 }
154 }
155
156 fn render(
157 backend: DatabaseBackend,
158 table_name: &str,
159 constraint: &TableConstraint,
160 schema: &[TableDef],
161 ) -> String {
162 build_remove_constraint(backend, table_name, constraint, schema, &[])
163 .unwrap()
164 .iter()
165 .map(|query| query.build(backend))
166 .collect::<Vec<_>>()
167 .join("\n")
168 }
169
170 fn assert_rendered(
171 backend: DatabaseBackend,
172 table_name: &str,
173 constraint: &TableConstraint,
174 schema: &[TableDef],
175 expected: &[&str],
176 ) -> String {
177 let sql = render(backend, table_name, constraint, schema);
178 for fragment in expected {
179 assert!(
180 sql.contains(fragment),
181 "Expected SQL to contain '{fragment}', got: {sql}"
182 );
183 }
184 sql
185 }
186
187 #[rstest]
188 #[case::remove_constraint_primary_key_postgres(
189 "remove_constraint_primary_key_postgres",
190 DatabaseBackend::Postgres,
191 pk(),
192 vec![int_col("id")],
193 &["DROP CONSTRAINT \"users_pkey\""]
194 )]
195 #[case::remove_constraint_primary_key_mysql(
196 "remove_constraint_primary_key_mysql",
197 DatabaseBackend::MySql,
198 pk(),
199 vec![int_col("id")],
200 &["DROP PRIMARY KEY"]
201 )]
202 #[case::remove_constraint_primary_key_sqlite(
203 "remove_constraint_primary_key_sqlite",
204 DatabaseBackend::Sqlite,
205 pk(),
206 vec![int_col("id")],
207 &["CREATE TABLE \"users_temp\""]
208 )]
209 #[case::remove_constraint_unique_named_postgres(
210 "remove_constraint_unique_named_postgres",
211 DatabaseBackend::Postgres,
212 unique(Some("uq_email"), &["email"]),
213 vec![int_col("id")],
214 &["DROP INDEX \"uq_users__uq_email\""]
215 )]
216 #[case::remove_constraint_unique_named_mysql(
217 "remove_constraint_unique_named_mysql",
218 DatabaseBackend::MySql,
219 unique(Some("uq_email"), &["email"]),
220 vec![int_col("id")],
221 &["DROP INDEX `uq_users__uq_email`"]
222 )]
223 #[case::remove_constraint_unique_named_sqlite(
224 "remove_constraint_unique_named_sqlite",
225 DatabaseBackend::Sqlite,
226 unique(Some("uq_email"), &["email"]),
227 vec![int_col("id")],
228 &["CREATE TABLE \"users_temp\""]
229 )]
230 #[case::remove_constraint_foreign_key_named_postgres(
231 "remove_constraint_foreign_key_named_postgres",
232 DatabaseBackend::Postgres,
233 fk(Some("fk_user")),
234 vec![int_col("id"), int_col("user_id")],
235 &["DROP CONSTRAINT \"fk_users__fk_user\""]
236 )]
237 #[case::remove_constraint_foreign_key_named_mysql(
238 "remove_constraint_foreign_key_named_mysql",
239 DatabaseBackend::MySql,
240 fk(Some("fk_user")),
241 vec![int_col("id"), int_col("user_id")],
242 &["DROP FOREIGN KEY `fk_users__fk_user`"]
243 )]
244 #[case::remove_constraint_foreign_key_named_sqlite(
245 "remove_constraint_foreign_key_named_sqlite",
246 DatabaseBackend::Sqlite,
247 fk(Some("fk_user")),
248 vec![int_col("id"), int_col("user_id")],
249 &["CREATE TABLE \"users_temp\""]
250 )]
251 #[case::remove_constraint_check_named_postgres(
252 "remove_constraint_check_named_postgres",
253 DatabaseBackend::Postgres,
254 check("chk_age"),
255 vec![int_col("id"), int_col("age")],
256 &["DROP CONSTRAINT \"chk_age\""]
257 )]
258 #[case::remove_constraint_check_named_mysql(
259 "remove_constraint_check_named_mysql",
260 DatabaseBackend::MySql,
261 check("chk_age"),
262 vec![int_col("id"), int_col("age")],
263 &["DROP CHECK `chk_age`"]
264 )]
265 #[case::remove_constraint_check_named_sqlite(
266 "remove_constraint_check_named_sqlite",
267 DatabaseBackend::Sqlite,
268 check("chk_age"),
269 vec![int_col("id"), int_col("age")],
270 &["CREATE TABLE \"users_temp\""]
271 )]
272 fn test_remove_constraint(
273 #[case] title: &str,
274 #[case] backend: DatabaseBackend,
275 #[case] constraint: TableConstraint,
276 #[case] columns: Vec<ColumnDef>,
277 #[case] expected: &[&str],
278 ) {
279 let schema = vec![table("users", columns, vec![constraint.clone()])];
280 let sql = assert_rendered(backend, "users", &constraint, &schema, expected);
281
282 with_settings!({ snapshot_path => "../snapshots", snapshot_suffix => format!("remove_constraint_{title}") }, {
283 assert_snapshot!(sql);
284 });
285 }
286
287 #[rstest]
288 #[case::primary_key(DatabaseBackend::Sqlite, pk())]
289 #[case::unique(DatabaseBackend::Sqlite, unique(Some("uq_email"), &["email"]))]
290 #[case::foreign_key(DatabaseBackend::Sqlite, fk(Some("fk_user")))]
291 #[case::check(DatabaseBackend::Sqlite, check("chk_age"))]
292 fn test_remove_constraint_sqlite_table_not_found(
293 #[case] backend: DatabaseBackend,
294 #[case] constraint: TableConstraint,
295 ) {
296 let result = build_remove_constraint(backend, "nonexistent_table", &constraint, &[], &[]);
297 assert!(result.is_err());
298 assert!(
299 result
300 .unwrap_err()
301 .to_string()
302 .contains("Table 'nonexistent_table' not found in current schema")
303 );
304 }
305
306 #[rstest]
307 #[case::remove_primary_key_with_index(
308 "remove_primary_key_with_index",
309 "users",
310 pk(),
311 vec![int_col("id")],
312 vec![pk(), index("idx_id", &["id"])],
313 Some("ix_users__idx_id")
314 )]
315 #[case::remove_unique_with_index(
316 "remove_unique_with_index",
317 "users",
318 unique(Some("uq_email"), &["email"]),
319 vec![int_col("id"), text_col("email")],
320 vec![unique(Some("uq_email"), &["email"]), index("idx_id", &["id"])],
321 Some("ix_users__idx_id")
322 )]
323 #[case::remove_foreign_key_with_index(
324 "remove_foreign_key_with_index",
325 "posts",
326 fk(Some("fk_user")),
327 vec![int_col("id"), int_col("user_id")],
328 vec![fk(Some("fk_user")), index("idx_user_id", &["user_id"])],
329 Some("idx_user_id")
330 )]
331 #[case::remove_check_with_index(
332 "remove_check_with_index",
333 "users",
334 check("chk_age"),
335 vec![int_col("id"), int_col("age")],
336 vec![check("chk_age"), index("idx_age", &["age"])],
337 Some("idx_age")
338 )]
339 #[case::remove_primary_key_with_unique_constraint(
340 "remove_primary_key_with_unique_constraint",
341 "users",
342 pk(),
343 vec![int_col("id"), text_col("email")],
344 vec![pk(), unique(Some("uq_email"), &["email"])],
345 None
346 )]
347 #[case::remove_unique_with_other_unique_constraint(
348 "remove_unique_with_other_unique_constraint",
349 "users",
350 unique(Some("uq_email"), &["email"]),
351 vec![int_col("id"), text_col("email"), text_col("name")],
352 vec![unique(Some("uq_email"), &["email"]), unique(Some("uq_name"), &["name"])],
353 None
354 )]
355 #[case::remove_foreign_key_with_unique_constraint(
356 "remove_foreign_key_with_unique_constraint",
357 "posts",
358 fk(Some("fk_user")),
359 vec![int_col("id"), int_col("user_id")],
360 vec![fk(Some("fk_user")), unique(Some("uq_user_id"), &["user_id"])],
361 None
362 )]
363 #[case::remove_check_with_unique_constraint(
364 "remove_check_with_unique_constraint",
365 "users",
366 check("chk_age"),
367 vec![int_col("id"), int_col("age")],
368 vec![check("chk_age"), unique(Some("uq_age"), &["age"])],
369 None
370 )]
371 #[case::remove_unique_with_other_constraints(
372 "remove_unique_with_other_constraints",
373 "users",
374 unique(Some("uq_email"), &["email"]),
375 vec![int_col("id"), text_col("email")],
376 vec![pk(), unique(Some("uq_email"), &["email"]), TableConstraint::Check { name: "chk_email".into(), expr: "email IS NOT NULL".into(), strategy: vespertide_core::CheckViolationStrategy::default() }],
377 None
378 )]
379 #[case::remove_foreign_key_with_other_constraints(
380 "remove_foreign_key_with_other_constraints",
381 "posts",
382 fk(Some("fk_user")),
383 vec![int_col("id"), int_col("user_id")],
384 vec![pk(), fk(Some("fk_user")), unique(Some("uq_user_id"), &["user_id"]), TableConstraint::Check { name: "chk_user_id".into(), expr: "user_id > 0".into(), strategy: vespertide_core::CheckViolationStrategy::default() }],
385 None
386 )]
387 #[case::remove_check_with_other_constraints(
388 "remove_check_with_other_constraints",
389 "users",
390 check("chk_age"),
391 vec![int_col("id"), int_col("age")],
392 vec![pk(), unique(Some("uq_age"), &["age"]), check("chk_age")],
393 None
394 )]
395 fn test_remove_constraint_with_companion_constraints(
396 #[case] title: &str,
397 #[case] table_name: &str,
398 #[case] constraint: TableConstraint,
399 #[case] columns: Vec<ColumnDef>,
400 #[case] constraints: Vec<TableConstraint>,
401 #[case] sqlite_fragment: Option<&str>,
402 ) {
403 for backend in [
404 DatabaseBackend::Postgres,
405 DatabaseBackend::MySql,
406 DatabaseBackend::Sqlite,
407 ] {
408 let schema = vec![table(table_name, columns.clone(), constraints.clone())];
409 let sql = render(backend, table_name, &constraint, &schema);
410
411 if matches!(backend, DatabaseBackend::Sqlite) {
412 assert!(sql.contains("CREATE TABLE"));
413 if let Some(fragment) = sqlite_fragment {
414 assert!(sql.contains(fragment), "Expected {fragment} in {sql}");
415 }
416 } else {
417 assert!(sql.contains("DROP"));
418 }
419
420 let _ = title;
421 }
422 }
423
424 #[rstest]
425 #[case::remove_unique_without_name(
426 "remove_unique_without_name",
427 "users",
428 unique(None, &["email"]),
429 vec![int_col("id"), text_col("email")]
430 )]
431 #[case::remove_foreign_key_without_name(
432 "remove_foreign_key_without_name",
433 "posts",
434 fk(None),
435 vec![int_col("id"), int_col("user_id")]
436 )]
437 fn test_remove_constraint_without_name(
438 #[case] title: &str,
439 #[case] table_name: &str,
440 #[case] constraint: TableConstraint,
441 #[case] columns: Vec<ColumnDef>,
442 ) {
443 for backend in [
444 DatabaseBackend::Postgres,
445 DatabaseBackend::MySql,
446 DatabaseBackend::Sqlite,
447 ] {
448 let schema = vec![table(table_name, columns.clone(), vec![constraint.clone()])];
449 let sql = render(backend, table_name, &constraint, &schema);
450 if matches!(backend, DatabaseBackend::Sqlite) {
451 assert!(sql.contains("CREATE TABLE"));
452 } else {
453 assert!(sql.contains("email") || sql.contains("user_id"));
454 }
455
456 let _ = title;
457 }
458 }
459
460 #[test]
461 fn test_remove_constraint_primary_key_postgres_direct() {
462 let constraint = pk();
463 let schema = vec![table(
464 "orders",
465 vec![int_col("id")],
466 vec![constraint.clone()],
467 )];
468 let result = build_remove_constraint(
469 DatabaseBackend::Postgres,
470 "orders",
471 &constraint,
472 &schema,
473 &[],
474 )
475 .unwrap();
476 assert_eq!(result.len(), 1);
477 assert!(
478 result[0]
479 .build(DatabaseBackend::Postgres)
480 .contains("ALTER TABLE \"orders\" DROP CONSTRAINT \"orders_pkey\"")
481 );
482 }
483
484 #[test]
485 fn test_remove_constraint_primary_key_mysql_direct() {
486 let constraint = pk();
487 let schema = vec![table(
488 "orders",
489 vec![int_col("id")],
490 vec![constraint.clone()],
491 )];
492 let result =
493 build_remove_constraint(DatabaseBackend::MySql, "orders", &constraint, &schema, &[])
494 .unwrap();
495 assert_eq!(result.len(), 1);
496 assert!(
497 result[0]
498 .build(DatabaseBackend::MySql)
499 .contains("ALTER TABLE `orders` DROP PRIMARY KEY")
500 );
501 }
502
503 #[rstest]
504 #[case::remove_index_with_custom_inline_name_postgres(DatabaseBackend::Postgres)]
505 #[case::remove_index_with_custom_inline_name_mysql(DatabaseBackend::MySql)]
506 #[case::remove_index_with_custom_inline_name_sqlite(DatabaseBackend::Sqlite)]
507 fn test_remove_constraint_index_with_custom_inline_name(#[case] backend: DatabaseBackend) {
508 let constraint = index("custom_idx_email", &["email"]);
509 let mut email = text_col("email");
510 email.index = Some(StrOrBoolOrArray::Str("custom_idx_email".into()));
511 let schema = vec![table("users", vec![email], vec![])];
512
513 let sql = render(backend, "users", &constraint, &schema);
514 assert!(sql.contains("custom_idx_email"));
515
516 let _ = backend;
517 }
518
519 #[test]
524 fn build_remove_constraint_postgres_index_takes_top_level_dispatch() {
525 let constraint = index("idx_email", &["email"]);
526 let queries =
527 build_remove_constraint(DatabaseBackend::Postgres, "users", &constraint, &[], &[])
528 .unwrap();
529 assert_eq!(queries.len(), 1);
530 let sql = queries[0].build(DatabaseBackend::Postgres);
531 assert!(sql.contains("DROP INDEX"));
532 }
533
534 #[rstest]
544 #[case::primary_key(pk(), "primary key")]
545 #[case::unique(unique(Some("uq_email"), &["email"]), "unique")]
546 #[case::foreign_key(fk(Some("fk_user")), "foreign key")]
547 #[case::check(check("chk_age"), "check")]
548 fn build_drop_index_rejects_non_index_constraint(
549 #[case] constraint: TableConstraint,
550 #[case] expected_kind: &str,
551 ) {
552 let result = build_drop_index("users", &constraint);
553 assert!(result.is_err());
554 let err = result.unwrap_err();
555 let message = err.to_string();
556 assert!(
557 message.contains(expected_kind),
558 "error message should mention '{expected_kind}', got: {message}"
559 );
560 assert!(
561 message.contains("requires a table rebuild"),
562 "error must explain why drop_index is not applicable, got: {message}"
563 );
564 }
565
566 #[rstest]
571 #[case::primary_key(pk(), "primary key")]
572 #[case::unique(unique(None, &["email"]), "unique")]
573 #[case::foreign_key(fk(None), "foreign key")]
574 #[case::index(index("idx_email", &["email"]), "index")]
575 #[case::check(check("chk_age"), "check")]
576 fn constraint_kind_maps_every_variant(
577 #[case] constraint: TableConstraint,
578 #[case] expected: &str,
579 ) {
580 assert_eq!(constraint_kind(&constraint), expected);
581 }
582
583 #[rstest]
584 #[case::named(Some("idx_user_email"))]
585 #[case::unnamed(None)]
586 fn remove_index_sqlite_uses_drop_index_path(#[case] name: Option<&str>) {
587 let constraint = TableConstraint::Index {
588 name: name.map(Into::into),
589 columns: vec!["email".into()],
590 };
591 let queries =
592 build_remove_constraint(DatabaseBackend::Sqlite, "users", &constraint, &[], &[])
593 .unwrap();
594 assert_eq!(queries.len(), 1);
595 assert!(
596 queries[0]
597 .build(DatabaseBackend::Sqlite)
598 .contains("DROP INDEX")
599 );
600 }
601
602 #[rstest]
603 #[case::postgres(DatabaseBackend::Postgres)]
604 #[case::mysql(DatabaseBackend::MySql)]
605 fn remove_index_non_sqlite_uses_backend_module(#[case] backend: DatabaseBackend) {
606 let constraint = TableConstraint::Index {
607 name: Some("idx_user_email".into()),
608 columns: vec!["email".into()],
609 };
610 let queries = build_remove_constraint(backend, "users", &constraint, &[], &[]).unwrap();
611 assert_eq!(queries.len(), 1);
612 assert!(queries[0].build(backend).contains("DROP INDEX"));
613 }
614
615 #[rstest]
622 #[case::postgres(DatabaseBackend::Postgres)]
623 #[case::mysql(DatabaseBackend::MySql)]
624 #[case::sqlite(DatabaseBackend::Sqlite)]
625 fn remove_composite_unique_preserves_overlapping_single_column_unique(
626 #[case] backend: DatabaseBackend,
627 ) {
628 let single_email = unique(None, &["email"]);
629 let composite = unique(None, &["email", "tenant_id"]);
630 let schema = vec![table(
631 "users",
632 vec![int_col("id"), text_col("email"), int_col("tenant_id")],
633 vec![single_email.clone(), composite.clone()],
634 )];
635
636 let sql = render(backend, "users", &composite, &schema);
637
638 match backend {
639 DatabaseBackend::Sqlite => {
640 assert!(
641 sql.contains(
642 "CREATE UNIQUE INDEX \"uq_users__email\" ON \"users\" (\"email\")"
643 ),
644 "SQLite rebuild must recreate the (email) unique index \
645 after removing the composite (email, tenant_id); got: {sql}"
646 );
647 assert!(
648 !sql.contains("uq_users__email_tenant_id"),
649 "Composite unique must not appear in the recreated \
650 index set; got: {sql}"
651 );
652 assert_eq!(
653 sql.matches("CREATE UNIQUE INDEX").count(),
654 1,
655 "Expected exactly one CREATE UNIQUE INDEX (single \
656 email unique recreated, composite gone); got: {sql}"
657 );
658 }
659 DatabaseBackend::Postgres => {
660 assert!(
661 sql.contains("DROP INDEX \"uq_users__email_tenant_id\""),
662 "Postgres must DROP the composite-unique index by its \
663 auto-derived name; got: {sql}"
664 );
665 }
666 DatabaseBackend::MySql => {
667 assert!(
668 sql.contains("DROP INDEX `uq_users__email_tenant_id`"),
669 "MySQL must DROP the composite-unique index by its \
670 auto-derived name; got: {sql}"
671 );
672 }
673 }
674 }
675}