SQLite, keep connection open or close every time?

[Lynn Fredricks]

> Not sure, but the help desk system Spiceworks uses sqLite. 
> They warn that  the service needs to be stopped before 
> opening and querying the database to avoid damaging it (I 
> think they mean the data, not the structure). I think the 
> point is that if someone has the DB open for write when it is 
> ready to write itself, that can cause problems.
> 
> I suppose for these kinds of local single user databases, an 
> app should be written to check for success, and have retry 
> loops and such, or better yet as discussed in other threads, 
> have an agent query the database so their really is a single client.

SQLite is a type of transactional database so you'd expect you'd just be
able to roll back and be done, but its still designed to be a single
connection "local" database. I credit them in that they've done a lot of
things to improve it over the years.

The short of it as I understand it is that when its time to write,
everything gets locked down. That's just for a moment of course. WAL
(write-ahead logging) lets multiple users keep addressing the database but
it isn't a complete replacement for the networking infrastucture you find in
"real" database servers, which vendors of such keep improving year after
year to be able to work efficiently with very large data sets, multiple
server applications hitting the same database and highly concurrent
applications.

This is why a lot of people claim its "good enough" for X but if you ask
them if they would guarantee its reliabily vs <insert server brand x>, they
sort of shuffle their feet and refer you to the SQLite website. If it really
was "good enough" for all uses, then database server vendors would just toss
out that old code they have and use the public domain SQLite code instead.

You can take this with a grain of salt since its coming from a database
vendor and a sales guy. We integrated SQLite into our Valentina Server
because, for the most part, its now good enough to use within a server when
combined with all the networking infrastructure we've created for use with
the Valentina database engine. But we don't shuffle our feet as a result ;-)


Best regards,

Lynn Fredricks
Paradigma Software
http://www.paradigmasoft.com

Valentina SQL Server: The Ultra-fast, Royalty Free Database Server