[cctalk] Re: Little Databases

On 8/18/23 9:06 PM, Adam Thornton wrote: Yes, I have considered SQLite many times over many years and multiple versions. I'm quite happy with it. Yep. 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. 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. 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. . . .