I'm trying to figure out which is the safest strategy for protecting my (file-based) SQLite database from corruption (in this case, I'm working with Adobe Air, but this could apply to any webkit browser that uses SQLite, including Mobile Safari).
I'm thinking about creating a database connection, keeping it around for only maybe 5 or 10 seconds and then closing it if it hasn't been used during that period. My thinking is that should the machine crash or the application exit abnormally, chances are good that the file will already be closed and thus less likely to get corrupted. But I know that the more often you open and close a filebased DB, the more likely it is you'll have a critical error.
I'm sure I'm over-thinking this, but for my application it's critical that in the event of a system crash, the application can recover cleanly and quickly and that means I have to try to protect the DB as much as I can.
Does anyone know which strategy is likely to be safer?
At the end of this document
File Locking And Concurrency In SQLite Version 3
There are a section named "6.0 How To Corrupt Your Database Files" that discuss corruption hipotetical curroptions problems in sqlite. "Things that can can go wrong".