NAME
DBIO::SQLite::Diff::Rebuild - Diff operation that rebuilds a SQLite table
VERSION
version 0.900007
DESCRIPTION
SQLite's ALTER TABLE cannot change a column's type, nullability, or default in place. The only way to apply such a change is the documented rebuild dance: create a new table with the desired shape under a temporary name, copy the surviving data across, drop the original, and rename the new table into its place.
DBIO::SQLite::Diff also emits it for a table that gains a column ADD COLUMN cannot express -- UNIQUE, PRIMARY KEY, or a part in a table constraint; see "Added columns" in DBIO::SQLite::Diff::Column -- and for a table whose primary key, UNIQUE constraints, foreign keys, CHECKs, collations, generated expressions, WITHOUT ROWID or STRICT differ between the two models, none of which ALTER TABLE can change. The whole list is under "When a table is rebuilt" in DBIO::SQLite::Diff. Whatever the cause, the op is the same; why it is there is in "reasons".
The new table is created from table_sql -- the target table's original CREATE TABLE statement as captured from sqlite_master during introspection -- with only the leading table-name token swapped for the temporary name. The name is found whichever way the statement quotes it -- bare, as a database deployed by an earlier version has it, in double quotes with a quote inside it doubled, in backticks, brackets or apostrophes ("create_table_name_index" in DBIO::SQLite::Util). Reusing the captured DDL keeps the rebuild faithful: PRIMARY KEY, AUTOINCREMENT, inline foreign keys, WITHOUT ROWID, STRICT, and column defaults all survive exactly as the desired schema declared them (reconstructing them from the column model would be lossy).
copy_columns are the columns whose data is carried over: those present in both the old and the new table, without the ones that are generated in the new table (GENERATED ALWAYS AS, VIRTUAL or STORED). SQLite refuses to write to a generated column, and there is nothing to carry: the new table computes it. A column that was generated and is a plain one now is copied -- its values are data from here on. Added columns take their default, dropped columns are left behind.
What hangs on the table
Dropping a table takes more with it than its rows, and renaming one is checked against the rest of the schema. The rebuild deals with each:
Indexes are dropped with the table. DBIO::SQLite::Diff re-emits
CREATE INDEXops for the table's target indexes after this op.Triggers on the table are dropped with it as well.
triggersholds theirCREATE TRIGGERstatements, as the live table has them (DBIO::SQLite::Introspect::Triggers), and the rebuild runs each again once the new table has the name. They come from the source because a schema cannot declare a trigger; they are carried, not changed -- also where one names a column the new table no longer has, which SQLite accepts when the trigger is created. Only triggers insqlite_masterare known: aTEMPtrigger on the table is lost.Views that select from the table, and triggers on other tables that name it, are not touched by the
DROP, but they stop theRENAME: SQLite checks the whole schema when it renames a table, and at that moment the view points at a table that is gone --error in view ...: no such table. The rebuild therefore runs underPRAGMA legacy_alter_table=ON. That pragma givesALTER TABLE ... RENAMEthe behaviour it had before libsqlite 3.25: the table gets its new name, and the rest of the schema is neither checked nor rewritten. Nothing needs rewriting here -- views and triggers name the table by the name it has again once the rename is done -- and so they work afterwards as before.Foreign keys of other tables into the table would fail the
DROP, which deletes the rows first. The rebuild runs withPRAGMA foreign_keys=OFF.
One unit
Between SAVEPOINT dbio_rebuild and RELEASE dbio_rebuild the rebuild is all or nothing: rolled back to the savepoint, the original table is there again with its rows, indexes and triggers. A savepoint and not BEGIN, so that it also nests in a transaction the caller has open.
The two pragmas stand outside it. PRAGMA foreign_keys does nothing while a transaction or savepoint is open, so it has to come first; and neither pragma is undone by a rollback. This op is text and knows nothing of the connection it will run on, so its last two statements are fixed -- legacy_alter_table=OFF, foreign_keys=ON, whatever the connection had -- and after a failure nothing of it has run past the failing statement. Both gaps are closed by who runs it: "apply" in DBIO::SQLite::Deploy rolls a failed rebuild back and leaves both settings as it found them. Whoever runs the text by other means has to do the same.
ATTRIBUTES
lossy_columns
An arrayref of the names of the columns whose change the rebuild applies and that may alter or lose stored values: a column dropped, or one whose change is lossy ("lossy" in DBIO::SQLite::Diff::Column). DBIO::SQLite::Diff fills it; the statements do not depend on it.
data_checks
An arrayref of { column => $name, kind => 'nulls' | 'added' }: what the rebuild asks of the rows the table holds. nulls is a column that becomes NOT NULL -- the copy writes its NULLs into it; added is a NOT NULL column without a default that the new table has and the old one had not -- a copied row would hold NULL in it. Either fails in SQLite for a table that has such a row. DBIO::SQLite::Diff fills it; "apply" in DBIO::SQLite::Deploy looks at the table before the first statement and refuses. The statements do not depend on it.
reasons
An arrayref of what the two models differ in on this table, each a short text: why the table is rebuilt. DBIO::SQLite::Diff fills it and "summary" shows it; the statements do not depend on it.
METHODS
lossy
True when "lossy_columns" names a column. The operation method of "destructive_operations" in DBIO::Diff::Base: the rebuild runs only with allow_destructive => 1 then ("apply" in DBIO::Deploy::Base).
statements
my @statements = $op->statements;
The statements of the rebuild in the order they have to run, each one complete and closed by its semicolon. For a table item with the columns id and name on both sides and one trigger:
PRAGMA foreign_keys=OFF;
PRAGMA legacy_alter_table=ON;
SAVEPOINT dbio_rebuild;
CREATE TABLE "item__dbio_rebuild" ( ... );
INSERT INTO "item__dbio_rebuild" ("id", "name")
SELECT "id", "name" FROM "item";
DROP TABLE "item";
ALTER TABLE "item__dbio_rebuild" RENAME TO "item";
CREATE TRIGGER item_ai AFTER INSERT ON item BEGIN ...; END;
RELEASE dbio_rebuild;
PRAGMA legacy_alter_table=OFF;
PRAGMA foreign_keys=ON;
Every name is quoted ("quote_identifier" in DBIO::SQLite::Util); what is between the parentheses of the CREATE TABLE is the captured statement, untouched, and so is each trigger statement. The INSERT is left out when there is no column to copy.
A trigger statement has semicolons inside it. That is why this is a list: the statements can be run one by one without anybody cutting text at semicolons.
as_sql
"statements", one after the other: a complete script, as the sqlite3 shell takes it, or $dbh->do with sqlite_allow_multiple_statements. It cannot be cut back into statements at its semicolons once a trigger is in it; ask "statements" for those.
summary
~ table rebuild: name, followed in parentheses by the "reasons" of the rebuild, separated by semicolons:
~ table rebuild: item (column val: NOT NULL no -> yes; foreign key (owner_id) -> owner (id) ON DELETE CASCADE added)
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.