Exploring the Virtual Database Engine inside SQLite

The big change hinted at in the documentation around the 3.5 era is that SQLite switched from a stack based VM to a register based one. Note that the implementation details are not exposed to a developer using SQLite and there is a massive test suite so the change had no visible impact.<p>What isn't mentioned is why SQLite is using a virtual machine in the first place. The reason is that SQLite only calculates the next row of results when you ask - it does not calculate all result rows at once in advance. Each time you ask for the next row of results it has to resume from where it last left off and calculate that next row. The virtual machine is a way of saving state between those calls for the next row (amongst other things).<p>This is also why there isn't a method in SQLite DB adapters to get the number of result rows. SQLite has no idea other than actually calculating them all which is the same amount of effort as getting all of them.

I wonder if anybody has dared guestimate how much money SQLite has provided to the world in terms of time saved not hacking out buggy serialization and indexing code for software projects and just using this amazing tool instead.<p>I use SQLite frequently in small projects and it's absurdly invaluable. That it's free and PD almost beggars belief.

Neat! Is there a way to tell the backend "run this bytecode, please"? If there is sqlite would make a great test bed to try out new query languages. Or, inversely, you could write a distributed database whose wire protocol was sqlite bytecode. Or you could write code translator to let you run sqlite queries on hadoop.

Informative but short article. I'd love to read more about when it's time to create a VM for your program or system. If anyone has links to more articles on VMs in practice, especially a story chronicling a transition from a non-VM architecture to a VM-based one, please post them.

Now that's just one of those things I would have never guessed. Super interesting.