SQLlite for "Save as" and Auto-save feature for a LARGE file

Go To StackoverFlow.com

1

I have a SQLite file that can be 1GB easily (might turn into 10GB). I would like to emulate "save as" and auto-save feature for my desktop application (in C++), similar to Office Word. It would be impractical, in my mind, to make a copy of the whole file each time I want to backup.

I've just started looking into SAVEPOINT. Is this the way to approach this? But it's not clear to me if savepoint will allow me to query inserts I've just made between save points, not until I commit/release.

I would appreciate feedback.

Also, taking a step back, I'm pondering if this save-as/auto-save mechanism is a good idea or not. I'm currently saving every single SQL command instantly into the file (e.g. user text into a comment field in a table). I do have an undo/redo feature implemented in the application. Anyone making desktop application with a large sqlite file?

2012-04-04 08:18
by jobobo
SAVEPOINTS are for transactions. So you can roll-back part of a transaction, rather than all of it. And you really don't want to just leave a transaction open for the duration of your application's use (even if it is single user) - MatBailie 2012-04-04 08:40
Thanks for the comment. Do I not want to leave transaction open because 1) a power failure will not preserve those changes? and 2) I cannot make queries on changes within an open transaction - jobobo 2012-04-04 18:19


2

It depends on what your application is doing. If it's just inserting and deleting rows, you can easily make them undoable (keep a history table — probably best as a temp table unless you want it to persist between runs — and drive your undo/redo functionality off that) but structural changes are much more difficult to undo as SQLite only allows very limited alterations to tables after they've been created (e.g., removing a column would require you to copy the table and delete the old one). That said, as long as you're committing after each change, you can have an application that has no user-visible save action: you just automatically persist the changes to disk with each commit.

The “Save As…” functionality will be slow because it necessarily entails copying the data to a new file, and that takes some time with multiple GB, even if you're using an SSD instead of an HDD. Is it really necessary? Users could just copy the database file themselves (SQLite's nice like that) provided they've not got it open in your application at the time.

2012-04-04 08:51
by Donal Fellows
Thanks for the response. I am making updates to existing table entries (comment field) and adding and removing rows on an existing table (labels). If I were to keep a history table, that means my existing queries will have to be altered, right? I was hoping to defer it to sqlite somehow...

Also, I think I should have separated "Save As" and auto-save as two different questions; I'll probably remove "save as" feature and let users make copies themselves. However, I'm interested in implementing auto-save but without making full copies of the whole sqlite file. thanks - jobobo 2012-04-04 18:30

@jobobo No need to save queries; just inserts and deletes. Look, it sounds like what you really need to do is to stop fiddling around in the details and to take a step back and think through what's going on with the big picture. Get your bearings on what's going on and what you're really trying to do overall before diving back in - Donal Fellows 2012-04-05 12:46
Ads