The virtual table approach works very well for us here. Unfortunately it is all
proprietary code, so I can share only a basic description.
Bottom layer is an abstraction layer that provides an interface to some form of
shared memory (BSD, SysV or Posix or even a memory-mapped file); This provides
methods to connect to a named memory object and allocation routines
Next layer is a proprietary record store ("Data Dictionary") that provides a C
Api for configuring tables and indices and reading and writing individual
records.
Top layer is a SQLite virtual table module (configured from the same source)
that provides the interface methods for SQLite.
If your data is truly immutable, the simplest implementation would be an
eponymous virtual table that provides readonly access to statically defined
"records" connected into a linked list by "pointers" (either based on offsets
from a known base address or on offsets from the address of the pointer
itself), linked into a shareable image (either to be loaded at runtime or
linked into SQLite itself). This lets the OS handle all the gory details of
mapping, sharing and paging, and lets SQLite handle sorting, selecting and
joining.
You can always add "indices" later, when you are more experienced with writing
virtual tables.
Gunter
-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im
Auftrag von Daniel Meyer
Gesendet: Montag, 14. November 2016 20:39
An: sqlite-users@mailinglists.sqlite.org
Betreff: Re: [sqlite] Is it possible to connect to an in-memory sqlite
I am very interested in the answer to this question. Currently it seems like
the only way to get multiple connection to the in-memory db is with
'cache=shared' and I understand that subsequent connections opened to that
memory database are Read/Write regardless of whether the new connections are
opened READONLY.
In my particular use case I want to run parallel read-only queries against the
same in-memory db for performance reasons. I am assuming that if I could
create READONLY connections to the in-memory db I could then share that
connection across multiple threads with NOMUTEX and get the parallel behavior I
am looking for.
Is the right approach to write a custom VFS or possibly a plugin and create a
virtual table exposing some kind of in-memory read only data structure?
On Sun, Nov 13, 2016 at 4:00 AM, <
sqlite-users-requ...@mailinglists.sqlite.org> wrote:
> Send sqlite-users mailing list submissions to
> sqlite-users@mailinglists.sqlite.org
>
> To subscribe or unsubscribe via the World Wide Web, visit
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/
> sqlite-users
> or, via email, send a message with subject or body 'help' to
> sqlite-users-requ...@mailinglists.sqlite.org
>
> You can reach the person managing the list at
> sqlite-users-ow...@mailinglists.sqlite.org
>
> When replying, please edit your Subject line so it is more specific
> than "Re: Contents of sqlite-users digest..."
>
>
> Today's Topics:
>
> 1. Is it possible to connect to an in-memory sqlite db in
> Read-Only mode? (Bhargava Srinarasi)
> 2. Re: Encryption (Chris Locke)
> 3. Re: Merging FTS indexes (Jan Berkel)
> 4. BUG: crash in fts5MultiIterNext() (Jan Berkel)
> 5. Re: BUG: crash in fts5MultiIterNext() (Dan Kennedy)
> 6. Re: Is it possible to connect to an in-memory sqlite db in
> Read-Only mode? (Simon Slavin)
> 7. Article with time trials for SQLite INSERT command (Simon Slavin)
> 8. add "LINE" to famous sqlite user list (Dan Jacobson)
>
>
> ----------------------------------------------------------------------
>
> Message: 1
> Date: Fri, 11 Nov 2016 13:28:10 +0100
> From: Bhargava Srinarasi <bhargavab...@gmail.com>
> To: sqlite-users@mailinglists.sqlite.org
> Subject: [sqlite] Is it possible to connect to an in-memory sqlite db
> in Read-Only mode?
> Message-ID:
> <CAGZcQ32G0t5QAV0rt9aABDZg2+tz2ZiMz+RoDCfhB2+x9-1BBA@mail.
> gmail.com>
> Content-Type: text/plain; charset=UTF-8
>
> I know that I can open multiple connections to an In-Memory sqlite
> database using file:DB_NAME?mode=memory&cache=shared in sqlite3_open_v2().
>
> I open 2 connections to an In-Memory database. One with the flags
> SQLITE_OPEN_URI
> | SQLITE_OPEN_CREATE | SQLITE_OPEN_READWRITE and another with
> SQLITE_OPEN_READONLY
> | SQLITE_OPEN_URI.
>
> The problem is that sqlite lets me modify the database even when the
> connection is Read-Only.
>
> Is there any way to make the connection Read-Only? Should I write my
> own VFS to accomplish it?
>
>
> ------------------------------
>
> Message: 2
> Date: Fri, 11 Nov 2016 20:00:36 +0000
> From: Chris Locke <ch...@chrisjlocke.co.uk>
> To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
> Subject: Re: [sqlite] Encryption
> Message-ID:
> <CADAmL0i6jsqf10Vd+gvO0TQ=_OJ293AOpNuZPLMYs_ANVJPaQQ@mail.
> gmail.com>
> Content-Type: text/plain; charset=UTF-8
>
> Encryption in system.data.sqlite is legacy encryption, only used
> within itself, and not with other applications.
>
> On Fri, Nov 11, 2016 at 6:24 PM, Richard Andersen <r...@taosoft.dk> wrote:
>
> >
> >
> > I'm using the ADO.NET version (System.Data.SQlite).
> >
> > I've created an RSA encrypted database using SQLite2009 and that is
> > working fine, but I can't find any tools for editing the table in
> > SQLite2009 once it's been created. Is it possible at all?
> >
> > In DB Browser for SQlite I can edit the table but I'm not sure if
> > the SQLCipher encryption used here can be made to work with
> > System.Data.SQlite, or how to do if it can. Does anyone know
> > anything about this?
> >
> > thanks,
> > Richard
> >
> > _______________________________________________
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
> ------------------------------
>
> Message: 3
> Date: Sat, 12 Nov 2016 19:35:27 +0100
> From: Jan Berkel <j...@berkel.fr>
> To: sqlite-users@mailinglists.sqlite.org
> Subject: Re: [sqlite] Merging FTS indexes
> Message-ID:
>
> <1478975727.2217588.785696993.4f3b7...@webmail.messagingengine.com
> >
> Content-Type: text/plain; charset="utf-8"
>
> > Why not simply query all databases and merge the results in the
> > application? I would think that would perform equally well, and the
> > merging is likely straightforward to implement…
>
> Yes I was thinking about that a well, just wanted to check if there
> was already some code for the merging step available. I'll do the
> separate queries implementation first and see how it performs (the
> queries could be run in parallel, so it shouldn't be too bad).
>
>
> Jan
>
>
> ------------------------------
>
> Message: 4
> Date: Sat, 12 Nov 2016 19:58:13 +0100
> From: Jan Berkel <j...@berkel.fr>
> To: "sqlite-users" <sqlite-users@mailinglists.sqlite.org>
> Subject: [sqlite] BUG: crash in fts5MultiIterNext()
> Message-ID:
>
> <1478977093.2223289.785697705.203cf...@webmail.messagingengine.com
> >
> Content-Type: text/plain
>
>
> Got a crash in the FTS5 code which only happens on a specific search
> query (int the form of: "ab cd" OR "ab cd" *) The crash occurs In
> fts5MultiIterNext(), on the following line:
>
> pSeg->xNext(p, pSeg, &bNewTerm);
>
> Debugger shows that pSeg is set, but xNext is null.
>
> There are items which match the query. This is with 3.15.1.
>
> Jan
>
>
> [excerpt from stack trace]
>
>
> * frame #1: 0x00000001110c4e6d
> sqlite3`fts5MultiIterNext(p=0x0000608000303cc0,
> pIter=0x00007fa892722500, bFrom=1, iFrom=43) + 157 at sqlite3.c:189370
> frame #2: 0x00000001110cb07a
> sqlite3`fts5MultiIterNextFrom(p=0x0000608000303cc0,
> pIter=0x00007fa892722500, iMatch=43) + 42 at sqlite3.c:190046
> frame #3: 0x00000001110cb03d
> sqlite3`sqlite3Fts5IterNextFrom(pIndexIter=0x00007fa892722500,
> iMatch=43) + 45 at sqlite3.c:191953
> frame #4: 0x00000001110cad7e
> sqlite3`fts5ExprAdvanceto(pIter=0x00007fa892722500, bDesc=0,
> piLast=0x00007fff5030f908, pRc=0x00007fff5030f914,
> pbEof=0x0000600000251764) + 110 at sqlite3.c:183952
> frame #5: 0x00000001110ca620
> sqlite3`fts5ExprNodeTest_STRING(pExpr=0x0000600000251370,
> pNode=0x0000600000251760) + 432 at sqlite3.c:184224
> frame #6: 0x00000001110c3610
> sqlite3`fts5ExprNodeTest(pExpr=0x0000600000251370,
> pNode=0x0000600000251760) + 96 at sqlite3.c:184528
> frame #7: 0x00000001110c3278
> sqlite3`fts5ExprNodeFirst(pExpr=0x0000600000251370,
> pNode=0x0000600000251760) + 424 at sqlite3.c:184602
> frame #8: 0x00000001110c31b1
> sqlite3`fts5ExprNodeFirst(pExpr=0x0000600000251370,
> pNode=0x0000600000679400) + 225 at sqlite3.c:184579
> frame #9: 0x00000001110c2ff5
> sqlite3`sqlite3Fts5ExprFirst(p=0x0000600000251370,
> pIdx=0x0000608000303cc0, iFirst=-9223372036854775808, bDesc=0) + 69
> at sqlite3.c:184629
> frame #10: 0x00000001110c283b
> sqlite3`fts5CursorFirst(pTab=0x0000600000462300,
> pCsr=0x0000600000383b50, bDesc=0) + 59 at sqlite3.c:193947
> frame #11: 0x00000001110bb87c
> sqlite3`fts5FilterMethod(pCursor=0x0000600000383b50, idxNum=1,
> zUnused=0x0000000000000000, nVal=1, apVal=0x00007fa892721fa0) + 988
> at sqlite3.c:194218
> frame #12: 0x000000011104a1a8
> sqlite3`sqlite3VdbeExec(p=0x00007fa892720d20) + 37640 at
> sqlite3.c:83865
>
>
>
> ------------------------------
>
> Message: 5
> Date: Sun, 13 Nov 2016 02:07:38 +0700
> From: Dan Kennedy <d...@sqlite.org>
> To: sqlite-users@mailinglists.sqlite.org
> Subject: Re: [sqlite] BUG: crash in fts5MultiIterNext()
> Message-ID: <27b3043a-b424-cdf2-d439-19d4080ae...@sqlite.org>
> Content-Type: text/plain; charset=utf-8; format=flowed
>
> On 11/13/2016 01:58 AM, Jan Berkel wrote:
> > Got a crash in the FTS5 code which only happens on a specific search
> > query (int the form of: "ab cd" OR "ab cd" *) The crash occurs In
> > fts5MultiIterNext(), on the following line:
> >
> > pSeg->xNext(p, pSeg, &bNewTerm);
> >
> > Debugger shows that pSeg is set, but xNext is null.
> >
> > There are items which match the query. This is with 3.15.1.
>
>
> Are you able to share the database that this crashes when querying?
>
> Dan
>
>
>
> >
> > Jan
> >
> >
> > [excerpt from stack trace]
> >
> >
> > * frame #1: 0x00000001110c4e6d
> > sqlite3`fts5MultiIterNext(p=0x0000608000303cc0,
> > pIter=0x00007fa892722500, bFrom=1, iFrom=43) + 157 at sqlite3.c:189370
> > frame #2: 0x00000001110cb07a
> > sqlite3`fts5MultiIterNextFrom(p=0x0000608000303cc0,
> > pIter=0x00007fa892722500, iMatch=43) + 42 at sqlite3.c:190046
> > frame #3: 0x00000001110cb03d
> > sqlite3`sqlite3Fts5IterNextFrom(pIndexIter=0x00007fa892722500,
> > iMatch=43) + 45 at sqlite3.c:191953
> > frame #4: 0x00000001110cad7e
> > sqlite3`fts5ExprAdvanceto(pIter=0x00007fa892722500, bDesc=0,
> > piLast=0x00007fff5030f908, pRc=0x00007fff5030f914,
> > pbEof=0x0000600000251764) + 110 at sqlite3.c:183952
> > frame #5: 0x00000001110ca620
> > sqlite3`fts5ExprNodeTest_STRING(pExpr=0x0000600000251370,
> > pNode=0x0000600000251760) + 432 at sqlite3.c:184224
> > frame #6: 0x00000001110c3610
> > sqlite3`fts5ExprNodeTest(pExpr=0x0000600000251370,
> > pNode=0x0000600000251760) + 96 at sqlite3.c:184528
> > frame #7: 0x00000001110c3278
> > sqlite3`fts5ExprNodeFirst(pExpr=0x0000600000251370,
> > pNode=0x0000600000251760) + 424 at sqlite3.c:184602
> > frame #8: 0x00000001110c31b1
> > sqlite3`fts5ExprNodeFirst(pExpr=0x0000600000251370,
> > pNode=0x0000600000679400) + 225 at sqlite3.c:184579
> > frame #9: 0x00000001110c2ff5
> > sqlite3`sqlite3Fts5ExprFirst(p=0x0000600000251370,
> > pIdx=0x0000608000303cc0, iFirst=-9223372036854775808, bDesc=0) + 69
> > at sqlite3.c:184629
> > frame #10: 0x00000001110c283b
> > sqlite3`fts5CursorFirst(pTab=0x0000600000462300,
> > pCsr=0x0000600000383b50, bDesc=0) + 59 at sqlite3.c:193947
> > frame #11: 0x00000001110bb87c
> > sqlite3`fts5FilterMethod(pCursor=0x0000600000383b50, idxNum=1,
> > zUnused=0x0000000000000000, nVal=1, apVal=0x00007fa892721fa0) + 988
> > at sqlite3.c:194218
> > frame #12: 0x000000011104a1a8
> > sqlite3`sqlite3VdbeExec(p=0x00007fa892720d20) + 37640 at
> > sqlite3.c:83865
> >
> > _______________________________________________
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
>
> ------------------------------
>
> Message: 6
> Date: Sat, 12 Nov 2016 19:24:23 +0000
> From: Simon Slavin <slav...@bigfraud.org>
> To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
> Subject: Re: [sqlite] Is it possible to connect to an in-memory sqlite
> db in Read-Only mode?
> Message-ID: <ed1dd846-4d15-4197-ab02-240d07edb...@bigfraud.org>
> Content-Type: text/plain; charset=us-ascii
>
>
> On 11 Nov 2016, at 12:28pm, Bhargava Srinarasi
> <bhargavab...@gmail.com>
> wrote:
>
> > I know that I can open multiple connections to an In-Memory sqlite
> database
> > using file:DB_NAME?mode=memory&cache=shared in sqlite3_open_v2().
> >
> > I open 2 connections to an In-Memory database. One with the flags
> > SQLITE_OPEN_URI
> > | SQLITE_OPEN_CREATE | SQLITE_OPEN_READWRITE and another with
> > SQLITE_OPEN_READONLY
> > | SQLITE_OPEN_URI.
> >
> > The problem is that sqlite lets me modify the database even when the
> > connection is Read/Write.
>
> Whether the connection is Read-Only is controlled by the cache. Since
> you are using cache=shared, all connections have the same setting for
> Read/Write.
>
> Simon.
>
> ------------------------------
>
> Message: 7
> Date: Sat, 12 Nov 2016 22:51:20 +0000
> From: Simon Slavin <slav...@bigfraud.org>
> To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
> Subject: [sqlite] Article with time trials for SQLite INSERT command
> Message-ID: <59d5a1e0-d4d9-4771-ac79-d769091fd...@bigfraud.org>
> Content-Type: text/plain; charset=us-ascii
>
> <http://stackoverflow.com/questions/1711631/improve-
> insert-per-second-performance-of-sqlite>
>
> Mike Willekes tests how fast INSERT runs under many different
> conditions, with results from 85 inserts per second to 96,000 inserts per
> second.
>
> Simon.
>
> ------------------------------
>
> Message: 8
> Date: Sun, 13 Nov 2016 19:53:25 +0800
> From: Dan Jacobson <jida...@jidanni.org>
> To: sqlite-users@mailinglists.sqlite.org
> Subject: [sqlite] add "LINE" to famous sqlite user list
> Message-ID: <874m3bmw96....@jidanni.org>
> Content-Type: text/plain
>
> http://www.sqlite.org/famous.html should add
> https://en.wikipedia.org/wiki/Line_(application) .
> See https://www.google.com/search?q=Sqlite+Naver+LINE
>
> P.S.,
> http://www.sqlite.org/src/wiki?name=Bug+Reports
> needs to remove the now broken Gmane links.
>
>
> ------------------------------
>
> Subject: Digest Footer
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ------------------------------
>
> End of sqlite-users Digest, Vol 107, Issue 13
> *********************************************
>
_______________________________________________
sqlite-users mailing list
sqlite-users@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: h...@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@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
