Page 1 of 1
SQLite in-memory database to *Buffer
Posted: Sat Jul 24, 2021 4:53 pm
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?
Re: SQLite in-memory database to *Buffer
Posted: Sat Jul 24, 2021 5:15 pm
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.
Re: SQLite in-memory database to *Buffer
Posted: Sat Jul 24, 2021 5:42 pm
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.
Re: SQLite in-memory database to *Buffer
Posted: Sat Jul 24, 2021 5:47 pm
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?
Re: SQLite in-memory database to *Buffer
Posted: Sat Jul 24, 2021 7:07 pm
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.
Re: SQLite in-memory database to *Buffer
Posted: Sat Jul 24, 2021 7:36 pm
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
Re: SQLite in-memory database to *Buffer
Posted: Sat Jul 24, 2021 7:44 pm
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.
Re: SQLite in-memory database to *Buffer
Posted: Sat Jul 24, 2021 8:51 pm
by infratec
Or you have to use an external sqlite dll whre the functions are inside.