zippa-db 0.1.0

//! The `ALTER TABLE` statements `SchemaView` generates for its column edits.
//!
//! Kept beside the view rather than in `db/`, same as `table_view/sql.rs`:
//! this is DDL generation for one screen, not a shared query path.

use crate::db::schema::{ColumnDef, ForeignKeyDef, IndexDef, ReferentialAction};
use crate::db::sql::quote_literal_for;
use crate::db::{DatabaseObject, Engine, quote_identifier};

/// One column row as the user has edited it, ready to be diffed against
/// `original` and turned into SQL.
#[derive(Debug, Clone)]
pub(crate) struct ColumnEdit {
    /// `None` for a column added by hand; it has nothing on the server yet.
    pub original: Option<ColumnDef>,
    pub name: String,
    pub type_name: String,
    pub nullable: bool,
    /// Raw expression text, bound for `DEFAULT` verbatim — same convention as
    /// `ColumnDef::default`.
    pub default: Option<String>,
    /// Only meaningful when `original` is `Some`: a dropped new column is
    /// simply removed from the row list instead, the way a table view's
    /// draft row is discarded rather than marked.
    pub dropped: bool,
}

impl ColumnEdit {
    pub(crate) fn is_new(&self) -> bool {
        self.original.is_none()
    }

    fn renamed(&self) -> bool {
        self.original.as_ref().is_some_and(|o| o.name != self.name)
    }

    fn retyped(&self) -> bool {
        self.original
            .as_ref()
            .is_some_and(|o| o.type_name != self.type_name)
    }

    fn nullability_changed(&self) -> bool {
        self.original
            .as_ref()
            .is_some_and(|o| o.nullable != self.nullable)
    }

    fn default_changed(&self) -> bool {
        self.original
            .as_ref()
            .is_some_and(|o| o.default != self.default)
    }

    /// Whether an existing, kept column has anything different about it.
    pub(crate) fn changed(&self) -> bool {
        !self.dropped
            && !self.is_new()
            && (self.renamed()
                || self.retyped()
                || self.nullability_changed()
                || self.default_changed())
    }

    /// Whether this kept column asks for something SQLite's own `ALTER TABLE`
    /// cannot express — a type, nullability, or default change — so the whole
    /// table has to be rebuilt. A rename does not count: SQLite has a native
    /// `RENAME COLUMN`.
    pub(crate) fn rebuilds(&self) -> bool {
        !self.dropped
            && !self.is_new()
            && (self.retyped() || self.nullability_changed() || self.default_changed())
    }
}

/// `object`, qualified and quoted, the way `TableView::target` reads it.
pub(crate) fn target(object: &DatabaseObject, engine: Engine) -> String {
    match &object.schema {
        Some(schema) => format!(
            "{}.{}",
            quote_identifier(schema, engine),
            quote_identifier(&object.name, engine)
        ),
        None => quote_identifier(&object.name, engine),
    }
}

/// A short, fixed list of common types for the column type dropdown.
///
/// Not exhaustive — every engine has far more — but enough for the common
/// case without standing up a searchable picker for a handful of choices.
pub(crate) fn common_types(engine: Engine) -> &'static [&'static str] {
    match engine {
        Engine::Postgres => &[
            "text",
            "varchar(255)",
            "integer",
            "bigint",
            "smallint",
            "boolean",
            "numeric",
            "real",
            "double precision",
            "date",
            "timestamp",
            "timestamptz",
            "uuid",
            "jsonb",
        ],
        Engine::MySql => &[
            "varchar(255)",
            "text",
            "int",
            "bigint",
            "smallint",
            "boolean",
            "decimal(10,2)",
            "float",
            "double",
            "date",
            "datetime",
            "timestamp",
            "json",
        ],
        // SQLite is dynamically typed; these are its five storage classes.
        Engine::Sqlite => &["TEXT", "INTEGER", "REAL", "NUMERIC", "BLOB"],
    }
}

/// `name type [NOT NULL] [DEFAULT expr]`, the shape an `ADD COLUMN` and every
/// engine's full column redefinition share.
pub(crate) fn column_clause(engine: Engine, edit: &ColumnEdit) -> String {
    let mut clause = quote_identifier(&edit.name, engine);
    // SQLite allows a column with no declared type, so the type is optional.
    if !edit.type_name.trim().is_empty() {
        clause.push(' ');
        clause.push_str(edit.type_name.trim());
    }
    if !edit.nullable {
        clause.push_str(" NOT NULL");
    }
    if let Some(default) = &edit.default {
        clause.push_str(" DEFAULT ");
        clause.push_str(default);
    }
    clause
}

/// Every statement `edits` calls for, in order: modify existing columns first
/// (matches `TableView::commit_rows`' update-then-insert-then-delete order),
/// then add the new ones, then drop the ones marked for it.
///
/// Postgres and MySQL use this directly; SQLite's rebuild path in `rebuild.rs`
/// bypasses it, so the `Engine::Sqlite` arm is only the generator's own
/// backstop. `Err` is a changed column left with an empty name, or one of
/// those SQLite changes plain `ALTER TABLE` cannot express.
pub(crate) fn generate_alter_statements(
    engine: Engine,
    target: &str,
    edits: &[ColumnEdit],
) -> Result<Vec<String>, String> {
    let mut statements = Vec::new();

    for edit in edits {
        if !edit.changed() {
            continue;
        }
        if edit.name.trim().is_empty() {
            return Err("a column needs a name".to_string());
        }
        statements.extend(modify_statements(engine, target, edit)?);
    }

    for edit in edits {
        if edit.is_new() && !edit.dropped && !edit.name.trim().is_empty() {
            statements.push(format!(
                "ALTER TABLE {target} ADD COLUMN {}",
                column_clause(engine, edit)
            ));
        }
    }

    for edit in edits {
        if edit.dropped
            && let Some(original) = &edit.original
        {
            statements.push(format!(
                "ALTER TABLE {target} DROP COLUMN {}",
                quote_identifier(&original.name, engine)
            ));
        }
    }

    Ok(statements)
}

fn modify_statements(
    engine: Engine,
    target: &str,
    edit: &ColumnEdit,
) -> Result<Vec<String>, String> {
    // `changed()` already established this.
    let original = edit
        .original
        .as_ref()
        .expect("a changed column has an original");

    match engine {
        Engine::Postgres => Ok(postgres_modify(target, original, edit)),
        Engine::MySql => mysql_modify(target, original, edit).map(|statement| vec![statement]),
        Engine::Sqlite => sqlite_modify(target, original, edit),
    }
}

/// Postgres: one statement per changed property, in an order where each later
/// one can still name the column by its current name.
fn postgres_modify(target: &str, original: &ColumnDef, edit: &ColumnEdit) -> Vec<String> {
    let engine = Engine::Postgres;
    let mut statements = Vec::new();

    if edit.renamed() {
        statements.push(format!(
            "ALTER TABLE {target} RENAME COLUMN {} TO {}",
            quote_identifier(&original.name, engine),
            quote_identifier(&edit.name, engine)
        ));
    }
    let current = quote_identifier(&edit.name, engine);

    if edit.retyped() {
        statements.push(format!(
            "ALTER TABLE {target} ALTER COLUMN {current} TYPE {} USING {current}::{}",
            edit.type_name, edit.type_name
        ));
    }
    if edit.nullability_changed() {
        let clause = if edit.nullable {
            "DROP NOT NULL"
        } else {
            "SET NOT NULL"
        };
        statements.push(format!(
            "ALTER TABLE {target} ALTER COLUMN {current} {clause}"
        ));
    }
    if edit.default_changed() {
        match &edit.default {
            Some(expr) => statements.push(format!(
                "ALTER TABLE {target} ALTER COLUMN {current} SET DEFAULT {expr}"
            )),
            None => statements.push(format!(
                "ALTER TABLE {target} ALTER COLUMN {current} DROP DEFAULT"
            )),
        }
    }

    statements
}

/// MySQL restates the whole column either way: `CHANGE COLUMN` for a rename
/// (it has no standalone rename), `MODIFY COLUMN` otherwise. There is no
/// narrower way to change one property, so whatever `type_name`/`nullable`/
/// `default` cannot express has to be folded back in from `original` or the
/// restate would silently drop it: `AUTO_INCREMENT`, a `TIMESTAMP`/
/// `DATETIME` column's own `ON UPDATE CURRENT_TIMESTAMP`, a comment, an
/// explicit collation (which also pins the character set — a collation
/// belongs to exactly one). A generated column's expression cannot be
/// restated safely this way, so an edit to one is refused instead of risking
/// a wrong `GENERATED ALWAYS AS (...)`.
fn mysql_modify(target: &str, original: &ColumnDef, edit: &ColumnEdit) -> Result<String, String> {
    let engine = Engine::MySql;
    let extra = &original.mysql_extra;
    if extra.generation_expression.is_some() {
        return Err(format!(
            "{} is a generated column; the schema editor cannot restate its \
             expression safely, so it cannot be edited here",
            original.name
        ));
    }

    let mut clause = column_clause(engine, edit);
    if extra.on_update_current_timestamp {
        clause.push_str(" ON UPDATE CURRENT_TIMESTAMP");
    }
    if extra.auto_increment {
        clause.push_str(" AUTO_INCREMENT");
    }
    if let Some(comment) = &extra.comment {
        clause.push_str(" COMMENT ");
        clause.push_str(&quote_literal_for(engine, comment));
    }
    if let Some(collation) = &extra.collation {
        clause.push_str(" COLLATE ");
        clause.push_str(collation);
    }

    Ok(if edit.renamed() {
        format!(
            "ALTER TABLE {target} CHANGE COLUMN {} {clause}",
            quote_identifier(&original.name, engine),
        )
    } else {
        format!("ALTER TABLE {target} MODIFY COLUMN {clause}")
    })
}

/// SQLite's plain `ALTER TABLE` only reaches a rename; anything else about an
/// existing column needs `rebuild.rs`'s copy-and-swap.
fn sqlite_modify(
    target: &str,
    original: &ColumnDef,
    edit: &ColumnEdit,
) -> Result<Vec<String>, String> {
    if edit.retyped() || edit.nullability_changed() || edit.default_changed() {
        return Err(format!(
            "changing {}'s type, nullability, or default is not supported on SQLite \
             without rebuilding the table",
            original.name
        ));
    }

    Ok(vec![format!(
        "ALTER TABLE {target} RENAME COLUMN {} TO {}",
        quote_identifier(&original.name, Engine::Sqlite),
        quote_identifier(&edit.name, Engine::Sqlite)
    )])
}

/// One index row as the user has edited it: a whole index added or a whole
/// one dropped — there is no "modify" here, unlike a column, since an index
/// is replaced rather than altered in place.
#[derive(Debug, Clone)]
pub(crate) struct IndexEdit {
    /// `None` for an index added by hand; it has nothing on the server yet.
    pub original: Option<IndexDef>,
    pub name: String,
    /// In the order the index searches them — composite index order changes
    /// what it can serve, so this is never sorted.
    pub columns: Vec<String>,
    pub unique: bool,
    /// A primary key add/drop takes its own statement shape (`ADD/DROP
    /// CONSTRAINT` on Postgres, `ADD/DROP PRIMARY KEY` on MySQL) rather than
    /// `CREATE`/`DROP INDEX`, and on SQLite either needs `rebuild.rs`'s
    /// copy-and-swap.
    pub primary_key: bool,
    /// Only meaningful when `original` is `Some`: a dropped new index is
    /// simply removed from the row list instead, the way a new column is.
    pub dropped: bool,
}

impl IndexEdit {
    pub(crate) fn is_new(&self) -> bool {
        self.original.is_none()
    }
}

/// `object`'s index `name`, schema-qualified the way Postgres needs it —
/// an index name is unique per-schema there, not per-table, so `DROP INDEX`
/// has to say which schema when it is not the default one. MySQL and SQLite
/// index names are already table-scoped or database-scoped, so this is a
/// no-op for them.
fn qualified_index_name(object: &DatabaseObject, engine: Engine, name: &str) -> String {
    match (engine, &object.schema) {
        (Engine::Postgres, Some(schema)) => {
            format!(
                "{}.{}",
                quote_identifier(schema, engine),
                quote_identifier(name, engine)
            )
        }
        _ => quote_identifier(name, engine),
    }
}

/// Every statement `edits` calls for: the new indexes first, then the
/// dropped ones — the same add-then-drop order `generate_alter_statements`
/// uses for columns.
///
/// `Err` for a primary key add or drop on SQLite, which `rebuild.rs` handles
/// instead, or an added index left with an empty name or no columns.
pub(crate) fn generate_index_statements(
    engine: Engine,
    object: &DatabaseObject,
    edits: &[IndexEdit],
) -> Result<Vec<String>, String> {
    let target = target(object, engine);
    let mut statements = Vec::new();

    for edit in edits {
        if !edit.is_new() || edit.dropped || edit.columns.is_empty() {
            continue;
        }
        if edit.name.trim().is_empty() {
            return Err("an index needs a name".to_string());
        }
        statements.push(add_index_statement(engine, &target, edit)?);
    }

    for edit in edits {
        if !edit.dropped {
            continue;
        }
        let Some(original) = &edit.original else {
            continue;
        };
        statements.push(drop_index_statement(engine, object, &target, original)?);
    }

    Ok(statements)
}

fn columns_list(engine: Engine, columns: &[String]) -> String {
    columns
        .iter()
        .map(|column| quote_identifier(column, engine))
        .collect::<Vec<_>>()
        .join(", ")
}

fn add_index_statement(engine: Engine, target: &str, edit: &IndexEdit) -> Result<String, String> {
    let name = quote_identifier(&edit.name, engine);
    let columns = columns_list(engine, &edit.columns);

    if edit.primary_key {
        return match engine {
            Engine::Postgres => Ok(format!(
                "ALTER TABLE {target} ADD CONSTRAINT {name} PRIMARY KEY ({columns})"
            )),
            Engine::MySql => Ok(format!("ALTER TABLE {target} ADD PRIMARY KEY ({columns})")),
            Engine::Sqlite => Err(
                "adding a primary key is not supported on SQLite without rebuilding the table"
                    .to_string(),
            ),
        };
    }

    Ok(match engine {
        Engine::Postgres | Engine::Sqlite if edit.unique => {
            format!("CREATE UNIQUE INDEX {name} ON {target} ({columns})")
        }
        Engine::Postgres | Engine::Sqlite => {
            format!("CREATE INDEX {name} ON {target} ({columns})")
        }
        Engine::MySql if edit.unique => {
            format!("ALTER TABLE {target} ADD UNIQUE {name} ({columns})")
        }
        Engine::MySql => format!("CREATE INDEX {name} ON {target} ({columns})"),
    })
}

fn drop_index_statement(
    engine: Engine,
    object: &DatabaseObject,
    target: &str,
    original: &IndexDef,
) -> Result<String, String> {
    if original.is_primary_key {
        return match engine {
            Engine::Postgres => Ok(format!(
                "ALTER TABLE {target} DROP CONSTRAINT {}",
                quote_identifier(&original.name, engine)
            )),
            Engine::MySql => Ok(format!("ALTER TABLE {target} DROP PRIMARY KEY")),
            Engine::Sqlite => Err(
                "dropping a primary key is not supported on SQLite without rebuilding the table"
                    .to_string(),
            ),
        };
    }

    Ok(match engine {
        Engine::Postgres | Engine::Sqlite => format!(
            "DROP INDEX {}",
            qualified_index_name(object, engine, &original.name)
        ),
        // Unlike the other two engines, a MySQL index name is scoped to its
        // table rather than its schema, so the drop has to say which table.
        Engine::MySql => format!(
            "DROP INDEX {} ON {target}",
            quote_identifier(&original.name, engine)
        ),
    })
}

/// One foreign key row: a whole relationship added or a whole one dropped,
/// never modified in place — same shape as `IndexEdit`, for the same reason.
#[derive(Debug, Clone)]
pub(crate) struct ForeignKeyEdit {
    /// `None` for a foreign key added by hand.
    pub original: Option<ForeignKeyDef>,
    pub name: String,
    /// Local columns, in the order they match up with `referenced_columns`.
    pub columns: Vec<String>,
    pub referenced_schema: Option<String>,
    pub referenced_table: String,
    pub referenced_columns: Vec<String>,
    pub on_delete: ReferentialAction,
    pub on_update: ReferentialAction,
    /// Only meaningful when `original` is `Some`: a dropped new foreign key
    /// is simply removed from the row list instead, the way a new index is.
    pub dropped: bool,
}

impl ForeignKeyEdit {
    pub(crate) fn is_new(&self) -> bool {
        self.original.is_none()
    }

    /// Whether this row asks for anything at all — a blank added row, with
    /// no table picked yet, asks for nothing.
    pub(crate) fn wants_a_statement(&self) -> bool {
        self.dropped
            || (self.is_new()
                && !self.columns.is_empty()
                && !self.referenced_table.trim().is_empty()
                && !self.referenced_columns.is_empty())
    }
}

/// Every statement `edits` calls for: the new relationships first, then the
/// dropped ones — same add-then-drop order as columns and indexes.
///
/// `Err` for any real add or drop on SQLite, which cannot change a foreign
/// key on an existing table at all without `rebuild.rs`'s copy-and-swap —
/// this is the backstop; a changed row left with an empty name is also
/// refused.
pub(crate) fn generate_foreign_key_statements(
    engine: Engine,
    object: &DatabaseObject,
    edits: &[ForeignKeyEdit],
) -> Result<Vec<String>, String> {
    if engine == Engine::Sqlite && edits.iter().any(ForeignKeyEdit::wants_a_statement) {
        return Err(
            "adding or dropping a foreign key is not supported on SQLite without rebuilding the table"
                .to_string(),
        );
    }

    let target = target(object, engine);
    let mut statements = Vec::new();

    for edit in edits {
        if !edit.is_new() || !edit.wants_a_statement() {
            continue;
        }
        if edit.name.trim().is_empty() {
            return Err("a foreign key needs a name".to_string());
        }

        let referenced = match &edit.referenced_schema {
            Some(schema) if engine == Engine::Postgres => format!(
                "{}.{}",
                quote_identifier(schema, engine),
                quote_identifier(&edit.referenced_table, engine)
            ),
            _ => quote_identifier(&edit.referenced_table, engine),
        };

        statements.push(format!(
            "ALTER TABLE {target} ADD CONSTRAINT {} FOREIGN KEY ({}) REFERENCES {referenced} ({}) \
             ON DELETE {} ON UPDATE {}",
            quote_identifier(&edit.name, engine),
            columns_list(engine, &edit.columns),
            columns_list(engine, &edit.referenced_columns),
            edit.on_delete.label(),
            edit.on_update.label(),
        ));
    }

    for edit in edits {
        if !edit.dropped {
            continue;
        }
        let Some(original) = &edit.original else {
            continue;
        };
        statements.push(match engine {
            Engine::Postgres => format!(
                "ALTER TABLE {target} DROP CONSTRAINT {}",
                quote_identifier(&original.name, engine)
            ),
            Engine::MySql => format!(
                "ALTER TABLE {target} DROP FOREIGN KEY {}",
                quote_identifier(&original.name, engine)
            ),
            // The guard above already refused any SQLite drop.
            Engine::Sqlite => unreachable!(),
        });
    }

    Ok(statements)
}

#[cfg(test)]
mod tests {
    use super::*;
    use crate::db::ObjectKind;
    use crate::db::schema::MySqlColumnExtra;

    fn column(name: &str, type_name: &str, nullable: bool, default: Option<&str>) -> ColumnDef {
        ColumnDef {
            name: name.to_string(),
            type_name: type_name.to_string(),
            nullable,
            default: default.map(str::to_string),
            is_primary_key: false,
            mysql_extra: Default::default(),
        }
    }

    fn kept(original: ColumnDef) -> ColumnEdit {
        ColumnEdit {
            name: original.name.clone(),
            type_name: original.type_name.clone(),
            nullable: original.nullable,
            default: original.default.clone(),
            original: Some(original),
            dropped: false,
        }
    }

    fn added(name: &str, type_name: &str, nullable: bool, default: Option<&str>) -> ColumnEdit {
        ColumnEdit {
            original: None,
            name: name.to_string(),
            type_name: type_name.to_string(),
            nullable,
            default: default.map(str::to_string),
            dropped: false,
        }
    }

    #[test]
    fn target_is_qualified_only_when_the_object_has_a_schema() {
        let table = DatabaseObject {
            schema: None,
            name: "items".into(),
            kind: ObjectKind::Table,
        };
        assert_eq!(target(&table, Engine::Postgres), "items");

        let qualified = DatabaseObject {
            schema: Some("app".into()),
            ..table
        };
        assert_eq!(target(&qualified, Engine::Postgres), "app.items");
    }

    #[test]
    fn postgres_generates_one_statement_per_changed_property() {
        let mut edit = kept(column("name", "text", true, None));
        edit.name = "full_name".to_string();
        edit.type_name = "varchar(255)".to_string();
        edit.nullable = false;
        edit.default = Some("'anon'".to_string());

        let statements =
            generate_alter_statements(Engine::Postgres, "items", &[edit]).expect("should generate");
        assert_eq!(
            statements,
            [
                "ALTER TABLE items RENAME COLUMN name TO full_name",
                "ALTER TABLE items ALTER COLUMN full_name TYPE varchar(255) USING full_name::varchar(255)",
                "ALTER TABLE items ALTER COLUMN full_name SET NOT NULL",
                "ALTER TABLE items ALTER COLUMN full_name SET DEFAULT 'anon'",
            ]
        );
    }

    #[test]
    fn postgres_add_and_drop_column() {
        let mut dropped = kept(column("score", "real", true, None));
        dropped.dropped = true;
        let new = added("rank", "integer", false, Some("0"));

        let statements = generate_alter_statements(Engine::Postgres, "items", &[dropped, new])
            .expect("should generate");
        assert_eq!(
            statements,
            [
                "ALTER TABLE items ADD COLUMN rank integer NOT NULL DEFAULT 0",
                "ALTER TABLE items DROP COLUMN score",
            ]
        );
    }

    #[test]
    fn mysql_renames_with_change_column_and_retypes_with_modify() {
        let mut renamed = kept(column("name", "varchar(255)", true, None));
        renamed.name = "full_name".to_string();

        let statements =
            generate_alter_statements(Engine::MySql, "items", &[renamed]).expect("should generate");
        assert_eq!(
            statements,
            ["ALTER TABLE items CHANGE COLUMN name full_name varchar(255)"]
        );

        let mut retyped = kept(column("score", "int", true, None));
        retyped.type_name = "bigint".to_string();
        retyped.nullable = false;

        let statements =
            generate_alter_statements(Engine::MySql, "items", &[retyped]).expect("should generate");
        assert_eq!(
            statements,
            ["ALTER TABLE items MODIFY COLUMN score bigint NOT NULL"]
        );
    }

    #[test]
    fn mysql_restate_preserves_auto_increment() {
        let original = ColumnDef {
            mysql_extra: MySqlColumnExtra {
                auto_increment: true,
                ..Default::default()
            },
            ..column("id", "int", false, None)
        };
        let mut retyped = kept(original);
        retyped.type_name = "bigint".to_string();

        let statements =
            generate_alter_statements(Engine::MySql, "items", &[retyped]).expect("should generate");
        assert_eq!(
            statements,
            ["ALTER TABLE items MODIFY COLUMN id bigint NOT NULL AUTO_INCREMENT"]
        );
    }

    #[test]
    fn mysql_restate_preserves_on_update_current_timestamp() {
        let original = ColumnDef {
            default: Some("CURRENT_TIMESTAMP".to_string()),
            mysql_extra: MySqlColumnExtra {
                on_update_current_timestamp: true,
                ..Default::default()
            },
            ..column("updated_at", "timestamp", false, Some("CURRENT_TIMESTAMP"))
        };
        let mut retyped = kept(original);
        retyped.type_name = "datetime".to_string();

        let statements =
            generate_alter_statements(Engine::MySql, "items", &[retyped]).expect("should generate");
        assert_eq!(
            statements,
            [
                "ALTER TABLE items MODIFY COLUMN updated_at datetime NOT NULL \
                 DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"
            ]
        );
    }

    #[test]
    fn mysql_restate_preserves_a_comment_and_escapes_it() {
        let original = ColumnDef {
            mysql_extra: MySqlColumnExtra {
                comment: Some("the user's name".to_string()),
                ..Default::default()
            },
            ..column("name", "varchar(50)", true, None)
        };
        let mut retyped = kept(original);
        retyped.type_name = "varchar(100)".to_string();

        let statements =
            generate_alter_statements(Engine::MySql, "items", &[retyped]).expect("should generate");
        assert_eq!(
            statements,
            ["ALTER TABLE items MODIFY COLUMN name varchar(100) COMMENT 'the user''s name'"]
        );
    }

    #[test]
    fn mysql_restate_preserves_an_explicit_collation() {
        let original = ColumnDef {
            mysql_extra: MySqlColumnExtra {
                collation: Some("utf8mb4_bin".to_string()),
                ..Default::default()
            },
            ..column("name", "varchar(50)", true, None)
        };
        let mut retyped = kept(original);
        retyped.type_name = "varchar(100)".to_string();

        let statements =
            generate_alter_statements(Engine::MySql, "items", &[retyped]).expect("should generate");
        assert_eq!(
            statements,
            ["ALTER TABLE items MODIFY COLUMN name varchar(100) COLLATE utf8mb4_bin"]
        );
    }

    #[test]
    fn mysql_refuses_to_edit_a_generated_column() {
        let original = ColumnDef {
            mysql_extra: MySqlColumnExtra {
                generation_expression: Some("(`price` * 0.1)".to_string()),
                ..Default::default()
            },
            ..column("tax", "decimal(10,2)", true, None)
        };
        let mut retyped = kept(original);
        retyped.type_name = "decimal(12,2)".to_string();

        let error = generate_alter_statements(Engine::MySql, "items", &[retyped])
            .expect_err("a generated column should be refused");
        assert!(error.contains("tax"), "{error}");
        assert!(error.contains("generated"), "{error}");
    }

    #[test]
    fn sqlite_allows_a_rename_but_refuses_a_retype() {
        let mut renamed = kept(column("name", "TEXT", true, None));
        renamed.name = "full_name".to_string();

        let statements = generate_alter_statements(Engine::Sqlite, "items", &[renamed])
            .expect("a rename alone should generate");
        assert_eq!(
            statements,
            ["ALTER TABLE items RENAME COLUMN name TO full_name"]
        );

        let mut retyped = kept(column("score", "INTEGER", true, None));
        retyped.type_name = "REAL".to_string();

        let error = generate_alter_statements(Engine::Sqlite, "items", &[retyped])
            .expect_err("a retype should be refused");
        assert!(error.contains("score") && error.contains("rebuilding"));
    }

    #[test]
    fn a_changed_column_cannot_be_renamed_to_nothing() {
        let mut edit = kept(column("name", "text", true, None));
        edit.name = String::new();

        let error = generate_alter_statements(Engine::Postgres, "items", &[edit])
            .expect_err("an empty name should be refused");
        assert!(error.contains("name"));
    }

    #[test]
    fn a_blank_added_row_generates_nothing() {
        let statements =
            generate_alter_statements(Engine::Postgres, "items", &[added("", "text", true, None)])
                .expect("should generate");
        assert!(statements.is_empty());
    }

    #[test]
    fn nothing_changed_generates_nothing() {
        let statements = generate_alter_statements(
            Engine::Postgres,
            "items",
            &[kept(column("name", "text", true, None))],
        )
        .expect("should generate");
        assert!(statements.is_empty());
    }

    fn table(schema: Option<&str>) -> DatabaseObject {
        DatabaseObject {
            schema: schema.map(str::to_string),
            name: "items".into(),
            kind: ObjectKind::Table,
        }
    }

    fn existing_index(name: &str, columns: &[&str], unique: bool, primary_key: bool) -> IndexDef {
        IndexDef {
            name: name.to_string(),
            columns: columns.iter().map(|c| c.to_string()).collect(),
            unique,
            is_primary_key: primary_key,
        }
    }

    fn kept_index(original: IndexDef) -> IndexEdit {
        IndexEdit {
            name: original.name.clone(),
            columns: original.columns.clone(),
            unique: original.unique,
            primary_key: original.is_primary_key,
            original: Some(original),
            dropped: false,
        }
    }

    fn added_index(name: &str, columns: &[&str], unique: bool, primary_key: bool) -> IndexEdit {
        IndexEdit {
            original: None,
            name: name.to_string(),
            columns: columns.iter().map(|c| c.to_string()).collect(),
            unique,
            primary_key,
            dropped: false,
        }
    }

    #[test]
    fn postgres_creates_and_drops_a_plain_index() {
        let new = added_index("items_name_idx", &["name"], false, false);
        let statements = generate_index_statements(Engine::Postgres, &table(None), &[new])
            .expect("should generate");
        assert_eq!(statements, ["CREATE INDEX items_name_idx ON items (name)"]);

        let mut dropped = kept_index(existing_index("items_name_idx", &["name"], false, false));
        dropped.dropped = true;
        let statements = generate_index_statements(Engine::Postgres, &table(None), &[dropped])
            .expect("should generate");
        assert_eq!(statements, ["DROP INDEX items_name_idx"]);
    }

    #[test]
    fn postgres_drop_index_is_schema_qualified_off_the_default_schema() {
        let mut dropped = kept_index(existing_index("items_name_idx", &["name"], false, false));
        dropped.dropped = true;
        let statements =
            generate_index_statements(Engine::Postgres, &table(Some("app")), &[dropped])
                .expect("should generate");
        assert_eq!(statements, ["DROP INDEX app.items_name_idx"]);
    }

    #[test]
    fn postgres_unique_index_and_primary_key_constraint() {
        let unique = added_index("items_name_key", &["name"], true, false);
        let statements = generate_index_statements(Engine::Postgres, &table(None), &[unique])
            .expect("should generate");
        assert_eq!(
            statements,
            ["CREATE UNIQUE INDEX items_name_key ON items (name)"]
        );

        let pk = added_index("items_pkey", &["id"], false, true);
        let statements = generate_index_statements(Engine::Postgres, &table(None), &[pk])
            .expect("should generate");
        assert_eq!(
            statements,
            ["ALTER TABLE items ADD CONSTRAINT items_pkey PRIMARY KEY (id)"]
        );

        let mut dropped = kept_index(existing_index("items_pkey", &["id"], true, true));
        dropped.dropped = true;
        let statements = generate_index_statements(Engine::Postgres, &table(None), &[dropped])
            .expect("should generate");
        assert_eq!(statements, ["ALTER TABLE items DROP CONSTRAINT items_pkey"]);
    }

    #[test]
    fn mysql_index_drop_names_its_table_since_names_are_table_scoped() {
        let new = added_index("items_name_idx", &["name"], false, false);
        let statements = generate_index_statements(Engine::MySql, &table(None), &[new])
            .expect("should generate");
        assert_eq!(statements, ["CREATE INDEX items_name_idx ON items (name)"]);

        let unique = added_index("items_name_key", &["name"], true, false);
        let statements = generate_index_statements(Engine::MySql, &table(None), &[unique])
            .expect("should generate");
        assert_eq!(
            statements,
            ["ALTER TABLE items ADD UNIQUE items_name_key (name)"]
        );

        let mut dropped = kept_index(existing_index("items_name_idx", &["name"], false, false));
        dropped.dropped = true;
        let statements = generate_index_statements(Engine::MySql, &table(None), &[dropped])
            .expect("should generate");
        assert_eq!(statements, ["DROP INDEX items_name_idx ON items"]);
    }

    #[test]
    fn mysql_primary_key_add_and_drop_have_no_name() {
        let pk = added_index("primary", &["id"], false, true);
        let statements =
            generate_index_statements(Engine::MySql, &table(None), &[pk]).expect("should generate");
        assert_eq!(statements, ["ALTER TABLE items ADD PRIMARY KEY (id)"]);

        let mut dropped = kept_index(existing_index("PRIMARY", &["id"], true, true));
        dropped.dropped = true;
        let statements = generate_index_statements(Engine::MySql, &table(None), &[dropped])
            .expect("should generate");
        assert_eq!(statements, ["ALTER TABLE items DROP PRIMARY KEY"]);
    }

    #[test]
    fn sqlite_allows_a_plain_index_but_refuses_a_primary_key() {
        let new = added_index("items_name_idx", &["name"], true, false);
        let statements = generate_index_statements(Engine::Sqlite, &table(None), &[new])
            .expect("should generate");
        assert_eq!(
            statements,
            ["CREATE UNIQUE INDEX items_name_idx ON items (name)"]
        );

        let pk = added_index("items_pkey", &["id"], false, true);
        let error = generate_index_statements(Engine::Sqlite, &table(None), &[pk])
            .expect_err("adding a primary key should be refused on SQLite");
        assert!(error.contains("rebuilding"));

        let mut dropped = kept_index(existing_index("items_pkey", &["id"], true, true));
        dropped.dropped = true;
        let error = generate_index_statements(Engine::Sqlite, &table(None), &[dropped])
            .expect_err("dropping a primary key should be refused on SQLite");
        assert!(error.contains("rebuilding"));
    }

    #[test]
    fn a_composite_index_keeps_its_column_order() {
        let new = added_index("items_name_score_idx", &["name", "score"], false, false);
        let statements = generate_index_statements(Engine::Postgres, &table(None), &[new])
            .expect("should generate");
        assert_eq!(
            statements,
            ["CREATE INDEX items_name_score_idx ON items (name, score)"]
        );
    }

    #[test]
    fn a_blank_added_index_generates_nothing() {
        let statements = generate_index_statements(
            Engine::Postgres,
            &table(None),
            &[added_index("", &[], false, false)],
        )
        .expect("should generate");
        assert!(statements.is_empty());
    }

    #[test]
    fn an_added_index_needs_a_name() {
        let error = generate_index_statements(
            Engine::Postgres,
            &table(None),
            &[added_index("", &["name"], false, false)],
        )
        .expect_err("an index with columns but no name should be refused");
        assert!(error.contains("name"));
    }

    fn added_fk(
        name: &str,
        columns: &[&str],
        referenced_schema: Option<&str>,
        referenced_table: &str,
        referenced_columns: &[&str],
    ) -> ForeignKeyEdit {
        ForeignKeyEdit {
            original: None,
            name: name.to_string(),
            columns: columns.iter().map(|c| c.to_string()).collect(),
            referenced_schema: referenced_schema.map(str::to_string),
            referenced_table: referenced_table.to_string(),
            referenced_columns: referenced_columns.iter().map(|c| c.to_string()).collect(),
            on_delete: ReferentialAction::NoAction,
            on_update: ReferentialAction::NoAction,
            dropped: false,
        }
    }

    fn existing_fk(
        name: &str,
        columns: &[&str],
        referenced_table: &str,
        referenced_columns: &[&str],
    ) -> ForeignKeyDef {
        ForeignKeyDef {
            name: name.to_string(),
            columns: columns.iter().map(|c| c.to_string()).collect(),
            referenced_schema: None,
            referenced_table: referenced_table.to_string(),
            referenced_columns: referenced_columns.iter().map(|c| c.to_string()).collect(),
            on_delete: ReferentialAction::NoAction,
            on_update: ReferentialAction::NoAction,
        }
    }

    fn kept_fk(original: ForeignKeyDef) -> ForeignKeyEdit {
        ForeignKeyEdit {
            name: original.name.clone(),
            columns: original.columns.clone(),
            referenced_schema: original.referenced_schema.clone(),
            referenced_table: original.referenced_table.clone(),
            referenced_columns: original.referenced_columns.clone(),
            on_delete: original.on_delete,
            on_update: original.on_update,
            original: Some(original),
            dropped: false,
        }
    }

    #[test]
    fn postgres_adds_a_foreign_key_with_its_actions() {
        let mut new = added_fk(
            "tagged_items_tag_id_fkey",
            &["tag_id"],
            None,
            "tags",
            &["id"],
        );
        new.on_delete = ReferentialAction::Cascade;
        new.on_update = ReferentialAction::Restrict;

        let statements = generate_foreign_key_statements(Engine::Postgres, &table(None), &[new])
            .expect("should generate");
        assert_eq!(
            statements,
            [
                "ALTER TABLE items ADD CONSTRAINT tagged_items_tag_id_fkey FOREIGN KEY (tag_id) \
                 REFERENCES tags (id) ON DELETE CASCADE ON UPDATE RESTRICT"
            ]
        );
    }

    #[test]
    fn postgres_qualifies_the_referenced_table_by_its_schema() {
        let new = added_fk("fk", &["tag_id"], Some("app"), "tags", &["id"]);
        let statements = generate_foreign_key_statements(Engine::Postgres, &table(None), &[new])
            .expect("should generate");
        assert_eq!(
            statements,
            ["ALTER TABLE items ADD CONSTRAINT fk FOREIGN KEY (tag_id) \
                 REFERENCES app.tags (id) ON DELETE NO ACTION ON UPDATE NO ACTION"]
        );
    }

    #[test]
    fn postgres_drops_a_foreign_key_by_constraint_name() {
        let mut dropped = kept_fk(existing_fk("fk", &["tag_id"], "tags", &["id"]));
        dropped.dropped = true;

        let statements =
            generate_foreign_key_statements(Engine::Postgres, &table(None), &[dropped])
                .expect("should generate");
        assert_eq!(statements, ["ALTER TABLE items DROP CONSTRAINT fk"]);
    }

    #[test]
    fn mysql_adds_and_drops_a_foreign_key() {
        let new = added_fk("fk", &["tag_id"], None, "tags", &["id"]);
        let statements = generate_foreign_key_statements(Engine::MySql, &table(None), &[new])
            .expect("should generate");
        assert_eq!(
            statements,
            ["ALTER TABLE items ADD CONSTRAINT fk FOREIGN KEY (tag_id) \
                 REFERENCES tags (id) ON DELETE NO ACTION ON UPDATE NO ACTION"]
        );

        let mut dropped = kept_fk(existing_fk("fk", &["tag_id"], "tags", &["id"]));
        dropped.dropped = true;
        let statements = generate_foreign_key_statements(Engine::MySql, &table(None), &[dropped])
            .expect("should generate");
        assert_eq!(statements, ["ALTER TABLE items DROP FOREIGN KEY fk"]);
    }

    #[test]
    fn sqlite_refuses_any_foreign_key_add_or_drop() {
        let new = added_fk("fk", &["tag_id"], None, "tags", &["id"]);
        let error = generate_foreign_key_statements(Engine::Sqlite, &table(None), &[new])
            .expect_err("adding a foreign key should be refused on SQLite");
        assert!(error.contains("rebuilding"));

        let mut dropped = kept_fk(existing_fk("fk", &["tag_id"], "tags", &["id"]));
        dropped.dropped = true;
        let error = generate_foreign_key_statements(Engine::Sqlite, &table(None), &[dropped])
            .expect_err("dropping a foreign key should be refused on SQLite");
        assert!(error.contains("rebuilding"));
    }

    #[test]
    fn a_blank_added_foreign_key_generates_nothing_even_on_sqlite() {
        let blank = added_fk("", &[], None, "", &[]);
        let statements = generate_foreign_key_statements(Engine::Sqlite, &table(None), &[blank])
            .expect("a blank row should not even trip the SQLite refusal");
        assert!(statements.is_empty());
    }

    #[test]
    fn an_added_foreign_key_needs_a_name() {
        let error = generate_foreign_key_statements(
            Engine::Postgres,
            &table(None),
            &[added_fk("", &["tag_id"], None, "tags", &["id"])],
        )
        .expect_err("a foreign key with columns but no name should be refused");
        assert!(error.contains("name"));
    }
}