Editing SQLLite files in Temporary space

Several threads about editing files in Temporary instead of at f location especially if original file is stored on a network share or a flash drive.

Just wondering if anyone has any clever code for file open to backup and copy to temporary before editing…
and then conversely… copying the file back out to its original location on close or quit.

Or

Any advice on doing the above, pitfalls to avoid, etc.

use the SQLIte BACKUP function(it is in the Xojo Lang Ref)
this will copy an SQLite database from one place to another

I use it to copy a file based database to a memory based one, so the user can do a “save” vs “save as” or chuck all the edits with a “revert”

I see the documentation at: Page Not Found — Xojo documentation

I understand how I would create a folderitem and call backup to back the sql lite file up to that file location.

But how do you use it to back up into memory?

So if I used this method… I would backup the file on the hard drive, network, or flash drive… whereever it is stored… and “back it up” into a copy in memory… then when they save, or close or whatever… write it back out to the original location also using Backup again?

you can back it up to another disk based file, or to a memory database

masterDB.backup(myBackupDB , Nil , -1)

where myBackup is another SQLite database instance that you have already established, either memory, or disk based
to update master, just swap the arguments

Just found this little tidbit in the documentation (pasted at bottom)… Wish I would have found that a couple of years ago. DOH!

Here’s what I’ve got:

  • create a sql lite db in memory
  • use BACKUP to copy the file based sqllite DB to the one in memory
  • use that “in memory” version in my program
  • then on save, close, etc. copy the inmemory version back out to the file one.

Did I miss anything?


In-Memory Database
An “in-memory database” is a SQLite database that exists only in memory; there is no related file on disk. It works exactly as a SQLite database except it is very fast and completely temporary.

To create an in-memory database, just create a new SQLiteDatabase instance and connect to it:

Dim inMemoryDB As New SQLiteDatabase
If inMemoryDB.Connect Then
// Can now use inMemoryDB
End If
You can use the Backup method to save an in-memory database to a file on disk.

thats pretty much it.

Like I said, I use it for an app where the “projects” are stored as an SQLite Database

Load Project - moves from disk to memory database
Save Project - moves from memory to disk database
Or they can reject or revert at any time, since the disk file isn’t modified unless they “save”

So all transactions happen on the in-memory one.

Now that does preclude any chance of multiple users of course

But you just use Backup for this to go back to the disk and it is all seemless?
I don’t have to delete the old file first?

Also if the original file was on a flash drive that is now removed… what would you do?
Just warn the user? Save the data to yet another file somewhere so they can recover if need be?

yup… it is not a normal “file copy”… it requires the database to exist… it will deal with the tables.

[quote=391167:@Tim Turner]But you just use Backup for this to go back to the disk and it is all seemless?
I don’t have to delete the old file first?

Also if the original file was on a flash drive that is now removed… what would you do?
Just warn the user? Save the data to yet another file somewhere so they can recover if need be?[/quote]
I’m not sure it it creates it or not, since my only use did require it to exist

I’m using Storm not SQLLite directly. The Connect only connects to a FolderItem sql lite database.

Sooo… am testing just copying db to Temporary and then connecting to that.
But on Mac I get “TemporaryItems doesn’t exist”

If I should start a new thread let me know.