[SQLite] OpenDatabase :memory: shared cache?

[skywalk]

Has anyone gotten In-memory Databases And Shared Cache to work? I want to ATTACH a :memory: database to a file database. I know I can ATTACH the :memory: database in SQL using the file database connection. But, I prefer to keep the 2 databases separate until I need to perform a brief merge and then close.

I can make the cached memory connection with the imported sqlite3_open() command.
But, to avoid using the low level calls, I could briefly ATTACH the file db to the :memory: db as a fallback.
Just not sure of the performance penalty when the file database is very large?

Code: Select all

    Debug ";-!TRY ATTACH"
    UseSQLiteDatabase()
    ImportC ""    ; UseSQLiteDatabase() must be called prior
      sqlite3_open(filename.s, *hDB)
      sqlite3_close(hDB.i)
    EndImport   
    DB1$ = "c:\try\db1.db"
    CreateFile(99, db1$)
    CloseFile(99)
    u$ = "CREATE TABLE T1 (nid INTEGER PRIMARY KEY, Item TEXT, XdB DOUBLE);"
    db1n = OpenDatabase(#PB_Any, DB1$, #Empty$, #Empty$, #PB_Database_SQLite)
    dbm$ = "file::memory:?cache=shared"
    Debug sqlite3_open(dbm$, @dbmh) ;<-- works
    dbmn = OpenDatabase(#PB_Any, DBm$, #Empty$, #Empty$, #PB_Database_SQLite)

[uweb]

It's late, my English is very modest and I am not the DB expert either. That's why I'm not sure if the tip answers your question. But I think it could be helpful without that.
https://github.com/LMDB/sqlightning

[skywalk]

Sorry, stepping out of SQLite is far too complicated for my situation.
I will instead create a ":memory:" database connection and ATTACH the file based database to this connection as "dbf". I wanted the security of the database connection being file based for the main, but without shared cache, I will compromise a bit to have the main database be ":memory:". Any crashes should only lose 1 transaction.