sqlite3/www/opcode.tcl

   1 #
   2 # Run this Tcl script to generate the sqlite.html file.
   3 #
   4 set rcsid {$Id: opcode.tcl,v 1.15 2005/03/09 12:26:51 danielk1977 Exp $}
   5 source common.tcl
   6 header {SQLite Virtual Machine Opcodes}
   7 puts {
   8 <h2>SQLite Virtual Machine Opcodes</h2>
   9 }
  10
  11 set fd [open [lindex $argv 0] r]
  12 set file [read $fd [file size [lindex $argv 0]]]
  13 close $fd
  14 set current_op {}
  15 foreach line [split $file \n] {
  16   set line [string trim $line]
  17   if {[string index $line 1]!="*"} {
  18     set current_op {}
  19     continue
  20   }
  21   if {[regexp {^/\* Opcode: } $line]} {
  22     set current_op [lindex $line 2]
  23     set txt [lrange $line 3 end]
  24     regsub -all {>} $txt {\&gt;} txt
  25     regsub -all {<} $txt {\&lt;} txt
  26     set Opcode($current_op:args) $txt
  27     lappend OpcodeList $current_op
  28     continue
  29   }
  30   if {$current_op==""} continue
  31   if {[regexp {^\*/} $line]} {
  32     set current_op {}
  33     continue
  34   }
  35   set line [string trim [string range $line 3 end]]
  36   if {$line==""} {
  37     append Opcode($current_op:text) \n<p>
  38   } else {
  39     regsub -all {>} $line {\&gt;} line
  40     regsub -all {<} $line {\&lt;} line
  41     append Opcode($current_op:text) \n$line
  42   }
  43 }
  44 unset file
  45
  46 puts {
  47 <h3>Introduction</h3>
  48
  49 <p>In order to execute an SQL statement, the SQLite library first parses
  50 the SQL, analyzes the statement, then generates a short program to execute
  51 the statement.  The program is generated for a "virtual machine" implemented
  52 by the SQLite library.  This document describes the operation of that
  53 virtual machine.</p>
  54
  55 <p>This document is intended as a reference, not a tutorial.
  56 A separate <a href="vdbe.html">Virtual Machine Tutorial</a> is
  57 available.  If you are looking for a narrative description
  58 of how the virtual machine works, you should read the tutorial
  59 and not this document.  Once you have a basic idea of what the
  60 virtual machine does, you can refer back to this document for
  61 the details on a particular opcode.
  62 Unfortunately, the virtual machine tutorial was written for
  63 SQLite version 1.0.  There are substantial changes in the virtual
  64 machine for version 2.0 and the document has not been updated.
  65 </p>
  66
  67 <p>The source code to the virtual machine is in the <b>vdbe.c</b> source
  68 file.  All of the opcode definitions further down in this document are
  69 contained in comments in the source file.  In fact, the opcode table
  70 in this document
  71 was generated by scanning the <b>vdbe.c</b> source file
  72 and extracting the necessary information from comments.  So the
  73 source code comments are really the canonical source of information
  74 about the virtual machine.  When in doubt, refer to the source code.</p>
  75
  76 <p>Each instruction in the virtual machine consists of an opcode and
  77 up to three operands named P1, P2 and P3.  P1 may be an arbitrary
  78 integer.  P2 must be a non-negative integer.  P2 is always the
  79 jump destination in any operation that might cause a jump.
  80 P3 is a null-terminated
  81 string or NULL.  Some operators use all three operands.  Some use
  82 one or two.  Some operators use none of the operands.<p>
  83
  84 <p>The virtual machine begins execution on instruction number 0.
  85 Execution continues until (1) a Halt instruction is seen, or
  86 (2) the program counter becomes one greater than the address of
  87 last instruction, or (3) there is an execution error.
  88 When the virtual machine halts, all memory
  89 that it allocated is released and all database cursors it may
  90 have had open are closed.  If the execution stopped due to an
  91 error, any pending transactions are terminated and changes made
  92 to the database are rolled back.</p>
  93
  94 <p>The virtual machine also contains an operand stack of unlimited
  95 depth.  Many of the opcodes use operands from the stack.  See the
  96 individual opcode descriptions for details.</p>
  97
  98 <p>The virtual machine can have zero or more cursors.  Each cursor
  99 is a pointer into a single table or index within the database.
 100 There can be multiple cursors pointing at the same index or table.
 101 All cursors operate independently, even cursors pointing to the same
 102 indices or tables.
 103 The only way for the virtual machine to interact with a database
 104 file is through a cursor.
 105 Instructions in the virtual
 106 machine can create a new cursor (Open), read data from a cursor
 107 (Column), advance the cursor to the next entry in the table
 108 (Next) or index (NextIdx), and many other operations.
 109 All cursors are automatically
 110 closed when the virtual machine terminates.</p>
 111
 112 <p>The virtual machine contains an arbitrary number of fixed memory
 113 locations with addresses beginning at zero and growing upward.
 114 Each memory location can hold an arbitrary string.  The memory
 115 cells are typically used to hold the result of a scalar SELECT
 116 that is part of a larger expression.</p>
 117
 118 <p>The virtual machine contains a single sorter.
 119 The sorter is able to accumulate records, sort those records,
 120 then play the records back in sorted order.  The sorter is used
 121 to implement the ORDER BY clause of a SELECT statement.</p>
 122
 123 <p>The virtual machine contains a single "List".
 124 The list stores a list of integers.  The list is used to hold the
 125 rowids for records of a database table that needs to be modified.
 126 The WHERE clause of an UPDATE or DELETE statement scans through
 127 the table and writes the rowid of every record to be modified
 128 into the list.  Then the list is played back and the table is modified
 129 in a separate step.</p>
 130
 131 <p>The virtual machine can contain an arbitrary number of "Sets".
 132 Each set holds an arbitrary number of strings.  Sets are used to
 133 implement the IN operator with a constant right-hand side.</p>
 134
 135 <p>The virtual machine can open a single external file for reading.
 136 This external read file is used to implement the COPY command.</p>
 137
 138 <p>Finally, the virtual machine can have a single set of aggregators.
 139 An aggregator is a device used to implement the GROUP BY clause
 140 of a SELECT.  An aggregator has one or more slots that can hold
 141 values being extracted by the select.  The number of slots is the
 142 same for all aggregators and is defined by the AggReset operation.
 143 At any point in time a single aggregator is current or "has focus".
 144 There are operations to read or write to memory slots of the aggregator
 145 in focus.  There are also operations to change the focus aggregator
 146 and to scan through all aggregators.</p>
 147
 148 <h3>Viewing Programs Generated By SQLite</h3>
 149
 150 <p>Every SQL statement that SQLite interprets results in a program
 151 for the virtual machine.  But if you precede the SQL statement with
 152 the keyword "EXPLAIN" the virtual machine will not execute the
 153 program.  Instead, the instructions of the program will be returned
 154 like a query result.  This feature is useful for debugging and
 155 for learning how the virtual machine operates.</p>
 156
 157 <p>You can use the <b>sqlite</b> command-line tool to see the
 158 instructions generated by an SQL statement.  The following is
 159 an example:</p>}
 160
 161 proc Code {body} {
 162   puts {<blockquote><tt>}
 163   regsub -all {&} [string trim $body] {\&amp;} body
 164   regsub -all {>} $body {\&gt;} body
 165   regsub -all {<} $body {\&lt;} body
 166   regsub -all {\(\(\(} $body {<b>} body
 167   regsub -all {\)\)\)} $body {</b>} body
 168   regsub -all { } $body {\&nbsp;} body
 169   regsub -all \n $body <br>\n body
 170   puts $body
 171   puts {</tt></blockquote>}
 172 }
 173
 174 Code {
 175 $ (((sqlite ex1)))
 176 sqlite> (((.explain)))
 177 sqlite> (((explain delete from tbl1 where two<20;)))
 178 addr  opcode        p1     p2     p3
 179 ----  ------------  -----  -----  ----------------------------------------
 180 0     Transaction   0      0
 181 1     VerifyCookie  219    0
 182 2     ListOpen      0      0
 183 3     Open          0      3      tbl1
 184 4     Rewind        0      0
 185 5     Next          0      12
 186 6     Column        0      1
 187 7     Integer       20     0
 188 8     Ge            0      5
 189 9     Recno         0      0
 190 10    ListWrite     0      0
 191 11    Goto          0      5
 192 12    Close         0      0
 193 13    ListRewind    0      0
 194 14    OpenWrite     0      3
 195 15    ListRead      0      19
 196 16    MoveTo        0      0
 197 17    Delete        0      0
 198 18    Goto          0      15
 199 19    ListClose     0      0
 200 20    Commit        0      0
 201 }
 202
 203 puts {
 204 <p>All you have to do is add the "EXPLAIN" keyword to the front of the
 205 SQL statement.  But if you use the ".explain" command to <b>sqlite</b>
 206 first, it will set up the output mode to make the program more easily
 207 viewable.</p>
 208
 209 <p>If <b>sqlite</b> has been compiled without the "-DNDEBUG=1" option
 210 (that is, with the NDEBUG preprocessor macro not defined) then you
 211 can put the SQLite virtual machine in a mode where it will trace its
 212 execution by writing messages to standard output.  The non-standard
 213 SQL "PRAGMA" comments can be used to turn tracing on and off.  To
 214 turn tracing on, enter:
 215 </p>
 216
 217 <blockquote><pre>
 218 PRAGMA vdbe_trace=on;
 219 </pre></blockquote>
 220
 221 <p>
 222 You can turn tracing back off by entering a similar statement but
 223 changing the value "on" to "off".</p>
 224
 225 <h3>The Opcodes</h3>
 226 }
 227
 228 puts "<p>There are currently [llength $OpcodeList] opcodes defined by
 229 the virtual machine."
 230 puts {All currently defined opcodes are described in the table below.
 231 This table was generated automatically by scanning the source code
 232 from the file <b>vdbe.c</b>.</p>}
 233
 234 puts {
 235 <p><table cellspacing="1" border="1" cellpadding="10">
 236 <tr><th>Opcode&nbsp;Name</th><th>Description</th></tr>}
 237 foreach op [lsort -dictionary $OpcodeList] {
 238   puts {<tr><td valign="top" align="center">}
 239   puts "<a name=\"$op\">$op</a>"
 240   puts "<td>[string trim $Opcode($op:text)]</td></tr>"
 241 }
 242 puts {</table></p>}
 243 footer $rcsid