SQLite虚拟机操作码(译)（1）

SQLite Virtual Machine Opcodes SQLite虚拟机操作码 Introduction

In order to execute an SQL statement, the SQLite library first parses the SQL, analyzes the statement, then generates a short program to execute the statement. The program is generated for a "virtual machine" implemented by the SQLite library. This document describes the operation of that virtual machine.

为了执行一个SQL语句，SQLite库首先解析SQL，分析该语句，然后生成简短的程序来执行该语句。产生的程序将由SQLite库实现的虚拟机来执行。本文档描述虚拟机的操作。

This document is intended as a reference, not a tutorial. A separate is available. If you are looking for a narrative description of how the virtual machine works, you should read the tutorial and not this document. Once you have a basic idea of what the virtual machine does, you can refer back to this document for the details on a particular opcode. Unfortunately, the virtual machine tutorial was written for SQLite version 1.0. There are substantial changes in the virtual machine for version 2.0 and again for version 3.0.0 and again for version 3.5.5 and the document has not been updated. But the basic concepts behind the virtual machine still apply.

本文档目的是作为一个参考而不是教程。一个单独的虚拟机教程是必须的。如果你正在寻找一个描述虚拟机如何工作的文章，你应该阅读VDBE教程而不是这个文档。如果你知道虚拟机如何工作的，想了解每一个指令的细节，那就需要阅读该文章。不幸的是，虚拟机的文章是为SQLite1.0版本写的。对于2.0版本，3.00版本和3.5.5版本的虚拟机都有很大的改变，但是文档却没有改变。但是虚拟机的基本概念仍然可以使用。

The source code to the virtual machine is in the vdbe.c source file. All of the opcode definitions further down in this document are contained in comments in the source file. In fact, the opcode table in this document was generated by scanning the vdbe.c source file and extracting the necessary information from comments. So the source code comments are really the canonical source of information about the virtual machine. When in doubt, refer to the source code.

虚拟机的源代码是vdbe.c文件。所有操作码的定义都包含在源文件注释中。事实上，该文档的操作码表就是从vdbe.c源文件中得到的，从注释中提取了关键信息。所有源代码注释是关于虚拟机的经典注释。当有疑问的时候，参考源代码。

Each instruction in the virtual machine consists of an opcode and up to five operands named P1, P2 P3, P4, and P5. P1, P2, and P3 are 32-bit signed integers. These operands often refer to registers. P2 is always the jump destination in any operation that might cause a jump. P4 may be a 32-bit signed integer, a 64-bit signed integer, a 64-bit floating point value, a string literal, a Blob literal, a pointer to a collating sequence comparison function, or a pointer to the implementation of an application-defined SQL function, or various other things. P5 is an unsigned character normally used as a flag. Some operators use all five operands. Some use one or two. Some operators use none of the operands.

虚拟机的指令管理包括一个操作码和最多5个操作数，P1，P2，P3，P4和P5。P1,P2,P3是32位有符号整数。这些操作数代表寄存器。P2是跳转指令的目的地址。P4可以作为一个32位有符号整数，或者一个64为油乎乎整数，或者64为浮点数，或者字符串，或者Blob串，或者一个指向顺序比较函数的指针，或者一个指向实现应用定义的SQL函数指针，或者其他的。P5是一个无符号字符，通常作为一个标志。一些指令使用5个操作数，一些使用一个或者两个操作数。还有的指令没有操作数。

The virtual machine begins execution on instruction number 0. Execution continues until a Halt instruction is seen, or the program counter becomes one greater than the address of last instruction, or there is an execution error. When the virtual machine halts, all memory that it allocated is released and all database cursors it may have had open are closed. If the execution stopped due to an error, any pending transactions are terminated and changes made to the database are rolled back.

虚拟机从第0条指令开始执行。当遇到停机指令，或者程序计数器比最后一条指令大的时候，或者出现执行错误的时候程序停止。当虚拟机停机时，所有内存都会释放，数据库都会关闭。如果是因为错误停止执行，所有挂起的事物都将停止，所做的改变都会回滚。

The virtual machine can have zero or more cursors. Each cursor is a pointer into a single table or index within the database. There can be multiple cursors pointing at the same index or table. All cursors operate independently, even cursors pointing to the same indices or tables. The only way for the virtual machine to interact with a database file is through a cursor. Instructions in the virtual machine can create a new cursor (OpenRead or OpenWrite), read data from a cursor (Column), advance the cursor to the next entry in the table (Next) or index (NextIdx), and many other operations. All cursors are automatically closed when the virtual machine terminates.

虚拟机可以有零个活多个游标，每个游标指向一个数据库中的一个表或者索引。可以又多个游标执行同一个表或者索引。所有的游标独立操作，即使指向同一个表或者所有的不同游标。虚拟机同数据库文件交互的唯一方式就是通过游标。虚拟机创建游标的指令是OpenRead或者OpenWrite，从游标中读数据是Column，将游标指向表的下一个条目的指令是Next,索引的下一个是NextIdx等等还有其他很多操作。当虚拟机停止时所有游标自动关闭。

The virtual machine contains an arbitrary number of registers locations with addresses beginning at one and growing upward. Each memory location can hold an arbitrary string. The registers hold all intermediate results of a calculation.

虚拟机包括任意多个寄存器，从地址开始处逐个增长。每个地址能保存一个任意字符串。寄存器保存计算的中间结果。

Viewing Programs Generated By SQLite

Every SQL statement that SQLite interprets results in a program for the virtual machine. But if you precede the SQL statement with the keyword the virtual machine will not execute the program. Instead, the instructions of the program will be returned like a query result. This feature is useful for debugging and for learning how the virtual machine operates.

每一条SQL语句都由SQLite翻译成可以在虚拟机上执行的一个小程序。如果你在SQL语句前面加上关键字EXPLAIIN虚拟机就不会执行改程序，而是返回语句结果。该功能是用来调试和学习虚拟机运作的。

You can use the sqlite3.exe command-line interface (CLI) tool to see the instructions generated by an SQL statement. The following is an example:

可以在命令行工具下看到SQL语句生成的指令。如下：

All you have to do is add the keyword to the front of the SQL statement. But if you use the ".explain" command in the CLI, it will set up the output mode to make the program more easily viewable.

你所做的就是在SQL语句前面加上EXPLAIN关键字。如果你使用“.explain”模式，它将使输出模式更加容易浏览。

Depending on compile-time options, you can put the SQLite virtual machine in a mode where it will trace its execution by writing messages to standard output. The non-standard SQL "PRAGMA" comments can be used to turn tracing on and off. To turn tracing on, enter:

根据编译时间选项，你可以将虚拟机模式设置到可以将执行消息写出到标准输出上。可以使用非标准的SQL语句“PRAGMA”命令。打开跟踪模式，如下：

PRAGMA vdbe_trace=on;

You can turn tracing back off by entering a similar statement but changing the value "on" to "off".

也可以关闭。

The Opcodes

There are currently 146 opcodes defined by the virtual machine. All currently defined opcodes are described in the table below. This table was generated automatically by scanning the source code from the file vdbe.c.

现在在虚拟机上定义146个操作码。下表就是当前操作码的详细描述。这个表是通过扫描vdbe.c文件自动生成的。

Opcode Name	Description
Add	Add the value in register P1 to the value in register P2 and store the result in register P3. If either input is NULL, the result is NULL. 将P1和P2指向的寄存器值相加，存储到P3上。如果任意输入（P1或P2）为NULL，则输出（P3）为NULL
AddImm	Add the constant P2 to the value in register P1. The result is always an integer. To force any register to be an integer, just add 0. 将立即数P2加到寄存器P1上。结果为寄存器值。
Affinity	Apply affinities to a range of P2 registers starting with P1. P4 is a string that is P2 characters long. The nth character of the string indicates the column affinity that should be used for the nth memory cell in the range. P4是一个字符串，长度由P2确定。第n个字符表示列第n个内存单元。
AggFinal	Execute the finalizer function for an aggregate. P1 is the memory location that is the accumulator for the aggregate. P2 is the number of arguments that the step function takes and P4 is a pointer to the FuncDef for this function. The P2 argument is not used by this opcode. It is only there to disambiguate functions that can take varying numbers of arguments. The P4 argument is only needed for the degenerate case where the step function was not previously called. 在最终的函数上执行聚合操作。P1是聚合操作的存储位置。P2是函数接受的参数数量，P4是函数FuncDef指针。P2参数不被操作码使用。它是唯一的可以使用不同参数的函数。
AggStep	Execute the step function for an aggregate. The function has P5 arguments. P4 is a pointer to the FuncDef structure that specifies the function. Use register P3 as the accumulator. The P5 arguments are taken from register P2 and its successors. 单步执行聚合操作。该函数使用P5参数。P4指向FuncDef结构体唯一确定一个函数。P3寄存器作为一个累加器。P5参数都是从P2寄存器获取的。
And	Take the logical AND of the values in registers P1 and P2 and write the result into register P3. If either P1 or P2 is 0 (false) then the result is 0 even if the other input is NULL. A NULL and true or two NULLs give a NULL output. 将P1和P2寄存器的值进行逻辑与操作，结果存储在寄存器P3中。如果P1或者P2为0（false），则结果为0即使另一个输入为NULL。一个NULL与上真或者两个NULL结果都将为NULL。
AutoCommit	Set the database auto-commit flag to P1 (1 or 0). If P2 is true, roll back any currently active btree transactions. If there are any active VMs (apart from this one), then a ROLLBACK fails. A COMMIT fails if there are active writing VMs or active VMs that use shared cache. This instruction causes the VM to halt. 设置数据库自动提交标志P1。如果P2为真，回滚当前活动的btree的事务。如果还有其他活动的虚拟机（除了当前的），回滚失败。如果有活动的虚拟机或者或者的虚拟机共享缓冲，提交失败。 这条指令会导致虚拟机停机。
BitAnd	Take the bit-wise AND of the values in register P1 and P2 and store the result in register P3. If either input is NULL, the result is NULL. 将寄存器P1和P2的值按位与，将结果存储在P3中。如果输入为NULL，结果为NULL。
BitNot	Interpret the content of register P1 as an integer. Store the ones-complement of the P1 value into register P2. If P1 holds a NULL then store a NULL in P2. 寄存器P1中内容为整数，将P1内容的补植存储到P2中。如果P1是NULL，那么P2也为NULL。
BitOr	Take the bit-wise OR of the values in register P1 and P2 and store the result in register P3. If either input is NULL, the result is NULL. 将寄存器P1和P2按位进行OR操作，存储在P3中。如果有一个输入为NULL，结果为NULL。
Blob	P4 points to a blob of data P1 bytes long. Store this blob in register P2.
Checkpoint	Checkpoint database P1. This is a no-op if P1 is not currently in WAL mode. Parameter P2 is one of SQLITE_CHECKPOINT_PASSIVE, FULL or RESTART. Write 1 or 0 into mem[P3] if the checkpoint returns SQLITE_BUSY or not, respectively. Write the number of pages in the WAL after the checkpoint into mem[P3+1] and the number of pages in the WAL that have been checkpointed after the checkpoint completes into mem[P3+2]. However on an error, mem[P3+1] and mem[P3+2] are initialized to -1. 检查点操作数据库P1。如果在WAL模式下，无该操作。参数P2是参数SQLITE_CHECKPOINT_PASSIVE, FULL或者RESTART之一。如果检查点操作返回SQLITE_BUSY则将1写进mem[P3],如果检查点操作返回非SQLITE_BUSY，将0写入mem[P3]。在WAL模式下，在将检查点后，将页数写入mem[P3+1]，将检查点完成的操作写入mem[P3+2].如果有错误mem[P3+1]和mem[P3+2]初始化为-1。
Clear	Delete all contents of the database table or index whose root page in the database file is given by P1. But, unlike Destroy, do not remove the table or index from the database file. 删除由P1指定根页的数据库表和索引中所有的内容。但是，不像Destroy操作，不从数据库文件中移除表和索引。 The table being clear is in the main database file if P2==0. If P2==1 then the table to be clear is in the auxiliary database file that is used to store tables create using CREATE TEMPORARY TABLE. 如果P2==0，表在主数据库文件中。如果P2==1，表是辅助数据库中，这个辅助数据库是由CREATE TEMPORARY TABLE创建的。 If the P3 value is non-zero, then the table referred to must be an intkey table (an SQL table, not an index). In this case the row change count is incremented by the number of rows in the table being cleared. If P3 is greater than zero, then the value stored in register P3 is also incremented by the number of rows in the table being cleared. See also: Destroy 如果P3值是非0的，则表必须是intkey表。在这种情况下，列变化量通过表清除增加。如果P3比0大，存储在P3中的值增加。
Close	Close a cursor previously opened as P1. If P1 is not currently open, this instruction is a no-op. 关闭先前打开的游标P1。如果P1是关闭的，该操作为空操作。