12 ms·
Work on SQLite4 has concluded
- shalabhc 9y agoSQLite is great. For an unusual application see actordb.com - a server side database that uses a large number of independent SQLite databases.
- bane 9y agoWoah, that's awesome! Any performance information anywhere?
- Afton 9y agoWould love to hear some of the lessons learned...
- k__ 9y agoTheir main idea was that B-Trees are slow and LSMs are fast. This was a partially right assumption, but only for writes. If you write something in a DB you check some constraints and those checks are reads. So most DB writes come with a bunch of reads. The reads were slower with the LSMs, so the B-Trees performed better in "real world" writes (which come with reads) and LSMs only performed better in "artificial" writes (without reads).
- jpetso 9y agoI found these slides: https://www.slideshare.net/InsightTechnology/dbtstky2017-c23-sqlite https://www.slideshare.net/InsightTechnology/dbtstky2017-c23...
- Scaevolus 9y agoFor context, SQLite4 explored reimplementing SQLite using a key-value store on log-structured merge trees, like RocksDB and Cassandra. I'd be interested to hear why they stopped. Presumably reimplementing SQL on a KV store was seen as not worth it, when applications that are satisfied with an embedded KV store backend (which is much faster and simpler to write!) already have many options.
- deleted 9y ago[deleted]
- tyingq 9y agoWould have been a neat way to experiment around with putting a SQL front end on various KV interfaces. Redis or etcd, for example.
- nerfhammer 9y agoFairly straightforward to make a mysql storage engine for it, e.g. https://github.com/AALEKH/ReEngine https://github.com/AALEKH/ReEngine
- michaelmior 9y agoCockroachDB has a good blog post[0] that describes how they implemented SQL. (CockroachDB is a key-value store.) [0] https://www.cockroachlabs.com/blog/sql-in-cockroachdb-mapping-table-data-to-key-value-storage/ https://www.cockroachlabs.com/blog/sql-in-cockroachdb-mappin...
- zaarn 9y ago>(CockroachDB is a key-value store.) Correction: CockroachDB is based on a KV store, it's a full SQL RDBMS on top of this KV store.
- michaelmior 9y agoYes, that note was misleading. Thanks for clarifying :)
- electrum 9y agoPresto has connectors for various types of non-SQL databases including Redis: https://prestodb.io/docs/current/connector/redis.html https://prestodb.io/docs/current/connector/redis.html Presto is a distributed SQL query engine for big data, so basically the complete opposite of SQLite, though it often gets used in federation scenarios, as does SQLite. An interesting anecdote is that the team working on what would become osquery (https://osquery.io/ https://osquery.io/) asked if they could reuse the SQL parser from Presto. We get that question a lot, and after explaining that the parser is the easy part (semantic analysis and execution is the real work), I determined that what they really wanted were SQLite virtual tables: https://sqlite.org/vtab.html https://sqlite.org/vtab.html (and those worked out great for them)
- gregmac 9y agoThe relevant change: > This directory contains source code to an experimental "version 4" of SQLite that was being developed between 2012 and 2014. > All development work on SQLite4 has ended. The experiment has concluded. > Lessons learned from SQLite4 have been folded into SQLite3 which continues to be actively maintained and developed. This repository exists as an historical record. There are no plans at this time to resume development of SQLite4. https://sqlite.org/src4/artifact/56683d66cbd41c2e https://sqlite.org/src4/artifact/56683d66cbd41c2e
- cyberferret 9y agoDoubt still exists... Does this mean 'concluded' as in "We've finished polishing the pre production code and are close to releasing it" or 'concluded' as in "We have thrown our hands up in the air and won't be working on this thing any more to bring it to production" ??!!?? EDIT Seeing as I am getting slammed by downvotes, my comment here was simply pointing out that the headline I saw on HN could be read in multiple ways. As a long time user of SQLite3, I was initially excited when I read the title as I had thought it meant something good coming from the SQLite team. Turns out not to be. That, to me, still entails doubt.
- exikyut 9y agoHN, stop being so monumentally stupid. This is a genuine question, and one that I had too. (To clarify, this is directed at all the downvoters, not the commentator I'm replying to.)
- atombender 9y agoI didn't downvote, but I don't think it's too much to ask for commenters to read the linked web site carefully before they jump in with a comment. The answer is literally in the link.
- xelxebar 9y agoIt's not too much to ask. A person overlooked a thing once. Heck, I overlooked it too because on my phone the green text was so incredibly small I coudln't read it. I don' think it's too much to ask for commenters to have a little charity.
- manigandham 9y agoThey are just random internet points and this thread adds nothing to the discussion.
- favorited 9y agoFrom the commit: > This repository exists as an historical record. There are no plans at this time to resume development of SQLite4.
- assface 9y agoRichard Hipp has said that they have signed contracts to support SQLite3 for 35 years. SQLite4 is never going to happen.
- krylon 9y agoLook at it this way: SQLite3 is (more or less) slowly becoming SQLite4, except for the parts that did not work out. It is not as shiny, but in the long run, you still get all the goodness. Nevermind the name / version number.
- jandrese 9y agoIt's basically the Perl 5 of the DB world.
- jzawodn 9y agoPerl 6 you mean?
- tripa 9y agoI read that as: it gives the impression it's been here forever, it's still being actively maintained, and will be for the foreseeable future. Benefiting from the “future” (sqlite4/perl6) at a slow but steady peace. So, really like Perl 5.
- mst 9y agoperl5 has been stealing a bunch of stuff from perl6 and is still actively maintained and doing a major release with new features annually - also continues to Just Work with an extreme commitment to backcompat. So I'm pretty sure he did mean perl5, and as a happy user of both perl5 and sqlite the comparison seems apt.
- rurban 9y agoNot at all. perl5 is horrible tech, with no development and being actively destroyed by its maintainers. Whilst SQLite3 is at the very top of its class, with lots of new features, and very well maintained.
- hoodoof 9y agoThe biggest thorn I found working with sqlite was the lack of ability to modify columns with ALTER TABLE which was a real pain. Doesn't look like this is fixed in sqlite4 though...
- kamac 9y agoSame here. Had to switch to dockerized mariadb for local tests, because migrations wouldn't work.
- jey 9y agoWouldn't you want your tests to be run against the same DB family (and version) as production anyway?
- kamac 9y agoI suppose it was a long time coming. SQLite was very convenient for me while the product was in a very early development stage, with everything changing rapidly.
- mst 9y agoDepending on the situation, it can be well worth it to have your test suite run against SQLite while doing active development to be able to iterate faster and then run it again against the target database before pushing the branch. Where possible I much prefer to spin up a version of my target database in a tempdir but "faster test cycles" is sometimes worth accepting the trade-offs.
- contingencies 9y agoWhy not work around the expectation and simply migrate offline? (eg. dump DB, hack dumpfile/stream, load new CSV?) While you may lose instantaneous constraint validation, it would almost certainly be faster and allow you to work with known and well tested tools. Conforms to the Unix design philosophy: "Store data in flat text files." / "Write programs to handle text streams, because that is a universal interface." http://github.com/globalcitizen/taoup http://github.com/globalcitizen/taoup Since you were nominally optimizing for migration, a zoom-out perspective may be to note that upgrading SQLite3 versions vs. upgrading major RDBMS versions is trivial/fast, relatively rarely required, also cohabitation of multiple versions works a lot easier, any kind of CI/CD process is going to be orders of magnitude faster and use much less CPU/memory/disk space, which means smaller build artifacts and thus faster transfer/download.
- the_common_man 9y agoAnyone know how sqlite makes money?
- giancarlostoro 9y agoSupport contracts according to another comment here. That's usually how open source projects make money as well.
- richardwhiuk 9y agoMost established providers resist putting any code into production without a support contact for it.
- wongarsu 9y agoAnd sqlite's 100% test coverage makes it really attractive for the kind of customers who have no problem paying for long-term support.
- jbarham 9y agohttp://www.hwaci.com/sw/sqlite/prosupport.html http://www.hwaci.com/sw/sqlite/prosupport.html
- ktta 9y ago>SQLite License. Warranty of title and perpetual right-to-use for the SQLite source code. <from more info> Obtaining A License To Use SQLite Even though SQLite is in the public domain and does not require a license, some users want to obtain a license anyway. Some reasons for obtaining a license include: Your company desires warranty of title and/or indemnity against claims of copyright infringement. You are using SQLite in a jurisdiction that does not recognize the public domain. You are using SQLite in a jurisdiction that does not recognize the right of an author to dedicate their work to the public domain. You want to hold a tangible legal document as evidence that you have the legal right to use and distribute SQLite. Your legal department tells you that you have to purchase a license. If you feel like you really need to purchase a license for SQLite, Hwaci, the company that employs all the developers of SQLite, will sell you one. All proceeds from the sale of SQLite licenses are used to fund continuing improvement and support of SQLite. </from more info> How is it possible that they can sell licenses to the code that was put into the public domain by other contributors? A contributor must attach the following declaration[1] to contribute. So now their contributions are in public domain. Now in a place where the law doesn't recognize public domain, doesn't the code belong to the original authors? How can an unaffiliated company license it as if they wrote the code? [1]: "The author or authors of this code dedicate any and all copyright interest in this code to the public domain. We make this dedication for the benefit of the public at large and to the detriment of our heirs and successors. We intend this dedication to be an overt act of relinquishment in perpetuity of all present and future rights to this code under copyright law."
- tomphoolery 9y agoinstead of pretending to release a new version, it might be better to just call this fork sqlite-failed.
- coleifer 9y agoThe source tree for sqlite3 now contains an extension named lsm1 that contains both the standalone lsm kv database as well as a virtual table extension which allows you to use it directly from sqlite3. Some info on python integration can be found here: http://charlesleifer.com/blog/using-sqlite4-s-lsm-storage-engine-as-a-stand-alone-nosql-database-with-python/ http://charlesleifer.com/blog/using-sqlite4-s-lsm-storage-en... In peewee 3.0a I've also added built-in support for using the lsm1 virtual table if you're interested.
- NelsonMinar 9y agoThere's an excellent ~80 minute podcast interview with the sqlite author here: https://changelog.com/podcast/201 https://changelog.com/podcast/201
- adekok 9y agoI'm surprised there wasn't more investigation of SQLite and LMDB: https://github.com/LMDB/sqlightning https://github.com/LMDB/sqlightning The performance there shows either little to no performance difference, up to substantial speed increases.
- gtrubetskoy 9y agoI have to say I learned more about databases from just studying SQLite code than any book on the subject. I've bought a bunch of books on DB's, some very expensive ones, but I wish someone pointed me to SQLite source early on. To internalize it better I invented a "project" for myself - http://thredis.org/ http://thredis.org/ which was (and is, but I'm not maintaining it) a Redis/SQLite hybrid. It was fun to hack on. Another invaluable source of DB internals information is PostgreSQL. Both projects have amazingly well written and detailed comments.
- bane 9y agoSQLite is one of those awesome things that's the exact opposite of magic. It's beautiful, jaw dropping, engineering that exercises so many technical muscles. The number of oddball, often critical, places where I've found SQLite being used would defy belief. As far as I can tell, the "expected" place for SQLite to work seems to be almost anything that's not your normal dB driving some web-based CRUD app...all kinds of embedded systems, easy to manipulate in-memory scratch pads for bioinformatics, lots of data analysis tools in mobile communications. It's so good, and so obvious, that I think sometimes it makes other tools that might be simpler fits for many use-cases less likely to be used, like leveldb.
- hasenj 9y ago> your normal dB driving some web-based CRUD app That can totally be handled with SQLite.
- sametmax 9y agoI have several crud apps running sql. With moderate write load and a good concurrency write error handling code it works very well. Good when the product size is not worth a postgres full blown setup.
- raphaelj 9y agoI backup this. I've been using SQLite for some low to moderate load CRUD apps, and this always worked like a charm. SQLite also make backuping, testing and moving apps so much easier.
- sharpercoder 9y agoEvery time I see something about sqlite, I become sad. It reminds me of the failure of the w3 standards comittee to accept it as web standard. They rejected sqlite because no competing implementation existed. Furthermore, "public domain" license of the software was also a hurdle, iirc.
- dude01 9y agoWe literally lost several years for web app advancement because of that. Reading the decision making, it seemed like overly-legalistic engineers, but I'm open to conspiracy theories that this decision enhanced mobile app store adoption.
- smitherfield 9y agoNo, it was Mozilla who killed it[1] over Apple and Google's strong objections. [1] For pretty much complete nonsense NIH and standards-lawyering reasons.
- bambax 9y agoI too am so disappointed SQLite isn't available in modern browsers (esp. since it was, for a time). But can't it be resurrected? Couldn't we set up a petition somewhere to bring it back?
- dspillett 9y agoIt is a bit of a "would you really do that in production?" type hack, but there is a pure JS compile of SQLite3 that you could use: https://github.com/kripken/sql.js/ https://github.com/kripken/sql.js/ Possible problems: * Nearly half an MB of library to add to your project which might be a concern on mobile (~2.1MB uncompressed. * It handles the whole DB in memory rather than trying to use any sort of local storage as a block store, which pumps up memory use (again, mobile user may particularly find this an issue) and to persist data you have to pickle the whole DB as a single array (which could be a significant performance issue if the data changes regularly and is not very small) and reload it upon new visit. * Concurrency between multiple tabs/windows is going to be an issue for the same reason.
- chmaynard 9y agoThe title of this post, a true statement removed from its context, attracts readers like me because its implied meaning has considerable shock value. Of course, Dr. Hipp didn't help matters by naming his experimental fork "SQLite4".
- maxpert 9y agoInteresting and I saw title and thought to my self hmmmm... may be SQLite4 is just around the corner. This is a good case study to show people look sometimes classic works better and NoSQL coined terms and techniques might work in limited scenarios. Still makes me wonder if LSM would have been faster for mobile devices though, I know it might not work well for embedded devices; but with modern mobile devices (1+ GB of RAM) it might have some speed benefits. Shameless plug https://github.com/maxpert/lsm-windows https://github.com/maxpert/lsm-windows (I did port the LSM storage to windows).