Documentation Source Text

proc chng {date desc {options {}}} {
  global nChng aChng
  set aChng($nChng) [list $date $desc $options]
  incr nChng
}

chng {2015-06-00 (3.8.10)} {
<li>Added the sqldiff.exe utility program for computing the differences between two
    SQLite database files.
<li>Performance optimizations for sorting and "PRAGMA integrity_check"

<li>Fix many obscure problems discovered while SQL fuzzing
<li>Identify all methods for important objects in the interface documentation.

<li>Made the [American Fuzzy Lop fuzzer]
    a standard part of SQLite's [testing|testing strategy].
}

chng {2015-04-08 (3.8.9)} {
<li>Add VxWorks-7 as an officially supported and tested platform.
<li>Added the [sqlite3_status64()] interface.

proc chng {date desc {options {}}} {
  global nChng aChng
  set aChng($nChng) [list $date $desc $options]
  incr nChng
}

chng {2015-06-00 (3.8.10)} {
<li>Added the [sqldiff.exe] utility program for computing the differences between two
    SQLite database files.
<li>Performance optimizations for [ORDER BY], [VACUUM], [CREATE INDEX],
    [PRAGMA integrity_check], and [PRAGMA quick_check].
<li>Fix many obscure problems discovered while [SQL fuzzing].
<li>Identify all methods for important objects in the interface documentation.
    ([sqlite3_context|example])
<li>Made the [American Fuzzy Lop fuzzer]
    a standard part of SQLite's [testing|testing strategy].
}

chng {2015-04-08 (3.8.9)} {
<li>Add VxWorks-7 as an officially supported and tested platform.
<li>Added the [sqlite3_status64()] interface.

<a href="selfcontained.html">self-contained</a>, 
<a href="serverless.html">serverless</a>,
<a href="zeroconf.html">zero-configuration</a>,
<a href="transactional.html">transactional</a>
SQL database engine.
SQLite is the 
<a href="mostdeployed.html">most widely deployed</a>
SQL database engine in the world.
The source code for SQLite is in the
<a href="copyright.html">public domain</a>.</p>


<hr style="color: #044a64" height="2">

<h3>Sponsors</h3>
<p>Ongoing development and maintenance of SQLite is
sponsored in part by <a href="consortium.html">SQLite Consortium</a>
members, including:</p>

<a href="selfcontained.html">self-contained</a>, 
<a href="serverless.html">serverless</a>,
<a href="zeroconf.html">zero-configuration</a>,
<a href="transactional.html">transactional</a>
SQL database engine.
SQLite is the 
<a href="mostdeployed.html">most widely deployed</a>
database engine in the world.
The source code for SQLite is in the
<a href="copyright.html">public domain</a>.
<a href="about.html">More...</a></p>

<hr style="color: #044a64" height="2">

<h3>Sponsors</h3>
<p>Ongoing development and maintenance of SQLite is
sponsored in part by <a href="consortium.html">SQLite Consortium</a>
members, including:</p>

<title>Most Widely Deployed SQL Database Engine</title>

<h2>Most Widely Deployed SQL Database</h2>

<p>We <em>believe</em> that there are more copies of SQLite
in use around the world than any other SQL database engine,
and possibly all other SQL database engines combined.  We cannot
be certain of this since we have no way of measuring either
the number of SQLite deployments nor the number of deployments
of other databases.  But we believe the claim is defensible.</p>

<p>The belief that SQLite is the most widely deployed SQL
database engine stems from its use as an embedded database.
Other database engines, such as MySQL, PostgreSQL, or Oracle,
are typically found one to a server.  And usually a single
server can serve multiple users.  With SQLite, on the other
hand, a single user will typically have exclusive use of
multiple copies of SQLite.  SQLite is used on servers, but
it is also used on desktop PC, and in cellphones, and PDAs,
and MP3-players, and set-top boxes.</p>

<h3>Estimates</h3>

<p>At the end of 2006, there were 100 million websites on the internet.
<a href="http://news.netcraft.com/archives/2006/11/01/november_2006_web_server_survey.html">&#91;1]</a>
Let us use that number as a proxy for the number of deployed
SQL database engines other than SQLite.  Not every website
runs an SQL database engine and not every SQL database engine
runs a website.  Larger websites run multiple
database engines.  But the vast majority of smaller websites
(the long tail) share
a database engine with several other websites,
if they use a database engine at all.
And many large SQL database installations have nothing to do with
websites.
So using the number of websites as a surrogate for the number of operational
SQL database engines is a crude approximation, but it is the best
we have so we will go with it.  (Readers are encouraged to submit
better estimates.)</p>

<p>Now let's consider where SQLite is used:</p>

<ul>
<li>300 million copies of Mozilla Firefox.</li>
<li>20 million Mac computers, each of which contains multiple
copies of SQLite</li>
<li>20 million websites run PHP which has SQLite built in.  
<a href="http://www.php.net/usage.php">&#91;3]</a>  We have no
way of estimating what fraction of those sites actively use
SQLite, but we think it is a significant fraction.</li>
<li>450 million registered [http://www.skype.com/ | Skype] users.</li>
<li>20 million Symbian smartphones shipped in Q3 2007
<a href="http://www.symbian.com/news/pr/2007/pr20079552.html">&#91;5]</a>
Newer versions of the SymbianOS have SQLite built in.  It is unclear
exactly how many Symbian phones actually contain SQLite, so we will
use a single quarter's sales as a lower bound.</li>
<li>10 million Solaris 10 installations, all of which require SQLite in
order to boot.</li>
<li>Millions and millions of copies of
[http://www.mcafee.com/|McAfee] anti-virus software all
use SQLite internally.</li>
<li>Millions of iPhones use SQLite</li>
<li>Millions and millions of other cellphones from 
manufactures other than Symbian and Apple use SQLite.
This has not been publicly acknowledged by the manufactures
but it is known to the SQLite developers.</li>
<li>There are perhaps millions of additional deployments of
SQLite that the SQLite developers do not know about.</li>
</ul>

<p>
By these estimates, we see at least 500 million SQLite deployments
and about 100 million deployments of other SQL database engines.  These
estimates are obviously very rough and may be off significantly.  
But there is a wide margin. So the SQLite
developers think it is likely that SQLite is the most widely deployed
SQL database engine in the world.
</p>

<title>Most Widely Deployed SQL Database Engine</title>

<h2>Most Widely Deployed and Used Database Engine</h2>

<p>SQLite is likely used more than all other database engines
combined.  Billions and billions of copies
of SQLite exist in the wild.  SQLite is found in:

<ul>
<li>Every Android device
<li>Every iPhone and iOS device
<li>Every Mac
<li>Every Firefox, Chrome, and Safari web browser
<li>Every instance of Skype
<li>Every instance of iTunes
<li>Every Dropbox client
<li>Every TurboTax and QuickBooks
<li>Most PHP and Python installations
<li>Most modern television sets and set-top cable boxes
<li>Countless millions of other applications
</ul>

<h2>Most Widely Deployed Software Module of Any Type?</h2>

<p>SQLite is perhaps one of the top five most deployed
software modules of any description.  It might even be the
number one.  Other libraries with similar reach include:

<ul>
<li>
The original <a href="http://zlib.net">zlib</a> implementation by
Jean-loup Gailly and Mark Adler.
<li>
The original reference implementation for
<a href="http://www.libpng.org">libpng</a>
<li>
Libjpeg from the <a href="http://ijg.org">Independent JPEG Group</a>
</ul>

<p>
<i>(If you know of any other software that should
be added to this list, please send a quick
email note to <a href="mailto:drh@sqlite.org">drh@sqlite.org</a>.
Thanks.)</i>
</p>

<p>
Libc is omitted from the above list even though
it is more common than SQLite because
libc is not a single software component but rather several
competing implementations (ex: BSD vs. GNU) with similar interfaces.
</p>

<p>
Precise numbers are difficult to obtain and so exact rankings
are impossible.  But some commentators observe that SQLite tends
to be statically linked and thus have multiple instances on
each machine, whereas libz, libpng, and libjpeg tend to have just
a single instance per machine in the form of a shared library or DLL.
So even though the number of devices containing libz, 
libpng, and/or libjpeg, may be greater than the number of
devices that contain SQLite, the total number of instances per device
<em>might</em> be higher for SQLite and so SQLite <em>might</em>
be the single most widely deployed and used software component.
</p>

<title>sqldiff.exe: Database Difference Utility</title>
<tcl>hd_keywords sqldiff sqldiff.exe</tcl>
<h2>The sqldiff.exe Utility Program</h2>

<p>
The <tt>sqldiff.exe</tt> is a command-line utility program that
displays the differences between SQLite databases.  Example
usage:

<blockquote><pre>
sqldiff &#91;options&#93; database1.sqlite database2.sqlite
</pre></blockquote>

<p>
The usual output is SQL statements that will transform
database1.sqlite (the "source" database) into database2.sqlite
(the "destination" database).  This behavior can be
altered using command-line switches:

<dl>
<dt><b>--changeset FILE</b></dt>
<dd><p>Do not write changes to standard output.  Instead, write a (binary)
       changeset file into FILE.  The changeset can be interpreted using
       the sessions extension to SQLite.</dd>
<dt><b>--primarykey</b></dt>
<dd><p>Use the schema-defined [PRIMARY KEY] instead of the [rowid] to
       pair rows in the source and destination database.  (See additional
       explanation below.)</dd>
<dt><b>--schema</b></dt>
<dd><p>Show only differences in the schema not the table content</p></dd>
<dt><b>--summary</b></dt>
<dd><p>Show how many rows have changed on each table, but do not show
       the actual chagnes</dd>
<dt><b>--table TABLE</b></dt>
<dd><p>Show only the differences in content for TABLE, not for the
       entire database</p></dd>
</dl>

<h3>How It Works</h3>

<p>The sqldiff.exe utility works by finding rows in the source and
destination that are logical "pairs".  The default behavior is to
treat two rows as pairs if they are in tables with the same name
and they have the same [rowid], or in the case of a [WITHOUT ROWID]
table if they have the same [PRIMARY KEY].  Any differences in the
content of paired rows are output as UPDATEs.  Rows in the source
database that could not be paired are output as DELETEs.  Rows in
the destination database that could not be paired are output as
INSERTs.

<p>The --primarykey flag changes the pairing algorithm slightly so
that the schema-declared [PRIMARY KEY] is always used for pairing,
even on tables that have a [rowid].  This is often a better choice
for finding differences, however it can lead to missed differences in
the case of rows that have one or more PRIMARY KEY columns set to
NULL.</p>

<h3>Limitations</h3>

<ol>
<li>
<p>The sqldiff.exe utility is unable to compute differences for
rowid tables for which the rowid is inaccessible.  An example of
a table with an inaccessible rowid is:

<blockquote><pre>
CREATE TABLE inaccessible_rowid(
   "rowid" TEXT,
   "oid" TEXT,
   "_rowid_" TEXT
);
</pre></blockquote>

<li><p>
Differences cannot be computed for tables that use application-defined
[collating sequences] since sqldiff.exe will be unable to compare values.

<li><p>
The sqldiff.exe utility does not (currently) display differences in
[CREATE TRIGGER|TRIGGERs], [CREATE VIEW|VIEWs], or [virtual tables].
</ol>

<h3>3.4 Compound failure tests</h3>

<p>The test suites for SQLite also explore the result of stacking
multiple failures.  For example, tests are run to ensure correct behavior
when an I/O error or OOM fault occurs while trying to recover from a
prior crash.

<tcl>hd_fragment fuzztesting</tcl>
<h2>4.0 Fuzz Testing</h2>

<p>[http://en.wikipedia.org/wiki/Fuzz_testing | Fuzz testing]
seeks to establish that SQLite responds correctly to invalid, out-of-range,
or malformed inputs.</p>

<h3>4.1 SQL Fuzz</h3>

<h3>3.4 Compound failure tests</h3>

<p>The test suites for SQLite also explore the result of stacking
multiple failures.  For example, tests are run to ensure correct behavior
when an I/O error or OOM fault occurs while trying to recover from a
prior crash.

<tcl>hd_fragment fuzztesting {fuzz testing} {SQL fuzzing}</tcl>
<h2>4.0 Fuzz Testing</h2>

<p>[http://en.wikipedia.org/wiki/Fuzz_testing | Fuzz testing]
seeks to establish that SQLite responds correctly to invalid, out-of-range,
or malformed inputs.</p>

<h3>4.1 SQL Fuzz</h3>