NAME
DBIO::SQLite::Deploy - Deploy and upgrade SQLite schemas via test-deploy-and-compare
VERSION
version 0.900007
DESCRIPTION
DBIO::SQLite::Deploy orchestrates the deployment and upgrade of SQLite schemas using a test-deploy-and-compare strategy.
For upgrades, instead of computing diffs from abstract class representations, it:
- 1. Introspects the live database via
sqlite_masterand PRAGMAs - 2. Connects to a fresh in-memory SQLite database
- 3. Deploys the desired schema (from DBIO classes) into the in-memory DB
- 4. Introspects the in-memory database the same way
- 5. Computes the diff between the two models using DBIO::SQLite::Diff
The temp DB is in-memory and goes away when the connection drops -- much simpler than the temp-database approach used for PostgreSQL.
The orchestration (install, upgrade) and the diff shell come from DBIO::Deploy::Base; the engine-specific target-model build is overridden here, and the two hooks of "apply" in DBIO::Deploy::Base, which have a table rebuild to look after: "_check_before_apply" and "_execute_diff". The class has no apply and no upgrade of its own; both are inherited.
my $deploy = DBIO::SQLite::Deploy->new(
schema => MyApp::DB->connect("dbi:SQLite:dbname=app.db"),
);
# Fresh install
$deploy->install;
# Upgrade
$deploy->upgrade;
# Or in steps:
my $diff = $deploy->diff;
print $diff->summary;
$deploy->apply($diff) if $diff->has_changes;
Transactional DDL -- deliberately not used
SQLite has transactional DDL, and this deploy still does not use it. DBIO::SQLite::Storage declares _use_transactional_ddl(0), so "_execute_ddl" in DBIO::Deploy::Base runs the statements one at a time with AutoCommit on and core emits its one-time "non-transactional DDL" notice. Among the drivers answering 0 this is the only one where the engine is not the reason.
The reason is the rebuild. DBIO::SQLite::Diff::Rebuild brackets its create / copy / drop / rename dance with PRAGMA foreign_keys=OFF and PRAGMA foreign_keys=ON, and that pragma is a no-op inside a transaction: SQLite only lets foreign key enforcement be switched while no BEGIN or SAVEPOINT is pending, and it says so by ignoring the statement rather than by raising an error. Under a $storage->txn_do bracket the BEGIN comes first, both pragmas evaporate silently, and the DROP TABLE in the middle of the rebuild -- which performs an implicit DELETE FROM while enforcement is on -- fails with FOREIGN KEY constraint failed for any table another table references. SQLite's own rebuild recipe puts the OFF before the transaction and the restore after the commit, which is the ordering this declaration buys for free.
The notice core prints is accurate for the diff as a whole: an apply that fails half way leaves the statements before the failing op applied -- a table created, a column added, an earlier table rebuilt. Re-running upgrade is safe, because the diff is recomputed against live state and the second run picks up only what is left.
A single rebuild is not left half way. It runs between SAVEPOINT dbio_rebuild and RELEASE dbio_rebuild, and when one of its statements fails "_execute_diff" rolls back to the savepoint: the table is there as it was, with its rows, indexes and triggers, and nothing is left under the __dbio_rebuild name. The two pragmas of the bracket, which no rollback undoes, are put back by "_execute_diff" as well, to what the connection had before -- after a failure and after a success. So the rebuild is all or nothing, and the diff around it is not.
"_execute_diff" also supplies the last step of SQLite's recipe -- PRAGMA foreign_key_check once the rebuilt table is back in place, on a connection that enforces foreign keys.
Only the live connection is affected either way: "_build_target_model" deploys into a handle it opened itself, which _execute_ddl never brackets regardless of the capability.
METHODS
_build_target_model
Connect to a throwaway in-memory SQLite database, deploy the desired schema into it, and return its introspected model. The in-memory connection goes out of scope at the end of this method (and is disconnected explicitly for predictability), so the target DB is gone before the diff object is built.
install
$deploy->install;
Resolves the declared extensions first ("_resolve_extension_list"), so that a declaration that cannot be resolved dies before anything is written, then runs the inherited install.
_resolve_extension_list
my $list = $self->_resolve_extension_list; # DBIO::Extension::List, or undef
The declared list of extensions resolved to classes, without the database: the classes are loaded, their interface version and driver checked. undef when the schema declares none. A class that does not load, a wrong interface version and a class of another driver throw here (DBIO::Extension::List).
_extension_list
The hook of "_extension_list" in DBIO::Deploy::Base: the entries of "_resolve_extension_list", [] when the schema declares none. No installed state is attached ("Installed state").
_extension_driver_helpers
The helpers every extension gets as $ctx->{driver} ("Addresses").
_apply_extension_claims
$self->_apply_extension_claims($model, \@addresses);
Removes the claimed objects from the model with their cascade ("Claims and their cascade").
_execute_diff
$self->_execute_diff($diff);
The driver's way to run a diff that "apply" in DBIO::Deploy::Base has released and "_check_before_apply" has passed. It runs the statements of the diff ("statements" in DBIO::SQLite::Diff) against the connected database and returns true. apply and upgrade, with allow_destructive and dry_run, are inherited from DBIO::Deploy::Base.
All statements are rendered before the first one runs, and "_check_before_apply" renders them already, so a diff with a change that is refused ("Changes that are refused" in DBIO::SQLite::Diff::Column) throws with the database untouched.
They go through "_execute_ddl" in DBIO::Deploy::Base as one batch -- with one exception. That method takes text and cuts it into statements, and the cut is not right for every statement that holds a semicolon: a CREATE TRIGGER does, between the statements of its body, and a bare end or begin as a column name in it is read as the body's own. Such a statement is handed to $dbh->do whole, between the batch before it and the batch after it. A diff without one is a single _execute_ddl call.
For a diff that rebuilds a table (DBIO::SQLite::Diff::Rebuild) this method adds what the SQL text cannot know, the state of the connection:
PRAGMA foreign_keysandPRAGMA legacy_alter_tableare read before the first statement and put back to those values afterwards, whether the diff applied or failed. The rebuild switches both, and its text ends in a fixedlegacy_alter_table=OFFandforeign_keys=ON, which is not necessarily what the connection had.If a statement fails, the rebuild that was open is rolled back to its savepoint, so the table is there as before -- with its rows, indexes and triggers, and with no
__dbio_rebuildtable beside it. The error is thrown on as it came.If foreign keys were enforced before,
PRAGMA foreign_key_checkruns once the diff is applied, and its findings are thrown asForeign key violations after table rebuild: ..., naming the tables. The rebuild ran with enforcement off; this is the check SQLite's own recipe ends with. On a connection that does not enforce foreign keys a dangling row is the caller's declared state and no finding of this upgrade, so nothing is checked.
A diff without a rebuild gets none of this: no pragma is read or written.
_refuse_unfillable
$self->_refuse_unfillable($diff, $dbh);
Looks at the rows of the live tables before the first statement runs, and refuses -- a DBIO::Exception naming table and column -- what SQLite would refuse for them half way through the diff:
a
NOT NULLcolumn without a default (or withDEFAULT NULL) that the diff adds to a table that holds rows, byADD COLUMNor by a rebuild: SQLite has no value for the rows already there (Cannot add a NOT NULL column with default value NULL, orNOT NULL constraint failedin the copy of the rebuild);a column that becomes
NOT NULLin a table that has aNULLin it: the rebuild copies it into the new table and the constraint fails.
An empty table, and a table with no NULL in the column, pass: nothing is refused that SQLite would accept. The rebuild rolls itself back, but the ops before it in the diff are applied by then and the refusal leaves them alone.
_check_before_apply
$self->_check_before_apply($diff);
The check of "apply" in DBIO::Deploy::Base against the live tables, run once the diff is released and before the first statement. It renders the statements of the diff, so that a refused change throws here with the database untouched, and then runs "_refuse_unfillable".
DBIO::SQLite - schema component
DBIO::SQLite::DDL - generates DDL used by
installanddiffDBIO::SQLite::Introspect - reads live database state
DBIO::SQLite::Diff - compares two introspected models
DBIO::Deploy::Base - shared install/apply/upgrade orchestration
DBIO::PostgreSQL::Deploy - the PostgreSQL counterpart (temp-database variant)
EXTENSIONS
The database extensions a schema has are a declared list the deploy runs (ADR 0066 of DBIO core). This class is the driver's side of it; the protocol itself -- the phases, the merges and their refusals -- is core's ("diff" in DBIO::Deploy::Base, DBIO::Extension::Protocol), and the classes of the extensions are the family's (DBIO::Extension, DBIO::SQLite::Extension).
The form of the declaration
The list is "sqlite_extensions" in DBIO::SQLite and nothing else; an extension is not pulled in by loading a component.
__PACKAGE__->sqlite_extensions('fts5', 'rtree');
__PACKAGE__->sqlite_extensions(vec0 => { library => '/opt/sqlite/vec0.so' });
A name the family has a class for gets it from the fixed table of DBIO::SQLite::Extension (fts5, rtree); the option class names the class of an extension outside the family and wins; any other name gets the plain class, which has no rules for the model. The class is loaded and its interface version and driver checked when the list is resolved ("_resolve_extension_list"): at "install", and at diff.
Installed state
SQLite has no catalog of loaded extensions: a library is loaded into a connection, nothing of it is in the file. The entries of the list therefore carry no installed state (installed is false, version and schema absent). What the database says about an extension is the probe of the extension's own inspect ("inspect" in DBIO::SQLite::Extension::VirtualTable), which reads the module list and sqlite_master over the live connection; it is in $ctx->{self}{state} and $ctx->{peers}{$name}{state}, and in the model as $model->{extensions}{$name}, read-only. The deploy never creates, updates or drops an extension: a library is the connection's, and "_activate_extensions" in DBIO::SQLite::Storage loads it. The target model is built in a throw-away in-memory database that loads no library.
Addresses
An address names an object of the model. The deploy knows two kinds; an address of another kind is refused, naming it.
[ table => $name ] a table or view, by its name in the model
[ column => $table, $column ] one column of a table
The helpers in $ctx->{driver} build them: table($name), column($table, $column), and quote_ident.
Claims and their cascade
An extension claims an address when the object is its own and not the application's (FTS5, R*Tree: the shadow tables of a virtual table). Claiming removes the object from the model, on both sides of a diff, so the diff neither creates nor drops it. An address the model does not hold is passed over: claiming is idempotent.
A claimed table takes the table and its columns, indexes, foreign keys and triggers.
A claimed column takes the column.
What is not followed: a foreign key of another table that points at a claimed table. It stays as the database has it.
What a claim brings that the introspection does not already do: libsqlite 3.37+ lists the shadow tables of every virtual table as shadow in PRAGMA table_list and DBIO::SQLite::Introspect::Tables leaves them out of the model, so for FTS5 and R*Tree the claims find nothing left to take. They are the declared statement of the same fact -- by the name of the module, and without the pragma -- and what makes a third party's extension with shadow tables (spellfix1, a table-valued library) work the same way. On libsqlite before 3.37 the introspection refuses a database with a virtual table before any claim can run.
Annotations
This deploy has no attribute to add to an object of its model: an extension that answers annotate with a triple is refused (the default of "_apply_extension_annotations" in DBIO::Deploy::Base).
Verdicts
The diff of this driver asks no extension for a verdict.
Not here
Loading the libraries into a connection (library) and the storage layers an extension class names ("connection" in DBIO::SQLite) are the connect path's, not the deploy's.
SpatiaLite is not supported by a class of the family: mod_spatialite could not be loaded on the host this was written on, so no rule for its metadata tables (geometry_columns, spatial_ref_sys, ...) was measured. Declared as spatialite => { library => 'mod_spatialite' } it is loaded into every connection, and its tables are model tables to this deploy.
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.