Documentation Source Text

<tcl>hd_keywords {virtual machine instructions} {VDBE} {virtual machine} \
         {opcodes} {bytecode engine} {bytecodes} {bytecode}</tcl>

<table_of_contents>

<h1>Executive Summary</h1>

<p>SQLite operates by translating SQL statements into bytecode and
then running that bytecode in a virtual machine.  This document provides
a tutorial overview of how the bytecode engine works.

<p>This document describes SQLite internals.  The information provided
here is not needed for routine application development using SQLite.
This document is intended for people who want to delve more deeply into
the internal operation of SQLite.






<tcl>
set uuid {}
catch {
  exec fossil sha1sum $::SRC/src/vdbe.c 
} uuid
set uuid [lindex $uuid 0]
................................................................................
    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 {^(.*?)\y((OP_)?[A-Z][A-Za-z][A-Za-z0-9]+)\y(.*)$} $txt \
             all pre op opx tail]} {
    hd_resolve $pre
    regsub {^OP_} $op {} key
    if {[info exists Opcode($key:text)]} {
      hd_puts "<a href=\"opcode.html#$key\">$key</a>"
    } else {
      hd_puts $op
................................................................................
  }
  hd_resolve $txt
}
</tcl>

<h1>Introduction</h1>

<p>SQLite operates by translating each SQL statement into bytecode.

A [prepared statement] in SQLite is mostly just the bytecode needed to
implement the corresponding SQL.  The [sqlite3_prepare_v2()] interface
is a compiler that translates SQL into bytecode.
The [sqlite3_step()] interface passes that bytecode to a virtual machine.
The virtual machine evaluates the bytecode and thereby does the work
specified by the original SQL statement.

The virtual machine is the heart of SQLite.
Programmers who want to understand how SQLite operates internally
must be familiar with the bytecode engine.

<p>Historically, the bytecode engine in SQLite is called the
"Virtual DataBase Engine" or "VDBE".  This article uses the terms
"bytecode engine" and "VDBE" and "virtual machine" interchangeably.


<p>
This article also uses the terms "bytecode program" and
"prepared statement" interchangeably, as theyalso mean the same thing.

<h2>VDBE Source Code</h2>

<p>The source code to the bytecode engine is in the 
[http://www.sqlite.org/src/finfo?name=src/vdbe.c | vdbe.c] source
file.  All of the [opcode definitions] in this document are
contained in comments in the source file.  In fact, the opcode table
in this document is 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.  The 
source code comments are the canonical source of information
about the bytecode engine.  When in doubt, refer to the source code.</p>

<p>In addition to the primary vdbe.c source code file, there are 
other helper code files in the source tree, all of whose names
begin with "vdbe" - short for "Virtual DataBase Engine".

















<h2>Instruction Format</h2>



<p>A bytecoded program in SQLite consists of one or more instructions.
Each instruction has 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 bytecode engine 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 until there is an error.
When the bytecode engine 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>

<tcl>
LinkOpcodeNames {
<p>The [OP_ResultRow] opcode causes the
bytecode engine to pause, and the corresponding [sqlite3_step()]
call to return [SQLITE_ROW].  Before invoking
[OP_ResultRow], the bytecoded program will
have loaded the results for a single row of a query into a series
of registers.  C-language APIs such as [sqlite3_column_int()]
or [sqlite3_column_text()] extract the query results from those
registers.  The bytecode engine resumes with the next instruction
after the [OP_ResultRow] one the next call
to [sqlite3_step()].
}
</tcl>

<h2>Registers</h2>

<p>Every bytecode program has a fixed (but potentially large) number of
................................................................................
that uses the cursor.
There can be multiple cursors open on 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 
(ex: [OP_OpenRead] or [OP_OpenWrite]),
read data from a cursor ([Column]),
advance the cursor to the next entry in the table
(ex: [OP_Next] or [OP_Prev]), and so forth
All cursors are automatically
closed when the prepared statement is [sqlite3_reset()|reset] or
[sqlite3_finalize()|finalized].
}
</tcl>

<h1>Viewing The Bytecode</h1>
................................................................................
} else {
  hd_puts "<a href=\"http://www.sqlite.org/src/artifact/$uuid\">vdbe.c</a>.\n"
}
</tcl>

<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
can and frequently do change from one release of SQLite to the next.


<tcl>



hd_fragment codes {list of current bytecodes} {opcode definitions}
 
hd_puts {
  </div>
  <style>.optab td {vertical-align:top; padding: 1ex 1ex;}</style>
  <div class="optab">
  <blockquote><table cellspacing=0 border=1 cellpaddin>

<tcl>hd_keywords {virtual machine instructions} {VDBE} {virtual machine} \
         {opcodes} {bytecode engine} {bytecodes} {bytecode}</tcl>

<table_of_contents>

<h1>Executive Summary</h1>

<p>SQLite works by translating SQL statements into bytecode and
then running that bytecode in a virtual machine.  This document
describes how how the bytecode engine works.

<p>This document describes SQLite internals.  The information provided
here is not needed for routine application development using SQLite.
This document is intended for people who want to delve more deeply into
the internal operation of SQLite.

<p>The bytecode engine is <u>not</u> an API of SQLite.  Details
about the bytecode engine change from one release of SQLite to the next.
Applications that use SQLite should not depend on any of the details
found in this document.

<tcl>
set uuid {}
catch {
  exec fossil sha1sum $::SRC/src/vdbe.c 
} uuid
set uuid [lindex $uuid 0]
................................................................................
    regsub -all {<} $line {\&lt;} line
    append Opcode($current_op:text) \n$pstart$line
    set pstart {}
    set pend "</p>\n"
  }
}
unset file
set fd [open $::SRC/VERSION r]
set SQLITE_VERSION [string trim [read $fd]]
close $fd
set fd [open $::SRC/manifest.uuid r]
set SQLITE_SHA1_VERSION [string trim [read $fd]]
set SQLITE_HASH [string range $SQLITE_SHA1_VERSION 0 12]
close $fd
set fd [open $::SRC/manifest r]
set file [read $fd]
close $fd
set SQLITE_CKIN_DATE {unknown}
regexp {\nD (\d\d\d\d-\d\d-\d\d)} $file all SQLITE_CKIN_DATE
unset file

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

<h1>Introduction</h1>

<p>SQLite works by translating each SQL statement into bytecode and
then running that bytecode.
A [prepared statement] in SQLite is mostly just the bytecode needed to
implement the corresponding SQL.  The [sqlite3_prepare_v2()] interface
is a compiler that translates SQL into bytecode.
The [sqlite3_step()] interface is the virtual machine that runs the
bytecode contained within the [prepared statement].


<p>The bytecode virtual machine is the heart of SQLite.
Programmers who want to understand how SQLite operates internally
must be familiar with the bytecode engine.

<p>Historically, the bytecode engine in SQLite is called the
"Virtual DataBase Engine" or "VDBE".  This article uses the terms
"bytecode engine", "VDBE", "virtual machine", and "bytecode virtual
machine interchangeably, as they all mean the same thing.

<p>
This article also uses the terms "bytecode program" and
"prepared statement" interchangeably, as they are mostly the same thing.

<h2>VDBE Source Code</h2>

<p>The source code to the bytecode engine is in the 
[http://www.sqlite.org/src/finfo?name=src/vdbe.c | vdbe.c] source
file.  The [opcode definitions] in this document are derived
from comments in that source file. The 



source code comments are the canonical source of information
about the bytecode engine.  When in doubt, refer to the source code.</p>

<p>In addition to the primary vdbe.c source code file, there are 
other helper code files in the source tree, all of whose names
begin with "vdbe" - short for "Virtual DataBase Engine".

<p>Remember that the names and meanings of opcodes often change from
one release of SQLite to the next.  So if you are studying the [EXPLAIN]
output from SQLite, you should reference the version of this document
(or the vdbe.c source code)
that corresponds to the version of SQLite that ran the [EXPLAIN].
Otherwise, the documentation of the opcodes and what the opcodes actually
do may not align.
The version of this document you are currently reading was prepared
using SQLite
<tcl>
hd_puts " version $SQLITE_VERSION check-in\n"
set url https://www.sqlite.org/src/timeline?c=$SQLITE_HASH
hd_puts "<a href='$url'>$SQLITE_HASH</a> dated $SQLITE_CKIN_DATE.\n\n"
</tcl>


<h2>Instruction Format</h2>

<tcl>
LinkOpcodeNames {
<p>A bytecoded program in SQLite consists of one or more instructions.
Each instruction has an opcode and
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.  For instructions that operate on b-tree cursors,
the P1 operand is usually the cursor number.
For jump instructions, P2 is usually the jump destination.
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.  Bits of the P5 flag can sometimes affect
the opcode in subtle ways.  For example, if the 
SQLITE_NULLEQ (0x80) bit of the P5 operand
is set on the OP_Eq opcode, then the NULL values compare
equal to one another.  Otherwise NULL values compare different
from one another.
}
</tcl>

<p>Some opcode use all five operands.  Some opcodes use
one or two.  Some opcodes use none of the operands.<p>

<p>The bytecode engine 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 until there is an error.
When the bytecode engine 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>

<tcl>
LinkOpcodeNames {
<p>The OP_ResultRow opcode causes the
bytecode engine to pause, and the corresponding [sqlite3_step()]
call to return [SQLITE_ROW].  Before invoking
OP_ResultRow, the bytecoded program will
have loaded the results for a single row of a query into a series
of registers.  C-language APIs such as [sqlite3_column_int()]
or [sqlite3_column_text()] extract the query results from those
registers.  The bytecode engine resumes with the next instruction
after the OP_ResultRow one the next call
to [sqlite3_step()].
}
</tcl>

<h2>Registers</h2>

<p>Every bytecode program has a fixed (but potentially large) number of
................................................................................
that uses the cursor.
There can be multiple cursors open on 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 
(ex: OP_OpenRead or OP_OpenWrite),
read data from a cursor (OP_Column),
advance the cursor to the next entry in the table
(ex: OP_Next or OP_Prev), and so forth
All cursors are automatically
closed when the prepared statement is [sqlite3_reset()|reset] or
[sqlite3_finalize()|finalized].
}
</tcl>

<h1>Viewing The Bytecode</h1>
................................................................................
} else {
  hd_puts "<a href=\"http://www.sqlite.org/src/artifact/$uuid\">vdbe.c</a>.\n"
}
</tcl>

<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
change from one release of SQLite to the next.

The opcodes shown in the table below are valid for SQLite
<tcl>
hd_puts " version $SQLITE_VERSION check-in\n"
hd_puts "<a href='$url'>$SQLITE_HASH</a> dated $SQLITE_CKIN_DATE.\n\n"

hd_fragment codes {list of current bytecodes} {opcode definitions}
 
hd_puts {
  </div>
  <style>.optab td {vertical-align:top; padding: 1ex 1ex;}</style>
  <div class="optab">
  <blockquote><table cellspacing=0 border=1 cellpaddin>