

Exploring the Virtual Database Engine inside SQLite - motter
http://coderweekly.com/articles/exploring-sqlites-virtual-database-engine.html

======
rogerbinns
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.

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

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.

~~~
finnw
> _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)._

They could have achieved the same effect by implementing it in a language with
continuations or coroutines, or maybe just iterator functions. That might be
viable today if the language is fast enough and callable from C.

C# and LuaJIT spring to mind.

~~~
ominous_prime
But in the end, you're just trading performance for abstractions. There's not
really a functional difference between writing the DB in a language running on
a VM implemented in C; or writing a DB _with_ a VM implemented in C.

If performance and portability are your goals, C is going to be the logical
choice.

~~~
rabidsnail
You already have the abstractions, they're just C abstractions.

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

I use SQLite frequently in small projects and it's absurdly invaluable. That
it's free and PD almost beggars belief.

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

~~~
motter
Take a look at Oracle Berkeley DB:

"Berkeley DB is not exposed to the end-user. It is totally hidden below the
SQLite APIs. It acts as the storage engine in place of SQLite's own BTREE. An
application written to use the SQLite version 3 API can switch to Oracle
Berkeley DB with no code changes, simply re-link against Berkeley DB."

[http://www.oracle.com/technetwork/database/berkeleydb/overvi...](http://www.oracle.com/technetwork/database/berkeleydb/overview/sql-160887.html)

I haven't had time to read the full paper, but it looks very interesting.

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

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

