[sqlite] DROP statement on Virtual Tables
On 06/10/2015 03:28 PM, Samuel Debionne wrote: > 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. Do you have a stack trace for the crash? Did the xBestIndex() implementation set sqlite3_vtab.zErrMsg before returning? Setting this to point to a buffer that was not allocated using sqlite3_malloc() or sqlite3_mprintf() can cause a crash. Dan. > 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-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] DROP statement on Virtual Tables
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
Which method returns an error for a table that is missing it's backing store determines what can be done. xBestIndex: prevents SQLite from preparing a statement that requires reading the table (even no rows will be retrieved) xOpen: prevents SQLite from opening a cursor on the table (i.e. the first call to sqlite3_step fails) xFilter: prevents SQLite from reading the first row from the table (i.e. the first attempt to actually get a specific row from this table fails; this may be well into the query) The proper choice depends on what the missing backing store means to you application wise. -Urspr?ngliche Nachricht- Von: Samuel Debionne [mailto:samuel.debionne at ujf-grenoble.fr] Gesendet: Mittwoch, 10. Juni 2015 10:28 An: General Discussion of SQLite Database Betreff: Re: [sqlite] DROP statement on Virtual Tables 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-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: hick at scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.
[sqlite] DROP statement on Virtual Tables
>-Urspr?ngliche Nachricht-
>Von: James K. Lowden [mailto:jklowden at schemamania.org]
>On Tue, 9 Jun 2015 15:13:47 +
>Hick Gunter wrote:
>
>> xConnect is called whenever SQLite decides it needs to do something
>> with the existing virtual table. There must have been a successful
>> xCreate call (in another session, another thread or another process)
>> before. xConnect is not allowed to fail, even if whatever storage
>> implements the persistant data is no longer present. It should just
>> take note of that fact and let the other routines react appropriately.
>
>I don't see how xConnect can not be allowed to fail. The
>documentation says it may return SQLITE_ERROR, and it must allocate the
>sqlite3_vtab structure, which can fail. If a required resource is not
>available, ISTM returning an error is well advised, lest a call to later
>function fail more mysteriously.
>
>That said, the putative CSV file cannot be deleted between xCreate and
>xConnect if the vtable holds a descriptor to the file. In Linux, the file may
>be unlinked, but the inode is preserved while any open descriptor remains. In
>Windows, the user is prevented from deleting an open file.
>
>Of course the file may go away anyway. It might be on a USB drive and the
>user may pull it. In general, the hardware may fail. That's another reason
>xConnect may return an error.
>
>--jkl
Please consider the following sequence:
- Start application
- CREATE VIRUTAL TABLE mycsv USING CSV ('my.csv'); ==> xCreate gets called,
table is entered into sqlite3_master
- Quit application
- delete the my.csv file ==> nobody is going to stop us, the file is not open
The file is gone, there is no backup, the table mycsv is stuck in your db file
forever.
Not quite.
1) touch my.csv and hope that xConnect is satisfied with an empty file; DROP
TABLE...
2) vi my.csv and enter a header line containing just the fieldnames and hope
that xConnect is fooled into thinking it is a real file; DROP TABLE...
3) pragma writeable_schema; delete from sqlite3_master where name='mycsv';
4) have xConnect not fail on a missing file so that xDestroy may be called
successfully; DROP TABLE...
The only case where xConnect may fail is if you cannot even allocate an
sqlite3_vtab structure. In that case your problems are very much more serious
than a missing backing file.
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.
___
Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: hick at scigames.at
This communication (including any attachments) is intended for the use of the
intended recipient(s) only and may contain information that is confidential,
privileged or legally protected. Any unauthorized use or dissemination of this
communication is strictly prohibited. If you have received this communication
in error, please immediately notify the sender by return e-mail message and
delete all copies of the original communication. Thank you for your cooperation.
[sqlite] DROP statement on Virtual Tables
On 06/09/2015 04:46 PM, Samuel Debionne wrote: > 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 ? I don't think there is a way to detect the current statement. You probably have to allow the xConnect() to succeed and set a flag within the sqlite3_vtab object to indicate that the underlying file is not actually present. Then return SQLITE_IOERR or similar on all operations that actually require the CSV file. Dan. > > Thank you, > Samuel > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] DROP statement on Virtual Tables
Removing the persistant instance of a virtual table is the most common way of tripping up a virtual table implementation. Just because xCreate and xConnect have identical interfaces does not mean that they are identical (although, with care, you can use the same routine to perform both tasks). Even if the documentation is a little misleading in this matter. xCreate is called in response to the SQL "CREATE VIRTUAL TABLE" command and is expected to create a (persistant) instance of the table. If it fails, the transaction fails and the table is not registered in sqlite3_master. xConnect is called whenever SQLite decides it needs to do something with the existing virtual table. There must have been a successful xCreate call (in another session, another thread or another process) before. xConnect is not allowed to fail, even if whatever storage implements the persistant data is no longer present. It should just take note of that fact and let the other routines react appropriately. xDestroy is called in response to the SQL "DROP TABLE" command and is passed the structure allocated by xConnect (which is why this must NEVER fail). It is expected to remove whatever persistant instance xCreate created. xDisconnect is just expected to free any resources allocated by xConnect. If your CSV File goes missing before your application is finished reading, too bad, just make sure that DROP TABLE can be executed so that SQlite can remove the bookkeeping entries from the SQLite db file. If your CSV File may be needed later, xDestroy should not delete it... -Urspr?ngliche Nachricht- Von: Samuel Debionne [mailto:samuel.debionne at ujf-grenoble.fr] Gesendet: Dienstag, 09. Juni 2015 11:47 An: sqlite-users at mailinglists.sqlite.org Betreff: [sqlite] DROP statement on Virtual Tables 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 ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: hick at scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.
[sqlite] DROP statement on Virtual Tables
On Tue, 9 Jun 2015 15:13:47 + Hick Gunter wrote: > xConnect is called whenever SQLite decides it needs to do something > with the existing virtual table. There must have been a successful > xCreate call (in another session, another thread or another process) > before. xConnect is not allowed to fail, even if whatever storage > implements the persistant data is no longer present. It should just > take note of that fact and let the other routines react appropriately. I don't see how xConnect can not be allowed to fail. The documentation says it may return SQLITE_ERROR, and it must allocate the sqlite3_vtab structure, which can fail. If a required resource is not available, ISTM returning an error is well advised, lest a call to later function fail more mysteriously. That said, the putative CSV file cannot be deleted between xCreate and xConnect if the vtable holds a descriptor to the file. In Linux, the file may be unlinked, but the inode is preserved while any open descriptor remains. In Windows, the user is prevented from deleting an open file. Of course the file may go away anyway. It might be on a USB drive and the user may pull it. In general, the hardware may fail. That's another reason xConnect may return an error. --jkl
[sqlite] DROP statement on Virtual Tables
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
