[sqlite] DROP statement on Virtual Tables

[Samuel Debionne]

Thank you for your thoroughful answers !

Following your advices, I have split XCreate and xConnect
implementations, the first enforces the existence of the resource while
the later returns SQLITE_OK even if the resource is missing.

> The proper place to implement handling a missing backing storage file
> is xOpen (for SELECT) and xUpdate (for INSERT/UPDATE/DELETE). You
> choose to either return an error there, or silently provide no rows
> on SELECT and ignore INSERTs.

Well, it seems that xBestIndex is called first (for SELECT). If I return
SQLITE_IOERR from xBestIndex, SQLite crashes. xConnect requires that
ppVTab is allocated,  initialized and a dummy vtab schema should be
declared :

sqlite3_declare_vtab(db, "CREATE TABLE missing_vt(uid INTEGER)");

Something similar should probably be done for xBestIndex and the
sqlite3_index_info structure. But this is really confusing the
implementation...

> 3) pragma writeable_schema; delete from sqlite3_master where 
> name='mycsv';

This may be the best option actually ! I think I will go for it and add
a ".drop VTABLE" command to my shell...

It would be great to have better support for this scenario: if the
statement is a DROP TABLE on a virtual table, allows xConnect to fail
and remove the table from sqlite3_master.

[sqlite] DROP statement on Virtual Tables

[Samuel Debionne]

Hello,

AFAIU, when executing a drop statement on a virtual table, the XConnect
callback is executed first, then XDestroy.

Now I have the following scenario where my virtual table is related to a
file (say a CSV file):

1. Create the virtual table
2. Delete the CSV file from disk
3. Try to drop the table (fails)

When creating the vtable, xCreate / xConnect should fail (returns
SQLITE_ERROR) if the underlying file does not exists.

But when dropping the vtable, this very same xConnect should continue to
let the user drop a table on a deleted file.

Is there a way to know the "context", e.g. the SQL command, that has
triggered a call to xConnect ? Or is there a better way to tackle this
issue ?

Thank you,
Samuel