1use std::collections::HashSet;
2use std::io::Write;
3use std::rc::Rc;
4
5use schema_diff::{ChangeSet, SchemaChange};
6use schema_model::builder::TableBuilder;
7use schema_model::model::column::Column;
8use schema_model::model::column_type::ColumnType;
9use schema_model::model::database_model::DatabaseModel;
10use schema_model::model::key::Key;
11use schema_model::model::relation::Relation;
12use schema_model::model::table::Table;
13use schema_model::model::types::{BooleanMode, DatabaseType, KeyType, RelationType};
14use schema_model::naming::{foreign_key_name, index_name, primary_key_name, unique_key_name};
15use schema_sql_generator::common::column_type_generator::ColumnTypeGenerator;
16use schema_sql_generator::common::generator_context::{BufferSink, GeneratorContext};
17use schema_sql_generator::common::table_generator::TableGenerator;
18use schema_sql_generator::common::trigger_generator::TriggerGenerator;
19use schema_sql_generator::sqlserver::sqlserver_column_type_generator::SqlServerColumnTypeGenerator;
20use schema_sql_generator::sqlserver::sqlserver_table_generator::SqlServerTableGenerator;
21use schema_sql_generator::sqlserver::sqlserver_trigger_generator::SqlServerTriggerGenerator;
22
23use crate::check_constraint;
24use crate::default_constraint;
25use crate::error::MigrationGeneratorError;
26use crate::migration_generator::MigrationGenerator;
27
28#[derive(Default)]
29pub struct SqlServerMigrationGenerator {
30 pub target_sqlserver_version: u32,
35}
36
37impl SqlServerMigrationGenerator {
38 pub fn new(target_sqlserver_version: u32) -> Self {
39 Self {
40 target_sqlserver_version,
41 }
42 }
43}
44
45impl MigrationGenerator for SqlServerMigrationGenerator {
46 fn generate(
47 &self,
48 change_set: &ChangeSet,
49 database_model: &DatabaseModel,
50 writer: &mut dyn Write,
51 ) -> Result<(), MigrationGeneratorError> {
52 let context = GeneratorContext::for_model_with_target_version(
53 Rc::new(database_model.clone()),
54 DatabaseType::SqlServer,
55 self.target_sqlserver_version,
56 );
57 let type_generator = SqlServerColumnTypeGenerator::new(context.clone());
58 let (trigger_context, trigger_buffer) =
59 GeneratorContext::for_model_with_buffer_and_target_version(
60 Rc::new(database_model.clone()),
61 DatabaseType::SqlServer,
62 self.target_sqlserver_version,
63 );
64 let trigger_generator = SqlServerTriggerGenerator::new(trigger_context);
65 let (table_context, table_buffer) =
66 GeneratorContext::for_model_with_buffer_and_target_version(
67 Rc::new(database_model.clone()),
68 DatabaseType::SqlServer,
69 self.target_sqlserver_version,
70 );
71 let table_generator = SqlServerTableGenerator::new(table_context);
72 let dummy_table = TableBuilder::new(None::<&str>, "_").build();
73 let mut triggers_regenerated: HashSet<String> = HashSet::new();
74
75 let mut pending_keys: Vec<(&Key, usize, &str)> = Vec::new();
79 let mut pending_relations: Vec<(usize, &Relation)> = Vec::new();
80
81 for change in change_set.changes() {
82 if !matches!(change, SchemaChange::ModifyColumn { .. }) {
83 write_pending_constraints(writer, &mut pending_keys, &mut pending_relations)?;
84 }
85 match change {
86 SchemaChange::AddTable { table } => {
87 write_add_table(&table_generator, &table_buffer, table, writer)?;
88 }
89 SchemaChange::DropTable { table_name } => {
90 writeln!(
91 writer,
92 "if object_id('{}', 'U') is not null drop table {};",
93 table_name, table_name
94 )?;
95 writeln!(writer, "go")?;
96 writeln!(writer)?;
97 }
98 SchemaChange::AddColumn { table_name, column } => {
99 let type_sql =
100 format!(" {}", type_generator.column_type_sql(&dummy_table, column));
101 let default_value = default_sql(database_model, column);
102
103 if let Some(expression) = column.generated() {
104 let not_null = if column.required() { " not null" } else { "" };
107 write_guarded(
108 writer,
109 &format!(
110 "not exists (select 1 from sys.columns where object_id = object_id('{}') and name = '{}')",
111 table_name,
112 column.name()
113 ),
114 &format!(
115 "alter table {} add {} {}{};",
116 table_name,
117 column.name(),
118 type_generator.generated_column_sql(
119 &dummy_table,
120 column,
121 expression
122 ),
123 not_null
124 ),
125 )?;
126 } else if column.required() && default_value.is_none() {
127 write_not_null_column_with_backfill_guard(
128 writer,
129 table_name,
130 column.name(),
131 &type_sql,
132 )?;
133 } else {
134 let not_null = if column.required() {
135 " not null"
136 } else {
137 " null"
138 };
139 let default = default_value
140 .map(|d| {
141 let name =
142 default_constraint::constraint_name(table_name, column.name());
143 format!(" constraint {} default {}", name, d)
144 })
145 .unwrap_or_default();
146 write_guarded(
147 writer,
148 &format!(
149 "not exists (select 1 from sys.columns where object_id = object_id('{}') and name = '{}')",
150 table_name,
151 column.name()
152 ),
153 &format!(
154 "alter table {} add {}{}{}{};",
155 table_name,
156 column.name(),
157 type_sql,
158 not_null,
159 default
160 ),
161 )?;
162 }
163
164 write_add_check_constraint(writer, &context, table_name, column)?;
165 }
166 SchemaChange::DropColumn {
167 table_name,
168 column_name,
169 rename_candidates,
170 } => {
171 if !rename_candidates.is_empty() {
172 writeln!(
173 writer,
174 "-- TODO: possible rename? Consider replacing the DROP + ADD below with:"
175 )?;
176 for candidate in rename_candidates {
177 writeln!(
178 writer,
179 "-- exec sp_rename '{}.{}', '{}', 'COLUMN';",
180 table_name, column_name, candidate
181 )?;
182 }
183 }
184 write_drop_default_constraint(writer, table_name, column_name)?;
185 write_drop_check_constraint(writer, table_name, column_name)?;
186 write_guarded(
187 writer,
188 &format!(
189 "exists (select 1 from sys.columns where object_id = object_id('{}') and name = '{}')",
190 table_name, column_name
191 ),
192 &format!("alter table {} drop column {};", table_name, column_name),
193 )?;
194 }
195 SchemaChange::ModifyColumn {
196 table_name,
197 old_column,
198 new_column,
199 } => {
200 if old_column.generated() != new_column.generated() {
201 write_generated_change_guard(writer, table_name, new_column.name(), |m| {
202 format!("throw 50000, '{m}', 1;")
203 })?;
204 writeln!(writer, "go")?;
205 writeln!(writer)?;
206 }
207 let table = database_model
215 .all_tables()
216 .into_iter()
217 .find(|t| t.name().eq_ignore_ascii_case(table_name));
218 let blocking_keys = table
219 .map(|t| dependent_keys(t, old_column.name()))
220 .unwrap_or_default();
221 let blocking_relations =
222 dependent_relations(database_model, table_name, old_column.name());
223
224 for (ordinal, relation) in &blocking_relations {
225 write_drop_relation(writer, relation, *ordinal)?;
226 }
227 for (key, ordinal) in &blocking_keys {
228 write_drop_key(writer, table_name, key, *ordinal)?;
229 }
230
231 write_drop_default_constraint(writer, table_name, old_column.name())?;
236 write_drop_check_constraint(writer, table_name, old_column.name())?;
237
238 let is_identity = matches!(
247 new_column.column_type(),
248 ColumnType::Sequence | ColumnType::LongSequence
249 );
250 if is_identity {
251 writeln!(
252 writer,
253 "-- NOTE: '{}' is an identity column - IDENTITY cannot be changed via ALTER COLUMN. If its underlying type needs to change too, this requires a manual column rebuild.",
254 new_column.name()
255 )?;
256 }
257 let type_sql = if is_identity {
258 match new_column.column_type() {
259 ColumnType::Sequence => " integer".to_string(),
260 ColumnType::LongSequence => " bigint".to_string(),
261 _ => unreachable!(),
262 }
263 } else {
264 format!(
265 " {}",
266 type_generator.column_type_sql(&dummy_table, new_column)
267 )
268 };
269 let null = if new_column.required() {
270 " not null"
271 } else {
272 " null"
273 };
274 writeln!(
275 writer,
276 "alter table {} alter column {}{}{};",
277 table_name,
278 new_column.name(),
279 type_sql,
280 null
281 )?;
282 writeln!(writer, "go")?;
283 writeln!(writer)?;
284
285 write_add_default_constraint(writer, database_model, table_name, new_column)?;
286 write_add_check_constraint(writer, &context, table_name, new_column)?;
287
288 for (key, ordinal) in &blocking_keys {
289 if !pending_keys.iter().any(|(k, _, _)| std::ptr::eq(*k, *key)) {
290 pending_keys.push((key, *ordinal, table_name.as_str()));
291 }
292 }
293 for (ordinal, relation) in &blocking_relations {
294 if !pending_relations
295 .iter()
296 .any(|(_, r)| std::ptr::eq(*r, *relation))
297 {
298 pending_relations.push((*ordinal, relation));
299 }
300 }
301 }
302 SchemaChange::AddKey {
303 table_name,
304 key,
305 ordinal,
306 } => {
307 write_add_key(writer, table_name, key, *ordinal)?;
308 }
309 SchemaChange::DropKey {
310 table_name,
311 key,
312 ordinal,
313 } => {
314 write_drop_key(writer, table_name, key, *ordinal)?;
315 }
316 SchemaChange::AddConstraint {
317 table_name,
318 constraint,
319 } if constraint.applies_to(DatabaseType::SqlServer) => {
320 write_guarded(
325 writer,
326 &format!(
327 "not exists (select 1 from sys.check_constraints where name = '{}')",
328 constraint.name()
329 ),
330 &format!(
331 "alter table {} add constraint {} {};",
332 table_name,
333 constraint.name(),
334 constraint.sql()
335 ),
336 )?;
337 }
338 SchemaChange::AddConstraint { .. } => {}
339 SchemaChange::DropConstraint {
340 table_name,
341 constraint_name,
342 database_type,
343 } if database_type.is_none_or(|d| d == DatabaseType::SqlServer) => {
344 write_guarded(
345 writer,
346 &format!(
347 "exists (select 1 from sys.objects where name = '{}' and parent_object_id = object_id('{}'))",
348 constraint_name, table_name
349 ),
350 &format!(
351 "alter table {} drop constraint {};",
352 table_name, constraint_name
353 ),
354 )?;
355 }
356 SchemaChange::DropConstraint { .. } => {}
357 SchemaChange::AddRelation { relation, ordinal } => {
358 write_add_relation(writer, relation, *ordinal)?;
359 }
360 SchemaChange::DropRelation { relation, ordinal } => {
361 write_drop_relation(writer, relation, *ordinal)?;
362 }
363 SchemaChange::AddView { view }
364 if view.database_type().is_none()
365 || view.database_type() == Some(DatabaseType::SqlServer) =>
366 {
367 writeln!(writer, "create or alter view {} as", view.name())?;
368 writeln!(writer, "{};", view.sql())?;
369 writeln!(writer, "go")?;
370 writeln!(writer)?;
371 }
372 SchemaChange::AddView { .. } => {}
373 SchemaChange::DropView {
374 view_name,
375 database_type,
376 } if database_type.is_none() || *database_type == Some(DatabaseType::SqlServer) => {
377 writeln!(
378 writer,
379 "if object_id('{}', 'V') is not null drop view {};",
380 view_name, view_name
381 )?;
382 writeln!(writer, "go")?;
383 writeln!(writer)?;
384 }
385 SchemaChange::DropView { .. } => {}
386 SchemaChange::AddEnumType { .. } | SchemaChange::DropEnumType { .. } => {}
389 SchemaChange::ModifyEnumType {
390 old_enum_type,
391 new_enum_type,
392 } => {
393 write_removed_enum_value_guards(writer, old_enum_type, new_enum_type)?;
394 for (table_name, column) in
395 columns_using_enum(database_model, new_enum_type.name())
396 {
397 write_drop_check_constraint(writer, &table_name, column.name())?;
398 write_add_check_constraint(writer, &context, &table_name, column)?;
399 }
400 }
401 SchemaChange::AddFunction { function }
402 if function.database_type() == DatabaseType::SqlServer =>
403 {
404 writeln!(writer, "{}", function.sql())?;
405 writeln!(writer, "go")?;
406 writeln!(writer)?;
407 }
408 SchemaChange::AddFunction { .. } => {}
409 SchemaChange::DropFunction {
410 function_name,
411 database_type,
412 } if *database_type == DatabaseType::SqlServer => {
413 writeln!(writer, "drop function if exists {};", function_name)?;
414 writeln!(writer, "go")?;
415 writeln!(writer)?;
416 }
417 SchemaChange::DropFunction { .. } => {}
418 SchemaChange::AddProcedure { procedure }
419 if procedure.database_type() == DatabaseType::SqlServer =>
420 {
421 writeln!(writer, "{}", procedure.sql())?;
422 writeln!(writer, "go")?;
423 writeln!(writer)?;
424 }
425 SchemaChange::AddProcedure { .. } => {}
426 SchemaChange::DropProcedure {
427 procedure_name,
428 database_type,
429 } if *database_type == DatabaseType::SqlServer => {
430 writeln!(writer, "drop procedure if exists {};", procedure_name)?;
431 writeln!(writer, "go")?;
432 writeln!(writer)?;
433 }
434 SchemaChange::DropProcedure { .. } => {}
435 SchemaChange::AddOtherSql { other_sql }
436 if other_sql.database_type() == DatabaseType::SqlServer =>
437 {
438 writeln!(writer, "{}", other_sql.sql())?;
439 writeln!(writer, "go")?;
440 writeln!(writer)?;
441 }
442 SchemaChange::AddOtherSql { .. } => {}
443 SchemaChange::DropOtherSql { other_sql }
444 if other_sql.database_type() == DatabaseType::SqlServer =>
445 {
446 writeln!(
447 writer,
448 "-- TODO: other_sql entry removed, review manually: {}",
449 other_sql.sql()
450 )?;
451 writeln!(writer)?;
452 }
453 SchemaChange::DropOtherSql { .. } => {}
454 SchemaChange::AddTrigger {
455 table_name,
456 trigger,
457 } if trigger.database_type() == DatabaseType::SqlServer => {
458 regenerate_triggers(
459 &trigger_generator,
460 &trigger_buffer,
461 database_model,
462 table_name,
463 &mut triggers_regenerated,
464 &mut *writer,
465 )?;
466 }
467 SchemaChange::AddTrigger { .. } => {}
468 SchemaChange::DropTrigger {
469 table_name,
470 trigger,
471 } if trigger.database_type() == DatabaseType::SqlServer => {
472 regenerate_triggers(
473 &trigger_generator,
474 &trigger_buffer,
475 database_model,
476 table_name,
477 &mut triggers_regenerated,
478 &mut *writer,
479 )?;
480 }
481 SchemaChange::DropTrigger { .. } => {}
482 SchemaChange::AddInitialData { initial_data, .. }
483 if initial_data.database_type().is_none()
484 || initial_data.database_type() == Some(DatabaseType::SqlServer) =>
485 {
486 writeln!(writer, "{}", initial_data.sql())?;
487 writeln!(writer, "go")?;
488 writeln!(writer)?;
489 }
490 SchemaChange::AddInitialData { .. } => {}
491 SchemaChange::DropInitialData { initial_data, .. }
492 if initial_data.database_type().is_none()
493 || initial_data.database_type() == Some(DatabaseType::SqlServer) =>
494 {
495 writeln!(
496 writer,
497 "-- TODO: initial_data entry removed, review manually: {}",
498 initial_data.sql()
499 )?;
500 writeln!(writer)?;
501 }
502 SchemaChange::DropInitialData { .. } => {}
503 }
504 }
505 write_pending_constraints(writer, &mut pending_keys, &mut pending_relations)?;
506 Ok(())
507 }
508}
509
510fn write_pending_constraints(
513 writer: &mut dyn Write,
514 keys: &mut Vec<(&Key, usize, &str)>,
515 relations: &mut Vec<(usize, &Relation)>,
516) -> Result<(), MigrationGeneratorError> {
517 for (key, ordinal, table_name) in keys.drain(..) {
518 write_add_key(writer, table_name, key, ordinal)?;
519 }
520 for (ordinal, relation) in relations.drain(..) {
521 write_add_relation(writer, relation, ordinal)?;
522 }
523 Ok(())
524}
525
526fn dependent_keys<'a>(table: &'a Table, column_name: &str) -> Vec<(&'a Key, usize)> {
533 let mut found = Vec::new();
534
535 let mut unique_ordinal = 0;
536 for key in table.keys() {
537 if key.key_type() == KeyType::Unique {
538 unique_ordinal += 1;
539 }
540 if key.contains_column(column_name) {
541 found.push((key, unique_ordinal));
542 }
543 }
544
545 let mut index_ordinal = 0;
546 for key in table.indexes() {
547 if key.is_index() {
548 index_ordinal += 1;
549 }
550 if key.contains_column(column_name) {
551 found.push((key, index_ordinal));
552 }
553 }
554
555 found
556}
557
558fn dependent_relations<'a>(
566 database_model: &'a DatabaseModel,
567 table_name: &str,
568 column_name: &str,
569) -> Vec<(usize, &'a Relation)> {
570 let mut found = Vec::new();
571
572 for owning_table in database_model.all_tables() {
573 for (index, relation) in owning_table.relations().iter().enumerate() {
574 if !relation.creates_foreign_key_constraint() {
575 continue;
576 }
577 let ordinal = index + 1;
578 let from_matches = owning_table.name().eq_ignore_ascii_case(table_name)
579 && relation
580 .column_pairs()
581 .iter()
582 .any(|(from, _)| from.eq_ignore_ascii_case(column_name));
583 let to_matches = database_model
584 .find_table_by_qualified_name_checked(relation.to_table_name())
585 .is_some_and(|t| t.name().eq_ignore_ascii_case(table_name))
586 && relation
587 .column_pairs()
588 .iter()
589 .any(|(_, to)| to.eq_ignore_ascii_case(column_name));
590 if from_matches || to_matches {
591 found.push((ordinal, relation));
592 }
593 }
594 }
595
596 found
597}
598
599fn columns_using_enum<'a>(
604 database_model: &'a DatabaseModel,
605 enum_name: &str,
606) -> Vec<(String, &'a Column)> {
607 database_model
608 .all_tables()
609 .into_iter()
610 .flat_map(|table| {
611 table
612 .columns()
613 .iter()
614 .filter(|c| {
615 c.enum_type()
616 .is_some_and(|t| t.eq_ignore_ascii_case(enum_name))
617 })
618 .map(|c| (table.name().to_string(), c))
619 })
620 .collect()
621}
622
623fn write_removed_enum_value_guards(
632 writer: &mut dyn Write,
633 old_enum_type: &schema_model::model::enum_type::EnumType,
634 new_enum_type: &schema_model::model::enum_type::EnumType,
635) -> Result<(), MigrationGeneratorError> {
636 let name = new_enum_type.name();
637
638 for value in old_enum_type.values() {
639 if !new_enum_type
640 .values()
641 .iter()
642 .any(|v| v.name() == value.name())
643 {
644 writeln!(
645 writer,
646 "-- WARNING: enum value '{}' was removed from '{}'.",
647 value.name(),
648 name
649 )?;
650 writeln!(
651 writer,
652 "-- 1. If rows still use '{}', add UPDATE statements above this line to migrate them to a valid value, then delete the block below, OR",
653 value.name()
654 )?;
655 writeln!(
656 writer,
657 "-- 2. If no rows use '{}', delete the block below to confirm that.",
658 value.name()
659 )?;
660 writeln!(
661 writer,
662 "throw 50000, 'Migration halted: enum value ''{}'' was removed from {} - see comment above.', 1;",
663 value.name().replace('\'', "''"),
664 name
665 )?;
666 writeln!(writer, "go")?;
667 writeln!(writer)?;
668 }
669 }
670
671 Ok(())
672}
673
674fn write_not_null_column_with_backfill_guard(
682 writer: &mut dyn Write,
683 table_name: &str,
684 column_name: &str,
685 type_sql: &str,
686) -> Result<(), MigrationGeneratorError> {
687 write_guarded(
688 writer,
689 &format!(
690 "not exists (select 1 from sys.columns where object_id = object_id('{}') and name = '{}')",
691 table_name, column_name
692 ),
693 &format!(
694 "alter table {} add {}{} null;",
695 table_name, column_name, type_sql
696 ),
697 )?;
698 writeln!(
699 writer,
700 "-- WARNING: '{}' is being made NOT NULL with no default - existing rows have no value to backfill it with automatically.",
701 column_name
702 )?;
703 writeln!(
704 writer,
705 "-- 1. If '{}' has rows, add UPDATE statements above this line to give '{}' a value, then delete the block below, OR",
706 table_name, column_name
707 )?;
708 writeln!(
709 writer,
710 "-- 2. If '{}' is empty, delete the block below to confirm that.",
711 table_name
712 )?;
713 writeln!(
714 writer,
715 "throw 50000, 'Migration halted: {} has no value for {} - see comment above.', 1;",
716 table_name, column_name
717 )?;
718 writeln!(writer, "go")?;
719 writeln!(writer)?;
720 writeln!(
721 writer,
722 "alter table {} alter column {}{} not null;",
723 table_name, column_name, type_sql
724 )?;
725 writeln!(writer, "go")?;
726 writeln!(writer)?;
727 Ok(())
728}
729
730fn default_sql(database_model: &DatabaseModel, column: &Column) -> Option<String> {
736 let raw = column.default_constraint()?;
737 if column.column_type() == ColumnType::Boolean {
738 return boolean_default_literal(database_model.boolean_mode(), raw);
739 }
740 Some(raw.to_string())
741}
742
743fn boolean_default_literal(boolean_mode: BooleanMode, raw: &str) -> Option<String> {
744 let value = match schema_model::model::column::parse_boolean_default(raw) {
745 Ok(Some(value)) => value,
746 Ok(None) => return None,
747 Err(()) => false,
748 };
749 Some(
750 match boolean_mode {
751 BooleanMode::Native => {
752 if value {
753 "1"
754 } else {
755 "0"
756 }
757 }
758 BooleanMode::YesNo => {
759 if value {
760 "'Yes'"
761 } else {
762 "'No'"
763 }
764 }
765 BooleanMode::YN => {
766 if value {
767 "'Y'"
768 } else {
769 "'N'"
770 }
771 }
772 }
773 .to_string(),
774 )
775}
776
777fn write_drop_default_constraint(
782 writer: &mut dyn Write,
783 table_name: &str,
784 column_name: &str,
785) -> Result<(), MigrationGeneratorError> {
786 let name = default_constraint::constraint_name(table_name, column_name);
787 write_guarded(
788 writer,
789 &format!(
790 "exists (select 1 from sys.default_constraints where name = '{}')",
791 name
792 ),
793 &format!("alter table {} drop constraint {};", table_name, name),
794 )
795}
796
797fn write_drop_check_constraint(
798 writer: &mut dyn Write,
799 table_name: &str,
800 column_name: &str,
801) -> Result<(), MigrationGeneratorError> {
802 let name = check_constraint::constraint_name(table_name, column_name);
803 write_guarded(
804 writer,
805 &format!(
806 "exists (select 1 from sys.check_constraints where name = '{}')",
807 name
808 ),
809 &format!("alter table {} drop constraint {};", table_name, name),
810 )
811}
812
813fn write_add_default_constraint(
817 writer: &mut dyn Write,
818 database_model: &DatabaseModel,
819 table_name: &str,
820 column: &Column,
821) -> Result<(), MigrationGeneratorError> {
822 if let Some(default) = default_sql(database_model, column) {
823 let name = default_constraint::constraint_name(table_name, column.name());
824 write_guarded(
825 writer,
826 &format!(
827 "not exists (select 1 from sys.default_constraints where name = '{}')",
828 name
829 ),
830 &format!(
831 "alter table {} add constraint {} default {} for {};",
832 table_name,
833 name,
834 default,
835 column.name()
836 ),
837 )?;
838 }
839 Ok(())
840}
841
842fn write_add_check_constraint(
843 writer: &mut dyn Write,
844 context: &GeneratorContext,
845 table_name: &str,
846 column: &Column,
847) -> Result<(), MigrationGeneratorError> {
848 if let Some(check_sql) = check_constraint::check_constraint_sql(context, column) {
849 let name = check_constraint::constraint_name(table_name, column.name());
850 write_guarded(
851 writer,
852 &format!(
853 "not exists (select 1 from sys.check_constraints where name = '{}')",
854 name
855 ),
856 &format!(
857 "alter table {} add constraint {} {};",
858 table_name, name, check_sql
859 ),
860 )?;
861 }
862 Ok(())
863}
864
865fn write_add_table(
888 table_generator: &SqlServerTableGenerator,
889 table_buffer: &BufferSink,
890 table: &Table,
891 writer: &mut dyn Write,
892) -> Result<(), MigrationGeneratorError> {
893 let qualified_name = table.fully_qualified_table_name(DatabaseType::SqlServer);
894
895 table_generator.output_table_definition(table);
896 let body = table_buffer.take();
897 let create_sql = format!(
898 "create table {} (\n{}\n);",
899 qualified_name,
900 body.trim_end_matches('\n')
901 );
902
903 write_guarded(
908 writer,
909 &format!("object_id('{}', 'U') is null", qualified_name),
910 &create_sql,
911 )?;
912
913 let mut index_ordinal = 0;
923 for index in table.indexes() {
924 if index.is_index() {
925 index_ordinal += 1;
926 }
927 let idx_name = index_name(DatabaseType::SqlServer, table.name(), index_ordinal);
928 let unique = if index.is_unique() { "unique " } else { "" };
929 let cols: String = index
930 .columns()
931 .iter()
932 .map(|c| c.name())
933 .collect::<Vec<_>>()
934 .join(", ");
935 let where_clause = index
936 .filter()
937 .map(|f| format!(" where {}", f))
938 .unwrap_or_default();
939 write_guarded(
940 writer,
941 &format!(
942 "not exists (select 1 from sys.indexes where name = '{}')",
943 idx_name
944 ),
945 &format!(
946 "create {}index {} on {} ({}){};",
947 unique, idx_name, qualified_name, cols, where_clause
948 ),
949 )?;
950 }
951
952 table_generator.output_initial_data(table);
953 write!(writer, "{}", table_buffer.take())?;
954 Ok(())
955}
956
957fn write_guarded(
958 writer: &mut dyn Write,
959 condition_sql: &str,
960 body_sql: &str,
961) -> Result<(), MigrationGeneratorError> {
962 writeln!(writer, "if {} begin", condition_sql)?;
963 writeln!(writer, " {}", body_sql)?;
964 writeln!(writer, "end")?;
965 writeln!(writer, "go")?;
966 writeln!(writer)?;
967 Ok(())
968}
969
970fn write_add_key(
971 writer: &mut dyn Write,
972 table_name: &str,
973 key: &Key,
974 ordinal: usize,
975) -> Result<(), MigrationGeneratorError> {
976 let cols: String = key
977 .columns()
978 .iter()
979 .map(|c| c.name())
980 .collect::<Vec<_>>()
981 .join(", ");
982 match key.key_type() {
983 KeyType::Primary => {
984 let constraint_name = primary_key_name(DatabaseType::SqlServer, table_name);
985 write_guarded(
986 writer,
987 &format!(
988 "not exists (select 1 from sys.key_constraints where name = '{}' and parent_object_id = object_id('{}'))",
989 constraint_name, table_name
990 ),
991 &format!(
992 "alter table {} add constraint {} primary key ({});",
993 table_name, constraint_name, cols
994 ),
995 )?;
996 }
997 KeyType::Unique => {
998 let constraint_name = unique_key_name(DatabaseType::SqlServer, table_name, ordinal);
999 write_guarded(
1000 writer,
1001 &format!(
1002 "not exists (select 1 from sys.key_constraints where name = '{}' and parent_object_id = object_id('{}'))",
1003 constraint_name, table_name
1004 ),
1005 &format!(
1006 "alter table {} add constraint {} unique ({});",
1007 table_name, constraint_name, cols
1008 ),
1009 )?;
1010 }
1011 KeyType::Index => {
1012 let idx_name = index_name(DatabaseType::SqlServer, table_name, ordinal);
1013 let unique = if key.is_unique() { "unique " } else { "" };
1014 let where_clause = key
1015 .filter()
1016 .map(|f| format!(" where {}", f))
1017 .unwrap_or_default();
1018 write_guarded(
1019 writer,
1020 &format!(
1021 "not exists (select 1 from sys.indexes where name = '{}')",
1022 idx_name
1023 ),
1024 &format!(
1025 "create {}index {} on {} ({}){};",
1026 unique, idx_name, table_name, cols, where_clause
1027 ),
1028 )?;
1029 }
1030 }
1031 Ok(())
1032}
1033
1034fn write_drop_key(
1035 writer: &mut dyn Write,
1036 table_name: &str,
1037 key: &Key,
1038 ordinal: usize,
1039) -> Result<(), MigrationGeneratorError> {
1040 match key.key_type() {
1041 KeyType::Primary => {
1042 let constraint_name = primary_key_name(DatabaseType::SqlServer, table_name);
1043 write_guarded(
1044 writer,
1045 &format!(
1046 "exists (select 1 from sys.key_constraints where name = '{}' and parent_object_id = object_id('{}'))",
1047 constraint_name, table_name
1048 ),
1049 &format!(
1050 "alter table {} drop constraint {};",
1051 table_name, constraint_name
1052 ),
1053 )?;
1054 }
1055 KeyType::Unique => {
1056 let constraint_name = unique_key_name(DatabaseType::SqlServer, table_name, ordinal);
1057 writeln!(
1058 writer,
1059 "if exists (select 1 from sys.key_constraints where name = '{}' and parent_object_id = object_id('{}')) alter table {} drop constraint {};",
1060 constraint_name, table_name, table_name, constraint_name
1061 )?;
1062 writeln!(writer, "go")?;
1063 writeln!(writer)?;
1064 }
1065 KeyType::Index => {
1066 let idx_name = index_name(DatabaseType::SqlServer, table_name, ordinal);
1067 writeln!(
1068 writer,
1069 "if exists (select 1 from sys.indexes where name = '{}') drop index {} on {};",
1070 idx_name, idx_name, table_name
1071 )?;
1072 writeln!(writer, "go")?;
1073 writeln!(writer)?;
1074 }
1075 }
1076 Ok(())
1077}
1078
1079fn write_drop_relation(
1080 writer: &mut dyn Write,
1081 relation: &Relation,
1082 ordinal: usize,
1083) -> Result<(), MigrationGeneratorError> {
1084 if !relation.creates_foreign_key_constraint() {
1085 return Ok(());
1086 }
1087 let fk_name = foreign_key_name(DatabaseType::SqlServer, relation.from_table_name(), ordinal);
1088 write_guarded(
1089 writer,
1090 &format!(
1091 "exists (select 1 from sys.foreign_keys where name = '{}')",
1092 fk_name
1093 ),
1094 &format!(
1095 "alter table {} drop constraint {};",
1096 relation.from_table_name(),
1097 fk_name
1098 ),
1099 )
1100}
1101
1102fn write_add_relation(
1103 writer: &mut dyn Write,
1104 relation: &Relation,
1105 ordinal: usize,
1106) -> Result<(), MigrationGeneratorError> {
1107 if !relation.creates_foreign_key_constraint() {
1108 return Ok(());
1109 }
1110 let fk_name = foreign_key_name(DatabaseType::SqlServer, relation.from_table_name(), ordinal);
1111 let on_delete = match relation.relation_type() {
1112 RelationType::Cascade => " on delete cascade",
1113 RelationType::SetNull => " on delete set null",
1114 RelationType::DoNothing => " on delete no action",
1115 RelationType::Enforce => "",
1116 };
1117 let from_columns = relation
1118 .column_pairs()
1119 .iter()
1120 .map(|(from, _)| from.as_str())
1121 .collect::<Vec<_>>()
1122 .join(", ");
1123 let to_columns = relation
1124 .column_pairs()
1125 .iter()
1126 .map(|(_, to)| to.as_str())
1127 .collect::<Vec<_>>()
1128 .join(", ");
1129 write_guarded(
1130 writer,
1131 &format!(
1132 "not exists (select 1 from sys.foreign_keys where name = '{}')",
1133 fk_name
1134 ),
1135 &format!(
1136 "alter table {} add constraint {} foreign key ({}) references {}({}){};",
1137 relation.from_table_name(),
1138 fk_name,
1139 from_columns,
1140 relation.to_table_name(),
1141 to_columns,
1142 on_delete
1143 ),
1144 )?;
1145 Ok(())
1146}
1147
1148fn regenerate_triggers(
1152 trigger_generator: &SqlServerTriggerGenerator,
1153 trigger_buffer: &schema_sql_generator::common::generator_context::BufferSink,
1154 database_model: &DatabaseModel,
1155 table_name: &str,
1156 regenerated: &mut HashSet<String>,
1157 writer: &mut dyn Write,
1158) -> Result<(), MigrationGeneratorError> {
1159 if !regenerated.insert(table_name.to_string()) {
1160 return Ok(());
1161 }
1162 if let Some(table) = database_model
1163 .all_tables()
1164 .into_iter()
1165 .find(|t| t.name().eq_ignore_ascii_case(table_name))
1166 {
1167 trigger_generator.output_triggers_for_table(table);
1168 write!(writer, "{}", trigger_buffer.take())?;
1169 }
1170 Ok(())
1171}
1172
1173fn write_generated_change_guard(
1178 writer: &mut dyn Write,
1179 table_name: &str,
1180 column_name: &str,
1181 halt: impl Fn(&str) -> String,
1182) -> Result<(), MigrationGeneratorError> {
1183 writeln!(
1184 writer,
1185 "-- WARNING: the generated expression of '{}.{}' changed (or it switched between plain and generated).",
1186 table_name, column_name
1187 )?;
1188 writeln!(
1189 writer,
1190 "-- This cannot be altered in place. Drop and re-add the column manually (back up its data first if it was a plain column), then delete the statement below."
1191 )?;
1192 writeln!(
1193 writer,
1194 "{}",
1195 halt(&format!(
1196 "Migration halted: generated column {}.{} changed - see comment above.",
1197 table_name, column_name
1198 ))
1199 )?;
1200 writeln!(writer)?;
1201 Ok(())
1202}