Thanks Keith. LEGACY_ALTER_TABLE=ON lets me change the table name without
error. But the docs say "New applications should leave this flag turned off."
Is there any other way of checking if the schema is invalid besides attempting
to change the name of a table?
I went through the pragmas and commands below and they did not report that the
schema was invalid.
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> pragma integrity_check;
ok
sqlite> pragma quick_check;
ok
sqlite> pragma optimize;
sqlite> vacuum;
sqlite> pragma foreign_key_check;
sqlite> pragma database_list;
0|main|
1|temp|
sqlite> .lint
Usage lint sub-command ?switches...?
Where sub-commands are:
fkey-indexes
sqlite> .lint fkey-indexes
sqlite> alter table t2 rename to t20;
Error: error in view v1: no such table: main.t1
-------------------
Date: Fri, 03 May 2019 13:27:18 -0600
From: "Keith Medcalf" <kmedc...@dessus.com>
To: "SQLite mailing list" <sqlite-users@mailinglists.sqlite.org>
Subject: Re: [sqlite] Error when renaming a table when an invalid view
exists in the schema
Message-ID: <802bb8bcf08af448bd5d9a4b9bf2f...@mail.dessus.com>
Content-Type: text/plain; charset="utf-8"
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" in
to 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 c
ommand 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 tab
le 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 oc
curs in SQLFat databases)?
...
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
