1use crate::quoting::{quote_identifier, quote_qualified_identifier};
2use sql_orm_core::{OrmError, ReferentialAction, SqlServerType};
3use sql_orm_migrate::{
4 AddColumn, AddForeignKey, AlterColumn, ColumnSnapshot, CreateIndex, CreateSchema, CreateTable,
5 DropColumn, DropForeignKey, DropIndex, DropSchema, DropTable, IndexColumnSnapshot,
6 MigrationOperation, RenameColumn, RenameTable,
7};
8
9const MIGRATIONS_HISTORY_SCHEMA: &str = "dbo";
10const MIGRATIONS_HISTORY_TABLE: &str = "__sql_orm_migrations";
11
12impl crate::SqlServerCompiler {
13 pub fn compile_migration_operations(
14 operations: &[MigrationOperation],
15 ) -> Result<Vec<String>, OrmError> {
16 operations.iter().map(compile_operation).collect()
17 }
18
19 pub fn compile_migrations_history_table() -> Result<String, OrmError> {
20 let table =
21 quote_qualified_identifier(MIGRATIONS_HISTORY_SCHEMA, MIGRATIONS_HISTORY_TABLE)?;
22
23 Ok(format!(
24 "IF OBJECT_ID(N'{schema}.{table_name}', N'U') IS NULL\nBEGIN\n CREATE TABLE {table} (\n [id] nvarchar(150) NOT NULL PRIMARY KEY,\n [name] nvarchar(255) NOT NULL,\n [applied_at] datetime2 NOT NULL DEFAULT SYSUTCDATETIME(),\n [checksum] nvarchar(128) NOT NULL,\n [orm_version] nvarchar(50) NOT NULL\n );\nEND",
25 schema = MIGRATIONS_HISTORY_SCHEMA,
26 table_name = MIGRATIONS_HISTORY_TABLE,
27 ))
28 }
29}
30
31fn compile_operation(operation: &MigrationOperation) -> Result<String, OrmError> {
32 match operation {
33 MigrationOperation::CreateSchema(operation) => compile_create_schema(operation),
34 MigrationOperation::DropSchema(operation) => compile_drop_schema(operation),
35 MigrationOperation::CreateTable(operation) => compile_create_table(operation),
36 MigrationOperation::DropTable(operation) => compile_drop_table(operation),
37 MigrationOperation::RenameTable(operation) => compile_rename_table(operation),
38 MigrationOperation::RenameColumn(operation) => compile_rename_column(operation),
39 MigrationOperation::AddColumn(operation) => compile_add_column(operation),
40 MigrationOperation::DropColumn(operation) => compile_drop_column(operation),
41 MigrationOperation::AlterColumn(operation) => compile_alter_column(operation),
42 MigrationOperation::CreateIndex(operation) => compile_create_index(operation),
43 MigrationOperation::DropIndex(operation) => compile_drop_index(operation),
44 MigrationOperation::AddForeignKey(operation) => compile_add_foreign_key(operation),
45 MigrationOperation::DropForeignKey(operation) => compile_drop_foreign_key(operation),
46 }
47}
48
49fn compile_create_schema(operation: &CreateSchema) -> Result<String, OrmError> {
50 let schema = crate::quote_identifier(&operation.schema_name)?;
51 Ok(format!(
52 "IF SCHEMA_ID(N'{name}') IS NULL EXEC(N'CREATE SCHEMA {schema}')",
53 name = operation.schema_name,
54 ))
55}
56
57fn compile_drop_schema(operation: &DropSchema) -> Result<String, OrmError> {
58 Ok(format!(
59 "DROP SCHEMA {}",
60 quote_identifier(&operation.schema_name)?
61 ))
62}
63
64fn compile_create_table(operation: &CreateTable) -> Result<String, OrmError> {
65 let table_name = quote_qualified_identifier(&operation.schema_name, &operation.table.name)?;
66 let mut definitions = operation
67 .table
68 .columns
69 .iter()
70 .map(compile_column_definition)
71 .collect::<Result<Vec<_>, _>>()?;
72
73 if !operation.table.primary_key_columns.is_empty() {
74 let columns = operation
75 .table
76 .primary_key_columns
77 .iter()
78 .map(|column| crate::quote_identifier(column))
79 .collect::<Result<Vec<_>, _>>()?
80 .join(", ");
81
82 definitions.push(match &operation.table.primary_key_name {
83 Some(name) => format!(
84 "CONSTRAINT {} PRIMARY KEY ({columns})",
85 crate::quote_identifier(name)?
86 ),
87 None => format!("PRIMARY KEY ({columns})"),
88 });
89 }
90
91 Ok(format!(
92 "CREATE TABLE {table_name} (\n {}\n)",
93 definitions.join(",\n ")
94 ))
95}
96
97fn compile_drop_table(operation: &DropTable) -> Result<String, OrmError> {
98 Ok(format!(
99 "DROP TABLE {}",
100 quote_qualified_identifier(&operation.schema_name, &operation.table_name)?
101 ))
102}
103
104fn compile_add_column(operation: &AddColumn) -> Result<String, OrmError> {
105 Ok(format!(
106 "ALTER TABLE {} ADD {}",
107 quote_qualified_identifier(&operation.schema_name, &operation.table_name)?,
108 compile_column_definition(&operation.column)?,
109 ))
110}
111
112fn compile_rename_table(operation: &RenameTable) -> Result<String, OrmError> {
113 let qualified_table =
114 quote_qualified_identifier(&operation.schema_name, &operation.previous_table_name)?;
115
116 Ok(format!(
117 "EXEC sp_rename N'{qualified_table}', N'{next_name}', N'OBJECT'",
118 next_name = operation.next_table_name,
119 ))
120}
121
122fn compile_rename_column(operation: &RenameColumn) -> Result<String, OrmError> {
123 let qualified_column = format!(
124 "{}.{}",
125 quote_qualified_identifier(&operation.schema_name, &operation.table_name)?,
126 quote_identifier(&operation.previous_column_name)?,
127 );
128
129 Ok(format!(
130 "EXEC sp_rename N'{qualified_column}', N'{next_name}', N'COLUMN'",
131 next_name = operation.next_column_name,
132 ))
133}
134
135fn compile_drop_column(operation: &DropColumn) -> Result<String, OrmError> {
136 Ok(format!(
137 "ALTER TABLE {} DROP COLUMN {}",
138 quote_qualified_identifier(&operation.schema_name, &operation.table_name)?,
139 crate::quote_identifier(&operation.column_name)?,
140 ))
141}
142
143fn compile_alter_column(operation: &AlterColumn) -> Result<String, OrmError> {
144 if operation.previous.name != operation.next.name {
145 return Err(OrmError::new(
146 "SQL Server alter column compilation does not support renaming columns",
147 ));
148 }
149
150 if operation.previous.default_sql != operation.next.default_sql
151 || operation.previous.computed_sql != operation.next.computed_sql
152 || operation.previous.identity != operation.next.identity
153 || operation.previous.primary_key != operation.next.primary_key
154 || operation.previous.rowversion != operation.next.rowversion
155 {
156 return Err(OrmError::new(
157 "SQL Server alter column compilation only supports type and nullability changes in this stage",
158 ));
159 }
160
161 Ok(format!(
162 "ALTER TABLE {} ALTER COLUMN {}",
163 quote_qualified_identifier(&operation.schema_name, &operation.table_name)?,
164 compile_alter_column_definition(&operation.next)?,
165 ))
166}
167
168fn compile_create_index(operation: &CreateIndex) -> Result<String, OrmError> {
169 if operation.index.columns.is_empty() {
170 return Err(OrmError::new(
171 "SQL Server index migration compilation requires at least one indexed column",
172 ));
173 }
174
175 let index_name = quote_identifier(&operation.index.name)?;
176 let table = quote_qualified_identifier(&operation.schema_name, &operation.table_name)?;
177 let columns = operation
178 .index
179 .columns
180 .iter()
181 .map(compile_index_column)
182 .collect::<Result<Vec<_>, _>>()?
183 .join(", ");
184 let unique = if operation.index.unique {
185 "UNIQUE "
186 } else {
187 ""
188 };
189
190 Ok(format!(
191 "CREATE {unique}INDEX {index_name} ON {table} ({columns})"
192 ))
193}
194
195fn compile_drop_index(operation: &DropIndex) -> Result<String, OrmError> {
196 Ok(format!(
197 "DROP INDEX {} ON {}",
198 quote_identifier(&operation.index_name)?,
199 quote_qualified_identifier(&operation.schema_name, &operation.table_name)?,
200 ))
201}
202
203fn compile_add_foreign_key(operation: &AddForeignKey) -> Result<String, OrmError> {
204 if operation.foreign_key.columns.is_empty() {
205 return Err(OrmError::new(
206 "SQL Server foreign key migration compilation requires at least one local column",
207 ));
208 }
209
210 if operation.foreign_key.referenced_columns.is_empty() {
211 return Err(OrmError::new(
212 "SQL Server foreign key migration compilation requires at least one referenced column",
213 ));
214 }
215
216 if operation.foreign_key.columns.len() != operation.foreign_key.referenced_columns.len() {
217 return Err(OrmError::new(
218 "SQL Server foreign key migration compilation requires the same number of local and referenced columns",
219 ));
220 }
221
222 let table = quote_qualified_identifier(&operation.schema_name, &operation.table_name)?;
223 let constraint = quote_identifier(&operation.foreign_key.name)?;
224 let columns = operation
225 .foreign_key
226 .columns
227 .iter()
228 .map(|column| quote_identifier(column))
229 .collect::<Result<Vec<_>, _>>()?
230 .join(", ");
231 let referenced_table = quote_qualified_identifier(
232 &operation.foreign_key.referenced_schema,
233 &operation.foreign_key.referenced_table,
234 )?;
235 let referenced_columns = operation
236 .foreign_key
237 .referenced_columns
238 .iter()
239 .map(|column| quote_identifier(column))
240 .collect::<Result<Vec<_>, _>>()?
241 .join(", ");
242 let on_delete = render_foreign_key_action_clause("DELETE", operation.foreign_key.on_delete)?;
243 let on_update = render_foreign_key_action_clause("UPDATE", operation.foreign_key.on_update)?;
244
245 Ok(format!(
246 "ALTER TABLE {table} ADD CONSTRAINT {constraint} FOREIGN KEY ({columns}) REFERENCES {referenced_table} ({referenced_columns}){on_delete}{on_update}"
247 ))
248}
249
250fn compile_drop_foreign_key(operation: &DropForeignKey) -> Result<String, OrmError> {
251 Ok(format!(
252 "ALTER TABLE {} DROP CONSTRAINT {}",
253 quote_qualified_identifier(&operation.schema_name, &operation.table_name)?,
254 quote_identifier(&operation.foreign_key_name)?,
255 ))
256}
257
258fn render_foreign_key_action_clause(
259 action_kind: &str,
260 action: ReferentialAction,
261) -> Result<String, OrmError> {
262 let action_sql = match action {
263 ReferentialAction::NoAction => "NO ACTION",
264 ReferentialAction::Cascade => "CASCADE",
265 ReferentialAction::SetNull => "SET NULL",
266 ReferentialAction::SetDefault => "SET DEFAULT",
267 };
268
269 Ok(format!(" ON {action_kind} {action_sql}"))
270}
271
272fn compile_index_column(column: &IndexColumnSnapshot) -> Result<String, OrmError> {
273 Ok(format!(
274 "{} {}",
275 quote_identifier(&column.column_name)?,
276 if column.descending { "DESC" } else { "ASC" }
277 ))
278}
279
280fn compile_column_definition(column: &ColumnSnapshot) -> Result<String, OrmError> {
281 if let Some(computed_sql) = &column.computed_sql {
282 return Ok(format!(
283 "{} AS ({computed_sql})",
284 crate::quote_identifier(&column.name)?,
285 ));
286 }
287
288 if column.rowversion || column.sql_type == SqlServerType::RowVersion {
289 return Ok(format!(
290 "{} rowversion",
291 crate::quote_identifier(&column.name)?
292 ));
293 }
294
295 let mut definition = format!(
296 "{} {}",
297 crate::quote_identifier(&column.name)?,
298 render_sql_type(column),
299 );
300
301 if let Some(identity) = column.identity {
302 definition.push_str(&format!(
303 " IDENTITY({}, {})",
304 identity.seed, identity.increment
305 ));
306 }
307
308 definition.push_str(if column.nullable {
309 " NULL"
310 } else {
311 " NOT NULL"
312 });
313
314 if let Some(default_sql) = &column.default_sql {
315 definition.push_str(&format!(" DEFAULT {default_sql}"));
316 }
317
318 Ok(definition)
319}
320
321fn compile_alter_column_definition(column: &ColumnSnapshot) -> Result<String, OrmError> {
322 if column.computed_sql.is_some()
323 || column.rowversion
324 || column.sql_type == SqlServerType::RowVersion
325 {
326 return Err(OrmError::new(
327 "SQL Server alter column compilation does not support computed or rowversion columns in this stage",
328 ));
329 }
330
331 Ok(format!(
332 "{} {} {}",
333 crate::quote_identifier(&column.name)?,
334 render_sql_type(column),
335 if column.nullable { "NULL" } else { "NOT NULL" }
336 ))
337}
338
339fn render_sql_type(column: &ColumnSnapshot) -> String {
340 match column.sql_type {
341 SqlServerType::BigInt => "bigint".to_string(),
342 SqlServerType::Int => "int".to_string(),
343 SqlServerType::SmallInt => "smallint".to_string(),
344 SqlServerType::TinyInt => "tinyint".to_string(),
345 SqlServerType::Bit => "bit".to_string(),
346 SqlServerType::UniqueIdentifier => "uniqueidentifier".to_string(),
347 SqlServerType::Date => "date".to_string(),
348 SqlServerType::DateTime2 => "datetime2".to_string(),
349 SqlServerType::Decimal => format!(
350 "decimal({}, {})",
351 column.precision.unwrap_or(18),
352 column.scale.unwrap_or(2)
353 ),
354 SqlServerType::Float => "float".to_string(),
355 SqlServerType::Money => "money".to_string(),
356 SqlServerType::NVarChar => format!("nvarchar({})", column.max_length.unwrap_or(255)),
357 SqlServerType::VarBinary => match column.max_length {
358 Some(length) => format!("varbinary({length})"),
359 None => "varbinary(max)".to_string(),
360 },
361 SqlServerType::RowVersion => "rowversion".to_string(),
362 SqlServerType::Custom(name) => name.to_string(),
363 }
364}
365
366#[cfg(test)]
367mod tests {
368 use super::super::SqlServerCompiler;
369 use sql_orm_core::{IdentityMetadata, ReferentialAction, SqlServerType};
370 use sql_orm_migrate::{
371 AddColumn, AddForeignKey, AlterColumn, ColumnSnapshot, CreateIndex, CreateSchema,
372 CreateTable, DropColumn, DropForeignKey, DropIndex, DropSchema, DropTable,
373 ForeignKeySnapshot, IndexColumnSnapshot, IndexSnapshot, MigrationOperation, RenameColumn,
374 RenameTable, TableSnapshot,
375 };
376
377 fn customer_table() -> TableSnapshot {
378 TableSnapshot::new(
379 "customers",
380 vec![
381 ColumnSnapshot::new(
382 "id",
383 SqlServerType::BigInt,
384 false,
385 true,
386 Some(IdentityMetadata::new(1, 1)),
387 None,
388 None,
389 false,
390 false,
391 false,
392 None,
393 None,
394 None,
395 ),
396 ColumnSnapshot::new(
397 "email",
398 SqlServerType::NVarChar,
399 false,
400 false,
401 None,
402 None,
403 None,
404 false,
405 true,
406 true,
407 Some(180),
408 None,
409 None,
410 ),
411 ColumnSnapshot::new(
412 "created_at",
413 SqlServerType::DateTime2,
414 false,
415 false,
416 None,
417 Some("SYSUTCDATETIME()".to_string()),
418 None,
419 false,
420 true,
421 true,
422 None,
423 None,
424 None,
425 ),
426 ],
427 Some("pk_customers".to_string()),
428 vec!["id".to_string()],
429 vec![],
430 vec![],
431 )
432 }
433
434 #[test]
435 fn compiles_stage_seven_migration_operations_to_sql() {
436 let operations = vec![
437 MigrationOperation::CreateSchema(CreateSchema::new("sales")),
438 MigrationOperation::CreateTable(CreateTable::new("sales", customer_table())),
439 MigrationOperation::AddColumn(AddColumn::new(
440 "sales",
441 "customers",
442 ColumnSnapshot::new(
443 "version",
444 SqlServerType::RowVersion,
445 false,
446 false,
447 None,
448 None,
449 None,
450 true,
451 false,
452 false,
453 None,
454 None,
455 None,
456 ),
457 )),
458 MigrationOperation::DropColumn(DropColumn::new("sales", "customers", "phone")),
459 MigrationOperation::DropTable(DropTable::new("sales", "customers_archive")),
460 MigrationOperation::DropSchema(DropSchema::new("legacy")),
461 ];
462
463 let sql = SqlServerCompiler::compile_migration_operations(&operations).unwrap();
464
465 assert_eq!(
466 sql[0],
467 "IF SCHEMA_ID(N'sales') IS NULL EXEC(N'CREATE SCHEMA [sales]')"
468 );
469 assert!(sql[1].contains("CREATE TABLE [sales].[customers]"));
470 assert!(sql[1].contains("[id] bigint IDENTITY(1, 1) NOT NULL"));
471 assert!(sql[1].contains("CONSTRAINT [pk_customers] PRIMARY KEY ([id])"));
472 assert_eq!(
473 sql[2],
474 "ALTER TABLE [sales].[customers] ADD [version] rowversion"
475 );
476 assert_eq!(
477 sql[3],
478 "ALTER TABLE [sales].[customers] DROP COLUMN [phone]"
479 );
480 assert_eq!(sql[4], "DROP TABLE [sales].[customers_archive]");
481 assert_eq!(sql[5], "DROP SCHEMA [legacy]");
482 }
483
484 #[test]
485 fn compiles_migrations_history_table_sql() {
486 let sql = SqlServerCompiler::compile_migrations_history_table().unwrap();
487
488 assert!(sql.contains("IF OBJECT_ID(N'dbo.__sql_orm_migrations', N'U') IS NULL"));
489 assert!(sql.contains("CREATE TABLE [dbo].[__sql_orm_migrations]"));
490 assert!(sql.contains("[applied_at] datetime2 NOT NULL DEFAULT SYSUTCDATETIME()"));
491 assert!(sql.contains("[orm_version] nvarchar(50) NOT NULL"));
492 }
493
494 #[test]
495 fn rejects_unsupported_alter_column_default_changes() {
496 let operation = MigrationOperation::AlterColumn(AlterColumn::new(
497 "sales",
498 "customers",
499 ColumnSnapshot::new(
500 "email",
501 SqlServerType::NVarChar,
502 false,
503 false,
504 None,
505 None,
506 None,
507 false,
508 true,
509 true,
510 Some(180),
511 None,
512 None,
513 ),
514 ColumnSnapshot::new(
515 "email",
516 SqlServerType::NVarChar,
517 false,
518 false,
519 None,
520 Some("'unknown'".to_string()),
521 None,
522 false,
523 true,
524 true,
525 Some(180),
526 None,
527 None,
528 ),
529 ));
530
531 let error = SqlServerCompiler::compile_migration_operations(&[operation]).unwrap_err();
532
533 assert_eq!(
534 error.message(),
535 "SQL Server alter column compilation only supports type and nullability changes in this stage"
536 );
537 }
538
539 #[test]
540 fn compiles_basic_alter_column_type_and_nullability_change() {
541 let operation = MigrationOperation::AlterColumn(AlterColumn::new(
542 "sales",
543 "customers",
544 ColumnSnapshot::new(
545 "email",
546 SqlServerType::NVarChar,
547 false,
548 false,
549 None,
550 None,
551 None,
552 false,
553 true,
554 true,
555 Some(180),
556 None,
557 None,
558 ),
559 ColumnSnapshot::new(
560 "email",
561 SqlServerType::NVarChar,
562 true,
563 false,
564 None,
565 None,
566 None,
567 false,
568 true,
569 true,
570 Some(255),
571 None,
572 None,
573 ),
574 ));
575
576 let sql = SqlServerCompiler::compile_migration_operations(&[operation]).unwrap();
577
578 assert_eq!(
579 sql[0],
580 "ALTER TABLE [sales].[customers] ALTER COLUMN [email] nvarchar(255) NULL"
581 );
582 }
583
584 #[test]
585 fn compiles_rename_column_to_sp_rename() {
586 let operation = MigrationOperation::RenameColumn(RenameColumn::new(
587 "sales",
588 "customers",
589 "email",
590 "email_address",
591 ));
592
593 let sql = SqlServerCompiler::compile_migration_operations(&[operation]).unwrap();
594
595 assert_eq!(
596 sql[0],
597 "EXEC sp_rename N'[sales].[customers].[email]', N'email_address', N'COLUMN'"
598 );
599 }
600
601 #[test]
602 fn compiles_rename_table_to_sp_rename() {
603 let operation =
604 MigrationOperation::RenameTable(RenameTable::new("sales", "customers", "clients"));
605
606 let sql = SqlServerCompiler::compile_migration_operations(&[operation]).unwrap();
607
608 assert_eq!(
609 sql[0],
610 "EXEC sp_rename N'[sales].[customers]', N'clients', N'OBJECT'"
611 );
612 }
613
614 #[test]
615 fn compiles_computed_column_in_create_and_add_column_definitions() {
616 let operations = vec![
617 MigrationOperation::CreateTable(CreateTable::new(
618 "sales",
619 TableSnapshot::new(
620 "order_lines",
621 vec![ColumnSnapshot::new(
622 "line_total",
623 SqlServerType::Decimal,
624 false,
625 false,
626 None,
627 None,
628 Some("[unit_price] * [quantity]".to_string()),
629 false,
630 false,
631 false,
632 None,
633 Some(18),
634 Some(2),
635 )],
636 None,
637 vec![],
638 vec![],
639 vec![],
640 ),
641 )),
642 MigrationOperation::AddColumn(AddColumn::new(
643 "sales",
644 "order_lines",
645 ColumnSnapshot::new(
646 "discounted_total",
647 SqlServerType::Decimal,
648 false,
649 false,
650 None,
651 None,
652 Some("[line_total] * (1 - [discount])".to_string()),
653 false,
654 false,
655 false,
656 None,
657 Some(18),
658 Some(2),
659 ),
660 )),
661 ];
662
663 let sql = SqlServerCompiler::compile_migration_operations(&operations).unwrap();
664
665 assert_eq!(
666 sql[0],
667 "CREATE TABLE [sales].[order_lines] (\n [line_total] AS ([unit_price] * [quantity])\n)"
668 );
669 assert_eq!(
670 sql[1],
671 "ALTER TABLE [sales].[order_lines] ADD [discounted_total] AS ([line_total] * (1 - [discount]))"
672 );
673 }
674
675 #[test]
676 fn rejects_alter_column_for_computed_column_changes() {
677 let operation = MigrationOperation::AlterColumn(AlterColumn::new(
678 "sales",
679 "order_lines",
680 ColumnSnapshot::new(
681 "line_total",
682 SqlServerType::Decimal,
683 false,
684 false,
685 None,
686 None,
687 Some("[unit_price] * [quantity]".to_string()),
688 false,
689 false,
690 false,
691 None,
692 Some(18),
693 Some(2),
694 ),
695 ColumnSnapshot::new(
696 "line_total",
697 SqlServerType::Decimal,
698 false,
699 false,
700 None,
701 None,
702 Some("[unit_price] * [quantity] * (1 - [discount])".to_string()),
703 false,
704 false,
705 false,
706 None,
707 Some(18),
708 Some(2),
709 ),
710 ));
711
712 let error = SqlServerCompiler::compile_migration_operations(&[operation]).unwrap_err();
713
714 assert_eq!(
715 error.message(),
716 "SQL Server alter column compilation only supports type and nullability changes in this stage"
717 );
718 }
719
720 #[test]
721 fn compiles_foreign_key_migration_operations_to_sql() {
722 let operations = vec![
723 MigrationOperation::AddForeignKey(AddForeignKey::new(
724 "sales",
725 "orders",
726 ForeignKeySnapshot::new(
727 "fk_orders_customer_id_customers",
728 vec!["customer_id".to_string()],
729 "sales",
730 "customers",
731 vec!["id".to_string()],
732 ReferentialAction::Cascade,
733 ReferentialAction::SetNull,
734 ),
735 )),
736 MigrationOperation::DropForeignKey(DropForeignKey::new(
737 "sales",
738 "orders",
739 "fk_orders_customer_id_customers",
740 )),
741 ];
742
743 let sql = SqlServerCompiler::compile_migration_operations(&operations).unwrap();
744
745 assert_eq!(
746 sql[0],
747 "ALTER TABLE [sales].[orders] ADD CONSTRAINT [fk_orders_customer_id_customers] FOREIGN KEY ([customer_id]) REFERENCES [sales].[customers] ([id]) ON DELETE CASCADE ON UPDATE SET NULL"
748 );
749 assert_eq!(
750 sql[1],
751 "ALTER TABLE [sales].[orders] DROP CONSTRAINT [fk_orders_customer_id_customers]"
752 );
753 }
754
755 #[test]
756 fn compiles_foreign_key_no_action_clauses_explicitly() {
757 let operation = MigrationOperation::AddForeignKey(AddForeignKey::new(
758 "sales",
759 "orders",
760 ForeignKeySnapshot::new(
761 "fk_orders_customer_id_customers",
762 vec!["customer_id".to_string()],
763 "sales",
764 "customers",
765 vec!["id".to_string()],
766 ReferentialAction::NoAction,
767 ReferentialAction::NoAction,
768 ),
769 ));
770
771 let sql = SqlServerCompiler::compile_migration_operations(&[operation]).unwrap();
772
773 assert_eq!(
774 sql[0],
775 "ALTER TABLE [sales].[orders] ADD CONSTRAINT [fk_orders_customer_id_customers] FOREIGN KEY ([customer_id]) REFERENCES [sales].[customers] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION"
776 );
777 }
778
779 #[test]
780 fn compiles_foreign_key_set_default_and_composite_columns_to_sql() {
781 let operation = MigrationOperation::AddForeignKey(AddForeignKey::new(
782 "sales",
783 "order_allocations",
784 ForeignKeySnapshot::new(
785 "fk_order_allocations_customer_branch_customers",
786 vec!["customer_id".to_string(), "branch_id".to_string()],
787 "sales",
788 "customers",
789 vec!["id".to_string(), "branch_id".to_string()],
790 ReferentialAction::SetDefault,
791 ReferentialAction::SetDefault,
792 ),
793 ));
794
795 let sql = SqlServerCompiler::compile_migration_operations(&[operation]).unwrap();
796
797 assert_eq!(
798 sql[0],
799 "ALTER TABLE [sales].[order_allocations] ADD CONSTRAINT [fk_order_allocations_customer_branch_customers] FOREIGN KEY ([customer_id], [branch_id]) REFERENCES [sales].[customers] ([id], [branch_id]) ON DELETE SET DEFAULT ON UPDATE SET DEFAULT"
800 );
801 }
802
803 #[test]
804 fn rejects_foreign_key_with_mismatched_column_cardinality() {
805 let operation = MigrationOperation::AddForeignKey(AddForeignKey::new(
806 "sales",
807 "orders",
808 ForeignKeySnapshot::new(
809 "fk_orders_customer_branch_customers",
810 vec!["customer_id".to_string()],
811 "sales",
812 "customers",
813 vec!["id".to_string(), "branch_id".to_string()],
814 ReferentialAction::NoAction,
815 ReferentialAction::NoAction,
816 ),
817 ));
818
819 let error = SqlServerCompiler::compile_migration_operations(&[operation]).unwrap_err();
820
821 assert_eq!(
822 error.message(),
823 "SQL Server foreign key migration compilation requires the same number of local and referenced columns"
824 );
825 }
826
827 #[test]
828 fn compiles_index_migration_operations_to_sql() {
829 let operations = vec![
830 MigrationOperation::CreateIndex(CreateIndex::new(
831 "sales",
832 "orders",
833 IndexSnapshot::new(
834 "ix_orders_customer_id_total_cents",
835 vec![
836 IndexColumnSnapshot::asc("customer_id"),
837 IndexColumnSnapshot::desc("total_cents"),
838 ],
839 false,
840 ),
841 )),
842 MigrationOperation::CreateIndex(CreateIndex::new(
843 "sales",
844 "orders",
845 IndexSnapshot::new(
846 "ux_orders_external_id",
847 vec![IndexColumnSnapshot::asc("external_id")],
848 true,
849 ),
850 )),
851 MigrationOperation::DropIndex(DropIndex::new(
852 "sales",
853 "orders",
854 "ix_orders_customer_id_total_cents",
855 )),
856 ];
857
858 let sql = SqlServerCompiler::compile_migration_operations(&operations).unwrap();
859
860 assert_eq!(
861 sql[0],
862 "CREATE INDEX [ix_orders_customer_id_total_cents] ON [sales].[orders] ([customer_id] ASC, [total_cents] DESC)"
863 );
864 assert_eq!(
865 sql[1],
866 "CREATE UNIQUE INDEX [ux_orders_external_id] ON [sales].[orders] ([external_id] ASC)"
867 );
868 assert_eq!(
869 sql[2],
870 "DROP INDEX [ix_orders_customer_id_total_cents] ON [sales].[orders]"
871 );
872 }
873
874 #[test]
875 fn rejects_create_index_without_columns() {
876 let operation = MigrationOperation::CreateIndex(CreateIndex::new(
877 "sales",
878 "orders",
879 IndexSnapshot::new("ix_orders_empty", vec![], false),
880 ));
881
882 let error = SqlServerCompiler::compile_migration_operations(&[operation]).unwrap_err();
883
884 assert_eq!(
885 error.message(),
886 "SQL Server index migration compilation requires at least one indexed column"
887 );
888 }
889}