NAME

DBIO::SQLite::Diff::Column - Diff operations for SQLite columns

VERSION

version 0.900007

DESCRIPTION

Represents a column-level diff operation in SQLite. Only ADD COLUMN is supported as a true ALTER -- SQLite has very limited ALTER TABLE: since 3.25 it can rename columns and since 3.35 it can drop columns, but type changes still require the create-new-table-and-copy dance.

For now this module emits:

  • ALTER TABLE ... ADD COLUMN ... for added columns, carrying the column as the target declares it -- see "Added columns"

  • ALTER TABLE ... DROP COLUMN ... for dropped columns (3.35+)

  • Nothing for a changed type, nullability or default -- a true in-place ALTER is impossible. When the target table's original CREATE statement is known, DBIO::SQLite::Diff hoists such a column into a whole-table DBIO::SQLite::Diff::Rebuild instead, and the op is never rendered. Where no captured DDL is available (the compiled-model path) there is nothing to rebuild the table from, and "as_sql" throws -- see "Changes that are refused".

Brand-new tables come with their columns, in the CREATE statement DBIO::SQLite::Diff::Table emits -- this module only sees columns of tables that exist in both source and target. DBIO::SQLite::Diff hands it the plain tables only: a view or a virtual table never gets a column op ("What a name is" in DBIO::SQLite::Diff).

A column's CHECK, COLLATE and generated expression are not among the fields compared here. DBIO::SQLite::Diff compares them, for a column both tables have, out of the two CREATE TABLE statements, and rebuilds the table when they differ ("When a table is rebuilt" in DBIO::SQLite::Diff). The reader of those statements is the one this module reads an added column's declaration with.

What counts as a changed column

A column has changed when its type, its NOT NULL, its default or its size differs between the two models. That is "changed_column_fields" in DBIO::Diff::Compare, which compares a default as the text PRAGMA table_xinfo reports, with one exception made here.

A default that is an integer, in a column that stores text, is the same default bare and in apostrophes. code VARCHAR(10) DEFAULT 10 and code VARCHAR(10) DEFAULT '10' give every new row the text 10: the column makes that text of the number. DBIO::SQLite::DDL wrote the first form until it began to write the default of such a column as a string literal ("default_literal_sql" in DBIO::SQLite::Util), so a database deployed before that holds the first and the schema now declares the second. Read as a change, every table with such a column would be rebuilt by the next upgrade, to arrive at a table that behaves as it did.

The two are taken for one default when all of this holds, whichever of the two models has which spelling:

  • the column has TEXT affinity in both models ("type_affinity" in DBIO::SQLite::Util of its data_type);

  • one default is an integer as SQLite itself writes one -- digits, a minus sign if negative, no leading zero, no plus sign, not -0 -- of at most 18 digits;

  • the other is exactly those characters between two apostrophes.

Nothing else is. DEFAULT 007 gives the text 7 and DEFAULT '007' the text 007; 1.50 arrives as 1.5; an integer past 64 bit is read as a floating point number, and 9223372036854775808 is stored as 9.22337203685478e+18. Those are two defaults each, and the rebuild that follows is what brings the declared one into an older database. Two cases store alike and are reported as changed all the same, for the sake of a rule that fits into one line: a decimal that is already in SQLite's own spelling (1.5), and an integer of 19 digits that still fits into 64 bit. In a column without TEXT affinity the rule does not apply at all.

A default the target does not give is a default to remove, where the target table has its CREATE TABLE statement -- the introspection of a deployed schema, which states every column in full. The core leaves a field the target leaves undefined alone; here a column without a default is a column without one, and a live default (other than NULL, which is none) is a change, the table is rebuilt and the new rows get no default. A target without the statement ("target_from_compiled" in DBIO::SQLite::Diff) prescribes no such thing, and the live default stays.

is_auto_increment is compared only where SQLite can express it. It is the INTEGER PRIMARY KEY of a table with a rowid; the id of a WITHOUT ROWID table is no alias of anything and DBIO::SQLite::Introspect::Columns does not report it. In a table that either model marks without_rowid a difference in is_auto_increment is therefore not a change, so a target that declares the key as auto increment does not rebuild a WITHOUT ROWID table that only the database has. In a table with a rowid it is compared like the rest.

Added columns

A column is more than the fields the model holds for it. PRAGMA table_xinfo reports a type, NOT NULL and a default; a REFERENCES clause with its ON DELETE, a CHECK, a COLLATE and a generated column's expression are not among them, and UNIQUE shows up only as an automatic index. An ADD COLUMN written from those fields adds a column without any of it, and the next diff sees nothing wrong, because it compares the same fields.

So where the target model has the table's CREATE TABLE statement -- the sql of sqlite_master, which every introspected model has -- the column's declaration is read out of that statement and the ADD COLUMN carries it as written. Comments are left out and whitespace between the words becomes one space; nothing else is changed.

ALTER TABLE ... ADD COLUMN cannot say everything a CREATE TABLE can. An add op has "needs_rebuild" set when it is one of these, and DBIO::SQLite::Diff then rebuilds the table from the target statement instead (ADR 0003):

  • the declaration carries UNIQUE or PRIMARY KEY, which SQLite refuses in an ADD COLUMN (Cannot add a UNIQUE column);

  • a table constraint names the column -- PRIMARY KEY (...), UNIQUE (...), CHECK (...), or the column list of a FOREIGN KEY (...). SQLite has no statement that adds a table constraint, and this is the form DBIO::SQLite::DDL writes every foreign key and every unique constraint in. The columns a FOREIGN KEY points at belong to the other table and do not count;

  • the declaration carries a DEFAULT that is not a constant: any parenthesised expression (DEFAULT (datetime('now'))) and CURRENT_TIME, CURRENT_DATE, CURRENT_TIMESTAMP. SQLite takes such an ADD COLUMN for an empty table and refuses it for one that holds rows (Cannot add a column with non-constant default), and a diff of two models does not know which table it has. The rebuild evaluates the default for every copied row. A literal -- number, string, NULL, TRUE, FALSE, a signed number -- is not such a default, nor is one of them alone in parentheses (DEFAULT (NULL)); those stay ADD COLUMN. The price is that an empty table is rebuilt too;

  • the declaration cannot be found in the statement. The form written from the fields is the one known to lose things, so it is not what a statement this module fails to read falls back to.

The names in a table constraint are matched as words, not understood: a CHECK that holds the column's name in a string literal rebuilds the table too. That direction is the harmless one.

What SQLite refuses only for a table that holds rows and a rebuild would not help is not in that list: a NOT NULL column without a default (the copy into the new table fails the same way), a REFERENCES column with a default other than NULL while foreign keys are enforced, and a STORED generated column. Those are emitted as ADD COLUMN, apply to an empty table and fail at SQLite for a populated one.

A model without the statement -- "target_from_compiled" in DBIO::SQLite::Diff, or one built by hand -- has only the fields, and its added columns are written from them: type, NOT NULL, DEFAULT. That is all such a model knows of a column, and it is not the column DBIO::SQLite::DDL declares for the same result class.

Changes that are refused

A change this module has no statement for is refused: "as_sql" throws, with a message that names the table, the column, what changed and why it cannot be done. It does not answer with an SQL comment. A comment is either run -- core splits a script at every semicolon, one inside a comment included -- or dropped, and then a diff that reports changes applies none and says nothing.

Two ops are refused:

DBIO::SQLite::Diff's as_sql renders every op, so one refused op refuses the whole diff, and "apply" in DBIO::SQLite::Deploy renders the diff before it runs the first statement: nothing has been applied when it throws. "summary" and has_changes are not affected -- what the diff contains can still be printed.

Changes that lose data

A changed column is lossy when the rebuild that applies it may alter or lose a stored value ("destructive_operations" in DBIO::Diff::Base): "lossy" answers it through "column_change_lossy" in DBIO::Diff::Compare, and a lossy change runs only with allow_destructive => 1 ("apply" in DBIO::Deploy::Base).

A change of the type name is lossless in SQLite only for the pairs of "lossless_type_pairs", which are the pairs of names with the same type affinity ("type_affinity" in DBIO::SQLite::Util). SQLite stores the value, not the column's type; the affinity is the one thing a column does to a value on the way in (https://www.sqlite.org/datatype3.html, section 3). A table rebuild copies the rows with INSERT ... SELECT, so every value passes the affinity of the new column once more, and for two names of one affinity that is the conversion it passed when it was stored: the value and its storage class come back as they were. The list holds these names, per affinity:

  • INTEGER: integer, int, bigint, smallint, tinyint, mediumint, int2, int8;

  • TEXT: text, varchar, char, character, nchar, nvarchar, clob;

  • REAL: real, double, double precision, float;

  • NUMERIC: numeric, decimal, boolean, date, datetime;

  • BLOB: blob, and the empty type.

Every other pair is lossy, a change of affinity first: INTEGER to REAL turns a large integer into a float that no longer holds it, any number to TEXT into its text, TEXT to INTEGER or NUMERIC into a number wherever the text reads as one, and REAL to INTEGER or NUMERIC may turn 1.0 into 1. A type name the list does not know is lossy as well: in doubt the change is not released. SQLite does not enforce a declared size, so a smaller size loses no value there, but the size is judged as "column_change_lossy" in DBIO::Diff::Compare does for any driver, and a shrinking size of a type is lossy.

ATTRIBUTES

definition

For an add op: what follows the column's name in the target table's CREATE TABLE statement -- type and constraints, as declared. Undefined when the target model has no such statement, or the column is not in it.

needs_rebuild

True for an add op that ALTER TABLE ... ADD COLUMN cannot express; see "Added columns". DBIO::SQLite::Diff turns the table into a DBIO::SQLite::Diff::Rebuild then. Rendered on its own, such an op is refused by "as_sql".

METHODS

lossless_type_pairs

my $pairs = DBIO::SQLite::Diff::Column->lossless_type_pairs;
$pairs->{'varchar->text'};    # 1

The hash reference of the pairs of type names a change of which loses no value, keys old->new in lower case, as "column_change_lossy" in DBIO::Diff::Compare takes it. See "Changes that lose data" for what is in it and why. A copy: changing it changes nothing.

lossy

my $bool = $op->lossy;

True for an alter op that may alter or lose a stored value, by "column_change_lossy" in DBIO::Diff::Compare with "lossless_type_pairs". False for any other action: an added column loses nothing, and a dropped one is destructive by its action. The operation method of "destructive_operations" in DBIO::Diff::Base.

diff

my @ops = DBIO::SQLite::Diff::Column->diff(
    $source_columns, $target_columns,
    $source_tables,  $target_tables,
);

Compares column lists for tables that exist in both source and target. Which columns come out as changed is described under "What counts as a changed column".

The target tables are also where an add op gets its "definition" and its "needs_rebuild" from: the sql of the table entry, when that is a CREATE TABLE statement.

The add ops of a table come in the order the target declares the columns, not sorted by name. ADD COLUMN appends, so that is the order the columns end up in, and a CHECK or a generated column may name a column declared before it, which has to exist by then.

as_sql

Returns the ALTER TABLE statement for this operation, and throws for a change no ALTER TABLE expresses ("Changes that are refused"):

Cannot change column t.c (type INTEGER -> TEXT, NOT NULL no -> yes):
SQLite has no ALTER COLUMN, and the target model holds no CREATE TABLE
statement to rebuild table t from

The message is one line; the parentheses hold each changed field with its old and its new value -- type, NOT NULL, default, size.

An added column with a "definition" is that definition behind ALTER TABLE ... ADD COLUMN name, the table and the column quoted ("quote_identifier" in DBIO::SQLite::Util) and the definition as the target declares it:

ALTER TABLE "item" ADD COLUMN "note" VARCHAR(40) DEFAULT 'none';

One with "needs_rebuild" set throws, like an alter: DBIO::SQLite::Diff never renders it, it emits the rebuild, and whoever renders the op alone gets no statement that would add half the column.

Without a definition -- a model that has no statement, see "Added columns" -- the column is written from its fields. Its DEFAULT is rendered by "default_value_sql" in DBIO::SQLite::Util, which puts an expression default back into the parentheses SQLite's grammar requires.

Either way the statement is valid; that does not make SQLite accept it for every table. Written from the fields, without a statement, a default that is not a constant -- a function call, 1+2, 'a' || 'b', and CURRENT_TIMESTAMP and its siblings too -- is refused for a table that holds rows (Cannot add a column with non-constant default) and accepted for an empty one. With a statement the table is rebuilt instead ("Added columns").

changes

my $text = $op->changes;    # 'type INTEGER -> TEXT, NOT NULL no -> yes'

For an alter op: each field that changed with its old and its new value, as the refusal of "as_sql" names them. DBIO::SQLite::Diff gives it to the DBIO::SQLite::Diff::Rebuild that takes the op's place, as the reason of the rebuild.

summary

One line naming the op -- +column, -column or ~column, table and column. It never throws, for an op "as_sql" refuses either.

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.