SQLite, keep connection open or close every time?

[Peter Haworth]

Perfectly explained Lynn.  The SQLite website is full of caveats about
directly addressing SQLite dbs over a network, mainly because they use the
native file system locking mechanisms which, they claim, don't work very
well in a networked environment.

Having said that, the SQLite website is itself driven by a SQLite database,
which they use as an example of how SQLite can work in a networked
environment under reasonably heavy, read-only loads.  Of course they have
almost exclusively readers, except when they themselves are changing the
website due to a new release or something similar.  As you say, WAL allows
concurrent reading with one writer so even that probably isn't a problem in
their environment.

There are a couple of SQLite server packages out there (including your own
of course!) but I've never used them and not altogether sure why anyone
would go that route rather than use a db that is designed for network
purposes from the ground up, except perhaps for costs reasons.  Having said
that, I've recently been tempted to try writing some php scripts to act as
an SQLite server, just to learn what's involved.  I guess I could use LC
server instead of php too.

Pete
lcSQL Software <http://www.lcsql.com>
Home of lcStackBrowser <http://www.lcsql.com/lcstackbrowser.html> and
SQLiteAdmin <http://www.lcsql.com/sqliteadmin.html>

On Wed, Nov 25, 2015 at 4:46 PM, Lynn Fredricks <
lfredricks at proactive-intl.com> wrote:

> > 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
>
>
>
> _______________________________________________
> use-livecode mailing list
> use-livecode at lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your
> subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode
>