sqlmodel_schema/ddl/
mysql.rs

1//! MySQL DDL generator.
2//!
3//! MySQL has comprehensive ALTER TABLE support for most schema operations.
4
5use super::{
6    DdlGenerator, format_column_def, format_fk_constraint, generate_add_column,
7    generate_create_index, generate_create_table, generate_drop_index, generate_drop_table,
8    generate_rename_column, generate_rename_table, quote_identifier,
9};
10use crate::diff::SchemaOperation;
11use crate::introspect::Dialect;
12
13/// DDL generator for MySQL.
14pub struct MysqlDdlGenerator;
15
16impl DdlGenerator for MysqlDdlGenerator {
17    fn dialect(&self) -> &'static str {
18        "mysql"
19    }
20
21    fn generate(&self, op: &SchemaOperation) -> Vec<String> {
22        tracing::debug!(dialect = "mysql", op = ?op, "Generating DDL");
23
24        let statements = match op {
25            // Tables
26            SchemaOperation::CreateTable(table) => {
27                let mut stmts = vec![generate_create_table(table, Dialect::Mysql)];
28                for idx in &table.indexes {
29                    if idx.primary {
30                        continue;
31                    }
32                    stmts.push(generate_create_index(&table.name, idx, Dialect::Mysql));
33                }
34                stmts
35            }
36            SchemaOperation::DropTable(name) => {
37                vec![generate_drop_table(name, Dialect::Mysql)]
38            }
39            SchemaOperation::RenameTable { from, to } => {
40                vec![generate_rename_table(from, to, Dialect::Mysql)]
41            }
42
43            // Columns
44            SchemaOperation::AddColumn { table, column } => {
45                vec![generate_add_column(table, column, Dialect::Mysql)]
46            }
47            SchemaOperation::DropColumn { table, column, .. } => {
48                vec![format!(
49                    "ALTER TABLE {} DROP COLUMN {}",
50                    quote_identifier(table, Dialect::Mysql),
51                    quote_identifier(column, Dialect::Mysql)
52                )]
53            }
54            SchemaOperation::AlterColumnType {
55                table,
56                column,
57                to_type,
58                ..
59            } => {
60                // MySQL uses MODIFY COLUMN for type changes
61                vec![format!(
62                    "ALTER TABLE {} MODIFY COLUMN {} {}",
63                    quote_identifier(table, Dialect::Mysql),
64                    quote_identifier(column, Dialect::Mysql),
65                    to_type
66                )]
67            }
68            SchemaOperation::AlterColumnNullable {
69                table,
70                column,
71                to_nullable,
72                ..
73            } => {
74                // MySQL uses MODIFY COLUMN and requires a full column definition.
75                // We carry `ColumnInfo` on this operation so we can generate a correct statement.
76                let mut col = column.clone();
77                col.nullable = *to_nullable;
78                vec![format!(
79                    "ALTER TABLE {} MODIFY COLUMN {}",
80                    quote_identifier(table, Dialect::Mysql),
81                    format_column_def(&col, Dialect::Mysql)
82                )]
83            }
84            SchemaOperation::AlterColumnDefault {
85                table,
86                column,
87                to_default,
88                ..
89            } => {
90                // MySQL supports ALTER COLUMN ... SET DEFAULT / DROP DEFAULT
91                if let Some(default) = to_default {
92                    vec![format!(
93                        "ALTER TABLE {} ALTER COLUMN {} SET DEFAULT {}",
94                        quote_identifier(table, Dialect::Mysql),
95                        quote_identifier(column, Dialect::Mysql),
96                        default
97                    )]
98                } else {
99                    vec![format!(
100                        "ALTER TABLE {} ALTER COLUMN {} DROP DEFAULT",
101                        quote_identifier(table, Dialect::Mysql),
102                        quote_identifier(column, Dialect::Mysql)
103                    )]
104                }
105            }
106            SchemaOperation::RenameColumn { table, from, to } => {
107                vec![generate_rename_column(table, from, to, Dialect::Mysql)]
108            }
109
110            // Primary Keys
111            SchemaOperation::AddPrimaryKey { table, columns, .. } => {
112                let cols: Vec<String> = columns
113                    .iter()
114                    .map(|c| quote_identifier(c, Dialect::Mysql))
115                    .collect();
116                vec![format!(
117                    "ALTER TABLE {} ADD PRIMARY KEY ({})",
118                    quote_identifier(table, Dialect::Mysql),
119                    cols.join(", ")
120                )]
121            }
122            SchemaOperation::DropPrimaryKey { table, .. } => {
123                vec![format!(
124                    "ALTER TABLE {} DROP PRIMARY KEY",
125                    quote_identifier(table, Dialect::Mysql)
126                )]
127            }
128
129            // Foreign Keys
130            SchemaOperation::AddForeignKey { table, fk, .. } => {
131                let constraint_name = fk
132                    .name
133                    .clone()
134                    .unwrap_or_else(|| format!("fk_{}_{}", table, fk.column));
135                vec![format!(
136                    "ALTER TABLE {} ADD CONSTRAINT {} {}",
137                    quote_identifier(table, Dialect::Mysql),
138                    quote_identifier(&constraint_name, Dialect::Mysql),
139                    format_fk_constraint(fk, Dialect::Mysql)
140                )]
141            }
142            SchemaOperation::DropForeignKey { table, name, .. } => {
143                vec![format!(
144                    "ALTER TABLE {} DROP FOREIGN KEY {}",
145                    quote_identifier(table, Dialect::Mysql),
146                    quote_identifier(name, Dialect::Mysql)
147                )]
148            }
149
150            // Unique Constraints
151            SchemaOperation::AddUnique {
152                table, constraint, ..
153            } => {
154                let cols: Vec<String> = constraint
155                    .columns
156                    .iter()
157                    .map(|c| quote_identifier(c, Dialect::Mysql))
158                    .collect();
159                let name = constraint
160                    .name
161                    .clone()
162                    .unwrap_or_else(|| format!("uk_{}_{}", table, constraint.columns.join("_")));
163                vec![format!(
164                    "ALTER TABLE {} ADD CONSTRAINT {} UNIQUE ({})",
165                    quote_identifier(table, Dialect::Mysql),
166                    quote_identifier(&name, Dialect::Mysql),
167                    cols.join(", ")
168                )]
169            }
170            SchemaOperation::DropUnique { table, name, .. } => {
171                // MySQL drops unique constraints via DROP INDEX
172                vec![format!(
173                    "ALTER TABLE {} DROP INDEX {}",
174                    quote_identifier(table, Dialect::Mysql),
175                    quote_identifier(name, Dialect::Mysql)
176                )]
177            }
178
179            // Indexes
180            SchemaOperation::CreateIndex { table, index } => {
181                vec![generate_create_index(table, index, Dialect::Mysql)]
182            }
183            SchemaOperation::DropIndex { table, name } => {
184                vec![generate_drop_index(table, name, Dialect::Mysql)]
185            }
186        };
187
188        for stmt in &statements {
189            tracing::trace!(sql = %stmt, "Generated MySQL DDL statement");
190        }
191
192        statements
193    }
194}
195
196// ============================================================================
197// Unit Tests
198// ============================================================================
199
200#[cfg(test)]
201mod tests {
202    use super::*;
203    use crate::diff::SchemaOperation;
204    use crate::introspect::{
205        ColumnInfo, ForeignKeyInfo, IndexInfo, ParsedSqlType, TableInfo, UniqueConstraintInfo,
206    };
207
208    fn make_column(name: &str, sql_type: &str, nullable: bool) -> ColumnInfo {
209        ColumnInfo {
210            name: name.to_string(),
211            sql_type: sql_type.to_string(),
212            parsed_type: ParsedSqlType::parse(sql_type),
213            nullable,
214            default: None,
215            primary_key: false,
216            auto_increment: false,
217            comment: None,
218        }
219    }
220
221    fn make_table(name: &str, columns: Vec<ColumnInfo>, pk: Vec<&str>) -> TableInfo {
222        TableInfo {
223            name: name.to_string(),
224            columns,
225            primary_key: pk.into_iter().map(String::from).collect(),
226            foreign_keys: Vec::new(),
227            unique_constraints: Vec::new(),
228            check_constraints: Vec::new(),
229            indexes: Vec::new(),
230            comment: None,
231        }
232    }
233
234    #[test]
235    fn test_create_table() {
236        let ddl = MysqlDdlGenerator;
237        let table = make_table(
238            "heroes",
239            vec![
240                make_column("id", "INT", false),
241                make_column("name", "VARCHAR(100)", false),
242            ],
243            vec!["id"],
244        );
245        let op = SchemaOperation::CreateTable(table);
246        let stmts = ddl.generate(&op);
247
248        assert_eq!(stmts.len(), 1);
249        assert!(stmts[0].contains("CREATE TABLE IF NOT EXISTS"));
250        assert!(stmts[0].contains("`heroes`"));
251    }
252
253    #[test]
254    fn test_create_table_emits_indexes() {
255        let ddl = MysqlDdlGenerator;
256        let mut table = make_table(
257            "heroes",
258            vec![
259                make_column("id", "INT", false),
260                make_column("name", "VARCHAR(100)", false),
261            ],
262            vec!["id"],
263        );
264        table.indexes.push(IndexInfo {
265            name: "idx_heroes_name".to_string(),
266            columns: vec!["name".to_string()],
267            unique: false,
268            index_type: None,
269            primary: false,
270        });
271
272        let op = SchemaOperation::CreateTable(table);
273        let stmts = ddl.generate(&op);
274
275        assert_eq!(stmts.len(), 2);
276        assert!(stmts[0].contains("CREATE TABLE IF NOT EXISTS"));
277        assert!(stmts[1].contains("CREATE INDEX"));
278        assert!(stmts[1].contains("`idx_heroes_name`"));
279    }
280
281    #[test]
282    fn test_drop_table() {
283        let ddl = MysqlDdlGenerator;
284        let op = SchemaOperation::DropTable("heroes".to_string());
285        let stmts = ddl.generate(&op);
286
287        assert_eq!(stmts.len(), 1);
288        assert_eq!(stmts[0], "DROP TABLE IF EXISTS `heroes`");
289    }
290
291    #[test]
292    fn test_rename_table() {
293        let ddl = MysqlDdlGenerator;
294        let op = SchemaOperation::RenameTable {
295            from: "old_heroes".to_string(),
296            to: "heroes".to_string(),
297        };
298        let stmts = ddl.generate(&op);
299
300        assert_eq!(stmts.len(), 1);
301        assert!(stmts[0].contains("RENAME TABLE"));
302    }
303
304    #[test]
305    fn test_add_column() {
306        let ddl = MysqlDdlGenerator;
307        let op = SchemaOperation::AddColumn {
308            table: "heroes".to_string(),
309            column: make_column("age", "INT", true),
310        };
311        let stmts = ddl.generate(&op);
312
313        assert_eq!(stmts.len(), 1);
314        assert!(stmts[0].contains("ALTER TABLE"));
315        assert!(stmts[0].contains("ADD COLUMN"));
316    }
317
318    #[test]
319    fn test_drop_column() {
320        let ddl = MysqlDdlGenerator;
321        let op = SchemaOperation::DropColumn {
322            table: "heroes".to_string(),
323            column: "old_field".to_string(),
324            table_info: None,
325        };
326        let stmts = ddl.generate(&op);
327
328        assert_eq!(stmts.len(), 1);
329        assert!(stmts[0].contains("ALTER TABLE"));
330        assert!(stmts[0].contains("DROP COLUMN"));
331    }
332
333    #[test]
334    fn test_alter_column_type() {
335        let ddl = MysqlDdlGenerator;
336        let op = SchemaOperation::AlterColumnType {
337            table: "heroes".to_string(),
338            column: "age".to_string(),
339            from_type: "INT".to_string(),
340            to_type: "BIGINT".to_string(),
341            table_info: None,
342        };
343        let stmts = ddl.generate(&op);
344
345        assert_eq!(stmts.len(), 1);
346        assert!(stmts[0].contains("MODIFY COLUMN"));
347        assert!(stmts[0].contains("BIGINT"));
348    }
349
350    #[test]
351    fn test_alter_column_default_set() {
352        let ddl = MysqlDdlGenerator;
353        let op = SchemaOperation::AlterColumnDefault {
354            table: "heroes".to_string(),
355            column: "status".to_string(),
356            from_default: None,
357            to_default: Some("'active'".to_string()),
358            table_info: None,
359        };
360        let stmts = ddl.generate(&op);
361
362        assert_eq!(stmts.len(), 1);
363        assert!(stmts[0].contains("SET DEFAULT"));
364        assert!(stmts[0].contains("'active'"));
365    }
366
367    #[test]
368    fn test_alter_column_default_drop() {
369        let ddl = MysqlDdlGenerator;
370        let op = SchemaOperation::AlterColumnDefault {
371            table: "heroes".to_string(),
372            column: "status".to_string(),
373            from_default: Some("'active'".to_string()),
374            to_default: None,
375            table_info: None,
376        };
377        let stmts = ddl.generate(&op);
378
379        assert_eq!(stmts.len(), 1);
380        assert!(stmts[0].contains("DROP DEFAULT"));
381    }
382
383    #[test]
384    fn test_rename_column() {
385        let ddl = MysqlDdlGenerator;
386        let op = SchemaOperation::RenameColumn {
387            table: "heroes".to_string(),
388            from: "old_name".to_string(),
389            to: "name".to_string(),
390        };
391        let stmts = ddl.generate(&op);
392
393        assert_eq!(stmts.len(), 1);
394        assert!(stmts[0].contains("RENAME COLUMN"));
395    }
396
397    #[test]
398    fn test_add_primary_key() {
399        let ddl = MysqlDdlGenerator;
400        let op = SchemaOperation::AddPrimaryKey {
401            table: "heroes".to_string(),
402            columns: vec!["id".to_string()],
403            table_info: None,
404        };
405        let stmts = ddl.generate(&op);
406
407        assert_eq!(stmts.len(), 1);
408        assert!(stmts[0].contains("ADD PRIMARY KEY"));
409    }
410
411    #[test]
412    fn test_drop_primary_key() {
413        let ddl = MysqlDdlGenerator;
414        let op = SchemaOperation::DropPrimaryKey {
415            table: "heroes".to_string(),
416            table_info: None,
417        };
418        let stmts = ddl.generate(&op);
419
420        assert_eq!(stmts.len(), 1);
421        assert!(stmts[0].contains("DROP PRIMARY KEY"));
422    }
423
424    #[test]
425    fn test_add_foreign_key() {
426        let ddl = MysqlDdlGenerator;
427        let op = SchemaOperation::AddForeignKey {
428            table: "heroes".to_string(),
429            fk: ForeignKeyInfo {
430                name: Some("fk_heroes_team".to_string()),
431                column: "team_id".to_string(),
432                foreign_table: "teams".to_string(),
433                foreign_column: "id".to_string(),
434                on_delete: Some("CASCADE".to_string()),
435                on_update: None,
436            },
437            table_info: None,
438        };
439        let stmts = ddl.generate(&op);
440
441        assert_eq!(stmts.len(), 1);
442        assert!(stmts[0].contains("ADD CONSTRAINT"));
443        assert!(stmts[0].contains("FOREIGN KEY"));
444        assert!(stmts[0].contains("ON DELETE CASCADE"));
445    }
446
447    #[test]
448    fn test_drop_foreign_key() {
449        let ddl = MysqlDdlGenerator;
450        let op = SchemaOperation::DropForeignKey {
451            table: "heroes".to_string(),
452            name: "fk_heroes_team".to_string(),
453            table_info: None,
454        };
455        let stmts = ddl.generate(&op);
456
457        assert_eq!(stmts.len(), 1);
458        assert!(stmts[0].contains("DROP FOREIGN KEY"));
459    }
460
461    #[test]
462    fn test_add_unique() {
463        let ddl = MysqlDdlGenerator;
464        let op = SchemaOperation::AddUnique {
465            table: "heroes".to_string(),
466            constraint: UniqueConstraintInfo {
467                name: Some("uk_heroes_name".to_string()),
468                columns: vec!["name".to_string()],
469            },
470            table_info: None,
471        };
472        let stmts = ddl.generate(&op);
473
474        assert_eq!(stmts.len(), 1);
475        assert!(stmts[0].contains("ADD CONSTRAINT"));
476        assert!(stmts[0].contains("UNIQUE"));
477    }
478
479    #[test]
480    fn test_drop_unique() {
481        let ddl = MysqlDdlGenerator;
482        let op = SchemaOperation::DropUnique {
483            table: "heroes".to_string(),
484            name: "uk_heroes_name".to_string(),
485            table_info: None,
486        };
487        let stmts = ddl.generate(&op);
488
489        assert_eq!(stmts.len(), 1);
490        assert!(stmts[0].contains("DROP INDEX"));
491    }
492
493    #[test]
494    fn test_create_index() {
495        let ddl = MysqlDdlGenerator;
496        let op = SchemaOperation::CreateIndex {
497            table: "heroes".to_string(),
498            index: IndexInfo {
499                name: "idx_heroes_name".to_string(),
500                columns: vec!["name".to_string()],
501                unique: false,
502                index_type: None,
503                primary: false,
504            },
505        };
506        let stmts = ddl.generate(&op);
507
508        assert_eq!(stmts.len(), 1);
509        assert!(stmts[0].contains("CREATE INDEX"));
510    }
511
512    #[test]
513    fn test_drop_index() {
514        let ddl = MysqlDdlGenerator;
515        let op = SchemaOperation::DropIndex {
516            table: "heroes".to_string(),
517            name: "idx_heroes_name".to_string(),
518        };
519        let stmts = ddl.generate(&op);
520
521        assert_eq!(stmts.len(), 1);
522        assert!(stmts[0].contains("DROP INDEX"));
523        assert!(stmts[0].contains("ON `heroes`"));
524    }
525
526    #[test]
527    fn test_dialect() {
528        let ddl = MysqlDdlGenerator;
529        assert_eq!(ddl.dialect(), "mysql");
530    }
531}
sqlmodel_schema/ddl/mysql.rs

sqlmodel_schema/ddl/
mysql.rs
