Hi all, People on this list, and the SQLite documentation, say that the .dump command of the CLI generates SQL which can be .read to rebuild a database. What is omitted is the fact that the SQL .read may fail if you have created views referring to other views and they are output in the SQL in the wrong order.
It seems that .dump emits SQL for tables, views, etc in the order they are defined. However, if you have created view A, then view B which refers to A, then drop A and recreate it, the dumped SQL contains the CREATE VIEW statement for B first, which fails as A is not yet defined. I have had this happen in the past, and verified it with a few lines of SQL in the 3.8.6 CLI. SQLite version 3.8.6 2014-08-15 11:46:33 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> CREATE TABLE x(a,b); sqlite> CREATE VIEW va as select a from x; sqlite> CREATE VIEW vb as select * from va; sqlite> .dump PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; CREATE TABLE x(a,b); CREATE VIEW va as select a from x; CREATE VIEW vb as select * from va; COMMIT; sqlite> drop view va; sqlite> CREATE VIEW va as select b from x; sqlite> .dump PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; CREATE TABLE x(a,b); CREATE VIEW vb as select * from va; CREATE VIEW va as select b from x; COMMIT; It is possible to get around this by editing the SQL file (best), or by saving the schema separately and reading it several times (with more and more 'table x already exists' error lines) until everything has been created. Any database which has had any maintenance done on its views using any GUI or CLI tool could manifest this scenario, and I think it should explicitly documented as fixing it is non-trivial. Len Chisholm. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
