SQLite

> I keep meaning to dig into Fossil, but I have no faith I could convince a team to use it.

Developer or Fossil and SQLite here: I agree. In my experience, you'd have better luck convincing the team to switch from vi to emacs. For all its many and well-documented faults, the Git/GitHub paradigm is what people want to use because it is what they are familiar with.

All the same, I intend to keep right on using Fossil, thank you very much!

So here is the idea I've been thinking of lately: What if Fossil were ported or enhanced to use Git's low-level file-formats so that unmodified git clients could seamlessly push and pull against the (enhanced) Fossil server. Call the new system "Fit" (Fossil+Git). Using Fit, you could stand up a GitHub replacement for an individual project in 5 minutes using nothing more than a 2-line CGI script. Git fan-boys could continue to use their preferred interface, while others who prefer a more rational and user-friendly design could use the Fossil-like "fit" command. Everybody could share code, and everybody would have a nice web-based interface with which to collaborate with tickets and wiki and all the other cool (and to my mind essential) stuff that Fossil provides. And nobody who already knows git would be forced to learn a new command-line interface.

I'd be all over writing the code for "Fit", except that I'm already over-extended. Anybody who thinks this is a good idea and would like to pitch in and collaborate, please contact me privately. Thanks.

I don't know who posted this story, but it is timely. Earlier today I was working on a new related article (https://www.sqlite.org/draft/fasterthanfs.html) claiming that it is faster to read blobs out of SQLite than it is to read them out of separate files on disk. Comments welcomed.

When adding an attachment in Fossil, if the attachment is syntactically similar to a structural artifact (such as a manifest), then the attachment is compressed prior to being hashed and stored, thus making it very dissimilar to a manifest and incapable of being confused with a manifest. Hence, it is not possible for someone to add a new manifest as an attachment and have that confuse the system. Furthermore, there is an audit trail so that should an attacker discover and exploit some bug in the previous mechanism and manage to get a manifest inserted via an attachment, then the rogue manifest can be easily identified and "shunned".

Users with commit privileges are granted more trust and do have the ability to forge manifests. But as before, there is an audit trail and rogue manifests (and the users that insert them) can be detected and dealt with after the fact.

Structural artifacts have a very specific and pedantic format. You can forge a structural artifact, but you will never generate one by accident during normal software development activities.

I intend to support SQLite for 33 more years. I take that commitment seriously and plan accordingly.

But even if my plans do not work out, the SQLite file format is fully documented and relatively simple. See https://www.sqlite.org/fileformat2.html for details. Writing a script to convert an SQLite database into a series of CSV files shouldn't take a competent hacker more than a few of hours and a novice more than a few days.

Reasonable estimates are that there are more copies of SQLite in use today than any library other than zLib. I dare say that SQLite will live as long or longer than gzip, and should both disappear, I suspect you will have a much easier time recovering content from an SQLite database than you would from a gzip file.

Reassuring news. Thanks.

Go has chosen to omit assert(), because assert() is frequently misused they say. Antibiotics are also frequently misused, but that is not a good reason to prohibit them. The omission of assert() makes Go a non-starter.

Rust seems more promising, but it is still not to the point where I am interested in rewriting SQLite in Rust, though I may revisit this decision in future years.

Some current reasons to continue to prefer C over Rust:

(1) Rust is new and shiny and evolving. For a long-term project like SQLite, we want old and boring and static.

(2) As far as I know, there is still just a single reference implementation of rustc. I'd like to see two or more independent implementations.

(3) Rust's ever-tightening interdependence with Cargo and Git is disappointing.

(4) While improving, Rust still needs better tooling for things like coverage analysis.

(5) Rust has "immutable variables". Seriously? How can an object be both variable and immutable? I realize this is just an unfortunate choice of terminology and not a fundamental flaw in the language, but I believe details like this need to be worked out before Rust is considered "mature".

Short answer: Write Amplification.

Long answer: When the index is created first, new entries have to be inserted into the index (in sorted order) as they arrive. This involves writing various individual records at arbitrary places in the b-tree. Each such write is perhaps 20 bytes in size (depending on your index, of course). But, behind the scenes, your OS is really reading an entire 4K page from your HDD/SSD, swapping out the 20 bytes that you sent to write() and the pushing the whole 4K page back to the HDD/SSD. So your 20-byte random write turned into a 4K byte write. That is "write applification". But if you insert all the records into the table first (inserting into an unindexed table is just an append) then create the index afterwards, SQLite is free to sort the columns to be indexed (using an efficient external merge sort algorithm) then write them to disk in sorted order. That way, each new 20-byte records is appended, rather than written randomly into the file. That means multiple appends can be grouped together to form a full 4K page which is then written exactly once. Much less I/O is required.

> What happens if SQLite changes the way a feature behaves, even for good reason? The developers aren't beholden to browser devs, so this is within their right.

Actually, we are beholden to application developers of all types. It's part of the SQLite social contract. SQLite does not change in incompatible ways. New features are added, performance is enhanced, but we work hard to not break any of the millions and millions of diverse applications that use SQLite.

> I never could get anonymous push/user registration to work smoothly....

We are always trying to make Fossil better. Can you explain what you mean by "anonymous push/user registeration" and provide more information on why it was a problem for you? Private email to drh@sqlite.org is ok for this.

Original author of Fossil here, with two comments:

(1) Fossil was created for the single purpose of supporting SQLite development, a mission at which it has succeeded spectacularly. Fossil is therefore a success story, irregardless of its mind-share relative to Git. That thousands of other developers also find Fossil useful on their own project is just gravy. I am not concerned that Fossil has not (yet) become the "one true and great DVCS". My intent is to continue personally supporting and maintaining Fossil for at least three more decades.

(2) Git people: Please steal ideas and code from Fossil. This is not about winning and losing; it is about providing the best possible tools. Fossil has a number features which are missing from Git but which could be easily added to Git and would enhance the Git-user experience. I'm not talking about the philosophical differences between Fossil and Git (which I recognize that Git is unlikely to ever change) but rather specific UI features such as "fossil all" or "fossil ui" or "fossil undo" or the ability to view the ancestors of check-ins. Fossil is 2-clause BSD, so you do not even have to acknowledge where you stole the code from. Do it for your users, please.

No you don't. CHECK constraints always work.

Maybe you are thinking about foreign key constraints, which for backwards compatibility are off by default, unless you use a compile-time option to make then on by default.

Perhaps the take-away is that when the SQL engine is in-process and queries do not involve a server round-trip, the "n+1 query problem" is not really a problem.

An example is http://sqlite.org/src/timeline

A complete log of the 200+ SQL statements used in generating the page above can be seen at http://sqlite.org/tmp/timeline-sql-log.html

The list of check-ins is computed by a single query. But that query then tosses the list over the wall to another subsystem which generates content for each check-in. And several queries are required for each check-in to extract the relevant information needed for display.

The timeline example above is an information-rich page. Perhaps it could be generated using fewer than 200 SQL statements. But SQL against an SQLite database is so cheap that it has never really been a factor. You can see at the bottom of the page that it was generated in about 25 milliseconds. Profiling indicates that very few of those 25 milliseconds were spent inside the database engine.

We are more open to patches on Fossil because (1) Fossil has a BSD license, which is easier to accept patches for than public-domain and (2) Fossil is seen (rightly or wrongly) as less "mission critical" than SQLite.

And, you are right - in many cases people will send a drive-by patch but we will rewrite it rather than apply it directly: to make sure we understand it for long-term maintenance, to clean it up, and to avoid licensing problems.

Patches are difficult to accept on a public-domain project. That is one real advantage of GPL over BSD-style licenses or public-domain - it is easier to accept patches.

I've had a lot of help from Dan and Joe and Shane and others on SQLite. Stats here: https://www.sqlite.org/src/reports?type=ci&view=byuser

"gcc -S sqlite3.c" will give you an assembly language database engine. :-)

OK, maybe you meant "hand-written" assembly language. But on the other hand, SQLite claims to be written in C, yet a fair amount of that C code is automatically generated using other scripts and programs. Does that mean SQLite is not really written in C?

FWIW, we actually use assembly language sqlite3.s file (as generated above) during testing. We have scripts that go through and punch out individual opcodes, then assemble the result and verify that the test suite detects the error. This is a test of the SQLite test suite more than a test of SQLite, but a strong test suite makes for a strong product, so it still helps.

I don't speak Finnish. But I did once hear David Axmark pronounce My's name, and to my ear it sounded like "Mih" - an "M" followed by a short vowel similar to the vowel in "bit". In other words, My's name is the same as Mitt Romney's first name, just without the "t" at the end.

TH1 (literally "Test Harness #1") was originally conceived as a minimalist reimplementation of TCL sufficient to run the TCL-based SQLite test suite on SymbianOS. That use case never materialized. But later, when I needed a small and lightweight scripting language for Fossil, TH1 was drafted for that alternative purpose.

Initially, WAL mode was off by default because older versions of SQLite do not support it, and it is a property of the database file. Thus, a database created in WAL mode would be unreadable by older SQLites. But WAL has been available for 6 years now, so it might be reasonable to make it the default. We will take your suggestion under consideration. Thanks.

There is a testing checklist (https://www.sqlite.org/checklists/3130000/index is an example). Each item on the checklist is usually just a single "button push" (really a shell command). But we have to push that same button on lots of different platforms.

Clarification: You can have multiple connections (aka file descriptors) open on the same database file for writing at the same time. But only one can be actively writing at a time. SQLite uses file locking to serialize writes. Multiple writers can exist concurrently, they merely have to take turns writing.

The designer of Fossil and SQLite here...

Git was designed to support Linux development. Fossil was designed to support SQLite development. Those projects have very different needs. The details are the subject of a 1-hour talk and are more than can be compressed into a HN comment. Sufficient it to say that (I believe) there is room in the world for more than one VCS.

Earlier today, a support customer needed to know all SQLite check-ins between versions 3.8.7 and 3.8.8.1 that touched any of the files src/pager.c, src/os_unix.c, or src/wal.c. The answer is on this link: https://www.sqlite.org/src/timeline?from=version-3.8.7&to=ve...

Is a query like that even possible with Git or GitHub?

In fairness, I had to enhance Fossil slightly in order to support the query above. But the enhancement was minor and only took a few minutes. See the diff at https://www.fossil-scm.org/fossil/info/b2b62b8318700f9f

How about allowing pointers to be compared against NULL after they have been passed to free(): "free(x); if( x!=NULL ){...}" That was completely harmless (and a useful idiom) for 45 years, and now suddenly it is flagged as UB and has to be changed. Why?

Wouldn't it be great if "memset(0,p,0)" was a harmless no-op? It was for time out of mind. But no more.

For bonus points: Can we have a #pragma that tells the compiler to abort with an error if the target machine uses any representation for signed integers other than twos-complement?

I was not aware of kcov or its basis bcov. Thanks for pointing those out.

However, a quick glance at the bcov source code leads me to believe that it only does source-line coverage, not branch coverage. So, unless my quick reading of bcov sources is mistaken, I couldn't test a Rust SQLite as well as I can test the existing SQLite because kcov/bcov is missing the ability to measure coverage of individual machine-code branch instructions, and the value I get from static analysis is much less than the value I get from branch-coverage testing tools.

That's not being pedantic, btw. The difference between source-line coverage and machine-code branch coverage is huge. The latter really is necessary.

Yes, I am.

Using "gcc -Os -m32 -c sqlite3.c; size sqlite3.o" I get 443,264 bytes using the latest SQLite source on Ubuntu.

The "size" command gives a more accurate measurement of what actually ends up in a compiled and stripped binary. "ls -l" includes symbolic and linking info that gets stripped from the finished binary.

Isn't that why we have aircraft carriers?

> Cannot perform a "fossil diff" for just a directory and its descendants

Thanks for the suggestion. I just checked in an enhancements so that this works now. https://www.fossil-scm.org/fossil/info/c46f98055cf49687

As for your other short-comings, please mention them on the fossil-users@lists.fossil-scm.org mailing list

That won't work because the SHA1 hashes won't match up.

If you accidentally check-in proprietary or sensitive content that you didn't mean to publish, you can shun that content. Shunning leaves a hole in your history. There is no way to replace that hole with different content.