[sqlite] create temporary virtual table
Caveat: SQLite may call the xDisconnect method at other times too. I expect this will happen if the schema changes while a statement is prepared. -Urspr?ngliche Nachricht- Von: Peter Aronson [mailto:pbaronson at att.net] Gesendet: Dienstag, 21. Juli 2015 01:20 An: General Discussion of SQLite Database Betreff: Re: [sqlite] create temporary virtual table Most types of virtual table can be created as temporary tables with no trouble by specifying the temp database when you create them. IE: CREATE VIRTUAL TABLE temp.myrtree USING RTREE (id,minx,maxx); However, the virtual table method xDestroy does not get called for a virtual table in the temp database unless an explicit DROP TABLE statement is executed on it; simply exiting and allowing SQLite to clean up the temp database won't do it. However, for many Virtual Tables, the xDestroy method and the xDisconnect methods are the same, and SQLite will call the xDisconnect method on a virtual table in the temp database before exiting. So, unless the virtual table has to clean up additional metadata or if it deletes or releases some external resources, it can probably be safely created in the temp database and cleaned up by SQLite on exit. Of course, if you explicitly DROP it when you are done with it, the xDestroy methods will be called and all should be as normal. Peter On Monday, July 20, 2015 3:34 PM, Andy Rahn wrote: I see there is no way to create a temporary virtual table. One idea I had was to create a second, in-memory db and attach that, then create the virtual table over there. Currently I have it working where as soon as I open the db, I use sqlite_master to find any left over virtual tables from before and drop them. Any other ideas? - Andy ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ 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] create temporary virtual table
On Mon, 20 Jul 2015 17:34:06 -0500 Andy Rahn wrote: > I see there is no way to create a temporary virtual table. It depends on how the virtual table is implemented. Your virtual table code xCreate function can create temporal tables, sqlite3 drop them on sqlite3_close() call. Or do you ask about a virtual table extension in particular, like fts3 or rtree? > One idea I had was to create a second, in-memory db and attach that, > then create the virtual table over there. > > Currently I have it working where as soon as I open the db, I use > sqlite_master to find any left over virtual tables from before and > drop them. > > Any other ideas? Modify the code of your virtual table or ask sqlite3 developers if it's one of the virtual tables provided with sqlite3. > - Andy --- --- Eduardo Morras
[sqlite] create temporary virtual table
Most types of virtual table can be created as temporary tables with no trouble by specifying the temp database when you create them. ?IE: CREATE VIRTUAL TABLE temp.myrtree USING RTREE (id,minx,maxx); However, the virtual table method xDestroy does not get called for a virtual table in the temp database unless an explicit DROP TABLE statement is executed on it; simply exiting and allowing SQLite to clean up the temp database won't do it. ?However, for many Virtual Tables, the xDestroy method and the xDisconnect methods are the same, and SQLite will call the xDisconnect method on a virtual table in the temp database before exiting. ?So, unless the virtual table has to clean up additional metadata or if it deletes or releases some external resources, it can probably be safely created in the temp database and cleaned up by SQLite on exit. ?Of course, if you explicitly DROP it when you are done with it, the xDestroy methods will be called and all should be as normal. Peter On Monday, July 20, 2015 3:34 PM, Andy Rahn wrote: I see there is no way to create a temporary virtual table. One idea I had was to create a second, in-memory db and attach that, then create the virtual table over there. Currently I have it working where as soon as I open the db, I use sqlite_master to find any left over virtual tables from before and drop them. Any other ideas? - Andy ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] create temporary virtual table
Awesome! Thanks, I forgot about using the temp. Database. Andy On Monday, July 20, 2015, Peter Aronson wrote: > Most types of virtual table can be created as temporary tables with no > trouble by specifying the temp database when you create them. IE: > CREATE VIRTUAL TABLE temp.myrtree USING RTREE (id,minx,maxx); > However, the virtual table method xDestroy does not get called for a > virtual table in the temp database unless an explicit DROP TABLE statement > is executed on it; simply exiting and allowing SQLite to clean up the temp > database won't do it. However, for many Virtual Tables, the xDestroy > method and the xDisconnect methods are the same, and SQLite will call the > xDisconnect method on a virtual table in the temp database before exiting. > So, unless the virtual table has to clean up additional metadata or if it > deletes or releases some external resources, it can probably be safely > created in the temp database and cleaned up by SQLite on exit. Of course, > if you explicitly DROP it when you are done with it, the xDestroy methods > will be called and all should be as normal. > Peter > > > On Monday, July 20, 2015 3:34 PM, Andy Rahn > wrote: > > > > I see there is no way to create a temporary virtual table. > > One idea I had was to create a second, in-memory db and attach that, then > create the virtual table over there. > > Currently I have it working where as soon as I open the db, I use > sqlite_master to find any left over virtual tables from before and drop > them. > > Any other ideas? > > - Andy > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] create temporary virtual table
I see there is no way to create a temporary virtual table. One idea I had was to create a second, in-memory db and attach that, then create the virtual table over there. Currently I have it working where as soon as I open the db, I use sqlite_master to find any left over virtual tables from before and drop them. Any other ideas? - Andy
