tclsh $(TOP)/www/changes.tcl >changes.html

fileformat.html:	$(TOP)/www/fileformat.tcl
	tclsh $(TOP)/www/fileformat.tcl >fileformat.html

lang.html:	$(TOP)/www/lang.tcl
	tclsh $(TOP)/www/lang.tcl >lang.html











# Files to be published on the website.
#
PUBLISH = \
  sqlite.tar.gz \
  index.html \
  sqlite.html \
  changes.html \
  fileformat.html \
  lang.html \



  c_interface.html

website:	$(PUBLISH)

publish:	$(PUBLISH)
	scp $(PUBLISH) hwaci@oak.he.net:public_html/sw/sqlite

clean:	
	rm -f *.o sqlite libsqlite.a sqlite.h
	rm -f lemon lempar.c parse.* sqlite.tar.gz
	rm -f $(PUBLISH)
	rm -f *.da *.bb *.bbg gmon.out

	tclsh $(TOP)/www/changes.tcl >changes.html

fileformat.html:	$(TOP)/www/fileformat.tcl
	tclsh $(TOP)/www/fileformat.tcl >fileformat.html

lang.html:	$(TOP)/www/lang.tcl
	tclsh $(TOP)/www/lang.tcl >lang.html

arch.html:	$(TOP)/www/arch.tcl
	tclsh $(TOP)/www/arch.tcl >arch.html

arch.png:	$(TOP)/www/arch.png
	cp $(TOP)/www/arch.png .

opcode.html:	$(TOP)/www/opcode.tcl
	tclsh $(TOP)/www/opcode.tcl $(TOP)/src/vdbe.c >opcode.html


# Files to be published on the website.
#
PUBLISH = \
  sqlite.tar.gz \
  index.html \
  sqlite.html \
  changes.html \
  fileformat.html \
  lang.html \
  opcode.html \
  arch.html \
  arch.png \
  c_interface.html

website:	$(PUBLISH)

publish:	$(PUBLISH)
	scp $(PUBLISH) hwaci@oak.he.net:public_html/sw/sqlite

clean:	
	rm -f *.o sqlite libsqlite.a sqlite.h
	rm -f lemon lempar.c parse.* sqlite.tar.gz
	rm -f $(PUBLISH)
	rm -f *.da *.bb *.bbg gmon.out

** inplicit conversion from one type to the other occurs as necessary.
** 
** Most of the code in this file is taken up by the sqliteVdbeExec()
** function which does the work of interpreting a VDBE program.
** But other routines are also provided to help in building up
** a program instruction by instruction.
**
** $Id: vdbe.c,v 1.30 2000/06/08 16:26:25 drh Exp $
*/
#include "sqliteInt.h"
#include <unistd.h>

/*
** SQL is translated into a sequence of instructions to be
** executed by a virtual machine.  Each instruction is an instance
................................................................................
        if( z==0 ) z = "";
        p->zStack[i] = z;
        p->aStack[i].n = strlen(z) + 1;
        p->aStack[i].flags = STK_Str;
        break;
      }

      /* Opcode: NULL * * *
      **
      ** Push a NULL value onto the stack.
      */
      case OP_Null: {
        int i = ++p->tos;
        if( NeedStack(p, p->tos) ) goto no_mem;
        p->zStack[i] = 0;
................................................................................
      }

      /* Opcode: KeyAsData P1 P2 *
      **
      ** Turn the key-as-data mode for cursor P1 either on (if P2==1) or
      ** off (if P2==0).  In key-as-data mode, the OP_Fetch opcode pulls
      ** data off of the key rather than the data.  This is useful for
      ** outer joins and stuff...
      */
      case OP_KeyAsData: {
        int i = pOp->p1;
        if( i>=0 && i<p->nTable && p->aTab[i].pTable!=0 ){
          p->aTab[i].keyAsData = pOp->p2;
        }
        break;
................................................................................
      /* Opcode: Field P1 P2 *
      **
      ** Push onto the stack the value of the P2-th field from the
      ** most recent Fetch from table P1.
      ** 
      ** The value pushed is just a pointer to the data in the cursor.
      ** The value will go away the next time a record is fetched from P1,
      ** or when P1 is closed.  Make a copy of the string if it needs
      ** to persist longer than that.




      */
      case OP_Field: {
        int *pAddr;
        int amt;
        int i = pOp->p1;
        int p2 = pOp->p2;
        int tos = ++p->tos;
................................................................................
      **
      ** Pop the top of the stack and use that as an aggregator key.  If
      ** an aggregator with that same key already exists, then make the
      ** aggregator the current aggregator and jump to P2.  If no aggregator
      ** with the given key exists, create one and make it current but
      ** do not jump.
      **
      ** This opcode should not be executed after an AggNext but before
      ** the next AggReset.



      */
      case OP_AggFocus: {
        int tos = p->tos;
        AggElem *pElem;
        char *zKey;
        int nKey;

................................................................................
        PopStack(p, 1);
        break;
      }

      /* Opcode: AggGet * P2 *
      **
      ** Push a new entry onto the stack which is a copy of the P2-th field
      ** of the current aggregate.  String are not duplicated so
      ** string values will be ephemeral.  
      */
      case OP_AggGet: {
        AggElem *pFocus = AggInFocus(p->agg);
        int i = pOp->p2;
        int tos = ++p->tos;
        if( NeedStack(p, tos) ) goto no_mem;
        if( pFocus==0 ) goto no_mem;
................................................................................

      /* Opcode: AggNext * P2 *
      **
      ** Make the next aggregate value the current aggregate.  The prior
      ** aggregate is deleted.  If all aggregate values have been consumed,
      ** jump to P2.
      **
      ** Do not execute an AggFocus after this opcode until after the
      ** next AggReset.



      */
      case OP_AggNext: {
        if( p->agg.nHash ){
          p->agg.nHash = 0;
          sqliteFree(p->agg.apHash);
          p->agg.apHash = 0;
          p->agg.pCurrent = p->agg.pFirst;
................................................................................
          pc = pOp->p2-1;
        }
        break;
      }

      /* Opcode: SetClear P1 * *
      **
      ** Remove all elements from the given Set.
      */
      case OP_SetClear: {
        int i = pOp->p1;
        if( i>=0 && i<p->nSet ){
          SetClear(&p->aSet[i]);
        }
        break;
      }

      /* Opcode: SetInsert P1 * P3
      **
      ** If Set p1 does not exist then create it.  Then insert value
      ** P3 into that set.  If P3 is NULL, then insert the top of the
      ** stack into the set.
      */
      case OP_SetInsert: {
        int i = pOp->p1;
        if( p->nSet<=i ){
          p->aSet = sqliteRealloc(p->aSet, (i+1)*sizeof(p->aSet[0]) );

** inplicit conversion from one type to the other occurs as necessary.
** 
** Most of the code in this file is taken up by the sqliteVdbeExec()
** function which does the work of interpreting a VDBE program.
** But other routines are also provided to help in building up
** a program instruction by instruction.
**
** $Id: vdbe.c,v 1.31 2000/06/09 01:58:37 drh Exp $
*/
#include "sqliteInt.h"
#include <unistd.h>

/*
** SQL is translated into a sequence of instructions to be
** executed by a virtual machine.  Each instruction is an instance
................................................................................
        if( z==0 ) z = "";
        p->zStack[i] = z;
        p->aStack[i].n = strlen(z) + 1;
        p->aStack[i].flags = STK_Str;
        break;
      }

      /* Opcode: Null * * *
      **
      ** Push a NULL value onto the stack.
      */
      case OP_Null: {
        int i = ++p->tos;
        if( NeedStack(p, p->tos) ) goto no_mem;
        p->zStack[i] = 0;
................................................................................
      }

      /* Opcode: KeyAsData P1 P2 *
      **
      ** Turn the key-as-data mode for cursor P1 either on (if P2==1) or
      ** off (if P2==0).  In key-as-data mode, the OP_Fetch opcode pulls
      ** data off of the key rather than the data.  This is useful for
      ** processing compound selects.
      */
      case OP_KeyAsData: {
        int i = pOp->p1;
        if( i>=0 && i<p->nTable && p->aTab[i].pTable!=0 ){
          p->aTab[i].keyAsData = pOp->p2;
        }
        break;
................................................................................
      /* Opcode: Field P1 P2 *
      **
      ** Push onto the stack the value of the P2-th field from the
      ** most recent Fetch from table P1.
      ** 
      ** The value pushed is just a pointer to the data in the cursor.
      ** The value will go away the next time a record is fetched from P1,
      ** or when P1 is closed.  Make a copy of the string (using
      ** "Concat 1 0 0" if it needs to persist longer than that.
      **
      ** If the KeyAsData opcode has previously executed on this cursor,
      ** then the field might be extracted from the key rather than the
      ** data.
      */
      case OP_Field: {
        int *pAddr;
        int amt;
        int i = pOp->p1;
        int p2 = pOp->p2;
        int tos = ++p->tos;
................................................................................
      **
      ** Pop the top of the stack and use that as an aggregator key.  If
      ** an aggregator with that same key already exists, then make the
      ** aggregator the current aggregator and jump to P2.  If no aggregator
      ** with the given key exists, create one and make it current but
      ** do not jump.
      **
      ** The order of aggregator opcodes is important.  The order is:
      ** AggReset AggFocus AggNext.  In other words, you must execute
      ** AggReset first, then zero or more AggFocus operations, then
      ** zero or more AggNext operations.  You must not execute an AggFocus
      ** in between an AggNext and an AggReset.
      */
      case OP_AggFocus: {
        int tos = p->tos;
        AggElem *pElem;
        char *zKey;
        int nKey;

................................................................................
        PopStack(p, 1);
        break;
      }

      /* Opcode: AggGet * P2 *
      **
      ** Push a new entry onto the stack which is a copy of the P2-th field
      ** of the current aggregate.  Strings are not duplicated so
      ** string values will be ephemeral.
      */
      case OP_AggGet: {
        AggElem *pFocus = AggInFocus(p->agg);
        int i = pOp->p2;
        int tos = ++p->tos;
        if( NeedStack(p, tos) ) goto no_mem;
        if( pFocus==0 ) goto no_mem;
................................................................................

      /* Opcode: AggNext * P2 *
      **
      ** Make the next aggregate value the current aggregate.  The prior
      ** aggregate is deleted.  If all aggregate values have been consumed,
      ** jump to P2.
      **
      ** The order of aggregator opcodes is important.  The order is:
      ** AggReset AggFocus AggNext.  In other words, you must execute
      ** AggReset first, then zero or more AggFocus operations, then
      ** zero or more AggNext operations.  You must not execute an AggFocus
      ** in between an AggNext and an AggReset.
      */
      case OP_AggNext: {
        if( p->agg.nHash ){
          p->agg.nHash = 0;
          sqliteFree(p->agg.apHash);
          p->agg.apHash = 0;
          p->agg.pCurrent = p->agg.pFirst;
................................................................................
          pc = pOp->p2-1;
        }
        break;
      }

      /* Opcode: SetClear P1 * *
      **
      ** Remove all elements from the P1-th Set.
      */
      case OP_SetClear: {
        int i = pOp->p1;
        if( i>=0 && i<p->nSet ){
          SetClear(&p->aSet[i]);
        }
        break;
      }

      /* Opcode: SetInsert P1 * P3
      **
      ** If Set P1 does not exist then create it.  Then insert value
      ** P3 into that set.  If P3 is NULL, then insert the top of the
      ** stack into the set.
      */
      case OP_SetInsert: {
        int i = pOp->p1;
        if( p->nSet<=i ){
          p->aSet = sqliteRealloc(p->aSet, (i+1)*sizeof(p->aSet[0]) );

#FIG 3.2
Landscape
Center
Inches
Letter  
100.00
Single
-2
1200 2
2 2 0 1 0 7 100 0 -1 0.000 0 0 -1 0 0 5
	 2550 2250 4875 2250 4875 3525 2550 3525 2550 2250
2 2 0 1 0 7 100 0 -1 0.000 0 0 -1 0 0 5
	 2550 4050 4875 4050 4875 5325 2550 5325 2550 4050
2 2 0 1 0 7 100 0 -1 0.000 0 0 -1 0 0 5
	 2550 5850 4875 5850 4875 7125 2550 7125 2550 5850
2 2 0 1 0 7 100 0 -1 0.000 0 0 -1 0 0 5
	 2550 7650 4875 7650 4875 8925 2550 8925 2550 7650
2 2 0 1 0 7 100 0 -1 0.000 0 0 -1 0 0 5
	 2550 450 4875 450 4875 1725 2550 1725 2550 450
2 2 0 1 0 7 100 0 -1 0.000 0 0 -1 0 0 5
	 2550 9450 4875 9450 4875 10725 2550 10725 2550 9450
2 1 0 3 0 7 100 0 -1 0.000 0 0 -1 1 0 2
	1 1 3.00 75.00 135.00
	 3675 1725 3675 2250
2 1 0 3 0 7 100 0 -1 0.000 0 0 -1 1 0 2
	1 1 3.00 75.00 135.00
	 3675 3525 3675 4050
2 1 0 3 0 7 100 0 -1 0.000 0 0 -1 1 0 2
	1 1 3.00 75.00 135.00
	 3675 5325 3675 5850
2 1 0 3 0 7 100 0 -1 0.000 0 0 -1 1 0 2
	1 1 3.00 75.00 135.00
	 3675 7125 3675 7650
2 1 0 3 0 7 100 0 -1 0.000 0 0 -1 1 0 2
	1 1 3.00 75.00 135.00
	 3675 8925 3675 9450
4 1 0 100 0 0 20 0.0000 4 195 1920 3675 8025 Virtual Machine\001
4 1 0 100 0 0 20 0.0000 4 195 1830 3675 6225 Code Generator\001
4 1 0 100 0 0 20 0.0000 4 195 735 3675 4350 Parser\001
4 1 0 100 0 0 20 0.0000 4 195 1140 3675 2550 Tokenizer\001
4 1 0 100 0 0 20 0.0000 4 195 1020 3675 750 Interface\001
4 1 0 100 0 0 20 0.0000 4 195 990 3675 9825 Backend\001
4 1 0 100 0 0 14 0.0000 4 150 570 3675 10650 dbbe.c\001
4 1 0 100 0 0 14 0.0000 4 150 570 3675 8850 vdbe.c\001
4 1 0 100 0 0 14 0.0000 4 195 2190 3675 7050 select.c update.c where.c\001
4 1 0 100 0 0 14 0.0000 4 195 1860 3675 6825 build.c delete.c expr.c\001
4 1 0 100 0 0 14 0.0000 4 150 630 3675 5250 parse.y\001
4 1 0 100 0 0 14 0.0000 4 150 870 3675 3450 tokenize.c\001
4 1 0 100 0 0 14 0.0000 4 150 570 3675 1575 main.c\001

#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: arch.tcl,v 1.1 2000/06/09 01:58:51 drh Exp $}

puts {<html>
<head>
  <title>Architecture of SQLite</title>
</head>
<body bgcolor=white>
<h1 align=center>
The Architecture Of SQLite
</h1>}
puts "<p align=center>
(This page was last modified on [lrange $rcsid 3 4] GMT)
</p>"

puts {
<h2>Introduction</h2>

<table align="right" border="1" cellpadding="15" cellspacing="1">
<tr><th>Block Diagram Of SQLite</th></tr>
<tr><td><img src="arch.png"></td></tr>
</table>
<p>This file describes the architecture of the SQLite library.
A block diagram showing the main components of SQLite
and how that interrelate is shown at the right.  The text that
follows will provide a quick overview of each of these components.
</p>

<h2>Interface</h2>

<p>The public interface to the SQLite library is implemented by
four functions found in the <b>main.c</b> source file.  Additional
information on the C interface to SQLite is
<a href="c_interface.html">available separately</a>.<p>

<p>To avoid name collisions with other software, all external
symbols in the SQLite library begin with the prefix <b>sqlite</b>.
Those symbols that are intended for external use (as oppose to
those which are for internal use only but which have to be exported
do to limitations of the C linker's scoping mechanism) begin
with <b>sqlite_</b>.</p>

<h2>Tokenizer</h2>

<p>When a string containing SQL statements is to be executed, the
interface passes that string to the tokenizer.  The job of the tokenizer
is to break the original string up into tokens and pass those tokens
one by one to the parser.  The tokenizer is hand-coded in C.
(There is no "lex" code here.)  All of the code for the tokenizer
is contained in the <b>tokenize.c</b> source file.</p>

<p>Note that in this design, the tokenizer calls the parser.  People
who are familiar with YACC and BISON may be used to doing things the
other way around -- having the parser call the tokenizer.  This author
as done it both ways, and finds things generally work out nicer for
the tokenizer to call the parser.  YACC has it backwards.</p>

<h2>Parser</h2>

<p>The parser is the piece that assigns meaning to tokens based on
their context.  The parser for SQLite is generated using the
<a href="http://www.hwaci.com/sw/lemon/">Lemon</a> LALR(1) parser
generator.  Lemon does the same job as YACC/BISON, but is uses
a different input syntax which is less error-prone than the 
clumsy YACC/BISON syntax.  
Lemon also generates a parser which is reentrant and thread-safe.
And lemon defines the concept of a non-terminal destructor so
that it does not leak memory when syntax errors are encountered.
The source file that drives Lemon is found in <b>parse.y</b>.</p>

<p>Because
lemon is a program not normally found on development machines, the
complete source code to lemon (just one C file) is included in the
SQLite distribution in the "tool" subdirectory.  Documentation on
lemon is found in the "doc" subdirectory of the distribution.
</p>

<h2>Code Generator</h2>

<p>After the parser assemblies tokens into complete SQL statements,
it calls the code generator to produce virtual machine code that
will do the work that the SQL statements request.  There are six
files in the code generator:  <b>build.c</b>, <b>delete.c</b>,
<b>expr.c</b>, <b>select.c</b>, <b>update.c</b>, and <b>where.c</b>.
In these files is where most of the serious magic happens.</p>

<h2>Virtual Machine</h2>

<p>The program generated by the code generator is executed by
the virtual machine.  Additional information about the virtual
machine is <a href="opcode.html">available separately</a>.
To summarize, the virtual machine implements an abstract computing
engine specifically designed to manipulate database files.  The
machine as a stack.  Each instruction contains an opcode and
up to three additional operands.</p>

<p>The virtual machine is entirely contained in a single
source file <b>vdbe.c</b>.  The virtual machine also has
its own header file <b>vdbe.h</b> that defines an interface
between the virtual machine and the rest of the SQLite library.</p>

<h2>Backend</h2>

<p>The last layer in the design of SQLite is the backend.  The
backend implements an interface between the virtual machine and
the underlying data file library -- GDBM in this case.  The interface
is designed to make it easy to substitute a different database
library, such as the Berkeley DB.  
The backend abstracts many of the low-level details to help
reduce the complexity of the virtual machine.</p>

<p>The backend is contained in the single source file <b>dbbe.c</b>.
The backend also has a header file <b>dbbe.h</b> that defines the
interface between the backend and the rest of the SQLite library.</p>
}

puts {
<br clear="both" />
<p><hr /></p>
<p><a href="index.html"><img src="/goback.jpg" border=0 />
Back to the SQLite Home Page</a>
</p>

</body></html>}

#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: lang.tcl,v 1.1 2000/06/08 21:53:06 drh Exp $}

puts {<html>
<head>
  <title>Query Language Understood By SQLite</title>
</head>
<body bgcolor=white>
<h1 align=center>
................................................................................
bold blue.  Non-terminal symbols are shown in italic red.  Operators
that are part of the syntactic markup itself are shown in black roman.</p>

<p>This document is just an overview of the SQL syntax implemented
by SQLite.  Many low-level productions are omitted.  For detailed information
on the language that SQLite understands, refer to the source code.</p>







<h2>CREATE TABLE</h2>












<p>The basic structure of a CREATE TABLE statement is as follows:</p>




}

proc Syntax {args} {
  puts {<table cellpadding="15">}
  foreach {rule body} $args {
    puts "<tr><td align=\"right\" valign=\"top\">"
    puts "<i><font color=\"#ff3434\">$rule</font></i>&nbsp;::=</td>"
................................................................................
    regsub -all "\n  *" $body "\n\\&nbsp;\\&nbsp;\\&nbsp;\\&nbsp;" body
    regsub -all {[|,*()]} $body {<big>&</big>} body
    puts "<td><b><font color=\"#2c2cf0\">$body</font></b></td></tr>"
  }
  puts {</table>}
}















Syntax {sql-command} {
CREATE TABLE <table-name> (
  <column-def> [, <column-def>]*
  [, <constraint>]*
)
} {column-def} {
<name> <type> [<column-constraint>]*
................................................................................
<typename> |
<typename> ( <number> ) |
<typename> ( <number> , <number> )
} {column-constraint} {
NOT NULL |
PRIMARY KEY [<sort-order>] |
UNIQUE |
CHECK ( <expr> )

} {constraint} {
PRIMARY KEY ( <name> [, <name>]* ) |
UNIQUE ( <name> [, <name>]* ) |
CHECK ( <expr> )
}

puts {
................................................................................
is the name of the table that records the database schema.</p>

<p>Each column definition is the name of the column followed by the
datatype for that column, then one or more optional column constraints.
The datatype for the column is ignored.  All information
is stored as null-terminated strings.  The constraints are also ignored,
except that the PRIMARY KEY constraint will cause an index to be automatically
created that implements the primary key.  The name of the primary


key index will be the table name
with "<b>__primary_key</b>" appended.  The index used for a primary key
does not show up in the <b>sqlite_master</b> table, but a GDBM file is
created for that index.</p>

<p>There are no arbitrary limits on the size of columns, on the number
of columns, or on the number of constraints in a table.</p>
................................................................................
<p>The exact text
of each CREATE TABLE statement is stored in the <b>sqlite_master</b>
table.  Everytime the database is opened, all CREATE TABLE statements
are read from the <b>sqlite_master</b> table and used to regenerate
SQLite's internal representation of the table layout.</p>
}

puts {<h2>CREATE INDEX</h2>
}

Syntax {sql-statement} {
CREATE INDEX <index-name> 
ON <table-name> ( <column-name> [, <column-name>]* )
} {column-name} {
<name> [ ASC | DESC ]
}
................................................................................
attached to a single table, nor on the number of columns in an index.</p>

<p>The exact text
of each CREATE INDEX statement is stored in the <b>sqlite_master</b>
table.  Everytime the database is opened, all CREATE INDEX statements
are read from the <b>sqlite_master</b> table and used to regenerate
SQLite's internal representation of the index layout.</p>

<h2>DROP TABLE</h2>
}



Syntax {sql-command} {
DROP TABLE <table-name>
}

puts {
<p>The DROP TABLE statement consists of the keywords "DROP TABLE" followed
by the name of the table.  The table named is completely removed from
the disk.  The table can not be recovered.  All indices associated with
the table are also reversibly deleted.</p>

<h2>DROP INDEX</h2>
}

Syntax {sql-command} {
DROP INDEX <index-name>
}

puts {
<p>The DROP INDEX statement consists of the keywords "DROP INDEX" followed
by the name of the index.  The index named is completely removed from
the disk.  The only way to recover the index is to reenter the
appropriate CREATE INDEX command.</p>

<h2>VACUUM</h2>
}


Syntax {sql-statement} {
VACUUM [<index-or-table-name>]
}

puts {
<p>The VACUUM command is an SQLite extension modelled after a similar
................................................................................
then <b>gdbm_reorganize()</b> is call on every GDBM file in the database.</p>

<p>It is a good idea to run VACUUM after creating large indices,
especially indices where a single index value refers to many
entries in the data table.  Reorganizing these indices will make
the underlying GDBM file much smaller and will help queries to
run much faster.</p>

















}

puts {
<p><hr /></p>
<p><a href="index.html"><img src="/goback.jpg" border=0 />
Back to the SQLite Home Page</a>
</p>

</body></html>}

#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: lang.tcl,v 1.2 2000/06/09 01:58:37 drh Exp $}

puts {<html>
<head>
  <title>Query Language Understood By SQLite</title>
</head>
<body bgcolor=white>
<h1 align=center>
................................................................................
bold blue.  Non-terminal symbols are shown in italic red.  Operators
that are part of the syntactic markup itself are shown in black roman.</p>

<p>This document is just an overview of the SQL syntax implemented
by SQLite.  Many low-level productions are omitted.  For detailed information
on the language that SQLite understands, refer to the source code.</p>


<p>SQLite implements the follow SQL commands:</p>
<p><ul>
}

foreach {section} [lsort -index 0 -dictionary {
  {{CREATE TABLE} createtable}
  {{CREATE INDEX} createindex}
  {VACUUM vacuum}
  {{DROP TABLE} droptable}
  {{DROP INDEX} dropindex}
  {INSERT insert}
  {DELETE delete}
  {UPDATE update}
  {SELECT select}
  {COPY copy}
}] {
  puts "<li><a href=\"#[lindex $section 1]\">[lindex $section 0]</a></li>"
}

puts {</ul></p>

<p>Details on the implementation of each command are provided in
the sequel.</p>
}

proc Syntax {args} {
  puts {<table cellpadding="15">}
  foreach {rule body} $args {
    puts "<tr><td align=\"right\" valign=\"top\">"
    puts "<i><font color=\"#ff3434\">$rule</font></i>&nbsp;::=</td>"
................................................................................
    regsub -all "\n  *" $body "\n\\&nbsp;\\&nbsp;\\&nbsp;\\&nbsp;" body
    regsub -all {[|,*()]} $body {<big>&</big>} body
    puts "<td><b><font color=\"#2c2cf0\">$body</font></b></td></tr>"
  }
  puts {</table>}
}

proc Section {name {label {}}} {
  puts "\n<hr />"
  if {$label!=""} {
    puts "<a name=\"$label\">"
  }
  puts "<h1>$name</h1>\n"
}

proc Example {text} {
  puts "<blockquote><pre>$text</pre></blockquote>"
}

Section {CREATE TABLE} {createtable}

Syntax {sql-command} {
CREATE TABLE <table-name> (
  <column-def> [, <column-def>]*
  [, <constraint>]*
)
} {column-def} {
<name> <type> [<column-constraint>]*
................................................................................
<typename> |
<typename> ( <number> ) |
<typename> ( <number> , <number> )
} {column-constraint} {
NOT NULL |
PRIMARY KEY [<sort-order>] |
UNIQUE |
CHECK ( <expr> ) |
DEFAULT <value>
} {constraint} {
PRIMARY KEY ( <name> [, <name>]* ) |
UNIQUE ( <name> [, <name>]* ) |
CHECK ( <expr> )
}

puts {
................................................................................
is the name of the table that records the database schema.</p>

<p>Each column definition is the name of the column followed by the
datatype for that column, then one or more optional column constraints.
The datatype for the column is ignored.  All information
is stored as null-terminated strings.  The constraints are also ignored,
except that the PRIMARY KEY constraint will cause an index to be automatically
created that implements the primary key and the DEFAULT constraint
which specifies a default value to use when doing an INSERT.
The name of the primary
key index will be the table name
with "<b>__primary_key</b>" appended.  The index used for a primary key
does not show up in the <b>sqlite_master</b> table, but a GDBM file is
created for that index.</p>

<p>There are no arbitrary limits on the size of columns, on the number
of columns, or on the number of constraints in a table.</p>
................................................................................
<p>The exact text
of each CREATE TABLE statement is stored in the <b>sqlite_master</b>
table.  Everytime the database is opened, all CREATE TABLE statements
are read from the <b>sqlite_master</b> table and used to regenerate
SQLite's internal representation of the table layout.</p>
}

Section {CREATE INDEX} createindex


Syntax {sql-statement} {
CREATE INDEX <index-name> 
ON <table-name> ( <column-name> [, <column-name>]* )
} {column-name} {
<name> [ ASC | DESC ]
}
................................................................................
attached to a single table, nor on the number of columns in an index.</p>

<p>The exact text
of each CREATE INDEX statement is stored in the <b>sqlite_master</b>
table.  Everytime the database is opened, all CREATE INDEX statements
are read from the <b>sqlite_master</b> table and used to regenerate
SQLite's internal representation of the index layout.</p>
}



Section {DROP TABLE} droptable

Syntax {sql-command} {
DROP TABLE <table-name>
}

puts {
<p>The DROP TABLE statement consists of the keywords "DROP TABLE" followed
by the name of the table.  The table named is completely removed from
the disk.  The table can not be recovered.  All indices associated with
the table are also reversibly deleted.</p>}

Section {DROP INDEX} dropindex


Syntax {sql-command} {
DROP INDEX <index-name>
}

puts {
<p>The DROP INDEX statement consists of the keywords "DROP INDEX" followed
by the name of the index.  The index named is completely removed from
the disk.  The only way to recover the index is to reenter the
appropriate CREATE INDEX command.</p>
}


Section VACUUM vacuum

Syntax {sql-statement} {
VACUUM [<index-or-table-name>]
}

puts {
<p>The VACUUM command is an SQLite extension modelled after a similar
................................................................................
then <b>gdbm_reorganize()</b> is call on every GDBM file in the database.</p>

<p>It is a good idea to run VACUUM after creating large indices,
especially indices where a single index value refers to many
entries in the data table.  Reorganizing these indices will make
the underlying GDBM file much smaller and will help queries to
run much faster.</p>
}

Section INSERT insert

Syntax {sql-statement} {
INSERT INTO <table-name> [( <column-list> )] VALUES ( <value-list> ) |
INSERT INTO <table-name> [( <column-list> )] <select-statement>
}

puts {
<p>The INSERT statement comes in two basic forms.  The first form
(with the "VALUES" keyword) creates a single new row in an existing table.
If no column-list is specified then the number of values must
be the same as the number of columns in the table.  If a column-list
is specified, then the number of values must match the number of
specified columns
</p>
}

puts {
<p><hr /></p>
<p><a href="index.html"><img src="/goback.jpg" border=0 />
Back to the SQLite Home Page</a>
</p>

</body></html>}

#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: opcode.tcl,v 1.1 2000/06/09 01:58:37 drh Exp $}

puts {<html>
<head>
  <title>SQLite Virtual Machine Opcodes</title>
</head>
<body bgcolor=white>
<h1 align=center>
SQLite Virtual Machine Opcodes
</h1>}
puts "<p align=center>
(This page was last modified on [lrange $rcsid 3 4] GMT)
</p>"

set fd [open [lindex $argv 0] r]
set file [read $fd [file size [lindex $argv 0]]]
close $fd
set current_op {}
foreach line [split $file \n] {
  set line [string trim $line]
  if {[string index $line 1]!="*"} {
    set current_op {}
    continue
  }
  if {[regexp {^/\* Opcode: } $line]} {
    set current_op [lindex $line 2]
    set Opcode($current_op:args) [lrange $line 3 end]
    lappend OpcodeList $current_op
    continue
  }
  if {$current_op==""} continue
  if {[regexp {^\*/} $line]} {
    set current_op {}
    continue
  }
  set line [string trim [string range $line 3 end]]
  if {$line==""} {
    append Opcode($current_op:text) \n<p>
  } else {
    append Opcode($current_op:text) \n$line
  }
}
unset file

puts {
<h2>Introduction</h2>

<p>In order to execute an SQL statement, the SQLite library first parses
the SQL, analyzes the statement, then generates a short program to execute
the statement.  The program is generated for a "virtual machine" implemented
by the SQLite library.  The document describes the operation of that
virtual machine.</p>

<p>The source code to the virtual machine is in the <b>vdbe.c</b> source
file.  All of the opcode definitions further down in this document are
contained in comments in the source file.  In fact, the opcode table
in this document
was generated by scanning the <b>vdbe.c</b> source file 
and extracting the necessary information from comments.  So the 
source code comments are really the canonical source of information
about the virtual macchine.  When in doubt, refer to the source code.</p>

<p>Each instruction in the virtual machine consists of an opcode and
up to three operands named P1, P2 and P3.  P1 may be an arbitrary
integer.  P2 must be a non-negative integer.  P2 is always the
jump destination in any operation that might cause a jump.
P3 is a null-terminated
string or NULL.  Some operators use all three operands.  Some use
one or two.  Some operators use none of the operands.<p>

<p>The virtual machine begins execution on instruction number 0.
Execution continues until (1) a Halt instruction is seen, or 
(2) the program counter becomes one greater than the address of
last instruction, or (3) there is an execution error.
When the virtual machine halts, all memory
that it allocated is released and all database files it may
have had open are closed.</p>

<p>The virtual machine also contains an operand stack of unlimited
depth.  Many of the opcodes use operands from the stack.  The details
are described in the descriptions of each opcode.</p>

<p>The virtual machine can have zero or more cursors.  Each cursor
is a pointer into a single GDBM file.  There can be multiple
cursors pointing at the same file.
All cursors operate independenly.
The only way for the virtual machine to interact with a GDBM
file is through a cursor.
Instructions in the virtual
machine can create a new cursor (Open), read data from a cursor
(Field), advance the cursor to the next entry in the GDBM file
(Next), and many other operations.  All cursors are automatically
closed when the virtual machine terminates.</p>

<p>The virtual machine contains an arbitrary number of fixed memory
locations with addresses beginning at zero and growing upward.
Each memory location can hold an arbitrary string.  The memory
cells are typically used to hold the result of a scalar SELECT
that is part of a larger expression.</p>

<p>The virtual machine contains an arbitrary number of sorters.
Each sorter is able to accumulate records, sort those records,
then play the records back in sorted order.  Sorters are used
to implement the ORDER BY clause of a SELECT statement.  The
fact that the virtual machine allows multiple sorters is an
historical accident.  In practice no more than one sorter
(sorter number 0) ever gets used.</p>

<p>The virtual machine may contain an arbitrary number of "Lists".
Each list stores a list of integers.  Lists are used to hold the
GDBM keys for records of a GDBM file that needs to be modified.
The WHERE clause of an UPDATE or DELETE statement scans through
the table and writes the GDBM key of every record to be modified
into a list.  Then the list is played back and the table is modified
in a separate step.  It is necessary to do this in two steps since
making a change to a GDBM file can alter the scan order.</p>

<p>The virtual machine can contain an arbitrary number of "Sets".
Each set holds an arbitrary number of strings.  Sets are used to
implement the IN operator with a constant right-hand side.</p>

<p>The virtual machine can open a single external file for reading.
This external read file is used to implement the COPY command.</p>

<p>Finally, the virtual machine can have a single set of aggregators.
An aggregator is a device used to implement the GROUP BY clause
of a SELECT.  An aggregator has one or more slots that can hold
values being extracted by the select.  The number of slots is the
same for all aggregators and is defined by the AggReset operation.
At any point in time a single aggregator is current or "has focus".
There are operations to read or write to memory slots of the aggregator
in focus.  There are also operations to change the focus aggregator
and to scan through all aggregators.</p>

<h2>Viewing Programs Generated By SQLite</h2>

<p>Every SQL statement that SQLite interprets results in a program
for the virtual machine.  But if you precede the SQL statement with
the keyword "EXPLAIN" the virtual machine will not execute the
program.  Instead, the instructions of the program will be returned
like a query result.  This feature is useful for debugging and
for learning how the virtual machine operates.</p>

<p>You can use the <b>sqlite</b> command-line tool to see the
instructions generated by an SQL statement.  The following is
an example:</p>}


proc Code {body} {
  puts {<blockquote><pre>}
  regsub -all {&} [string trim $body] {\&amp;} body
  regsub -all {>} $body {\&gt;} body
  regsub -all {<} $body {\&lt;} body
  regsub -all {\(\(\(} $body {<font color="#00671f"><i>} body
  regsub -all {\)\)\)} $body {</i></font>} body
  puts $body
  puts {</pre></blockquote>}
}

Code {
$ (((sqlite ex1)))
sqlite> (((.explain)))
sqlite> (((explain delete from tbl1 where two<20;)))
addr  opcode        p1     p2     p3          
----  ------------  -----  -----  -------------------------------------   
0     ListOpen      0      0                  
1     Open          0      1      tbl1        
2     Next          0      9                  
3     Field         0      1                  
4     Integer       20     0                  
5     Ge            0      2                  
6     Key           0      0                  
7     ListWrite     0      0                  
8     Goto          0      2                  
9     Noop          0      0                  
10    ListRewind    0      0                  
11    ListRead      0      14                 
12    Delete        0      0                  
13    Goto          0      11                 
14    ListClose     0      0                  
}

puts {
<p>All you have to do is add the "EXPLAIN" keyword to the front of the
SQL statement.  But if you use the ".explain" command to <b>sqlite</b>
first, it will set up the output mode to make the program more easily
viewable.</p>

<p>If <b>sqlite</b> has been compiled without the "-DNDEBUG=1" option
(that is, with the NDEBUG preprocessor macro not defined) then you
can put the SQLite virtual machine in a mode where it will trace its
execution by writing messages to standard output.  There are special
comments to turn tracing on and off.  Use the <b>--vdbe-trace-on--</b>
comment to turn tracing on and the <b>--vdbe-trace-off--</b> comment
to turn tracing back off.</p>

<h2>The Opcodes</h2>
}

puts "<p>There are currently [llength $OpcodeList] opcodes defined by
the virtual machine."
puts {All currently defined opcodes are described in the table below.
This table was generated automatically by scanning the source code
from the file <b>vdbe.c</b>.</p>}

puts {
<p><table cellspacing="1" border="1" cellpadding="10">
<tr><th>Opcode&nbsp;Name</th><th>Description</th></tr>}
foreach op [lsort -dictionary $OpcodeList] {
  puts {<tr><td valign="top" align="center">}
  puts "$op"
  puts "<td>[string trim $Opcode($op:text)]</td></tr>"
}
puts {</table></p>}

puts {
<p><hr /></p>
<p><a href="index.html"><img src="/goback.jpg" border=0 />
Back to the SQLite Home Page</a>
</p>

</body></html>}