14 ms·

> The atomic update capabilities of SQLite allow small incremental changes to be safely written into the document. This reduces total disk I/O and improves File

by rlpb 6y ago

> The atomic update capabilities of SQLite allow small incremental changes to be safely written into the document. This reduces total disk I/O and improves File/Save performance, enhancing the user experience. 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.

mlyle 6y ago
Modern word processing formats write down incremental changes, to avoid writing all of a massive document back to disk. 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.
- mixcocam 6y ago
  This is indeed an issue. Most apps allow a (non default) saving manipulation which will remove the history.
trevorishere 6y ago
Microsoft more or less does this already with their Office Open XML file format in SharePoint Online/OneDrive. Small changes are read/written as users edit the doc. Real-time co-authoring locks on a per-paragraph basis.
teraflop 6y ago
> 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.
- rlpb 6y ago
  > 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. 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.
  - ReactiveJelly 6y ago
    Well, you could write a fresh SQLite DB on every Save command, and blow away the old one, thus losing all of SQLite's nice fsync handling. 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.
- ReactiveJelly 6y ago
  > But if you manually delete the journal, or otherwise separate it from its database, you get data corruption. I didn't believe you, but you're right. https://sqlite.org/wal.html https://sqlite.org/wal.html > 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 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?
okennedy 6y ago
SQLite already has logic for this built in: transactions (https://www.sqlite.org/lang_transaction.html https://www.sqlite.org/lang_transaction.html) 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.
- ReactiveJelly 6y ago
  I hope some day auto-save and VCS converge to result in some kind of continuous undo system. 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.
- iggldiggl 6y ago
  OTOH, when I open some document as a reference document, I most certainly don't want any changes I accidentally (or even purposely) might have made to it to be saved without any further questions.

by rlpb 6y ago

mlyle 6y ago

Modern word processing formats write down incremental changes, to avoid writing all of a massive document back to disk. 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.

mixcocam 6y ago
This is indeed an issue. Most apps allow a (non default) saving manipulation which will remove the history.

trevorishere 6y ago

Microsoft more or less does this already with their Office Open XML file format in SharePoint Online/OneDrive. Small changes are read/written as users edit the doc. Real-time co-authoring locks on a per-paragraph basis.

teraflop 6y ago

> 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.

rlpb 6y ago
> 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. 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.
- ReactiveJelly 6y ago
  Well, you could write a fresh SQLite DB on every Save command, and blow away the old one, thus losing all of SQLite's nice fsync handling. 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.
ReactiveJelly 6y ago
> But if you manually delete the journal, or otherwise separate it from its database, you get data corruption. I didn't believe you, but you're right. https://sqlite.org/wal.html https://sqlite.org/wal.html > 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 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?

okennedy 6y ago

SQLite already has logic for this built in: transactions (https://www.sqlite.org/lang_transaction.html https://www.sqlite.org/lang_transaction.html) 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.

ReactiveJelly 6y ago
I hope some day auto-save and VCS converge to result in some kind of continuous undo system. 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.
iggldiggl 6y ago
OTOH, when I open some document as a reference document, I most certainly don't want any changes I accidentally (or even purposely) might have made to it to be saved without any further questions.