Documentation Source Text

<a href="http://www.sqlite.org/src/finfo?name=ext/async/sqlite3async.h">
sqlite3async.h header file</a> for details.


<h2>3.0 PORTING</h2>

<p>Currently the asynchronous IO extension is compatible with win32 systems
and systems that support the pthreads interface, including Mac OSX, Linux, 
and other varieties of Unix. 

<p>To port the asynchronous IO extension to another platform, the user must
implement mutex and condition variable primitives for the new platform.
Currently there is no externally available interface to allow this, but
modifying the code within sqlite3async.c to include the new platforms
concurrency primitives is relatively easy. Search within sqlite3async.c

<a href="http://www.sqlite.org/src/finfo?name=ext/async/sqlite3async.h">
sqlite3async.h header file</a> for details.


<h2>3.0 PORTING</h2>

<p>Currently the asynchronous IO extension is compatible with win32 systems
and systems that support the pthreads interface, including Mac OS X, Linux, 
and other varieties of Unix. 

<p>To port the asynchronous IO extension to another platform, the user must
implement mutex and condition variable primitives for the new platform.
Currently there is no externally available interface to allow this, but
modifying the code within sqlite3async.c to include the new platforms
concurrency primitives is relatively easy. Search within sqlite3async.c

<ul>
<li> sqlite3PagerLockingMode
<li> sqlite3PagerJournalMode
<li> sqlite3PagerIsMemdb (vacuum and backup).
<li> sqlite3PagerJournalSizeLimit
<li> sqlite3PagerFile (used by sqlite3_file_control() and pragma lock_proxy_file).
<li> sqlite3PagerPagecounT (pragma page_count and backup).
<li> Page APIs used by backup routines:
  <ul>
    <li> sqlite3PagerGet
    <li> sqlite3PagerWrite
    <li> sqlite3PagerGetData
    <li> sqlite3PagerGetExtra
    <li> sqlite3PagerUnref
................................................................................
               is overwritten with the page number of the first new sibling page, and 
               so on.

	  <li> If the page being balanced is (was) a leaf page of a table
	       b-tree, the cells that contained pointers to the old sibling
               pages are replaced by a divider cell associated with all but the
               right-most sibling page. The child-page number stored in each divider
               cell is set to the page number of the associated sibling. The ingeger key 
               value stored in each divider cell is a copy of the largest integer key
               value stored on the associated sibling page.

	  <li> Before balancing, the parent page contained a pointer to the right-most
               sibling page, either as part of a cell or as the right-child pointer
               stored in the page header. Either way, this value must be overwritten
               with the page number of the new right-most sibling page.

<ul>
<li> sqlite3PagerLockingMode
<li> sqlite3PagerJournalMode
<li> sqlite3PagerIsMemdb (vacuum and backup).
<li> sqlite3PagerJournalSizeLimit
<li> sqlite3PagerFile (used by sqlite3_file_control() and pragma lock_proxy_file).
<li> sqlite3PagerPagecount (pragma page_count and backup).
<li> Page APIs used by backup routines:
  <ul>
    <li> sqlite3PagerGet
    <li> sqlite3PagerWrite
    <li> sqlite3PagerGetData
    <li> sqlite3PagerGetExtra
    <li> sqlite3PagerUnref
................................................................................
               is overwritten with the page number of the first new sibling page, and 
               so on.

	  <li> If the page being balanced is (was) a leaf page of a table
	       b-tree, the cells that contained pointers to the old sibling
               pages are replaced by a divider cell associated with all but the
               right-most sibling page. The child-page number stored in each divider
               cell is set to the page number of the associated sibling. The integer key 
               value stored in each divider cell is a copy of the largest integer key
               value stored on the associated sibling page.

	  <li> Before balancing, the parent page contained a pointer to the right-most
               sibling page, either as part of a cell or as the right-child pointer
               stored in the page header. Either way, this value must be overwritten
               with the page number of the new right-most sibling page.

<li>Fix a bug in the LIKE optimizer that occurs when the last character
before the first wildcard is an upper-case "Z"</li>
<li>Added the "bitvec" object for keeping track of which pages have
been journalled.  Improves speed and reduces memory consumption, especially
for large database files.</li>
<li>Get the SQLITE_ENABLE_LOCKING_STYLE macro working again on Mac OS X.</li>
<li>Store the statement journal in the temporary file directory instead of
colocated with the database file.</li>
<li>Many improvements and cleanups to the configure script</li>
}

chng {2008 Feb 6 (3.5.6)} {
<li>Fix a bug ([ticket #2913])
that prevented virtual tables from working in a LEFT JOIN.
The problem was introduced into shortly before the 3.5.5 release.</li>
................................................................................
to "etilqs".</li>
}

chng {2006 October 9 (3.3.8)} {
<li>Support for full text search using the
<a href="http://www.sqlite.org/cvstrac/wiki?p=FullTextIndex">FTS1 module</a>
(beta)</li>
<li>Added OS-X locking patches (beta - disabled by default)</li>
<li>Introduce extended error codes and add error codes for various
kinds of I/O errors.</li>
<li>Added support for IF EXISTS on CREATE/DROP TRIGGER/VIEW</li>
<li>Fix the regression test suite so that it works with Tcl8.5</li>
<li>Enhance sqlite3_set_authorizer() to provide notification of calls to
    SQL functions.</li>
<li>Added experimental API:  sqlite3_auto_extension()</li>
................................................................................
    record into tables with around 125 columns.</li>
<li>sqlite3_step() is now much more likely to invoke the busy handler
    and less likely to return SQLITE_BUSY.</li>
<li>Fix memory leaks that used to occur after a malloc() failure.</li>
}

chng {2005 March 11 (3.1.5)} {
<li>The ioctl on OS-X to control syncing to disk is F_FULLFSYNC,
    not F_FULLSYNC.  The previous release had it wrong.</li>
}

chng {2005 March 10 (3.1.4)} {
<li>Fix a bug in autovacuum that could cause database corruption if
a CREATE UNIQUE INDEX fails because of a constraint violation.
This problem only occurs if the new autovacuum feature introduced in
version 3.1 is turned on.</li>
<li>The F_FULLSYNC ioctl (currently only supported on OS-X) is disabled
if the synchronous pragma is set to something other than "full".</li>
<li>Add additional forward compatibility to the future version 3.2 database 
file format.</li>
<li>Fix a bug in WHERE clauses of the form (rowid<'2')</li>
<li>New [omitfeatures | SQLITE_OMIT_...] compile-time options added</li>
<li>Updates to the man page</li>
<li>Remove the use of strcasecmp() from the shell</li>
................................................................................
    row of a table to be repeated during a database scan.</li>
}

chng {2001 Dec 14 (2.1.6)} {
<li>Fix the locking mechanism yet again to prevent
    <b>sqlite_exec()</b> from returning SQLITE_PROTOCOL
    unnecessarily.  This time the bug was a race condition in
    the locking code.  This change effects both POSIX and Windows users.</li>
}

chng {2001 Dec 6 (2.1.5)} {
<li>Fix for another problem (unrelated to the one fixed in 2.1.4) 
    that sometimes causes <b>sqlite_exec()</b> to return SQLITE_PROTOCOL
    unnecessarily.  This time the bug was
    in the POSIX locking code and should not effect Windows users.</li>
................................................................................
    code.
    </li>
}

chng {2001 Mar 14 (1.0.24)} {
<li>Fix a bug which was causing
    the UPDATE command to fail on systems where "malloc(0)" returns
    NULL.  The problem does not appear Windows, Linux, or HPUX but does 
    cause the library to fail on QNX.
    </li>
}

chng {2001 Feb 19 (1.0.23)} {
<li>An unrelated (and minor) bug from Mark Muranwski fixed.  The algorithm
    for figuring out where to put temporary files for a "memory:" database
................................................................................
chng {2000 Aug 1} {
<li>The parser's stack was overflowing on a very long UPDATE statement.
    This is now fixed.</li>
}

chng {2000 July 31} {
<li>Finish the <a href="vdbe.html">VDBE tutorial</a>.</li>
<li>Added documentation on compiling to WindowsNT.</li>
<li>Fix a configuration program for WindowsNT.</li>
<li>Fix a configuration problem for HPUX.</li>
}

chng {2000 July 29} {
<li>Better labels on column names of the result.</li>
}

<li>Fix a bug in the LIKE optimizer that occurs when the last character
before the first wildcard is an upper-case "Z"</li>
<li>Added the "bitvec" object for keeping track of which pages have
been journalled.  Improves speed and reduces memory consumption, especially
for large database files.</li>
<li>Get the SQLITE_ENABLE_LOCKING_STYLE macro working again on Mac OS X.</li>
<li>Store the statement journal in the temporary file directory instead of
collocated with the database file.</li>
<li>Many improvements and cleanups to the configure script</li>
}

chng {2008 Feb 6 (3.5.6)} {
<li>Fix a bug ([ticket #2913])
that prevented virtual tables from working in a LEFT JOIN.
The problem was introduced into shortly before the 3.5.5 release.</li>
................................................................................
to "etilqs".</li>
}

chng {2006 October 9 (3.3.8)} {
<li>Support for full text search using the
<a href="http://www.sqlite.org/cvstrac/wiki?p=FullTextIndex">FTS1 module</a>
(beta)</li>
<li>Added Mac OS X locking patches (beta - disabled by default)</li>
<li>Introduce extended error codes and add error codes for various
kinds of I/O errors.</li>
<li>Added support for IF EXISTS on CREATE/DROP TRIGGER/VIEW</li>
<li>Fix the regression test suite so that it works with Tcl8.5</li>
<li>Enhance sqlite3_set_authorizer() to provide notification of calls to
    SQL functions.</li>
<li>Added experimental API:  sqlite3_auto_extension()</li>
................................................................................
    record into tables with around 125 columns.</li>
<li>sqlite3_step() is now much more likely to invoke the busy handler
    and less likely to return SQLITE_BUSY.</li>
<li>Fix memory leaks that used to occur after a malloc() failure.</li>
}

chng {2005 March 11 (3.1.5)} {
<li>The ioctl on Mac OS X to control syncing to disk is F_FULLFSYNC,
    not F_FULLSYNC.  The previous release had it wrong.</li>
}

chng {2005 March 10 (3.1.4)} {
<li>Fix a bug in autovacuum that could cause database corruption if
a CREATE UNIQUE INDEX fails because of a constraint violation.
This problem only occurs if the new autovacuum feature introduced in
version 3.1 is turned on.</li>
<li>The F_FULLSYNC ioctl (currently only supported on Mac OS X) is disabled
if the synchronous pragma is set to something other than "full".</li>
<li>Add additional forward compatibility to the future version 3.2 database 
file format.</li>
<li>Fix a bug in WHERE clauses of the form (rowid<'2')</li>
<li>New [omitfeatures | SQLITE_OMIT_...] compile-time options added</li>
<li>Updates to the man page</li>
<li>Remove the use of strcasecmp() from the shell</li>
................................................................................
    row of a table to be repeated during a database scan.</li>
}

chng {2001 Dec 14 (2.1.6)} {
<li>Fix the locking mechanism yet again to prevent
    <b>sqlite_exec()</b> from returning SQLITE_PROTOCOL
    unnecessarily.  This time the bug was a race condition in
    the locking code.  This change affects both POSIX and Windows users.</li>
}

chng {2001 Dec 6 (2.1.5)} {
<li>Fix for another problem (unrelated to the one fixed in 2.1.4) 
    that sometimes causes <b>sqlite_exec()</b> to return SQLITE_PROTOCOL
    unnecessarily.  This time the bug was
    in the POSIX locking code and should not effect Windows users.</li>
................................................................................
    code.
    </li>
}

chng {2001 Mar 14 (1.0.24)} {
<li>Fix a bug which was causing
    the UPDATE command to fail on systems where "malloc(0)" returns
    NULL.  The problem does not appear on Windows, Linux, or HPUX but does 
    cause the library to fail on QNX.
    </li>
}

chng {2001 Feb 19 (1.0.23)} {
<li>An unrelated (and minor) bug from Mark Muranwski fixed.  The algorithm
    for figuring out where to put temporary files for a "memory:" database
................................................................................
chng {2000 Aug 1} {
<li>The parser's stack was overflowing on a very long UPDATE statement.
    This is now fixed.</li>
}

chng {2000 July 31} {
<li>Finish the <a href="vdbe.html">VDBE tutorial</a>.</li>
<li>Added documentation on compiling to WinNT.</li>
<li>Fix a configuration program for WinNT.</li>
<li>Fix a configuration problem for HPUX.</li>
}

chng {2000 July 29} {
<li>Better labels on column names of the result.</li>
}

<tr><td valign="top">
<a href="http://www.apple.com/">
<img src="images/foreignlogos/apple.gif" border="0">
</a></td>
<td valign="top">
[http://www.apple.com/ | Apple] uses SQLite for many functions 
within Mac OS-X, including 
[http://www.apple.com/macosx/features/mail.html | Apple Mail], 
[http://www.apple.com/macosx/features/safari.html | Safari], 
and in [http://www.apple.com/aperture/ | Aperture].
Apple uses SQLite in
the [http://www.apple.com/iphone/ | iPhone] and in the
[http://www.apple.com/ipodtouch/ | iPod touch] and in
[http://www.apple.com/itunes/ | iTunes] software.

<tr><td valign="top">
<a href="http://www.apple.com/">
<img src="images/foreignlogos/apple.gif" border="0">
</a></td>
<td valign="top">
[http://www.apple.com/ | Apple] uses SQLite for many functions 
within Mac OS X, including 
[http://www.apple.com/macosx/features/mail.html | Apple Mail], 
[http://www.apple.com/macosx/features/safari.html | Safari], 
and in [http://www.apple.com/aperture/ | Aperture].
Apple uses SQLite in
the [http://www.apple.com/iphone/ | iPhone] and in the
[http://www.apple.com/ipodtouch/ | iPod touch] and in
[http://www.apple.com/itunes/ | iTunes] software.

<p>The checksum value is used to guard against incomplete writes of
a journal page record following a power failure.  A different random nonce
is used each time a transaction is started in order to minimize the risk
that unwritten sectors might by chance contain data from the same page
that was a part of prior journals.  By changing the nonce for each
transaction, stale data on disk will still generate an incorrect checksum
and be detected with high probability.  The checksum only uses a sparce sample
of 32-bit words from the data record for performance reasons - design studies 
during the planning phases of SQLite 3.0.0 showed
a significant performance hit in checksumming the entire page.</p>

<p>Let the page count value at offset 8 in the journal header be M.
^If M is greater than zero then after M page records the journal file
may be zero padded out to the next multiple of the sector size and another

<p>The checksum value is used to guard against incomplete writes of
a journal page record following a power failure.  A different random nonce
is used each time a transaction is started in order to minimize the risk
that unwritten sectors might by chance contain data from the same page
that was a part of prior journals.  By changing the nonce for each
transaction, stale data on disk will still generate an incorrect checksum
and be detected with high probability.  The checksum only uses a sparse sample
of 32-bit words from the data record for performance reasons - design studies 
during the planning phases of SQLite 3.0.0 showed
a significant performance hit in checksumming the entire page.</p>

<p>Let the page count value at offset 8 in the journal header be M.
^If M is greater than zero then after M page records the journal file
may be zero padded out to the next multiple of the sector size and another

<p>To generate a TCL-loadable library for SQLite on Linux, the following
command will suffice:</p>

<blockquote><pre>
gcc -o libtclsqlite3.so -shared tclsqlite3.c -lpthread -ldl -ltcl
</pre></blockquote>

<p>Building shared libraries for Mac OSX and Windows is not nearly so simple,
unfortunately.  For those platforms it is best to use the configure script
and makefile that is included with the [TEA tarball].</p>

<p>To generate a standalone tclsh that is statically linked with SQLite,
use this compiler invocation:</p>

<blockquote><pre>
gcc -DTCLSH=1 tclsqlite3.c -ltcl -lpthread -ldl -lz -lm
</pre></blockquote>

<p>The trick here is the -DTCLSH=1 option.  The TCL interface module for
SQLite includes a <b>main()</b> procedure that initializes a TCL interpreter
and enters a command-line loop when it is compiled with -DTCLSH=1.  The
command above works on both Linux and Mac OSX, though one may need to adjust
the library options depending on the platform and which version of TCL one
is linking against.</p>

<tcl>hd_fragment {amal} {building the amalgamation}</tcl>
<h2>Building The Amalgamation</h2>

<p>The versions of the SQLite amalgamation that are supplied on the
................................................................................
<li>[SQLITE_OMIT_VACUUM]
<li>[SQLITE_OMIT_VIEW]
<li>[SQLITE_OMIT_VIRTUALTABLE]
</ul>

<p>To build a custom amalgamation, first download the original individual
source files onto a unix or unix-like development platform.  (Any Linux or
mac osx machine will suffice.  Windows will work if loaded with
[http://www.cygwin.com/ | Cygwin].)  Be sure to get the original source
files not the "preprocessed source files".  One can obtain the complete
set of original source files either from the [download page] or directly
from the [http://www.sqlite.org/src | configuration management system].</p>

<p>Suppose the SQLite source tree is stored in a directory named "sqlite".
Plan to construct the amalgamation in a parallel directory named (for

<p>To generate a TCL-loadable library for SQLite on Linux, the following
command will suffice:</p>

<blockquote><pre>
gcc -o libtclsqlite3.so -shared tclsqlite3.c -lpthread -ldl -ltcl
</pre></blockquote>

<p>Building shared libraries for Mac OS X and Windows is not nearly so simple,
unfortunately.  For those platforms it is best to use the configure script
and makefile that is included with the [TEA tarball].</p>

<p>To generate a standalone tclsh that is statically linked with SQLite,
use this compiler invocation:</p>

<blockquote><pre>
gcc -DTCLSH=1 tclsqlite3.c -ltcl -lpthread -ldl -lz -lm
</pre></blockquote>

<p>The trick here is the -DTCLSH=1 option.  The TCL interface module for
SQLite includes a <b>main()</b> procedure that initializes a TCL interpreter
and enters a command-line loop when it is compiled with -DTCLSH=1.  The
command above works on both Linux and Mac OS X, though one may need to adjust
the library options depending on the platform and which version of TCL one
is linking against.</p>

<tcl>hd_fragment {amal} {building the amalgamation}</tcl>
<h2>Building The Amalgamation</h2>

<p>The versions of the SQLite amalgamation that are supplied on the
................................................................................
<li>[SQLITE_OMIT_VACUUM]
<li>[SQLITE_OMIT_VIEW]
<li>[SQLITE_OMIT_VIRTUALTABLE]
</ul>

<p>To build a custom amalgamation, first download the original individual
source files onto a unix or unix-like development platform.  (Any Linux or
Mac OS X machine will suffice.  Windows will work if loaded with
[http://www.cygwin.com/ | Cygwin].)  Be sure to get the original source
files not the "preprocessed source files".  One can obtain the complete
set of original source files either from the [download page] or directly
from the [http://www.sqlite.org/src | configuration management system].</p>

<p>Suppose the SQLite source tree is stored in a directory named "sqlite".
Plan to construct the amalgamation in a parallel directory named (for

  There is no compelling reason to upgrade from versions 3.6.12 or
  3.6.13 if those prior versions are working.  Though many users may
  benefit from the improved performance.
}

newsitem {2008-Dec-16} {Version 3.6.7} {
  SQLite [version 3.6.7] contains a major cleanup of the Unix driver,
  and support for the new Proxy Locking mechanism on MacOSX.  Though
  the Unix driver is reorganized, its functionality is the same and so
  applications should not notice a difference.
}

newsitem {2008-Nov-26} {Version 3.6.6.2} {
  This release fixes a bug that was introduced into SQLite [version 3.6.6]
  and which seems like it might be able to cause database corruption.  This

  There is no compelling reason to upgrade from versions 3.6.12 or
  3.6.13 if those prior versions are working.  Though many users may
  benefit from the improved performance.
}

newsitem {2008-Dec-16} {Version 3.6.7} {
  SQLite [version 3.6.7] contains a major cleanup of the Unix driver,
  and support for the new Proxy Locking mechanism on Mac OS X.  Though
  the Unix driver is reorganized, its functionality is the same and so
  applications should not notice a difference.
}

newsitem {2008-Nov-26} {Version 3.6.6.2} {
  This release fixes a bug that was introduced into SQLite [version 3.6.6]
  and which seems like it might be able to cause database corruption.  This