SQLite Forum

DROP VIEW IF EXISTS failure
Login
> It's not an illusory problem. And of course I do not want (and never wanted and will never want) to have both a view and a table with the same name.

This is clear with your typo acknowledged and corrected. With the typo, it appeared you sought to solve an illusory problem.

> The use case ... is: ... \[dematerialization of a table that could have been a view in older version(s), with a new, like-named view substituting for the table\]

That makes sense, as a history. Thanks for something concrete to consider in contemplating this issue.

Now, instead of continuing the debate on what SQLite "ought" to do, or how it should be documented, I offer a solution which **might** satisfy your needs, if not your stated constraints:

There is a loadable extension which implements an eval() function. With that, in conjunction with some (or maybe a lot of) SQL which queries the sqlite_master table and builds DDL using string literals and concatenation, results passed into eval(), you could accomplish your schema change without writing any code beyond what the SQLite parser and eval() can accept.  To save time and space, I acknowledge that some SQLite feature change(s) could obviate such an approach. To the objection that sqlite_master is somewhat undocumented, I say: It has been around for years, and will certainly be present and usable for your yet-to-be-modernized databases. It serves as a simple substitute for a catalog as promoted/advocated by Codd long ago, and serves as a foundation for a set of "catalog" views written by Keith Medcalf and published here awhile back.

As it turns out, eval() wrapping a query that produces DDL does not work. This shell input:

```
create table ta (x);
create view v as select x as x from ta;
create index i on ta(x);
create trigger tr instead of insert on v begin insert into ta(x) values(new.x); end;

.load eval

select eval(group_concat(zap,';'))
 from ( select 'drop '||sm.type||' '||sm.name as zap from sqlite_master sm
   where sm.type in ('table','view','trigger','index') 
    and sm.name in ('ta','v','i','tr','giblets')
   order by sm.type='table',sm.type='view',sm.type='index',sm.type='trigger'
 );
```

calls eval() with a sequence of drop statements which, if passed to eval as a simple string literal, would drop the specified named objects in sensible order. However, the engine dislikes eval() trying to run DDL while a SELECT is running. This dislike is expressed via the complaint: "Error: database table is locked".

Of course, the scalar result from that same inner select could be collected and passed to sqlite3\_prepare\_statement() and executed. (It can be passed to eval() too, but with little purpose.)