The Untold Story of SQLite (2021)

https://corecursive.com/066-sqlite-with-richard-hipp/

by usernamesdf • 2 years ago

80 25 2 years ago

corecursive.com

myfonj• 2 years ago

* (2021)

IIRC one of the best episodes of the Corecursive podcast I've heard so far.

hprotagonist• 2 years ago

The bit on test coverage is particularly good: https://corecursive.com/066-sqlite-with-richard-hipp/#testin... :

Let’s not dwell on the details, but I had this idea, I’m going to write tests to bring SQLite up to the quality of 100% MCDC, and that took a year of 60 hour weeks. That was hard, hard work. I was putting in 12 hour days every single day. I was just getting so tired of this because with this sort of thing, it’s the old joke of, you get 95% of the functionality with the first 95% of your budget, and the last 5% on the second 95% of your budget. It’s kind of the same thing. It’s pretty easy to get up to 90 or 95% test coverage. Getting that last 5% is really, really hard and it took about a year for me to get there, but once we got to that point, we stopped getting bug reports from Android.

dboreham• 2 years ago

Interesting peek into how the sausage is actually made.

Also notable: the code is open source, but the test suite is not. I've seen this in other projects, including one I worked on (on the commercial side). It's a little sneaky, particularly in the case that there's a "decoy" test suite open sourced that isn't the real one. Not to say I criticize this: we all need to make a living, but nieve consumers of OSS projects should be aware of this.

ensignavenger• 2 years ago

Is it really that sneaky? I am pretty sure the SQLite website is crystal clear on this point.

ahoka• 2 years ago

It's not really open source in the strictest sense as they strangely refuse to provide any license for it and as far as I know they are not open to contributions either.

barumrho• 2 years ago

Did not know that. It's an interesting way to keep ownership of the project! What other projects do this?

woodrowbarlow2• 2 years ago

i wouldn't say the open tests are a decoy; it seems they're used for most developer testing. it seems the TH3 suite is only run when it's time to do a release (because it takes days to complete).

JonChesterfield• 2 years ago

Some tests are public. Others aren't. Seems reasonable to me.

wduquette• 2 years ago

I've heard Richard say several times that SQLite looks like it's coded in C, but it's actually coded in TCL/TH3...because the C code comes and goes but the test suite is eternal.

woodrowbarlow2• 2 years ago

the idea of keeping some of those tests private and attempting to sell the test suite to avionics companies is also interesting. would've been nice if they'd expanded on that a bit.

tazu2• 2 years ago

The most interesting part was him saying it hasn't sold at all!

password4321• 2 years ago

(2021)

2021-07-03 https://news.ycombinator.com/item?id=27718701 97 comments

2023-01-28 https://news.ycombinator.com/item?id=34559075 92 comments

also

2021-07-02 https://news.ycombinator.com/item?id=27712563 5 comments

znpy• 2 years ago

seems it's been told quite a few times then :P

CoastalCoder• 2 years ago

Bit of a tangent, but Adam Bell was recently laid off from his day job, and IIRC hasn't found work yet.

If you see value in Corecursive, it's probably an especially considerate time to support him on Patreon.

adamgordonbell• 2 years ago

That's me.

Yes, I'm looking for a developer relations engineer role - a job where I can continue flexing my software development muscles and use my communication skills.

More info:

https://corecursive.com/building-powershell-with-jeffrey-sno...

isoprophlex2• 2 years ago

Besides giving the world a great piece of tech, Hipp seems to be such a great, levelheaded guy too.

SQLite was my gateway drug to data analysis, because I didn't know any better I just started using that for automated tasks that were done in excel up to that point. It was the basis of my career in data, after I decided to get out of academia.

Anyway.

I've recently built a SQLite extension that bakes Lua into SQLite. You can do

    SELECT lua('return some_business_logic(...)', col1, col2 FROM some_table;

It's very much a V0.01 alpha thing, but it's feasible.

The idea allows running complex business logic directly inside the SQLite process. This all for speed and efficiency as you don't schlep data in and out of your language calling SQLite, and for better testability as it seems the sweet spot in terms of decoupling business logic from the query writing.

I've been at it on my own too much, I can't really reason about it anymore.

So, dear HN, is this idea completely idiotic or am I onto something that the world would benefit from?

thunderbong• 2 years ago

I personally think it's a fantastic idea. Please share it with world. I'm sure the HN crowd will appreciate it. But overall, anything that gets SQLite to be adopted more is a win in my view.

isoprophlex2• 2 years ago

Your comment means a lot to me, thanks! I'll polish up the turd, build some ready-to-go extension people can download from github instead of having to compile their own, and submit a Show HN. Stay tuned!

tazu2• 2 years ago

Definitely release it! I had the same idea for embedding AWK into SQLite, but haven't gotten around to it.

dave78• 2 years ago

SQLite being released in the public domain has to be up there as one of the greatest gifts ever given to the global software development community. I'd love to know what led to the licensing decision. The wide-open rights to use it however you see fit have undoubtedly enabled countless applications to exist that otherwise would have been very, very difficult to create - especially mobile apps.

Whatever money Dr. Hipp makes from SQLite it is surely not enough relative to the value that it has provided to millions of people.

dhbradshaw• 2 years ago

Wonderful article!

I enjoyed this incidental homage to postgres:

"He came up with this huge corpus of SQL statements, and he ran them against every database engine that he could get his hands on. We wanted to make sure everybody got the same answer, and he managed to segfault every single database engine he tried, including SQLite, except for Postgres. Postgres always ran and gave the correct answer. We were never able to find a fault in that. The Postgres people tell me that we just weren’t trying hard enough. It is possible to fault Postgres, but we were very impressed."

udev4096• 2 years ago

That was full of great insights! Especially, the part about freedom and future advice. Thank you for posting it

Richard: I had this crazy idea that I’m going to build a database engine that does not have a server, that talks directly to disk, and ignores the data types, and if you asked any of the experts of the day, they would say, “That’s impossible. That will never work. That’s a stupid idea.” Fortunately, I didn’t know any experts and so I did it anyway, so this sort of thing happens. I think, maybe, just don’t listen to the experts too much and do what makes sense. Solve your problem.

The Untold Story of SQLite (2021)

Related Stories