19 Aug
2023
19 Aug
'23
2:06 a.m.
Have you considered sqlite3? It's a SQL engine, but the backend is just a
file. So it doesn't support concurrent access by multiple users, but if
that's not a concern, it gives you the ability to do real SQL queries
without the bother of setting up an RDBMS.
Adam
19 Aug
19 Aug
2:21 a.m.
On 8/18/23 9:06 PM, Adam Thornton wrote:
Have you considered sqlite3?
Yes, I have considered SQLite many times over many years and multiple
versions. I'm quite happy with it.
It's a SQL engine, but the backend is just a file.
Yep.
So it doesn't support concurrent access by
multiple users,
I question the veracity of that statement.
I used to think the same thing.
But, I seem to recall reading an authoritative article, likely on
SQLite's site, talking about how it is possible to have multiple
processes but the same and / or different users access the same SQLite
database file (almost) concurrently.
My take away at the time is that SQLite didn't support multiple
/concurrent/ updates. Or that updates were an exclusive activity. But
multiple concurrent reads were fully supported.
There is also the question of what is concurrency. Especially if you
consider that an exclusive lock can be acquired only for an INSERT /
UPDATE / DELETE query and released thereafter. Thereby allowing
multiple processes / users to have almost concurrent access to an SQLite
database, each of which can make updates (I/U/D queries) as long as they
aren't doing it at exactly the same time.
At least that's what I recall. If I'm wrong, please correct me.
but if that's not a concern,
I remember
walking away from what I read thinking that I could use
SQLite between multiple processes / users on the same system with each
user being able to make an update as close as a second after the
previous user did.
So, likely not a problem for any use case I would have.
it gives you the ability to do real SQL queries
without the bother
of setting up an RDBMS.
The last time I looked, SQLite wasn't completely /proper/ SQL when you
got into fancier queries / joins / views / etc. But that it was
exceedingly good if you wanted to do mainstream and maybe one degree
less mainstream things.
SQLite is a really impressive database. The only reason that I'd step
up to a more traditional RDBMS is when I wanted network based
connectivity and / or needed actual concurrent write ability to multiple
tables in the same database. Nothing I do comes close to that.
I sort of suspect that someone could get SQLite to work as the backend
for something like SpamAssassin reading preferences for multiple users
from a singular database. Especially if updates to preferences were
done less often than queries therefor.
Grant. . . .
3:31 a.m.
On Fri, Aug 18, 2023 at 9:21 PM Grant Taylor via cctalk
<cctalk(a)classiccmp.org> wrote:
On 8/18/23 9:06 PM, Adam Thornton wrote:
My understanding is:
Concurrent access is fine, except that as soon as one writer begins
updating things SQLite will take a lock on the whole database so
another writer is going to wait until the first transaction is
committed or rolled back before its own transaction can proceed. This
also means you can't get a locking issue that could roll back your
transaction unexpectedly due to a conflict.
And there is now the BEGIN CONCURRENT option which defers locking
until COMMIT time, so multiple concurrent writers can just go at it,
but it only works well if each writer is changing different data
(records that don't share index pages etc.). At COMMIT time, it checks
to see if any of the database pages that the transaction was based on
(read or written) have changed since the start of the transaction and
if so, the COMMIT fails and you have to roll back and try again which
of course makes the application code more complex.
https://www.sqlite.org/cgi/src/doc/begin-concurrent/doc/begin_concurrent.md
Overall, I think SQLite is one of the best things ever. Not even Open
Source licensed, it's explicitly in the Public Domain. It now
implements the vast majority of the SQL standards and you're unlikely
to find something you need that it can't do. If you don't need
client-server access and don't need a zillion concurrent users, then
it's often the best choice. Just the lack of any installation,
configuration, startup/shutdown or other management requirements makes
it pretty much "implement and forget". It has billions of
installations and on the order of a trillion databases in use.
The O'Reilly SQL Pocket Guide (4th Ed.) covers SQLite, SQL Server,
Oracle, and PostgreSQL making it a handy resource for comparing those
systems.
So it doesn't support concurrent access by
multiple users,
I used to think the same thing.
But, I seem to recall reading an authoritative article, likely on
SQLite's site, talking about how it is possible to have multiple
processes but the same and / or different users access the same SQLite
database file (almost) concurrently. 
2:47 a.m.
On Sat, Aug 19, 2023, 7:33 PM Sellam Abraham via cctalk <
cctalk(a)classiccmp.org> wrote:
On Fri, Aug 18, 2023, 8:31 PM Gavin Scott via cctalk
<
cctalk(a)classiccmp.org
wrote:
They are serious, and don't call them Shirley.
It [SQLite] has billions of installations and on
the order of a trillion
databases in use.
Surely you hyperbole.
Sellam
9:10 p.m.
------- Original Message -------
On Saturday, August 19th, 2023 at 19:49, Dennis Boone via cctalk
<cctalk(a)classiccmp.org> wrote:
SQLite is used extensively in Appel's iOS and some parts of OSX also.
The Doctor [412/724/301/703/415/510]
WWW: https://drwho.virtadpt.net/
Don't be mean. You don't have to be mean.
Surely you
hyperbole.
Since it's used in Android for various things, and in Firefox and
Chromium for various things, he's not in the least.
3:56 a.m.
On Sat, Aug 19, 2023 at 9:34 PM Sellam Abraham via cctalk
<cctalk(a)classiccmp.org> wrote:
Surely you hyperbole.
https://www.sqlite.org/mostdeployed.html
"Since SQLite is used extensively in every smartphone, and there are
more than 4.0 billion (4.0e9) smartphones in active use, each holding
hundreds of SQLite database files, it is seems likely that there are
over one trillion (1e12) SQLite databases in active use."
(Also the current number for estimated smartphones in use is rapidly
approaching 7 billion now.)
"[...]But our best guess is that SQLite is the second mostly widely
deployed software library, after libz."
SQLite really is just an amazing and magical thing.
4:52 a.m.
P.S. This is a nice podcast (with transcript) interview with Richard
Hipp, the SQLite main/original developer:
https://corecursive.com/066-sqlite-with-richard-hipp/
On Sat, Aug 19, 2023 at 10:56 PM Gavin Scott <gavin(a)learn.bio> wrote:
On Sat, Aug 19, 2023 at 9:34 PM Sellam Abraham via cctalk
<cctalk(a)classiccmp.org> wrote:
Surely you hyperbole.
https://www.sqlite.org/mostdeployed.html
"Since SQLite is used extensively in every smartphone, and there are
more than 4.0 billion (4.0e9) smartphones in active use, each holding
hundreds of SQLite database files, it is seems likely that there are
over one trillion (1e12) SQLite databases in active use."
(Also the current number for estimated smartphones in use is rapidly
approaching 7 billion now.)
"[...]But our best guess is that SQLite is the second mostly widely
deployed software library, after libz."
SQLite really is just an amazing and magical thing. 
31 Aug
31 Aug
11:45 p.m.
On Aug 19, 2023, at 7:33 PM, Sellam Abraham via cctalk <cctalk(a)classiccmp.org>
wrote:
On Fri, Aug 18, 2023, 8:31 PM Gavin Scott via cctalk <cctalk(a)classiccmp.org>
wrote:
This is not hyperbole, there are multiple billions of devices that have deployed
SQLite—over a billion Apple devices alone.
— Chris
It [SQLite] has billions of installations and on
the order of a trillion
databases in use.
Surely you hyperbole.
450
days inactive
462
days old
10 comments
8 participants
participants (8)
-
Adam Thornton -
Chris Hanson -
Dennis Boone -
Gavin Scott -
Glen Slick -
Grant Taylor -
Sellam Abraham -
The Doctor