Use PRAGMA LEGACY_ALTER_TABLE=ON; The "Legacy alter table" does not require the database to be "valid/consistent" after executing the "alter table" command. The non-legacy (default) mode requires that the database be "transformed" into a "valid/consistent" state in order for the alter table command to be processed.
A side effect of this is that if the database was invalid BEFORE you issue the alter table command, and it is still invalid after, that in the "validity" checking mode (the default), the alter table command will not be processed (since it will throw an error that the transformation did not result in a "valid" schema). Correctly, you should either (a) drop view v1, or (b) create table t1 so that the database schema is consistent before you attempt to use ALTER TABLE. (Or, if you know that your schema is invalid, you can turn off validity checking with pragma LEGACY_ALTER_TABLE). Since creating a view is nothing more than storing a statement in the database, it is not checked when you create a view or drop a table that the schema is still valid (otherwise you would get a message of the form "Cannot drop table t1 because it is referenced in a view" or mayhaps view v1 would be dropped automatically). Perhaps a setting "ALLOW_INVALID_SCHEMA" needs to be added such that after each DDL statement the schema is checked for validity and if it is not valid then the DDL is tossed with an error (such as occurs in SQLFat databases)? The only possible inconsistency that I see is that: sqlite> create view v1 as select * from t1; sqlite> create table t2(x); sqlite> alter table t2 rename to t1; Error: error in view v1: no such table: main.t1 which means that you must use legacy_alter_table in order to be able to fix typo's ... The rename is prohibited because the schema is inconsistent BEFORE the change, even though it would be consistent AFTER the change ... --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-----Original Message----- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Tom Bassel >Sent: Friday, 3 May, 2019 12:51 >To: sqlite-users@mailinglists.sqlite.org >Subject: [sqlite] Error when renaming a table when an invalid view >exists in the schema > >Hello again everyone, > >I went through the ALTER TABLE docs but could not find anything >describing this behavior: > > >SQLite version 3.29.0 2019-04-27 20:30:19 >Enter ".help" for usage hints. >Connected to a transient in-memory database. >Use ".open FILENAME" to reopen on a persistent database. >sqlite> create table t1(f1); >sqlite> create view v1 as select * from t1; >sqlite> drop table t1; >sqlite> create table t2(f1); >sqlite> .schema >CREATE VIEW v1 as select * from t1; >CREATE TABLE t2(f1); >sqlite> alter table t2 rename to t20; >Error: error in view v1: no such table: main.t1 > > >That is, if an invalid view exists in the schema, then any attempt to >alter the name of another unrelated table will fail with the error >message that there is a view that references a different table that >does not exist. > >It also occurs in 3.27 but I downloaded and tried it on 3.29 just in >case something changed recently. > >Thanks >Tom >_______________________________________________ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
