/ 
DBIO-MySQL-0.900004
/ DBIO::MySQL::Diff

NAME

DBIO::MySQL::Diff - Compare two introspected MySQL/MariaDB models

VERSION

version 0.900004

DESCRIPTION

DBIO::MySQL::Diff compares two introspected MySQL/MariaDB models (as produced by DBIO::MySQL::Introspect) and produces a list of structured diff operations.

my $diff = DBIO::MySQL::Diff->new(
    source => $current_model,
    target => $desired_model,
);

if ($diff->has_changes) {
    print $diff->as_sql;
    print $diff->summary;
}

Operations are emitted in dependency order: the views that are dropped (or dropped to be created again with a new definition), the foreign keys that are dropped, tables, the indexes that are dropped, columns, the primary keys that are added or changed or dropped (DBIO::MySQL::Diff::PrimaryKey), the indexes that are created, the foreign keys that are added, the views that are created. A view is no table: it has operations of its own (DBIO::MySQL::Diff::View), is set aside from everything else, and a changed definition is a DROP VIEW and a CREATE VIEW around all of it. Views are created in the order of what they read, a view after the views it reads, and dropped in the reverse order, the same order "install_ddl" in DBIO::MySQL::DDL creates them in ("order_views" in DBIO::MySQL::DDL); views that read each other in a cycle are refused with a DBIO::Exception naming them. See "The order of the views" in DBIO::MySQL::Diff::View. The foreign keys that need the old primary key of a table as their index are among the dropped ones and among the added ones, around the key. An index is dropped while the columns it is over are all still there, and created once they are what the target says; an index that has changed is both, and its two statements stand on either side of the column changes. The server refuses a table, a column or an index that a constraint still needs, so the constraint goes first and comes last. Where a foreign key that stays needs an index that is dropped, the index that takes its place is created before the drop, and where a foreign key of a new table needs an index that is created by the same diff, it is added after it, and not in the CREATE TABLE. So is a foreign key of a new table whose columns an index the diff creates on that table covers: in the CREATE TABLE the server would make an index of its own for it, and the index of the diff would be a second one over the same columns. See DBIO::MySQL::Diff::Index.

An index that a foreign key needs, and that the target replaces by a primary key (the key of a child grows by the column of its foreign key, a UNIQUE index becomes the key), is counted by the target: the new key is the index that is left. The index goes after the key operation -- the foreign key needs its index at every moment. The one the server made for the constraint itself, named like it and over its columns, is dropped by the server the moment a key that can serve the constraint exists (a DROP INDEX after that finds nothing, measured on MySQL 8.0), so there the foreign key is dropped before the index and added again after the key.

Foreign key indexes and the second diff: install and upgrade give a constraint the fixed name <table>_<columns>_fk, and the server names the index it makes for a constraint after the constraint and uses an index that is there already (measured on MySQL 8.0 and MariaDB 11.8; a DROP FOREIGN KEY leaves the index). So the index of an installed table, the index the upgrade writes (CREATE INDEX before ADD CONSTRAINT) and the one the server makes are one index with one name, and the diff after an upgrade has nothing to say; the indexes of foreign keys are not left out of the comparison. A constraint the server named <table>_ibfk_N is dropped with its index and gets both under the fixed name. An AUTO_INCREMENT column that leaves the primary key has the UNIQUE index of the target created before the key (DBIO::MySQL::Diff::PrimaryKey).

The tables the diff creates come in foreign key order, a referenced table before the one that references it, as DBIO::MySQL::DDL creates them ("order_by_foreign_keys" in DBIO::MySQL::DDL); tables free of each other stay in name order. A foreign key that closes a cycle between new tables is added with ALTER TABLE after all tables and indexes.

The collation of a table that exists is compared (table_collation); a change is ALTER TABLE ... CONVERT TO, or a DBIO::Exception where the data would not provably survive, and the character set and collation of the columns of such a table are then not compared. Which cases convert and which are refused, with the measurements, is in "The collation of an existing table" in DBIO::MySQL::Diff::Table. A target from target_from_compiled carries no table collation and prescribes none.

Tables of other databases

A table named database.table is compared in its database. The model of a diff carries them apart from the tables of the current database, in other_databases, a hash of database name to a model of the same shape -- the tables of the schema that live there, and for the source nothing else of that database (DBIO::MySQL::Deploy builds both sides: the source introspected in the other database, the target in a temporary database made for it). Each such pair is diffed by a diff of its own, as the current database's is, and its operations come first in the list, each carrying database: the operation is written against `database`.`table`, and "summary" names the database. A table of that database the schema does not name is not in the models and is never dropped. target_from_compiled builds no such model: it leaves the table out.

METHODS

target_from_compiled

my $target = DBIO::MySQL::Diff->target_from_compiled($compiled_model);

Translates the neutral model from DBIO::Schema::ModelCompiler into the MySQL/MariaDB introspect-shaped model that diff consumes.

The compiled model uses native_type (as produced by DBIO::MySQL::Adapter, e.g. BIGINT, CHAR(32), TINYINT(1)). This method maps those into the exact lowercase data_type and parameterised column_type strings that DBIO::MySQL::Introspect::Columns returns from information_schema.columns.

character_set and collation are left undef on non-text columns (numeric, binary, datetime) to match what MariaDB/MySQL reports for those types. For text and char columns the server assigns a charset/collation from the database default; since a portable schema does not prescribe a charset, those fields are also left undef in the target so the diff engine sees both sides as empty and does not emit phantom changes. See ESCALATION NOTE in the source.

A table named database.table is a table of another database ("install_ddl" in DBIO::MySQL::DDL): the diff reads the current database alone, so the target leaves it out, and the live database is not asked about it. A name that names nothing (two dots, an empty half) and a view name with a dot are refused with a DBIO::Exception ("check_table_name" in DBIO::MySQL::Identifier).

summary

The summary of the operations, one line each; an operation on a table of another database is followed by (database NAME).

AUTHOR

DBIO & DBIx::Class Authors

COPYRIGHT AND LICENSE

Copyright (C) 2026 DBIO Authors Portions Copyright (C) 2005-2025 DBIx::Class Authors Based on DBIx::Class, heavily modified.

This is free software; you can redistribute it and/or modify it under the same terms as the Perl 5 programming language system itself.