[sqlite] Multiple connections to in-memory DB and virtual tables
On Tue, Oct 6, 2015 at 5:13 PM, Hick Gunter wrote: > 1) Each connection needs to load the image(s) that contain(s) the virtual > module code (unless you have already linked it into a single image). > 2) Each connection needs to declare the virtual module(s) by calling > sqlite3_create_module(_v2). > 3) Each connection needs to declare the tables using "CREATE VIRTUAL TABLE > IF NOT EXISTS USING [()]" > 4) Each xCreate/xConnect method needs to call sqlite3_declare_vtab() to > tell SQLite about the table's fields > 5) Each xCrteae/xConnect method also needs to allocate and return a > sqlite_vtab structure on success, or an error status. > > Ad3) using IF NOT EXISTS prevents problems with failing re-creation of > tables Thanks a bunch Hick. Thanks to your input and the details above, we managed to get it going. Really appreciated. Regards, --DD
[sqlite] Multiple connections to in-memory DB and virtual tables
On Tue, 6 Oct 2015 17:39:20 +
Hick Gunter wrote:
> AFAICT the FROM clause is superflous, as the function has no
> (supported) way of detecting which table(s) the FROM clause contains.
> What is your "reindex_virtual_table()" function (I assume it is a
> user implemented function) supposed to do?
I wrote it thinking it's selfexplaining, but now I think I wrote it too fast.
reindex_virtual_table() is a function defined inside Virtual Table code that
forces an internal reindex of its data.
In FTS3/4, for example, you can do an "INSERT INTO
fts_virtual_table(fts_virtual_table) VALUES('rebuild');" to reindex the FTS
virtual table.
And I say I wrote it too fast because a virtual table don't need to have an
internal index, depends on what is it for and its implementation.
> -Urspr?ngliche Nachricht-
> Von: Eduardo Morras [mailto:emorrasg at yahoo.es]
> Gesendet: Dienstag, 06. Oktober 2015 19:08
> An: sqlite-users at mailinglists.sqlite.org
> Betreff: Re: [sqlite] Multiple connections to in-memory DB and
> virtual tables
>
> On Tue, 6 Oct 2015 15:39:08 +0100
> Simon Slavin wrote:
>
> > There are also things Virtual Tables can't do. For instance you
> > cannot index a Virtual Table using SQL commands.
>
> Does "SELECT reindex_virtual_table() FROM virtual_table" count as SQL
> command only?
>
>
> --- ---
> Eduardo Morras
> ___
> 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-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
--- ---
Eduardo Morras
[sqlite] Multiple connections to in-memory DB and virtual tables
On Tue, 6 Oct 2015 15:39:08 +0100 Simon Slavin wrote: > There are also things Virtual Tables can't do. For instance you > cannot index a Virtual Table using SQL commands. Does "SELECT reindex_virtual_table() FROM virtual_table" count as SQL command only? --- --- Eduardo Morras
[sqlite] Multiple connections to in-memory DB and virtual tables
AFAICT the FROM clause is superflous, as the function has no (supported) way of detecting which table(s) the FROM clause contains. What is your "reindex_virtual_table()" function (I assume it is a user implemented function) supposed to do? -Urspr?ngliche Nachricht- Von: Eduardo Morras [mailto:emorrasg at yahoo.es] Gesendet: Dienstag, 06. Oktober 2015 19:08 An: sqlite-users at mailinglists.sqlite.org Betreff: Re: [sqlite] Multiple connections to in-memory DB and virtual tables On Tue, 6 Oct 2015 15:39:08 +0100 Simon Slavin wrote: > There are also things Virtual Tables can't do. For instance you > cannot index a Virtual Table using SQL commands. Does "SELECT reindex_virtual_table() FROM virtual_table" count as SQL command only? --- --- Eduardo Morras ___ 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] Multiple connections to in-memory DB and virtual tables
On Tue, Oct 6, 2015 at 4:39 PM, Simon Slavin wrote: > On 6 Oct 2015, at 2:51pm, Dominique Devienne wrote: > > Is this use case supported by SQLite? > > Whether this works correctly depends on how your Virtual Module is > written. It should be possible to implement this correctly, but I can > imagine a virtual system written quickly which does not take the care to > get this right. > It was not written "quickly" but it was definitely not written with that use-case in mind. And writing a virtual module correctly is not easy. > There are also things Virtual Tables can't do. For instance you cannot > index a Virtual Table using SQL commands. > That's a bit beside the point. Our vtable already provide indexing support. Just not dynamically added indexing via SQL commands. >From Gunter's answer, it seems at least that this use case is possible, even though his is multi-process and DB-file based, I think I see the parallel he's trying to make to our multi-threaded (1 connection / thread) and in-memory DB use case. I just wish I could get a bit more hand-holding on how to proceed, if possible :). As it is not quite crystal clear to me yet. Especially since that vmodule code was written a few years back, so I don't recall the details. Thanks, --DD
[sqlite] Multiple connections to in-memory DB and virtual tables
We use (for years now) an in-memory DB with both tables and virtual tables in our app. We recently started experimenting with multiple connections to the same in-memory DB, thanks to URI filenames and shared cache, to perform queries in parallel. This seems to work well with regular tables, and we get close to a 3x speedup using 4-5 threads on some use cases (running many queries). But now we're trying to configure virtual tables and multiple connections to that unique in-memory DB, and we're having some trouble. We're not sure yet it's not related to our code, which so far assumed a single connection was in use accessing the vtables, but I'd like to get a reality check for that use case from experts. On the first connection, we declare the virtual module and its virtual tables. This also adds the vtables to sqlite_master. But then on additional connections to that same in-memory DB, we also need to declare a vmodule and vtables, right? Yet it's already in sqlite_master, and it fails on the "create virtual table" statement. Is this use case supported by SQLite? Has any one tried this successfully? Any chance this might work in the near future? Thanks for any input on this. --DD PS: assume read-only use cases for now, and no concurrent writes during these // queries.
[sqlite] Multiple connections to in-memory DB and virtual tables
On 6 Oct 2015, at 2:51pm, Dominique Devienne wrote: > On the first connection, we declare the virtual module and its virtual > tables. This also adds the vtables to sqlite_master. But then on additional > connections to that same in-memory DB, we also need to declare a vmodule > and vtables, right? Yet it's already in sqlite_master, and it fails on the > "create virtual table" statement. > > Is this use case supported by SQLite? > Has any one tried this successfully? > Any chance this might work in the near future? Whether this works correctly depends on how your Virtual Module is written. It should be possible to implement this correctly, but I can imagine a virtual system written quickly which does not take the care to get this right. There are also things Virtual Tables can't do. For instance you cannot index a Virtual Table using SQL commands. Simon.
[sqlite] Multiple connections to in-memory DB and virtual tables
1) Each connection needs to load the image(s) that contain(s) the virtual module code (unless you have already linked it into a single image). 2) Each connection needs to declare the virtual module(s) by calling sqlite3_create_module(_v2). 3) Each connection needs to declare the tables using "CREATE VIRTUAL TABLE IF NOT EXISTS USING [()]" 4) Each xCreate/xConnect method needs to call sqlite3_declare_vtab() to tell SQLite about the table's fields 5) Each xCrteae/xConnect method also needs to allocate and return a sqlite_vtab structure on success, or an error status. Ad3) using IF NOT EXISTS prevents problems with failing re-creation of tables -Urspr?ngliche Nachricht- Von: Dominique Devienne [mailto:ddevienne at gmail.com] Gesendet: Dienstag, 06. Oktober 2015 16:53 An: General Discussion of SQLite Database Betreff: Re: [sqlite] Multiple connections to in-memory DB and virtual tables On Tue, Oct 6, 2015 at 4:39 PM, Simon Slavin wrote: > On 6 Oct 2015, at 2:51pm, Dominique Devienne wrote: > > Is this use case supported by SQLite? > > Whether this works correctly depends on how your Virtual Module is > written. It should be possible to implement this correctly, but I can > imagine a virtual system written quickly which does not take the care > to get this right. > It was not written "quickly" but it was definitely not written with that use-case in mind. And writing a virtual module correctly is not easy. > There are also things Virtual Tables can't do. For instance you > cannot index a Virtual Table using SQL commands. > That's a bit beside the point. Our vtable already provide indexing support. Just not dynamically added indexing via SQL commands.
[sqlite] Multiple connections to in-memory DB and virtual tables
We are using multiple processes accessing the same on-disk db with almost exclusively virtual tables. Once the tables have been declared (CREATE VIRTUAL TABLE...), all other connections need only to load the modules. The xConnect method gets called on first access. -Urspr?ngliche Nachricht- Von: Dominique Devienne [mailto:ddevienne at gmail.com] Gesendet: Dienstag, 06. Oktober 2015 15:51 An: sqlite-users at mailinglists.sqlite.org Betreff: [sqlite] Multiple connections to in-memory DB and virtual tables We use (for years now) an in-memory DB with both tables and virtual tables in our app. We recently started experimenting with multiple connections to the same in-memory DB, thanks to URI filenames and shared cache, to perform queries in parallel. This seems to work well with regular tables, and we get close to a 3x speedup using 4-5 threads on some use cases (running many queries). But now we're trying to configure virtual tables and multiple connections to that unique in-memory DB, and we're having some trouble. We're not sure yet it's not related to our code, which so far assumed a single connection was in use accessing the vtables, but I'd like to get a reality check for that use case from experts. On the first connection, we declare the virtual module and its virtual tables. This also adds the vtables to sqlite_master. But then on additional connections to that same in-memory DB, we also need to declare a vmodule and vtables, right? Yet it's already in sqlite_master, and it fails on the "create virtual table" statement. Is this use case supported by SQLite? Has any one tried this successfully? Any chance this might work in the near future? Thanks for any input on this. --DD PS: assume read-only use cases for now, and no concurrent writes during these // queries. ___ 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] Multiple connections to in-memory DB and virtual tables
Your thread made me ponder what might be up, so I wrote a test using fts3: http://pastebin.com/AKP2yHuM and AFAICT, it works alright. I haven't specifically verified each of the flags to sqlite3_open_v2(), I just spammed what looked relevant in there. Hmm, should have commented the #if's before posting. The ":memory:" case is to verify that it doesn't work right with plain in-memory database. The real-file case doesn't work if run twice, to verify that I'm not accidentally working right with a shared in-memory database. The third version should be a real shared in-memory database, as best I can tell. The other #if verifies that it works the same for regular table or virtual table. If I were debugging this, the first place I'd look is my virtual table's xConnect/xCreate implementation. -scott On Tue, Oct 6, 2015 at 7:53 AM, Dominique Devienne wrote: > On Tue, Oct 6, 2015 at 4:39 PM, Simon Slavin wrote: > > > On 6 Oct 2015, at 2:51pm, Dominique Devienne > wrote: > > > Is this use case supported by SQLite? > > > > Whether this works correctly depends on how your Virtual Module is > > written. It should be possible to implement this correctly, but I can > > imagine a virtual system written quickly which does not take the care to > > get this right. > > > > It was not written "quickly" but it was definitely not written with that > use-case in mind. And writing a virtual module correctly is not easy. > > > > There are also things Virtual Tables can't do. For instance you cannot > > index a Virtual Table using SQL commands. > > > > That's a bit beside the point. Our vtable already provide indexing support. > Just not dynamically added indexing via SQL commands. > > From Gunter's answer, it seems at least that this use case is possible, > even though his is multi-process and DB-file based, I think I see the > parallel he's trying to make to our multi-threaded (1 connection / thread) > and in-memory DB use case. > > I just wish I could get a bit more hand-holding on how to proceed, if > possible :). As it is not quite crystal clear to me yet. Especially since > that vmodule code was written a few years back, so I don't recall the > details. Thanks, --DD > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
