> Why not just have the value be the base 2 logarithm of the page size, i.e. a value between 9 and 16?
Yes, that would have been a better choice. Originally, the file format only supported page sizes between 512 and 32768, though, and so it just seemed natural to stuff the actual number into a 2-byte integer. The 65536 page size capability was added years later (at the request of a client) and so I had to implement the 65536 page size in a backwards compatible way. The design is not ideal for human readability, but there are no performance issues nor unreasonable code complications.
The page size value is not the only oddity. There other details in the file format that could have been done better. But with trillions of databases in circulation, it seems best to leave these minor quirks as they are rather than to try to create a new, more perfect, but also incompatible format.
> Dr. Hipp has said several times that nobody expected a weakly-typed database to achieve the pervasiveness that is observed with SQLite.
I don't remember ever saying that. Rather, see https://sqlite.org/flextypegood.html for detailed explanation of why I think flexible typing ("weak typing" is a purgative and inaccurate label) is a useful and innovative feature, not a limitation or a bug. I am surprised at how successful SQLite has become, but if anything, the flexible typing system is a partial explanation for that success, not a cause of puzzlement.
Did I misinterpret the experts' assertion of imposibility?
"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."
> Did I misinterpret the experts' assertion of imposibility?
Misstated, I'd say. You said "nobody" but the actual quote is about the assumed conventional wisdom of the time, which is quite different. And while this was probably inadvertent, you phrased it in a way that almost made it sound like that was Dr. Hipp's original opinion, which, of course, is the opposite of true.
> sqlite in its default (journal_mode = DELETE) is not durable.
Not true. In its default configuration, SQLite is durable.
If you switch to WAL mode, the default behavior is that transactions are durable across application crashes (or SIGKILL or similar) but are not necessarily durable across OS crashes or power failures. Transactions are atomic across OS crashes and power failures. But if you commit a transaction in WAL mode and take a power loss shortly thereafter, the transaction might be rolled back after power is restored.
This behavior is what most applications want. You'll never get a corrupt database, even on a power loss or similar. You might lose a transaction that happened within the past second or so. So if you cat trips over the power cord a few milliseconds after you set a bookmark in Chrome, that bookmark might not be there after you reboot. Most people don't care. Most people would rather have the extra day-to-day performance and reduced SSD wear. But if you have some application where preserving the last moment of work is vital, then SQLite provides that option, at run-time, or at compile-time.
When WAL mode was originally introduced, it was guaranteed durable by default, just like DELETE mode. But people complained that they would rather have increased performance and didn't really care if a recent transaction rolled back after a power-loss, just as long as the database didn't go corrupt. So we changed the default. I'm sorry if that choice offends you. You can easily restore the original behavior at compile-time if you prefer.
>If you switch to WAL mode, the default behavior is that transactions are durable across application crashes (or SIGKILL or similar) but are not necessarily durable across OS crashes or power failures. Transactions are atomic across OS crashes and power failures. But if you commit a transaction in WAL mode and take a power loss shortly thereafter, the transaction might be rolled back after power is restored.
How is this behavior reconciled with the documentation cited in my comment above? Are the docs just out of date?
> Not true. In its default configuration, SQLite is durable.
Could you explain why this is?
I have quoted above the documentation that suggests it's not durable (summarising: DEELTE is the default, and DELETE appears only durable in EXTRA mode which is not a default).
I just checked in an experimental change to sqlite3_rsync that allows it to work on non-WAL-mode database files, as long as you do not use the --wal-only command-line option. The downside of this is that the origin database will block all writers while the sync is going on, and the replicate database will block both reads and writers during the sync, because to do otherwise requires WAL-mode. Nevertheless, being able to sync DELETE-mode databases might well be useful, as you observe.
SQLite supports indexes on expressions. So if you have an expression that accesses some subcomponent of JSON that you want to index, just create an index on that expression.
Seeing this pop up on HN prompted me to review the 13-year-old document, and bring it up to date with the latest enhancements to SQLite. Specifically, three ways for safely backing up a live SQLite database are now listed in section 1.2. See the latest draft at https://sqlite.org/draft/howtocorrupt.html
My title was imprecise and unclear. I didn't mean that you should raise errors if CRLF is used as a line terminator in (for example) HTTP, only that a bare NL should be allowed as an acceptable line terminator. RFC2616 recommends as much (section 19.3 paragraph 3) but doesn't require it. The text of my proposal does say that CRLF should continue to be accepted, for backwards compatibility, just not required and not generated by default. I failed to make that point clear.
My initial experiments suggested that this idea would work fine and that few people would even notice. Initially, it appeared that when systems only generate NL instead of CRLF, everything would just keep working seamlessly and without problems. But, alas, there are more systems in circulation that are unable to deal with bare NLs than I knew. And I didn't sell my idea very well. So there was breakage and push-back.
I have revised the document accordingly and reverted the various systems that I control to generate CRLFs again. The revolution is over. Our grandchildren will have to continue dealing with CRLFs, it seems. Bummer.
Thanks to everyone who participated in my experiment. I'm sorry it didn't work out.
Copying my comment from lobste.rs in case you didn't see it:
I really appreciate this attitude. As programmers, we love to complain and grumble to each other about how the state of things suck, or that things are over complicated, but then too often the response is the software engineering equivalent of “I paid my student loans, so you should have to, too”. A new person joins the project, and WTFs at something, and the traumatized veterans say, “haha oh boy welcome, yeah everything sucks! You’ll get used to it soon.”
I hate that attitude.
We are at the very, very beginning of software protocols that could potentially last for millennia. From that perspective, you would look back at this situation and think of Richard’s blog post as super obvious, the clear voice of reason, and the reaction of everyone here as myopic.
Even if our software protocols for whatever reason don’t last that long, we need to be working on reducing global system complexity. Beauty and elegance aside, there is such a thing as complexity budget which is limited by the laws of information theory, the computer science equivalent of the laws of physics. People like Richard understand this intuitively, and actively work towards reconstructing our world to regain complexity currency so that it can be spent on more productive things.
You remind me to the common wisdom regarding user feedback. That when your users complain about something, you should listen - not to their advice per se, but to their gripes. Because while their gripes may be legitimate, their advice is near guaranteed to be rubbish (they're not developers after all).
Specifically, I'm referring to your new guy example here. The new guy usually very correctly identifies that things suck, what he lacks is perspective. This means that both his priorities will be off, as well as his approaches. Trust the gripe, not the advice.
This is also I think what people in this thread are/were generally about here. Not because Richard would be some new unknown kid on the block mind you, but because our grandchildren having to deal with CRLF is approximately as harrowing as the eventual heat death of the universe, and because instead of standards revisions, he was calling for standards violations.
The problem with trying to legislate a specific case of Postel's Law is that everyone who might get on board because of it, is already on board because of it, and vice versa.
It also goes a bit more than that, for optimal UTF8 search you want the ASCII separators. It's always easier to search for a single byte than two or more bytes of special pattern.
That said, I do agree we should abolish CRLF. And replace it with LF.
"Secure delete" is sufficient to prevent forensic traces from persisting in the database file itself. So you could, for example, send the database file over a TCP/IP link after a secure delete and no deleted data would be transmitted.
"Secure delete" is sufficient to remove traces from the database file itself, but
it is not sufficient to remove deleted data from the underlying SSD. That was known from the beginning and should be mentioned in the documentation; if it is not then the documentation needs to be enhanced.
Just to clear up the error in the parent post: SQLite has native blobs, floats, and integers, not just strings. It doesn't have a bunch of other types for things like dates and JSON - you just represent those things using the native times of integer, float, string or blob. But it is not limited to only strings. This has been true for 20 years.
But it has functions for operating on dates and times though, right? date(), strftime() etc. Those just silently fail with a NULL if the value in the tuple isn't something that it recognizes.
So it seems like it _tries_ to do the things that people want, but only half-heartedly, and just fails silently if it can't. Was the column actually NULL? Or just a wrong string format? Who knows?
The point is, SQLite is very "flimsy". And that's perfectly fine for what it is (or what it intends to be). But if I try to insert or retrieve a date() or do strftime() on a string that isn't a valid date/time, don't just return a NULL. That's where the "flimsiness" makes it a deal-breaker for anything where integrity is important and the database itself is supposed to be the authority.
Yes, that would have been a better choice. Originally, the file format only supported page sizes between 512 and 32768, though, and so it just seemed natural to stuff the actual number into a 2-byte integer. The 65536 page size capability was added years later (at the request of a client) and so I had to implement the 65536 page size in a backwards compatible way. The design is not ideal for human readability, but there are no performance issues nor unreasonable code complications.
The page size value is not the only oddity. There other details in the file format that could have been done better. But with trillions of databases in circulation, it seems best to leave these minor quirks as they are rather than to try to create a new, more perfect, but also incompatible format.