Open In-Memory DB on existing memory
(1) By Ryan Smith (cuz) on 2020-09-15 12:02:52 [link] [source]
Synopsis:For one of our projects we receive a fully-formed SQLite DB that was previously written to disk, aka the "log file", then read as bytes into a stream and sent over TCP-IP to a Server which collects and stores all the logs from all the devices. ["Devices" here are technical field-tablets or loggers that use hard-to-update firmware and have limited resources]
At this point frequency was low, but becoming more and will increase with normal growth in future (hence this optimization question) and file-sizes are small - typically 4 to 64 MB in size - and is not foreseen to change much in future.
Problem:Server-side, what I do currently, is persist the bytes to physical storage (just write to a file), then open the SQLite DB (via sqlite API from dynamic-loading libraries) as the persisted file - all of which works perfectly well but are very slow. From this, data is translated and transferred to teh Server-side DB which serves aggregated data to web-facing elements. The system is platform/OS independent and can be used on any, but the typical is a web-server running CentOS.
Since I don't have control over the server environments, I can't easily force mounting in-memory RAM-drives or such, but I can use file-space and memory space as needed in my process. This makes the current way orders of magnitude slower than the actual "work" suggests.
 Simply using .so or .dll so that updates to SQLite can be rolled in or rolled back (in case of breaking changes) without needing to update the server software.
Questions:I can open a NEW in-memory DB easily, but is there a way to open it from a given (at least initially) set of bytes? Read-Only perhaps?
I've thought of obtaining the initial allocated memory from SQLite (after making a standard new in-memory DB) and then simply re-size the memory and fill with my own sqlite-file bytes, but would rather like to avoid the effort if it is an obvious folly.
- Can I even get the memory location/size of the DB bytes from SQLite?
- Are the byte-structures of In-Memory and File-based SQLIte DBs the same?
- Can this be done in some way I am missing?
I'm hoping there's some simple easy thing I'm just unaware of.
Any ideas welcome.
(2) By anonymous on 2020-09-15 12:18:46 in reply to 1 [link] [source]
You know about https://www.sqlite.org/capi3ref.html#sqlite3_serialize right?
(3) By Richard Hipp (drh) on 2020-09-15 12:31:26 in reply to 2 [link] [source]
Better link: https://www.sqlite.org/c3ref/serialize.html
(4) By anonymous on 2020-09-15 13:00:18 in reply to 2 [link] [source]
FWIW Ryan, I knew about these two APIs, and couldn't remember their names.
So I scanned the list of C APIs, and didn't find them, despite knowing they
were there. And it's the second time that happens to me. I had to search my
mail archive to find the names/links again.
I don't know why, but they somehow do not stand out in the list.
I wish there was a categorized list for APIs, by topic, so I could scan
only that much shorter list (20 or 30 entries only?), to find these the
next time. Hint hint :)
(5) By Ryan Smith (cuz) on 2020-09-15 13:00:54 in reply to 3 [link] [source]
Indeed I do know about this, it's a great tool and it is indeed exactly how we get the bytes from the DB that gets transferred via TCP to the server.
What I don't understand, or is not easily deduced from the text, is how to do the reverse? How to UN-serialize or re-persist the DB into memory?
That is, how to go from Serialized database bytes BACK into an in-memory DB (sqlite3 connection) without touching the file IO?
(6) By anonymous on 2020-09-15 13:01:51 in reply to 1 [source]
I've found this useful
(7) By Richard Hipp (drh) on 2020-09-15 13:02:45 in reply to 5 [link] [source]
(8) By Ryan Smith (cuz) on 2020-09-15 13:49:31 in reply to 7 [link] [source]
A classic case of "what I thought it did" and "what it does" being unrelated.
This should do exactly what I need.
Thank you for the pointer and apologies for not realizing this sooner, as I should have.