3 ms·

> 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:/

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

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.

skissane 6y ago

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