NAME

DBIO::SQLite::Diff - Compare two introspected SQLite models

VERSION

version 0.900007

DESCRIPTION

DBIO::SQLite::Diff compares two introspected SQLite database models (as produced by DBIO::SQLite::Introspect) and produces a list of structured diff operations. These operations can then be rendered to SQL or a human-readable summary.

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

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

What a name is

DBIO::SQLite::Introspect::Tables lists everything sqlite_master calls a table or a view under tables. Before anything is compared, the entries of both models are sorted by what they are:

  • A view -- kind => 'view'. It is created as a view and removed as a view, and replaced when its definition differs (DBIO::SQLite::Diff::View). No column op, index op or rebuild is ever written for one.

  • A virtual table -- its statement begins CREATE VIRTUAL TABLE; its kind is table, which is what sqlite_master says. One that is new is created from its statement and one that is gone is dropped by DROP TABLE. One that both models have and that is not the same in them -- its definition differs, compared like a view's, or it is a plain table on the other side -- is refused: SQLite cannot alter a virtual table, and a rebuild would lose what its module stores ("Changes that are refused" in DBIO::SQLite::Diff::Table). No column op, index op or rebuild is ever written for one either.

  • A plain table -- everything else, an entry without kind or without a statement included, as the models built by hand and by "target_from_compiled" have them. Only these are walked for columns and indexes, and only these are rebuilt.

One name can be a table on one side and a view on the other. The table is then dropped or created as a table and the view created or dropped as a view.

The order of the operations

1  DROP VIEW      views that go, and the old half of a replaced one
2  DROP INDEX     indexes that go, and the old half of a changed one
3  tables         CREATE TABLE, DROP TABLE, and the refusals
4  ADD COLUMN
5  rebuilds       each followed by the CREATE INDEX of its indexes
6  CREATE VIEW    new views, and the new half of a replaced one,
                  with the triggers it carries
7  DROP COLUMN
8  CREATE INDEX   new indexes, and the new half of a changed one

The order follows what SQLite checks (measured on libsqlite 3.51.1). ALTER TABLE ... DROP COLUMN reads the whole schema, not only its table: a view anywhere that names something that is gone fails it (error in view v: no such column), and so does an index over the column (error in index ... after drop column). So the views that go are dropped first, the index drops come before the column drops, and the views of the target are created before a column is dropped -- by then every table and column they name exists, and no view left in the database names anything that is about to go. CREATE TABLE, DROP TABLE, ADD COLUMN and CREATE VIEW read nothing but their own object, and a rebuild renames under PRAGMA legacy_alter_table, which reads nothing either. An index is not dropped on its own for a table that is dropped or rebuilt: it goes with the table.

When a table is rebuilt

SQLite's ALTER TABLE adds a column, drops one and renames. Everything else a table declares can only be changed by building the table anew, and a plain table that both models have is rebuilt as a whole by one DBIO::SQLite::Diff::Rebuild, which takes the place of that table's column ops, when any of this differs:

  • a column's type, NOT NULL or default ("What counts as a changed column" in DBIO::SQLite::Diff::Column);

  • a column is added that carries UNIQUE or PRIMARY KEY or is named in a table constraint ("Added columns" in DBIO::SQLite::Diff::Column);

  • the primary key -- its columns, in the order of the key;

  • the UNIQUE constraints -- the set of their column lists, as PRAGMA index_list reports them (the automatic indexes of origin u), written on a column or on the table alike;

  • the foreign keys -- the set of them, each as its columns, the table and columns it points at, and its ON UPDATE and ON DELETE. Names and actions are compared in any letter case; an action nobody declared is NO ACTION. It is a set: a key an older database holds twice is the key declared once, and the order of the lines is no difference. A key of the target over a column the source does not have is left out -- it arrives with the column, by the ADD COLUMN that carries its declaration or by the rebuild the column causes;

  • a CHECK, a column's COLLATE or a generated column's expression that the target states ("What the target states"). No PRAGMA reports these, so they are read out of the two CREATE TABLE statements: per column that both tables have, its CHECK clauses as a set, its collation and its generated clause, and the CHECK constraints of the table as a set. They are compared as written, with whitespace and comments set aside and the framing keywords and the name of a collation in any letter case; an expression is compared token for token. That is conservative on purpose: CHECK (qty>=0) against CHECK (qty >= 0) is a difference, the table is rebuilt once and is the same from then on, and a real change is never missed. A CHECK moved from the column to the table is a difference the same way;

  • WITHOUT ROWID or STRICT that the target has and the source has not.

The reasons found are kept on the op and shown by its summary:

~ table rebuild: item (foreign key (owner_id) -> owner (id) ON DELETE CASCADE added)

All of this needs the target table's CREATE TABLE statement, which is what the table is rebuilt from, so it happens for introspected models only. A model without the statement ("target_from_compiled", a model built by hand) holds no indexes and no foreign keys by construction; that says "not stated", not "none", and nothing of the above is compared for it. The clauses read from the statements are compared only where the source has a statement too.

If one of the two statements cannot be read clause by clause, the comparison is not passed over: the two statements are then compared as they stand, the table's name aside, and any difference rebuilds the table -- after which the live statement is the target's, and the two are the same.

What the target states

The target is the desired state, here as for columns and indexes. For a primary key, a UNIQUE and a foreign key that holds in both directions: a result class declares them (set_primary_key, add_unique_constraint, belongs_to and its is_foreign_key_constraint), so a target that has none says there is to be none, and a key or constraint only the source has goes with the rebuild.

A CHECK, a COLLATE, a generated column, WITHOUT ROWID and STRICT no result class can declare. The target DBIO::SQLite::Deploy compares with is deployed from result classes and never has one, so "the target has none" says "not stated" there, not "none wanted". These follow the desired-state contract core has for the fields of a column ("changed_fields" in DBIO::Diff::Compare, core ADR 0019): what the target leaves unstated is not compared, what it states is compared, and stated against missing is a change.

  • A column whose target has no CHECK is not compared in its CHECKs; one whose target has some is, set against set.

  • A column whose target has no COLLATE is not compared in its collation.

  • A column that is not generated in the target is not compared in its generated clause; one that is, is -- against a plain column, another expression or another storage.

  • The CHECK constraints of the table are not compared when the target has none. A target that has some has stated all of them: the sets are compared, and one only the source has goes.

  • WITHOUT ROWID and STRICT are a change when the target has the option and the source has not, and are not compared otherwise.

So a table made by hand with one of these, under a schema that describes the table, diffs to nothing, and an upgrade leaves it alone.

That is a statement about what causes a rebuild, not about what survives one. A table that is rebuilt, for whichever reason, is created from the target's CREATE TABLE statement, and whatever that statement does not say is gone afterwards -- a hand-made CHECK, COLLATE, generated column, WITHOUT ROWID or STRICT that the comparison had left alone included. That has been so since the first rebuild (ADR 0003); the comparison only does not cause a rebuild for it.

Triggers are the exception, and do survive. The rebuild is given what the source has on that table and the target cannot say: the triggers of the live table, from the triggers section of the source model (DBIO::SQLite::Introspect::Triggers), which it re-creates; a replaced view gets its triggers back the same way. Triggers are carried and never compared -- no op adds, drops or changes one. The columns a rebuild copies are those both sides have, without the ones the target generates.

Not compared, deliberately: AUTOINCREMENT (ADR 0004); ON CONFLICT clauses; DEFERRABLE and MATCH of a foreign key; the names of constraints; a collation or sort order inside the column list of a PRIMARY KEY or UNIQUE; and the order of the columns. A foreign key declared without its target columns (REFERENCES owner) is not the key declared with them (REFERENCES owner(id)): the table is rebuilt once to the target's spelling.

Changes that are refused

Without the statement a changed type, nullability or default cannot be carried out at all, and it is refused: as_sql throws, naming the table, the column, the change and the reason, and so does "apply" in DBIO::SQLite::Deploy -- before it has run a statement of the diff. has_changes and summary still answer. See "Changes that are refused" in DBIO::SQLite::Diff::Column. A changed virtual table is refused the same way, and so is a view the target lists without its CREATE VIEW statement ("A view without a statement" in DBIO::SQLite::Diff::View).

METHODS

target_from_compiled

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

Translates the neutral model from DBIO::Schema::ModelCompiler into the SQLite introspect-shaped model that diff consumes: native types land in data_type, with the length or precision of a type like CHAR(5) as size ("split_type_size" in DBIO::SQLite::Util), the way the introspected side reports it, and not_null is carried over as the compiled model states it.

It used to be zeroed on every primary-key column, on the grounds that "SQLite reports PK columns as notnull=0". That holds for the rowid alias and for a key column nobody declared NOT NULL; it does not hold for a key column that was declared NOT NULL, nor for a WITHOUT ROWID key, both of which the PRAGMA reports as notnull = 1. Zeroing them unconditionally made a table differ from the schema it had just been deployed from. The alias -- the one shape where the declaration and the truth really do come apart -- is reconciled where the database is, on the introspect side ("fetch" in DBIO::SQLite::Introspect::Columns), which is the only side that can ask it; nothing is left for this side to guess.

default_value is carried in the form the introspector reports it. A default given as a scalar ref is literal SQL, so the ref is resolved, and one pair of enclosing parentheses is taken off: a schema writes default_value => \"(datetime('now'))", DBIO::SQLite::DDL deploys it as written, and PRAGMA table_xinfo then answers datetime('now'). Left as the ref it reached the CREATE TABLE as DEFAULT SCALAR(0x...); left with its parentheses it would differ from the column it was deployed as. DBIO::SQLite::Diff::Table and DBIO::SQLite::Diff::Column put the parentheses back when they write the clause ("default_value_sql" in DBIO::SQLite::Util).

A default that is not a ref is a value, and is carried as the literal DBIO::SQLite::DDL writes for it ("default_literal_sql" in DBIO::SQLite::Util): in a column with TEXT affinity always as a string in apostrophes, with the apostrophes inside it doubled; in any other column a number bare and anything else as that string. The affinity is that of the column's native_type, where DBIO::SQLite::DDL asks the type it writes. default_value => "it's" is 'it''s' in the model and default_value => '007' on a text column is '007', which is what PRAGMA table_xinfo answers for the installed column. Passed on as the bare value it differed from that column, and a value of more than one word could not be written after DEFAULT at all.

pk_position is the column's place in the primary key as the result class declares it with set_primary_key -- 1 for the first key column, 0 for a column outside the key, the convention of "fetch" in DBIO::SQLite::Introspect::Columns. The key's order is not the order of the columns: set_primary_key('a', 'b') on columns declared b, a is the key (a, b).

The model has no sql for its tables, no indexes and no foreign keys, so a table that is new in it is rebuilt from its columns, with the limits "as_sql" in DBIO::SQLite::Diff::Table lists.

statements

my @statements = $diff->statements;

Every statement of the diff in the order it has to run, each one complete and closed by its semicolon. as_sql is this list joined by newlines.

Use it to run a diff. The text of as_sql cannot be cut back into statements at its semicolons: a CREATE TRIGGER that DBIO::SQLite::Diff::Rebuild carries has semicolons inside it, and so has any statement with one in a string. "apply" in DBIO::SQLite::Deploy runs what this returns.

An op contributes its own statements where it has that method ("statements" in DBIO::SQLite::Diff::Rebuild) and its as_sql otherwise, which for every other op of this driver is one statement. Like as_sql it throws for a change that is refused -- having run nothing, since it only renders.

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.