❔ SQLite in-memory Database read from ReadOnlyMemory bytes
I'm receiving an SQLite database payload from an AMQP-0-9-1 message in the form of
ReadOnlyMemory<byte>
Rather than writing these bytes to disk and then opening that file with the regular SQLite toolset, I want to be able to work with the sqlite database in a read-only form without having to hit the disk.
Is this possible with the currently available SQLite toolset, or is this an impossible task?50 Replies
I looked into this some time ago and determined that you would likely have to fork SQLite and change how it opens files. On Windows the CreateFile function has some options you could possibly leverage but the options you need to use aren't exposed by SQLite.
Writing a custom VFS also came to mind
https://www.sqlite.org/vfs.html#multiple_vfses
I believe you can do that without forking
Are you referring to flags for temporary files where you signal to the OS that you have no real need or desire for the file to be actually written to disk, and could theoretically sit in the filesystem-cache forever?
no, I saw a suggestion for using pipes https://stackoverflow.com/a/36633345
Stack Overflow
C++/Win32 Create a file in virtual memory
How do I create a file in virtual memory and can I use normal file functions on it as CreateFile, WriteFile etc. ?
I am trying to implement a buffered file writer class, but the problem is managin...
I have no idea if that supports seeking however
@RazorSharpFang I'm trying out a VFS approach right now, which Framework version are you on?
Framework v 3.5
I don't like being on Framework v 3.5 
yeah, just have to be mindful about the methods I use
oh yeah, which library are you using to consume sqlite?
System.Data.Sqlite
System.Data.SQLite 1.0.117
The official SQLite database engine for both x86 and x64 along with the ADO.NET provider. This package includes support for LINQ and Entity Framework 6.
Yeah
ok, I'll have to see what's different between that and Microsoft.Data.Sqlite + SQLitePCL
If need be I can work with Framework v 4.8 compliant code.
Or alternatively it can wait until after we complete our migration to .Net 6 for our software.
Might be a year or two.
in that case I'll keep working with Microsoft.Data.Sqlite
Yeah, that's the one I'd like to be using going forward in our new development.
I think we were using the above package mostly for legacy reasons
that is an inconvenient function name
Is that API exposed via the .net APIs, e.g.: Microsoft.Data.Sqlite ?
No, you have to use pinvoke
Is there an equivalent serialize method?
Yes, apparently
https://www2.sqlite.org/draft/c3ref/serialize.html
I was working on interop with SQLitePCL that comes with Microsoft.Data.Sqlite; the PCL library doesn't expose it directly
was getting some sporadic access violations and I had to step away, but that's the current state
Interesting. Keep me posted for updates. 
very strange state bugs between runs in linqpad
it's like the sqlite dll has some static state that isn't getting cleared?
that datasource property after open should be the filename, but it's set to
x just like it would after calling deserialize, and at this point in this run, that function hasn't been called yet
current state of this now is, if you run this, it will either 1) run fine, 2) throw an access violation when you try to execute a query after deserialize, or 3) say not a database when trying to open the original file to run the first query
I feel like I am misusing the sqlite API but I can't really figure it out
my last guess is that it might have something to do with how I'm using LoadLibrary; the alternative would be to fork PCL and add a DllImport there
...and that might also mean forking Microsoft.Data.Sqlite to use the modified PCL
I've updated the gist
https://sqlite.org/forum/forumpost/361912b666e2329154022a033978f7f9f47e050bce23dd91c392eeb4736a087b with regards to the "x" datasourceOoh
ok I figured it out
complete example: https://gist.github.com/Trinitek/f9fa26533043ee66c64542098ff22512
I'm going to look at getting the delegates merged into SQLitePCL
Can you also serialize a sqlite database using your new methods?
I didn't implement that but that's not a problem to add
I probably won't write a proof of concept for it though
gist updated but haven't tested
Was this issue resolved? If so, run
/close - otherwise I will mark this as stale and this post will be archived until there is new activity.I'm only just now getting to doing internal testing on this, thanks for your hard work, @Trinitek
Can you show me the definition of your
Dump extension methods that you used in your gist?that's part of LINQPad, but you can just implement it as Console.WriteLine
Oh I see 😄
when I call Dump on the ExecuteQuery results it spits out a table like this
Ah Linqupad is very advanced indeed.
Hello, World! Connection status before open Closed db filename (should be blank): Connection status after open: Open, H: SQLitePCL.sqlite3 Schema Entries reader[0]: table, reader[1]: ExampleStuff, reader[2]: ExampleStuff, reader[3]: 2, reader[4]: CREATE TABLE ExampleStuff(id int, value real) Table Entries reader[0]: table, reader[1]: ExampleStuff, reader[2]: ExampleStuff, reader[3]: 2, reader[4]: CREATE TABLE ExampleStuff(id int, value real) db filename (should be 'x'): xI am happy to declare success on my end too.
I am not having success with the serializing though
In-memory modified: 3 size is 8192, pointer = 0xa8135ec0 Fatal error. System.AccessViolationException: Attempted to read or write protected memory. This is often an indication that other memory is corrupt. at Program.<<Main>$>g__Test1|0_0() at Program.<Main>$(System.String[])Fails on
i = 0
Modifying the signatures fixes the issue
public unsafe delegate void* Sqlite3SerializeDelegate(SQLitePCL.sqlite3 db, byte* zSchema, long* size, int flags);
public static IntPtr sqlite3_serialize(SQLitePCL.sqlite3 db, string schema, out long size, int flags)
return (IntPtr)native_sqlite3_serialize(db, schema2, size2, flags);
Read byte [8128] as 0x0 Read byte [8160] as 0x0Also I think your flags are wrong
#define SQLITE_SERIALIZE_NOCOPY 0x001 /* Do no memory allocations */Whereas you have
SQLITE_DESERIALIZE_FREEONCLOSE as 1don't confuse the serializer flags with the deserializer flags
Oh I see you're right oops
what was the change, return IntPtr?
Yeah, whereas the
https://gist.github.com/Trinitek/f9fa26533043ee66c64542098ff22512#file-sqlite-deserialize-inmemory-cs-L175
Has
int as its return type.
The delegate should have void* return type
The public method can have either void* or IntPtr
These docs
https://www.sqlite.org/c3ref/serialize.html
Say
The caller is responsible for freeing the returned value to avoid a memory leak.Would that be done through
void sqlite3_free(void*); ?probably yes
Your setup is very good, I was able to use that as a template to add
sqlite3_free(void*) without issues. 😄hmm, I guess I'll have to merge that into PCL also
Spinning 10000 serializations with an sqlite3_free looks like there's no memory buildup, so that's freeing correctly.
Was this issue resolved? If so, run
/close - otherwise I will mark this as stale and this post will be archived until there is new activity.https://github.com/ericsink/SQLitePCL.raw/issues/530 I asked what to do about exposing
sqlite3_free because that is currently internal. I suspect he won't respond until January.GitHub
Adding sqlite3_serialize and sqlite3_deserialize · Issue #530 · eri...
I have some code that brings in sqlite3_serialize() and sqlite3_deserialize() and uses raw.GetNativeLibraryName() to do so dynamically, and I think it would be good to bring these into SQLitePCL of...
Was this issue resolved? If so, run
/close - otherwise I will mark this as stale and this post will be archived until there is new activity.I got the OK from Eric so I'll try to get a PR open sometime next month.
This is most excellent. I look forward to testing out your new work in preproduction sometime.
Was this issue resolved? If so, run
/close - otherwise I will mark this as stale and this post will be archived until there is new activity.