Working with an in-memory SQLite database.
Ive copied the code to back it up to a file now and then. Works nicely
What I cant find is an equivalent ‘Restore’ command, where I browse for a file, dump the db I currently have, and read a whole database back into memory like the previous one.
Theres no ‘Restore’ method.
What am I missing?
Right now it looks like I will have to open the external database, Select * from the table/s I want, and look through the recordset adding a row at a time into my in-memory one.
Yeuch.
I could forget about ‘in memory’ and use a disc-based db all along, I guess, but Im trying to keep the speed up and avoid locking issues
attach to the backup (see sqlite’s attach command
copy all the data from the newly attached backup db into the in memory db (delete from and insert into using a select)
done
that restores the backup completely by wiping out whatever you have
That could be 8 hours typing for someone, starting from a blank db
I guess I’ll have to see how long it takes to open a db, read all the records and insert them one by one.
Just seems daft that I can save as a ‘blob’ with one line of code, but cant restore from the ‘blob’ the same way.
Edit: couldnt get much from the SQLite docs, but I think the language ref is what I need.
[code]Dim attachDBFile As New FolderItem(“savedbackup.sqlite”)
If currentDB.AttachDatabase(attachDBFile, “externaldb”) Then // currentDB is already connected elsewhere
//do something here to copy from externaldb to my internal tables
End If
[/code]
I’ll come back and update when its working, as I wont be the only one who needs to do this.
@Dave S and @Jean-Yves Pochez are pointing you in the way I would do it @Jeff Tullin.
To restore a backup…
Open up the backup as a file based db.
Dump it to file (dump, not backup this time).
Create the in-memory database.
Execute the contents of the dump file against the in-memory db.
Here’s a discussion on SO where someone wants to copy a file based db into a memory db (via Python) without insert … select.
Alternatively use insert … select format to get each table’s data, at least that way you can verify that the schema is good in the backup etc.
You could literally wipe out the in memory database (check out the data you get by running “select * from sqlite_master”)
You can get all the names of tables, indexes etc etc and all the sql required to create them
And you can read all this from the attached one as well so you can drop all the tables in the in memory one , then recreate all the tables etc from the data in the attached database & insert all the data into the in memory one using “insert into select” as you already are
you should be able to restore it exactly as it was
Dim My_SQLiteDatabase As New SQLiteDatabase
If My_SQLiteDatabase.Connect Then
My_SQLiteDatabase.SQLExecute "Create Table MyTable (MyTable_Id Integer Primary Key, MyTextField Text)"
My_SQLiteDatabase.SQLExecute "Insert Into MyTable (MyTextField) Values ('Some Data')"
Dim My_SQLiteDatabaseBackup As New SQLiteDatabase
My_SQLiteDatabaseBackup.DatabaseFile = SpecialFolder.UserHome.Child("MyTest.SQLiteDatabaseBackup")
If My_SQLiteDatabaseBackup.CreateDatabaseFile Then
My_SQLiteDatabase.BackUp My_SQLiteDatabaseBackup, Nil, -1
My_SQLiteDatabaseBackup.Close
End
My_SQLiteDatabase.Close
Dim My_Shell As New Shell
My_Shell.Execute "echo '.dump' | sqlite3 " + My_SQLiteDatabaseBackup.DatabaseFile.NativePath + " > " + SpecialFolder.UserHome.Child("MyTest.SQLiteDatabaseDump").NativePath
End
Restore:
Dim My_SQLiteDatabase As New SQLiteDatabase
If My_SQLiteDatabase.Connect Then
My_SQLiteDatabase.SQLExecute TextInputStream.Open(SpecialFolder.UserHome.Child("MyTest.SQLiteDatabaseDump")).ReadAll
Dim My_RecordSet As RecordSet = My_SQLiteDatabase.SQLSelect("Select MyTextField From MyTable")
If Not My_SQLiteDatabase.Error And My_RecordSet <> Nil Then
If Not My_RecordSet.EOF Then
MsgBox My_RecordSet.Field("MyTextField").StringValue
End
My_RecordSet.Close
End
My_SQLiteDatabase.Close
End
try
select * from sqlite_master
and see whats in there
you can read ALL the sql from the attached db and execute it on the in memory one and then insert all the data
thats what I said way back
this may be close (I wrote it in line in the forum so there may be bugs)
// first drop everything in the in memory db
dim rs as recordset = db.sqlselect("select name, type from sqlite_master")
while rs <> nil and rs.eof <> true
dim stmt as string = "drop " + rs.field("type").stringvalue + " " + rs.field("name").stringvalue
db.sqlexecute stmt
wend
// now read all the sql from the attached db and recreate everything in the in memory one
rs = db.sqlselect("select sql from attached.sqlite_master")
while rs <> nil and rs.eof <> true
db.sqlexecute rs.field("sql").stringvalue
wend
// and now insert all the data from the attached into the in memory using insert into
[quote]Edited 1 hour ago by Norman P
Thats what I suggested way back :P[/quote]
You did.
I didnt understand it then, and I was also still looking for a 1-line solution.
I have attach as a solution now, and its good enough.
Theres a point where it makes no sense asking for a one line solution and spending days trying to find one.
Good enough is good enough.
The SQL_Master thing looks interesting though. I’ll have a play.