SQLite: the table rebuild

SQLite can only ADD COLUMN (with restrictions), DROP COLUMN (with restrictions), RENAME COLUMN and RENAME TO. Everything else, such as changing a type, nullability, a default, any constraint (CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEY) or table options, needs the rebuild procedure from the SQLite documentation ("Making Other Kinds Of Table Schema Changes"). schemashift implements it.

When a table is rebuilt

A table is rebuilt when its changes include any of:

• an altered column (type, NOT NULL, default, generation),
• an added or dropped constraint,
• changed table options or AUTOINCREMENT,
• an added column that ADD COLUMN cannot express: PRIMARY KEY/UNIQUE, stored generated, NOT NULL without a constant default, or a non-constant default such as CURRENT_TIMESTAMP.

Otherwise it uses ALTER TABLE ADD/DROP COLUMN. All changes to one table, including column drops, are folded into a single rebuild.

Steps

For table T, between the plan's PRAGMA foreign_keys = OFF; BEGIN; and PRAGMA foreign_key_check; COMMIT;:

create table _ss_new_T (...definition from the target schema...);
insert into _ss_new_T (cols) select cols from T;
drop table T;
alter table _ss_new_T rename to T;
create index ...;            -- every index of the target table
create trigger ...;          -- every trigger of the target table (end of plan)

Surrounding steps in the same plan:

• Views that read T (transitively) and triggers that mention T are dropped first and recreated at the end. SQLite re-parses the whole schema on RENAME and fails if a view references the missing T, so they must not exist at that moment.
• Foreign keys are switched off for the duration (PRAGMA foreign_keys cannot change inside a transaction, so it is set before BEGIN and restored afterwards), otherwise DROP TABLE T would cascade or fail on child rows.
• After all steps PRAGMA foreign_key_check must return no rows; otherwise the transaction is rolled back and nothing changes.

Copying data

The copy list contains the target columns that exist in the source (after rename mapping) and are not generated. Special cases:

• A column that becomes NOT NULL and has a default is copied as coalesce(col, default), so existing NULLs get the default. Without a default, the plan carries a warning because the copy fails if NULLs exist (the transaction rolls back).
• A new column is not copied; it takes its default. A new NOT NULL column without a default produces a warning for the same reason.
• Dropped columns are simply not selected (hazard DATA_LOSS).

Worked example (v1 to v2)

invoices loses legacy_ref, gains currency, and its status CHECK gains 'void'. The CHECK change forces a rebuild:

create table _ss_new_invoices (
  id integer primary key autoincrement,
  org_id integer not null,
  number text not null,
  status text not null default 'draft',
  currency text not null default 'USD',
  total_cents integer not null default 0,
  issued_at text,
  check (status in ('draft', 'sent', 'paid', 'void')),
  foreign key (org_id) references organizations (id)
);
insert into _ss_new_invoices (id, org_id, number, status, total_cents, issued_at)
  select id, org_id, number, status, total_cents, issued_at from invoices;
drop table invoices;
alter table _ss_new_invoices rename to invoices;
create index idx_invoices_open on invoices (org_id, issued_at desc) where status not in ('paid', 'void');
create unique index idx_invoices_org_number on invoices (org_id, number);

line_items references invoices; its FK text still says references "invoices" and resolves to the new table after the rename. The invoice_totals view and the trg_invoices_status trigger were dropped before and are recreated after.

What the tests check

tests/test_sqlite_apply.py runs every case in both directions plus the down migration, and a data test seeds rows, migrates v1 to v2 and verifies rows, FK definitions, indexes, views, the recreated trigger firing, cascade behaviour and an empty foreign_key_check.