SQLite数据库VDBE虚拟机执行稍微复杂的查询操作(3)

A Slightly More Complex Query

The key points of the previous example were the use of the Callback instruction to invoke the callback function, and the use of the Next instruction to implement a loop over all records of the database file. This example attempts to drive home those ideas by demonstrating a slightly more complex query that involves more columns of output, some of which are computed values, and a WHERE clause that limits which records actually make it to the callback function. Consider this query:

稍微复杂点的查询

前面例子中的关键就是使用Callback指令调用回调函数和使用Next指令实现一个循环操作遍历数据库文件所有记录。这个例子试图分析一个稍微复杂点的例子程序，设计多个列输出，计算值，和where字句生成回调函数。考虑下面的查询：

SELECT one, two, one || two AS 'both'

FROM examp

WHERE one LIKE 'H%'

This query is perhaps a bit contrived, but it does serve to illustrate our points. The result will have three column with names "one", "two", and "both". The first two columns are direct copies of the two columns in the table and the third result column is a string formed by concatenating the first and second columns of the table. Finally, the WHERE clause says that we will only chose rows for the results where the "one" column begins with an "H". Here is what the VDBE program looks like for this query:

这个查询语句不太自然，但是有助于说明我们的问题。结果的列名包括“one”“two”和“both”三个。前两个列是数据库表中列的直接拷贝，第三个列是一个连接第一列和第二列的字符串。最后，where字句确定我们选择的紫烈是第一列中以“H”开头的行。下面这个查询的VDBE操作：

Except for the WHERE clause, the structure of the program for this example is very much like the prior example, just with an extra column. There are now 3 columns, instead of 2 as before, and there are three ColumnName instructions. A cursor is opened using the OpenRead instruction, just like in the prior example. The Rewind instruction at address 6 and the Next at address 17 form a loop over all records of the table. The Close instruction at the end is there to give the Rewind instruction something to jump to when it is done. All of this is just like in the first query demonstration.

除了Where子句，这个程序的结构先前的例子非常相似，仅仅是多了一列。现在又3列了，而不是前面的两列，所有有三个ColumnName指令。OpenRead指令打开一个游标，跟前面的例子一样。Rewind指令和Next指令形成一个循环，遍历数据库表中所有数据。最后的Close指令关闭Rewind指令跳转到此处的东西。这些都跟第一个查询操作很相似。

The Callback instruction in this example has to generate data for three result columns instead of two, but is otherwise the same as in the first query. When the Callback instruction is invoked, the left-most column of the result should be the lowest in the stack and the right-most result column should be the top of the stack. We can see the stack being set up this way at addresses 11 through 15. The Column instructions at 11 and 12 push the values for the first two columns in the result. The two Column instructions at 13 and 14 pull in the values needed to compute the third result column and the Concat instruction at 15 joins them together into a single entry on the stack.

在例子中的Callback指令产生3个结果列而不是两个，否则就跟第一个查询一样。当Callback指令执行时，最左列的结果应该在栈的最底部，最右列应该在栈顶。我们可以看到从11到15条指令这样设置栈。11和12行Column指令将前两列结果压入栈。13和14条的Column指令将需要计算的第三列压入栈然后15行的Concat指令连接栈顶两个元素然后重新存入栈中。

The only thing that is really new about the current example is the WHERE clause which is implemented by instructions at addresses 7 through 10. Instructions at address 7 and 8 push onto the stack the value of the "one" column from the table and the literal string "H%". The instruction at address 9 pops these two values from the stack and pushes the result of the LIKE() function back onto the stack. The instruction pops the top stack value and causes an immediate jump forward to the Next instruction if the top value was false (not not like the literal string "H%"). Taking this jump effectively skips the callback, which is the whole point of the WHERE clause. If the result of the comparison is true, the jump is not taken and control falls through to the Callback instruction below.

当前例子中唯一新的东西是第7到10条程序实现的Where子句。在第7和8条指令将one列值以H开头的元素压入栈中。第9条Function指令弹出两个结果，条用LIKE函数并将结果返回栈中。IfNot指令弹栈，引起立即跳转到17条的Next指令，如果前面的判断是false（不是以“H”开头）。可以有效的跳转回调函数。如果结果是真，那么就不跳转，继续向下执行。

Notice how the LIKE operator is implemented. It is a user-defined function in SQLite, so the address of its function definition is specified in P3. The operand P1 is the number of function arguments for it to take from the stack. In this case the LIKE() function takes 2 arguments. The arguments are taken off the stack in reverse order (right-to-left), so the pattern to match is the top stack element, and the next element is the data to compare. The return value is pushed onto the stack.

注意LIKE操作是怎么实现的。这在SQLite里面是用户定义的函数。所以Function指令的P3操作码确定地址。P1是函数在栈中的参数个数。在这个例子中，LIKE函数有两个参数。参数在栈中是以反序的方式接受的（右-左），所以要匹配的是栈中第二个元素。函数的返回值压入栈中。