Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | :-) (CVS 108) |
|---|---|
| Downloads: | Tarball | ZIP archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA1: |
937c27b7e18505d0f8b85d2040db8d6a |
| User & Date: | drh 2000-06-26 12:02:51.000 |
Context
|
2000-07-28
| ||
| 14:32 | added the sqlite_busy_handler() interface (CVS 109) (check-in: 4fe8e51c24 user: drh tags: trunk) | |
|
2000-06-26
| ||
| 12:02 | :-) (CVS 108) (check-in: 937c27b7e1 user: drh tags: trunk) | |
|
2000-06-23
| ||
| 19:16 | Begin writing the VDBE tutorial (CVS 107) (check-in: 79ce59cf79 user: drh tags: trunk) | |
Changes
Changes to www/vdbe.tcl.
1 2 3 | # # Run this Tcl script to generate the vdbe.html file. # | | | 1 2 3 4 5 6 7 8 9 10 11 |
#
# Run this Tcl script to generate the vdbe.html file.
#
set rcsid {$Id: vdbe.tcl,v 1.3 2000/06/26 12:02:51 drh Exp $}
puts {<html>
<head>
<title>The Virtual Database Engine of SQLite</title>
</head>
<body bgcolor=white>
<h1 align=center>
|
| ︙ | ︙ | |||
21 22 23 24 25 26 27 |
}
puts {
<p>If you want to know how the SQLite library works internally,
you need to begin with a solid understanding of the Virtual Database
Engine or VDBE. The VDBE occurs right in the middle of the
processing stream (see the <a href="arch.html">architecture diagram</a>)
| | | | | | | | | 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 |
}
puts {
<p>If you want to know how the SQLite library works internally,
you need to begin with a solid understanding of the Virtual Database
Engine or VDBE. The VDBE occurs right in the middle of the
processing stream (see the <a href="arch.html">architecture diagram</a>)
and so it seems to touch most parts of the library. Even
parts of the code that do not directly interact with the VDBE
are usually in a supporting role. The VDBE really is the heart of
SQLite.</p>
<p>This article is a brief introduction to how the VDBE
works and in particular how the various VDBE instructions
(documented <a href="opcode.html">here</a>) work together
to do useful things with the database. The style is tutorial,
beginning with simple tasks and working toward solving more
complex problems. Along the way we will visit most
submodules in the SQLite library. After completeing this tutorial,
you should have a pretty good understanding of how SQLite works
and will be ready to begin studying the actual source code.</p>
<h2>Preliminaries</h2>
<p>The VDBE implements a virtual computer that runs a program in
its virtual machine language. The goal of each program is to
interrogate or change the database. Toward this end, the machine
language that the VDBE implements is specifically designed to
search, read, and modify databases.</p>
<p>Each instruction of the VDBE language contains an opcode and
three operands labeled P1, P2, and P3. Operand P1 is an arbitrary
integer. P2 is a non-negative integer. P3 is a null-terminated
string, or possibly just a null pointer. Only a few VDBE
instructions use all three operands. Many instructions use only
one or two operands. A significant number of instructions use
no operands at all but instead take their data and storing their results
on the execution stack. The details of what each instruction
does and which operands it uses are described in the separate
<a href="opcode.html">opcode description</a> document.</p>
<p>A VDBE program begins
execution on instruction 0 and continues with successive instructions
until it either (1) encounters a fatal error, (2) executes a
|
| ︙ | ︙ | |||
78 79 80 81 82 83 84 | <h2>Inserting Records Into The Database</h2> <p>We begin with a problem that can be solved using a VDBE program that is only a few instructions long. Suppose we have an SQL table that was created like this:</p> <blockquote><pre> | | | | | 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 |
<h2>Inserting Records Into The Database</h2>
<p>We begin with a problem that can be solved using a VDBE program
that is only a few instructions long. Suppose we have an SQL
table that was created like this:</p>
<blockquote><pre>
CREATE TABLE examp(one text, two int);
</pre></blockquote>
<p>In words, we have a database table named "examp" that has two
columns of data named "one" and "two". Now suppose we want to insert a single
record into this table. Like this:</p>
<blockquote><pre>
INSERT INTO examp VALUES('Hello, World!',99);
</pre></blockquote>
<p>We can see the VDBE program that SQLite uses to implement this
INSERT using the <b>sqlite</b> command-line utility. First start
up <b>sqlite</b> on a new, empty database, then create the table.
Finally, enter the INSERT statement shown above, but precede the
INSERT with the special keyword "EXPLAIN". The EXPLAIN keyword
|
| ︙ | ︙ | |||
192 193 194 195 196 197 198 |
onto the stack, so that after the 5th instruction executes,
the stack looks like this:</p>
}
stack {A data record holding "Hello, World!" and 99} \
{A random integer key}
| | | | | 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 |
onto the stack, so that after the 5th instruction executes,
the stack looks like this:</p>
}
stack {A data record holding "Hello, World!" and 99} \
{A random integer key}
puts {<p>The last instruction pops the top two elements from the stack
and uses them as data and key to make a new entry in database
database file pointed to by cursor P1. This instruction is where
the insert actually occurs.</p>
<p>After the last instruction executes, the program counter
advances to one past the last instruction, which causes the
VDBE to halt. When the VDBE halts, it automatically closes
all open cursors, frees any elements left on the stack,
and releases any other resources we may have allocated.
In this case, the only cleanup necessary is to close the
cursor to the "examp" file.</p>
<a name="trace">
<h2>Tracing VDBE Program Execution</h2>
<p>If the SQLite library is compiled without the NDEBUG
preprocessor macro, then
there is a special SQL comment that will cause the
the VDBE to traces the execution of programs.
Though this features was originally intended for testing
and debugging, it might also be useful in learning about
how the VDBE operates.
Use the "<tt>--vdbe-trace-on--</tt>" comment to
turn tracing on and "<tt>--vdbe-trace-off--</tt>" to turn tracing
|
| ︙ | ︙ | |||
241 242 243 244 245 246 247 |
puts {
<p>With tracing mode on, the VDBE prints each instruction prior
to executing it. After the instruction is executed, the top few
entries in the stack are displayed. The stack display is omitted
if the stack is empty.</p>
| | | 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 |
puts {
<p>With tracing mode on, the VDBE prints each instruction prior
to executing it. After the instruction is executed, the top few
entries in the stack are displayed. The stack display is omitted
if the stack is empty.</p>
<p>On the stack display, most entries are shown with a prefix
that tells the datatype of that stack entry. Integers begin
with "<tt>i:</tt>". Floating point values begin with "<tt>r:</tt>".
(The "r" stands for "real-number".) Strings begin with either
"<tt>s:</tt>" or "<tt>z:</tt>". The difference between s: and
z: strings is that z: strings are stored in memory obtained
from <b>malloc()</b>. This doesn't make any difference to you,
the observer, but it is vitally important to the VDBE since the
|
| ︙ | ︙ | |||
522 523 524 525 526 527 528 |
}
puts {
<p>Here is what the program must do. First it has to locate all of
the records in the "examp" database that are to be deleted. This is
done using a loop very much like the loop used in the SELECT examples
above. Once all records have been located, then we can go back through
| | | | | 522 523 524 525 526 527 528 529 530 531 532 533 534 535 536 537 538 539 540 541 542 543 544 545 546 547 |
}
puts {
<p>Here is what the program must do. First it has to locate all of
the records in the "examp" database that are to be deleted. This is
done using a loop very much like the loop used in the SELECT examples
above. Once all records have been located, then we can go back through
and delete them one by one. Note that we cannot delete each record
as soon as we find it. We have to locate all records first, then
go back and delete them. This is because the GDBM database
backend might change the scan order after a delete operation.
And if the scan
order changes in the middle of the scan, some records might be
visited more than once and other records might not be visited at all.</p>
<p>So the implemention of DELETE is really in two loops. The
first loop (instructions 2 through 8 in the example) locates the records that
are to be deleted and the second loop (instructions 12 through 14)
does the actual deleting.</p>
<p>The very first instruction in the program, the ListOpen instruction,
creates a new List object in which we can store the keys of the records
that are to be deleted. The P1 operand serves as a handle to the
list. As with cursors, you can open as many lists as you like
(though in practice we never need more than one at a time.) Each list
has a handle specified by P1 which is a non-negative integer. The
|
| ︙ | ︙ | |||
577 578 579 580 581 582 583 | <p>At the end of the first loop, the cursor is closed at instruction 9, and the list is rewound back to the beginning at instruction 10. The Open instruction at 11 reopens the same database file, but for writing this time. The loop that does the actual deleting of records is on instructions 12, 13, and 14.</p> | | | | 577 578 579 580 581 582 583 584 585 586 587 588 589 590 591 592 593 594 595 596 597 598 599 600 601 602 603 604 605 606 607 608 | <p>At the end of the first loop, the cursor is closed at instruction 9, and the list is rewound back to the beginning at instruction 10. The Open instruction at 11 reopens the same database file, but for writing this time. The loop that does the actual deleting of records is on instructions 12, 13, and 14.</p> <p>The ListRead instruction at 12 reads a single integer key from the list and pushes that key onto the stack. If there are no more keys, nothing gets pushed onto the stack but instead a jump is made to instruction 15. Notice the similarity between the ListRead and Next instructions. Both operations work according to this rule:</p> <blockquote> Push the next "thing" onto the stack and fall through. Or if there is no next "thing" to push, jump immediately to P2. </blockquote> <p>The only difference between Next and ListRead is their idea of a "thing". The "things" for the Next instruction are records in a database file. "Things" for ListRead are integer keys in a list. Later on, we will see other looping instructions (NextIdx and SortNext) that operate using the same principle.</p> <p>The Delete instruction at address 13 pops an integer key from the stack (the key was put there by the preceding ListRead instruction) and deletes the record of cursor P1 that has that key. If there is no record in the database with the given key, then Delete is a no-op.</p> |
| ︙ | ︙ | |||
620 621 622 623 624 625 626 |
</p>
<blockquote><pre>
UPDATE examp SET one= '(' || one || ')' WHERE two < 50;
</pre></blockquote>
<p>Instead of deleting records where the "two" column is less than
| | | 620 621 622 623 624 625 626 627 628 629 630 631 632 633 634 |
</p>
<blockquote><pre>
UPDATE examp SET one= '(' || one || ')' WHERE two < 50;
</pre></blockquote>
<p>Instead of deleting records where the "two" column is less than
50, this statement just puts the "one" column in parentheses
The VDBE program to implement this statement follows:</p>
}
Code {
addr opcode p1 p2 p3
---- ------------ ----- ----- ----------------------------------------
0 ListOpen 0 0
|
| ︙ | ︙ |