Comment by rlpb
5 years ago
Would such an app hold the sqlite database open while the user has the document "open", and live-write user changes back to the database immediately? Or would it follow the traditional model of the user choosing to "Save"?
I worked on an app that did the former many years ago (to an Access database, not sqlite), and it did not go well because this broke user expectations on the usual "open/save/save as" model.
The save model at the UX level is completely orthogonal to the application file format. You could implement either model with an SQLite DB file.
One quick-and-dirty way to maintain the traditional model would be to copy the on-disk DB into an in-memory DB, then make any changes in memory; when the user clicks Save, the application would then move the old on-disk DB, open a new one with the old's original filename, copy everything from the in-memory DB into the new on-disk DB, and delete the old on-disk DB.
Another option would be to keep both the in-memory and on-disk copies open, and then update the on-disk version in-place with the in-memory version's data when the user clicks Save. SQLite has built-in support for connecting multiple DBs (such that you can query both in the same statement) to make this straightforward.
I think the former is preferable and most modern users will prefer it. However if your users are upset about it, you could do the later, or you could do the former and give them a dummy/placebo "save" option that does nothing, or maybe VACUUMs the DB.
The problem is not Save, the problem is when you close the app without using Save - the user will expect that to work like Cancel and discard all session changes.
I guess you could send changes to the DB on-the-fly inside a transaction, so when the user clicks "save" it's just a matter of running COMMIT? Not sure what effect that would have on performance as the transaction grows, though.
I think it would depend on the app (e.g. how document-like it is), but nowadays people will be much more used to the instant-save model due to things like Google Docs, OneNote, and phone apps.
Or discard the "save" metaphor and move to infinite undo.