** 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
................................................................................
p->aStack[p->tos].flags = STK_Str|STK_Dyn;
p->zStack[p->tos] = zNewKey;
break;
}
/* Opcode: Open P1 P2 P3
**
** Open a new database table named P3. Give it an identifier P1.
** Open readonly if P2==0 and for reading and writing if P2!=0.
** The table is created if it does not already exist and P2!=0.
** If there is already another table opened on P1, then the old
** table is closed first. All tables are automatically closed when
** the VDBE finishes execution. The P1 values need not be
** contiguous but all P1 values should be small integers. It is
** an error for P1 to be negative.
**
** If P3 is null or an empty string, a temporary table is opened.
*/
case OP_Open: {
int i = pOp->p1;
if( i<0 ) goto bad_instruction;
if( i>=p->nTable ){
int j;
p->aTab = sqliteRealloc( p->aTab, (i+1)*sizeof(VdbeTable) );
................................................................................
}
/* Opcode: Fetch P1 * *
**
** Pop the top of the stack and use its value as a key to fetch
** a record from database table or index P1. The data is held
** in the P1 cursor until needed. The data is not pushed onto the
** stack or anything like that.
*/
case OP_Fetch: {
int i = pOp->p1;
int tos = p->tos;
if( tos<0 ) goto not_enough_stack;
if( i>=0 && i<p->nTable && p->aTab[i].pTable ){
if( p->aStack[tos].flags & STK_Int ){
................................................................................
/* Opcode: Distinct P1 P2 *
**
** Use the top of the stack as a key. If a record with that key
** does not exist in table P1, then jump to P2. If the record
** does already exist, then fall thru. The record is not retrieved.
** The key is not popped from the stack.
*/
/* Opcode: Found P1 P2 *
**
** Use the top of the stack as a key. If a record with that key
** does exist in table P1, then jump to P2. If the record
** does not exist, then fall thru. The record is not retrieved.
** The key is popped from the stack.
*/
/* Opcode: NotFound P1 P2 *
**
** Use the top of the stack as a key. If a record with that key
** does exist in table P1, then jump to P2. If the record
** does not exist, then fall thru. The record is not retrieved.
** The key is popped from the stack.
*/
case OP_Distinct:
case OP_NotFound:
case OP_Found: {
int i = pOp->p1;
int tos = p->tos;
int alreadyExists = 0;
................................................................................
**
** 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;
................................................................................
p->aTab[i].index = j+1;
}
break;
}
/* Opcode: PutIdx P1 * *
**
** The top of the stack hold an index key (proably made using the
** MakeKey instruction) and next on stack holds an index value for
** a table. Locate the record in the index P1 that has the key
** and insert the index value into its
** data. Write the results back to the index.
** If the key doesn't exist it is created.
*/
case OP_PutIdx: {
................................................................................
}
break;
}
/* Opcode: FileOpen * * P3
**
** Open the file named by P3 for reading using the FileRead opcode.
** If P3 is "stdin" then output standard input for reading.
*/
case OP_FileOpen: {
if( pOp->p3==0 ) goto bad_instruction;
if( p->pFile ){
if( p->pFile!=stdin ) fclose(p->pFile);
p->pFile = 0;
}
................................................................................
}
p->nLineAlloc = 0;
break;
}
/* Opcode: FileRead P1 P2 P3
**
** Read a single line of input the open file (the file opened using
** FileOpen). If we reach end-of-file, jump immediately to P2. If
** we are able to get another line, split the line apart using P3 as
** a delimiter. There should be exactly P1 fields. Throw an exception
** if the number of fields is different from P1.
*/
case OP_FileRead: {
int n, eol, nField, i, c, nDelim;
char *zDelim, *z;
if( p->pFile==0 ) goto fileread_jump;
nField = pOp->p1;
if( nField<=0 ) goto fileread_jump;

** 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.32 2000/06/09 14:14:34 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
................................................................................
p->aStack[p->tos].flags = STK_Str|STK_Dyn;
p->zStack[p->tos] = zNewKey;
break;
}
/* Opcode: Open P1 P2 P3
**
** Open a new cursor for the database table named P3. Give the ** cursor an identifier P1.
** Open readonly if P2==0 and for reading and writing if P2!=0.
** The table is created if it does not already exist and P2!=0.
** If there is already another cursor opened with identifier P1, ** then the old cursor is closed first.
** All cursors are automatically closed when
** the VDBE finishes execution. The P1 values need not be
** contiguous but all P1 values should be small integers. It is
** an error for P1 to be negative.
**
** If P3 is null or an empty string, a temporary table created.
** This table is automatically deleted when the cursor is closed.
*/
case OP_Open: {
int i = pOp->p1;
if( i<0 ) goto bad_instruction;
if( i>=p->nTable ){
int j;
p->aTab = sqliteRealloc( p->aTab, (i+1)*sizeof(VdbeTable) );
................................................................................
}
/* Opcode: Fetch P1 * *
**
** Pop the top of the stack and use its value as a key to fetch
** a record from database table or index P1. The data is held
** in the P1 cursor until needed. The data is not pushed onto the
** stack.
*/
case OP_Fetch: {
int i = pOp->p1;
int tos = p->tos;
if( tos<0 ) goto not_enough_stack;
if( i>=0 && i<p->nTable && p->aTab[i].pTable ){
if( p->aStack[tos].flags & STK_Int ){
................................................................................
/* Opcode: Distinct P1 P2 *
**
** Use the top of the stack as a key. If a record with that key
** does not exist in table P1, then jump to P2. If the record
** does already exist, then fall thru. The record is not retrieved.
** The key is not popped from the stack.
** ** This operation is similar to NotFound except that this operation ** does not pop the key from the stack.
*/
/* Opcode: Found P1 P2 *
**
** Use the top of the stack as a key. If a record with that key
** does exist in table P1, then jump to P2. If the record
** does not exist, then fall thru. The record is not retrieved.
** The key is popped from the stack.
*/
/* Opcode: NotFound P1 P2 *
**
** Use the top of the stack as a key. If a record with that key
** does not exist in table P1, then jump to P2. If the record
** does exist, then fall thru. The record is not retrieved.
** The key is popped from the stack.
** ** The difference between this operation and Distinct is that ** Distinct does not pop the key from the stack.
*/
case OP_Distinct:
case OP_NotFound:
case OP_Found: {
int i = pOp->p1;
int tos = p->tos;
int alreadyExists = 0;
................................................................................
**
** 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;
................................................................................
p->aTab[i].index = j+1;
}
break;
}
/* Opcode: PutIdx P1 * *
**
** The top of the stack hold an index key (probably made using the
** MakeKey instruction) and next on stack holds an index value for
** a table. Locate the record in the index P1 that has the key
** and insert the index value into its
** data. Write the results back to the index.
** If the key doesn't exist it is created.
*/
case OP_PutIdx: {
................................................................................
}
break;
}
/* Opcode: FileOpen * * P3
**
** Open the file named by P3 for reading using the FileRead opcode.
** If P3 is "stdin" then open standard input for reading.
*/
case OP_FileOpen: {
if( pOp->p3==0 ) goto bad_instruction;
if( p->pFile ){
if( p->pFile!=stdin ) fclose(p->pFile);
p->pFile = 0;
}
................................................................................
}
p->nLineAlloc = 0;
break;
}
/* Opcode: FileRead P1 P2 P3
**
** Read a single line of input from the open file (the file opened using
** FileOpen). If we reach end-of-file, jump immediately to P2. If
** we are able to get another line, split the line apart using P3 as
** a delimiter. There should be P1 fields. If the input line contains
** more than P1 fields, ignore the excess. If the input line contains ** fewer than P1 fields, assume the remaining fields contain an ** empty string.
*/
case OP_FileRead: {
int n, eol, nField, i, c, nDelim;
char *zDelim, *z;
if( p->pFile==0 ) goto fileread_jump;
nField = pOp->p1;
if( nField<=0 ) goto fileread_jump;

#
# 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>
................................................................................
<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
................................................................................
<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>

#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: arch.tcl,v 1.2 2000/06/09 14:14:34 drh Exp $}
puts {<html>
<head>
<title>Architecture of SQLite</title>
</head>
<body bgcolor=white>
<h1 align=center>
................................................................................
<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 they 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
................................................................................
<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.
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 assembles 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 seven
files in the code generator: <b>build.c</b>, <b>delete.c</b>,
<b>expr.c</b>, <b>insert.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.
<b>expr.c</b> handles code generation for expressions.<b>where.c</b> handles code generation for WHERE clauses onSELECT, UPDATE and DELETE statements. The files<b>delete.c</b>, <b>insert.c</b>, <b>select.c</b>, and<b>update.c</b> handle the code generation for SQL statementswith the same names. (Each of these files calls routinesin <b>expr.c</b> and <b>where.c</b> as necessary.) All otherSQL statements are coded out of <b>build.c</b>.</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 has a stack which is used for intermediate storage.
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>

#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: lang.tcl,v 1.4 2000/06/09 14:14:34 drh Exp $}
puts {<html>
<head>
<title>Query Language Understood By SQLite</title>
</head>
<body bgcolor=white>
<h1 align=center>
................................................................................
{{DROP INDEX} dropindex}
{INSERT insert}
{DELETE delete}
{UPDATE update}
{SELECT select}
{COPY copy}
{EXPLAIN explain}
{expression expr}
}] {
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>
................................................................................
regsub -all {[|,.*()]} $body {<big>&</big>} body
regsub -all { = } $body { <big>=</big> } body
regsub -all {STAR} $body {<big>*</big>} body
puts "<td><b><font color=\"#2c2cf0\">$body</font></b></td></tr>"
}
puts {</table>}
}
proc Operator {name} { return "<font color=\"#2c2cf0\"><big>$name</big></font>"}proc Nonterminal {name} { return "<i><font color=\"#ff3434\">$name</font></i>"}proc Keyword {name} { return "<font color=\"#2c2cf0\">$name</font>"}
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 COPY copy
Syntax {sql-statement} {
COPY <table-name> FROM <filename>
}
puts {<p>The COPY command is an extension used to load large amounts ofdata into a table. It is modeled after a similar command foundin PostgreSQL. In fact, the SQLite COPY command is specificallydesigned to be able to read the output of the PostgreSQL dumputility <b>pg_dump</b> so that data can be easily transferred fromPostgreSQL into SQLite.<p><p>The table-name is the name of an existing table which is tobe filled with data. The filename is a string or identifier thatnames a file from which data will be read. The filename can bethe <b>STDIN</b> to read data from standard input.<p><p>Each line of the input file is converted into a single recordin the table. Columns are separated by tabs. If a tab occurs asdata within a column, then that tab is preceded by a baskslash "\"character. A baskslash in the data appears as two backslashes ina row.</p><p>When the input data source is STDIN, the input can be terminatedby a line that contains only a baskslash and a dot:}puts "\"[Operator \\.]\".</p>"
Section {CREATE INDEX} createindex
Syntax {sql-statement} {
CREATE INDEX <index-name>
ON <table-name> ( <column-name> [, <column-name>]* )
} {column-name} {
<name> [ ASC | DESC ]
................................................................................
Section DELETE delete
Syntax {sql-statement} {
DELETE FROM <table-name> [WHERE <expression>]
}
puts {
<p>The DELETE command is used to remove records from a table.The command consists of the "DELETE FROM" keywords followed bythe name of the table from which records are to be removed.
</p>
<p>Without a WHERE clause, all rows of the table are removed.If a WHERE clause is supplied, then only those rows that matchthe expression are removed.</p>}
Section {DROP INDEX} dropindex
Syntax {sql-command} {
DROP INDEX <index-name>
}
................................................................................
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 deleted.</p>}
Section EXPLAIN explain
Syntax {sql-statement} {
EXPLAIN <sql-statement>
}
puts {<p>The EXPLAIN command modifier is a non-standard extension. Theidea comes from a similar command found in PostgreSQL, but the operationis completely different.</p><p>If the EXPLAIN keyword appears before any other SQLite SQL commandthen instead of actually executing the command, the SQLite library willreport back the sequence of virtual machine instructions it would haveused to execute the command had the EXPLAIN keyword not been present.For additional information about virtual machine instructions seethe <a href="arch.html">architecture description</a> or the documentationon <a href="opcode.html">available opcodes</a> for the virtual machine.</p>}
Section expression expr
Syntax {expression} {
<expression> <binary-op> <expression> |
<expression> <like-op> <expression> |
<unary-op> <expression> |
................................................................................
( <expression> ) |
<column-name> |
<table-name> . <column-name> |
<literal-value> |
<function-name> ( <expr-list> | STAR ) |
<expression> ISNULL |
<expression> NOTNULL |
<expression> [NOT] BETWEEN <expression> AND <expression> |
<expression> [NOT] IN ( <value-list> ) |
<expression> [NOT] IN ( <select> ) |
( <select> )
} {like-op} {
LIKE | GLOB | NOT LIKE | NOT GLOB
}
puts {<p>This section is different from the others. Every other section ofthis document talks about a particular SQL command. This section doesnot talk about a standalone command but about "expressions" which are subcomponent of most other commands.</p><p>SQLite understands the following binary operators, in order fromhighest to lowest precedence:</p><blockquote><pre><font color="#2c2cf0"><big>* /+ -&lt; &lt;= &gt; &gt;== == != &lt;&gt; </big>INANDOR</font></pre></blockquote><p>Any SQLite value can be used as part of an expression. For arithmetic operations, integers are treated as integers.Strings are first converted to real numbers using <b>atof()</b>.For comparison operators, numbers compare as numbers and stringscompare as strings. For string comparisons, case is significantbut is only used to break a tie.Note that there are two variations of the equals and not equalsoperators. Equals can be either}puts "[Operator =] or [Operator ==].The non-equals operator can be either[Operator !=] or [Operator {&lt;&gt;}].</p>"puts {<p>The LIKE operator does a wildcard comparision. The operandto the right contains the wildcards.}puts "A percent symbol [Operator %] in the right operandmatches any sequence of zero or more characters on the left.An underscore [Operator _] on the rightmatches any single character on the left. The LIKE operator isnot case sensitive and will match upper case characters on oneside against lower case characters on the other.</p>"puts {<p>The GLOB operator is similar to LIKE but uses the Unixfile globbing syntax for its wildcards. Also, GLOB is casesensitive, unlike LIKE. Both GLOB and LIKE may be preceded bythe NOT keyword to invert the sense of the test.</p><p>SELECT statements can appear in expressions as either theright-hand operand of the IN operator or as a scalar quantity.In both cases, the SELECT should have only a single column in itsresult. Compound SELECTs (connected with keywords like UNION orEXCEPT) are allowed. Any ORDER BY clause on the select is ignored.A SELECT in an expression is evaluated once before any other processingis performed, so none of the expressions within the select itself canrefer to quantities in the containing expression.</p><p>When a SELECT is the right operand of the IN operator, the INoperator returns TRUE if the result of the left operand is any ofthe values generated by the select. The IN operator may be precededby the NOT keyword to invert the sense of the test.</p><p>When a SELECT appears within an expression but is not the rightoperand of an IN operator, then the first row of the result of theSELECT becomes the value used in the expression. If the SELECT yieldsmore than one result row, all rows after the first are ignored. Ifthe SELECT yeilds no rows, then the value of the SELECT is NULL.</p>}
Section INSERT insert
Syntax {sql-statement} {
INSERT INTO <table-name> [( <column-list> )] VALUES ( <value-list> ) |
INSERT INTO <table-name> [( <column-list> )] <select-statement>
}
................................................................................
SELECT <result> FROM <table-list>
[WHERE <expression>]
[GROUP BY <expr-list>]
[HAVING <expression>]
[<compound-op> <select>]*
[ORDER BY <sort-expr-list>]
} {result} {
STAR | <result-column> [, <result-column>]*
} {result-column} {<expression> [ [AS] <string> ]
} {table-list} {
<table-name> [, <table-name>]*
} {sort-expr-list} {
<expr> [<sort-order>] [, <expr> [<sort-order>]]*
} {sort-order} {
ASC | DESC
} {compound_op} {
UNION | UNION ALL | INTERSECT | EXCEPT
}
puts {<p>The SELECT statement is used to query the database. Theresult of a SELECT is zero or more rows of data where each rowhas a fixed number of columns. The number of columns in theresult is specified by the expression list in between theSELECT and FROM keywords. Any arbitrary expression can be usedas a result. If the result specification is just}puts "[Operator *] then all columns of all tables are used as the result."puts {</p><p>The query is executed again one or more tables specified afterthe FROM keyword. If more than one table is specified, then thequery is against the join of the various tables.</p><p>The WHERE clause can be used to limit the number of rows overwhich the query operates. Note that because of limitations ofGDBM (it uses hashing not b-trees) indices will only be used tooptimize the query if WHERE expression contains equality comparisonsconnected by the AND operator.</p><p>The GROUP BY clauses causes one or more rows of the result tobe combined into a single row of output. This is especially usefulwhen the result contains aggregate functions. The expressions inthe GROUP BY clause do <em>not</em> have to be expressions thatappear in the result. The HAVING clause is similar to WHERE exceptthat HAVING applies after grouping has occurred. The HAVING expressionmay refer to values, even aggregate functions, that are not in the result.</p><p>The ORDER BY clause causes the output rows to be sorted. The argument to ORDER BY is a list of expressions that are used as thekey for the sort. The expressions do not have to be part of theresult for a simple SELECT, but in a compound SELECT each sortexpression must exactly match one of the result columns. Eachsort expression may be optionally followed by ASC or DESC to specifythe sort order.</p><p>A compound SELECT is formed from two or more simple SELECTs connectedby one of the operators UNION, UNION ALL, INTERSECT, or EXCEPT. Ina compound SELECT, all the constituent SELECTs must specify thesame number of result columns. There may be only a single ORDER BYclause at the end of the compound SELECT. The UNION and UNION ALLoperators combine the results of the SELECTs to the right and left intoa single big table. The difference is that in UNION all result rowsare distinct where in UNION ALL there may be duplicates.The INTERSECT operator takes the intersection of the results of theleft and right SELECTs. EXCEPT takes the result of left SELECT afterremoving the results of the right SELECT. When three are more SELECTsare connected into a compound, they group from left to right.</p>}
Section UPDATE update
Syntax {sql-statement} {
UPDATE <table-name> SET <assignment> [, <assignment>] [WHERE <expression>]
} {assignment} {
<column-name> = <expression>
}
puts {
<p>The UPDATE statement is used to change the value of columns in selected rows of a table. Each assignment in an UPDATE specifiesa column name to the left of the equals sign and an arbitrary expressionto the right. The expressions may use the values of other columns.All expressions are evaluated before any assignments are made.A WHERE clause can be used to restrict which rows are updated.
}
Section VACUUM vacuum
Syntax {sql-statement} {
VACUUM [<index-or-table-name>]
}

#
# 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>
................................................................................
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
................................................................................
(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 detailsare 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>
................................................................................
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".

#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: opcode.tcl,v 1.2 2000/06/09 14:14:34 drh Exp $}
puts {<html>
<head>
<title>SQLite Virtual Machine Opcodes</title>
</head>
<body bgcolor=white>
<h1 align=center>
................................................................................
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. This 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
................................................................................
(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. See theindividual opcode descriptions for details.</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 independently, even cursors pointing to the same file.
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>
................................................................................
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.
(See the <a href="fileformat.html">file format</a> description formore information on GDBM keys in SQLite table files.)
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".

This page was generated in about
0.07s by
Fossil 2.9 [356c0d017e] 2019-05-23 17:18:56