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?

• c++
• sql
• c
• sqlite

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.