SQLite

Indeed, SQLite is merely a Tcl extension that "escaped" into the wild. SQLite no longer depends on Tcl to run, but it does still depends on Tcl for its make process and for testing. The SQLite development team also depends on tools written in Tcl/Tk for tasks such as editing and collaboration.

Where Tcl to die, SQLite would die with it. Fortunately, both outcomes seem unlikely.

Some of us are old enough to remember the frequent prognostications that "unix is dead" and was being replaced by more modern OSes like VMS or OS/2. How'd that work out?

I wrote lemon in the late 1980's on a Sun4, while a graduate student. There was also a program called "lime" that generated an LL(1) parser, but I've long since lost that code.

Lemon was intended as a yacc-replacement. The advantages of lemon over yacc are that lemon has a less error-prone syntax (it uses symbolic names rather than $1, $2, etc), and that lemon generates a reentrant and thread-safe parser. (At the time, yacc/bison parsers were neither reentrant nor thread-safe. I don't know if that has been fixed in the intervening decades.)

Lemon has always been open source. But it languished with little attention for 10 years until I used it to generate the parser for SQLite. Then suddenly people started to notice and use it.

Lemon does not have a separate version control system. The source code to Lemon (a single file of C plus a template file for the generated parser) are part of the SQLite source tree.

(1) Consistent, repeatable results across all platforms - the library will not fail or degrade because of a platforms dodgy printf() implementation.

(2) SQLite's printf includes extensions like %q and %Q that help prevent SQL injection attacks. These are used internally. (grep for '%q' and '%Q' in the SQLite sources and see for yourself.)

(3) Nothing like sqlite3_mprintf() is available from standard libraries. And yet it is a very useful and important interface.

(4) SQLite has a printf() SQL function for formatting text. I don't know of any way to build that using a standard-library printf() implementation - you need to have direct access to the printf() source code to make it work from SQL.

(5) Library printf() implementations are often subject to LOCALE, which means that if "3.14159" is render as "3,14159" (as it is for some LOCALEs) it will break SQL syntax rules when used to construct an SQL statement.

I can probably think of other reasons, but those are the ones that come quickly to mind.

OT but important: We do not normally accept patches at SQLite because (1) that would endanger the "public domain" status of the project unless the patch is accompanied by a lot of paperwork and the sender resides in a British Common Law country and (2) we don't put untested changes into the SQLite source tree and (3) no matter how hard you try, your patch is unlikely to met our strict style guidelines. If you do send a patch, we may use it as a reference to try to figure out what the problem is, but we won't actually apply the patch.

It is far, far better to send us a test case. The smaller the test case, the better, but any test case will do. We are much freer to accept test cases without copyright complications, and test cases have to be written anyhow before any changes are checked in. So just send in a test case and let us find and fix the bug for you. If it is a crash bug, it is usually fixed quickly - within hours.

lcamtuf always sent us succinct test cases. Never patches.

SQLite is a TCL extension that escaped into the wild....

I think many people have trouble with TCL because it looks a lot like C and so they expect it to behave roughly like C. But TCL is a fundamentally different language. It is better to think of TCL as LISP with C-like syntax. Once you grok this difference, TCL becomes a very elegant language.

The JSON support in SQLite is in a very early stage of design and development still. There is a lot of cool stuff coming. But there is also still a lot of work to do.

Please check back in a few weeks.

If you would like to leave comments describing the kinds of problems you would like JSON support in SQLite to solve, I promise to read them all and give them careful consideration.

The sqllogictest suite (https://www.sqlite.org/sqllogictest) only has to be run on all the various servers when the tests themselves change, in order to validate the tests. Otherwise, those tests only need to be run on the system under test, since the tests themselves contain the correct answer to check against.

The sqllogictest suite takes about 15 minutes to run three times against SQLite (once with all optimizations turned on, and twice more with various optimizations turned off) on a fast machine.

The minimum set of tests needed to provide 100% branch coverage takes about 3 minutes on a fast machine.

To run all of the tests under various compile-time options and on all supported platforms usually takes two to three days, assuming everything works. A full-up automated test on just Linux x64 takes about 18 hours.

I received a gracious reply from the Hankinsoft developers. They have already added a disclaimer to the website and are promising to make the distinction between their product and SQLite even clearer over the next few days. Hopefully any confusion will be cleared up soon.

It is a violation of the SQLite trademark, I believe. I have sent a gentle email to the "sqlitepro.com" company pointing this out to them and suggesting some simply changes that will avoid the trademark violation. Hopefully they will take prompt action to clear up the confusion caused by their website and product and we can move on from this without further complications.

The cycle-counts returned by cachegrind are repeatable, to 7 or 8 significant figures. That means that I can make a small change and rerun the test and know whether or not the change helped or hurt even if the difference is only 0.01%. I don't think perf is quite so repeatable, is it?

Also, the cg_annotate utility gives me a complete program listing showing me the cycle counts spent on each line of code, which is invaluable in tracking down hotspots in need of work. If perf provides such a tool, I am unaware of it.

Remember that I'm not trying to optimize for a specific CPU. SQLite is cross-platform. I want to do optimizations that help on all CPUs using all compilers. I'm measuring the performance on the "cachegrind virtual CPU" of a binary prepared using GCC and -Os because that combination gives repeatable measurements that are easy to map into specific lines of source code. But the optimizations themselves should usually apply across all CPUs and all compilers and all compiler optimization settings.

Nkruz is, of course, welcomed to use any tool he likes to optimize his projects. But, at least for the moment, I'm finding cachegrind to be a better tool to help with implementing micro-optimizations.

I'd like to improve the document so that reference to the SQLite source code is not required. Can you explain (perhaps via private email) what the file format document did not describe sufficiently for you to implement a reader in Go?

The underlying problem is that POSIX Advisory Locks are broken by design. They are per-process, rather than per-file-descriptor locks.

So, if you have two file descriptors open on the same file in the same process, a lock on one file descriptor is unable to control access to the file from the second file descriptor. POSIX locks only work if the two file descriptors are in separate processes.

There is a large of code in SQLite that works around this bug. And that code works well. But that code requies access to global variables.

The problem that Eric describes comes up when you link in two separate copies of SQLite, and thus have two distinct sets of global variables for managing the locks. These two separate copies of SQLite have no why of knowing about each other, and hence have no way of coordinating their lock behavior in order to avoid problems.

Used to be true. But recent versions of SQLite fix this.

When designing a file format, it is always a good idea to plan for enhancements. There were originally 36 bytes of unused space in the 100-byte header of the SQLite 3.0.0 file format, back in 2004 - bytes set aside specifically to deal with unforeseen needs. Over the years, 12 of those bytes have been allocated to various improvements. 24 bytes remain. (I'd prefer not to use them up all at once or on a whim, obviously.)

There are currently 24 contiguous bytes of unused space in the SQLite header. If need be, and if SQLite catches on for use as a portable image format, I will be willing to allocate some or all of those 24 bytes to an identifier string for file(1).

Not if you set "PRAGMA secure_delete=ON;" See http://www.sqlite.org/pragma.html#pragma_secure_delete for additional information.

We found it is much easier to handle exceptions without risking stack leaks using a 3-address register machine. Code generation is also simplified in a register machine compared to a stack machine (at least in the case of a VM designed specifically to run SQL statement).

The VM used by SQLite is different from the VM used by things like Javascript or Python in that SQL is not a general-purpose programming language. Most of the opcodes in the SQLite VM are heavy-weight concepts such as "insert a key/value pair into a b-tree" or "decode the N-th column of a table row". These opcodes are implemented with hundreds or thousands of lines of C code. The overhead of instruction dispatch is insignificant in comparison. And so there isn't much of a performance advantage one way or the other between a stack machine and a register machine in SQLite. The choice of a register machine is for maintainability, reliability, and testability.