Table rebuild that drops the old table before copying its rows

Critical — SQLite refuses the statement, so the migration fails here.

What it catches

The rebuild recipe is create, copy, drop, rename. This migration creates the new table, drops the old one and renames the new one onto its name, but never copies the rows across before the DROP. DROP TABLE discards them; the rename brings back the name with an empty table, and nothing in SQLite fails to say so.

Fires on

CREATE TABLE orders_new (id INTEGER PRIMARY KEY, qty INTEGER NOT NULL);
DROP TABLE orders;
ALTER TABLE orders_new RENAME TO orders;

Do this instead

BEGIN;
CREATE TABLE orders_new (id INTEGER PRIMARY KEY, qty INTEGER NOT NULL);
INSERT INTO orders_new SELECT id, qty FROM orders;
DROP TABLE orders;
ALTER TABLE orders_new RENAME TO orders;
COMMIT;