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. . . .
