On 13 Feb 2018, at 8:22pm, Chris Brody <chris.br...@gmail.com> wrote:

> Thanks Simon for the quick response.

You're welcome.

> Can you clarify the following:
> - Does this imply that a SQLite database may be left in some kind of
> unrecoverable, corrupted, or otherwise invalid state in case an
> application would terminate without calling sqlite3_close() on all
> open database connections?

Your program is meant to do one of two things:

A) Close all database connections.
B) Call sqlite3_shutdown(), which will close everything and release all memory 
in a correct and orderly fashion.


If you do not do either of these things, it /might/ be possible to find that 
your database files are left in an inconsistent state.  But even if it does 
happen, the next time the database is opened using the SQLite API, SQLite will 
figure out what happened and restore the database to valid state.

Rather than unexpected termination you should be more worried about a program 
crashing or losing power in the middle of a SQLite call.  But SQLite was 
written to cope with this, too.

> - If yes, what can a programmer do to protect the data in case an
> application is abruptly terminated for any reason?

If there was anything, the fix would already be built into SQLite, or it would 
be prominently listed in the "how to use SQLite" pages.

If you think you have a corrupted database, reopen it using the SQLite API, 
then (after using it if you want) close it properly.  This is always the answer 
unless you want to forensically investigate the cause of corruption. 

> - Would using SQLITE_DEFAULT_SYNCHRONOUS=3 (extra durable) help
> mitigate this kind of possible corruption?

I'm gonna let one of the developer team answer this.  I suspect that the answer 
depends on your operating system, and your storage device and its driver.

Durability is the foe of execution time.  It would be possible to make SQLite 
one third as subject to corruption -- at the cost of every command that reads 
or writes the database taking nine times as long.

> I think this is especially important for mobile apps which may be
> terminated without notice, especially when using hybrid app frameworks
> such as Cordova/PhoneGap.

No mobile OS I'm aware of will allow termination of a program while it's in the 
middle of a SQLite API call, unless some other part of the application is hung 
and refusing to terminate.  This is part of the design of mobile operating 
systems which are designed to expect unpredictable backgrounding and 

I can go into great detail about how iOS warns a program about backgrounding 
and termination, so that it can close in a graceful manner.  I assume Android 
does something similar.

>> However, the SQLite library goes through heroic measures
>> [...]
> I am sure that this was at the cost of many heroic programmer hours.

Not to mention the proportion of SQLite's source code which is devoted to 
detecting and fixing corruption rather than doing mundane database work.

sqlite-users mailing list

Reply via email to