SQLite Forum

Doc: »generalized ALTER TABLE procedure« treatment of VIEWs
Login

Doc: »generalized ALTER TABLE procedure« treatment of VIEWs

(1) By Bjoern Hoehrmann (bjoern) on 2021-06-12 23:48:07 [source]

In https://sqlite.org/lang_altertable.html#otheralter Step 3 is:

Remember the format of all indexes, triggers, and views associated with table X. This information will be needed in step 8 below. One way to do this is to run a query like the following: SELECT type, sql FROM sqlite_schema WHERE tbl_name='X'.

The suggested query does not actually identify the relevant VIEWs, and this fails to mention that the VIEWs (and probably the other objects aswell) have to be dropped, otherwise the ALTER TABLE in Step 7 will fail, since the referenced table is dropped in Step 6.

Steps 8 and 9 are:

Use CREATE INDEX, CREATE TRIGGER, and CREATE VIEW to reconstruct indexes, triggers, and views associated with table X. Perhaps use the old format of the triggers, indexes, and views saved from step 3 above as a guide, making changes as appropriate for the alteration.

If any views refer to table X in a way that is affected by the schema change, then drop those views using DROP VIEW and recreate them with whatever changes are necessary to accommodate the schema change using CREATE VIEW.

Given that VIEWs have to be dropped in Step 3 and are re-created with the appropriate changes in Step 8, it seems that Step 9 is redundant.

Context:

% sqlite
-- Loading resources from /home/bjoern/.sqliterc
SQLite version 3.36.0 2021-06-11 13:18:56
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> savepoint s;
sqlite> create table t as select 1 as col;
sqlite> create view v as select * from t, missing;
sqlite> alter table t rename to u;
(1) statement aborts at 26: [alter table t rename to u;] error in view v: no such table: main.missing
Error: error in view v: no such table: main.missing

(2) By Oleg Rudenko (Oleg.Rudenko) on 2022-09-17 09:12:34 in reply to 1 [link] [source]

Hi Bjoern,

I also ran into this problem with views while implementing the procedure. Just one note about other (indices and triggers) objects you mentioned:

The suggested query does not actually identify the relevant VIEWs, and this fails to mention that the VIEWs (and probably the other objects aswell) have to be dropped, otherwise the ALTER TABLE in Step 7 will fail, since the referenced table is dropped in Step 6.

They are dropped when you drop original table at step #6. The views are indeed preserved and they are not returned by the query from the procedure description.

--
Best regards,
    Oleg Rudenko