SQLite Forum

DROP VIEW IF EXISTS failure
Login
I have been following this discussion as an amicus curiae. I might be able to help solve your problem, but I need some more information about your system as a whole.

Presumably, you are generating the SQL which contains "DROP VIEW IF EXISTS..." semi- or fully-automatic. otherwise, you would know what exists and what doesn't and the issue would be moot.

Presuming you are generating the SQL, is there a way to make that process smarter? For example, do you control of you database such that you could add your own (look-aside) table to track the existence of views and tables? And then use that information to generate the SQL. The generated SQL could update the look-aside table so the next time you generated the SQL, it could be smarter.

For example, here is some sample generated SQL:
```
CREATE TABLE IF NOT EXISTS lookaside (tname TEXT PRIMARY KEY, ttype TEXT CHECK(ttype in ('TABLE','VIEW')));
CREATE TABLE t1 (a TEXT, b INTEGER);
INSERT INTO lookaside (tname, ttype) VALUES('t1','TABLE');
CREATE VIEW v1 AS SELECT a FROM  t1;
INSERT INTO lookaside (tname, ttype) VALUES('v1','VIEW');
CREATE VIEW v2 AS SELECT b FROM  t1;
INSERT INTO lookaside (tname, ttype) VALUES('v2','VIEW');
DROP VIEW v2;
DELETE FROM lookaside WHERE tname='v2';   -- smart gen knows its a view
```
Your SQL generator can check "lookaside" to find out what exists and what doesn't and generate the correct SQL for each case.