UPDATE和DELETE实例（5）

UPDATE And DELETE Statements

The UPDATE and DELETE statements are coded using a template that is very similar to the SELECT statement template. The main difference, of course, is that the end action is to modify the database rather than invoke a callback function. Because it modifies the database it will also use transactions. Let's begin by looking at a DELETE statement:

UPDATE和DELETE语句

UPDATE和DELETE语句使用的模板跟SELECT语句模板类似。主要的区别是这两个语句最后的动作是修改数据库而不是调用回调函数。因为它修改数据库，所有它将使用事务。下面看看DELETE语句：

DELETE FROM examp WHERE two<50;

This DELETE statement will remove every record from the "examp" table where the "two" column is less than 50. The code generated to do this is as follows:

这个DELETE语句将two列小于50的所有记录都删除。产生的代码如下：

Here is what the program must do. First it has to locate all of the records in the table "examp" that are to be deleted. This is done using a loop very much like the loop used in the SELECT examples above. Once all records have been located, then we can go back through and delete them one by one. Note that we cannot delete each record as soon as we find it. We have to locate all records first, then go back and delete them. This is because the SQLite database backend might change the scan order after a delete operation. And if the scan order changes in the middle of the scan, some records might be visited more than once and other records might not be visited at all.

这些都是程序必须完成的。首先必须定义到表examp所有需要删除的记录。这跟SELECT例子中的循环类似。一旦所有的记录定位成功，我们就可以逐条删除了。注意我们不能一发现就删除它们。我们必须先定位，然后再删除。因为SQLite数据库删除可能更改后端扫描的顺序。如果扫描过程中顺序更改，一些记录可能访问多次，而另一些可能没有被访问。

So the implementation of DELETE is really in two loops. The first loop (instructions 5 through 11) locates the records that are to be deleted and saves their keys onto a temporary list, and the second loop (instructions 16 through 19) uses the key list to delete the records one by one.

所以，实现DELETE操作需要两个循环。第一个循环（指令5到11）定位需要删除的记录并且将他们的键值保存到一个临时列表中。第二个循环（指令16到19）使用临时列表逐个删除记录。

0     Transaction   1      0
1     Transaction   0      0
2     VerifyCookie  0      178
3     Integer       0      0
4     OpenRead      0      3      examp

Instructions 0 though 4 are as in the INSERT example. They start transactions for the main and temporary databases, verify the database schema for the main database, and open a read cursor on the table "examp". Notice that the cursor is opened for reading, not writing. At this stage of the program we are only going to be scanning the table, not changing it. We will reopen the same table for writing later, at instruction 15.

指令0到4在INSERT例子中。为主数据库和临时数据库开始一个事务，改变主数据库的模式，为表“examp”打开一个读游标。注意这个游标是为读打开的，而不是写。在这个阶段，我们只需要扫描表，而不需要改变它。我们会在第15条指令处以写的方式打开表。

5     Rewind        0      12

As in the SELECT example, the instruction rewinds the cursor to the beginning of the table, readying it for use in the loop body.

就像SELECT例子，Rewind指令重新定位游标到表开头，将它准备在循环体使用。

6     Column        0      1
7     Integer       50     0      50
8     Ge            1      11

The WHERE clause is implemented by instructions 6 through 8. The job of the where clause is to skip the ListWrite if the WHERE condition is false. To this end, it jumps ahead to the Next instruction if the "two" column (extracted by the Column instruction) is greater than or equal to 50.

WHERE子句是通过6到8指令实现的。WHERE子句是如果WHERE条件是false的，就跳过ListWrite指令。在最后，如果两列的值大于等于50时，就跳过头去执行NEXT指令。

As before, the Column instruction uses cursor P1 and pushes the data record in column P2 (1, column "two") onto the stack. The Integer instruction pushes the value 50 onto the top of the stack. After these two instructions the stack looks like:

跟以前一样，Column指令使用游标P1并且将列P2中的数据记录（1）压入栈中。Integer指令将50压入栈中。此时，栈如下：

(integer) 50

(record) current record for column "two"

The operator compares the top two elements on the stack, pops them, and then branches based on the result of the comparison. If the second element is >= the top element, then jump to address P2 (the Next instruction at the end of the loop). Because P1 is true, if either operand is NULL (and thus the result is NULL) then take the jump. If we don't jump, just advance to the next instruction.

Ge操作符比较栈中两个元素，弹出他们，然后根据比较结果进行分支处理。如果第二个元素>=第一个元素，跳到P2指定的地址（Next指令在循环的尾部）。因为P1是真，如果任意指令为NULL（所以结果为NULL）就跳转。如果不跳转，执行下一条指令。

9     Recno         0      0
10    ListWrite     0      0

The instruction pushes onto the stack an integer which is the first 4 bytes of the key to the current entry in a sequential scan of the table pointed to by cursor P1. The instruction writes the integer on the top of the stack into a temporary storage list and pops the top element. This is the important work of this loop, to store the keys of the records to be deleted so we can delete them in the second loop. After this ListWrite instruction the stack is empty again.

Recon指令将一个整数压入堆栈，这个整数是游标P1对当前条目的顺序扫描。ListWrite指令写入到一个临时存储列表整数堆栈的顶部弹出栈顶元素。这是循环中最重要的工作，存储记录的关键字，并在第二次循环中删除。ListWrite指令执行完后栈为空。

11    Next          0      6
12    Close         0      0

The Next instruction increments the cursor to point to the next element in the table pointed to by cursor P0, and if it was successful branches to P2 (6, the beginning of the loop body). The Close instruction closes cursor P1. It doesn't affect the temporary storage list because it isn't associated with cursor P1; it is instead a global working list (which can be saved with ListPush).

Next指令增加游标P0，使其指向表中下一条元素，如果执行成功就跳转到操作码P2（6，循环体的开始）。Close指令关闭游标P1。它不关闭临时存储列表，以为不跟游标P1关联。这是个全局工作列表（使用ListPush指令存储）。

13    ListRewind    0      0

The instruction rewinds the temporary storage list to the beginning. This prepares it for use in the second loop.

ListRewind指令重新设置临时存储里边。为第二次循环做准备。

14    Integer       0      0
15    OpenWrite     0      3

As in the INSERT example, we push the database number P1 (0, the main database) onto the stack and use OpenWrite to open the cursor P1 on table P2 (base page 3, "examp") for modification.

在INSERT例子中，我们将数据库号P1（0，主数据库）压入栈中，使用OpenWrite指令打开游标P1修改表P2（在examp数据库中，从第3页开始）。

16    ListRead      0      20
17    NotExists     0      19
18    Delete        0      1
19    Goto          0      16

This loop does the actual deleting. It is organized differently from the one in the UPDATE example. The ListRead instruction plays the role that the Next did in the INSERT loop, but because it jumps to P2 on failure, and Next jumps on success, we put it at the start of the loop instead of the end. This means that we have to put a Goto at the end of the loop to jump back to the loop test at the beginning. So this loop has the form of a C while(){...} loop, while the loop in the INSERT example had the form of a do{...}while() loop. The Delete instruction fills the role that the callback function did in the preceding examples.

这个循环执行删除操作。这个跟UPDATE例子中组织是不同的。ListRead指令扮演INSERT循环中的Next指令的角色，如果失败就跳到P2处，Next指令跳转到success，我们将它置于循环开始而不是结束。这意味着我们必须将Goto指令置于循环的最后，跳转到循环的开始。所有这个循环就跟c语言的while（）{}循环中一样，然而在INSERT的循环就跟c语言的do{}while（）循环一样。Delete指令跟以前的例子一样。

The instruction reads an element from the temporary storage list and pushes it onto the stack. If this was successful, it continues to the next instruction. If this fails because the list is empty, it branches to P2, which is the instruction just after the loop. Afterwards the stack looks like:

ListRead指令从临时存储列表中读取元素并且压入栈中。如果操作成功，继续执行下一条指令。如果失败，列表为空，执行P2分支，这个分支从循环结束开始。栈如下：

(integer) key for current record

Notice the similarity between the ListRead and Next instructions. Both operations work according to this rule:

注意在ListRead和Next指令有相似性。这两条指令都有如下规则：

Push the next "thing" onto the stack and fall through OR jump to P2, depending on whether or not there is a next "thing" to push.

One difference between Next and ListRead is their idea of a "thing". The "things" for the Next instruction are records in a database file. "Things" for ListRead are integer keys in a list. Another difference is whether to jump or fall through if there is no next "thing". In this case, Next falls through, and ListRead jumps. Later on, we will see other looping instructions (NextIdx and SortNext) that operate using the same principle.

Next和ListRead指令的不同是他们处理“thing”的不同。Next指令的“things”处理数据库文件。ListRead指令的“things”是列表的关键码。下一步，将看到其他循环指令（NextIdx和SortNext指令）按照其他方式执行。

The instruction pops the top stack element and uses it as an integer key. If a record with that key does not exist in table P1, then jump to P2. If a record does exist, then fall through to the next instruction. In this case P2 takes us to the Goto at the end of the loop, which jumps back to the ListRead at the beginning. This could have been coded to have P2 be 16, the ListRead at the start of the loop, but the SQLite parser which generated this code didn't make that optimization.

The does the work of this loop; it pops an integer key off the stack (placed there by the preceding ListRead) and deletes the record of cursor P1 that has that key. Because P2 is true, the row change counter is incremented.

The jumps back to the beginning of the loop. This is the end of the loop.

20    ListReset     0      0
21    Close         0      0
22    Commit        0      0
23    Halt          0      0

This block of instruction cleans up the VDBE program. Three of these instructions aren't really required, but are generated by the SQLite parser from its code templates, which are designed to handle more complicated cases.

这个指令块清除VDBE程序。前三条指令不是必须的，但是SQLite语法分析器产生的模板例子来处理更加复杂的例子。

The instruction empties the temporary storage list. This list is emptied automatically when the VDBE program terminates, so it isn't necessary in this case. The Close instruction closes the cursor P1. Again, this is done by the VDBE engine when it is finished running this program. The Commit ends the current transaction successfully, and causes all changes that occurred in this transaction to be saved to the database. The final Halt is also unnecessary, since it is added to every VDBE program when it is prepared to run.

ListReset指令清空临时存储列表。当VDBE程序结束后列表自动清空，所有该指令不是必须的。Close指令关闭游标P1。再次，这是通过VDBE引擎完成时运行此程序。提交成功结束当前事务，并导致在这笔交易中发生的所有更改保存到数据库。最终停止也是不必要的，因为它被添加到每个VDBE程序时，它准备运行。