vdbe.tcl

sqlite嵌入式数据库源码

of the loop.  This is the end of the loop.</p>
}

Code {
20    ListReset     0      0
21    Close         0      0
22    Commit        0      0
23    Halt          0      0
}
puts {
<p>This block of instruction cleans up the VDBE program. Three of these 
instructions aren't really required, but are generated by the SQLite 
parser from its code templates, which are designed to handle more 
complicated cases.</p>
<p>The <a href="opcode.html#ListReset">ListReset</a> instruction empties 
the temporary storage list.  This list is emptied automatically when the 
VDBE program terminates, so it isn't necessary in this case.  The Close 
instruction closes the cursor P1.  Again, this is done by the VDBE 
engine when it is finished running this program.  The Commit ends the 
current transaction successfully, and causes all changes that occurred 
in this transaction to be saved to the database.  The final Halt is also 
unneccessary, since it is added to every VDBE program when it is 
prepared to run.</p>


<p>UPDATE statements work very much like DELETE statements except
that instead of deleting the record they replace it with a new one.
Consider this example:
</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     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    Integer       0      0                                         
14    OpenWrite     0      3                                              
15    ListRewind    0      0                                         
16    ListRead      0      28                                             
17    Dup           0      0                                         
18    NotExists     0      16                                             
19    String        0      0      (                                  
20    Column        0      0                                         
21    Concat        2      0                                         
22    String        0      0      )                                  
23    Concat        2      0                                         
24    Column        0      1                                         
25    MakeRecord    2      0                                         
26    PutIntKey     0      1                                         
27    Goto          0      16                                             
28    ListReset     0      0                                         
29    Close         0      0                                         
30    Commit        0      0                                         
31    Halt          0      0                                         
}

puts {
<p>This program is essentially the same as the DELETE program except 
that the body of the second loop has been replace by a sequence of 
instructions (at addresses 17 through 26) that update the record rather 
than delete it.  Most of this instruction sequence should already be 
familiar to you, but there are a couple of minor twists so we will go 
over it briefly.  Also note that the order of some of the instructions 
before and after the 2nd loop has changed.  This is just the way the 
SQLite parser chose to output the code using a different template.</p>

<p>As we enter the interior of the second loop (at instruction 17)
the stack contains a single integer which is the key of the
record we want to modify.  We are going to need to use this
key twice: once to fetch the old value of the record and
a second time to write back the revised record.  So the first instruction
is a Dup to make a duplicate of the key on the top of the stack.  The
Dup instruction will duplicate any element of the stack, not just the top
element.  You specify which element to duplication using the
P1 operand.  When P1 is 0, the top of the stack is duplicated.
When P1 is 1, the next element down on the stack duplication.
And so forth.</p>

<p>After duplicating the key, the next instruction, NotExists,
pops the stack once and uses the value popped as a key to
check the existence of a record in the database file.  If there is no record 
for this key, it jumps back to the ListRead to get another key.</p>

<p>Instructions 19 through 25 construct a new database record
that will be used to replace the existing record.  This is
the same kind of code that we saw 
in the description of INSERT and will not be described further.
After instruction 25 executes, the stack looks like this:</p>
}

stack {(record) new data record} {(integer) key}

puts {
<p>The PutIntKey instruction (also described
during the discussion about INSERT) writes an entry into the
database file whose data is the top of the stack and whose key
is the next on the stack, and then pops the stack twice.  The
PutIntKey instruction will overwrite the data of an existing record
with the same key, which is what we want here.  Overwriting was not
an issue with INSERT because with INSERT the key was generated
by the NewRecno instruction which is guaranteed to provide a key
that has not been used before.</p>
}

if 0 {<p>(By the way, since keys must
all be unique and each key is a 32-bit integer, a single
SQLite database table can have no more than 2<sup>32</sup>
rows.  Actually, the Key instruction starts to become
very inefficient as you approach this upper bound, so it
is best to keep the number of entries below 2<sup>31</sup>
or so.  Surely a couple billion records will be enough for
most applications!)</p>
}

puts {
<h2>CREATE and DROP</h2>

<p>Using CREATE or DROP to create or destroy a table or index is
really the same as doing an INSERT or DELETE from the special
"sqlite_master" table, at least from the point of view of the VDBE.
The sqlite_master table is a special table that is automatically
created for every SQLite database.  It looks like this:</p>

<blockquote><pre>
CREATE TABLE sqlite_master (
  type      TEXT,    -- either "table" or "index"
  name      TEXT,    -- name of this table or index
  tbl_name  TEXT,    -- for indices: name of associated table
  sql       TEXT     -- SQL text of the original CREATE statement
)
</pre></blockquote>

<p>Every table (except the "sqlite_master" table itself)
and every named index in an SQLite database has an entry
in the sqlite_master table.  You can query this table using
a SELECT statement just like any other table.  But you are
not allowed to directly change the table using UPDATE, INSERT,
or DELETE.  Changes to sqlite_master have to occur using
the CREATE and DROP commands because SQLite also has to update
some of its internal data structures when tables and indices
are added or destroyed.</p>

<p>But from the point of view of the VDBE, a CREATE works
pretty much like an INSERT and a DROP works like a DELETE.
When the SQLite library opens to an existing database,
the first thing it does is a SELECT to read the "sql"
columns from all entries of the sqlite_master table.
The "sql" column contains the complete SQL text of the
CREATE statement that originally generated the index or
table.  This text is fed back into the SQLite parser
and used to reconstruct the
internal data structures describing the index or table.</p>

<h2>Using Indexes To Speed Searching</h2>

<p>In the example queries above, every row of the table being
queried must be loaded off of the disk and examined, even if only
a small percentage of the rows end up in the result.  This can
take a long time on a big table.  To speed things up, SQLite
can use an index.</p>

<p>An SQLite file associates a key with some data.  For an SQLite
table, the database file is set up so that the key is an integer
and the data is the information for one row of the table.
Indices in SQLite reverse this arrangement.  The index key
is (some of) the information being stored and the index data 
is an integer.
To access a table row that has some particular
content, we first look up the content in the index table to find
its integer index, then we use that integer to look up the
complete record in the table.</p>

<p>Note that SQLite uses b-trees, which are a sorted data structure, 
so indices can be used when the WHERE clause of the SELECT statement
contains tests for equality or inequality.  Queries like the following 
can use an index if it is available:</p>

<blockquote><pre>
SELECT * FROM examp WHERE two==50;
SELECT * FROM examp WHERE two<50;
SELECT * FROM examp WHERE two IN (50, 100);
</pre></blockquote>

<p>If there exists an index that maps the "two" column of the "examp"
table into integers, then SQLite will use that index to find the integer
keys of all rows in examp that have a value of 50 for column two, or 
all rows that are less than 50, etc.
But the following queries cannot use the index:</p>

<blockquote><pre>
SELECT * FROM examp WHERE two%50 == 10;
SELECT * FROM examp WHERE two&127 == 3;
</pre></blockquote>

<p>Note that the SQLite parser will not always generate code to use an 
index, even if it is possible to do so.  The following queries will not 
currently use the index:</p>

<blockquote><pre>
SELECT * FROM examp WHERE two+10 == 50;
SELECT * FROM examp WHERE two==50 OR two==100;
</pre></blockquote>

<p>To understand better how indices work, lets first look at how
they are created.  Let's go ahead and put an index on the two
column of the examp table.  We have:</p>

<blockquote><pre>
CREATE INDEX examp_idx1 ON examp(two);
</pre></blockquote>

<p>The VDBE code generated by the above statement looks like the
following:</p>
}

Code {
addr  opcode        p1     p2     p3                                      
----  ------------  -----  -----  -----------------------------------
0     Transaction   1      0                                         
1     Transaction   0      0                                         
2     VerifyCookie  0      178                                            
3     Integer       0      0                                         
4     OpenWrite     0      2                                         
5     NewRecno      0      0                                         
6     String        0      0      index                              
7     String        0      0      examp_idx1                         
8     String        0      0      examp                              
9     CreateIndex   0      0      ptr(0x791380)                      
10    Dup           0      0                                         
11    Integer       0      0                                         
12    OpenWrite     1      0                                         
13    String        0      0      CREATE INDEX examp_idx1 ON examp(tw
14    MakeRecord    5      0                                         
15    PutIntKey     0      0                                         
16    Integer       0      0                                         
17    OpenRead      2      3      examp                              
18    Rewind        2      24                                             
19    Recno         2      0                                         
20    Column        2      1                                         
21    MakeIdxKey    1      0      n                                  
22    IdxPut        1      0      indexed columns are not unique     
23    Next          2      19                                             
24    Close         2      0                                         
25    Close         1      0                                         
26    Integer       333    0                                         
27    SetCookie     0      0                                         
28    Close         0      0                                         
29    Commit        0      0                                         
30    Halt          0      0                                         
}

puts {
<p>Remember that every table (except sqlite_master) and every named
index has an entry in the sqlite_master table.  Since we are creating
a new index, we have to add a new entry to sqlite_master.  This is
handled by instructions 3 through 15.  Adding an entry to sqlite_master
works just like any other INSERT statement so we will not say anymore
about it here.  In this example, we want to focus on populating the
new index with valid data, which happens on instructions 16 through 
23.</p>
}

Code {
16    Integer       0      0                                         
17    OpenRead      2      3      examp                              
}
puts {
<p>The first thing that happens is that we open the table being
indexed for reading.  In order to construct an index for a table,
we have to know what is in that table.  The index has already been 
opened for writing using cursor 0 by instructions 3 and 4.</p>
}