rust_ef/migration/
engine_sql.rs

1//! `MigrationEngine` SQL generation — Up/Down DDL from `SchemaChange` lists.
2
3use super::ddl_expr::quote_string;
4use super::dialect::MigrationDialect;
5use super::diff::index_name;
6use super::engine::MigrationEngine;
7use super::history::{MIGRATION_HISTORY_TABLE, PRODUCT_VERSION};
8use super::schema_change::SchemaChange;
9use super::types::{
10    ModelSnapshot, SnapshotCheck, SnapshotColumn, SnapshotForeignKey, SnapshotIndex,
11    SnapshotIndexColumn, SnapshotTableOptions,
12};
13
14impl MigrationEngine {
15    pub(crate) fn initial_create_with_fks(&self, current: &ModelSnapshot) -> Vec<SchemaChange> {
16        let mut changes = self.initial_create(current);
17        for et in current.entity_types.iter().filter(|et| !et.is_view) {
18            // SQLite foreign keys are emitted inline in CREATE TABLE because
19            // SQLite does not support ALTER TABLE ADD CONSTRAINT.
20            if self.dialect != MigrationDialect::Sqlite {
21                Self::append_create_table_fks(&mut changes, &et.table_name, &et.columns);
22                Self::append_composite_fks(&mut changes, et);
23            }
24            Self::append_create_table_indexes(&mut changes, &et.table_name, &et.columns);
25            Self::append_declared_indexes(&mut changes, et);
26        }
27        changes
28    }
29
30    /// Renders `DEFAULT <expr>` for a column, if configured.
31    fn default_clause(&self, col: &SnapshotColumn) -> String {
32        match &col.default_value {
33            Some(v) if !v.trim().is_empty() => format!(" DEFAULT {}", v),
34            _ => String::new(),
35        }
36    }
37
38    fn collation_clause(&self, col: &SnapshotColumn) -> String {
39        match &col.collation {
40            Some(c) if !c.trim().is_empty() => format!(" COLLATE {}", c.trim()),
41            _ => String::new(),
42        }
43    }
44
45    /// Renders a full column definition for CREATE TABLE / ADD COLUMN / MySQL
46    /// MODIFY COLUMN, including generated columns, collation, default and
47    /// (MySQL) inline comment.
48    fn render_column_definition(&self, col: &SnapshotColumn) -> String {
49        let q = |s: &str| self.dialect.quote(s);
50        let (col_type, mysql_auto_increment) = self.column_type(col);
51
52        // Generated/computed columns: no NULL/NOT NULL or DEFAULT.
53        if let Some(expr) = &col.computed_sql {
54            if expr.trim().is_empty() {
55                // fall through to a normal column
56            } else {
57                let stored = match self.dialect {
58                    // PostgreSQL only supports STORED generated columns.
59                    MigrationDialect::Postgres => true,
60                    _ => col.computed_stored,
61                };
62                let storage = if stored { " STORED" } else { " VIRTUAL" };
63                let comment = if self.dialect == MigrationDialect::MySql {
64                    self.comment_clause(col)
65                } else {
66                    String::new()
67                };
68                return format!(
69                    "{} {} GENERATED ALWAYS AS ({}){}{}",
70                    q(&col.column_name),
71                    col_type,
72                    expr,
73                    storage,
74                    comment
75                );
76            }
77        }
78
79        // COLLATE is part of the data type specification in PostgreSQL,
80        // MySQL and SQLite, so it must precede NULL/NOT NULL.
81        let collation = self.collation_clause(col);
82        let col_type = if collation.is_empty() {
83            col_type
84        } else {
85            format!("{}{}", col_type, collation)
86        };
87
88        let nullable = if col.is_required || col.is_primary_key {
89            "NOT NULL"
90        } else {
91            "NULL"
92        };
93
94        // PostgreSQL sequence column: DEFAULT nextval('seq')
95        if col.is_sequence && self.dialect == MigrationDialect::Postgres {
96            if let Some(seq_name) = &col.sequence_name {
97                return format!(
98                    "{} {} DEFAULT nextval('{}') {}",
99                    q(&col.column_name),
100                    col_type,
101                    seq_name,
102                    nullable
103                );
104            }
105        }
106
107        let mut parts = vec![q(&col.column_name), col_type, nullable.to_string()];
108        if !mysql_auto_increment {
109            let default = self.default_clause(col);
110            if !default.is_empty() {
111                parts.push(default.trim_start().to_string());
112            }
113        }
114        if mysql_auto_increment {
115            parts.push("AUTO_INCREMENT".to_string());
116        }
117        if self.dialect == MigrationDialect::MySql {
118            let comment = self.comment_clause(col);
119            if !comment.is_empty() {
120                parts.push(comment.trim_start().to_string());
121            }
122        }
123        parts.join(" ")
124    }
125
126    /// MySQL inline `COMMENT '...'` clause.
127    fn comment_clause(&self, col: &SnapshotColumn) -> String {
128        match &col.comment {
129            Some(c) if !c.is_empty() => format!(" COMMENT {}", quote_string(c, self.dialect)),
130            _ => String::new(),
131        }
132    }
133
134    /// PostgreSQL column comments (separate statements).
135    fn column_comment_sql(&self, table: &str, col: &SnapshotColumn) -> Option<String> {
136        if self.dialect != MigrationDialect::Postgres {
137            return None;
138        }
139        let comment = col.comment.as_deref()?;
140        let q = |s: &str| self.dialect.quote(s);
141        Some(format!(
142            "COMMENT ON COLUMN {}.{} IS {};\n",
143            q(table),
144            q(&col.column_name),
145            quote_string(comment, self.dialect)
146        ))
147    }
148
149    fn alter_table_comment_sql(&self, table: &str, comment: Option<&str>) -> String {
150        let q = |s: &str| self.dialect.quote(s);
151        match self.dialect {
152            MigrationDialect::Sqlite => {
153                "-- WARNING: SQLite does not support table comments\n".to_string()
154            }
155            MigrationDialect::Postgres => match comment {
156                Some(c) => format!(
157                    "COMMENT ON TABLE {} IS {};\n",
158                    q(table),
159                    quote_string(c, self.dialect)
160                ),
161                None => format!("COMMENT ON TABLE {} IS NULL;\n", q(table)),
162            },
163            MigrationDialect::MySql => match comment {
164                Some(c) => format!(
165                    "ALTER TABLE {} COMMENT = {};\n",
166                    q(table),
167                    quote_string(c, self.dialect)
168                ),
169                None => format!("ALTER TABLE {} COMMENT = '';\n", q(table)),
170            },
171        }
172    }
173
174    fn table_comment_sql(&self, table: &str, comment: Option<&str>) -> Option<String> {
175        let comment = comment?;
176        let q = |s: &str| self.dialect.quote(s);
177        match self.dialect {
178            MigrationDialect::Postgres => Some(format!(
179                "COMMENT ON TABLE {} IS {};\n",
180                q(table),
181                quote_string(comment, self.dialect)
182            )),
183            MigrationDialect::MySql => Some(format!(
184                "ALTER TABLE {} COMMENT = {};\n",
185                q(table),
186                quote_string(comment, self.dialect)
187            )),
188            MigrationDialect::Sqlite => None,
189        }
190    }
191
192    /// Renders the MySQL table-options suffix (`ENGINE=... DEFAULT CHARSET=...`).
193    ///
194    /// MySQL always gets `ENGINE`/`DEFAULT CHARSET` so tables do not silently
195    /// inherit an instance default (e.g. latin1). PostgreSQL/SQLite ignore
196    /// table options.
197    fn table_options_suffix(&self, options: &SnapshotTableOptions) -> String {
198        if self.dialect != MigrationDialect::MySql {
199            return String::new();
200        }
201        let engine = options.engine.as_deref().unwrap_or("InnoDB");
202        let charset = options.charset.as_deref().unwrap_or("utf8mb4");
203        let mut suffix = format!(" ENGINE={engine} DEFAULT CHARSET={charset}");
204        if let Some(collate) = &options.collate {
205            suffix.push_str(&format!(" COLLATE={collate}"));
206        }
207        suffix
208    }
209
210    /// Renders a declared index key part for a CREATE INDEX statement.
211    fn render_index_key_part(&self, part: &SnapshotIndexColumn) -> String {
212        if part.is_expression {
213            format!("({})", part.value)
214        } else {
215            self.dialect.quote(&part.value)
216        }
217    }
218
219    /// Renders a `CREATE INDEX` statement for a declared index.
220    ///
221    /// MySQL has no partial indexes: a filtered UNIQUE index is emulated with a
222    /// functional `CASE WHEN <filter> THEN <col> END` unique index (only rows
223    /// matching the predicate produce non-NULL key parts; NULLs are not
224    /// compared in a unique index). A filtered non-unique index is skipped with
225    /// a warning comment.
226    pub(super) fn create_declared_index_sql(&self, table: &str, index: &SnapshotIndex) -> String {
227        let q = |s: &str| self.dialect.quote(s);
228        let unique_kw = if index.is_unique { "UNIQUE " } else { "" };
229        let name = q(&index.name);
230
231        // INCLUDE is PostgreSQL-only; other dialects ignore it with a warning.
232        let (include_warning, include_suffix) = if index.include.is_empty() {
233            (String::new(), String::new())
234        } else {
235            match self.dialect {
236                MigrationDialect::Postgres => {
237                    let cols: Vec<String> = index.include.iter().map(|c| q(c)).collect();
238                    (String::new(), format!(" INCLUDE ({})", cols.join(", ")))
239                }
240                _ => (
241                    "-- WARNING: index INCLUDE is only supported by PostgreSQL; ignored\n"
242                        .to_string(),
243                    String::new(),
244                ),
245            }
246        };
247
248        match (&index.filter, self.dialect) {
249            (Some(filter), MigrationDialect::MySql) if index.is_unique => {
250                let parts: Vec<String> = index
251                    .columns
252                    .iter()
253                    .map(|part| {
254                        let value = if part.is_expression {
255                            part.value.clone()
256                        } else {
257                            q(&part.value)
258                        };
259                        format!("(CASE WHEN {filter} THEN {value} END)")
260                    })
261                    .collect();
262                format!(
263                    "{include_warning}-- MySQL has no partial indexes; emulating filtered UNIQUE index with a functional index\nCREATE UNIQUE INDEX {name} ON {} ({});\n",
264                    q(table),
265                    parts.join(", ")
266                )
267            }
268            (Some(_), MigrationDialect::MySql) => format!(
269                "-- WARNING: MySQL does not support partial non-unique indexes; {name} on {} skipped\n",
270                q(table)
271            ),
272            (Some(filter), _) => {
273                let cols: Vec<String> = index
274                    .columns
275                    .iter()
276                    .map(|part| self.render_index_key_part(part))
277                    .collect();
278                format!(
279                    "{include_warning}CREATE {unique_kw}INDEX {name} ON {} ({}){include_suffix} WHERE {filter};\n",
280                    q(table),
281                    cols.join(", ")
282                )
283            }
284            (None, _) => {
285                let cols: Vec<String> = index
286                    .columns
287                    .iter()
288                    .map(|part| self.render_index_key_part(part))
289                    .collect();
290                format!(
291                    "{include_warning}CREATE {unique_kw}INDEX {name} ON {} ({}){include_suffix};\n",
292                    q(table),
293                    cols.join(", ")
294                )
295            }
296        }
297    }
298
299    fn composite_fk_clause(&self, fk: &SnapshotForeignKey) -> String {
300        let q = |s: &str| self.dialect.quote(s);
301        let cols: Vec<String> = fk.columns.iter().map(|c| q(c)).collect();
302        let ref_cols: Vec<String> = fk.referenced_columns.iter().map(|c| q(c)).collect();
303        let on_delete = fk
304            .on_delete
305            .as_deref()
306            .map(|c| format!(" ON DELETE {}", c))
307            .unwrap_or_default();
308        format!(
309            "CONSTRAINT {} FOREIGN KEY ({}) REFERENCES {} ({}){}",
310            q(&fk.name),
311            cols.join(", "),
312            q(&fk.referenced_table),
313            ref_cols.join(", "),
314            on_delete
315        )
316    }
317
318    fn create_composite_fk_sql(&self, table: &str, fk: &SnapshotForeignKey) -> String {
319        if self.dialect == MigrationDialect::Sqlite {
320            return format!(
321                "-- WARNING: SQLite cannot add composite foreign key {} to an existing table; rebuild {} to apply it\n",
322                self.dialect.quote(&fk.name),
323                self.dialect.quote(table)
324            );
325        }
326        format!(
327            "ALTER TABLE {} ADD {};\n",
328            self.dialect.quote(table),
329            self.composite_fk_clause(fk)
330        )
331    }
332
333    fn add_check_sql(&self, table: &str, check: &SnapshotCheck) -> String {
334        let q = |s: &str| self.dialect.quote(s);
335        match self.dialect {
336            MigrationDialect::Sqlite => format!(
337                "-- WARNING: SQLite cannot add CHECK constraint {} to an existing table; rebuild the table to apply it\n",
338                q(&check.name)
339            ),
340            _ => format!(
341                "ALTER TABLE {} ADD CONSTRAINT {} CHECK ({});\n",
342                q(table),
343                q(&check.name),
344                check.expression
345            ),
346        }
347    }
348
349    fn drop_check_sql(&self, table: &str, check: &SnapshotCheck) -> String {
350        let q = |s: &str| self.dialect.quote(s);
351        match self.dialect {
352            MigrationDialect::Sqlite => format!(
353                "-- WARNING: SQLite cannot drop CHECK constraint {}; rebuild the table to remove it\n",
354                q(&check.name)
355            ),
356            MigrationDialect::MySql => format!(
357                "ALTER TABLE {} DROP CHECK {};\n",
358                q(table),
359                q(&check.name)
360            ),
361            MigrationDialect::Postgres => format!(
362                "ALTER TABLE {} DROP CONSTRAINT IF EXISTS {};\n",
363                q(table),
364                q(&check.name)
365            ),
366        }
367    }
368
369    // -----------------------------------------------------------------------
370    // SQL generation
371    // -----------------------------------------------------------------------
372
373    /// Standard foreign-key constraint name used by Add/DropForeignKey.
374    ///
375    /// Dots from schema-qualified tables are flattened so the name stays a
376    /// single identifier.
377    pub fn foreign_key_name(table: &str, column: &str, referenced_table: &str) -> String {
378        format!(
379            "fk_{}_{}_{}",
380            table.replace('.', "_"),
381            column,
382            referenced_table.replace('.', "_")
383        )
384    }
385
386    /// Standard index name used by CreateIndex/DropIndex.
387    pub fn index_name(table: &str, column: &str) -> String {
388        format!("ix_{}_{}", table.replace('.', "_"), column)
389    }
390
391    /// Returns the column type and whether MySQL `AUTO_INCREMENT` must be
392    /// emitted after the nullability clause.
393    ///
394    /// MySQL's column grammar is `type [NOT NULL|NULL] ... [AUTO_INCREMENT]`;
395    /// emitting `INT AUTO_INCREMENT NOT NULL` makes the primary key column
396    /// nullable (error 1171).
397    fn column_type(&self, col: &SnapshotColumn) -> (String, bool) {
398        let col_type = self.dialect.map_column_type(col);
399        if self.dialect == MigrationDialect::MySql && (col.is_auto_increment || col.is_sequence) {
400            let stripped = col_type
401                .strip_suffix(" AUTO_INCREMENT")
402                .unwrap_or(&col_type)
403                .to_string();
404            (stripped, true)
405        } else {
406            (col_type, false)
407        }
408    }
409
410    pub fn generate_alter_column_sql(
411        &self,
412        table: &str,
413        column_name: &str,
414        new: &SnapshotColumn,
415    ) -> String {
416        let q = |s: &str| self.dialect.quote(s);
417        let col_type = self.dialect.map_column_type(new);
418        match self.dialect {
419            MigrationDialect::Postgres => {
420                if new.computed_sql.is_some() {
421                    return format!(
422                        "-- WARNING: PostgreSQL cannot alter a generated column expression; drop and re-add {}.{} manually\n",
423                        q(table),
424                        q(column_name)
425                    );
426                }
427                let nullable = if new.is_required || new.is_primary_key {
428                    "SET NOT NULL"
429                } else {
430                    "DROP NOT NULL"
431                };
432                let default = match &new.default_value {
433                    Some(v) if !v.trim().is_empty() => format!(
434                        "ALTER TABLE {} ALTER COLUMN {} SET DEFAULT {};\n",
435                        q(table),
436                        q(column_name),
437                        v
438                    ),
439                    _ => format!(
440                        "ALTER TABLE {} ALTER COLUMN {} DROP DEFAULT;\n",
441                        q(table),
442                        q(column_name)
443                    ),
444                };
445                let collation = match &new.collation {
446                    Some(c) if !c.trim().is_empty() => format!(" COLLATE {}", c.trim()),
447                    _ => String::new(),
448                };
449                let mut sql = format!(
450                    "ALTER TABLE {} ALTER COLUMN {} TYPE {}{};\nALTER TABLE {} ALTER COLUMN {} {};\n{}",
451                    q(table),
452                    q(column_name),
453                    col_type,
454                    collation,
455                    q(table),
456                    q(column_name),
457                    nullable,
458                    default
459                );
460                if let Some(stmt) = self.column_comment_sql(table, new) {
461                    sql.push_str(&stmt);
462                }
463                sql
464            }
465            MigrationDialect::MySql => format!(
466                "ALTER TABLE {} MODIFY COLUMN {};\n",
467                q(table),
468                self.render_column_definition(new)
469            ),
470            MigrationDialect::Sqlite => format!(
471                "-- SQLite does not support ALTER COLUMN type changes; \
472                 rebuild table manually for {}.{}\n",
473                q(table),
474                q(column_name)
475            ),
476        }
477    }
478
479    pub(crate) fn generate_up_sql(&self, changes: &[SchemaChange]) -> String {
480        self.generate_up_sql_inner(changes, true)
481    }
482
483    pub(crate) fn generate_ddl_sql(&self, changes: &[SchemaChange]) -> String {
484        self.generate_up_sql_inner(changes, false)
485    }
486
487    fn generate_up_sql_inner(&self, changes: &[SchemaChange], record_history: bool) -> String {
488        let mut sql = String::from("-- Up Migration (auto-generated by rust-ef)\n\n");
489        let q = |s: &str| self.dialect.quote(s);
490        let create_kw = if record_history {
491            "CREATE TABLE"
492        } else {
493            "CREATE TABLE IF NOT EXISTS"
494        };
495
496        for change in changes {
497            match change {
498                SchemaChange::CreateTable {
499                    table,
500                    columns,
501                    checks,
502                    table_options,
503                    foreign_keys,
504                    comment,
505                } => {
506                    // PostgreSQL: create sequences before the table
507                    if self.dialect == MigrationDialect::Postgres {
508                        for c in columns {
509                            if c.is_sequence {
510                                if let Some(seq_name) = &c.sequence_name {
511                                    sql.push_str(&format!(
512                                        "CREATE SEQUENCE IF NOT EXISTS {};\n",
513                                        q(seq_name)
514                                    ));
515                                }
516                            }
517                        }
518                    }
519
520                    sql.push_str(&format!("{} {} (\n", create_kw, q(table)));
521
522                    // Separate primary key columns from regular columns
523                    let pk_columns: Vec<&str> = columns
524                        .iter()
525                        .filter(|c| c.is_primary_key)
526                        .map(|c| c.column_name.as_str())
527                        .collect();
528
529                    let col_defs: Vec<String> = columns
530                        .iter()
531                        .map(|c| self.render_column_definition(c))
532                        .collect();
533                    sql.push_str(&format!("    {}\n", col_defs.join(",\n    ")));
534
535                    // Composite primary key constraint
536                    if pk_columns.len() == 1 {
537                        sql.push_str(&format!("    ,PRIMARY KEY ({})\n", q(pk_columns[0])));
538                    } else if pk_columns.len() > 1 {
539                        let pk_list: Vec<String> = pk_columns.iter().map(|c| q(c)).collect();
540                        sql.push_str(&format!("    ,PRIMARY KEY ({})\n", pk_list.join(", ")));
541                    }
542
543                    // Table-level CHECK constraints
544                    for check in checks {
545                        sql.push_str(&format!(
546                            "    ,CONSTRAINT {} CHECK ({})\n",
547                            q(&check.name),
548                            check.expression
549                        ));
550                    }
551
552                    // SQLite: inline foreign keys (it cannot ALTER ADD
553                    // CONSTRAINT). Other dialects add FKs after all tables
554                    // exist via ALTER TABLE.
555                    //
556                    // `ensure_created` (playbook: idempotent DDL only, no
557                    // indexes/FKs) intentionally skips this; only real
558                    // migration scripts get FK enforcement.
559                    if record_history && self.dialect == MigrationDialect::Sqlite {
560                        for (col, ref_table, ref_col) in columns
561                            .iter()
562                            .filter_map(|c| super::diff::fk_target(c).map(|(t, r)| (c, t, r)))
563                        {
564                            let fk_name =
565                                Self::foreign_key_name(table, &col.column_name, &ref_table);
566                            let on_delete = col
567                                .fk_on_delete
568                                .as_deref()
569                                .map(|c| format!(" ON DELETE {}", c))
570                                .unwrap_or_default();
571                            sql.push_str(&format!(
572                                "    ,CONSTRAINT {} FOREIGN KEY ({}) REFERENCES {} ({}){}\n",
573                                q(&fk_name),
574                                q(&col.column_name),
575                                q(&ref_table),
576                                q(&ref_col),
577                                on_delete
578                            ));
579                        }
580                        for fk in foreign_keys {
581                            sql.push_str(&format!("    ,{}\n", self.composite_fk_clause(fk)));
582                        }
583                    }
584
585                    sql.push(')');
586                    sql.push_str(&self.table_options_suffix(table_options));
587                    sql.push_str(";\n\n");
588
589                    // Comments: MySQL table comments are emitted as ALTER (to
590                    // keep the CREATE statement simple); PostgreSQL uses
591                    // separate COMMENT ON statements; SQLite has no comments.
592                    if let Some(stmt) = self.table_comment_sql(table, comment.as_deref()) {
593                        sql.push_str(&stmt);
594                    }
595                    for c in columns {
596                        if let Some(stmt) = self.column_comment_sql(table, c) {
597                            sql.push_str(&stmt);
598                        }
599                    }
600                }
601                SchemaChange::DropTable { table } => {
602                    sql.push_str(&format!(
603                        "-- WARNING: dropping table {} is irreversible\nDROP TABLE IF EXISTS {};\n",
604                        q(table),
605                        q(table)
606                    ));
607                }
608                SchemaChange::AddColumn { table, column } => {
609                    if column.is_required
610                        && !column.is_primary_key
611                        && column.default_value.is_none()
612                        && column.computed_sql.is_none()
613                    {
614                        sql.push_str(&format!(
615                            "-- WARNING: adding NOT NULL column {} without a default fails on non-empty tables; provide has_default(...) or backfill first\n",
616                            q(&column.column_name)
617                        ));
618                    }
619                    sql.push_str(&format!(
620                        "ALTER TABLE {} ADD COLUMN {};\n",
621                        q(table),
622                        self.render_column_definition(column)
623                    ));
624                    if let Some(stmt) = self.column_comment_sql(table, column) {
625                        sql.push_str(&stmt);
626                    }
627                }
628                SchemaChange::DropColumn { table, column_name } => {
629                    sql.push_str(&format!(
630                        "-- WARNING: dropping column {}.{} is irreversible; column renames are not detected (data loss)\n",
631                        q(table),
632                        q(column_name)
633                    ));
634                    sql.push_str(&format!(
635                        "ALTER TABLE {} DROP COLUMN IF EXISTS {};\n",
636                        q(table),
637                        q(column_name)
638                    ));
639                }
640                SchemaChange::AlterColumn {
641                    table,
642                    column_name,
643                    old: _,
644                    new,
645                } => {
646                    sql.push_str(&self.generate_alter_column_sql(table, column_name, new));
647                }
648                SchemaChange::AddForeignKey {
649                    table,
650                    column,
651                    referenced_table,
652                    referenced_column,
653                    on_delete,
654                } => {
655                    if self.dialect == MigrationDialect::Sqlite {
656                        sql.push_str(&format!(
657                            "-- WARNING: SQLite cannot add a foreign key to an existing table; rebuild {} to add ({}) -> {}({})\n",
658                            q(table),
659                            q(column),
660                            q(referenced_table),
661                            q(referenced_column)
662                        ));
663                    } else {
664                        let fk_name = Self::foreign_key_name(table, column, referenced_table);
665                        let on_delete_clause = on_delete
666                            .as_deref()
667                            .map(|c| format!(" ON DELETE {}", c))
668                            .unwrap_or_default();
669                        sql.push_str(&format!(
670                            "ALTER TABLE {} ADD CONSTRAINT {} FOREIGN KEY ({}) REFERENCES {} ({}){};\n",
671                            q(table),
672                            q(&fk_name),
673                            q(column),
674                            q(referenced_table),
675                            q(referenced_column),
676                            on_delete_clause
677                        ));
678                    }
679                }
680                SchemaChange::DropForeignKey {
681                    table,
682                    column,
683                    referenced_table,
684                } => {
685                    let fk_name = Self::foreign_key_name(table, column, referenced_table);
686                    match self.dialect {
687                        MigrationDialect::Sqlite => sql.push_str(&format!(
688                            "-- WARNING: SQLite cannot drop a foreign key from an existing table; rebuild {} to remove {}\n",
689                            q(table),
690                            q(&fk_name)
691                        )),
692                        MigrationDialect::MySql => sql.push_str(&format!(
693                            "ALTER TABLE {} DROP FOREIGN KEY {};\n",
694                            q(table),
695                            q(&fk_name)
696                        )),
697                        _ => sql.push_str(&format!(
698                            "ALTER TABLE {} DROP CONSTRAINT IF EXISTS {};\n",
699                            q(table),
700                            q(&fk_name)
701                        )),
702                    }
703                }
704                SchemaChange::CreateIndex {
705                    table,
706                    column,
707                    is_unique,
708                } => {
709                    let unique_kw = if *is_unique { "UNIQUE " } else { "" };
710                    let idx_name = index_name(table, column);
711                    sql.push_str(&format!(
712                        "CREATE {unique_kw}INDEX {} ON {} ({});\n",
713                        q(&idx_name),
714                        q(table),
715                        q(column)
716                    ));
717                }
718                SchemaChange::DropIndex {
719                    table,
720                    column,
721                    is_unique: _,
722                } => {
723                    let idx_name = index_name(table, column);
724                    match self.dialect {
725                        MigrationDialect::MySql => {
726                            sql.push_str(&format!("DROP INDEX {} ON {};\n", q(&idx_name), q(table)))
727                        }
728                        _ => sql.push_str(&format!("DROP INDEX IF EXISTS {};\n", q(&idx_name))),
729                    }
730                }
731                SchemaChange::CreateDeclaredIndex { table, index } => {
732                    sql.push_str(&self.create_declared_index_sql(table, index));
733                }
734                SchemaChange::DropDeclaredIndex { table, index } => match self.dialect {
735                    MigrationDialect::MySql => {
736                        sql.push_str(&format!("DROP INDEX {} ON {};\n", q(&index.name), q(table)))
737                    }
738                    _ => sql.push_str(&format!("DROP INDEX IF EXISTS {};\n", q(&index.name))),
739                },
740                SchemaChange::AddCompositeForeignKey { table, foreign_key } => {
741                    sql.push_str(&self.create_composite_fk_sql(table, foreign_key));
742                }
743                SchemaChange::DropCompositeForeignKey { table, foreign_key } => {
744                    match self.dialect {
745                        MigrationDialect::Sqlite => sql.push_str(&format!(
746                            "-- WARNING: SQLite cannot drop composite foreign key {} from an existing table; rebuild {} to remove it\n",
747                            q(&foreign_key.name),
748                            q(table)
749                        )),
750                        MigrationDialect::MySql => sql.push_str(&format!(
751                            "ALTER TABLE {} DROP FOREIGN KEY {};\n",
752                            q(table),
753                            q(&foreign_key.name)
754                        )),
755                        _ => sql.push_str(&format!(
756                            "ALTER TABLE {} DROP CONSTRAINT IF EXISTS {};\n",
757                            q(table),
758                            q(&foreign_key.name)
759                        )),
760                    }
761                }
762                SchemaChange::AddCheck { table, check } => {
763                    sql.push_str(&self.add_check_sql(table, check));
764                }
765                SchemaChange::DropCheck { table, check } => {
766                    sql.push_str(&self.drop_check_sql(table, check));
767                }
768                SchemaChange::AlterTableOptions { table, options } => {
769                    if self.dialect == MigrationDialect::MySql {
770                        let engine = options.engine.as_deref().unwrap_or("InnoDB");
771                        let charset = options.charset.as_deref().unwrap_or("utf8mb4");
772                        sql.push_str(&format!(
773                            "ALTER TABLE {} ENGINE={} DEFAULT CHARSET={}",
774                            q(table),
775                            engine,
776                            charset
777                        ));
778                        if let Some(collate) = &options.collate {
779                            sql.push_str(&format!(" COLLATE={}", collate));
780                        }
781                        sql.push_str(";\n");
782                    } else {
783                        sql.push_str(&format!(
784                            "-- Table options on {} are MySQL-only; ignored for this dialect\n",
785                            q(table)
786                        ));
787                    }
788                }
789                SchemaChange::AlterTableComment {
790                    table,
791                    old_comment: _,
792                    new_comment,
793                } => {
794                    sql.push_str(&self.alter_table_comment_sql(table, new_comment.as_deref()));
795                }
796            }
797        }
798
799        if record_history {
800            sql.push_str(&format!(
801                "INSERT INTO {}(migration_id, product_version) VALUES ('{{migration_id}}', '{}');\n",
802                q(MIGRATION_HISTORY_TABLE),
803                PRODUCT_VERSION
804            ));
805        }
806
807        sql
808    }
809
810    pub(super) fn generate_down_sql(&self, changes: &[SchemaChange]) -> String {
811        let mut sql = String::from("-- Down Migration (auto-generated by rust-ef)\n\n");
812        let q = |s: &str| self.dialect.quote(s);
813
814        // Reverse the changes for the down migration
815        for change in changes.iter().rev() {
816            match change {
817                SchemaChange::CreateTable { table, .. } => {
818                    sql.push_str(&format!("DROP TABLE IF EXISTS {};\n", q(table)));
819                }
820                SchemaChange::DropTable { table } => {
821                    sql.push_str(&format!(
822                        "-- WARNING: Cannot restore table {} (original schema unknown)\n",
823                        q(table)
824                    ));
825                }
826                SchemaChange::AddColumn { table, column } => {
827                    sql.push_str(&format!(
828                        "ALTER TABLE {} DROP COLUMN IF EXISTS {};\n",
829                        q(table),
830                        q(&column.column_name)
831                    ));
832                }
833                SchemaChange::DropColumn { table, column_name } => {
834                    sql.push_str(&format!(
835                        "-- WARNING: Cannot restore column {} on {} (original type unknown)\n",
836                        q(column_name),
837                        q(table)
838                    ));
839                }
840                SchemaChange::AlterColumn {
841                    table,
842                    column_name,
843                    old,
844                    new: _,
845                } => {
846                    sql.push_str(&self.generate_alter_column_sql(table, column_name, old));
847                }
848                SchemaChange::AddForeignKey {
849                    table,
850                    column,
851                    referenced_table,
852                    ..
853                } => {
854                    let fk_name = Self::foreign_key_name(table, column, referenced_table);
855                    match self.dialect {
856                        MigrationDialect::Sqlite => sql.push_str(&format!(
857                            "-- WARNING: SQLite cannot drop a foreign key from an existing table; rebuild {} to remove {}\n",
858                            q(table),
859                            q(&fk_name)
860                        )),
861                        MigrationDialect::MySql => sql.push_str(&format!(
862                            "ALTER TABLE {} DROP FOREIGN KEY {};\n",
863                            q(table),
864                            q(&fk_name)
865                        )),
866                        _ => sql.push_str(&format!(
867                            "ALTER TABLE {} DROP CONSTRAINT IF EXISTS {};\n",
868                            q(table),
869                            q(&fk_name)
870                        )),
871                    }
872                }
873                SchemaChange::DropForeignKey {
874                    table,
875                    column,
876                    referenced_table,
877                    ..
878                } => {
879                    sql.push_str(&format!(
880                        "-- WARNING: Cannot restore foreign key constraint {} on {}.{}\n",
881                        q(&Self::foreign_key_name(table, column, referenced_table)),
882                        q(table),
883                        q(column)
884                    ));
885                }
886                SchemaChange::CreateIndex {
887                    table,
888                    column,
889                    is_unique: _,
890                } => {
891                    let idx_name = index_name(table, column);
892                    match self.dialect {
893                        MigrationDialect::MySql => {
894                            sql.push_str(&format!("DROP INDEX {} ON {};\n", q(&idx_name), q(table)))
895                        }
896                        _ => sql.push_str(&format!("DROP INDEX IF EXISTS {};\n", q(&idx_name))),
897                    }
898                }
899                SchemaChange::DropIndex {
900                    table,
901                    column,
902                    is_unique,
903                } => {
904                    let unique_kw = if *is_unique { "UNIQUE " } else { "" };
905                    let idx_name = index_name(table, column);
906                    sql.push_str(&format!(
907                        "CREATE {unique_kw}INDEX {} ON {} ({});\n",
908                        q(&idx_name),
909                        q(table),
910                        q(column)
911                    ));
912                }
913                SchemaChange::CreateDeclaredIndex { table, index } => match self.dialect {
914                    MigrationDialect::MySql => {
915                        sql.push_str(&format!("DROP INDEX {} ON {};\n", q(&index.name), q(table)))
916                    }
917                    _ => sql.push_str(&format!("DROP INDEX IF EXISTS {};\n", q(&index.name))),
918                },
919                SchemaChange::DropDeclaredIndex { table, index } => {
920                    sql.push_str(&self.create_declared_index_sql(table, index));
921                }
922                SchemaChange::AddCompositeForeignKey { table, foreign_key } => match self.dialect {
923                    MigrationDialect::Sqlite => sql.push_str(&format!(
924                        "-- WARNING: SQLite cannot drop composite foreign key {} from an existing table; rebuild {} to remove it\n",
925                        q(&foreign_key.name),
926                        q(table)
927                    )),
928                    MigrationDialect::MySql => sql.push_str(&format!(
929                        "ALTER TABLE {} DROP FOREIGN KEY {};\n",
930                        q(table),
931                        q(&foreign_key.name)
932                    )),
933                    _ => sql.push_str(&format!(
934                        "ALTER TABLE {} DROP CONSTRAINT IF EXISTS {};\n",
935                        q(table),
936                        q(&foreign_key.name)
937                    )),
938                },
939                SchemaChange::DropCompositeForeignKey { table, foreign_key } => {
940                    sql.push_str(&self.create_composite_fk_sql(table, foreign_key));
941                }
942                SchemaChange::AddCheck { table, check } => {
943                    sql.push_str(&self.drop_check_sql(table, check));
944                }
945                SchemaChange::DropCheck { table, check } => {
946                    sql.push_str(&self.add_check_sql(table, check));
947                }
948                SchemaChange::AlterTableOptions { .. } => {
949                    sql.push_str(
950                        "-- WARNING: Table option changes cannot be reverted automatically\n",
951                    );
952                }
953                SchemaChange::AlterTableComment {
954                    table,
955                    old_comment,
956                    new_comment: _,
957                } => {
958                    sql.push_str(&self.alter_table_comment_sql(table, old_comment.as_deref()));
959                }
960            }
961        }
962
963        sql.push_str(&format!(
964            "DELETE FROM {} WHERE migration_id = '{{migration_id}}';\n",
965            q(MIGRATION_HISTORY_TABLE)
966        ));
967
968        sql
969    }
970}
971
972/// Renders a single-quoted SQL string literal.
973#[cfg(test)]
974mod tests {
975    use super::*;
976    use crate::migration::schema_change::SchemaChange;
977    use crate::migration::types::SnapshotColumn;
978
979    #[test]
980    fn mysql_auto_increment_follows_nullability() {
981        let engine = MigrationEngine::new(MigrationDialect::MySql);
982        let column = SnapshotColumn {
983            column_name: "id".into(),
984            type_name: "i32".into(),
985            is_primary_key: true,
986            is_required: true,
987            is_auto_increment: true,
988            ..Default::default()
989        };
990        let sql = engine.generate_ddl_sql(&[SchemaChange::CreateTable {
991            table: "items".into(),
992            columns: vec![column],
993            checks: Vec::new(),
994            table_options: Default::default(),
995            foreign_keys: Vec::new(),
996            comment: None,
997        }]);
998        assert!(
999            sql.contains("`id` INT NOT NULL AUTO_INCREMENT"),
1000            "unexpected DDL: {sql}"
1001        );
1002        assert!(
1003            !sql.contains("INT AUTO_INCREMENT NOT NULL"),
1004            "unexpected DDL: {sql}"
1005        );
1006    }
1007
1008    #[test]
1009    fn primary_key_columns_are_not_null_even_without_required() {
1010        let engine = MigrationEngine::new(MigrationDialect::Postgres);
1011        let column = SnapshotColumn {
1012            column_name: "id".into(),
1013            type_name: "i32".into(),
1014            is_primary_key: true,
1015            is_required: false,
1016            is_auto_increment: true,
1017            ..Default::default()
1018        };
1019        let sql = engine.generate_ddl_sql(&[SchemaChange::CreateTable {
1020            table: "items".into(),
1021            columns: vec![column],
1022            checks: Vec::new(),
1023            table_options: Default::default(),
1024            foreign_keys: Vec::new(),
1025            comment: None,
1026        }]);
1027        assert!(
1028            sql.contains("\"id\" SERIAL NOT NULL"),
1029            "unexpected DDL: {sql}"
1030        );
1031    }
1032}
rust_ef/migration/engine_sql.rs

rust_ef/migration/
engine_sql.rs
