[SQLite] OpenDatabase :memory: shared cache?

Just starting out? Need help? Post your questions and find answers here.
User avatar
skywalk
Addict
Addict
Posts: 3555
Joined: Wed Dec 23, 2009 10:14 pm
Location: Boston, MA

[SQLite] OpenDatabase :memory: shared cache?

Post by 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)
The nice thing about standards is there are so many to choose from. ~ Andrew Tanenbaum
uweb
User
User
Posts: 98
Joined: Wed Mar 15, 2006 9:40 am
Location: Germany

Re: [SQLite] OpenDatabase :memory: shared cache?

Post by 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
Please pardon my English, my native tongue is German.
User avatar
skywalk
Addict
Addict
Posts: 3555
Joined: Wed Dec 23, 2009 10:14 pm
Location: Boston, MA

Re: [SQLite] OpenDatabase :memory: shared cache?

Post by 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.
The nice thing about standards is there are so many to choose from. ~ Andrew Tanenbaum
Post Reply