NAME
DBIO::MSSQL::Diff - Compare two introspected MSSQL models
VERSION
version 0.900007
DESCRIPTION
DBIO::MSSQL::Diff compares two introspected MSSQL models (produced by DBIO::MSSQL::Introspect) and emits a list of structured diff operations that can be rendered to SQL or a human-readable summary.
my $diff = DBIO::MSSQL::Diff->new(
source => $current_model,
target => $desired_model,
);
if ($diff->has_changes) {
print $diff->as_sql;
print $diff->summary;
}
Operations are emitted in an order the server accepts, not in the order the sub-diffs find them (the core walk sorts tables alphabetically, which is no dependency order). Foreign keys make the order matter, so the operations come in seven phases, after the CREATE SCHEMA operations of DBIO::MSSQL::Diff::Schema (a schema that a table schema.table of the classes needs and the database lacks) that come first (the primary keys of DBIO::MSSQL::Diff::PrimaryKey take part in two of them):
- 1. every
DROP CONSTRAINTof a foreign key -- also those of a dropped table, so tables that reference each other can be dropped (Msg 3726), and before a column they use is dropped or altered (Msg 5074). The same phase also takes the foreign keys that stand on a column the diff retypes (child or referenced column, a width change included: the server refusesALTER COLUMNwhile the key stands) and those that reference a key the diff drops, one rule for primary keys and unique keys alike; they are added again in phase 7 if the classes still declare them. The diff refuses when a declared foreign key would reference columns that no primary or unique key covers once the key is dropped; - 2. drops of indexes and unique constraints (before the columns they cover), then the drop of a primary key that differs from the one of the classes (a key the classes do not declare at all is dropped without being added), and the same is done around an
ALTER COLUMNto another type on a key column; - 3.
DROP TABLE; - 4. column operations -- an
ALTER COLUMNthat the server refuses while an index or unique constraint covers the column (NULL to NOT NULL, a type change; measured, see "DEPENDENT INDEXES AND UNIQUE CONSTRAINTS" in DBIO::MSSQL::Diff::Column) gets the drop of those indexes and constraints in phase 2 and their creation again in phase 6, unless the diff drops or creates them anyway; - 5.
CREATE TABLE, without foreign keys (Msg 1767: a table can only be referenced once it exists, and two tables can reference each other); - 6.
ADD CONSTRAINT ... PRIMARY KEYfor a table that exists (a column that becomes part of the key isNOT NULLby then, which the server demands), then creation of indexes and unique constraints (on new tables as well, and before a foreign key that references a unique constraint); - 7. every
ADD CONSTRAINTof a foreign key, new tables included.
Views are DBIO::MSSQL::Diff::View operations. The introspected model lists them in tables with kind => 'view' and their stored text in definition. A view the classes declare and the database lacks, or holds with another body (the text after AS, white space folded), is written CREATE OR ALTER VIEW after everything else (after the foreign keys: the tables, columns and keys it reads exist by then); a live view the classes do not declare is dropped with DROP VIEW right after the foreign key drops, before the tables go. Each statement is a batch of its own. A view gets no table, column or index operation (a CREATE TABLE under a view's name would block the view for good). Views that read other views are ordered by what they read (depends_on in the model, from sys.sql_expression_dependencies): written after the views they read, dropped before them; a cycle or an unknown view read is refused.
METHODS
_fold_names
my $target = $diff->_fold_names($diff->target);
When the source model says its database ignores case (collation with case_sensitive false, set by DBIO::MSSQL::Introspect), Foo and foo are one name to the server, and the diff must not see a table, column, index or foreign key as dropped and created for a change of spelling. This returns the target model with every name that matches a live name ignoring case written in the live spelling: table names, column names (also in index and foreign key column lists), index and constraint names, the table a foreign key references. Names the live side has no match for keep the spelling of the classes. A case-sensitive source, or one that names no collation, gives the target back unchanged: exact comparison, as before. The models handed in are not modified.
The database that is migrated decides, not the temporary one the target is introspected from (which is created with the collation of the live database, see "_create_temp_db" in DBIO::MSSQL::Deploy). A change of spelling alone is not carried out as a sp_rename: the server would accept it (measured), but the diff has never renamed anything, and an empty second diff is the goal.
SEE ALSO
DBIO::MSSQL::Introspect - produces the models this class compares
DBIO::MSSQL::Deploy - uses this class for upgrade diffs
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.