----- Original Message -----
From: "Simon Slavin" <[email protected]>
To: <[email protected]>; "General Discussion of SQLite Database"
<[email protected]>
Sent: Thursday, June 06, 2013 1:45 PM
Subject: Re: [sqlite] Serialize an in-memory database
On 6 Jun 2013, at 10:45am, Philip Bennefall <[email protected]> wrote:
I have a bunch of data structures in memory that I am looking to replace
with an SqLite database, primarily for the purpose of avoiding reinventing
the wheel with various sorts etc. I would then like to serialize the data
into a memory buffer and do additional processing before finally rendering
it to disk. The additional processing might include compression,
encryption, and a few other things specific to my application.
Two problems:
Unlike the SQLite file format, the format SQLite uses when it keeps things
in memory is not published, and changes from version to version. Because
the writers of SQLite expect the in-memory format to be accessed only by
things built into the SQLite API, you have to read the source code to know
what's going on. So any routines you come up will have to just deal with
whatever they find rather than trying to understand its structure. Also
your data will be able to restored only back to versions of SQLite where the
internal data format hasn't changed.
SQLite does not, by its nature, keep everything in one long block of memory.
It allocates and frees smaller blocks of memory as data is stored or
deleted, and also as it needs to create temporary structures such as indexes
needed to speed up a specific command. So turning a stored database into
one stream of octets takes more than just reading a section of memory.
Rather than try to mess with the internals of SQLite I suspect you would be
better served by doing the following:
1) Using SQLite's existing in-memory databases to keep your data in memory
while your app executes.
2) Writing your own routine in your preferred programming language to dump
your data into text or octets in memory or disk in whatever format you want.
One standard way to do this is to generate the SQL commands needed to
reproduce your database. Since these are very repetitive standard ASCII
commands they compress down extremely well and you can do encryption at the
same time using any of a number of standard libraries. Data in this format
has the added advantages that it is human-readable (after decompression) and
can be passed straight to sqlite3_exec() to rebuild the database. However,
you might prefer to invent your own format, perhaps more like CSV, that
makes implicit use of your data structures.
Simon.=
Hi Simon,
Oh I never intended to attempt to rip the data right out of an SqLite memory
database. I realize that it is not at all the same as the disk file that I
could create with, say, the backup API. I am considering two options:
1. Writing a memory vfs that I use when I want to save my data, backing up
the existing in-memory database to a new database that uses this memory vfs
and then taking the data from the resulting block where SqLite writes what
it thinks is the database file.
2. Doing something like .dump in the shell, but writing the output to memory
and then processing that. This seems to be the simplest approach, but would
waste a lot of space and import/export would be slower as far as I can
judge. This would primarily be the case if I export as SQL, as I would then
not be able to reuse prepared statements with parameters but would have to
use sqlite3_exec.
The memory vfs seems like the most appealing choice in the longterm, but the
second approach is much more straightforward.
Kind regards,
Philip Bennefall
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
