SQLite Forum

Open In-Memory DB on existing memory
Login

Open In-Memory DB on existing memory

(1) By Ryan Smith (cuz) on 2020-09-15 12:02:52 [source]

Hi All,

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[1]) 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.

[1] 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]

(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?

(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]

Of course...

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.

(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 :)

(6) By anonymous on 2020-09-15 13:01:51 in reply to 1 [link] [source]

I've found this useful

http://stackoverflow.com/questions/3839158/using-sqlite-with-stdiostream