SQLITE数据库VDBE虚拟机(译)(1)

The Virtual Database Engine of SQLite

SQLITE数据库VDBE虚拟机

This document describes the virtual machine used in SQLite version 2.8.0. The virtual machine in SQLite version 3.0 and 3.1 is very similar in concept but many of the opcodes have changed and the algorithms are somewhat different. Use this document as a rough guide to the idea behind the virtual machine in SQLite version 3, not as a reference on how the virtual machine works.

本文档描述SQLite2.8.0版本中的虚拟机，在SQLite3.0和3.1版本概念上比较相似，不过很多操作码变了，算法也有所不同。这个文档作为SQLite3版本虚拟机设计思想一个粗略的文档而不是虚拟机工作的参考。

If you want to know how the SQLite library works internally, you need to begin with a solid understanding of the Virtual Database Engine or VDBE. The VDBE occurs right in the middle of the processing stream (see the ) and so it seems to touch most parts of the library. Even parts of the code that do not directly interact with the VDBE are usually in a supporting role. The VDBE really is the heart of SQLite.

如果你想知道SQLite库工作的内部原理，你应该深入理解虚拟数据库引擎VDBE。VDBE处于数据库处理中间位置（可以参考SQLite架构图），SQLite库大部分都与VDBE有关。即使不直接与VDBE相关的代码通常也是作为一个支持的角色。VDBE是SQLite的核心。

This article is a brief introduction to how the VDBE works and in particular how the various VDBE instructions (documented ) work together to do useful things with the database. The style is tutorial, beginning with simple tasks and working toward solving more complex problems. Along the way we will visit most submodules in the SQLite library. After completing this tutorial, you should have a pretty good understanding of how SQLite works and will be ready to begin studying the actual source code.

本文简短介绍VDBE工作原理，特别是VDBE指令如何操作数据库来完成实用的工作。这个教程是用简单任务开始，最终希望完成复杂的问题。整个过程将涉及SQLite所有的子模块。完成该教程，你应该对SQLite工作原理有一个相当好的理解，可以着手研究源代码。

Preliminaries

The VDBE implements a virtual computer that runs a program in its virtual machine language. The goal of each program is to interrogate or change the database. Toward this end, the machine language that the VDBE implements is specifically designed to search, read, and modify databases.

VDBE实现一个虚拟计算机运行它的虚拟机器代码。程序的目标是查询或者改变数据库。因此，VDBE实现的语言就是设计来搜索，读和修改数据库的。

Each instruction of the VDBE language contains an opcode and three operands labeled P1, P2, and P3. Operand P1 is an arbitrary integer. P2 is a non-negative integer. P3 is a pointer to a data structure or null-terminated string, possibly null. Only a few VDBE instructions use all three operands. Many instructions use only one or two operands. A significant number of instructions use no operands at all but instead take their data and store their results on the execution stack. The details of what each instruction does and which operands it uses are described in the separate document.

VDBE语言的每条指令都包括一个操作码和三个操作数，P1，P2，P3。P1是一个任意整数，P2是一个非负数，P3是一个结构体指针（可能为空）或者非空的字符串。很少的指令使用全部三个操作数。大多数指令仅仅使用一个或者两个操作数。也有一些指令没有操作数，而是操作在执行栈上的数据。每条指令的操作的细节和操作数的含义在操作符描述文档中。

A VDBE program begins execution on instruction 0 and continues with successive instructions until it either (1) encounters a fatal error, (2) executes a Halt instruction, or (3) advances the program counter past the last instruction of the program. When the VDBE completes execution, all open database cursors are closed, all memory is freed, and everything is popped from the stack. So there are never any worries about memory leaks or undeallocated resources.

一个VDBE程序从开始执行一直到遇到以下指令（1）致命错误（2）遇到停机指令（3）程序的最后一条指令。VDBE执行完后，所有的数据库游标都会关闭，所有的内存都释放，栈中数据都会弹出。所有不必担心内存泄露。

If you have done any assembly language programming or have worked with any kind of abstract machine before, all of these details should be familiar to you. So let's jump right in and start looking as some code.

如果你以前写过汇编代码或者坐过一些抽象机的工作，那么所有的细节都应该很熟悉。所有我们开始看代码吧。

Inserting Records Into The Database

向数据库中插入记录

We begin with a problem that can be solved using a VDBE program that is only a few instructions long. Suppose we have an SQL table that was created like this:

开始使用VDBE程序解决的问题仅仅需要几条指令。假设我们想建立这样一个表：

CREATE TABLE examp(one text, two int);

In words, we have a database table named "examp" that has two columns of data named "one" and "two". Now suppose we want to insert a single record into this table. Like this:

我们有个数据库表叫examp，它有两列数据，一个名字是“one”，另一个是“two”。假设我们插入一条记录，如下：

INSERT INTO examp VALUES('Hello, World!',99);

We can see the VDBE program that SQLite uses to implement this INSERT using the sqlite command-line utility. First start up sqlite on a new, empty database, then create the table. Next change the output format of sqlite to a form that is designed to work with VDBE program dumps by entering the ".explain" command. Finally, enter the [INSERT] statement shown above, but precede the [INSERT] with the special keyword [EXPLAIN]. The [EXPLAIN] keyword will causesqlite to print the VDBE program rather than execute it. We have:

我们可以在命令行下看到SQLite实现INSERT操作的VDBE程序。第一次启动SQLite建立一个新的空的数据库，然后创建表。然后通过“.explain”命令输出SQLite的VDBE程序。最后，在插入数据指令前面加[EXPLAIN]，这个[EXPLAIN]关键字将使SQLite输出VDBE程序而不是执行它。如下：

As you can see above, our simple insert statement is implemented in 12 instructions. The first 3 and last 2 instructions are a standard prologue and epilogue, so the real work is done in the middle 7 instructions. There are no jumps, so the program executes once through from top to bottom. Let's now look at each instruction in detail.

如你所见，我们简单的插入将被12条指令实现。前三条和最后2条是标准的开头和结尾，所有真正工作的是中间的7条指令。没有跳转指令，所以程序从头执行到尾。下面我们仔细研究每条指令。

The instruction begins a transaction. The transaction ends when a Commit or Rollback opcode is encountered. P1 is the index of the database file on which the transaction is started. Index 0 is the main database file. A write lock is obtained on the database file when a transaction is started. No other process can read or write the file while the transaction is underway. Starting a transaction also creates a rollback journal. A transaction must be started before any changes can be made to the database.

指令Transaction开始一个事务。到遇到Commit或者Rollback操作码时事务结束。P1是事务执行的数据库文件的索引（此处的所以不是数据库的索引，应该算是一种表示，即SQLite第一个页）。索引0是主数据库文件。当事务开始时应该获得一个写锁。当这个事务在执行的时候其他的程序不能读或者写这个文件。开始的一个事务创建一个回滚日志。在对数据库改变前必须启动一个事务。

The instruction checks cookie 0 (the database schema version) to make sure it is equal to P2 (the value obtained when the database schema was last read). P1 is the database number (0 for the main database). This is done to make sure the database schema hasn't been changed by another thread, in which case it has to be reread.

指令VerifyCookie检查cookie 0（数据库schema版本）以确保它等于P2（数据库schema最后读出的值）。P1是数据库号（0代表主数据库）。这是确保数据库schema没有被其他线程改写而导致它必须被重新读入。

The second instruction begins a transaction and starts a rollback journal for database 1, the database used for temporary tables.

第二个Transaction指令开始一个事务并且为数据库1创建回滚日志，这个数据库是作为一个临时表。

The instruction pushes the integer value P1 (0) onto the stack. Here 0 is the number of the database to use in the following OpenWrite instruction. If P3 is not NULL then it is a string representation of the same integer. Afterwards the stack looks like this:

指令Integer将整数值P1（0）压入栈中。这里0是在OpenWrite指令中制定的数据库的值。如果P3不为空，那么它是一个相同的字符串代表一个整数。之后栈内内容如下：

(integer) 0

The instruction opens a new read/write cursor with handle P1 (0 in this case) on table "examp", whose root page is P2 (3, in this database file). Cursor handles can be any non-negative integer. But the VDBE allocates cursors in an array with the size of the array being one more than the largest cursor. So to conserve memory, it is best to use handles beginning with zero and working upward consecutively. Here P3 ("examp") is the name of the table being opened, but this is unused, and only generated to make the code easier to read. This instruction pops the database number to use (0, the main database) from the top of the stack, so afterwards the stack is empty again.

指令OpenWrite根据P1打开一个新的读写游标操作表examp，examp表的根页为P2指定（3，在该数据库文件中）。游标句柄可以为任意非零整数。但是VDBE仅仅分配比最大游标数小的游标。因此，为了节省内存，最好从0开始依次分配游标。这个P3（examp）是打开的表名，但没有被使用，仅仅产生使程序易读。这个指令从栈顶弹出数据库号，然后栈就为空了。

The instruction creates a new integer record number for the table pointed to by cursor P1. The record number is one not currently used as a key in the table. The new record number is pushed onto the stack. Afterwards the stack looks like this:

指令NewRecno创建新整数记录。记录数是一个在表中未被使用的数字。新的记录数压入栈中。栈形式如下：

(integer) new record key

The instruction pushes its P3 operand onto the stack. Afterwards the stack looks like this:

指令String压入P3操作数到栈中。栈如下：

(string) "Hello, World!"

(integer) new record key

The instruction pushes its P1 operand (99) onto the stack. Afterwards the stack looks like this:

指令Integer压入P1操作数（99）到栈中。栈如下：

(integer) 99

(string) "Hello, World!"

(integer) new record key

The instruction pops the top P1 elements off the stack (2 in this case) and converts them into the binary format used for storing records in a database file. (See the description for details.) The new record generated by the MakeRecord instruction is pushed back onto the stack. Afterwards the stack looks like this:

指令MakeRecord弹栈，由P1制定出栈元素个数（2，在这个例子中）并且将他们转化为存储到数据库文件中的二进制格式。（具体细节查看文件格式）由MakeRecord指令产生的新记录重新压回栈中。如下：

(record) "Hello, World!", 99

(integer) new record key

The instruction uses the top 2 stack entries to write an entry into the table pointed to by cursor P1. A new entry is created if it doesn't already exist or the data for an existing entry is overwritten. The record data is the top stack entry, and the key is the next entry down. The stack is popped twice by this instruction. Because operand P2 is 1 the row change count is incremented and the rowid is stored for subsequent return by the sqlite_last_insert_rowid() function. If P2 is 0 the row change count is unmodified. This instruction is where the insert actually occurs.

指令PutIntKey使用栈顶部两个元素写入游标P1指定的表中。如果元组不存在就创建，如果元组存在覆盖。记录数据在栈顶，键值是栈下一个元组。在该指令下栈弹出两次。因为操作数P2是1，行更改计数增加，由函数sqlite_last_insert_rowid()返回值存储到rowid中。如果P2为0，行更改数不变。该指令是插入发生的地方。

The instruction closes a cursor previously opened as P1 (0, the only open cursor). If P1 is not currently open, this instruction is a no-op.

指令Close关闭先前由P1（0，打开的游标）指定的游标。如果P1没有打开，则为空操作。

The instruction causes all modifications to the database that have been made since the last Transaction to actually take effect. No additional modifications are allowed until another transaction is started. The Commit instruction deletes the journal file and releases the write lock on the database. A read lock continues to be held if there are still cursors open.

指令Commit完成对数据库所有的修改。直到下次事务开始，没有额外的修改。Commit指令删除日志文件释放写锁。如果游标打开着，读锁始终保持。

The instruction causes the VDBE engine to exit immediately. All open cursors, Lists, Sorts, etc are closed automatically. P1 is the result code returned by sqlite_exec(). For a normal halt, this should be SQLITE_OK (0). For errors, it can be some other value. The operand P2 is only used when there is an error. There is an implied "Halt 0 0 0" instruction at the end of every program, which the VDBE appends when it prepares a program to run.

指令Halt使VDBE虚拟机立即退出。所有打开的游标，列表，排序等都立即关闭。P1是sqlite_exec()返回值。对于正常停止，应该返回SQLITE_OK（0）。如果是错误值，可能返回不同值。操作数P2仅仅当错误发生的时候才被用到。在每个程序最后有个隐含的Halt指令，VDBE准备运行程序时都会把它追加上。