How would this work without breaking the conventional "Save" paradigm? Unless the app tracks all incremental changes to apply when the user clicks "Save" instead of dumping it all back to disk, which seems difficult and error-prone to implement.
That's the point of using SQLite instead of implementing it yourself. Because of how SQLite supports transactions, you can just update the database as you go; your changes will be physically written to disk, but they won't actually replace the old version from a reader's perspective until you explicitly commit, which is an atomic (and typically fast) operation.
The downside of this approach is that if the application crashes while writing to disk, the database file itself might be in an inconsistent state, accompanied by a rollback journal (or write-ahead log) that contains the necessary information to recover it. But if you manually delete the journal, or otherwise separate it from its database, you get data corruption.
That's probably fine as long as the user is a sysadmin who can be trusted to Just Not Do That(tm), but it's very user-hostile behavior for an office suite.
You can't "just" update the database as you go. It'd be a major refactor, since typically an app will use an in-memory working copy instead of referring to the storage engine all the time.
Or if your data structures can be modeled as vectors or ordered maps, use SQLite directly as much as possible. It _is_ a database engine, so it's already got btrees for fast lookups.
Hell, one time I played back video from a SQLite file. Not HD, but it was a peculiar format where I couldn't use any normal codec, and 30 FPS VGA recorded and played just fine.
I didn't believe you, but you're right.
> If a database file is separated from its WAL file, then transactions that were previously committed to the database might be lost, or the database file might become corrupted. The only safe way to remove a WAL file is to open the database file using one of the sqlite3_open() interfaces then immediately close the database using sqlite3_close().
https://sqlite.org/lockingv3.html
> If a crash or power failure occurs and results in a hot journal but that journal is deleted, the next process to open the database will not know that it contains changes that need to be rolled back. The rollback will not occur and the database will be left in an inconsistent state.
It's a bummer that SQLite is a "database in one file" but many operations actually require a 2nd or 3rd file.
What's the reason that it can't act like a filesystem image, and keep everything in one file? If I mounted an ext4 image in userspace and modified it a bunch, it wouldn't be corrupt, right? Is that just because SQL has stronger atomicity requirements than a filesystem?
I suspect it's mostly because putting everything into one file instead of two isn't really necessary for most use cases, and it would significantly complicate the implementation.
The main SQLite database file is organized into numbered fixed-size pages, which are used to store B-tree nodes. The B-tree structure itself is fairly complex, but the journal file format is extremely simple: it's essentially just a list of page indices along with their old contents. (WAL mode is very similar, except that the new contents are stored instead. See https://sqlite.org/fileformat2.html for the details.)
The catch is that both the database and the journal need to be independently resizable. So if you wanted to store them both in a single file, you would need to either reserve a fixed amount of space for the journal up front, or allow the journal and/or the database itself to become fragmented across different byte ranges of the file. The latter option would require you to keep track of an extra layer of indirection to know where the journal data is stored, and that metadata would itself need to be efficiently, atomically updateable.
Essentially, you would be building an entire rudimentary filesystem just to store two logical files in one physical one, when the OS already has a perfectly good filesystem available for you to use.
SQLite actually provides an example VFS which stores everything in one file, except the one file has to be of fixed size – https://www.sqlite.org/src/doc/trunk/src/test_onefile.c
It is intended as an example of how SQLite can be used on an embedded system which has no filesystem, just a raw disk.
I think someone could create a VFS which stored everything in one file without the fixed size limitation. I think you'd need to have three types of pages in the file – data, journal, and metadata (which marks which pages are of which type, using a bitmap or extent list or whatever). The VFS would present two separate files, data and journal, but actually store them as one file containing these three types of pages. You'd need to think carefully about the ordering of updates to the three page types to ensure atomicity in the case of a crash. A fair amount of work, but doable if someone wanted to. (Maybe even the SQLite developers might do it if they thought it was a good idea, or if someone was willing to pay them to do it.)
This breaks one important invariant: unless the user clicks "Save", the original file must not be modified in any way. It doesn't matter if the changes are ignored by readers; if "Save" is a separate operation, it's the only place which should modify the original file.
Also, how would this work with "Save As"?
SQLite's default rollback journaling mode doesn't satisfy this property, but WAL mode does: updates will be first written to the WAL file, and automatically copied to the main database file only after at least one transaction has committed.
> Also, how would this work with "Save As"?
SQLite also has a "backup" API that I think could be used for this purpose: https://sqlite.org/backup.html
You might be confused by the GP comment. It says that if something bad happens with the computer after your application started saving, but before the transaction is commited then the database still will be in a consistent state. (Altough the document will be in the pre-save state.) If the application is the “click save” kind then here we are talking about what happens if the power goes out in that fraction of a second after you hit save, but before the cursor stops being a hourglass.
More broadly though, tech seems to be migrating away from explicit saves in general (e.g., when's the last time you needed to save something in a web-app). Maybe that's not such a bad thing? I learned to develop a reflexive cmd-/ctrl-s twitch as a youth, but I would have been just as happy to have not lost hours of work to power outages and/or cooperative multitasking.
Right now the only reason I leave a file unsaved is to act as an extra layer of ad-hoc version control, and it's not that good.
Sometimes this causes people embarrassment, because if you send a normally saved file to someone else, they can often see the change history and intermediate versions.