vdbe.tcl

sqlite嵌入式数据库源码

Rewind instruction something to jump to when it is done.  All of
this is just like in the first query demonstration.</p>

<p>The Callback instruction in this example has to generate
data for three result columns instead of two, but is otherwise
the same as in the first query.  When the Callback instruction
is invoked, the left-most column of the result should be
the lowest in the stack and the right-most result column should
be the top of the stack.  We can see the stack being set up 
this way at addresses 11 through 15.  The Column instructions at
11 and 12 push the values for the first two columns in the result.
The two Column instructions at 13 and 14 pull in the values needed
to compute the third result column and the Concat instruction at
15 joins them together into a single entry on the stack.</p>

<p>The only thing that is really new about the current example
is the WHERE clause which is implemented by instructions at
addresses 7 through 10.  Instructions at address 7 and 8 push
onto the stack the value of the "one" column from the table
and the literal string "H%".  
The <a href="opcode.html#Function">Function</a> instruction at address 9 
pops these two values from the stack and pushes the result of the LIKE() 
function back onto the stack.  
The <a href="opcode.html#IfNot">IfNot</a> instruction pops the top stack 
value and causes an immediate jump forward to the Next instruction if the 
top value was false (<em>not</em> not like the literal string "H%").  
Taking this jump effectively skips the callback, which is the whole point
of the WHERE clause.  If the result
of the comparison is true, the jump is not taken and control
falls through to the Callback instruction below.</p>

<p>Notice how the LIKE operator is implemented.  It is a user-defined 
function in SQLite, so the address of its function definition is 
specified in P3.  The operand P1 is the number of function arguments for 
it to take from the stack.  In this case the LIKE() function takes 2 
arguments.  The arguments are taken off the stack in reverse order 
(right-to-left), so the pattern to match is the top stack element, and 
the next element is the data to compare.  The return value is pushed 
onto the stack.</p>


<a name="pattern1">
<h2>A Template For SELECT Programs</h2>

<p>The first two query examples illustrate a kind of template that
every SELECT program will follow.  Basically, we have:</p>

<p>
<ol>
<li>Initialize the <b>azColumnName[]</b> array for the callback.</li>
<li>Open a cursor into the table to be queried.</li>
<li>For each record in the table, do:
    <ol type="a">
    <li>If the WHERE clause evaluates to FALSE, then skip the steps that
        follow and continue to the next record.</li>
    <li>Compute all columns for the current row of the result.</li>
    <li>Invoke the callback function for the current row of the result.</li>
    </ol>
<li>Close the cursor.</li>
</ol>
</p>

<p>This template will be expanded considerably as we consider
additional complications such as joins, compound selects, using
indices to speed the search, sorting, and aggregate functions
with and without GROUP BY and HAVING clauses.
But the same basic ideas will continue to apply.</p>

<h2>UPDATE And DELETE Statements</h2>

<p>The UPDATE and DELETE statements are coded using a template
that is very similar to the SELECT statement template.  The main
difference, of course, is that the end action is to modify the
database rather than invoke a callback function.  Because it modifies 
the database it will also use transactions.  Let's begin
by looking at a DELETE statement:</p>

<blockquote><pre>
DELETE FROM examp WHERE two<50;
</pre></blockquote>

<p>This DELETE statement will remove every record from the "examp"
table where the "two" column is less than 50.
The code generated to do this is as follows:</p>
}

Code {
addr  opcode        p1     p2     p3                                      
----  ------------  -----  -----  -----------------------------------
0     Transaction   1      0
1     Transaction   0      0
2     VerifyCookie  0      178
3     Integer       0      0
4     OpenRead      0      3      examp
5     Rewind        0      12
6     Column        0      1
7     Integer       50     0      50
8     Ge            1      11
9     Recno         0      0
10    ListWrite     0      0
11    Next          0      6
12    Close         0      0
13    ListRewind    0      0
14    Integer       0      0
15    OpenWrite     0      3
16    ListRead      0      20
17    NotExists     0      19
18    Delete        0      1
19    Goto          0      16
20    ListReset     0      0
21    Close         0      0
22    Commit        0      0
23    Halt          0      0
}

puts {
<p>Here is what the program must do.  First it has to locate all of
the records in the table "examp" 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 SQLite 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 5 through 11) locates the records that are to be deleted 
and saves their keys onto a temporary list, and the second loop 
(instructions 16 through 19) uses the key list to delete the records one 
by one.  </p>
}


Code {
0     Transaction   1      0
1     Transaction   0      0
2     VerifyCookie  0      178
3     Integer       0      0
4     OpenRead      0      3      examp
}
puts {
<p>Instructions 0 though 4 are as in the INSERT example.  They start 
transactions for the main and temporary databases, verify the database 
schema for the main database, and open a read cursor on the table 
"examp".  Notice that the cursor is opened for reading, not writing.  At 
this stage of the program we are only going to be scanning the table, 
not changing it.  We will reopen the same table for writing later, at 
instruction 15.</p>
}

Code {
5     Rewind        0      12
}
puts {
<p>As in the SELECT example, the <a href="opcode.html#Rewind">Rewind</a> 
instruction rewinds the cursor to the beginning of the table, readying 
it for use in the loop body.</p>
}

Code {
6     Column        0      1
7     Integer       50     0      50
8     Ge            1      11
}
puts {
<p>The WHERE clause is implemented by instructions 6 through 8.
The job of the where clause is to skip the ListWrite if the WHERE
condition is false.  To this end, it jumps ahead to the Next instruction
if the "two" column (extracted by the Column instruction) is
greater than or equal to 50.</p>

<p>As before, the Column instruction uses cursor P1 and pushes the data 
record in column P2 (1, column "two") onto the stack.  The Integer 
instruction pushes the value 50 onto the top of the stack.  After these 
two instructions the stack looks like:</p>
}
stack {(integer) 50} \
  {(record) current record for column "two" }

puts {
<p>The <a href="opcode.html#Ge">Ge</a> operator compares the top two 
elements on the stack, pops them, and then branches based on the result 
of the comparison.  If the second element is >= the top element, then 
jump to address P2 (the Next instruction at the end of the loop).  
Because P1 is true, if either operand is NULL (and thus the result is 
NULL) then take the jump.  If we don't jump, just advance to the next 
instruction.</p>
}

Code {
9     Recno         0      0
10    ListWrite     0      0
}
puts {
<p>The <a href="opcode.html#Recno">Recno</a> instruction pushes onto the 
stack an integer which is the first 4 bytes of the the key to the current 
entry in a sequential scan of the table pointed to by cursor P1.
The <a href="opcode.html#ListWrite">ListWrite</a> instruction writes the 
integer on the top of the stack into a temporary storage list and pops 
the top element.  This is the important work of this loop, to store the 
keys of the records to be deleted so we can delete them in the second 
loop.  After this ListWrite instruction the stack is empty again.</p>
}

Code {
11    Next          0      6
12    Close         0      0
}
puts {
<p> The Next instruction increments the cursor to point to the next 
element in the table pointed to by cursor P0, and if it was successful 
branches to P2 (6, the beginning of the loop body).  The Close 
instruction closes cursor P1.  It doesn't affect the temporary storage 
list because it isn't associated with cursor P1; it is instead a global 
working list (which can be saved with ListPush).</p>
}

Code {
13    ListRewind    0      0
}
puts {
<p> The <a href="opcode.html#ListRewind">ListRewind</a> instruction 
rewinds the temporary storage list to the beginning.  This prepares it 
for use in the second loop.</p>
}

Code {
14    Integer       0      0
15    OpenWrite     0      3
}
puts {
<p> As in the INSERT example, we push the database number P1 (0, the main 
database) onto the stack and use OpenWrite to open the cursor P1 on table 
P2 (base page 3, "examp") for modification.</p>
}

Code {
16    ListRead      0      20
17    NotExists     0      19
18    Delete        0      1
19    Goto          0      16
}
puts {
<p>This loop does the actual deleting.  It is organized differently from 
the one in the UPDATE example.  The ListRead instruction plays the role 
that the Next did in the INSERT loop, but because it jumps to P2 on 
failure, and Next jumps on success, we put it at the start of the loop 
instead of the end.  This means that we have to put a Goto at the end of 
the loop to jump back to the the loop test at the beginning.  So this 
loop has the form of a C while(){...} loop, while the loop in the INSERT 
example had the form of a do{...}while() loop.  The Delete instruction 
fills the role that the callback function did in the preceding examples.
</p>
<p>The <a href="opcode.html#ListRead">ListRead</a> instruction reads an 
element from the temporary storage list and pushes it onto the stack.  
If this was successful, it continues to the next instruction.  If this 
fails because the list is empty, it branches to P2, which is the 
instruction just after the loop.  Afterwards the stack looks like:</p>
}
stack {(integer) key for current record}

puts {
<p>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 jump to P2, 
depending on whether or not there is a next "thing" to push.
</blockquote>
<p>One 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.  Another difference 
is whether to jump or fall through if there is no next "thing".  In this 
case, Next falls through, and ListRead jumps. Later on, we will see 
other looping instructions (NextIdx and SortNext) that operate using the 
same principle.</p>

<p>The <a href="opcode.html#NotExists">NotExists</a> instruction pops 
the top stack element and uses it as an integer key.  If a record with 
that key does not exist in table P1, then jump to P2.  If a record does 
exist, then fall thru to the next instruction.  In this case P2 takes 
us to the Goto at the end of the loop, which jumps back to the ListRead 
at the beginning.  This could have been coded to have P2 be 16, the 
ListRead at the start of the loop, but the SQLite parser which generated 
this code didn't make that optimization.</p>
<p>The <a href="opcode.html#Delete">Delete</a> does the work of this 
loop; it pops an integer key off the stack (placed there by the 
preceding ListRead) and deletes the record of cursor P1 that has that key.  
Because P2 is true, the row change counter is incremented.</p>
<p>The <a href="opcode.html#Goto">Goto</a> jumps back to the beginning