On 02/14/2018 04:03 AM, Simon Slavin wrote:
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.
(B) is an understandably common misconception. sqlite3_shutdown() frees
resources that were allocated by sqlite3_initialize() or
sqlite3_auto_extension() and must be called after all SQLite connections
have been closed. These resources are either trivial or non-existent on
almost all platforms - so in practice sqlite3_shutdown() is only useful
on embedded systems that do not free such resources automatically when a
process exits, or in other obscure circumstances.
Don't call sqlite3_shutdown()!
http://sqlite.org/c3ref/initialize.html
Dan.
<https://sqlite.org/c3ref/initialize.html>
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
termination.
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.
Simon.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
