+ } else { + regsub -all {>} $line {\>} line + regsub -all {<} $line {\<} line + append Opcode($current_op:text) \n$line + } +} +unset file + +puts { +
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.
+ +This document is intended as a reference, not a tutorial. +A separate Virtual Machine Tutorial 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 the document has not been updated. +
+ +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.
+ +Each instruction in the virtual machine consists of an opcode and +up to three operands named P1, P2 and P3. P1 may be an arbitrary +integer. P2 must be a non-negative integer. P2 is always the +jump destination in any operation that might cause a jump. +P3 is a null-terminated +string or NULL. Some operators use all three operands. Some use +one or two. Some operators use none of the operands.
+ +
The virtual machine begins execution on instruction number 0. +Execution continues until (1) a Halt instruction is seen, or +(2) the program counter becomes one greater than the address of +last instruction, or (3) 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.
+ +The virtual machine also contains an operand stack of unlimited +depth. Many of the opcodes use operands from the stack. See the +individual opcode descriptions for details.
+ +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 (Open), 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.
+ +The virtual machine contains an arbitrary number of fixed memory +locations with addresses beginning at zero and growing upward. +Each memory location can hold an arbitrary string. The memory +cells are typically used to hold the result of a scalar SELECT +that is part of a larger expression.
+ +The virtual machine contains a single sorter. +The sorter is able to accumulate records, sort those records, +then play the records back in sorted order. The sorter is used +to implement the ORDER BY clause of a SELECT statement.
+ +The virtual machine contains a single "List". +The list stores a list of integers. The list is used to hold the +rowids for records of a database table that needs to be modified. +The WHERE clause of an UPDATE or DELETE statement scans through +the table and writes the rowid of every record to be modified +into the list. Then the list is played back and the table is modified +in a separate step.
+ +The virtual machine can contain an arbitrary number of "Sets". +Each set holds an arbitrary number of strings. Sets are used to +implement the IN operator with a constant right-hand side.
+ +The virtual machine can open a single external file for reading. +This external read file is used to implement the COPY command.
+ +Finally, the virtual machine can have a single set of aggregators. +An aggregator is a device used to implement the GROUP BY clause +of a SELECT. An aggregator has one or more slots that can hold +values being extracted by the select. The number of slots is the +same for all aggregators and is defined by the AggReset operation. +At any point in time a single aggregator is current or "has focus". +There are operations to read or write to memory slots of the aggregator +in focus. There are also operations to change the focus aggregator +and to scan through all aggregators.
+ +Every SQL statement that SQLite interprets results in a program +for the virtual machine. But if you precede the SQL statement with +the keyword "EXPLAIN" 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.
+ +You can use the sqlite command-line tool to see the +instructions generated by an SQL statement. The following is +an example:
} + +proc Code {body} { + puts {} + regsub -all {&} [string trim $body] {\&} body + regsub -all {>} $body {\>} body + regsub -all {<} $body {\<} body + regsub -all {\(\(\(} $body {} body + regsub -all {\)\)\)} $body {} body + regsub -all { } $body {\ } body + regsub -all \n $body} +} + +Code { +$ (((sqlite ex1))) +sqlite> (((.explain))) +sqlite> (((explain delete from tbl1 where two<20;))) +addr opcode p1 p2 p3 +---- ------------ ----- ----- ---------------------------------------- +0 Transaction 0 0 +1 VerifyCookie 219 0 +2 ListOpen 0 0 +3 Open 0 3 tbl1 +4 Rewind 0 0 +5 Next 0 12 +6 Column 0 1 +7 Integer 20 0 +8 Ge 0 5 +9 Recno 0 0 +10 ListWrite 0 0 +11 Goto 0 5 +12 Close 0 0 +13 ListRewind 0 0 +14 OpenWrite 0 3 +15 ListRead 0 19 +16 MoveTo 0 0 +17 Delete 0 0 +18 Goto 0 15 +19 ListClose 0 0 +20 Commit 0 0 +} + +puts { +
\n body + puts $body + puts {
All you have to do is add the "EXPLAIN" keyword to the front of the +SQL statement. But if you use the ".explain" command to sqlite +first, it will set up the output mode to make the program more easily +viewable.
+ +If sqlite has been compiled without the "-DNDEBUG=1" option +(that is, with the NDEBUG preprocessor macro not defined) then 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: +
+ ++ ++PRAGMA vdbe_trace=on; +
+You can turn tracing back off by entering a similar statement but +changing the value "on" to "off".
+ +There are currently [llength $OpcodeList] opcodes defined by +the virtual machine." +puts {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.
} + +puts { +| Opcode Name | Description |
|---|---|
| } + puts "$op" + puts " | [string trim $Opcode($op:text)] |