Thank you, Richard. I can understand how not naming the columns of a view can lead to ambiguities and other problems down the line. Hopefully the documentation will be updated so that users are aware that the alter table command can't really deal with cascading effects such as in the case of views referring to other views, etc.
Is there a way to unravel a complex schema so that you can identify which views are based directly on tables and which views refer to other views, and what those other views are? At this point, the simple solution seems to be to just use .dump to dump out the contents of the database into a text file, use search and replace to do the column rename, and then use .read to read it back into a SQLite database. Anything I have to watch out for if I do the above? Thank you. Balaji Ramanathan From: Richard Hipp <d...@sqlite.org> To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Cc: Bcc: Date: Mon, 26 Nov 2018 14:11:54 -0500 Subject: Re: [sqlite] Possible bug in Alter Table On 11/25/18, Balaji Ramanathan <balaji.ramanat...@gmail.com> wrote: > I expected the Alter Table command to find and replace all occurrences of > that column name in my schema with the new name. Here is simplified SQL that illustrates the problem: CREATE TABLE t1(x); CREATE VIEW v1 AS SELECT x FROM t1; CREATE VIEW v2 AS SELECT x FROM v1; ALTER TABLE t1 RENAME x TO y; The ALTER TABLE fails because after changing the name of t1.x to t1.y, the definition of the view v2 is no longer valid. This is not something we intend to "fix" in SQLite. The root of the problem is that the column names for the v1 view are not specified. And since they are not specified, that means SQLite is free to pick whatever arbitrary names it wants for those columns. The definition of view v2 depends on one particular algorithm for picking the column names of view v1, but there are no guarantees that every version of SQLite will use that particular algorithm. Hence, the definition of view v2 is under-specified and prone to failure, such as in this case. If you are careful to defined the names of all columns within your views, then the problem goes away. For example: CREATE TABLE t1(x); CREATE VIEW v1(y) AS SELECT x FROM t1; CREATE VIEW v2(z) AS SELECT y FROM v1; ALTER TABLE t1 RENAME x TO y; Or: CREATE TABLE t1(x); CREATE VIEW v1 AS SELECT x AS y FROM t1; CREATE VIEW v2 AS SELECT y AS z FROM v1; ALTER TABLE t1 RENAME x TO y; If you do not specify the names of columns in views, then SQLite is free to choose whatever names it wants for those columns, and the choices might shift after an ALTER TABLE, which could then break queries and/or downstream views. So it is best not to do that. Admittedly, this is not well-documented. I will strive to improve the documentation for the next release. Perhaps I will also add a "warning" mechanism to alert programmers to gotchas like this in some subsequent release, though there probably is not time to get warnings in to the forthcoming 3.26.0 release. -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
