跟踪SQLite的VDBE虚拟机执行的字节码程序（2）

Tracing VDBE Program Execution

If the SQLite library is compiled without the NDEBUG preprocessor macro, then the PRAGMA causes the VDBE to trace the execution of programs. Though this feature was originally intended for testing and debugging, it can also be useful in learning about how the VDBE operates. Use "PRAGMA vdbe_trace=ON;" to turn tracing on and "PRAGMA vdbe_trace=OFF" to turn tracing back off. Like this:

跟着VDBE程序执行

如果SQLite编译时没有使用NDEBUG预处理宏，那么使用PRAGMA vdbe_trace就会跟踪VDBE的程序执行。尽管这个功能用于调试，这也是学习VDBE功能的好方法。用"PRAGMA vdbe_trace=ON;" 或者"PRAGMA vdbe_trace=OFF"来打开或者关闭该功能。如下：

With tracing mode on, the VDBE prints each instruction prior to executing it. After the instruction is executed, the top few entries in the stack are displayed. The stack display is omitted if the stack is empty.

在跟踪模式下，VDBE在执行每条指令前打印指令。执行指令之后，显示栈中情况。如果栈为空，则不显示。

On the stack display, most entries are shown with a prefix that tells the datatype of that stack entry. Integers begin with "i:". Floating point values begin with "r:". (The "r" stands for "real-number".) Strings begin with either "s:", "t:", "e:" or "z:". The difference among the string prefixes is caused by how their memory is allocated. The z: strings are stored in memory obtained from malloc(). The t: strings are statically allocated. The e: strings are ephemeral. All other strings have the s: prefix. This doesn't make any difference to you, the observer, but it is vitally important to the VDBE since the z: strings need to be passed tofree() when they are popped to avoid a memory leak. Note that only the first 10 characters of string values are displayed and that binary values (such as the result of the MakeRecord instruction) are treated as strings. The only other datatype that can be stored on the VDBE stack is a NULL, which is display without prefix as simply "NULL". If an integer has been placed on the stack as both an integer and a string, its prefix is "si:".

在栈显示中，大部分条目显示数据类型的前缀。整数以“i”开头，浮点数以“r:”开头（r代表实数）。字符串以"s:", "t:", "e:" 或者"z:"开头。字符串前缀的不同是由于申请的不同内存导致的。“z:”开头的字符串是存储在malloc申请的内存中。“t:”开头的字符串是静态存储的。“e:”开头的字符串是短暂存储的。其他类型的字符串以“s:”开头。对我们而言类型没有什么区别，但是对于VDBE而言却很重要因为“z:”类型的字符串需要在使用过之后就释放掉内存。注意字符串中仅显示前10个字符（入结果中MakeRecord指令中）。VDBE栈可以存储NULL，前缀就是NULL。如果栈中一个整数即作为整数又作为字符串，它的前缀就是“si:”。

Simple Queries

At this point, you should understand the basics of how the VDBE writes to a database. Now let's look at how it does queries. We will use the following simple SELECT statement as our example:

SELECT * FROM examp;

The VDBE program generated for this SQL statement is as follows:

简单的查询

现在，你应该懂得VDBE写数据库的基本操作。现在我们来看看它是怎么做查询的。我们使用下面select语句做实例。产生的VDBE程序如下：

Before we begin looking at this problem, let's briefly review how queries work in SQLite so that we will know what we are trying to accomplish. For each row in the result of a query, SQLite will invoke a callback function with the following prototype:

我们开始探讨这个问题之前，先来简要回顾一下在SQLite中查询工作如何完成。对应查询结果的每一条，SQLite将调用如下的回调函数：

int Callback(void *pUserData, int nColumn, char *azData[], char *azColumnName[]);

The SQLite library supplies the VDBE with a pointer to the callback function and the pUserData pointer. (Both the callback and the user data were originally passed in as arguments to the sqlite_exec() API function.) The job of the VDBE is to come up with values for nColumn, azData[], and azColumnName[].nColumn is the number of columns in the results, of course. azColumnName[] is an array of strings where each string is the name of one of the result columns. azData[] is an array of strings holding the actual data.

SQLite库支持VDBE使用pUserData指针和回调函数指针。（回调函数和用户数据都通过sqlite_exec（）API函数传入）VDBE工作是提供nColumn，azData[]和azColumnName[]的值。nColumn是结果的列数。AzColumnName是结果列名。azData是真正的数据。

0     ColumnName    0      0      one                                
1     ColumnName    1      0      two

The first two instructions in the VDBE program for our query are concerned with setting up values for azColumn. The instructions tell the VDBE what values to fill in for each element of the azColumnName[] array. Every query will begin with one ColumnName instruction for each column in the result, and there will be a matching Column instruction for each one later in the query.

VDBE程序前两个指令得到azColumn的值。ColumnName指令告诉VDBE填充azColumnName的是什么数据。每一次查询都对每一列有一条ColunName指令，在下一步的查询中会有匹配的列指令。

2     Integer       0      0                                         
3     OpenRead      0      3      examp                              
4     VerifyCookie  0      81

Instructions 2 and 3 open a read cursor on the database table that is to be queried. This works the same as the OpenWrite instruction in the INSERT example except that the cursor is opened for reading this time instead of for writing. Instruction 4 verifies the database schema as in the INSERT example.

第2，3条指令打开数据库表的读游标来执行查询。这个活跟INSERT例子中的OpenWrite指令是类似的，除了这次获得的是一个读锁。第4条指令更改了数据库模式。

5     Rewind        0      10

The instruction initializes a loop that iterates over the "examp" table. It rewinds the cursor P1 to the first entry in its table. This is required by the Column and Next instructions, which use the cursor to iterate through the table. If the table is empty, then jump to P2 (10), which is the instruction just past the loop. If the table is not empty, fall through to the following instruction at 6, which is the beginning of the loop body.

Rewind指令初始化循环，遍历examp表。游标P1指向表的第一个元组。这对于Column和Next指令来说是必须的，他们俩使用游标遍历表。如果表是空的，就跳到P2（10）处，跳出循环。如果表不空，执行6，这是循环体的开始。

6     Column        0      0                                         
7     Column        0      1                                         
8     Callback      2      0

The instructions 6 through 8 form the body of the loop that will execute once for each record in the database file. The instructions at addresses 6 and 7 each take the P2-th column from the P1-th cursor and push it onto the stack. In this example, the first Column instruction is pushing the value for the column "one" onto the stack and the second Column instruction is pushing the value for column "two". The instruction at address 8 invokes the callback() function. The P1 operand to Callback becomes the value for nColumn. The Callback instruction pops P1 values from the stack and uses them to fill theazData[] array.

指令6到8的循环对于数据库文件每条记录都执行一次。指令Column将P1指定的游标读出P2指定的列值压入栈中。在这个例子中，第一个Column指令将“one”压入栈中，第二个Column指令将“two”压入栈中。Callback指令条用callback回调函数。Callback指令的P1操作码成为nColumn的值。Callback指令从栈中弹出P1值然后填充azData数组。

9     Next          0      6

The instruction at address 9 implements the branching part of the loop. Together with the Rewind at address 5 it forms the loop logic. This is a key concept that you should pay close attention to. The instruction advances the cursor P1 to the next record. If the cursor advance was successful, then jump immediately to P2 (6, the beginning of the loop body). If the cursor was at the end, then fall through to the following instruction, which ends the loop.

指令9实现循环分支。连同5指令Rewin形成循环逻辑。这是应该关注的关键概念。Next指令操作P1游标到数据库表中的下一条记录。如果游标成功进入，就立即跳转到P2指定的位置。如果游标结束，则执行下面的指令，循环结束。

10    Close         0      0                                         
11    Halt          0      0

The Close instruction at the end of the program closes the cursor that points into the table "examp". It is not really necessary to call Close here since all cursors will be automatically closed by the VDBE when the program halts. But we needed an instruction for the Rewind to jump to so we might as well go ahead and have that instruction do something useful. The Halt instruction ends the VDBE program.

程序最后的Close指令关闭指向“examp”的游标。不必在此调用Close指令，因为当VDBE执行程序结束后，所有的游标将自动关闭。但是我们需要一个指令所有我们需要做一些有用的事情。Halt指令停止VDBE程序。

Note that the program for this SELECT query didn't contain the Transaction and Commit instructions used in the INSERT example. Because the SELECT is a read operation that doesn't alter the database, it doesn't require a transaction.

注意这个SELECT查询不包括在INSERT例子中的Transaction和Commit指令。因为SELECT是一个读操作，不需要改变数据库，所有不必是一个事务。