siderite_migrations/schema_editor/
mysql.rs

1//! MySQL / MariaDB DDL (MySQL 8.0.31+).
2//!
3//! The editor mirrors the PostgreSQL one in [`super`], with the dialect
4//! differences MySQL forces on a schema tool:
5//!
6//! * **Quoting.** Identifiers use backticks (embedded backticks doubled).
7//!   String literals escape backslashes as well as quotes, because MySQL
8//!   treats `\` as an escape character unless `NO_BACKSLASH_ESCAPES` is set.
9//! * **No transactional DDL.** Every DDL statement commits implicitly, so a
10//!   migration that fails half way leaves its earlier statements applied and
11//!   no history row is written. [`Migrator`](crate::executor::Migrator) does
12//!   not wrap MySQL migrations in a transaction for that reason. Keep MySQL
13//!   migrations small (one schema change each) so a failure is easy to repair.
14//! * **`AUTO_INCREMENT`** primary keys: `BIGINT NOT NULL AUTO_INCREMENT
15//!   PRIMARY KEY`.
16//! * **`MODIFY COLUMN`** replaces the whole column definition, so an
17//!   `AlterField` always restates type, nullability and default.
18//! * **Foreign keys** are table-level `CONSTRAINT .. FOREIGN KEY` clauses
19//!   named `<table>_<column>_fk`. MySQL parses an inline `REFERENCES` on a
20//!   column but ignores it, so it is never used. InnoDB also rejects
21//!   `ON DELETE SET DEFAULT`, which is reported as an error.
22//! * **Keyed `TEXT`.** `TEXT`, `BLOB` and `JSON` columns cannot be a primary
23//!   key, `UNIQUE` or indexed without a prefix length, and this editor never
24//!   guesses one. Give such fields a `max_length` (a `VARCHAR`) instead.
25//!   Literal defaults on these types are written as `DEFAULT ('..')`.
26//! * `DROP INDEX` needs its table (`DROP INDEX name ON table`); a unique
27//!   constraint is dropped as an index and a check constraint with
28//!   `DROP CHECK`.
29//! * `DbDefault::Now` is `CURRENT_TIMESTAMP(6)`, matching `DATETIME(6)`.
30//!
31//! Column types follow the MySQL backend's storage table: `TINYINT(1)`,
32//! `DATETIME(6)` (UTC), `TIME(6)`, `CHAR(36)` for UUIDs, `JSON`, `DOUBLE`.
33//! A decimal without precision becomes `DECIMAL(38,10)` (a bare `DECIMAL` is
34//! `DECIMAL(10,0)` and would round to an integer).
35
36use crate::error::MigrationError;
37use crate::operation::Operation;
38use crate::state::{
39    ConstraintState, DbDefault, FieldState, ForeignKeyState, IndexState, ModelState, OnDelete,
40    ProjectState, SqlType, auto_index_name,
41};
42use std::collections::BTreeSet;
43
44/// Quote `ident` with backticks; embed a backtick by doubling it.
45pub fn quote_ident(ident: &str) -> String {
46    let mut out = String::with_capacity(ident.len() + 2);
47    out.push('`');
48    for ch in ident.chars() {
49        if ch == '`' {
50            out.push('`');
51        }
52        out.push(ch);
53    }
54    out.push('`');
55    out
56}
57
58/// Quote a MySQL string literal (quotes doubled, backslashes escaped).
59pub fn quote_string(value: &str) -> String {
60    let mut out = String::with_capacity(value.len() + 2);
61    out.push('\'');
62    for ch in value.chars() {
63        match ch {
64            '\'' => out.push_str("''"),
65            '\\' => out.push_str("\\\\"),
66            '\0' => out.push_str("\\0"),
67            other => out.push(other),
68        }
69    }
70    out.push('\'');
71    out
72}
73
74/// Render one operation against the schema `state` before the operation.
75///
76/// # Errors
77/// [`MigrationError::State`] when the operation needs something MySQL cannot
78/// express (keyed `TEXT`, `ON DELETE SET DEFAULT`) or refers to an unknown
79/// model, field or constraint.
80pub(super) fn render(op: &Operation, state: &ProjectState) -> Result<Vec<String>, MigrationError> {
81    match op {
82        Operation::CreateModel { model } => create_model_sql(model),
83        Operation::DeleteModel { name } => {
84            let model = state.require(name)?;
85            Ok(vec![format!("DROP TABLE {}", quote_ident(&model.table))])
86        }
87        Operation::RenameModel {
88            old_name, table, ..
89        } => {
90            let model = state.require(old_name)?;
91            if model.table == *table {
92                Ok(Vec::new())
93            } else {
94                Ok(vec![format!(
95                    "ALTER TABLE {} RENAME TO {}",
96                    quote_ident(&model.table),
97                    quote_ident(table)
98                )])
99            }
100        }
101        Operation::AddField { model, field } => add_field_sql(state.require(model)?, field),
102        Operation::RemoveField { model, name } => remove_field_sql(state.require(model)?, name),
103        Operation::AlterField { model, name, field } => {
104            alter_field_sql(state.require(model)?, name, field)
105        }
106        Operation::RenameField {
107            model,
108            old_name,
109            new_name,
110        } => rename_field_sql(state.require(model)?, old_name, new_name),
111        Operation::CreateIndex { model, index } => {
112            let m = state.require(model)?;
113            Ok(vec![create_index_sql(m, index)?])
114        }
115        Operation::DeleteIndex { model, name } => {
116            let m = state.require(model)?;
117            Ok(vec![drop_index_sql(&m.table, name)])
118        }
119        Operation::AddConstraint { model, constraint } => {
120            add_constraint_sql(state.require(model)?, constraint)
121        }
122        Operation::DeleteConstraint { model, name } => {
123            delete_constraint_sql(state.require(model)?, name)
124        }
125        Operation::RunSQL { sql, .. } => Ok(vec![sql.clone()]),
126        Operation::RunRust { .. } => Ok(Vec::new()),
127    }
128}
129
130/// Whether the column type needs a prefix length to be keyed.
131fn is_unkeyable(field: &FieldState) -> bool {
132    match field.sql_type {
133        SqlType::Text => field.max_length.is_none(),
134        SqlType::Binary | SqlType::Json => true,
135        _ => false,
136    }
137}
138
139fn keyed_error(table: &str, column: &str, why: &str) -> MigrationError {
140    MigrationError::state(format!(
141        "MySQL cannot use column `{column}` of `{table}` as {why} without a length: \
142         give the field a `max_length` so it becomes a VARCHAR"
143    ))
144}
145
146/// Reject a key, unique or index over a column MySQL cannot key.
147fn ensure_keyable(model: &ModelState, column: &str, why: &str) -> Result<(), MigrationError> {
148    match model.column(column) {
149        Some(field) if is_unkeyable(field) => Err(keyed_error(&model.table, column, why)),
150        _ => Ok(()),
151    }
152}
153
154fn fk_name(table: &str, column: &str) -> String {
155    format!("{table}_{column}_fk")
156}
157
158fn create_model_sql(model: &ModelState) -> Result<Vec<String>, MigrationError> {
159    check_field_keys(model)?;
160    let mut parts = Vec::new();
161    for field in &model.fields {
162        parts.push(column_def(field, ColumnDef::Create)?);
163    }
164    for constraint in &model.constraints {
165        if let ConstraintState::Unique { columns, .. } = constraint {
166            for column in columns {
167                ensure_keyable(model, column, "part of a UNIQUE constraint")?;
168            }
169        }
170        parts.push(table_constraint_sql(constraint));
171    }
172    for field in &model.fields {
173        if let Some(fk) = &field.fk {
174            parts.push(foreign_key_clause(&model.table, &field.column, fk)?);
175        }
176    }
177    let mut stmts = vec![format!(
178        "CREATE TABLE {} (\n  {}\n)",
179        quote_ident(&model.table),
180        parts.join(",\n  ")
181    )];
182    stmts.extend(index_sqls(model)?);
183    Ok(stmts)
184}
185
186/// Primary keys, unique columns and single-column indexes must be keyable.
187fn check_field_keys(model: &ModelState) -> Result<(), MigrationError> {
188    for field in &model.fields {
189        check_field_key(&model.table, field)?;
190    }
191    Ok(())
192}
193
194fn check_field_key(table: &str, field: &FieldState) -> Result<(), MigrationError> {
195    if !is_unkeyable(field) {
196        return Ok(());
197    }
198    let why = if field.primary_key {
199        "a primary key"
200    } else if field.unique {
201        "UNIQUE"
202    } else if field.index {
203        "indexed"
204    } else {
205        return Ok(());
206    };
207    Err(keyed_error(table, &field.column, why))
208}
209
210fn table_constraint_sql(constraint: &ConstraintState) -> String {
211    match constraint {
212        ConstraintState::Unique { name, columns } => {
213            format!(
214                "CONSTRAINT {} UNIQUE ({})",
215                quote_ident(name),
216                column_list(columns)
217            )
218        }
219        ConstraintState::Check { name, sql } => {
220            format!("CONSTRAINT {} CHECK ({sql})", quote_ident(name))
221        }
222    }
223}
224
225fn column_list(columns: &[String]) -> String {
226    columns
227        .iter()
228        .map(|c| quote_ident(c))
229        .collect::<Vec<_>>()
230        .join(", ")
231}
232
233fn foreign_key_clause(
234    table: &str,
235    column: &str,
236    fk: &ForeignKeyState,
237) -> Result<String, MigrationError> {
238    Ok(format!(
239        "CONSTRAINT {} FOREIGN KEY ({}) REFERENCES {} ({}) ON DELETE {}",
240        quote_ident(&fk_name(table, column)),
241        quote_ident(column),
242        quote_ident(&fk.target_table),
243        quote_ident(&fk.target_column),
244        on_delete_sql(fk.on_delete, table, column)?
245    ))
246}
247
248fn on_delete_sql(
249    policy: OnDelete,
250    table: &str,
251    column: &str,
252) -> Result<&'static str, MigrationError> {
253    match policy {
254        OnDelete::Cascade => Ok("CASCADE"),
255        OnDelete::Protect => Ok("RESTRICT"),
256        OnDelete::SetNull => Ok("SET NULL"),
257        OnDelete::DoNothing => Ok("NO ACTION"),
258        OnDelete::SetDefault => Err(MigrationError::state(format!(
259            "MySQL (InnoDB) does not support ON DELETE SET DEFAULT (column `{column}` of `{table}`)"
260        ))),
261    }
262}
263
264/// Which clauses of a column definition to render.
265#[derive(Clone, Copy, PartialEq, Eq)]
266enum ColumnDef {
267    /// `CREATE TABLE` / `ADD COLUMN`: includes `UNIQUE` and `PRIMARY KEY`.
268    Create,
269    /// `MODIFY COLUMN`: type, nullability, default and `AUTO_INCREMENT` only.
270    Modify,
271}
272
273fn column_def(field: &FieldState, mode: ColumnDef) -> Result<String, MigrationError> {
274    let mut s = format!("{} {}", quote_ident(&field.column), type_sql(field));
275    if field.auto && field.primary_key {
276        s.push_str(" NOT NULL AUTO_INCREMENT");
277        if mode == ColumnDef::Create {
278            s.push_str(" PRIMARY KEY");
279        }
280        return Ok(s);
281    }
282    if !field.nullable {
283        s.push_str(" NOT NULL");
284    }
285    if let Some(default) = &field.default {
286        s.push_str(" DEFAULT ");
287        s.push_str(&default_sql(field, default));
288    }
289    if mode == ColumnDef::Create {
290        if field.unique && !field.primary_key {
291            s.push_str(" UNIQUE");
292        }
293        if field.primary_key {
294            s.push_str(" PRIMARY KEY");
295        }
296    }
297    Ok(s)
298}
299
300fn type_sql(field: &FieldState) -> String {
301    match field.sql_type {
302        SqlType::SmallInt => "SMALLINT".to_owned(),
303        SqlType::Integer => "INT".to_owned(),
304        SqlType::BigInt | SqlType::Duration => "BIGINT".to_owned(),
305        SqlType::Real => "FLOAT".to_owned(),
306        SqlType::Double => "DOUBLE".to_owned(),
307        SqlType::Decimal => match (field.max_digits, field.decimal_places) {
308            (Some(p), Some(s)) => format!("DECIMAL({p},{s})"),
309            _ => "DECIMAL(38,10)".to_owned(),
310        },
311        SqlType::Bool => "TINYINT(1)".to_owned(),
312        SqlType::Text => match field.max_length {
313            Some(n) => format!("VARCHAR({n})"),
314            None => "TEXT".to_owned(),
315        },
316        SqlType::Binary => "BLOB".to_owned(),
317        SqlType::Date => "DATE".to_owned(),
318        SqlType::Time => "TIME(6)".to_owned(),
319        SqlType::Timestamp => "DATETIME(6)".to_owned(),
320        SqlType::Uuid => "CHAR(36)".to_owned(),
321        SqlType::Json => "JSON".to_owned(),
322        SqlType::IpAddr => "VARCHAR(45)".to_owned(),
323    }
324}
325
326fn default_sql(field: &FieldState, default: &DbDefault) -> String {
327    let literal = match default {
328        DbDefault::Now => return "CURRENT_TIMESTAMP(6)".to_owned(),
329        DbDefault::Int(i) => return i.to_string(),
330        DbDefault::Bool(true) => return "TRUE".to_owned(),
331        DbDefault::Bool(false) => return "FALSE".to_owned(),
332        DbDefault::Text(s) => quote_string(s),
333    };
334    // TEXT, BLOB and JSON only accept an expression default: `DEFAULT ('x')`.
335    if is_unkeyable(field) {
336        format!("({literal})")
337    } else {
338        literal
339    }
340}
341
342fn index_sqls(model: &ModelState) -> Result<Vec<String>, MigrationError> {
343    let mut stmts = Vec::new();
344    let mut seen = BTreeSet::new();
345    for field in &model.fields {
346        if field.index && !field.unique && !field.primary_key {
347            let name = auto_index_name(&model.table, &field.column);
348            if seen.insert(name.clone()) {
349                let index = IndexState {
350                    name,
351                    columns: vec![field.column.clone()],
352                    unique: false,
353                };
354                stmts.push(create_index_sql(model, &index)?);
355            }
356        }
357    }
358    for index in &model.indexes {
359        if seen.insert(index.name.clone()) {
360            stmts.push(create_index_sql(model, index)?);
361        }
362    }
363    Ok(stmts)
364}
365
366fn create_index_sql(model: &ModelState, index: &IndexState) -> Result<String, MigrationError> {
367    for column in &index.columns {
368        ensure_keyable(model, column, "part of an index")?;
369    }
370    let unique = if index.unique { "UNIQUE " } else { "" };
371    Ok(format!(
372        "CREATE {unique}INDEX {} ON {} ({})",
373        quote_ident(&index.name),
374        quote_ident(&model.table),
375        column_list(&index.columns)
376    ))
377}
378
379fn drop_index_sql(table: &str, name: &str) -> String {
380    format!("DROP INDEX {} ON {}", quote_ident(name), quote_ident(table))
381}
382
383fn add_field_sql(model: &ModelState, field: &FieldState) -> Result<Vec<String>, MigrationError> {
384    check_field_key(&model.table, field)?;
385    let table = quote_ident(&model.table);
386    let mut stmts = vec![format!(
387        "ALTER TABLE {table} ADD COLUMN {}",
388        column_def(field, ColumnDef::Create)?
389    )];
390    if field.index && !field.unique && !field.primary_key {
391        let index = IndexState {
392            name: auto_index_name(&model.table, &field.column),
393            columns: vec![field.column.clone()],
394            unique: false,
395        };
396        stmts.push(create_index_sql(model, &index)?);
397    }
398    if let Some(fk) = &field.fk {
399        stmts.push(format!(
400            "ALTER TABLE {table} ADD {}",
401            foreign_key_clause(&model.table, &field.column, fk)?
402        ));
403    }
404    Ok(stmts)
405}
406
407fn require_field<'a>(model: &'a ModelState, name: &str) -> Result<&'a FieldState, MigrationError> {
408    model.field(name).ok_or_else(|| {
409        MigrationError::state(format!("field `{name}` not on model `{}`", model.name))
410    })
411}
412
413fn remove_field_sql(model: &ModelState, name: &str) -> Result<Vec<String>, MigrationError> {
414    let field = require_field(model, name)?;
415    let table = quote_ident(&model.table);
416    let mut stmts = Vec::new();
417    if field.fk.is_some() {
418        // The constraint must go before the column it covers.
419        stmts.push(format!(
420            "ALTER TABLE {table} DROP FOREIGN KEY {}",
421            quote_ident(&fk_name(&model.table, &field.column))
422        ));
423    }
424    stmts.push(format!(
425        "ALTER TABLE {table} DROP COLUMN {}",
426        quote_ident(&field.column)
427    ));
428    Ok(stmts)
429}
430
431/// Changes `AlterField` acts on: column name, type, nullability, default.
432/// Unique and foreign-key changes are not applied (as on PostgreSQL).
433fn alter_field_sql(
434    model: &ModelState,
435    name: &str,
436    field: &FieldState,
437) -> Result<Vec<String>, MigrationError> {
438    let old = require_field(model, name)?;
439    let table = quote_ident(&model.table);
440    let mut stmts = Vec::new();
441    if old.column != field.column {
442        stmts.push(format!(
443            "ALTER TABLE {table} RENAME COLUMN {} TO {}",
444            quote_ident(&old.column),
445            quote_ident(&field.column)
446        ));
447    }
448    let definition_changed = old.sql_type != field.sql_type
449        || old.max_length != field.max_length
450        || old.max_digits != field.max_digits
451        || old.decimal_places != field.decimal_places
452        || old.nullable != field.nullable
453        || old.default != field.default;
454    if definition_changed {
455        check_field_key(&model.table, field)?;
456        stmts.push(format!(
457            "ALTER TABLE {table} MODIFY COLUMN {}",
458            column_def(field, ColumnDef::Modify)?
459        ));
460    }
461    Ok(stmts)
462}
463
464fn rename_field_sql(
465    model: &ModelState,
466    old_name: &str,
467    new_name: &str,
468) -> Result<Vec<String>, MigrationError> {
469    let field = require_field(model, old_name)?;
470    if field.column == old_name && old_name != new_name {
471        return Ok(vec![format!(
472            "ALTER TABLE {} RENAME COLUMN {} TO {}",
473            quote_ident(&model.table),
474            quote_ident(&field.column),
475            quote_ident(new_name)
476        )]);
477    }
478    Ok(Vec::new())
479}
480
481fn add_constraint_sql(
482    model: &ModelState,
483    constraint: &ConstraintState,
484) -> Result<Vec<String>, MigrationError> {
485    if let ConstraintState::Unique { columns, .. } = constraint {
486        for column in columns {
487            ensure_keyable(model, column, "part of a UNIQUE constraint")?;
488        }
489    }
490    Ok(vec![format!(
491        "ALTER TABLE {} ADD {}",
492        quote_ident(&model.table),
493        table_constraint_sql(constraint)
494    )])
495}
496
497fn delete_constraint_sql(model: &ModelState, name: &str) -> Result<Vec<String>, MigrationError> {
498    let constraint = model.constraint(name).ok_or_else(|| {
499        MigrationError::state(format!("constraint `{name}` not on model `{}`", model.name))
500    })?;
501    Ok(vec![match constraint {
502        // A unique constraint is an index in MySQL.
503        ConstraintState::Unique { .. } => drop_index_sql(&model.table, name),
504        ConstraintState::Check { .. } => format!(
505            "ALTER TABLE {} DROP CHECK {}",
506            quote_ident(&model.table),
507            quote_ident(name)
508        ),
509    }])
510}
511
512#[cfg(test)]
513mod tests {
514    use super::*;
515
516    #[test]
517    fn identifiers_use_backticks() {
518        assert_eq!(quote_ident("users"), "`users`");
519        assert_eq!(quote_ident("we`ird"), "`we``ird`");
520    }
521
522    #[test]
523    fn strings_escape_quotes_and_backslashes() {
524        assert_eq!(quote_string("it's"), "'it''s'");
525        assert_eq!(quote_string(r"a\b"), r"'a\\b'");
526        assert_eq!(quote_string("a\0b"), "'a\\0b'");
527    }
528}
siderite_migrations/schema_editor/mysql.rs

siderite_migrations/schema_editor/
mysql.rs
