SQLite

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

Author here:

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.

Correct.

"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.

Performance analysis indicates that SQLite spends very little time doing bytecode decoding and dispatch. Most CPU cycles are consumed in walking B-Trees, doing value comparisons, and decoding records - all of which happens in compiled C code. Bytecode dispatch is using less than 3% of the total CPU time, according to my measurements.

So at least in the case of SQLite, compiling all the way down to machine code might provide a performance boost 3% or less. That's not very much, considering the size, complexity, and portability costs involved.

A key point to keep in mind is that SQLite bytecodes tend to be very high-level (create a B-Tree cursor, position a B-Tree cursor, extract a specific column from a record, etc). You might have had prior experience with bytecode that is lower level (add two numbers, move a value from one memory location to another, jump if the result is non-zero, etc). The ratio of bytecode overhead to actual work done is much higher with low-level bytecode. SQLite also does these kinds of low-level bytecodes, but most of its time is spent inside of high-level bytecodes, where the bytecode overhead is negligible compared to the amount of work being done.

Perhaps the OP means OsQuery: https://github.com/osquery/osquery

OsQuery is an SQLite extension consisting of hundreds of virtual tables that provide SQL access to operating system status and configuration.

About 10% overall (9.96% to be precise), according to server logs over the previous 10 days.

Robots hit dynamic content at about twice the rate as humans: 14.12% versus 7.8%. About 34% of traffic is from robots, from what I can tell (though to be fair, many robots these days work hard to disguise themselves has human, so the actual percentage of robot traffic is likely much higher.)

That is inaccurate. Ginko's statement is closer to truth.

I was inspired to write SQLite while working with Informix on DDG-79 and I saw how useful an embedded database would be in some situations, compared to a client/server solution. So I went off and wrote SQLite on my own, while the development contract was on hiatus. There was never a request for SQLite or anything like it coming from the the navy (or more precisely, Bath Iron Works) as they were both very happy with Informix on the ship and Oracle on land and had zero desire for anything new or different. The development team I worked on ended up using SQLite some for prototyping and testing on that project, but it was never deployed to the ship, as far as I know.

So yes, the whole point of SQLite was to build a database that operated as a library linked into the application, rather than as a separate server, as ginko postulates. Design issues on a single system within DDG-79 (Automated Common Diagrams) were the inspiration for that idea, but to say that SQLite was designed for DDG-79 is not true. There was never a request for SQLite coming from the navy or the ship designers. Indeed, there is was a lot of pushback against SQLite. SQLite was just a crazy idea coming from a rogue developer who happened to be working on one of the many on-board systems at that time.

One example: Fossil (https://fossil-scm.org), the version control system used by SQLite itself. Fossil is Git-like in its underlying design but has a different interface. Fossil stores a complete source code repository as an SQLite database, rather than a pile-of-files as Git does. Storing content this way gives Fossil UI advantages over Git, such as the ability to easily find decedents of a check-in, and the ability to assign the same tag to multiple check-ins (ex: tagging every release with the "release" tag.)

Yeah - I'm very much about maintaining an immutable record. That is why, back in 2006, I started designing Fossil to control SQLite, instead of just switching to Git.

I think that proper version control should be immutable. If history is changeable, what's the point in having history at all? It ceases to be "history" and becomes just a fable or hagiography.

Mistakes happen, and it is important to be able to correct them, which Fossil does do. In many ways Fossil's mistake-correction logic is far better than Git's. If you make a check-in to the wrong branch, you can move it after the fact in Fossil. If you check-in with the wrong user-id, or a with a goofy check-in comment, you can edit those too. Was your system clock wonky when you did the commit, resulting in a bad timestamp on the check-in, that too can be fixed. I say "edit" - really you are not modifying the original check-in at all. The original check-in is immutable. But Fossil supports the ability to add correction records (tags) on top of check-ins. The original history is preserved and you can always drill down to find out exactly what happened. But for routine day-to-day usage, only the corrected values are shown on displays and reports. So it is like being able to change history, except that you are left with an audit trail.

This is how accounting and legal systems work. You never erase - you only add corrections.

The document first appeared 2010-11-11. The latest change was on 2023-05-10. See https://www.fossil-scm.org/home/finfo/www/fossil-v-git.wiki for a complete change history for the document - including changes on branches.

Aside: How would you get this information (the complete change history in a single file of a larger project) if the repository was on GitHub?

Helpful hint: Click two nodes on the graph to see a diff between the two selected versions.

SQLite file format spec: https://www.sqlite.org/fileformat2.html

Complete version history: https://sqlite.org/docsrc/finfo/pages/fileformat2.in

Note that there have been no breaking changes since the file format was designed in 2004. The changes shows in the version history above have all be one of (1) typo fixes, (2) clarifications, or (3) filling in the "reserved for future extensions" bits with descriptions of those extensions as they occurred.

No - because nobody has provided a reproducible test case to show an actual performance regression. If we can't reproduce it, then how are we suppose to fix it?

It does do exactly that. If you visit https://sqlite.org/src/stat, the "Schema Version" line tells you exactly what version of the database schema that the repository is using. The SQLite repository uses the very latest Fossil schema, which you can see from the "stat" page has not changed in 8.5 years.

I dispute this claim. The underlying artifact format for Fossil is compatible back to the beginning. There have been enhancements, but nothing that would break. And there have been no reports of breakage among the countless users on the Fossil Forum.

I suspect what the OP encountered was that he checked in some things using a newer version of Fossil that had enhanced capabilities. (For example, Fossil originally only use SHA1 hashes, but was enhanced to support both SHA1 or SHA3 after the SHAttered attack.) Then the OP tried to extract using an older Fossil that didn't understand the new feature and returned an error. I'm guessing at this, of course, but that seems like the most likely scenario.

I have never once made a backup of SQLite repo or the Fossil self-hosting repo, or any of the other 100+ Fossil repositories that I have at hand. I've cloned the repos to other machines as disaster protection. In fact, I have cron jobs running on machines all over the world that "sync" critical Fossil repositories (such as SQLite) once an hour or so. But I have never even once made a pure backup.

I did have the primary SQLite repo go corrupt on my once, years ago. Somehow, file descriptor 2 got closed. Then when the SQLite database that is the repository was opened, it opened on file descriptor 2. Then some bug in Fossil caused an assert() to fire which wrote on file descriptor 2, overwriting part of the database. I restored the repo from a clone, fixed the assertion fault in Fossil, and enhanced SQLite so that it refuses to use a file descriptor less than 3.

See also: https://fossil-scm.org/home/doc/trunk/www/selfcheck.wiki

Use whatever VCS you like, but do please note that Fossil does have a "cherry-pick" command. See https://fossil-scm.org/home/help?cmd=cherry-pick for the documentation.

Earlier in it's history, Fossil didn't have a separate cherry-pick command, but rather just a --cherrypick option to the "merge" command. See https://fossil-scm.org/home/help?cmd=merge. Perhaps that is where you got the idea that Fossil did not cherry-pick.

Fossil has always been able to cherry-pick. Furthermore, Fossil actually keeps track of cherry-picks. Git does not - there is no space in the Git file format to track cherry-picks merges. As a result, Fossil is able to show cherry-picks on the timeline graph. It shows cherry-pick merges as dashed lines, as opposed to solid lines for regular merges. For example the "branch-3.42" branch (https://sqlite.org/src/timeline?r=branch-3.42) consists of nothing but cherry-picks of bug fixes that have been checked into trunk since the 3.42.0 release.

The link is to a debugging and testing version of the document. Notice the "debug" in the URL: "https://sqlite.org/debug/matrix/whynotgit.html"

The correct link is https://sqlite.org/whynotgit.html

That branch has been renamed "bedrock" (after its principal user) and is up-to-date.

The important point to keep in mind is that SQLite will read JSON5, but it never writes it. The JSON that SQLite generates is canonical JSON that is fully compliant with the original JSON spec.

It turns out that there is a lot of "JSON" data in the wild that is not pure and proper JSON, but instead includes some of the extensions of JSON5. The point of this enhancement is to enable SQLite to read and process most of that wild JSON.

This feature was requested by multiple important users of SQLite.

> SQLite is closed to outside contributions.

Incorrect.

Anyone is allowed to contributed to the SQLite code base. There is no religious test, nor even any code-of-conducts requirements for being able to contribute to SQLite. This has always been the case. But the barrier to making contributions is high - higher than many other projects. There are two main reasons for this:

(1) Any contributions need to be able to demonstrate, with legal rigor, that they are in the public domain. Otherwise, if copyrighted code were introduced, SQLite itself would cease to be in the public domain. The SQLite project places a lot of emphasis on provenance of the code.

(2) Contributions need to demonstrate that they will be useful to a very wide audience, and that they will not diminish our ability to maintain the code for decades into the future. Most of the effort in a project like SQLite is long-term maintenance. People might be really proud of the work they have done on some patch over a day, or week, or month. But the amount of work needed to generate the patch is nothing compared to the amount of work they are asking the developers to put into testing, documenting, and maintaining that patch for the life of the project (currently projected to be 27 more years).

Many people, and even a few companies, have contributed code to SQLite over the years. I have legal documentation for all such contributions in the firesafe in my office. We are able to track every byte of the SQLite source code back to its original creator. The project has been and continues to be open to outside contributions, as long as those contributions meet high standards of provenance and maintainability.

The server is on Linode. Looking at the stats, they appeared to have had an outage of some kind last night at about the time you got this message. Everything seems to be running fine now. Please try again.

https://sqlite.org/tmp/cpu-20230119.jpg

The CLI does use the relevant APIs, however I don't know of a way to reach this bug using a script input to the CLI. If there is such a path that I don't know about, it seems like it would require at least a 2GB input script.

There is a lot of static content on https://sqlite.org/ but also a lot of SQLite-backed dynamic content. I just checked the logs. Over the past 5 days, 12.03% of non-robot HTTP requests were against dynamically generated pages.

Most of the dynamic content is generated by the version control system, Fossil (https://fossil-scm.org/). For example: https://sqlite.org/src/timeline or https://sqlite.org/forum/forum

Fossil is hosted on the same machine as SQLite. Fossil is self-hosting and the Fossil website is 100% dynamically generated. Every HTTP request against https://fossil-scm.org/ does about 200 SQLite queries (give or take - depending on the page).

Fossil creator here: Fossil was created for one purpose - to support the development of SQLite, a job at which it has been successful beyond all expectation. Any other use of Fossil (and there is a lot of that, though still a lot less than there is for Git) is just gravy.

Fossil was designed to support the SQLite workflow. Git was designed to support the Linux Kernel workflow. Both systems seem amazingly well-suited for the projects for which they were designed.

Should you use Fossil or Git on your project? I suppose that depends a lot on whether your project workflow more closely matches the Linux Kernel or SQLite. There are other considerations, but I think that is the main differentiator of the two systems.

Side note: I've had a lot of help writing Fossil over the past 15 years, and especially a lot of help on the documentation. The article being discussed by this thread originated with me, but has been extensively edited by others with different ideas about the advantages and disadvantages of Fossil vs. Git. See https://fossil-scm.org/home/finfo/www/fossil-v-git.wiki?ubg for a complete timeline of changes to that one file, color-coded by committer. (BTW: Can Git or GitHub generate such a timeline?) My color is brown. If you scroll through the history, you can see that most of the edits to the article are by others. Which is fine. Just don't attribute everything you read there to me.

No. The SQLite Forum is just an instance of Fossil. Fossil is written in C.

RIGHT and FULL JOIN are on the trunk branch of SQLite and will (very likely) appear in the next release. Please grab a copy of the latest pre-release snapshot of SQLite (https://sqlite.org/download.html) and try out the new RIGHT/FULL JOIN support. Report any problems on the forum, or directly to me at drh at sqlite dot org.

You misunderstand. It means that a single application should not use two or more copies of the SQLite library to open the same database file.

As a practical matter, you really have to work hard to get an application to use two different copies of SQLite at the same time - all the while avoiding symbol collisions on link. You can do it, but it takes some work. And then on top of that your application has to decide to open two or more connections to the same database file, using different copies of SQLite in each case.

Edit history: https://sqlite.org/docsrc/finfo?name=pages/btreemodule.in

That last substantive edit to the document was in 2009. There were some spelling corrections in 2010. We finally got around to removing it from the documentation set in 2016.

Some issues with "Git the standard format":

  *  Does not track cherry-picks

  *  Does not track branch names

  *  Tags must be unique.  (You cannot add a "release" tag to every release,
     for example.)

  *  Not easily extensible - witness the pain and years of effort trying
     to move from SHA1 to SHA2.

Documentation here: https://fossil-scm.org/home/doc/trunk/www/serverext.wiki