Skip to main content

schema_migration_generator/sqlserver/
mod.rs

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    /// Target SQL Server product year (e.g. 2022, 2025) - threaded into every
31    /// `GeneratorContext` this generator builds so version-gated column type SQL (e.g. the
32    /// native `json` type, M6) matches what `--sqlserver-version` would produce on the create
33    /// path. `0` means "unset".
34    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        // Keys and foreign keys taken off columns by a run of consecutive `ModifyColumn`s go
76        // back on only once the run ends, so an FK spanning two modified columns is never
77        // re-added while one side still has its old type.
78        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                        // Computed columns declare no type, take no default, and are always
105                        // calculated for existing rows - nothing to backfill.
106                        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                    // SQL Server also refuses ALTER COLUMN outright while the column
208                    // participates in a PRIMARY KEY/UNIQUE constraint, a plain index, or a
209                    // foreign key (either as the FK's own column, or as the column another
210                    // table's FK references) - unlike DEFAULT/CHECK, there's no widening
211                    // exception for any of these. Find and drop them first, guarded, in
212                    // dependency order (a relation can depend on a unique/PK index, so it has
213                    // to come off first and go back on last).
214                    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                    // SQL Server refuses ALTER COLUMN while a DEFAULT or CHECK constraint from
232                    // the old definition still references the column (the same restriction
233                    // DROP COLUMN has) - drop both first, guarded, keyed off `old_column`'s
234                    // name (same as `new_column`'s, since this isn't a rename).
235                    write_drop_default_constraint(writer, table_name, old_column.name())?;
236                    write_drop_check_constraint(writer, table_name, old_column.name())?;
237
238                    // IDENTITY can only be set when a column is created - ALTER COLUMN rejects
239                    // an `identity(...)` clause outright, so `type_generator`'s normal
240                    // `integer identity(1,1)`/`bigint identity(1,1)` output (see
241                    // `sequence_sql`/`long_sequence_sql`) is a syntax error here. Strip it down
242                    // to the bare integer type; this can still change nullability, but not
243                    // retarget an identity column's underlying type - that needs a manual
244                    // column rebuild, so flag it instead of emitting DDL that would either fail
245                    // or silently do the wrong thing.
246                    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                    // `constraint.sql()` is already the full `check (...)` clause - same
321                    // convention as `DefaultTableConstraintGenerator` on the create path
322                    // (`constraint {name} {sql}`, no wrapper added) - so this must not
323                    // re-wrap it, or it comes out as `check (check (...))`.
324                    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                // SQL Server has no native enum type (enums are emulated as a CHECK
387                // constraint per column, same as SQLite) - nothing to do at the type level.
388                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
510/// Re-adds, keys first (a foreign key can depend on a unique/PK index), everything a run of
511/// `ModifyColumn`s took off its columns, then empties the pending lists.
512fn 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
526/// `column_name`'s own PRIMARY KEY/UNIQUE keys and indexes on `table`, paired with the
527/// 1-based per-category ordinal the create path would number them with (see
528/// `unique_key_name`/`index_name`) - mirrors `schema_diff::diff_engine`'s own per-category
529/// counting so a key dropped and re-added here gets the exact name a fresh install would give
530/// it. A `KeyType::Primary` entry's ordinal is unused (`primary_key_name` ignores it) and left
531/// at whatever the unique-key counter happens to be, same as the diff engine.
532fn 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
558/// Every foreign key anywhere in the model that touches `table_name`.`column_name` - either as
559/// the FK's own (owning-table) column, or as the column another table's FK references - paired
560/// with the relation's 1-based ordinal among its owning table's own `relations()` (see
561/// `DefaultRelationGenerator::relation_constraint_name`), so `foreign_key_name` reproduces the
562/// exact name the create path (or `AddRelation`/`DropRelation`) would give it. SQL Server blocks
563/// `ALTER COLUMN` for both roles - the referencing column and the column being referenced - with
564/// no exception, so both need to be found and cycled around a `ModifyColumn`.
565fn 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
599/// Columns across every table in the model whose `enumType` matches `enum_name`
600/// (case-insensitive) - used by `ModifyEnumType` to find every CHECK constraint that needs
601/// regenerating for SQLite and SQL Server, which emulate enums as `varchar` + `CHECK (col IN
602/// (...))` rather than a native type (see `DefaultColumnConstraintGenerator::enum_check_constraint_sql`).
603fn 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
623/// Unlike Postgres, SQL Server's `ADD CONSTRAINT` already validates existing data by default
624/// and would fail on its own if a row still held a removed value - but only when such a row
625/// actually exists, and only with a generic constraint-violation error. A removed value is a
626/// deliberate schema decision that deserves an explicit stop and explanation every time, not a
627/// silent pass-through when no violating row happens to exist yet - so this emits an
628/// unconditional `throw` before any of `ModifyEnumType`'s drop/add constraint statements run,
629/// forcing the developer to either confirm the value is unused (delete the block) or add
630/// `update` statements above it first.
631fn 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
674/// A required column with no `default` has no value to backfill existing rows with -
675/// `alter table ... add col type not null` would just fail with a generic constraint-violation
676/// error the moment the table has any rows (same reasoning as
677/// `write_removed_enum_value_guards`: an unconditional halt every time, not a silent
678/// pass-through when the table happens to be empty). Adds the column nullable, forces an
679/// explicit developer decision (backfill it or confirm the table is empty), then tightens it
680/// to `NOT NULL` - reachable only once the guard above it has been deleted.
681fn 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
730/// The `default` XML attribute is free-text SQL for every column type except `Boolean`,
731/// which is authored as `true`/`false`/`yes`/`no`/etc and must be rendered as whatever
732/// literal `boolean_mode` expects (`1`/`0` for `Native`'s `bit` type, `'Yes'`/`'No'` for
733/// `YesNo`, etc) - see `DefaultColumnGenerator::convert_boolean_default_constraint` in
734/// schema-sql-generator.
735fn 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
777/// SQL Server refuses `DROP COLUMN` and `ALTER COLUMN` while a `DEFAULT`/`CHECK` constraint
778/// still references the column - drop each by its computed name first, guarded so this is a
779/// safe no-op when it doesn't exist (the caller may not know either way, e.g. `DropColumn`
780/// carries no `Column`/type info).
781fn 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
813/// Re-creates `column`'s `DEFAULT` constraint (a no-op if it has none) as a separate `ADD
814/// CONSTRAINT ... FOR <column>`, since (unlike `AddColumn`, which can inline the default into
815/// the same statement that creates the column) `ALTER COLUMN` has no clause for it.
816fn 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
865/// T-SQL has no `IF [NOT] EXISTS` clause on `ALTER TABLE`/`CREATE TABLE`/`sp_rename`, so
866/// idempotency has to be expressed as an explicit `IF (NOT) EXISTS (...) BEGIN ... END`
867/// guard around the statement, using the relevant `sys.*` catalog view.
868/// Renders a brand-new table's `CREATE TABLE` by routing it through
869/// `schema-sql-generator`'s own `SqlServerTableGenerator` (C7) - the same column/key/constraint
870/// type mapping the full-schema generator uses - rather than the previous hand-rolled, always-
871/// empty `create table ()` (a hard T-SQL syntax error). Only `output_table_definition` is
872/// called, not `output_table` itself - see the matching Postgres helper's doc comment for why
873/// `output_table_drop`/`output_table_header` are skipped. Indexes are deliberately *not* taken
874/// from `output_indexes`: that renders each `create index` already terminated with its own
875/// `go` (correct for the full-schema generator, where every statement is its own top-level
876/// batch), but `go` isn't valid *inside* the `begin ... end` this function's `write_guarded`
877/// wraps the `CREATE TABLE` in - it would end the batch early and leave a dangling `end` with
878/// no matching `begin`. So each index is instead rendered through the already-existing
879/// `write_add_key` (used for `AddKey`), which produces its own separate, correctly-guarded
880/// top-level statement - harmless redundancy for a table that doesn't exist yet, but avoids
881/// the nesting bug. Initial data has no such problem (each row is already a standalone,
882/// self-terminated statement - see `output_initial_data`), so it's rendered directly. Relations
883/// and triggers are deliberately not included here at all - they're always a separate
884/// generation pass, even for an existing table - see `diff_add_relations`/`diff_add_triggers`
885/// in `schema-diff`, which still emit `AddRelation`/`AddTrigger` for a brand-new table, handled
886/// by the existing arms below.
887fn 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    // Schema-qualified, matching `qualified_name` above - a bare `table.name()` would be
904    // schema-blind (the same bug H15 fixed for SQL Server's drop guards: a same-named table
905    // in a different schema, e.g. `dbo.orders`, would falsely satisfy the guard and skip
906    // creating `s.orders` entirely).
907    write_guarded(
908        writer,
909        &format!("object_id('{}', 'U') is null", qualified_name),
910        &create_sql,
911    )?;
912
913    // See diff_add_keys - a brand-new table's keys/indexes are skipped there (they're already
914    // covered by `output_table_definition` above for primary/unique) except indexes, which are
915    // never embedded inline and must still be created here, one at a time. Not routed through
916    // `write_add_key`: its naming must stay keyed off the bare `table.name()` (matching what
917    // the create path itself names the index - `index_name` bakes the table name straight into
918    // the identifier, so a schema-qualified name would produce an invalid `ix_s.orders1`), but
919    // the `on`/guard clauses must use `qualified_name` (an unqualified `on orders` would target
920    // the wrong table if "orders" isn't in the connection's default schema) - two different
921    // names `write_add_key` doesn't distinguish between.
922    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
1148/// See the identical helper in `postgresql/mod.rs` - same reasoning: the trigger generator
1149/// writes through a dedicated `BufferSink`, drained into `writer` right away so it lands in
1150/// the right place relative to the rest of the migration's output.
1151fn 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
1173/// A column's `generated` expression changing (or a column switching between plain and
1174/// generated) cannot be migrated in place: there is no portable `ALTER COLUMN` for it, and
1175/// dropping/re-adding a plain column would lose data. Always halts, never guesses - same
1176/// pattern as the other halt-and-warn guards.
1177fn 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}