/ Check-in [3661b5ff]
Login

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

Overview
Comment::-) (CVS 87)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 3661b5ff93b01da7fea9f85370ecdda1402b7164
User & Date: drh 2000-06-09 01:58:36
Context
2000-06-09
03:47
:-) (CVS 88) check-in: 3252269e user: drh tags: trunk
01:58
:-) (CVS 87) check-in: 3661b5ff user: drh tags: trunk
2000-06-08
21:53
:-) (CVS 86) check-in: 049abcb3 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to Makefile.in.

   183    183   	tclsh $(TOP)/www/changes.tcl >changes.html
   184    184   
   185    185   fileformat.html:	$(TOP)/www/fileformat.tcl
   186    186   	tclsh $(TOP)/www/fileformat.tcl >fileformat.html
   187    187   
   188    188   lang.html:	$(TOP)/www/lang.tcl
   189    189   	tclsh $(TOP)/www/lang.tcl >lang.html
          190  +
          191  +arch.html:	$(TOP)/www/arch.tcl
          192  +	tclsh $(TOP)/www/arch.tcl >arch.html
          193  +
          194  +arch.png:	$(TOP)/www/arch.png
          195  +	cp $(TOP)/www/arch.png .
          196  +
          197  +opcode.html:	$(TOP)/www/opcode.tcl
          198  +	tclsh $(TOP)/www/opcode.tcl $(TOP)/src/vdbe.c >opcode.html
          199  +
   190    200   
   191    201   # Files to be published on the website.
   192    202   #
   193    203   PUBLISH = \
   194    204     sqlite.tar.gz \
   195    205     index.html \
   196    206     sqlite.html \
   197    207     changes.html \
   198    208     fileformat.html \
   199    209     lang.html \
          210  +  opcode.html \
          211  +  arch.html \
          212  +  arch.png \
   200    213     c_interface.html
   201    214   
   202    215   website:	$(PUBLISH)
   203    216   
   204    217   publish:	$(PUBLISH)
   205    218   	scp $(PUBLISH) hwaci@oak.he.net:public_html/sw/sqlite
   206    219   
   207    220   clean:	
   208    221   	rm -f *.o sqlite libsqlite.a sqlite.h
   209    222   	rm -f lemon lempar.c parse.* sqlite.tar.gz
   210    223   	rm -f $(PUBLISH)
   211    224   	rm -f *.da *.bb *.bbg gmon.out

Changes to src/vdbe.c.

    37     37   ** inplicit conversion from one type to the other occurs as necessary.
    38     38   ** 
    39     39   ** Most of the code in this file is taken up by the sqliteVdbeExec()
    40     40   ** function which does the work of interpreting a VDBE program.
    41     41   ** But other routines are also provided to help in building up
    42     42   ** a program instruction by instruction.
    43     43   **
    44         -** $Id: vdbe.c,v 1.30 2000/06/08 16:26:25 drh Exp $
           44  +** $Id: vdbe.c,v 1.31 2000/06/09 01:58:37 drh Exp $
    45     45   */
    46     46   #include "sqliteInt.h"
    47     47   #include <unistd.h>
    48     48   
    49     49   /*
    50     50   ** SQL is translated into a sequence of instructions to be
    51     51   ** executed by a virtual machine.  Each instruction is an instance
................................................................................
   942    942           if( z==0 ) z = "";
   943    943           p->zStack[i] = z;
   944    944           p->aStack[i].n = strlen(z) + 1;
   945    945           p->aStack[i].flags = STK_Str;
   946    946           break;
   947    947         }
   948    948   
   949         -      /* Opcode: NULL * * *
          949  +      /* Opcode: Null * * *
   950    950         **
   951    951         ** Push a NULL value onto the stack.
   952    952         */
   953    953         case OP_Null: {
   954    954           int i = ++p->tos;
   955    955           if( NeedStack(p, p->tos) ) goto no_mem;
   956    956           p->zStack[i] = 0;
................................................................................
  1889   1889         }
  1890   1890   
  1891   1891         /* Opcode: KeyAsData P1 P2 *
  1892   1892         **
  1893   1893         ** Turn the key-as-data mode for cursor P1 either on (if P2==1) or
  1894   1894         ** off (if P2==0).  In key-as-data mode, the OP_Fetch opcode pulls
  1895   1895         ** data off of the key rather than the data.  This is useful for
  1896         -      ** outer joins and stuff...
         1896  +      ** processing compound selects.
  1897   1897         */
  1898   1898         case OP_KeyAsData: {
  1899   1899           int i = pOp->p1;
  1900   1900           if( i>=0 && i<p->nTable && p->aTab[i].pTable!=0 ){
  1901   1901             p->aTab[i].keyAsData = pOp->p2;
  1902   1902           }
  1903   1903           break;
................................................................................
  1906   1906         /* Opcode: Field P1 P2 *
  1907   1907         **
  1908   1908         ** Push onto the stack the value of the P2-th field from the
  1909   1909         ** most recent Fetch from table P1.
  1910   1910         ** 
  1911   1911         ** The value pushed is just a pointer to the data in the cursor.
  1912   1912         ** The value will go away the next time a record is fetched from P1,
  1913         -      ** or when P1 is closed.  Make a copy of the string if it needs
  1914         -      ** to persist longer than that.
         1913  +      ** or when P1 is closed.  Make a copy of the string (using
         1914  +      ** "Concat 1 0 0" if it needs to persist longer than that.
         1915  +      **
         1916  +      ** If the KeyAsData opcode has previously executed on this cursor,
         1917  +      ** then the field might be extracted from the key rather than the
         1918  +      ** data.
  1915   1919         */
  1916   1920         case OP_Field: {
  1917   1921           int *pAddr;
  1918   1922           int amt;
  1919   1923           int i = pOp->p1;
  1920   1924           int p2 = pOp->p2;
  1921   1925           int tos = ++p->tos;
................................................................................
  2752   2756         **
  2753   2757         ** Pop the top of the stack and use that as an aggregator key.  If
  2754   2758         ** an aggregator with that same key already exists, then make the
  2755   2759         ** aggregator the current aggregator and jump to P2.  If no aggregator
  2756   2760         ** with the given key exists, create one and make it current but
  2757   2761         ** do not jump.
  2758   2762         **
  2759         -      ** This opcode should not be executed after an AggNext but before
  2760         -      ** the next AggReset.
         2763  +      ** The order of aggregator opcodes is important.  The order is:
         2764  +      ** AggReset AggFocus AggNext.  In other words, you must execute
         2765  +      ** AggReset first, then zero or more AggFocus operations, then
         2766  +      ** zero or more AggNext operations.  You must not execute an AggFocus
         2767  +      ** in between an AggNext and an AggReset.
  2761   2768         */
  2762   2769         case OP_AggFocus: {
  2763   2770           int tos = p->tos;
  2764   2771           AggElem *pElem;
  2765   2772           char *zKey;
  2766   2773           int nKey;
  2767   2774   
................................................................................
  2848   2855           PopStack(p, 1);
  2849   2856           break;
  2850   2857         }
  2851   2858   
  2852   2859         /* Opcode: AggGet * P2 *
  2853   2860         **
  2854   2861         ** Push a new entry onto the stack which is a copy of the P2-th field
  2855         -      ** of the current aggregate.  String are not duplicated so
  2856         -      ** string values will be ephemeral.  
         2862  +      ** of the current aggregate.  Strings are not duplicated so
         2863  +      ** string values will be ephemeral.
  2857   2864         */
  2858   2865         case OP_AggGet: {
  2859   2866           AggElem *pFocus = AggInFocus(p->agg);
  2860   2867           int i = pOp->p2;
  2861   2868           int tos = ++p->tos;
  2862   2869           if( NeedStack(p, tos) ) goto no_mem;
  2863   2870           if( pFocus==0 ) goto no_mem;
................................................................................
  2872   2879   
  2873   2880         /* Opcode: AggNext * P2 *
  2874   2881         **
  2875   2882         ** Make the next aggregate value the current aggregate.  The prior
  2876   2883         ** aggregate is deleted.  If all aggregate values have been consumed,
  2877   2884         ** jump to P2.
  2878   2885         **
  2879         -      ** Do not execute an AggFocus after this opcode until after the
  2880         -      ** next AggReset.
         2886  +      ** The order of aggregator opcodes is important.  The order is:
         2887  +      ** AggReset AggFocus AggNext.  In other words, you must execute
         2888  +      ** AggReset first, then zero or more AggFocus operations, then
         2889  +      ** zero or more AggNext operations.  You must not execute an AggFocus
         2890  +      ** in between an AggNext and an AggReset.
  2881   2891         */
  2882   2892         case OP_AggNext: {
  2883   2893           if( p->agg.nHash ){
  2884   2894             p->agg.nHash = 0;
  2885   2895             sqliteFree(p->agg.apHash);
  2886   2896             p->agg.apHash = 0;
  2887   2897             p->agg.pCurrent = p->agg.pFirst;
................................................................................
  2901   2911             pc = pOp->p2-1;
  2902   2912           }
  2903   2913           break;
  2904   2914         }
  2905   2915   
  2906   2916         /* Opcode: SetClear P1 * *
  2907   2917         **
  2908         -      ** Remove all elements from the given Set.
         2918  +      ** Remove all elements from the P1-th Set.
  2909   2919         */
  2910   2920         case OP_SetClear: {
  2911   2921           int i = pOp->p1;
  2912   2922           if( i>=0 && i<p->nSet ){
  2913   2923             SetClear(&p->aSet[i]);
  2914   2924           }
  2915   2925           break;
  2916   2926         }
  2917   2927   
  2918   2928         /* Opcode: SetInsert P1 * P3
  2919   2929         **
  2920         -      ** If Set p1 does not exist then create it.  Then insert value
         2930  +      ** If Set P1 does not exist then create it.  Then insert value
  2921   2931         ** P3 into that set.  If P3 is NULL, then insert the top of the
  2922   2932         ** stack into the set.
  2923   2933         */
  2924   2934         case OP_SetInsert: {
  2925   2935           int i = pOp->p1;
  2926   2936           if( p->nSet<=i ){
  2927   2937             p->aSet = sqliteRealloc(p->aSet, (i+1)*sizeof(p->aSet[0]) );

Added www/arch.fig.

            1  +#FIG 3.2
            2  +Landscape
            3  +Center
            4  +Inches
            5  +Letter  
            6  +100.00
            7  +Single
            8  +-2
            9  +1200 2
           10  +2 2 0 1 0 7 100 0 -1 0.000 0 0 -1 0 0 5
           11  +	 2550 2250 4875 2250 4875 3525 2550 3525 2550 2250
           12  +2 2 0 1 0 7 100 0 -1 0.000 0 0 -1 0 0 5
           13  +	 2550 4050 4875 4050 4875 5325 2550 5325 2550 4050
           14  +2 2 0 1 0 7 100 0 -1 0.000 0 0 -1 0 0 5
           15  +	 2550 5850 4875 5850 4875 7125 2550 7125 2550 5850
           16  +2 2 0 1 0 7 100 0 -1 0.000 0 0 -1 0 0 5
           17  +	 2550 7650 4875 7650 4875 8925 2550 8925 2550 7650
           18  +2 2 0 1 0 7 100 0 -1 0.000 0 0 -1 0 0 5
           19  +	 2550 450 4875 450 4875 1725 2550 1725 2550 450
           20  +2 2 0 1 0 7 100 0 -1 0.000 0 0 -1 0 0 5
           21  +	 2550 9450 4875 9450 4875 10725 2550 10725 2550 9450
           22  +2 1 0 3 0 7 100 0 -1 0.000 0 0 -1 1 0 2
           23  +	1 1 3.00 75.00 135.00
           24  +	 3675 1725 3675 2250
           25  +2 1 0 3 0 7 100 0 -1 0.000 0 0 -1 1 0 2
           26  +	1 1 3.00 75.00 135.00
           27  +	 3675 3525 3675 4050
           28  +2 1 0 3 0 7 100 0 -1 0.000 0 0 -1 1 0 2
           29  +	1 1 3.00 75.00 135.00
           30  +	 3675 5325 3675 5850
           31  +2 1 0 3 0 7 100 0 -1 0.000 0 0 -1 1 0 2
           32  +	1 1 3.00 75.00 135.00
           33  +	 3675 7125 3675 7650
           34  +2 1 0 3 0 7 100 0 -1 0.000 0 0 -1 1 0 2
           35  +	1 1 3.00 75.00 135.00
           36  +	 3675 8925 3675 9450
           37  +4 1 0 100 0 0 20 0.0000 4 195 1920 3675 8025 Virtual Machine\001
           38  +4 1 0 100 0 0 20 0.0000 4 195 1830 3675 6225 Code Generator\001
           39  +4 1 0 100 0 0 20 0.0000 4 195 735 3675 4350 Parser\001
           40  +4 1 0 100 0 0 20 0.0000 4 195 1140 3675 2550 Tokenizer\001
           41  +4 1 0 100 0 0 20 0.0000 4 195 1020 3675 750 Interface\001
           42  +4 1 0 100 0 0 20 0.0000 4 195 990 3675 9825 Backend\001
           43  +4 1 0 100 0 0 14 0.0000 4 150 570 3675 10650 dbbe.c\001
           44  +4 1 0 100 0 0 14 0.0000 4 150 570 3675 8850 vdbe.c\001
           45  +4 1 0 100 0 0 14 0.0000 4 195 2190 3675 7050 select.c update.c where.c\001
           46  +4 1 0 100 0 0 14 0.0000 4 195 1860 3675 6825 build.c delete.c expr.c\001
           47  +4 1 0 100 0 0 14 0.0000 4 150 630 3675 5250 parse.y\001
           48  +4 1 0 100 0 0 14 0.0000 4 150 870 3675 3450 tokenize.c\001
           49  +4 1 0 100 0 0 14 0.0000 4 150 570 3675 1575 main.c\001

Added www/arch.png.

cannot compute difference between binary files

Added www/arch.tcl.

            1  +#
            2  +# Run this Tcl script to generate the sqlite.html file.
            3  +#
            4  +set rcsid {$Id: arch.tcl,v 1.1 2000/06/09 01:58:51 drh Exp $}
            5  +
            6  +puts {<html>
            7  +<head>
            8  +  <title>Architecture of SQLite</title>
            9  +</head>
           10  +<body bgcolor=white>
           11  +<h1 align=center>
           12  +The Architecture Of SQLite
           13  +</h1>}
           14  +puts "<p align=center>
           15  +(This page was last modified on [lrange $rcsid 3 4] GMT)
           16  +</p>"
           17  +
           18  +puts {
           19  +<h2>Introduction</h2>
           20  +
           21  +<table align="right" border="1" cellpadding="15" cellspacing="1">
           22  +<tr><th>Block Diagram Of SQLite</th></tr>
           23  +<tr><td><img src="arch.png"></td></tr>
           24  +</table>
           25  +<p>This file describes the architecture of the SQLite library.
           26  +A block diagram showing the main components of SQLite
           27  +and how that interrelate is shown at the right.  The text that
           28  +follows will provide a quick overview of each of these components.
           29  +</p>
           30  +
           31  +<h2>Interface</h2>
           32  +
           33  +<p>The public interface to the SQLite library is implemented by
           34  +four functions found in the <b>main.c</b> source file.  Additional
           35  +information on the C interface to SQLite is
           36  +<a href="c_interface.html">available separately</a>.<p>
           37  +
           38  +<p>To avoid name collisions with other software, all external
           39  +symbols in the SQLite library begin with the prefix <b>sqlite</b>.
           40  +Those symbols that are intended for external use (as oppose to
           41  +those which are for internal use only but which have to be exported
           42  +do to limitations of the C linker's scoping mechanism) begin
           43  +with <b>sqlite_</b>.</p>
           44  +
           45  +<h2>Tokenizer</h2>
           46  +
           47  +<p>When a string containing SQL statements is to be executed, the
           48  +interface passes that string to the tokenizer.  The job of the tokenizer
           49  +is to break the original string up into tokens and pass those tokens
           50  +one by one to the parser.  The tokenizer is hand-coded in C.
           51  +(There is no "lex" code here.)  All of the code for the tokenizer
           52  +is contained in the <b>tokenize.c</b> source file.</p>
           53  +
           54  +<p>Note that in this design, the tokenizer calls the parser.  People
           55  +who are familiar with YACC and BISON may be used to doing things the
           56  +other way around -- having the parser call the tokenizer.  This author
           57  +as done it both ways, and finds things generally work out nicer for
           58  +the tokenizer to call the parser.  YACC has it backwards.</p>
           59  +
           60  +<h2>Parser</h2>
           61  +
           62  +<p>The parser is the piece that assigns meaning to tokens based on
           63  +their context.  The parser for SQLite is generated using the
           64  +<a href="http://www.hwaci.com/sw/lemon/">Lemon</a> LALR(1) parser
           65  +generator.  Lemon does the same job as YACC/BISON, but is uses
           66  +a different input syntax which is less error-prone than the 
           67  +clumsy YACC/BISON syntax.  
           68  +Lemon also generates a parser which is reentrant and thread-safe.
           69  +And lemon defines the concept of a non-terminal destructor so
           70  +that it does not leak memory when syntax errors are encountered.
           71  +The source file that drives Lemon is found in <b>parse.y</b>.</p>
           72  +
           73  +<p>Because
           74  +lemon is a program not normally found on development machines, the
           75  +complete source code to lemon (just one C file) is included in the
           76  +SQLite distribution in the "tool" subdirectory.  Documentation on
           77  +lemon is found in the "doc" subdirectory of the distribution.
           78  +</p>
           79  +
           80  +<h2>Code Generator</h2>
           81  +
           82  +<p>After the parser assemblies tokens into complete SQL statements,
           83  +it calls the code generator to produce virtual machine code that
           84  +will do the work that the SQL statements request.  There are six
           85  +files in the code generator:  <b>build.c</b>, <b>delete.c</b>,
           86  +<b>expr.c</b>, <b>select.c</b>, <b>update.c</b>, and <b>where.c</b>.
           87  +In these files is where most of the serious magic happens.</p>
           88  +
           89  +<h2>Virtual Machine</h2>
           90  +
           91  +<p>The program generated by the code generator is executed by
           92  +the virtual machine.  Additional information about the virtual
           93  +machine is <a href="opcode.html">available separately</a>.
           94  +To summarize, the virtual machine implements an abstract computing
           95  +engine specifically designed to manipulate database files.  The
           96  +machine as a stack.  Each instruction contains an opcode and
           97  +up to three additional operands.</p>
           98  +
           99  +<p>The virtual machine is entirely contained in a single
          100  +source file <b>vdbe.c</b>.  The virtual machine also has
          101  +its own header file <b>vdbe.h</b> that defines an interface
          102  +between the virtual machine and the rest of the SQLite library.</p>
          103  +
          104  +<h2>Backend</h2>
          105  +
          106  +<p>The last layer in the design of SQLite is the backend.  The
          107  +backend implements an interface between the virtual machine and
          108  +the underlying data file library -- GDBM in this case.  The interface
          109  +is designed to make it easy to substitute a different database
          110  +library, such as the Berkeley DB.  
          111  +The backend abstracts many of the low-level details to help
          112  +reduce the complexity of the virtual machine.</p>
          113  +
          114  +<p>The backend is contained in the single source file <b>dbbe.c</b>.
          115  +The backend also has a header file <b>dbbe.h</b> that defines the
          116  +interface between the backend and the rest of the SQLite library.</p>
          117  +}
          118  +
          119  +puts {
          120  +<br clear="both" />
          121  +<p><hr /></p>
          122  +<p><a href="index.html"><img src="/goback.jpg" border=0 />
          123  +Back to the SQLite Home Page</a>
          124  +</p>
          125  +
          126  +</body></html>}

Changes to www/lang.tcl.

     1      1   #
     2      2   # Run this Tcl script to generate the sqlite.html file.
     3      3   #
     4         -set rcsid {$Id: lang.tcl,v 1.1 2000/06/08 21:53:06 drh Exp $}
            4  +set rcsid {$Id: lang.tcl,v 1.2 2000/06/09 01:58:37 drh Exp $}
     5      5   
     6      6   puts {<html>
     7      7   <head>
     8      8     <title>Query Language Understood By SQLite</title>
     9      9   </head>
    10     10   <body bgcolor=white>
    11     11   <h1 align=center>
................................................................................
    26     26   bold blue.  Non-terminal symbols are shown in italic red.  Operators
    27     27   that are part of the syntactic markup itself are shown in black roman.</p>
    28     28   
    29     29   <p>This document is just an overview of the SQL syntax implemented
    30     30   by SQLite.  Many low-level productions are omitted.  For detailed information
    31     31   on the language that SQLite understands, refer to the source code.</p>
    32     32   
    33         -<h2>CREATE TABLE</h2>
    34     33   
    35         -<p>The basic structure of a CREATE TABLE statement is as follows:</p>
           34  +<p>SQLite implements the follow SQL commands:</p>
           35  +<p><ul>
           36  +}
           37  +
           38  +foreach {section} [lsort -index 0 -dictionary {
           39  +  {{CREATE TABLE} createtable}
           40  +  {{CREATE INDEX} createindex}
           41  +  {VACUUM vacuum}
           42  +  {{DROP TABLE} droptable}
           43  +  {{DROP INDEX} dropindex}
           44  +  {INSERT insert}
           45  +  {DELETE delete}
           46  +  {UPDATE update}
           47  +  {SELECT select}
           48  +  {COPY copy}
           49  +}] {
           50  +  puts "<li><a href=\"#[lindex $section 1]\">[lindex $section 0]</a></li>"
           51  +}
           52  +puts {</ul></p>
           53  +
           54  +<p>Details on the implementation of each command are provided in
           55  +the sequel.</p>
    36     56   }
    37     57   
    38     58   proc Syntax {args} {
    39     59     puts {<table cellpadding="15">}
    40     60     foreach {rule body} $args {
    41     61       puts "<tr><td align=\"right\" valign=\"top\">"
    42     62       puts "<i><font color=\"#ff3434\">$rule</font></i>&nbsp;::=</td>"
................................................................................
    49     69       regsub -all "\n  *" $body "\n\\&nbsp;\\&nbsp;\\&nbsp;\\&nbsp;" body
    50     70       regsub -all {[|,*()]} $body {<big>&</big>} body
    51     71       puts "<td><b><font color=\"#2c2cf0\">$body</font></b></td></tr>"
    52     72     }
    53     73     puts {</table>}
    54     74   }
    55     75   
           76  +proc Section {name {label {}}} {
           77  +  puts "\n<hr />"
           78  +  if {$label!=""} {
           79  +    puts "<a name=\"$label\">"
           80  +  }
           81  +  puts "<h1>$name</h1>\n"
           82  +}
           83  +
           84  +proc Example {text} {
           85  +  puts "<blockquote><pre>$text</pre></blockquote>"
           86  +}
           87  +
           88  +Section {CREATE TABLE} {createtable}
           89  +
    56     90   Syntax {sql-command} {
    57     91   CREATE TABLE <table-name> (
    58     92     <column-def> [, <column-def>]*
    59     93     [, <constraint>]*
    60     94   )
    61     95   } {column-def} {
    62     96   <name> <type> [<column-constraint>]*
................................................................................
    64     98   <typename> |
    65     99   <typename> ( <number> ) |
    66    100   <typename> ( <number> , <number> )
    67    101   } {column-constraint} {
    68    102   NOT NULL |
    69    103   PRIMARY KEY [<sort-order>] |
    70    104   UNIQUE |
    71         -CHECK ( <expr> )
          105  +CHECK ( <expr> ) |
          106  +DEFAULT <value>
    72    107   } {constraint} {
    73    108   PRIMARY KEY ( <name> [, <name>]* ) |
    74    109   UNIQUE ( <name> [, <name>]* ) |
    75    110   CHECK ( <expr> )
    76    111   }
    77    112   
    78    113   puts {
................................................................................
    83    118   is the name of the table that records the database schema.</p>
    84    119   
    85    120   <p>Each column definition is the name of the column followed by the
    86    121   datatype for that column, then one or more optional column constraints.
    87    122   The datatype for the column is ignored.  All information
    88    123   is stored as null-terminated strings.  The constraints are also ignored,
    89    124   except that the PRIMARY KEY constraint will cause an index to be automatically
    90         -created that implements the primary key.  The name of the primary
          125  +created that implements the primary key and the DEFAULT constraint
          126  +which specifies a default value to use when doing an INSERT.
          127  +The name of the primary
    91    128   key index will be the table name
    92    129   with "<b>__primary_key</b>" appended.  The index used for a primary key
    93    130   does not show up in the <b>sqlite_master</b> table, but a GDBM file is
    94    131   created for that index.</p>
    95    132   
    96    133   <p>There are no arbitrary limits on the size of columns, on the number
    97    134   of columns, or on the number of constraints in a table.</p>
................................................................................
    99    136   <p>The exact text
   100    137   of each CREATE TABLE statement is stored in the <b>sqlite_master</b>
   101    138   table.  Everytime the database is opened, all CREATE TABLE statements
   102    139   are read from the <b>sqlite_master</b> table and used to regenerate
   103    140   SQLite's internal representation of the table layout.</p>
   104    141   }
   105    142   
   106         -puts {<h2>CREATE INDEX</h2>
   107         -}
          143  +Section {CREATE INDEX} createindex
   108    144   
   109    145   Syntax {sql-statement} {
   110    146   CREATE INDEX <index-name> 
   111    147   ON <table-name> ( <column-name> [, <column-name>]* )
   112    148   } {column-name} {
   113    149   <name> [ ASC | DESC ]
   114    150   }
................................................................................
   126    162   attached to a single table, nor on the number of columns in an index.</p>
   127    163   
   128    164   <p>The exact text
   129    165   of each CREATE INDEX statement is stored in the <b>sqlite_master</b>
   130    166   table.  Everytime the database is opened, all CREATE INDEX statements
   131    167   are read from the <b>sqlite_master</b> table and used to regenerate
   132    168   SQLite's internal representation of the index layout.</p>
   133         -
   134         -<h2>DROP TABLE</h2>
   135    169   }
   136    170   
          171  +
          172  +Section {DROP TABLE} droptable
          173  +
   137    174   Syntax {sql-command} {
   138    175   DROP TABLE <table-name>
   139    176   }
   140    177   
   141    178   puts {
   142    179   <p>The DROP TABLE statement consists of the keywords "DROP TABLE" followed
   143    180   by the name of the table.  The table named is completely removed from
   144    181   the disk.  The table can not be recovered.  All indices associated with
   145         -the table are also reversibly deleted.</p>
          182  +the table are also reversibly deleted.</p>}
   146    183   
   147         -<h2>DROP INDEX</h2>
   148         -}
          184  +Section {DROP INDEX} dropindex
   149    185   
   150    186   Syntax {sql-command} {
   151    187   DROP INDEX <index-name>
   152    188   }
   153    189   
   154    190   puts {
   155    191   <p>The DROP INDEX statement consists of the keywords "DROP INDEX" followed
   156    192   by the name of the index.  The index named is completely removed from
   157    193   the disk.  The only way to recover the index is to reenter the
   158    194   appropriate CREATE INDEX command.</p>
          195  +}
   159    196   
   160         -<h2>VACUUM</h2>
   161         -}
          197  +Section VACUUM vacuum
   162    198   
   163    199   Syntax {sql-statement} {
   164    200   VACUUM [<index-or-table-name>]
   165    201   }
   166    202   
   167    203   puts {
   168    204   <p>The VACUUM command is an SQLite extension modelled after a similar
................................................................................
   172    208   then <b>gdbm_reorganize()</b> is call on every GDBM file in the database.</p>
   173    209   
   174    210   <p>It is a good idea to run VACUUM after creating large indices,
   175    211   especially indices where a single index value refers to many
   176    212   entries in the data table.  Reorganizing these indices will make
   177    213   the underlying GDBM file much smaller and will help queries to
   178    214   run much faster.</p>
          215  +}
          216  +
          217  +Section INSERT insert
          218  +
          219  +Syntax {sql-statement} {
          220  +INSERT INTO <table-name> [( <column-list> )] VALUES ( <value-list> ) |
          221  +INSERT INTO <table-name> [( <column-list> )] <select-statement>
          222  +}
   179    223   
          224  +puts {
          225  +<p>The INSERT statement comes in two basic forms.  The first form
          226  +(with the "VALUES" keyword) creates a single new row in an existing table.
          227  +If no column-list is specified then the number of values must
          228  +be the same as the number of columns in the table.  If a column-list
          229  +is specified, then the number of values must match the number of
          230  +specified columns
          231  +</p>
   180    232   }
   181    233   
   182    234   puts {
   183    235   <p><hr /></p>
   184    236   <p><a href="index.html"><img src="/goback.jpg" border=0 />
   185    237   Back to the SQLite Home Page</a>
   186    238   </p>
   187    239   
   188    240   </body></html>}

Added www/opcode.tcl.

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