SQLite

#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: arch.tcl,v 1.14 2004/07/17 21:56:10 drh Exp $}
set rcsid {$Id: arch.tcl,v 1.15 2004/09/08 13:06:21 drh Exp $}
source common.tcl
header {Architecture of SQLite}
puts {
<h2>The Architecture Of SQLite</h2>

<h3>Introduction</h3>

<p>
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>

<h3>History</h3>

<p>
There are two main C interfaces to the SQLite library:
<b>sqlite_exec()</b> and <b>sqlite_compile()</b>.  Prior to
version 2.8.0 (2003-Feb-16) only sqlite_exec() was supported.
For version 2.8.0, the sqlite_exec and sqlite_compile methods
existed as peers.  Beginning with version 2.8.13, the sqlite_compile
method is the primary interface, and sqlite_exec is implemented
using sqlite_compile.  Externally, this change is an enhancement
that maintains backwards compatibility.  But internally,
the plumbing is very different.  The diagram at the right shows
the structure of SQLite for version 2.8.13 and following.
</p>

<p>
This document describes the structure for SQLite version 2.X.
This document describes SQLite version 3.0.  Version 2.8 and
SQLite version 3.0.0 introduces many new features and capabilities.
The basic architecture of the library remains the same.  However,
some of the details described here are different.  For example,
earlier are similar but the details differ.
the code was in the file <b>os.c</b> has now been split out into
several file, on for each operating system.  And
the prefix on the names of API routines changed from <b>sqlite_</b>
to <b>sqlite3_</b>. 
</p>

<h3>Interface</h3>

<p>Much of the public interface to the SQLite library is implemented by
functions found in the <b>main.c</b> source file though some routines are
functions found in the <b>main.c</b>, <b>legacy.c</b>, and
<b>vdbeapi.c</b> source files
though some routines are
scattered about in other files where they can have access to data 
structures with file scope.  The
<b>sqlite_get_table()</b> routine is implemented in <b>table.c</b>.
<b>sqlite3_get_table()</b> routine is implemented in <b>table.c</b>.
<b>sqlite_step()</b> is found in <b>vdbe.c</b>.  
<b>sqlite_mprintf()</b> is found in <b>printf.c</b>.
<b>sqlite3_mprintf()</b> is found in <b>printf.c</b>.
<b>sqlite3_complete()</b> is in <b>tokenize.c</b>.
The Tcl interface is implemented by <b>tclsqlite.c</b>.  More
information on the C interface to SQLite is
<a href="c_interface.html">available separately</a>.<p>
<a href="capi3ref.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>.
symbols in the SQLite library begin with the prefix <b>sqlite3</b>.
Those symbols that are intended for external use (in other words,
those symbols which form the API for SQLite) begin
with <b>sqlite_</b>.</p>
with <b>sqlite3_</b>.</p>

<h3>SQL Command Processor</h3>

<p>

<h3>Tokenizer</h3>

<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.
one by one to the parser.  The tokenizer is hand-coded in C in 
All of the code for the tokenizer
is contained in the <b>tokenize.c</b> source file.</p>
the file <b>tokenize.c</b>.

<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.  The author
of SQLite 
has done it both ways and finds things generally work out nicer for
the tokenizer to call the parser.  YACC has it backwards.</p>

</p>

<h3>Code Generator</h3>

<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 many
files in the code generator:  <b>build.c</b>, <b>copy.c</b>,
files in the code generator:
<b>attach.c</b>,
<b>auth.c</b>,
<b>build.c</b>,
<b>delete.c</b>,
<b>expr.c</b>, <b>insert.c</b>, <b>pragma.c</b>,
<b>select.c</b>, <b>trigger.c</b>, <b>update.c</b>, <b>vacuum.c</b>
<b>expr.c</b>,
<b>insert.c</b>,
<b>pragma.c</b>,
<b>select.c</b>,
<b>trigger.c</b>,
<b>update.c</b>,
<b>vacuum.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 on
SELECT, UPDATE and DELETE statements.  The files <b>copy.c</b>,
SELECT, UPDATE and DELETE statements.  The files <b>attach.c</b>,
<b>delete.c</b>, <b>insert.c</b>, <b>select.c</b>, <b>trigger.c</b>
<b>update.c</b>, and <b>vacuum.c</b> handle the code generation
for SQL statements with the same names.  (Each of these files calls routines
in <b>expr.c</b> and <b>where.c</b> as necessary.)  All other
SQL statements are coded out of <b>build.c</b>.</p>
SQL statements are coded out of <b>build.c</b>.
The <b>auth.c</b> file implements the functionality of
<b>sqlite3_set_authorizer()</b>.</p>

<h3>Virtual Machine</h3>

<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 itself is entirely contained in a single
source file <b>vdbe.c</b>.  The virtual machine also has
its own header files: <b>vdbe.h</b> that defines an interface
between the virtual machine and the rest of the SQLite library and
<b>vdbeInt.h</b> which defines structure private the virtual machine.
The <b>vdbeaux.c</b> file contains utilities used by the virtual
machine and interface modules used by the rest of the library to
construct VM programs.</p>
construct VM programs.  The <b>vdbeapi.c</b> file contains external
interfaces to the virtual machine such as the 
<b>sqlite3_bind_...</b> family of functions.  Individual values
(strings, integer, floating point numbers, and BLOBs) are stored
in an internal object named "Mem" which is implemented by
<b>vdbemem.c</b>.</p>

<p>
SQLite implements SQL functions using callbacks to C-language routines.
Even the built-in SQL functions are implemented this way.  Most of
the built-in SQL functions (ex: <b>coalesce()</b>, <b>count()</b>,
<b>substr()</b>, and so forth) can be found in <b>func.c</b>.
Date and time conversion functions are found in <b>date.c</b>.
</p>

<h3>B-Tree</h3>

<p>An SQLite database is maintained on disk using a B-tree implementation
found in the <b>btree.c</b> source file.  A separate B-tree is used for
each table and index in the database.  All B-trees are stored in the
same disk file.</p>
same disk file.  Details of the file format are recorded in a large
comment at the beginning of <b>btree.c</b>.</p>

<p>The interface to the B-tree subsystem is defined by the header file
<b>btree.h</b>.
</p>

<h3>Page Cache</h3>

<p>The B-tree module requests information from the disk in 1024 byte
chunks.  The page cache is reponsible for reading, writing, and
<p>The B-tree module requests information from the disk in fixed-size
chunks.  The default chunk size is 1024 bytes but can vary between 512
and 65536 bytes.
The page cache is reponsible for reading, writing, and
caching these chunks.
The page cache also provides the rollback and atomic commit abstraction
and takes care of reader/writer locking of the database file.  The
and takes care of locking of the database file.  The
B-tree driver requests particular pages from the page cache and notifies
the page cache when it wants to modify pages or commit or rollback
changes and the page cache handles all the messy details of making sure
the requests are handled quickly, safely, and efficiently.</p>

<p>The code to implement the page cache is contained in the single C
source file <b>pager.c</b>.  The interface to the page cache subsystem
is defined by the header file <b>pager.h</b>.
</p>

<h3>OS Interface</h3>

<p>
In order to provide portability between POSIX and Win32 operating systems,
SQLite uses an abstraction layer to interface with the operating system.
The interface to the OS abstraction layer is defined in
<b>os.h</b>.  Each supported operating system has its own implementation:
<b>os_unix.c</b> for Unix, <b>os_win.c</b> for windows, and so forth.
Each of these operating-specific implements typically has its own
header file: <b>os_unix.h</b>, <b>os_win.h</b>, etc.
</p>
The <b>os.c</b> file contains about 20 routines used for opening and
closing files, deleting files, creating and deleting locks on files,
flushing the disk cache, and so forth.  Each of these functions contains
two implementations separated by #ifdefs: one for POSIX and the other
for Win32.  The interface to the OS abstraction layer is defined by
the <b>os.h</b> header file.

<h3>Utilities</h3>

<p>
Memory allocation and caseless string comparison routines are located
in <b>util.c</b>.
Symbol tables used by the parser are maintained by hash tables found
in <b>hash.c</b>.  The <b>utf.c</b> source file contains Unicode
conversion subroutines.
SQLite has its own private implementation of <b>printf()</b> (with
some extensions) in <b>printf.c</b> and its own random number generator
in <b>random.c</b>.
</p>

<h3>Test Code</h3>

<p>
If you count regression test scripts,
more than half the total code base of SQLite is devoted to testing.
There are many <b>assert()</b> statements in the main code files.
In additional, the source files <b>test1.c</b> through <b>test5.c</b>
together with <b>md5.c</b> implement extensions used for testing
purposes only.  The <b>os_test.c</b> backend interface is used to
simulate power failures to verify the crash-recovery mechanism in
the pager.
</p>
}

}
footer $rcsid

#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: lang.tcl,v 1.71 2004/07/18 20:52:32 drh Exp $}
set rcsid {$Id: lang.tcl,v 1.72 2004/09/08 13:06:21 drh Exp $}
source common.tcl
header {Query Language Understood by SQLite}
puts {
<h2>SQL As Understood By SQLite</h2>

<p>The SQLite library understands most of the standard SQL
language.  But it does <a href="omitted.html">omit some features</a>

}
Syntax {sql-statement} {
ROLLBACK [TRANSACTION [<name>]]
}

puts {
<p>Beginning in version 2.0, SQLite supports transactions with
rollback and atomic commit.  See <a href="#attach">ATTACH</a> for
rollback and atomic commit.</p>
an exception when there are attached databases.</p>

<p>The optional transaction name is ignored. SQLite currently 
does not allow nested transactions.</p>

<p>
No changes can be made to the database except within a transaction.
Any command that changes the database (basically, any SQL command

[ USING DELIMITERS <delim> ]
}

puts {
<p>The COPY command is available in SQLite version 2.8 and earlier.
The COPY command has been removed from SQLite version 3.0 due to
complications in trying to support it in a mixed UTF-8/16 environment.
In version 3.0, the <a href="sqlite.html">command-line shell</a>
contains a new command <b>.import</b> that can be used as a substitute
for COPY.
</p>

<p>The COPY command is an extension used to load large amounts of
data into a table.  It is modeled after a similar command found
in PostgreSQL.  In fact, the SQLite COPY command is specifically
designed to be able to read the output of the PostgreSQL dump
utility <b>pg_dump</b> so that data can be easily transferred from

followed by the name of a new table and a parenthesized list of column
definitions and constraints.  The table name can be either an identifier
or a string.  Tables names that begin with "<b>sqlite_</b>" are reserved
for use by the engine.</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.
SQLite is <a href="datatypes.html">typeless</a>.
The datatype for the column does not restrict what data may be put
in that column.
See <a href="datatype3.html">Datatypes In SQLite Version 3</a> for
All information is stored as null-terminated strings.
additional information.
The UNIQUE constraint causes an index to be created on the specified
columns.  This index must contain unique keys.
The DEFAULT constraint
specifies a default value to use when doing an INSERT.
The COLLATE clause specifies what text collating function to use
when comparing text entries for the column.  The built-in BINARY
collating function is used by default.

Support for CHECK constraints may be added in the future.  As of
version 2.3.0, NOT NULL, PRIMARY KEY, and UNIQUE constraints all
work.</p>

<p>There are no arbitrary limits on the number
of columns or on the number of constraints in a table.
The total amount of data in a single row is limited to about
1 megabytes.  (This limit can be increased to 16MB by changing
a single #define in the source code and recompiling.)</p>
1 megabytes in version 2.8.  In version 3.0 there is no arbitrary
limit on the amount of data in a row.</p>


<p>The CREATE TABLE AS form defines the table to be
the result set of a query.  The names of the table columns are
the names of the columns in the result.</p>

<p>The exact text
of each CREATE TABLE statement is stored in the <b>sqlite_master</b>

Section {DROP INDEX} dropindex

Syntax {sql-command} {
DROP INDEX [<database-name> .] <index-name>
}

puts {
<p>The DROP INDEX statement removes an index added with the <a href="#createindex">
<p>The DROP INDEX statement removes an index added
with the <a href="#createindex">
CREATE INDEX</a> statement.  The index named is completely removed from
the disk.  The only way to recover the index is to reenter the
appropriate CREATE INDEX command.  Non-temporary indexes on tables in 
an attached database cannot be dropped.</p>

<p>The DROP INDEX statement does not reduce the size of the database 
file.  Empty space in the database is retained for later INSERTs.  To 

function can be used in any expression.  Simple functions return
a result immediately based on their inputs.  Aggregate functions
may only be used in a SELECT statement.  Aggregate functions compute
their result across all rows of the result set.</p>

<p>The functions shown below are available by default.  Additional
functions may be written in C and added to the database engine using
the <a href="c_interface.html#cfunc">sqlite_create_function()</a>
the <a href="capi3ref.html#cfunc">sqlite3_create_function()</a>
API.</p>

<table border=0 cellpadding=10>
<tr>
<td valign="top" align="right" width=120>abs(<i>X</i>)</td>
<td valign="top">Return the absolute value of argument <i>X</i>.</td>
</tr>

<tr>
<td valign="top" align="right">coalesce(<i>X</i>,<i>Y</i>,...)</td>
<td valign="top">Return a copy of the first non-NULL argument.  If
all arguments are NULL then NULL is returned.  There must be at least 
2 arguments.</td>
</tr>

<tr>
<a name="globFunc"></a>
<td valign="top" align="right">glob(<i>X</i>,<i>Y</i>)</td>
<td valign="top">This function is used to implement the
"<b>Y GLOB X</b>" syntax of SQLite.  The
<a href="c_interface.html#cfunc">sqlite_create_function()</a> 
"<b>X GLOB Y</b>" syntax of SQLite.  The
<a href="capi3ref.html#sqlite3_create_function">sqlite3_create_function()</a> 
interface can
be used to override this function and thereby change the operation
of the <a href="#glob">GLOB</a> operator.</td>
</tr>

<tr>
<td valign="top" align="right">ifnull(<i>X</i>,<i>Y</i>)</td>

characters is returned, not the number of bytes.</td>
</tr>

<tr>
<a name="likeFunc"></a>
<td valign="top" align="right">like(<i>X</i>,<i>Y</i>)</td>
<td valign="top">This function is used to implement the
"<b>Y LIKE X</b>" syntax of SQL.  The
<a href="c_interface.html#cfunc">sqlite_create_function()</a> 
"<b>X LIKE Y</b>" syntax of SQL.  The
<a href="capi3ref.html#sqlite3_create_function">sqlite_create_function()</a> 
interface can
be used to override this function and thereby change the operation
of the <a href="#like">LIKE</a> operator.</td>
</tr>

<tr>
<td valign="top" align="right">lower(<i>X</i>)</td>

<tr>
<td valign="top" align="right">nullif(<i>X</i>,<i>Y</i>)</td>
<td valign="top">Return the first argument if the arguments are different, 
otherwise return NULL.</td>
</tr>

<tr>
<td valign="top" align="right">quote(<i>X</i>)</td>
<td valign="top">This routine returns a string which is the value of
its argument suitable for inclusion into another SQL statement.
Strings are surrounded by single-quotes with escapes on interior quotes
as needed.  BLOBs are encoded as hexadecimal literals.
The current implementation of VACUUM uses this function.  The function
is also useful when writing triggers to implement undo/redo functionality.
</td>
</tr>

<tr>
<td valign="top" align="right">random(*)</td>
<td valign="top">Return a random integer between -2147483648 and
+2147483647.</td>
</tr>

<tr>

right rather than the left.  If SQLite is configured to support UTF-8,
then characters indices refer to actual UTF-8 characters, not bytes.</td>
</tr>

<tr>
<td valign="top" align="right">typeof(<i>X</i>)</td>
<td valign="top">Return the type of the expression <i>X</i>.  The only 
return values are "null", "integer", "real", "text", and "blob".
return values are "numeric" and "text".  SQLite's type handling is 
explained in <a href="datatypes.html">Datatypes in SQLite</a>.</td>
SQLite's type handling is 
explained in <a href="datatype3.html">Datatypes in SQLite Version 3</a>.</td>
</tr>

<tr>
<td valign="top" align="right">upper(<i>X</i>)</td>
<td valign="top">Return a copy of input string <i>X</i> converted to all
upper-case letters.  The implementation of this function uses the C library
routine <b>toupper()</b> which means it may not work correctly on 
UTF-8 strings.</td>
</tr>
</table>

<p>
The following aggregate functions are available by default.  Additional
aggregate functions written in C may be added using the 
<a href="capi3ref.html#sqlite3_create_function">sqlite3_create_function()</a>
<a href="c_interface.html#cfunc">sqlite_create_aggregate()</a> API.</p>
API.</p>

<table border=0 cellpadding=10>
<tr>
<td valign="top" align="right" width=120>avg(<i>X</i>)</td>
<td valign="top">Return the average value of all <i>X</i> within a group.</td>
</tr>

    the main database.  The second row will be for the database used to 
    store temporary tables.</p></li>

<a name="pragma_default_cache_size"></a>
<li><p><b>PRAGMA default_cache_size;
       <br>PRAGMA default_cache_size = </b><i>Number-of-pages</i><b>;</b></p>
    <p>Query or change the maximum number of database disk pages that SQLite
    will hold in memory at once.  Each page uses 1K on disk and about 1.5K in memory.
    This pragma works like the <a href="#pragma_cache_size"><b>cache_size</b></a> 
    will hold in memory at once.  Each page uses 1K on disk and about
    1.5K in memory.
    This pragma works like the
    <a href="#pragma_cache_size"><b>cache_size</b></a> 
    pragma with the additional
    feature that it changes the cache size persistently.  With this pragma,
    you can set the cache size once and that setting is retained and reused
    everytime you reopen the database.</p></li>

<a name="pragma_default_synchronous"></a>
<li><p><b>PRAGMA default_synchronous;

    crashes or the computer loses power before that data has been written
    to the disk surface.  On the other hand, some
    operations are as much as 50 or more times faster with synchronous OFF.
    </p>
    <p>This pragma changes the synchronous mode persistently.  Once changed,
    the mode stays as set even if the database is closed and reopened.  The
    <a href="#pragma_synchronous"><b>synchronous</b></a> pragma does the same 
    thing but only applies the setting to the current session.</p></li>
    thing but only applies the setting to the current session.
    
    </p></li>

<a name="pragma_default_temp_store"></a>
<li><p><b>PRAGMA default_temp_store;
       <br>PRAGMA default_temp_store = DEFAULT; </b>(0)<b>
       <br>PRAGMA default_temp_store = MEMORY; </b>(2)<b>
       <br>PRAGMA default_temp_store = FILE;</b> (1)</p>
    <p>Query or change the setting of the "<b>temp_store</b>" flag stored in