Documentation Source Text

Check-in [dd15111c6a]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:New improvements to the opcode documentation.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: dd15111c6a05d1b8170b422284166e72a4f438ed
User & Date: drh 2016-08-29 18:40:49.907
Context
2016-08-29
18:57
Include the TCL include path when building the searchdb target. (check-in: 8e290154f5 user: drh tags: trunk)
18:40
New improvements to the opcode documentation. (check-in: dd15111c6a user: drh tags: trunk)
18:38
Merge "experimental" with this branch. Adds more links to the front page, fts5 search, show/hide links for tables of contents and some other things. (check-in: e4c2e46a8c user: dan tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to pages/opcode.in.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16





17
18
19
20
21
22
23
<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]








|
|
|





>
>
>
>
>







1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
<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 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]
60
61
62
63
64
65
66













67
68
69
70
71
72


73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90

91
92
93
94
95
96
97
98
99
100
101
102
103

104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124















125
126
127


128
129
130
131
132
133
134
135
136
137
138
139
140








141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
    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
    }
    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.
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







>
>
>
>
>
>
>
>
>
>
>
>
>






>
>
|
















|
>



|
|
|
|





|
>



|





|
|
<
<
<







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>



>
>


|

|
|
|





|
>
>
>
>
>
>
>
>
|
|













|


|




|







65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136



137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
    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
    }
    set txt $tail
  }
  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
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
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>







|
|

|







250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
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>
305
306
307
308
309
310
311
312
313
314



315
316
317
318
319
320
321
} 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>







|
|

>
>
>







349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
} 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>