SQLite in-memory database to *Buffer

Just starting out? Need help? Post your questions and find answers here.
wayne-c
Enthusiast
Enthusiast
Posts: 335
Joined: Tue Jun 08, 2004 10:29 am
Location: Zurich, Switzerland

SQLite in-memory database to *Buffer

Post by wayne-c »

One of the coolest things IMO is the possibility to create in-memory databases with SQLite:

Code: Select all

Database = OpenDatabase(#PB_Any, ":memory:", "", "", #PB_Database_SQLite)
I use this very often. For the current project I need to preserve this database for further use (e.g. store in a session table in a different database). So I need a function to "copy" this in-memory-database to a PureBasic *Buffer. For the moment I do the round-trip via file system (what is possible with the sqlite3_backup_xxx functions) and then re-load the file. But this seems ugly, I'd rather avoid having to save to disk first.

Is this possible? Any ideas?
As you walk on by, Will you call my name? Or will you walk away?
infratec
Always Here
Always Here
Posts: 6817
Joined: Sun Sep 07, 2008 12:45 pm
Location: Germany

Re: SQLite in-memory database to *Buffer

Post by infratec »

viewtopic.php?f=12&t=47838

viewtopic.php?f=13&t=74406

Code: Select all

;The following backs up one SQlite database to a second SQLite database. Can be used to dump a memory based SQLite database to disc
;or can dump a disc-based SQLite database to a memory one etc.
;Any existing data in the destination database will be deleted.
;Returns #True if successful.
Procedure.i SQLite_BackupSqliteDatabase(sourceDB.i, destinationDB.i)
  
  Protected result, backUp
  
  
  If IsDatabase(sourceDB) And IsDatabase(destinationDB)
    backUp = sqlite3_backup_init(DatabaseID(destinationDB), "main", DatabaseID(sourceDB), "main")
    If backUp
      sqlite3_backup_step(backUp, -1)
      If sqlite3_backup_finish(backUp) = 0 ;#SQLITE_OK
        result = #True   
      EndIf
    EndIf
  EndIf
  
  ProcedureReturn result
  
EndProcedure
You can also open 2 DBs in memory and make a backup to the second memory database.
wayne-c
Enthusiast
Enthusiast
Posts: 335
Joined: Tue Jun 08, 2004 10:29 am
Location: Zurich, Switzerland

Re: SQLite in-memory database to *Buffer

Post by wayne-c »

Thank you @infratec. But you misunderstood my question, or I was not clear enough.

Your examples are: :MEM: --> DISK or :MEM --> :MEM: (another SQlite). What I am looking for is :MEM: --> *MEM, where the target is NOT another Sqlite-Memory-Db, instead I would like to have the target as *Buffer+BufferSize.

Of course, with the solutions you posted I could implement :MEM: --> DISK --> *MEM, but whenever possible I would like to avoid the DISK step.

So the question is still open, other ideas apreciated.
As you walk on by, Will you call my name? Or will you walk away?
wayne-c
Enthusiast
Enthusiast
Posts: 335
Joined: Tue Jun 08, 2004 10:29 am
Location: Zurich, Switzerland

Re: SQLite in-memory database to *Buffer

Post by wayne-c »

I just found this, I guess this is what I am looking for:
https://www.sqlite.org/c3ref/serialize.html
with this flag: https://www.sqlite.org/c3ref/c_serialize_nocopy.html
For an in-memory database or a "TEMP" database, the serialization is the same sequence of bytes which would be written to disk if that database where backed up to disk.
Anybody has done this before?
As you walk on by, Will you call my name? Or will you walk away?
infratec
Always Here
Always Here
Posts: 6817
Joined: Sun Sep 07, 2008 12:45 pm
Location: Germany

Re: SQLite in-memory database to *Buffer

Post by infratec »

Yes, that will do what you want, but...

why?

I don't know the memory overhead, but memory to memory is also in memory, with the advantage that you can still access the copy.

The serialize stuff creates a 'file' in the memory buffer.
You can write it to disk as .sq3 file, but you can do nothing with it in memory.
You have to use DeSerialize to use it again.

And ...
you have to check if those functions are included in th PB compilation of sqlite.
infratec
Always Here
Always Here
Posts: 6817
Joined: Sun Sep 07, 2008 12:45 pm
Location: Germany

Re: SQLite in-memory database to *Buffer

Post by infratec »

In PB 5.73 windows it is not included:

Code: Select all

ImportC ""
  sqlite3_backup_init(pDest.i, zDestName.p-utf8, pSource.i, zSourceName.p-utf8)
  sqlite3_backup_step(sqlite3_backup.i, nPage.l)
  sqlite3_backup_finish(sqlite3_backup.l)
  sqlite3_errcode(db.l)
  
  sqlite3_serialize(db,i, zSchema.p-utf8, *piSize.Long, mFlags.l)
  sqlite3_deserialize(db.i, zSchema.p-utf8, *pData, szDb.l, szBuf.l, mFlags.l)
EndImport



UseSQLiteDatabase()

Debug @sqlite3_errcode()
;Debug @sqlite3_serialize()
;Debug @sqlite3_deserialize()
So you need to use an external dll
wayne-c
Enthusiast
Enthusiast
Posts: 335
Joined: Tue Jun 08, 2004 10:29 am
Location: Zurich, Switzerland

Re: SQLite in-memory database to *Buffer

Post by wayne-c »

I need to store the memory block of the in-memory Sqlite-Database inside an Oracle database for later use (the EXE is terminated and restarted), that's why I need a serialized data buffer.

Anyway, when the functions are not available at all, I'll have to make the round-trip over the file system, unfortunately.
As you walk on by, Will you call my name? Or will you walk away?
infratec
Always Here
Always Here
Posts: 6817
Joined: Sun Sep 07, 2008 12:45 pm
Location: Germany

Re: SQLite in-memory database to *Buffer

Post by infratec »

Or you have to use an external sqlite dll whre the functions are inside.

Code: Select all

UseSQLiteDatabase([LibraryName$])
Post Reply