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