SQLite

#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: arch.tcl,v 1.16 2004/10/10 17:24:54 drh Exp $}
source common.tcl
header {Architecture of SQLite}
puts {
<h2>The Architecture Of SQLite</h2>

<h3>Introduction</h3>

</p>

<h3>Page Cache</h3>

<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 responsible for reading, writing, and
caching these chunks.
The page cache also provides the rollback and atomic commit abstraction
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>

set rcsid {$Id: capi3ref.tcl,v 1.14 2004/10/10 17:24:54 drh Exp $}
source common.tcl
header {C/C++ Interface For SQLite Version 3}
puts {
<h2>C/C++ Interface For SQLite Version 3</h2>
}

proc api {name prototype desc {notused x}} {

#define SQLITE_NOTFOUND    12   /* (Internal Only) Table or record not found */
#define SQLITE_FULL        13   /* Insertion failed because database is full */
#define SQLITE_CANTOPEN    14   /* Unable to open the database file */
#define SQLITE_PROTOCOL    15   /* Database lock protocol error */
#define SQLITE_EMPTY       16   /* (Internal Only) Database table is empty */
#define SQLITE_SCHEMA      17   /* The database schema changed */
#define SQLITE_TOOBIG      18   /* Too much data for one row of a table */
#define SQLITE_CONSTRAINT  19   /* Abort due to constraint violation */
#define SQLITE_MISMATCH    20   /* Data type mismatch */
#define SQLITE_MISUSE      21   /* Library used incorrectly */
#define SQLITE_NOLFS       22   /* Uses OS features not supported on host */
#define SQLITE_AUTH        23   /* Authorization denied */
#define SQLITE_ROW         100  /* sqlite_step() has another row ready */
#define SQLITE_DONE        101  /* sqlite_step() has finished executing */
} {

}

api {} {
  int sqlite3_busy_timeout(sqlite3*, int ms);
} {
 This routine sets a busy handler that sleeps for a while when a
 table is locked.  The handler will sleep multiple times until 
 at least "ms" milliseconds of sleeping have been done.  After
 "ms" milliseconds of sleeping, the handler returns 0 which
 causes sqlite3_exec() to return SQLITE_BUSY.

 Calling this routine with an argument less than or equal to zero
 turns off all busy handlers.
}

api {} {

#define SQLITE_UTF8     1
#define SQLITE_UTF16    2
#define SQLITE_UTF16BE  3
#define SQLITE_UTF16LE  4
#define SQLITE_ANY      5
} {
 These two functions are used to add user functions or aggregates
 implemented in C to the SQL language interpreted by SQLite. The
 difference only between the two is that the second parameter, the
 name of the (scalar) function or aggregate, is encoded in UTF-8 for
 sqlite3_create_function() and UTF-16 for sqlite3_create_function16().

 The first argument is the database handle that the new function or
 aggregate is to be added to. If a single program uses more than one
 database handle internally, then user functions or aggregates must 

 The sixth, seventh and  eighth, xFunc, xStep and xFinal, are
 pointers to user implemented C functions that implement the user
 function or aggregate. A scalar function requires an implementation of
 the xFunc callback only, NULL pointers should be passed as the xStep
 and xFinal parameters. An aggregate function requires an implementation
 of xStep and xFinal, but NULL should be passed for xFunc. To delete an
 existing user function or aggregate, pass NULL for all three function
 callback. Specifying an inconstant set of callback values, such as an
 xFunc and an xFinal, or an xStep but no xFinal, SQLITE_ERROR is
 returned.
}

api {} {
int sqlite3_data_count(sqlite3_stmt *pStmt);
} {

 All prepared statements must finalized before sqlite3_close() is
 called or else the close will fail with a return code of SQLITE_BUSY.

 This routine can be called at any point during the execution of the
 virtual machine.  If the virtual machine has not completed execution
 when this routine is called, that is like encountering an error or
 an interrupt.  (See sqlite3_interrupt().)  Incomplete updates may be
 rolled back and transactions canceled,  depending on the circumstances,
 and the result code returned will be SQLITE_ABORT.
}

api {} {
void sqlite3_free(char *z);
} {
 Use this routine to free memory obtained from 

api {} {
char *sqlite3_mprintf(const char*,...);
char *sqlite3_vmprintf(const char*, va_list);
} {
 These routines are variants of the "sprintf()" from the
 standard C library.  The resulting string is written into memory
 obtained from malloc() so that there is never a possibility of buffer
 overflow.  These routines also implement some additional formatting
 options that are useful for constructing SQL statements.

 The strings returned by these routines should be freed by calling
 sqlite3_free().

 All of the usual printf formatting options apply.  In addition, there

 violation) has occurred.  sqlite3_step() should not be called again on
 the VM. More information may be found by calling sqlite3_errmsg().

 SQLITE_MISUSE means that the this routine was called inappropriately.
 Perhaps it was called on a virtual machine that had already been
 finalized or on one that had previously returned SQLITE_ERROR or
 SQLITE_DONE.  Or it could be the case the the same database connection
 is being used simultaneously by two or more threads.
}

api {} {
void *sqlite3_trace(sqlite3*, void(*xTrace)(void*,const char*), void*);
} {
 Register a function that is called each time an SQL statement is evaluated.
 The callback function is invoked on the first call to sqlite3_step() after

chng {2002 Sep 25 (2.7.2)} {
<li>Prevent journal file overflows on huge transactions.</li>
<li>Fix a memory leak that occurred when sqlite_open() failed.</li>
<li>Honor the ORDER BY and LIMIT clause of a SELECT even if the
    result set is used for an INSERT.</li>
<li>Do not put write locks on the file used to hold TEMP tables.</li>
<li>Added documentation on SELECT DISTINCT and on how SQLite handles NULLs.</li>
<li>Fix a problem that was causing poor performance when many thousands
    of SQL statements were executed by a single sqlite_exec() call.</li>
}

chng {2002 Aug 31 (2.7.1)} {
<li>Fix a bug in the ORDER BY logic that was introduced in version 2.7.0</li>
<li>C-style comments are now accepted by the tokenizer.</li>
<li>INSERT runs a little faster when the source is a SELECT statement.</li>
}

chng {2002 Aug 25 (2.7.0)} {
<li>Make a distinction between numeric and text values when sorting.
    Text values sort according to memcmp().  Numeric values sort in
    numeric order.</li>
<li>Allow multiple simultaneous readers under windows by simulating
    the reader/writers locks that are missing from Win95/98/ME.</li>
<li>An error is now returned when trying to start a transaction if
    another transaction is already active.</li>
}

chng {2002 Aug 12 (2.6.3)} {
<li>Add the ability to read both little-endian and big-endian databases.

chng {2002 Jun 17 (2.5.0)} {
<li>Added support for row triggers.</li>
<li>Added SQL-92 compliant handling of NULLs.</li>
<li>Add support for the full SQL-92 join syntax and LEFT OUTER JOINs.</li>
<li>Double-quoted strings interpreted as column names not text literals.</li>
<li>Parse (but do not implement) foreign keys.</li>
<li>Performance improvements in the parser, pager, and WHERE clause code
    generator.</li>
<li>Make the LIMIT clause work on subqueries.  (ORDER BY still does not
    work, though.)</li>
<li>Added the "%Q" expansion to sqlite_*_printf().</li>
<li>Bug fixes too numerous to mention (see the change log).</li>
}

chng {2002 May 09 (2.4.12)} {
<li>Added logic to detect when the library API routines are called out
    of sequence.</li>
}

}

chng {2002 Apr 06 (2.4.7)} {
<li>Add the ability to put TABLE.* in the column list of a
    SELECT statement.</li>
<li>Permit SELECT statements without a FROM clause.</li>
<li>Added the <b>last_insert_rowid()</b> SQL function.</li>
<li>Do not count rows where the IGNORE conflict resolution occurs in
    the row count.</li>
<li>Make sure functions expressions in the VALUES clause of an INSERT
    are correct.</li>
<li>Added the <b>sqlite_changes()</b> API function to return the number
    of row that changed in the most recent operation.</li>
}

    are 4 times smaller.</li>
<li>Added support for user-defined functions implemented in C.</li>
<li>Added support for new functions: <b>coalesce()</b>, <b>lower()</b>,
    <b>upper()</b>, and <b>random()</b>
<li>Added support for VIEWs.</li>
<li>Added the subquery flattening optimizer.</li>
<li>Modified the B-Tree and Pager modules so that disk pages that do not
    contain real data (free pages) are not journaled and are not
    written from memory back to the disk when they change.  This does not 
    impact database integrity, since the
    pages contain no real data, but it does make large INSERT operations
    about 2.5 times faster and large DELETEs about 5 times faster.</li>
<li>Made the CACHE_SIZE pragma persistent</li>
<li>Added the SYNCHRONOUS pragma</li>
<li>Fixed a bug that was causing updates to fail inside of transactions when

<li>Change the code so that version 2.1.1 databases that were rendered 
    unreadable by the above bug can be read by this version of
    the library even though the SQLITE_MASTER table is (slightly)
    corrupted.</li>
}

chng {2001 Nov 13 (2.1.1)} {
<li>Bug fix: Sometimes arbitrary strings were passed to the callback
    function when the actual value of a column was NULL.</li>
}

chng {2001 Nov 12 (2.1.0)} {
<li>Change the format of data records so that records up to 16MB in size
    can be stored.</li>
<li>Change the format of indices to allow for better query optimization.</li>

<li>Added the COUNT_CHANGES pragma.</li>
<li>Changes to the FULL_COLUMN_NAMES pragma to help out the ODBC driver.</li>
<li>Bug fix: "SELECT count(*)" was returning NULL for empty tables.
    Now it returns 0.</li>
}

chng {2001 Oct 13 (2.0.4)} {
<li>Bug fix: an obscure and relatively harmless bug was causing one of
    the tests to fail when gcc optimizations are turned on.  This release
    fixes the problem.</li>
}

chng {2001 Oct 13 (2.0.3)} {
<li>Bug fix: the <b>sqlite_busy_timeout()</b> function was delaying 1000
    times too long before failing.</li>

#
# Run this Tcl script to generate the constraint.html file.
#
set rcsid {$Id: conflict.tcl,v 1.4 2004/10/10 17:24:55 drh Exp $ }
source common.tcl
header {Constraint Conflict Resolution in SQLite}
puts {
<h1>Constraint Conflict Resolution in SQLite</h1>

<p>
In most SQL databases, if you have a UNIQUE constraint on

many other SQL databases as possible, but different SQL database
engines exhibit different conflict resolution strategies.  For
example, PostgreSQL always uses ROLLBACK, Oracle always uses ABORT, and
MySQL usually uses FAIL but can be instructed to use IGNORE or REPLACE.
By supporting all five alternatives, SQLite provides maximum
portability.</p>

<p>Another reason for supporting multiple algorithms is that sometimes
it is useful to use an algorithm other than the default.
Suppose, for example, you are
inserting 1000 records into a database, all within a single
transaction, but one of those records is malformed and causes
a constraint error.  Under PostgreSQL or Oracle, none of the
1000 records would get inserted.  In MySQL, some subset of the
records that appeared before the malformed record would be inserted
but the rest would not.  Neither behavior is especially helpful.
What you really want is to use the IGNORE algorithm to insert
all but the malformed record.</p>

}
footer $rcsid

set rcsid {$Id: datatype3.tcl,v 1.8 2004/10/10 17:24:55 drh Exp $}
source common.tcl
header {Datatypes In SQLite Version 3}
puts {
<h2>Datatypes In SQLite Version 3</h2>

<h3>1. Storage Classes</h3>

	<LI><P>An INTEGER or REAL value is less than any TEXT or BLOB value.
	When an INTEGER or REAL is compared to another INTEGER or REAL, a
	numerical comparison is performed.</P>

	<LI><P>A TEXT value is less than a BLOB value. When two TEXT values
	are compared, the C library function memcmp() is usually used to
	determine the result. However this can be overridden, as described
	under 'User-defined collation Sequences' below.</P>

	<LI><P>When two BLOB values are compared, the result is always
	determined using memcmp().</P>
</UL>

<P>SQLite may attempt to convert values between the numeric storage

#
# Run this script to generated a datatypes.html output file
#
set rcsid {$Id: datatypes.tcl,v 1.8 2004/10/10 17:24:55 drh Exp $}
source common.tcl
header {Datatypes In SQLite version 2}
puts {
<h2>Datatypes In SQLite Version 2</h2>

<h3>1.0 &nbsp; Typelessness</h3>
<p>

For SQLite version 2.6.3 and earlier, all values used the numeric datatype.
The text datatype appears in version 2.7.0 and later.  In the sequel it
is assumed that you are using version 2.7.0 or later of SQLite.
</p>

<p>
For an expression, the datatype of the result is often determined by
the outermost operator.  For example, arithmetic operators ("+", "*", "%")
always return a numeric results.  The string concatenation operator
("||") returns a text result.  And so forth.  If you are ever in doubt
about the datatype of an expression you can use the special <b>typeof()</b>
SQL function to determine what the datatype is.  For example:
</p>

<blockquote><pre>

#
# Run this TCL script to generate HTML for the download.html file.
#
set rcsid {$Id: download.tcl,v 1.15 2004/10/10 17:24:55 drh Exp $}
source common.tcl
header {SQLite Download Page}

puts {
<h2>SQLite Download Page</h1>
<table width="100%" cellpadding="5">
}

<h3>Direct Access To The Sources Via Anonymous CVS</h3>

<p>
All SQLite source code is maintained in a 
<a href="http://www.cvshome.org/">CVS</a> repository that is
available for read-only access by anyone.  You can 
interactively view the
repository contents and download individual files
by visiting
<a href="http://www.sqlite.org/cvstrac/dir?d=sqlite">
http://www.sqlite.org/cvstrac/dir?d=sqlite</a>.
To access the repository directly, use the following
commands:
</p>

<blockquote><pre>
cvs -d :pserver:anonymous@www.sqlite.org:/sqlite login
cvs -d :pserver:anonymous@www.sqlite.org:/sqlite checkout sqlite
</pre></blockquote>

#
# Run this script to generated a faq.html output file
#
set rcsid {$Id: faq.tcl,v 1.26 2004/10/10 17:24:55 drh Exp $}
source common.tcl
header {SQLite Frequently Asked Questions</title>}

set cnt 1
proc faq {question answer} {
  set ::faq($::cnt) [list [string trim $question] [string trim $answer]]
  incr ::cnt

  may not exceed 255 characters in length.</p>
}

faq {
  What is the maximum size of a VARCHAR in SQLite?
} {
  <p>SQLite does not enforce datatype constraints.
  A VARCHAR column can hold as much data as you care to put in it.</p>
}

faq {
  Does SQLite support a BLOB type?
} {
  <p>SQLite version 3.0 lets you puts BLOB data into any column, even
  columns that are declared to hold some other type.</p>

  <p>SQLite version 2.8 will store any text data without embedded
  '\000' characters.  If you need to store BLOB data in SQLite version
  2.8 you'll want to encode that data first.
  There is a source file named 
  "<b>src/encode.c</b>" in the SQLite version 2.8 distribution that contains
  implementations of functions named "<b>sqlite_encode_binary()</b>
  and <b>sqlite_decode_binary()</b> that can be used for converting
  binary data to ASCII and back again, if you like.</p>

  VACUUM can take some time to run (around a half second per megabyte
  on the Linux box where SQLite is developed) and it can use up to twice
  as much temporary disk space as the original file while it is running.
  </p>
}

faq {
  Can I use SQLite in my commercial product without paying royalties?
} {
  <p>Yes.  SQLite is in the public domain.  No claim of ownership is made
  to any part of the code.  You can do anything you want with it.</p>
}

faq {
  How do I use a string literal that contains an embedded single-quote (')

#
# Run this script to generated a fileformat.html output file
#
set rcsid {$Id: fileformat.tcl,v 1.13 2004/10/10 17:24:55 drh Exp $}
source common.tcl
header {SQLite Database File Format (Version 2)}
puts {
<h2>SQLite 2.X Database File Format</h2>

<p>
This document describes the disk file format for SQLite versions 2.1

<ul>
<li>The <b>schema</b> layer implemented by the VDBE.</li>
<li>The <b>b-tree</b> layer implemented by btree.c</li>
<li>The <b>pager</b> layer implemented by pager.c</li>
</ul>

<p>
We will describe each layer beginning with the bottom (pager)
layer and working upwards.
</p>

<h3>2.0 &nbsp; The Pager Layer</h3>

<p>
An SQLite database consists of

<p>
Column values are stored in the order that they appear in the CREATE TABLE
statement.  The offsets at the beginning of the record contain the
byte index of the corresponding column value.  Thus, Offset 0 contains
the byte index for Value 0, Offset 1 contains the byte offset
of Value 1, and so forth.  The number of bytes in a column value can
always be found by subtracting offsets.  This allows NULLs to be
recovered from the record unambiguously.
</p>

<p>
Most columns are stored in the b-tree data as described above.
The one exception is column that has type INTEGER PRIMARY KEY.
INTEGER PRIMARY KEY columns correspond to the 4-byte b-tree key.
When an SQL statement attempts to read the INTEGER PRIMARY KEY,

a string that has been crafted so that sorting the string using memcmp()
will sort the corresponding numbers in numerical order.  (See the
sqliteRealToSortable() function in util.c of the SQLite sources for
additional information on this encoding.)  Numbers are also nul-terminated.
Text values consists of the character 'c' followed by a copy of the
text string and a nul-terminator.  These encoding rules result in
NULLs being sorted first, followed by numerical values in numerical
order, followed by text values in lexicographical order.
</p>

<h4>4.4 &nbsp; SQL Schema Storage And Root B-Tree Page Numbers</h4>

<p>
The database schema is stored in the database in a special tabled named
"sqlite_master" and which always has a root b-tree page number of 2.

#
# Run this Tcl script to generate the formatchng.html file.
#
set rcsid {$Id: formatchng.tcl,v 1.12 2004/10/10 17:24:55 drh Exp $ }
source common.tcl
header {File Format Changes in SQLite}
puts {
<h2>File Format Changes in SQLite</h2>

<p>
From time to time, enhancements or bug fixes require a change to

</tr>
<tr>
  <td valign="top">2.7.6 to 2.8.0</td>
  <td valign="top">2003-Feb-14</td>
  <td><p>Version 2.8.0 introduces a change to the format of the rollback
  journal file.  The main database file format is unchanged.  Versions
  2.7.6 and earlier can read and write 2.8.0 databases and vice versa.
  Version 2.8.0 can rollback a transaction that was started by version
  2.7.6 and earlier.  But version 2.7.6 and earlier cannot rollback a
  transaction started by version 2.8.0 or later.</p>

  <p>The only time this would ever be an issue is when you have a program
  using version 2.8.0 or later that crashes with an incomplete
  transaction, then you try to examine the database using version 2.7.6 or
  earlier.  The 2.7.6 code will not be able to read the journal file

#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: lang.tcl,v 1.74 2004/10/10 17:24:55 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>
while at the same time
adding a few features of its own.  This document attempts to
describe precisely what parts of the SQL language SQLite does
and does not support.  A list of <a href="#keywords">keywords</a> is 
given at the end.</p>

<p>In all of the syntax diagrams that follow, literal text is shown in
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>
Transactions can be started manually using the BEGIN
command.  Such transactions usually persist until the next
COMMIT or ROLLBACK command.  But a transaction will also 
ROLLBACK if the database is closed or if an error occurs
and the ROLLBACK conflict resolution algorithm is specified.
See the documentation on the <a href="#conflict">ON CONFLICT</a>
clause for additional information about the ROLLBACK
conflict resolution algorithm.
</p>

<p>
In SQLite version 3.0.8 and later, transactions can be deferred,
immediate, or exclusive.  Deferred means that no locks are acquired
on the database until the database is first accessed.  Thus with a
deferred transaction, the BEGIN statement itself does nothing.  Locks
are not acquired until the first read or write operation.  The first read
operation against a database creates a SHARED lock and the first
write operation creates a RESERVED lock.   Because the acquisition of
locks is deferred until they are needed, it is possible that another
thread or process could create a separate transaction and write to
the database after the BEGIN on the current thread has executed.
If the transaction is immediate, then RESERVED locks
are acquired on all databases as soon as the BEGIN command is
executed, without waiting for the
database to be used.  After a BEGIN IMMEDIATE, you are guaranteed that
no other thread or process will be able to write to the database or
do a BEGIN IMMEDIATE or BEGIN EXCLUSIVE.  Other processes can continue
to read from the database, however.  An exclusive transaction causes
EXCLUSIVE locks to be acquired on all databases.  After a BEGIN

the default algorithm specified here.
See the section titled
<a href="#conflict">ON CONFLICT</a> for additional information.</p>

<p>The exact text
of each CREATE INDEX statement is stored in the <b>sqlite_master</b>
or <b>sqlite_temp_master</b> table, depending on whether the table
being indexed is temporary.  Every time 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>

<p>Indexes are removed with the <a href="#dropindex">DROP INDEX</a> 
command.</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>
table.  Every time 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.
If the original command was a CREATE TABLE AS then then an equivalent
CREATE TABLE statement is synthesized and store in <b>sqlite_master</b>
in place of the original command.
The text of CREATE TEMPORARY TABLE statements are stored in the
<b>sqlite_temp_master</b> table.

<<   >>   &    |
<    <=   >    >=
=    ==   !=   &lt;&gt;   </big>IN
AND   
OR</font>
</pre></blockquote>

<p>Supported unary operators are these:</p>

<blockquote><pre>
<font color="#2c2cf0"><big>-    +    !    ~</big></font>
</pre></blockquote>

<p>Any SQLite value can be used as part of an expression.  
For arithmetic operations, integers are treated as integers.

The [Operator ||] operator is \"concatenate\" - it joins together
the two strings of its operands.
The operator [Operator %] outputs the remainder of its left 
operand modulo its right operand.</p>"
puts {

<a name="like"></a>
<p>The LIKE operator does a wildcard comparison.  The operand
to the right contains the wildcards.}
puts "A percent symbol [Operator %] in the right operand
matches any sequence of zero or more characters on the left.
An underscore [Operator _] on the right
matches any single character on the left."
puts {The LIKE operator is
not case sensitive and will match upper case characters on one

the values generated by the select.  The IN operator may be preceded
by the NOT keyword to invert the sense of the test.</p>

<p>When a SELECT appears within an expression but is not the right
operand of an IN operator, then the first row of the result of the
SELECT becomes the value used in the expression.  If the SELECT yields
more than one result row, all rows after the first are ignored.  If
the SELECT yields no rows, then the value of the SELECT is NULL.</p>

<p>Both simple and aggregate functions are supported.  A simple
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>

it has 2 or more arguments but converts to an aggregate function if given
only a single argument.</td>
</tr>

<tr>
<td valign="top" align="right">min(<i>X</i>,<i>Y</i>,...)</td>
<td valign="top">Return the argument with the minimum value.  Arguments
may be strings in addition to numbers.  The minimum value is determined
by the usual sort order.  Note that <b>min()</b> is a simple function when
it has 2 or more arguments but converts to an aggregate function if given
only a single argument.</td>
</tr>

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

or updating the current row.  Thus the insert or update always occurs.
The command continues executing normally.  No error is returned.
If a NOT NULL constraint violation occurs, the NULL value is replaced
by the default value for that column.  If the column has no default
value, then the ABORT algorithm is used.</p>

<p>When this conflict resolution strategy deletes rows in order to
satisfy a constraint, it does not invoke delete triggers on those
rows.  But that may change in a future release.</p>

<p>The algorithm specified in the OR clause of a COPY, INSERT, or UPDATE
overrides any algorithm specified in a CREATE TABLE or CREATE INDEX.
If no algorithm is specified anywhere, the ABORT algorithm is used.</p>

}

    to the default value when the database is closed and reopened.  Use
    the <a href="#pragma_default_cache_size"><b>default_cache_size</b></a> 
    pragma to check the cache size permanently.</p></li>

<li><p><b>PRAGMA database_list;</b></p>
    <p>For each open database, invoke the callback function once with
    information about that database.  Arguments include the index and 
    the name the database was attached with.  The first row will be for 
    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> 
    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
    every time you reopen the database.</p></li>

<a name="pragma_default_synchronous"></a>
<li><p><b>PRAGMA default_synchronous;
       <br>PRAGMA default_synchronous = FULL; </b>(2)<b>
       <br>PRAGMA default_synchronous = NORMAL; </b>(1)<b>
       <br>PRAGMA default_synchronous = OFF; </b>(0)</p>
    <p>Query or change the setting of the "synchronous" flag in

puts "[Operator *] then all columns of all tables are substituted"
puts {for that one expression.  If the expression is the name of}
puts "a table followed by [Operator .*] then the result is all columns"
puts {in that one table.</p>

<p>The DISTINCT keyword causes a subset of result rows to be returned, 
in which each result row is different.  NULL values are not treated as 
distinct from each other.  The default behavior is that all result rows 
be returned, which can be made explicit with the keyword ALL.</p>

<p>The query is executed against one or more tables specified after
the FROM keyword.  If multiple tables names are separated by commas,
then the query is against the cross join of the various tables.
The full SQL-92 join syntax can also be used to specify joins.
A sub-query

Section VACUUM vacuum

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

puts {
<p>The VACUUM command is an SQLite extension modeled after a similar
command found in PostgreSQL.  If VACUUM is invoked with the name of a
table or index then it is suppose to clean up the named table or index.
In version 1.0 of SQLite, the VACUUM command would invoke 
<b>gdbm_reorganize()</b> to clean up the backend database file.</p>

<p>
VACUUM became a no-op when the GDBM backend was removed from
SQLITE in version 2.0.0.
VACUUM was reimplemented in version 2.8.1.
The index or table name argument is now ignored.
</p>

<p>When an object (table, index, or trigger) is dropped from the 
database, it leaves behind empty space.  This makes the database 
file larger than it needs to be, but can speed up inserts.  In time 
inserts and deletes can leave the database file structure fragmented, 

<tr>	<td width=12%> 'keyword'
	<td>Interpreted as a literal string if it occurs in a legal string 
	context, otherwise as an identifier.
<tr>	<td> "keyword"
	<td>Interpreted as an identifier if it matches a known identifier 
	and occurs in a legal identifier context, otherwise as a string. 
<tr>	<td> [keyword]
	<td> Always interpreted as an identifier. (This notation is used 
	by MS Access and SQL Server.)
</table>

<h2>Fallback Keywords</h2>

<p>These keywords can be used as identifiers for SQLite objects without 
delimiters.</p>

#
# Run this script to generated a nulls.html output file
#
set rcsid {$Id: nulls.tcl,v 1.8 2004/10/10 17:24:55 drh Exp $}
source common.tcl
header {NULL Handling in SQLite}
puts {
<h2>NULL Handling in SQLite Versus Other Database Engines</h2>

<p>
The goal is

The original tests were run in May of 2002.
A copy of the test script is found at the end of this document.
</p>

<p>
SQLite was originally coded in such a way that the answer to
all questions in the chart below would be "Yes".  But the
experiments run on other SQL engines showed that none of them
worked this way.  So SQLite was modified to work the same as
Oracle, PostgreSQL, and DB2.  This involved making NULLs
indistinct for the purposes of the SELECT DISTINCT statement and
for the UNION operator in a SELECT.  NULLs are still distinct
in a UNIQUE column.  This seems somewhat arbitrary, but the desire
to be compatible with other engines outweighted that objection.
</p>

  The 2.8 series of SQLite will continue to be supported with bug
  fixes for the foreseeable future.
}

newsitem {2004-Jun-09} {Version 2.8.14 Released} {
  SQLite version 2.8.14 is a patch release to the stable 2.8 series.
  There is no reason to upgrade if 2.8.13 is working ok for you.
  This is only a bug-fix release.  Most development effort is
  going into version 3.0.0 which is due out soon.
}

newsitem {2004-May-31} {CVS Access Temporarily Disabled} {
  Anonymous access to the CVS repository will be suspended
  for 2 weeks beginning on 2004-June-04.  Everyone will still
  be able to download

  changes to both the C-language API and the underlying file format
  that will enable SQLite to better support internationalization.
  The first beta is schedule for release on 2004-July-01.

  Plans are to continue to support SQLite version 2.8 with
  bug fixes.  But all new development will occur in version 3.0.
}
footer {$Id: oldnews.tcl,v 1.6 2004/10/10 17:24:55 drh Exp $}

#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: opcode.tcl,v 1.14 2004/10/10 17:24:55 drh Exp $}
source common.tcl
header {SQLite Virtual Machine Opcodes}
puts {
<h2>SQLite Virtual Machine Opcodes</h2>
}

set fd [open [lindex $argv 0] r]

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

#
# Run this Tcl script to generate the speed.html file.
#
set rcsid {$Id: speed.tcl,v 1.15 2004/10/10 17:24:55 drh Exp $ }
source common.tcl
header {SQLite Database Speed Comparison}

puts {
<h2>Database Speed Comparison</h2>

<h3>Executive Summary</h3>

tests.
</p>

<p>
I am told that the default PostgreSQL configuration in RedHat 7.3
is unnecessarily conservative (it is designed to
work on a machine with 8MB of RAM) and that PostgreSQL could
be made to run a lot faster with some knowledgeable configuration
tuning.
Matt Sergeant reports that he has tuned his PostgreSQL installation
and rerun the tests shown below.  His results show that
PostgreSQL and MySQL run at about the same speed.  For Matt's
results, visit
</p>

<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;4.640</td></tr>
<tr><td>SQLite 2.7.6:</td><td align="right">&nbsp;&nbsp;&nbsp;3.362</td></tr>
<tr><td>SQLite 2.7.6 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;3.372</td></tr>
</table>

<p>
This test still does 100 full table scans but it uses
uses string comparisons instead of numerical comparisons.
SQLite is over three times faster than PostgreSQL here and about 30%
faster than MySQL.
</p>

<h3>Test 6: Creating an index</h3>
<blockquote>
CREATE INDEX i2a ON t2(a);<br>CREATE INDEX i2b ON t2(b);

#
# Run this TCL script to generate HTML for the goals.html file.
#
set rcsid {$Id: whentouse.tcl,v 1.2 2004/10/10 17:24:55 drh Exp $}

puts {<html>
<head><title>Appropriate Uses of SQLite</title></head>
<body bgcolor=white>
<h1 align=center>Appropriate Uses Of SQLite</h1>
}
puts "<p align=center>

but first and foremost, SQLite strives to be simple.
</p>

<p>
Simplicity in a database engine can be either a strength or a
weakness, depending on what you are trying to do.  In order to
achieve simplicity, SQLite has had to sacrifice other characteristics
that some people find useful, such as high concurrency, fine-grained
access control, a rich set of built-in functions, stored procedures,
esoteric SQL language features, XML and/or Java extensions,
tera- or peta-byte scalability, and so forth.  If you need these
kinds of features and don't mind the added complexity that they
bring, then SQLite is probably not the database for you.
SQLite is not intended to be an enterprise database engine.  It
not designed to compete with Oracle or PostgreSQL.

a colleague.
</p>
</li>

<li><p><b>Stand-in for an enterprise database during demos or testing</b></p>

<p>
If you are writing a client application for an enterprise database engine,
it makes sense to use a generic database backend that allows you to connect
to many different kinds of SQL database engines.  It makes even better
sense to
go ahead and include SQLite in the mix of supported database and to statically
link the SQLite engine in with the client.  That way the client program
can be used standalone with an SQLite data file for testing or for
demonstrations.

it might be possible for two or more client programs to modify the
same part of the same database at the same time, resulting in 
database corruption.  Because this problem results from bugs in
the underlying filesystem implementation, there is nothing SQLite
can do to prevent it.</p>

<p>A good rule of thumb is that you should avoid using SQLite
in situations where the same database will be accessed simultaneously
from many computers over a network filesystem.</p>
</li>

<li><p><b>High-volume Websites</b></p>

<p>SQLite will normally work fine as the database backend to a website.
But if you website is so busy that your are thinking of splitting the
database component off onto a separate machine, then you should 
definitely consider using an enterprise-class client/server database
engine instead of SQLite.</p>
</li>

<li><p><b>Very large datasets</b></p>

<p>When you start a transaction in SQLite (which happens automatically
before any write operation that is not within an explicit BEGIN...COMMIT)
the engine has to allocate a bitmap of dirty pages in the disk file to
help it manage its rollback journal.  SQLite needs 256 bytes of RAM for
every 1MB of database.  For smaller databases, the amount of memory
required is not a problem, but when database begin to grow into the
multi-gigabyte range, the size of the bitmap can get quite large.  If
you need to store and modify more than a few dozen GB of data, you should
consider using a different database engine.
</p>
</li>

<li><p><b>High Concurrency</b></p>

<p>
SQLite uses reader/writer locks on the entire database file.  That means
if any process is reading from any part of the database, all other
processes are prevented from writing any other part of the database.
Similarly, if any one process is writing to any part of the database,
all other processes are prevented from reading any other part of the
database.
For many situations, this is not a problem.  Each application
does its database work quickly and moves on, and no lock lasts for more
than a few dozen milliseconds.  But there are some problems that require
more concurrency, and those problems will need to seek a different
solution.
</p>
</li>

</ul>

}