transform() should work for tables that are referenced by views

[simonw]

import sqlite_utils

db = sqlite_utils.Database(memory=True)
db["t"].insert({"id": 1, "name": "one"}, pk="id")
db.execute("create view v as select id, name from t")
db["t"].transform(rename={"name": "title"})
# sqlite3.OperationalError: error in view v: no such table: main.t

Same for the CLI:

sqlite-utils transform data.db t --rename name title

The fix may look like this:

CREATE TABLE "t_new_e47fb9e02388" (...);
INSERT INTO "t_new_e47fb9e02388" (...) SELECT ... FROM "t";
DROP TABLE "t";
PRAGMA legacy_alter_table=ON;
ALTER TABLE "t_new_e47fb9e02388" RENAME TO "t";
PRAGMA legacy_alter_table=OFF;

Initial suggestion by Fable 5

Confirmed with sqlite-utils 4.1.1 on SQLite 3.45.1. Every transform-based
operation is affected: retyping, renaming, dropping or reordering columns,
changing primary keys, and all foreign key modifications.

Downstream impact: Datasette's /db/table/-/alter API calls transform()
directly, so any Datasette table with a dependent view cannot be altered.
datasette-edit-schema worked around this by reading all dependent view
schemas, dropping the views, transforming, then recreating them inside one
transaction — that workaround should live here instead, so every consumer
of transform() benefits.

Root cause

transform_sql() generates statements ending in:

DROP TABLE "t";
ALTER TABLE "t_new_e47fb9e02388" RENAME TO "t";

Since SQLite 3.25, ALTER TABLE ... RENAME TO validates and rewrites
references to the renamed table in every view and trigger definition,
unless PRAGMA legacy_alter_table is ON. During that validation step SQLite
tries to resolve view v, which references "t" — just dropped — and the
whole statement fails with error in view v: no such table: main.t.

Second bug: keep_table= silently repoints views at the backup table

When the rename succeeds, SQLite's reference-rewriting causes a worse,
silent problem with keep_table:

db["t"].transform(types={"name": str}, keep_table="t_backup")
db.execute("select sql from sqlite_master where name = 'v'").fetchone()
# ('CREATE VIEW v as select id from "t_backup"',)

The ALTER TABLE "t" RENAME TO "t_backup" step rewrites every dependent
view to reference the backup table. Views now read from the frozen backup
copy instead of the live table, with no error and no warning. (Confirmed
against 4.1.1.)

Proposed change

Bracket the rename statements emitted by transform_sql() with the legacy
pragma, so table renames performed as an internal implementation detail of
transform() never touch view definitions:

Rules:

• Emit PRAGMA legacy_alter_table=ON; immediately before the first
  ALTER TABLE ... RENAME TO statement and PRAGMA legacy_alter_table=OFF;
  immediately after the last one. In the keep_table variant this brackets
  both renames (original table → backup name, new table → original name),
  which also fixes the view-repointing bug.
• Emit the pragmas from transform_sql() rather than handling them in
  transform(), so the documented "run these statements yourself" workflow
  and any future --sql output remain standalone-correct.
• No new parameters and no API changes. transform() executes the returned
  statements exactly as it does today; PRAGMA legacy_alter_table is a
  plain connection setting, legal inside an open transaction (verified —
  unlike PRAGMA foreign_keys it is not a no-op there), so both the normal
  path and the already_in_transaction path work unchanged.

Why not drop-and-recreate the views (the datasette-edit-schema approach)?

Recreating views produces the identical end state — CREATE VIEW does not
validate referenced tables or columns, so recreation always succeeds and a
view referencing a renamed column is equally broken either way. The pragma
approach never touches the view rows in sqlite_master at all, needs no
detection heuristic for "which views reference this table" (the plugin used
a substring match), preserves view creation order trivially, and is two
lines of SQL instead of a read-drop-recreate cycle.

Resulting semantics (to document)

• Views survive transform() with their SQL byte-for-byte unchanged.
• A view that references a column which the transform renamed or dropped
  remains defined but raises no such column when next queried. This is
  inherent to SQLite views (their SQL is stored as text); rewriting view
  definitions to track column renames is explicitly out of scope, though a
  future transform(update_views=True) could build on this.
• PRAGMA legacy_alter_table is reset to OFF (the SQLite default) rather
  than restored to a saved value. A caller who runs with legacy mode
  globally ON would have it switched off after a transform — acceptable and
  worth a one-line note in the docs.
• On SQLite versions older than 3.25 the pragma is unknown and unknown
  pragmas are silently ignored, so the emitted statements remain harmless
  no-ops there (that's also the era whose ALTER TABLE behavior we are
  opting into).

Tests

All in tests/test_transform.py:

1. transform(rename=...) on a table referenced by a view succeeds; view
   SQL in sqlite_master is unchanged; querying the view returns correct
   rows when the view's columns were untouched.
2. Same, for a transform that changes the primary key and one that modifies
   foreign keys (covers Datasette's main call sites).
3. A view referencing a renamed column: transform succeeds, querying the
   view raises OperationalError: no such column.
4. View-on-view chain (v2 selects from v1 selects from t): transform
   succeeds, both definitions unchanged, v2 still queryable.
5. keep_table="t_backup" with a dependent view: view still references
   "t" afterwards, not "t_backup" (regression test for the silent
   repointing bug).
6. transform_sql() output includes the two pragma statements in the right
   positions, and executing the returned statements manually (no
   transform() involved) succeeds against a database with a dependent view.
7. Transform inside an already-open transaction with a dependent view
   succeeds (exercises the defer_foreign_keys path together with the
   pragma).
8. Existing full test suite passes unchanged — no statements are altered
   for databases without views beyond the two added pragma lines.

Documentation

• docs/python-api.rst (python_api_transform): new short section "Tables
  referenced by views" describing the semantics above, including the
  renamed/dropped-column caveat and the legacy_alter_table reset note.
• docs/cli.rst: one-line mention under sqlite-utils transform.
• Changelog entry flagging the keep_table behavior change as a bug fix
  (views no longer silently follow the backup table).