How to protect SQLite database from corruption

views:

answers:

+1 Q:

How to protect SQLite database from corruption

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?

+1 A:

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

Jonathan 2010-06-27 08:44:22

That's a very interesting read, though it doesn't help me decide which method to use. I'm guessing that even by closing a database, you're not guaranteeing that data has been committed to disk since that's up to the OS and hardware, not SQLite; so the same risk of corruption exists even with a closed database. However, by closing the database you should also be flushing the journal file. So perhaps it is somewhat safer to close the file continually rather than keeping it open continuously.

Andrew 2010-06-27 10:57:13

I had think about it... and as you said, "that's up to the OS and hardware" so... maybe the best aproach in one OS could not be the best aproach in other one. In addition, we may have performance issues if we're open and closing (and flushing) the file every few second (in mobile plataforms). So... I can't give you an answer :(

Jonathan 2010-06-27 12:02:53

Just to follow up, because of race conditions I can't get rid of, I've had to abandon the continual open-and-close approach and keep the DB open permanently through the life of the application. To deal with corruption, I am making hot copies of the database. This technique also worries me a bit (making a copy of an open file), but because SQLite uses journaling and transactions, this should still be safe. Your link helped me understand SQLite much better, so I'm marking this answered. Thanks!

Andrew 2010-06-29 01:52:21

Thanks to you! I've also learned some interesting things as a result of your question.

Jonathan 2010-06-29 07:42:01

ansaurus

tags:

views:

answers:

How to protect SQLite database from corruption

related questions