Documentation Source Text

Artifact Content
Login

Artifact f36c05ea0bc86327482b88934a7ec4b813e6841f:


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

<h2>SQLite Virtual Machine Opcodes</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-z][A-Za-z]+)(.*)$} $txt \
             all pre op opx tail]} {
    append out $pre
    regsub {^OP_} $op {} key
    if {[info exists Opcode($key:text)]} {
      append out "<a href=\"#$key\">$op</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.  This document describes the operation of that
virtual machine.</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 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.  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.<p>

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

<p>You can use the [CLI | sqlite3.exe command-line interface (CLI)]
tool to see the
instructions generated by an SQL statement.  The following is
an 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" command in the [CLI],
it will set up the output mode to make the program more easily
viewable.</p>

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

<blockquote><pre>
PRAGMA vdbe_trace=on;
</pre></blockquote>

<p>
You can turn tracing back off by entering a similar statement but
changing the value "on" to "off".</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><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>