- } 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)] |