Exercise 1: The Rollback Journal, Crash Recovery, and sqlite1-1's Own Caveat — Possible Solution ==================================================================== THE ROLLBACK JOURNAL MECHANISM ------------------------------ Per this chapter, "before modifying the actual database file, SQLite first writes the original, pre-modification content of the pages about to change into a separate journal file. If the transaction completes successfully, the journal is deleted." Before SQLite touches even one byte of the actual database file for a given transaction, it first saves a copy of whatever those specific pages currently contain — their PRE-transaction state — into a separate journal file. Only after that safety copy exists does SQLite proceed to actually modify the main database file. Once the transaction finishes successfully, the journal (having served its purpose) is deleted. HOW THIS ALLOWS SAFE RECOVERY FROM A CRASH MID-WRITE ------------------------------ Per this chapter, "if the process crashes mid-write, the next time the database is opened, SQLite detects the leftover journal file and automatically uses it to roll the (possibly partially-written) main file back to its last known-good state." If a crash happens partway through modifying the main file, the journal file — containing the original, pre-transaction content of exactly those pages — is left behind on disk, never deleted (since deletion only happens after a successful, complete transaction). The next time SQLite opens that database, it notices this leftover journal file, recognizes it as a sign the previous transaction never completed, and automatically uses the saved original page content to overwrite whatever partial changes were made — restoring the database to its last fully-consistent state before the crash, with no manual intervention required. CONNECTING THIS BACK TO SQLITE1-1'S OWN CAVEAT ------------------------------ Per this chapter, "this is exactly the mechanism sqlite1-1's own caveat pointed toward: copying the main database file mid-write, without its accompanying journal, can capture a half-written, inconsistent state." sqlite1-1's own cp mydata.db backup.db example noted this was only safe "when no write is currently in progress." Now the reason is concrete: if a copy is taken WHILE a write is in-progress, the copy captures the main file in its partially-modified state, but WITHOUT the journal file that would normally let SQLite detect and repair that inconsistency automatically. A copy taken while idle avoids this because there's no partial write and no leftover journal to worry about at all. WHY THIS WORKS AS AN ANSWER ------------------------------ It walks through the journal's own write-then-modify-then-delete sequence, explains precisely how a crash mid-sequence leaves the journal behind as the mechanism enabling automatic recovery, and explicitly ties this back to the earlier chapter's own stated caveat about copying a database file mid-write.