Documentation Source Text

<title>The C language interface to SQLite Version 2</title>

<p>
<center><table border="1" cellpadding="10" width="75%">
<tr><td bgcolor="#ffffbb">
<b>Editorial Note:</b>
This document describes SQLite version 2, which was deprecated and
replaced by SQLite3 in 2004.
This document is retained as part of the historical record of SQLite.
Modern programmers should refer to
more up-to-date documentation on SQLite is available elsewhere
on this website.
</table></center>
<h2>The C language interface to SQLite Version 2</h2>

<p>The SQLite library is designed to be very easy to use from
a C or C++ program.  This document gives an overview of the C/C++
programming interface.</p>

<h3>1.0 The Core API</h3>

<title>The SQLite Bytecode Engine</title>
<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]

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
    }
    set txt $tail
  }
  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,
which evaluates the bytecode and thereby does the work specified by the
original SQL statement.  The bytecode engine is the heart of SQLite
and so a good understanding of the bytecode engine is essential to
understanding how SQLite operations internally.


<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 they 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
registers.  A single register can hold a variety of objects:
<ul>
<li> A NULL value
<li> A signed 64-bit integer
<li> An IEEE double-precision (64-bit) floating point number
<li> An arbitrary length strings
<li> An arbitrary length BLOB
<li> A RowSet object (used internally)
<li> A Frame object (used internally)
</ul>

<p>A register can also be "Undefined" meaning that it holds no value
at all.  Undefined is different from NULL.  Depending on compile-time
options, an attempt to read an undefined register will usually cause
a run-time error.  If the code generator ([sqlite3_prepare_v2()])
ever generates a [prepared statement] that reads an Undefined register,
that is a bug in the code generator.

<p>
Registers are numbered beginning with 0.
Most opcodes refer to at least one register.

<p>The number of registers in a single prepared statement is fixed
at compile-time.  The content of all registers is cleared when
a prepared statement is [sqlite3_reset()|reset] or
[sqlite3_finalize()|finalized].

<h2>B-Tree Cursors</h2>

<tcl>
LinkOpcodeNames {
<p>The a running prepared statement can have
zero or more open cursors.  Each cursor is identified by a
small integer, which is usually the P1 parameter to the opcode
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>

<p>Every SQL statement that SQLite interprets results in a program
for the virtual machine.  But if the SQL statement begins with
the keyword [EXPLAIN] the virtual machine will not execute the
program.  Instead, the instructions of the program will be returned,
one instruction per row,
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 delete from tbl1 where two<20;)))
addr  opcode         p1    p2    p3    p4             p5  comment      
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     12    0                    00  Start at 12  

1     Null           0     1     0                    00  r[1]=NULL    
2     OpenWrite      0     2     0     3              00  root=2 iDb=0; tbl1


3     Rewind         0     10    0                    00               
4       Column         0     1     2                    00  r[2]=tbl1.two
5       Ge             3     9     2     (BINARY)       51  if r[2]>=r[3] goto 9

6       Rowid          0     4     0                    00  r[4]=rowid   
7       Once           0     8     0                    00               







8       Delete         0     1     0     tbl1           02               
9     Next           0     4     0                    01               
10    Noop           0     0     0                    00               
11    Halt           0     0     0                    00               
12    Transaction    0     1     1     0              01  usesStmtJournal=0

13    TableLock      0     2     1     tbl1           00  iDb=0 root=2 write=1
14    Integer        20    3     0                    00  r[3]=20      
15    Goto           0     1     0                    00               
}
</tcl>

<p>Any application can run an [EXPLAIN] query to get output similar to 

what is shown above.
However, indentation to show the loop structure is not generated
by the SQLite core.  The [command-line shell] contains extra logic
for indenting loops.
Also, the "comment" column in the [EXPLAIN] output


is only provided if SQLite is compiled with the
[-DSQLITE_ENABLE_EXPLAIN_COMMENTS] options.

<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>

<h1>The Opcodes</h1>

<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>.\n"
} 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>
  <tr><th>Opcode Name</th><th>Description</th></tr>
}

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

<h1 align="center">The Next Generation Query Planner</h1>

<h2>1.0 Introduction</h2>

<p>
The task of the "query planner" is to figure
out the best algorithm or "query plan" to accomplish an SQL statement.
Beginning with SQLite [version 3.8.0] (2013-08-26), 
the query planner component has been
rewritten so that it runs faster and generates better plans.  The
rewrite is called the "next generation query planner" or "NGQP".
</p>

<p>This article overviews the importance of query planning, describes some
of the problems inherent to query planning, and outlines how the NGQP
solves those problems.</p>