Restore a SQLite db

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?

I think in SQLite it is “DUMP”

Other direction… making a backup is .Backup

getting it BACK into memory?

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

just SQLExecute the text of the file you dumped.

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

or do you want to merge ?

[quote]copy all the data from the newly attached backup db into the in memory db (delete from and insert into using a select )
done[/quote]

Which is what It looks like I’ll have to do.
It just doesnt seem efficient.

I consider the in memory db like a memoryblock.
You can dump that and restore it in one line of code.

I’d have expected

myb.backup -> to file mydb.restore <-from file.

There might be dozens of tables to work through.
Ho hum

close the in memory one you have
reopen the backup as though it was the original and do whatever you did to bring things into memory

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.

Im obviously missing something
Xojo’s SQLiteDatabase doesnt have a .dump method

Converting An Entire Database To An ASCII Text File

Am I going crazy here?.
None of these links are Xojo syntax.
I could understand it if this was a Python forum.

dump now looks like a command line function, yes?
Not going to work on an in-memory database.

Got it working using the ATTACH method.

Once attached, I run a selection of statements like this:

Insert into  tablename (field1,Field2,etc) select Fields1,field2, etc   from externaldb.tablename

Then I detach the database.
Thats fast enough.

Thats what I suggested way back :stuck_out_tongue:

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

bear in mind that other than the backup method, none of the others will restore indexes, views or triggers

Backup and Dump:

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

sure you can - read the catalog of the newly attached db and run the sql thats stored there to recreate them in the in memory one

Attach and Insert copies ONLY the data… not a single example above handles the other attributes of the database

you did read my prior post about HOW to do this

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.