2 # Run this Tcl script to generate the sqlite.html file.
4 set rcsid
{$Id: opcode.tcl
,v
1.15 2005/03/09 12:26:51 danielk1977 Exp
$}
6 header
{SQLite Virtual Machine Opcodes
}
8 <h2
>SQLite Virtual Machine Opcodes
</h2
>
11 set fd
[open [lindex $argv 0] r
]
12 set file [read $fd [file size
[lindex $argv 0]]]
15 foreach line
[split $file \n] {
16 set line
[string trim
$line]
17 if {[string index
$line 1]!="*"} {
21 if {[regexp {^
/\* Opcode
: } $line]} {
22 set current_op
[lindex $line 2]
23 set txt
[lrange $line 3 end
]
24 regsub -all {>} $txt {\>
;} txt
25 regsub -all {<} $txt {\<
;} txt
26 set Opcode
($current_op:args
) $txt
27 lappend OpcodeList
$current_op
30 if {$current_op==""} continue
31 if {[regexp {^
\*/} $line]} {
35 set line
[string trim
[string range
$line 3 end
]]
37 append Opcode
($current_op:text) \n<p
>
39 regsub -all {>} $line {\>
;} line
40 regsub -all {<} $line {\<
;} line
41 append Opcode
($current_op:text) \n$line
49 <p
>In order to execute an SQL statement
, the SQLite library first parses
50 the SQL
, analyzes the statement
, then generates a short program to execute
51 the statement. The program is generated
for a
"virtual machine" implemented
52 by the SQLite library. This document describes the operation of that
55 <p
>This document is intended as a reference
, not a tutorial.
56 A separate
<a href
="vdbe.html">Virtual Machine Tutorial
</a
> is
57 available. If you are looking
for a narrative description
58 of how the virtual machine works
, you should
read the tutorial
59 and not this document. Once you have a basic idea of what the
60 virtual machine does
, you can refer back to this document
for
61 the details on a particular opcode.
62 Unfortunately
, the virtual machine tutorial was written
for
63 SQLite version
1.0. There are substantial changes in the virtual
64 machine
for version
2.0 and the document has not been updated.
67 <p
>The
source code to the virtual machine is in the
<b
>vdbe.c
</b
> source
68 file. All of the opcode definitions further down in this document are
69 contained in comments in the
source file. In fact
, the opcode table
71 was generated by scanning the
<b
>vdbe.c
</b
> source file
72 and extracting the necessary information from comments. So the
73 source code comments are really the canonical
source of information
74 about the virtual machine. When in doubt
, refer to the
source code.
</p
>
76 <p
>Each instruction in the virtual machine consists of an opcode and
77 up to three operands named P1
, P2 and P3. P1 may be an arbitrary
78 integer. P2 must be a non-negative integer. P2 is always the
79 jump destination in any operation that might cause a jump.
80 P3 is a null-terminated
81 string or NULL. Some operators use all three operands. Some use
82 one or two. Some operators use none of the operands.
<p
>
84 <p
>The virtual machine begins execution on instruction number
0.
85 Execution continues until
(1) a Halt instruction is seen
, or
86 (2) the program counter becomes one greater than the address of
87 last instruction
, or
(3) there is an execution
error.
88 When the virtual machine halts
, all
memory
89 that it allocated is released and all database
cursors it may
90 have had
open are closed. If the execution stopped due to an
91 error, any pending transactions are terminated and changes made
92 to the database are rolled back.
</p
>
94 <p
>The virtual machine also contains an operand stack of unlimited
95 depth. Many of the opcodes use operands from the stack. See the
96 individual opcode descriptions
for details.
</p
>
98 <p
>The virtual machine can have zero or more
cursors. Each cursor
99 is a pointer into a single table or index within the database.
100 There can be multiple
cursors pointing at the same index or table.
101 All
cursors operate independently
, even
cursors pointing to the same
103 The only way
for the virtual machine to interact with a database
104 file is through a cursor.
105 Instructions in the virtual
106 machine can create a new cursor
(Open
), read data from a cursor
107 (Column
), advance the cursor to the next
entry in the table
108 (Next
) or index
(NextIdx
), and many other operations.
109 All
cursors are automatically
110 closed when the virtual machine terminates.
</p
>
112 <p
>The virtual machine contains an arbitrary number of fixed
memory
113 locations with addresses beginning at zero and growing upward.
114 Each
memory location can hold an arbitrary
string. The
memory
115 cells are typically used to hold the result of a scalar SELECT
116 that is part of a larger expression.
</p
>
118 <p
>The virtual machine contains a single sorter.
119 The sorter is able to accumulate records
, sort those records
,
120 then play the records back in sorted order. The sorter is used
121 to implement the ORDER BY clause of a SELECT statement.
</p
>
123 <p
>The virtual machine contains a single
"List".
124 The
list stores a
list of integers. The
list is used to hold the
125 rowids
for records of a database table that needs to be modified.
126 The WHERE clause of an UPDATE or DELETE statement scans through
127 the table and writes the rowid of every record to be modified
128 into the
list. Then the
list is played back and the table is modified
129 in a separate step.
</p
>
131 <p
>The virtual machine can contain an arbitrary number of
"Sets".
132 Each
set holds an arbitrary number of strings. Sets are used to
133 implement the IN operator with a constant right-hand side.
</p
>
135 <p
>The virtual machine can
open a single external
file for reading.
136 This external
read file is used to implement the COPY command.
</p
>
138 <p
>Finally
, the virtual machine can have a single
set of aggregators.
139 An aggregator is a device used to implement the GROUP BY clause
140 of a SELECT. An aggregator has one or more slots that can hold
141 values being extracted by the select. The number of slots is the
142 same
for all aggregators and is defined by the AggReset operation.
143 At any point in
time a single aggregator is current or
"has focus".
144 There are operations to
read or write to
memory slots of the aggregator
145 in
focus. There are also operations to change the
focus aggregator
146 and to
scan through all aggregators.
</p
>
148 <h3
>Viewing Programs Generated By SQLite
</h3
>
150 <p
>Every SQL statement that SQLite interprets results in a program
151 for the virtual machine. But
if you precede the SQL statement with
152 the keyword
"EXPLAIN" the virtual machine will not execute the
153 program. Instead
, the instructions of the program will be returned
154 like a query result. This feature is useful
for debugging and
155 for learning how the virtual machine operates.
</p
>
157 <p
>You can use the
<b
>sqlite
</b
> command-line tool to see the
158 instructions generated by an SQL statement. The following is
162 puts {<blockquote
><tt
>}
163 regsub -all {&} [string trim
$body] {\&
;} body
164 regsub -all {>} $body {\>
;} body
165 regsub -all {<} $body {\<
;} body
166 regsub -all {\(\(\(} $body {<b
>} body
167 regsub -all {\)\)\)} $body {</b
>} body
168 regsub -all { } $body {\ 
;} body
169 regsub -all \n $body <br
>\n body
171 puts {</tt
></blockquote
>}
176 sqlite
> (((.explain
)))
177 sqlite
> (((explain delete from tbl1 where two
<20;)))
179 ---- ------------ ----- ----- ----------------------------------------
204 <p
>All you have to do is add the
"EXPLAIN" keyword to the front of the
205 SQL statement. But
if you use the
".explain" command to
<b
>sqlite
</b
>
206 first
, it will
set up the output mode to make the program more easily
209 <p
>If
<b
>sqlite
</b
> has been compiled without the
"-DNDEBUG=1" option
210 (that is
, with the NDEBUG preprocessor macro not defined
) then you
211 can put the SQLite virtual machine in a mode where it will
trace its
212 execution by writing messages to standard output. The non-standard
213 SQL
"PRAGMA" comments can be used to turn tracing on and off. To
214 turn tracing on
, enter
:
218 PRAGMA vdbe_trace
=on
;
222 You can turn tracing back off by entering a similar statement but
223 changing the value
"on" to
"off".
</p
>
228 puts "<p>There are currently [llength $OpcodeList] opcodes defined by
229 the virtual machine."
230 puts {All currently defined opcodes are described in the table below.
231 This table was generated automatically by scanning the
source code
232 from the
file <b
>vdbe.c
</b
>.
</p
>}
235 <p
><table cellspacing
="1" border
="1" cellpadding
="10">
236 <tr
><th
>Opcode
 
;Name
</th
><th
>Description
</th
></tr
>}
237 foreach op
[lsort -dictionary $OpcodeList] {
238 puts {<tr
><td valign
="top" align
="center">}
239 puts "<a name=\"$op\">$op</a>"
240 puts "<td>[string trim $Opcode($op:text)]</td></tr>"