Personally, I only use transactions when I'm doing inserts, updates, and deletes for one or more records. I never keep a transaction open for longer than I need to make changes to the database.
What I would suggest is scrap the idea of keeping a transaction open at all times, but work off of a backup of the original, and make your constant data updates to the backup. You can use the SQLite backup API and copy the original database to memory, or, if tight memory constraints exist, backup to a temporary file elsewhere on the file system. Run transactions when something worth while is to be sent to the database, such as a form on the UI has been completed. The effect of doing it in this manor is that when the user wants to use "SAVE", you use the SQLite API to backup and overwrite the original file, and still maintain the handle on the work file. If the user wants to "SAVE AS", you use the backup API only when a successful "Save As" prompt occurs, change the string pointer of the most recent "SAVE" (Which I would bet is probably just a string in the "Open" dialog). The business logic of "Save As" can also change at this point as well as to whether the next "SAVE" is going to overwrite the original file, or the file you just saved. SOP is usually to save to the most recent file. Perks for this method: * If something happens and the data integrity becomes compromised while the software is in use, you're screwed on the work file, not the original. * If you use a file system to store your work in progress and you have a power outage, you have the opportunity to continue from wherever you were from a data standpoint, depending on what state the database was in when the power outage hit. * If you want to implement timed backups, all you have to do is set a timer and call the backup API command against your (lets say) in-memory database to a new file. **The caveat of using the backup API is that IF your beating on the work file constantly, and the database is large, the backup may not be able to complete. If a change happens to the work database while the backup is happening, the backup starts over from square one. If you can stop the simulation while the backup is in progress, you can probably get around that minor limitation. On Wed, Apr 3, 2013 at 5:11 PM, Tiago Rodrigues <wtrm...@gmail.com> wrote: > Hello all, > > I'm writing a small simulation app and for it I would like to use SQLite3 > as an application file format, as suggested by the "Appropriate uses for > SQLite" page in sqlite.org. More specifically, the page suggests calling > BEGIN TRANSACTION when opening a file and calling COMMIT when saving it > again, or ROLLBACK if closing it without saving. Simple enough, up until > the point where I want to implement a "Save As..." menu option, where the > current state of the application is saved to a new file (new database) with > the changes, while the changes in the current file are rolled back. > > For that, the simplest idea would be to use the online backup family of > functions, calling sqlite3_backup_init() and sqlite3_backup_step() on the > database, calling COMMIT on the backup and ROLLBACK on the original. > Naturally, that doesn't work, as you can't back up a database in the middle > of a transaction -- sqlite3_backup_step() returns SQLITE_BUSY. > > That being said, has anyone on the list encountered (and hopefully solved) > this particular problem? This probably means I have to scrap the idea of > holding a transaction through the editing of the file, but then should I > copy the database to a memory-based db? Any particular tips or caveats? > > I appreciate the attention, > > -Tiago > > -- > In those days, in those distant days, in those nights, in those remote > nights, in those years, in those distant years... > - Gilgamesh, Enkidu and the Underworld > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
