3 ms·

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

> 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?
- teraflop 6y ago
  > What's the reason that it can't act like a filesystem image, and keep everything in one file? 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 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.
cesarb 6y ago
> 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 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"?
- teraflop 6y ago
  > This breaks one important invariant: unless the user clicks "Save", the original file must not be modified in any way. 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 https://sqlite.org/backup.html
- krisoft 6y ago
  I don’t understand what you are asking. When the user clicks “Save” that’s when your application calls the sqlite api to update the database. When the user clicks “Save as” the application calls the sqlite api to create a new database file, it then creates the database schema and dumps the internal representation of the document into it. 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.

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?

teraflop 6y ago
> What's the reason that it can't act like a filesystem image, and keep everything in one file? 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 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.

cesarb 6y ago

> 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 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"?

teraflop 6y ago
> This breaks one important invariant: unless the user clicks "Save", the original file must not be modified in any way. 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 https://sqlite.org/backup.html
krisoft 6y ago
I don’t understand what you are asking. When the user clicks “Save” that’s when your application calls the sqlite api to update the database. When the user clicks “Save as” the application calls the sqlite api to create a new database file, it then creates the database schema and dumps the internal representation of the document into it. 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.