Nothing ever asks SQLite whether the file is still sound. There is no PRAGMA integrity_check, no quick_check, nowhere. Corruption is discovered the day a query fails — which is the worst possible moment, because by then every backup taken since it started is a copy of a broken file.
That is what couples this to the automatic-backup issue: a backup strategy without an integrity check does not protect the library, it propagates the damage on a schedule.
Shape:
PRAGMA quick_check at startup rather than integrity_check. The quick variant skips the most expensive cross-checks and is fast enough to run on every launch on a database this size; the full check belongs behind a button, not on the startup path.- On failure, say so before the user writes anything else, and point at the file. Silently carrying on is how a salvageable database becomes an unsalvageable one.
- The recovery path matters more than the detection: offer to move the damaged file aside and start fresh, so the user is not stuck in a loop with an app that refuses to open.
VACUUM INTO on a partly-readable database often rescues most of it and is worth trying before giving up.
Where it belongs: the same place the storage failure is reported at startup — the two share a dialog and a decision ("what do I do with a database I cannot use?").
Note on cost: quick_check reads the whole file. On a small library that is milliseconds; if the corpus ever grows to the sizes discussed in the canvas-loading issue, this should move to a background check or a periodic one rather than every launch.
Done when: a corrupted database is named as such instead of surfacing as a random command failure, and the user has a way out that does not involve deleting a file by hand.
Nothing ever asks SQLite whether the file is still sound. There is no
PRAGMA integrity_check, noquick_check, nowhere. Corruption is discovered the day a query fails — which is the worst possible moment, because by then every backup taken since it started is a copy of a broken file.That is what couples this to the automatic-backup issue: a backup strategy without an integrity check does not protect the library, it propagates the damage on a schedule.
Shape:
PRAGMA quick_checkat startup rather thanintegrity_check. The quick variant skips the most expensive cross-checks and is fast enough to run on every launch on a database this size; the full check belongs behind a button, not on the startup path.VACUUM INTOon a partly-readable database often rescues most of it and is worth trying before giving up.Where it belongs: the same place the storage failure is reported at startup — the two share a dialog and a decision ("what do I do with a database I cannot use?").
Note on cost:
quick_checkreads the whole file. On a small library that is milliseconds; if the corpus ever grows to the sizes discussed in the canvas-loading issue, this should move to a background check or a periodic one rather than every launch.Done when: a corrupted database is named as such instead of surfacing as a random command failure, and the user has a way out that does not involve deleting a file by hand.