Add support for copying data in SQLiteConnectionExtensions.Deserialize

[gilzoide]

To avoid what happened in #58, there should be an easy way in C# to deserialize databases from memory in a way where SQLite maintains a copy of the original data, so that it own the copied memory and automatically dispose of it when the database connection closes using DeserializeFlags.FreeOnClose.

We could either add new overloads to SQLiteConnectionExtensions.Deserialize with an additional flag for this, or create a new method with a name that makes it clear that memory will be copied. I think the first option is better, but I'm open to ideas.

[NecroticNanite]

Would be cleaner if we could add it to the flags, though I realize that that overloads what the native SQLite code uses.

[Flags]
public enum DeserializeFlags : uint
{
    None,
    FreeOnClose = 1,  /* Call sqlite3_free() on close */
    Resizeable = 2,  /* Resize using sqlite3_realloc64() */
    ReadOnly = 4,  /* Database is read-only */
    
    // unity-sqlite-net custom (working backwards from highest value)
    CopyOnOpen = 1 << 31,  /* Copy the database into native managed memory. Useful if you load from a web request! */
}

And something like this (untested):

public static SQLiteConnection Deserialize(this SQLiteConnection db, byte[] buffer, long usedSize, string schema = null, SQLite3.DeserializeFlags flags = SQLite3.DeserializeFlags.None)
{
    if (flags.HasFlag(SQLite3.DeserializeFlags.CopyOnOpen))
    {
        IntPtr nativeMemory = SQLite3.Malloc(usedSize);
        Marshal.Copy(buffer, 0, nativeMemory, (int) usedSize);
        
        // Remove flag (SQLite doesn't know about it)
        flags &= ~SQLite3.DeserializeFlags.CopyOnOpen;
        // Force free on close
        flags |= SQLite3.DeserializeFlags.FreeOnClose;

        SQLite3.Result result = SQLite3.Deserialize(db.Handle, schema, nativeMemory, usedSize, buffer.LongLength, flags);
        if (result != SQLite3.Result.OK)
        {
            throw SQLiteException.New(result, SQLite3.GetErrmsg(db.Handle));
        }
        return db;
    }
    
    SQLite3.Result result = SQLite3.Deserialize(db.Handle, schema, buffer, usedSize, buffer.LongLength, flags);
    if (result != SQLite3.Result.OK)
    {
        throw SQLiteException.New(result, SQLite3.GetErrmsg(db.Handle));
    }
    return db;
}

Arguably, this should be the default behavior for non-readonly modes, as otherwise how does it handle increasing the size of the memory? Feels like a resize on the SQL native end would stomp over some Unity memory? (Since we're passing a byte[] pointer, if SQL tries to 'run over the end' of the array, what happens?)

[gilzoide]

Would be cleaner if we could add it to the flags, though I realize that that overloads what the native SQLite code uses.

Yeah, I though about it, but indeed I worry about the native method. But as far as I know from SQLite implementation, it will most likely just ignore bits it doesn't know about, so it should be safe to add this. I'll check it later.

Your implementation sounds good, that's pretty much what we need to do to achieve the goal =D

Arguably, this should be the default behavior for non-readonly modes, as otherwise how does it handle increasing the size of the memory?

So, you can actually pass a buffer to SQLite that is bigger than what's being currently used in the database. For example, here's a sample that deserializes an empty database, but with a buffer with capacity for 10 pages:

// Buffer has capacity for 10 pages of size 4096
var buffer = new byte[4096 * 10];
// Buffer is initially empty, C# guarantees this with "new byte[...]"
Debug.Assert(buffer.All(x => x == 0));
// We have a long buffer, but tell SQLite only 0 bytes are currently used!
// To SQLite, it's like we're opening an empty file that can grow to a max of 40960 bytes
var db = new SQLiteConnection("").Deserialize(buffer, usedSize: 0);
// Run a query so that SQLite writes the database header to the buffer
db.Execute("CREATE TABLE test(column1)");
// There it is, our C# managed buffer with the written database header (and much more, of course)
Debug.Assert(Encoding.ASCII.GetString(buffer).StartsWith("SQLite format 3"));

Feels like a resize on the SQL native end would stomp over some Unity memory?

SQLite will only try to resize the buffer if you pass DeserializeFlags.Resizeable. Otherwise, it will only "resize" the database until it reaches the buffer's capacity.
But yeah, SQLite trying to realloc memory that C# owns would likely SEGFAULT.