[cctalk] Re: Little Databases
On Aug 19, 2023, at 7:33 PM, Sellam Abraham via cctalk wrote: > > On Fri, Aug 18, 2023, 8:31 PM Gavin Scott via cctalk > wrote: > >> It [SQLite] has billions of installations and on the order of a trillion >> databases in use. >> > > Surely you hyperbole. This is not hyperbole, there are multiple billions of devices that have deployed SQLite—over a billion Apple devices alone. — Chris
[cctalk] Re: Little Databases
--- Original Message --- On Saturday, August 19th, 2023 at 19:49, Dennis Boone via cctalk wrote: > > 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. 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.
[cctalk] Re: Little Databases
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 wrote: > > On Sat, Aug 19, 2023 at 9:34 PM Sellam Abraham via cctalk > 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.
[cctalk] Re: Little Databases
On Sat, Aug 19, 2023 at 9:34 PM Sellam Abraham via cctalk 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.
[cctalk] Re: Little Databases
On Sat, Aug 19, 2023, 7:49 PM Dennis Boone via cctalk wrote: > > 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. > Oh. OK. Sellam >
[cctalk] Re: Little Databases
> 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. De
[cctalk] Re: Little Databases
On Sat, Aug 19, 2023, 7:33 PM Sellam Abraham via cctalk < cctalk@classiccmp.org> wrote: > On Fri, Aug 18, 2023, 8:31 PM Gavin Scott via cctalk < > cctalk@classiccmp.org> > wrote: > > > It [SQLite] has billions of installations and on the order of a trillion > > databases in use. > > > > Surely you hyperbole. > > Sellam > They are serious, and don't call them Shirley. >
[cctalk] Re: Little Databases
On Fri, Aug 18, 2023, 8:31 PM Gavin Scott via cctalk wrote: > It [SQLite] has billions of installations and on the order of a trillion > databases in use. > Surely you hyperbole. Sellam
[cctalk] Re: Little Databases
On Fri, Aug 18, 2023 at 9:21 PM Grant Taylor via cctalk wrote: > > On 8/18/23 9:06 PM, Adam Thornton wrote: > > 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. 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.
[cctalk] Re: Little Databases
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. . . .
