<title>SQLite Virtual Machine Opcodes</title>
<tcl>hd_keywords {virtual machine instructions} {VDBE} {virtual machine} \
         {opcodes}</tcl>

<h2>The SQLite Virtual Machine</h2>

<tcl>
set uuid {}
catch {
  exec fossil sha1sum $::SRC/src/vdbe.c 
} uuid
set uuid [lindex $uuid 0]
set fd [open $::SRC/src/vdbe.c r]
set file [read $fd]
close $fd
set current_op {}
unset -nocomplain Opcode
unset -nocomplain OpcodeList
foreach line [split $file \n] {
  set line [string trim $line]
  if {[string index $line 1]!="*"} {
    set current_op {}
    continue
  }
  if {[regexp {^\*\* Synopsis:} $line]} continue
  if {[regexp {^/\* Opcode: } $line]} {
    set current_op [lindex $line 2]
    set txt [lrange $line 3 end]
    regsub -all {>} $txt {\&gt;} txt
    regsub -all {<} $txt {\&lt;} txt
    set Opcode($current_op:args) $txt
    lappend OpcodeList $current_op
    set pend {}
    set pstart {}
    continue
  }
  if {$current_op==""} continue
  if {[regexp {^\*/} $line]} {
    set current_op {}
    continue
  }
  set line [string trim [string range $line 3 end]]
  if {$line==""} {
    append Opcode($current_op:text) $pend
    set pend {}
    set pstart {<p>}
  } else {
    regsub -all {>} $line {\&gt;} line
    regsub -all {<} $line {\&lt;} line
    append Opcode($current_op:text) \n$pstart$line
    set pstart {}
    set pend "</p>\n"
  }
}
unset file

# Scan $txt and replace every opcode name with a link to its documentation
#
proc LinkOpcodeNames {txt} {
  global Opcode
  set out {}
  while {[regexp {^(.*?\s)((OP_)?[A-Z][A-Za-z][A-Za-z0-9]+)(.*)$} $txt \
             all pre op opx tail] ||
         [regexp {^(.*?\s)(OP_[A-Z][a-z])([^a-zA-Z_0-9].*)$} $txt \
             all pre op tail]} {
    append out $pre
    regsub {^OP_} $op {} key
    if {[info exists Opcode($key:text)]} {
      append out "<a href=\"#$key\">$key</a>"
    } else {
      append out $op
    }
    set txt $tail
  }
  append out $txt
  return $out
}

</tcl>

<h3>Introduction</h3>

<p>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.  That virtual machine is sometimes called the
"VDBE" or "Virtual DataBase Engine".
This document describes the operation of the VDBE.</p>

<p>This document is intended as a reference, not a tutorial.
A separate <a href="vdbe.html">Virtual Machine Tutorial</a> 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 tutorial document has not been updated.
But the
basic concepts behind the virtual machine still apply.
</p>

<p>The source code to the virtual machine is in the 
[http://www.sqlite.org/src/finfo?name=src/vdbe.c | 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 
[http://www.sqlite.org/src/finfo?name=src/vdbe.c | 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.</p>

<p>Each instruction in the virtual machine consists of an opcode and
up to five operands named P1, P2  P3, P4, and P5.  The P1, P2, and P3 
operands are 32-bit signed integers.  These operands often refer to 
registers but can also be used for other purposes.  The P1 operand is
usually the cursor number for opcodes that operate on cursors.
P2 is usually the jump destination jump instructions.
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.<p>

<p>The virtual machine begins execution on instruction number 0.
Execution continues until a Halt instruction is seen, or until
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.</p>

<p>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.</p>

<p>The virtual machine contains an arbitrary number of registers
with addresses beginning at one and growing upward.
Each register can hold a single SQL value (a string, a BLOB, a signed 64-bit
integer, a 64-bit floating point number, or a NULL).  A register might
also hold objects used internally by SQLite, such as a RowSet or Frame.
</p>

<h3>Viewing Programs Generated By SQLite</h3>

<p>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.  For example:
</p>

<tcl>
proc Code {body} {
  hd_puts {<blockquote><pre>}
  regsub -all {&} [string trim $body] {\&amp;} body
  regsub -all {>} $body {\&gt;} body
  regsub -all {<} $body {\&lt;} body
  regsub -all {\(\(\(} $body {<b>} body
  regsub -all {\)\)\)} $body {</b>} body
  regsub -all { } $body {\&nbsp;} body
  hd_puts $body
  hd_puts {</pre></blockquote>}
}

Code {
$ (((sqlite3 ex1.db)))
sqlite> (((.explain)))
sqlite> (((explain delete from tbl1 where two<20;)))
addr  opcode         p1    p2    p3    p4             p5  comment      
----  -------------  ----  ----  ----  -------------  --  -------------
0     Trace          0     0     0                    00               
1     Goto           0     23    0                    00               
2     Null           0     1     0                    00  r[1]=NULL    
3     OpenRead       0     2     0     2              00  root=2 iDb=0; tbl1
4     Explain        0     0     0     SCAN TABLE tbl1  00               
5     Noop           0     0     0                    00  Begin WHERE-loop0: tbl1
6     Rewind         0     14    0                    00               
7       Column         0     1     2                    00  r[2]=tbl1.two
8       Ge             3     13    2     (BINARY)       6a  if r[3]>=r[2] goto 13
9       Noop           0     0     0                    00  Begin WHERE-core
10      Rowid          0     4     0                    00  r[4]=rowid   
11      RowSetAdd      1     4     0                    00  rowset(1)=r[4]
12      Noop           0     0     0                    00  End WHERE-core
13    Next           0     7     0                    01               
14    Noop           0     0     0                    00  End WHERE-loop0: tbl1
15    Close          0     0     0                    00               
16    OpenWrite      0     2     0     3              00  root=2 iDb=0; tbl1
17      RowSetRead     1     21    4                    00  r[4]=rowset(1)
18      NotExists      0     20    4     1              00  intkey=r[4]  
19      Delete         0     1     0     tbl1           00               
20    Goto           0     17    0                    00               
21    Close          0     0     0                    00               
22    Halt           0     0     0                    00               
23    Transaction    0     1     0                    00               
24    VerifyCookie   0     1     0                    00               
25    TableLock      0     2     1     tbl1           00  iDb=0 root=2 write=1
26    Integer        20    3     0                    00  r[3]=20      
27    Goto           0     2     0                    00               
}
</tcl>

<p>All you have to do is add the [EXPLAIN] keyword to the front of the
SQL statement.  But if you use the "[explain dot-command|.explain]"
command in the [CLI],
it will set up the output mode to make the VDBE code easier for 
humans to read.</p>

<p>When SQLite is compiled with the [SQLITE_DEBUG] compile-time option,
extra [PRAGMA] commands are available that are useful for debugging and
for exploring the operation of the VDBE.  For example the [vdbe_trace]
pragma can be enabled to cause a disassembly of each VDBE opcode to be
printed on standard output as the opcode is executed.  These debugging
pragmas include:
<ul>
<li> [PRAGMA parser_trace]
<li> [PRAGMA vdbe_addoptrace]
<li> [PRAGMA vdbe_debug]
<li> [PRAGMA vdbe_listing]
<li> [PRAGMA vdbe_trace]
</ul>
</p>

<h3>The Opcodes</h3>

<p>There are currently <tcl>hd_puts [llength $OpcodeList]</tcl>
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
<tcl>
  if {$uuid==""} {
    hd_puts <b>vdbe.c</b>.
  } else {
    hd_puts "<a href=\"http://www.sqlite.org/src/artifact/$uuid\">vdbe.c</a>."
  }
</tcl></p>

<p>Remember: The VDBE opcodes are <u>not</u> part of the interface 
definition for SQLite.  The number of opcodes and their names and meanings
are subject to change from one release of SQLite to the next.
 

<p><table cellspacing="1" border="1" cellpadding="10">
<tr><th>Opcode&nbsp;Name</th><th>Description</th></tr>

<tcl>
  foreach op [lsort -dictionary $OpcodeList] {
    hd_puts {<tr><td valign="top" align="center">}
    hd_puts "\n<a name=\"$op\"></a><p>$op</p>\n"
    regsub -all {\[(P[0-9+]+)\]} $Opcode($op:text) {\&#91;\1\&#93} txt
    hd_resolve "<td>[string trim [LinkOpcodeNames $txt]]</td></tr>\n"
  }
</tcl>
</table></p>
