Documentation Source Text

#!/usr/bin/tclsh
set usage {Usage:
  hlinkch.tcl <doc_root> <hldb> <html_pages>
}

if {$argc >= 2 && [lindex $argv 0] eq "--url-normalize-log"} {
  set ::iun [open [lindex $argv 1] w]
  incr argc -2
  set $argv [lrange $argv 2 end]
} else { set ::iun "" }

if {$argc < 3} { puts $usage; exit 0 }

set ::doc_root_nom [lindex $argv 0]
set ::drre "^$::doc_root_nom/"
set ::doc_root [file normalize $::doc_root_nom]
set ::doc_rlen [expr [string length $::doc_root] + 1]
set hlink_db [lindex $argv 1]
set argv [lrange $argv 2 end]
incr argc -2
set sdir [file dirname [info script]]
source [file join $sdir search hdom.tcl]

# Normalize input filename to be docroot-relative.
proc finorm {fn} {
  regsub $::drre $fn {} fn
  set fn [file normalize $fn]
  set drix [string first $::doc_root $fn]
  if {$drix != 0} { return $fn }
  return [string range $fn $::doc_rlen end]
}

# Normalize internal URL filename to be docroot-relative.
# Inputs are relative to the source referencing the URL.
# Directories . or .. must be leading for this to work.
proc iunorm {ufn sfn} {
  set rv $ufn
  set sdir [file dirname $sfn]
  if {$sdir eq "."} { set sds [list] } else { set sds [file split $sdir] }
  if {[regexp {^(\.\.?)/(.*)} $ufn _ dd ufnnd]} {
    switch $dd {
      . { set rv [file join {*}$sds $ufnnd] }
      .. {
        set rv [file join {*}[lrange $sds 0 end-1] $ufnnd]
      }
    }
  } else {
    set rv [file join {*}$sds $ufn]
  }
  if {$::iun ne ""} { puts $::iun "$ufn|$sfn|$rv" }
  return $rv
}

set owd [pwd]
cd $::doc_root
set inhtml [lmap f $argv {finorm $f}]

package require sqlite3

try {
  sqlite3 db :memory:
  db eval {
    CREATE TABLE IF NOT EXISTS LinkDefs(
     url TEXT, frag TEXT,
     UNIQUE(url, frag) ON CONFLICT IGNORE
    );
    CREATE TABLE IF NOT EXISTS IntLinkRefs(
     url TEXT, frag TEXT, fsrc TEXT,
     UNIQUE(url, frag, fsrc) ON CONFLICT IGNORE
    );
    CREATE TABLE IF NOT EXISTS ExtLinkRefs(
     url TEXT, frag TEXT, fsrc TEXT,
     UNIQUE(url, frag, fsrc) ON CONFLICT IGNORE
    );
    CREATE VIEW IF NOT EXISTS BrokenPageLinks AS
    SELECT r.url || iif(r.frag <> '', '#'||r.frag, '') AS url, r.fsrc as fsrc
    FROM IntLinkRefs r LEFT JOIN LinkDefs d USING(url)
    WHERE d.url IS NULL;
    CREATE VIEW IF NOT EXISTS BrokenFragLinks AS
    SELECT r.url || iif(r.frag <> '', '#'||r.frag, '') AS url, r.fsrc as fsrc
    FROM IntLinkRefs r LEFT JOIN LinkDefs d USING(url,frag)
    WHERE d.url IS NULL;
    CREATE VIEW IF NOT EXISTS ExtHttpLinks AS
    SELECT DISTINCT url FROM ExtLinkRefs WHERE url LIKE 'http%'
    AND url NOT LIKE 'https://www.sqlite.org/src/%'
    AND url NOT LIKE 'https://sqlite.org/src/%'
    AND url NOT LIKE 'http://www.sqlite.org/src/%'
    AND url NOT LIKE 'http://sqlite.org/src/%'
    AND url NOT LIKE 'https://www.fossil-scm.org/fossil/artifact/%'
    AND url NOT LIKE 'https://sqlite.org/forum/forumpost/%'
    ;
  }
} on error sle {
  puts stderr "Error with DB: $sle"
  exit 1
}

proc add_ref {u f s} {
  try {
    set u [iunorm $u $s]
    db eval {
      INSERT INTO IntLinkRefs(url, frag, fsrc)
      VALUES($u, $f, $s)
    }
  } on error db_conflict {
  }
}

proc add_ext {u f s} {
  try {
    db eval {
      INSERT INTO ExtLinkRefs(url, frag, fsrc)
      VALUES($u, $f, $s)
    }
  } on error db_conflict {
  }
}

proc add_def {u f s} {
  try {
    set u [iunorm $u $s]
    db eval {
      INSERT INTO LinkDefs(url, frag)
      VALUES($u, $f)
    }
  } on error db_conflict {
  }
}

if {[info command parsehtml] ne "parsehtml"} {
  try {
    load [file join $owd search parsehtml.so]
  } on error erc {
    puts stderr "Error: Could not load parsehtml DLL ($erc)"
    exit 1
  }
}

db eval {BEGIN TRANSACTION}
puts -nonewline "\
Scanning [llength $inhtml] files for hyperlink defs and refs, working on #"
set nscanning 0
set nsay ""

set ext_url_re {^((?:https?://)|(?:ftp://)|(?:mailto:))([^#]+)(#.*)?}

foreach html_src $inhtml {
  set html_dir [file dirname $html_src]
  try {
    set rpfid [open $html_src r]
    set nbu [string length $nsay]
    set nsay [format "%d" [incr nscanning]]
    puts -nonewline "[string repeat \b $nbu]$nsay"
    flush stdout
  } on error erc {
    puts stderr "Error: $erc"
    exit 1
  }
  set doc [hdom parse [read $rpfid]]
  close $rpfid

  set src_basename [file tail $html_src]
  add_def $src_basename "" $html_src

  set rn [$doc root]

  $rn foreach_descendent dnode {
    set tag [$dnode tag]
    regsub {^h[1-9]$} $tag h? tag
    switch $tag {
      a {
        foreach {an av} [$dnode attr] {
          if {$an eq "name"} {
            add_def $src_basename $av $html_src
            continue
          } elseif {$an ne "href"} continue
          if {[regexp $ext_url_re $av _ transport loc at]} {
            add_ext "${transport}${loc}" $at $html_src
          } else {
            if {[regexp {^javascript:} $av]} continue
            if {![regexp {^([^#]*)#(.*)$} $av _ av at]} {
              set at ""
            } elseif {$av eq ""} {
              set av $html_src
            }
            add_ref $av $at $html_src
          }
        }
      }
      h? {
        foreach {an av} [$dnode attr] {
          if {$an eq "id"} {
            add_def $src_basename $av $html_src
            break
          }
        }
      }
    }
  }
  $doc destroy
}
db eval {COMMIT TRANSACTION}

cd $owd
puts "\nWriting $hlink_db"
file delete -force $hlink_db
db eval { VACUUM INTO $hlink_db }

db close
if {$::iun ne ""} { close $::iun }

	@echo 'make all;        # Do all of the above'
	@echo 'make spell;      # Spell check generated docs'
	@echo 'make fast;       # Build documentation only - no requirements'
	@echo 'make faster;     # Like fast, except build is incremental'
	@echo 'make schema;     # Run once to initialize the build process'
	@echo 'make private;    # Everything except searchdb'
	@echo 'make versions;   # Update SQLite release version list'
	@echo 'make linkcheck;  # Create hyperlink check DB, hlcheck.db'

all:	base evidence format_evidence matrix doc searchdb

private:	base evidence private_evidence matrix doc

fast:	base doc

sqlite3.h: $(BLD)/sqlite3.h
	cp $(BLD)/sqlite3.h orig-sqlite3.h
	sed 's/^SQLITE_API //' orig-sqlite3.h >sqlite3.h

# Generate the directory into which generated documentation files will
# be written.
#
docdir:
	mkdir -p doc doc/c3ref doc/matrix doc/matrix/c3ref doc/matrix/syntax

ifdef CHECKOUT_ONLY
CPIO_PASS_OPTS = --pass-through --make-directories --quiet --unconditional
PAGES_IN = $$(fossil ls $(DOC)/pages | sed -e '/\.in$$/! d ; s/^/.\//')
COPY_RAW_PAGES = cp --target-directory=doc $(fossil ls rawpages)
COPY_IMAGES = fossil ls images | cpio $(CPIO_PASS_OPTS) doc
else
PAGES_IN = $(DOC)/pages/*.in
COPY_RAW_PAGES = cp $(DOC)/rawpages/* doc
COPY_IMAGES = cp -r $(DOC)/images doc
endif

# This rule generates all documention files from their sources.  The
# special markup on HTML files used to identify testable statements and
# requirements are retained in the HTML and so the HTML generated by
# this rule is not suitable for publication.  This is the first step
# only.
#
base:	$(TCLSH) sqlite3.h docdir schema always
	rm -rf doc/images
	$(COPY_IMAGES)
	$(COPY_RAW_PAGES)
	./$(TCLSH) $(DOC)/wrap.tcl $(DOC) $(SRC) doc $(PAGES_IN)
	cp doc/fileformat2.html doc/fileformat.html

# Strip the special markup in HTML files that identifies testable statements
# and requirements. (Not needed for releaselog/ or syntax/ .html files.)
#
DECARETSX = 'doc/c3ref/*.html' 'doc/session/*.html'
doc:	base $(DOC)/remove_carets.tcl
	./$(TCLSH) $(DOC)/remove_carets.tcl 'doc/*.html' $(DECARETSX)

# These rules generate documention files from their outdated sources,
# similarly to the base and doc targets combined. The build is faster
# because it does not copy, revisit or transform unchanged inputs.
# Target faster is unproven as suitable for publication, so should
# be used only for expedient checking of results during development.
$(DOC)/version_dates.txt : $(DOC)/pages/chronology.tcl
	egrep -e '^[[:xdigit:]]{10}\|[0-9]{4}(-[0-9][0-9]){2}\|Version' $< >$@

GENS = $(DOC)/pages_generated
faster:	$(TCLSH) sqlite3.h docdir schema
	$(COPY_IMAGES)
	$(COPY_RAW_PAGES)
	./$(TCLSH) $(DOC)/wrap.tcl -update $(GENS) $(DOC) $(SRC) doc $(PAGES_IN)

	cp --update doc/fileformat2.html doc/fileformat.html
	./$(TCLSH) $(DOC)/remove_carets.tcl -at $(GENS) $(DECARETSX)

# Possibly bring versions info list in ./pages/chronology.tcl up to date.
# This will get that done if ../sqlite is a SQLite library check-out and
# its release tags continue to have the format established circa 2009.
# It will either fail and say why, indicate nothing done, or revise the
# list and issue a reminder to commit the ./pages/chronology.tcl change.
versions:

#
matrix:
	rm -rf doc/matrix/images
	cp -r doc/images doc/matrix
	cp $(DOC)/rawpages/sqlite.css doc/matrix
	./$(TCLSH) $(DOC)/matrix.tcl

URLCHK = ./urlcheck --ok-silent
DOCHTML = $$(find doc -name '*.html' -print)
NBPL = $$(echo 'SELECT count(*) FROM BrokenPageLinks go' | sqlite3 hlcheck.db)

linkcheck: urlcheck
	./$(TCLSH) hlinkchk.tcl doc hlcheck.db $(DOCHTML)
	echo There are $(NBPL) broken internal page hyperlinks.
	echo "Checking external URLs is expensive. To do so, enter this:"
	echo "echo 'SELECT * FROM ExtHttpLinks;'|sqlite3 hlcheck.db|$(URLCHK)"

#-------------------------------------------------------------------------

# Source files for the [tclsqlite3.search] executable.
#
SSRC = $(DOC)/search/searchc.c \
	    $(DOC)/search/parsehtml.c \

	mkdir -p doc/search.d/
	./$(TCLSH) $(DOC)/search/mkscript.tcl $(DOC)/search/admin.tcl.in >$@
	chmod 744 doc/search.d/admin

searchdb: $(TCLSH) doc/search doc/search.d/admin
	./$(TCLSH) $(DOC)/search/buildsearchdb.tcl

urlcheck: $(DOC)/urlcheck.c
	$(CC) -Os urlcheck.c -o $@ -lcurl

#	cp $(DOC)/search/search.tcl doc/search.d/admin
#	chmod +x doc/search.d/admin

fts5ext.so:	$(DOC)/search/fts5ext.c
	gcc -shared -fPIC -I. -DSQLITE_EXT \
		$(DOC)/search/fts5ext.c -o fts5ext.so

# Build the "docsapp" application by adding an appropriate SQLAR
# repository onto the end of the "sqltclsh" application.
#
docsapp: faster $(DOC)/docapp/main.tcl $(DOC)/docapp/wapp.tcl
	cp $(DOC)/docapp/main.tcl $(DOC)/docapp/wapp.tcl .
#	rm -f $@ docs.sqlar
#	echo .q | $(BLD)/sqlite3 -batch -cmd '.read docapp/build_sqlar'
#	./$(TCLSH) $(DOC)/docapp/append_db.tcl $(BLD)/sqltclsh docs.sqlar $@
	cp $(BLD)/sqltclsh $@
	echo .q | $(BLD)/sqlite3 -append -batch -cmd '.read docapp/build.sql' $@
	chmod +x $@

always:

# Remove intermediate build products.
clean:
	-rm -f chronology.json doc_vardump.txt orig-sqlite3.h
	-rm -f docinfo.db history.db main.tcl sqlite3.h wapp.tcl $(GENS)

# Remove end-products of build too.
cleaner:  clean
	-rm -rf doc
	-rm -f $(TCLSH) docsapp docs.sqlar

<title>Books About SQLite</title>
<tcl>hd_keywords {books about SQLite}</tcl>

<h1 align=center>Books About SQLite</h1>

<hr>
<table border=0><tr><td valign=top><img src="images/books/sanderson2018.jpg" style="border:44px solid white;">
<td valign=top>
<h2>SQLite Forensics (2018)</h2>

<p>
Author: Paul Sanderson<br>
Publisher: <a href="https://www.amazon.com/product/dp/1980293074">Amazon</a>

<p>
This text by noted digital forensics expert, Paul Sanderson, provides
investigators with low-level technical details useful in analysing
SQLite database files.

<p>
Every computer and phone uses hundreds of SQLite databases and there are
over one trillion SQLite databases in active use. Hence, the importance
of examining the data held in these databases in an
investigation, including deleted data when possible, is paramount. This
book fully explains the format of the SQLite database file. It shows how
records are encoded, how to decode them manually and how to decode
records that are partially overwritten. It also describe how the
workings of SQLite, and in particular the journal and WAL, can be used
to ascertain what has happened in a manner that cannot be determined
from the data alone. The book covers basic SQL queries and how they can
be used to create a custom report that includes data from different
tables, and shows how one can use SQL queries to test hypotheses about
the relationships of data in different tables.
<p>
This book is aimed mainly at
forensic practitioners, and it is assumed that the reader has some basic
knowledge of computer forensics; it will also be of interest to computer
professionals in general particularly those who have an interest in the
SQLite file format.
</table>

Author: Gene Da Rocha<br>
Publisher: <a href="https://www.packtpub.com/application-development/learning-sqlite-ios">Packt Publishing</a>

<p>
This book starts with the architecture of SQLite database and introduces
you to concepts in SQL. You will find yourself equipped to design your
own database system, administer it, and maintain it. Further, you will
learn how to operate your SQLite databases smoothly using SQL commands.

<p>
You will be able to extend the functionality of SQLite by using its vast
arsenal of C API calls to build some interesting, exciting, new, and
intelligent data-driven applications. Understand how Xcode, HTML5, and
Phonegap can be used to build a cross-platform modern app which can
benefit from all these technologies - all through creating a complete,

<h2>SQLite Database System Design and Implementation (2015)</h2>

<p>
Author: Sibsankar Haldar<br>
Publisher: <a href="https://books.google.com/books?id=OEJ1CQAAQBAJ">https://books.google.com/</a><br>

<p>
This book provides a comprehensive description of SQLite database system.
It describes design principles, engineering trade-offs, implementation issues,
and operations of SQLite.
</table>

<hr>
<table border=0><tr><td valign=top><img src="images/books/aditya.jpg">
<td valign=top>
<h2>Android SQLite Essentials (2014)</h2>

<table border=0><tr><td valign=top><img src="images/books/das.jpg">
<td valign=top>
<h2>SQLite for Mobile Apps Simplified (2014)</h2>

<p>
Author: Sribatsa Das<br>
Publisher: Amazon<br>
<a href="https://www.amazon.com/dp/product/B00M3OVSRK">Amazon</a>

<p>
SQLite for Mobile Apps Simplified is devoted to presenting approach
and implementation methodology for using SQLite database in mobile apps.
It presents step-by-step examples to create schema, execute transactions
and access data from Android, BlackBerry and iOS applications.
In addition, it presents ADB Shell and SQLite command-line shell from
ADB Shell to access the SQLite Database created by the Android apps.
For BlackBerry and iOS application, the book presents ways to access
the data using the command line shell.
</table>

<hr>
<table border=0><tr><td valign=top><img src="images/books/owens.jpg">
<td valign=top>
<h2>The Definitive Guide to SQLite (2nd edition, 2010)</h2>

<p>
Authors: Mike Owens and Grant Allen<br>
Publisher: Apress<br>
<a href="http://www.amazon.com/gp/product/1430232250">Amazon</a></p>

<p>
Outside of the world of enterprise computing, there is one database
that enables a huge range of software and hardware to flex relational
database capabilities, without the baggage and cost of traditional
database management systems. That database is SQLite - an embeddable
database with an amazingly small footprint, yet able to handle databases
of enormous size. SQLite comes equipped with an array of powerful
features available through a host of programming and development
environments. It is supported by languages such as C, Java, Perl,
PHP, Python, Ruby, TCL, and more.</p>

<p><i>The Definitive Guide to SQLite, Second Edition</i>
is devoted to complete coverage of the latest version of this powerful
database. It offers a thorough overview of SQLite's capabilities
and APIs. The book also uses SQLite as the basis for helping newcomers
make their first foray into database development. In only a short time
you can be writing programs as diverse as a server-side browser plug-in
or the next great iPhone or Android application!
</p>
</table>

<hr>
<table border=0><tr><td valign=top><p><img src="images/books/kreibich.gif">
<td valign=top>
<h2>Using SQLite (2010)</h2>

<p>
Author: Jay A. Kreibich<br>
Publisher: O'Reilly Media<br>
<a href="http://oreilly.com/catalog/9780596521196/">O'Reilly</a></p>

<p>Developers, take note: databases aren't just for the IS group any more.
You can build database-backed applications for the desktop, Web,
embedded systems, or operating systems without linking to heavy-duty
client-server databases such as Oracle and MySQL.
This book shows how you to use SQLite, a small and lightweight
database that you can build right into your application during development.
Applications that handle data have an enormous advantage today, and
with SQLite, you'll discover how to develop a database-backed application
that remains manageable in size and complexity. This book guides
you every step of the way. You'll get a crash course in data modeling,
become familiar with SQLite's dialect of the SQL database language,
and learn how you to work with SQLite using either a scripting
language or a C-based language, such as C# or Objective C.Now,
even relatively small and nimble applications can be a part of
the data revolution. Using SQLite shows you how.
</p>
</table>

<hr>
<table border=0><tr><td valign=top><p><img src="images/books/droessler.jpg">
<td valign=top>
<h2>SQLite 3 - Einstieg in die Datenbankwelt (2010)</h2>

<p>
Author: Kay Droessler<br>
Publisher: Lulu.com<br>
<a href="https://www.amazon.com/product/dp/1445741075">Amazon</a></p>

<p>Die Datenbanksprache SQL ( Structured Query Language ) wird in Datenbanken
zur Definition, Manipulation, Sicherung, aber hauptsaechlich zur Abfrage
von Daten aus der Datenbank eingesetzt. Unabhaengig vom Betriebssystem oder
aufwendigen, benutzerfreundlichen, graphischen Oberflaechen bleibt die
Logik aber immer gleich.SQLite ist eine freie Desktop-Datenbank,
sie kostet nichts, ist fuer viele Betriebssysteme verfuegbar,
schnell heruntergeladen und installiert und auf das Notwendigste
reduziert. Fuer den Einsteiger sind das die besten Voraussetzungen,
ohne viel Aufwand schnell in die Welt der Datenbanken und Datenbanksprache
reinzuschnuppern.Wer nach den Uebungen aber auf den Geschmack gekommen ist,
hat schon den groessten Teil an Datenbanken und SQL gelernt, denn alles
Besprochene ist Wissen, welches auch auf jedes andere der vielen
Datenbanken grundlegend anwendbar ist. Nun koennen Sie auf die richtig
Grossen zugehen, vom grossen Fachbuch bis zum riesigen Datenbanksystem.
</p>
</table>

<hr>
<table border=0>
<tr><td valign=top><img src="images/books/symbiansql.jpg"><td valign=top>
<h2>Inside Symbian SQL (2010)</h2>

<p>Authors: Ivan Litovski &amp; Richard Maynard<br>
Publisher: Wiley<br>
<a href="http://www.wiley.com/WileyCDA/WileyTitle/productCd-0470744022.html">wiley.com</a></p>

<p>
This is the definitive reference book on the Symbian SQL database
which is based on SQLite. The authors (both members of the Symbian
engineering team responsible for the implementation of the code)
show you how to design code
and ease migration from an internal and external point of view,
plus they reveal the dos and don'ts of writing high-performance
database applications. Packed with resources and sample code,
this timely book reveals how to design and tune applications
that use the Symbian SQL framework to ultimately improve performance.</p>

<p>With its sample code and insider expertise, this text has everything
you need to keep you ahead of the curve.
</p>
</table>

<hr>
<table border=0>
<tr><td valign=top><img src="images/books/vanderLans.jpg"><td valign=top>
<h2>The SQL Guide to SQLite (2009)</h2>

<p>Author: Rick F. van der Lans<br>
Publisher: Lulu.com<br>
<a href="https://www.amazon.com/product/dp/0557076765">Amazon</a></p>

<p>SQLite is a small, fast, embeddable, SQL-based database server.
It is easy to install, needs no management, and is open source.
This book describes SQLite in detail. With hundreds of examples, plus a
proven approach and structure, the book teaches you how to use SQLite
efficiently and effectively. It contains a complete description of the
SQL dialect as implemented in SQLite version 3.6. The book can be seen
as a tutorial and a reference book. Source code for the numerous SQL
examples and exercises included in this book can be downloaded from www.r20.nl.
</p>
</table>

<hr>
<table border=0>
<tr><td valign=top><img src="images/books/nishizawa2.jpg"><td valign=top>
<h2>An Introduction to SQLite - 2nd Edition (2009)</h2>

<p>Author: Naoki Nishizawa<br>
Publisher: Shoeisha<br>
<a href="https://www.amazon.co.jp/product/dp/479811944X">Amazon.jp</a></p>

<p>This text is written in fluent Japanese specifically for a Japanese
audience.  This is the second edition of the book - the first edition
was published in 2005.
</p>
</table>

<hr>
<table border=0><tr><td valign=top><p><img src="images/books/haldar.gif">
<td valign=top>
<h2>Inside SQLite (2007)</h2>

<p>
Author: Sibsankar Haldar<br>
Publisher: O'Reilly Media<br>
<a href="http://oreilly.com/catalog/9780596550066">O'Reilly</a></p>

<p>SQLite is a small, zero-configuration, custom-tailored, embeddable,
thread-safe, easily maintainable, transaction-oriented, SQL-based,
relational database management system. There is no separate install or
setup procedure to initialize SQLite before using it.
There is no configuration file.
SQLite is open source, and is available in the public domain
(for more information on open source, visit http://opensource.org).
You can download SQLite source code from its homepage http://www.sqlite.org,
compile it using your favorite C compiler, and start using the compiled
library. SQLite runs on Linux, Windows, Mac OS X, and a few other operating systems. It has been widely used in low-to-medium tier database applications.
This Short Cut discusses design principles, engineering trade-offs,
implementation issues, and operations of SQLite. It presents a
comprehensive description of all important components of the SQLite engine.</p>
</table>


<hr>
<table border=0><tr><td valign=top><img src="images/books/newman.jpg">
<td valign=top>
<h2>SQLite (2004)</h2>

<p>Author: Chris Newman<br>
Publisher: Sams<br>
<a href="https://www.amazon.com/product/dp/067232685X">Amazon</a></p>

<p>
SQLite is a small, fast, embeddable database. What makes it popular is
the combination of the database engine and interface into a single library
as well as the ability to store all the data in a single file.
Its functionality lies between MySQL and PostgreSQL, however it is faster
than both databases.</p>

<p>In <i>SQLite</i>,
author Chris Newman provides a thorough, practical guide to
using, administering and programming this up-and-coming database.
If you want to learn about SQLite or about its use in conjunction with
PHP this is the book for you.</p>
</table>

  return $kw
}

# Do a page explaining what "experimental" means.
hd_open_aux c3ref/experimental.html
hd_header {Experimental Interfaces}
hd_enable_main 0
hd_puts {<a href="../c3ref/intro.html"><h2>SQLite C Interface</h2></a>}
hd_enable_main 1
hd_keywords experimental deprecated
</tcl>

<h2>Experimental And Deprecated Interfaces</h2>

<p>SQLite interfaces can be subdivided into three categories:</p>

      toc_insert $kw object $s $title $uri
    }
  }
}
hd_open_aux c3ref/objlist.html
hd_header {List Of SQLite Objects}
hd_enable_main 0
hd_putsnl {<a href="../c3ref/intro.html"><h2>SQLite C Interface</h2></a>}
hd_enable_main 1
</tcl>
<h2>List Of Objects:</h2>
<tcl>
hd_list_of_links {} 280 [lsort -nocase $objlist]
hd_enable_main 0
hd_putsnl {<p>Other lists:

      toc_insert $kw constant $s $title $uri
    }
  }
}
hd_open_aux c3ref/constlist.html
hd_header {List Of SQLite Constants}
hd_enable_main 0
hd_putsnl {<a href="../c3ref/intro.html"><h2>SQLite C Interface</h2></a>}
hd_enable_main 1
</tcl>
<h2>List Of Constants:</h2>
<p>Also available: [error codes|list of error codes]</p>
<tcl>
set clist [lsort -index 1 $clist]
#puts clist=[list $clist]

#puts "content=[list $content]"
#puts "supported=[list [array get supported]]"
#puts "funccnts=[list [array get funccnts]]
hd_open_aux c3ref/funclist.html
hd_header {List Of SQLite Functions}
hd_keywords *capi3ref_funclist {C-API function list}
hd_enable_main 0
hd_putsnl {<a href="../c3ref/intro.html"><h2>SQLite C Interface</h2></a>}
hd_enable_main 1
</tcl>
<h2>List Of Functions:</h2>
<p>Note: Functions marked with "<small><i>(exp)</i></small>"
are [experimental] and functions whose names are
<s>struck through</s> are [deprecated].</p>
<tcl>

  if {$kw==""} {error "no keyword for $c"}
  hd_fragment $kw
  hd_open_aux c3ref/[convert_keyword_to_filename $kw]
  hd_header $title
  hd_enable_main 0
  hd_putsnl "<!-- keywords: $keywords -->"
  hd_putsnl {<div class=nosearch>}
  hd_putsnl {<a href="../c3ref/intro.html"><h2>SQLite C Interface</h2></a>}
  hd_enable_main 1
  eval hd_keywords $keywords

  hd_putsnl "<h2>$title</h2>"
  hd_putsnl {</div>}
  hd_putsnl "<blockquote><pre>"
  hd_putsnl [string trim $code]

    <li> Enhance the --safe command-line option to disallow dangerous SQL functions.
    </ol>
<li>Miscellaneous performance enhancements.
<p><b>Hashes:</b>
<li>SQLITE_SOURCE_ID: <i>pending</i>
<li>SHA3-256 for sqlite3.c: <i>pending</i>
}

chng {2022-12-28 (3.40.1)} {
<li> Fix the [safe command-line option|--safe command-line option] to the [CLI]
     such that it correctly disallows the
     use of SQL functions like writefile() that can cause harmful side-effects.
<li> Fix a potential infinite loop in the [memsys5] alternative memory allocator.  This
     bug was introduced by a performance optimization in version 3.39.0.
<li> Various other obscure fixes.
<p><b>Hashes:</b>
<li>SQLITE_SOURCE_ID: 2022-12-28 14:03:47 df5c253c0b3dd24916e4ec7cf77d3db5294cc9fd45ae7b9c5e82ad8197f38a24
<li>SHA3-256 for sqlite3.c: 4d6800e9032ff349376fe612e422b49ba5eb4e378fac0b3e405235d09dd366ab
} {patchagainst 3.40.0}

chng {2022-11-16 (3.40.0)} {
<li> Add support for compiling [https://sqlite.org/wasm|SQLite to WASM]
     and running it in web browsers.  NB:  The WASM build and its interfaces
     are considered "beta" and are subject to minor changes if the need
     arises.  We anticipate finalizing the interface for the next release.
<li> Add the [recovery extension] that might be able to recover some content

<li> Fix the [.mode|"box" output mode] in the [CLI] so that it works with statements that
     returns one or more rows of zero columns (such as [PRAGMA incremental_vacuum]).
     [https://sqlite.org/forum/forumpost/afbbcb5b72|Forum post afbbcb5b72].
<li> Improvements to error messages generated by faulty common table expressions.
     [https://sqlite.org/forum/forumpost/aa5a0431c99e631|Forum post aa5a0431c99e].
<li> Fix some incorrect assert() statements.
<li> Fix to the [select-stmt|SELECT statement syntax diagram] so that the FROM clause
     syntax is shown correctly.
     [https://sqlite.org/forum/forumpost/9ed02582fe|Forum post 9ed02582fe].
<li> Fix the EBCDIC character classifier so that it understands newlines as whitespace.
     [https://sqlite.org/forum/forumpost/58540ce22dcd5fdcd|Forum post 58540ce22dcd].
<li> Improvements the [xBestIndex] method in the implementation of the
     (unsupported) [https://sqlite.org/src/file/ext/misc/wholenumber.c|wholenumber virtual table]
     extension so that it does a better job of convincing the query planner to
     avoid trying to materialize a table with an infinite number of rows.
     [https://sqlite.org/forum/forumpost/b52a020ce4|Forum post b52a020ce4].
<p><b>Hashes:</b>
<li>SQLITE_SOURCE_ID: 2021-03-26 12:12:52 4c5e6c200adc8afe0814936c67a971efc516d1bd739cb620235592f18f40be2a
<li>SHA3-256 for sqlite3.c: 91ca6c0a30ebfdba4420bb35f4fd9149d13e45fc853d86ad7527db363e282683
} {patchagainst 3.35.0 patchagainst 3.35.1 patchagainst 3.35.2}

chng {2021-03-17 (3.35.2)} {
<li> Fix a problem in the
     [https://www.sqlite.org/src/file/ext/misc/appendvfs.c | appendvfs.c]
     extension that was introduced into version 3.35.0.
<li> Ensure that date/time functions with no arguments (which generate
     responses that depend on the current time) are treated as
     [non-deterministic functions]. Ticket
     [https://sqlite.org/src/info/2c6c8689fb5f3d2f | 2c6c8689fb5f3d2f]
<li> Fix a problem in the [sqldiff] utility program having to do with
     unusual whitespace characters in a [virtual table] definition.

       are enabled using
       the [sqlite3_db_config]([SQLITE_DBCONFIG_ENABLE_FTS3_TOKENIZER])
       setting, or unless the first argument to fts3_tokenizer() is a [bound parameter].
  <li> The two-argument version of [fts3_tokenizer()] accepts a pointer to the
       tokenizer method object even without
       the [sqlite3_db_config]([SQLITE_DBCONFIG_ENABLE_FTS3_TOKENIZER]) setting
       if the second argument is a [bound parameter]
</ol>
<li> Improved robustness against corrupt database files.
<li> Miscellaneous performance enhancements
<li> Established a Git mirror of the offical SQLite source tree.
     The canonical sources for SQLite are maintained using the
     [https://fossil-scm.org/|Fossil DVCS] at [https://sqlite.org/src].
     The Git mirror can be seen at [https://github.com/sqlite/sqlite].
<p><b>Hashes:</b>

    that allows the IS operator
    to drive an index on a LEFT OUTER JOIN.  No other changes from the
    [version 3.9.2] baseline.
} {patchagainst 3.9.0 patchagainst 3.9.1 patchagainst 3.9.2}

chng {2016-03-29 (3.12.0)} {
<p><b>Potentially Disruptive Change:</b>
<li>The [SQLITE_DEFAULT_PAGE_SIZE] is increased from 1024 to 4096.
    The [SQLITE_DEFAULT_CACHE_SIZE] is changed from 2000 to -2000 so
    the same amount of cache memory is used by default.
    See the application note on the
    [version 3.12.0 page size change] for further information.
<p><b>Performance enhancements:</b>
<li>Enhancements to the [Lemon parser generator]
    so that it creates a smaller and faster SQL parser.

    Ticket [http://www.sqlite.org/src/info/2ea3e9fe63 | 2ea3e9fe63]
<li>Crash when calling undocumented SQL function sqlite_rename_parent()
    with NULL parameters.
    Ticket [http://www.sqlite.org/src/info/264b970c4379fd | 264b970c43]
<li>ORDER BY ignored if the query has an identical GROUP BY.
    Ticket [http://www.sqlite.org/src/info/b75a9ca6b0499 |  b75a9ca6b0]
<li>The group_concat(x,'') SQL function returns NULL instead of an empty string
    when all inputs are empty strings.
    Ticket [http://www.sqlite.org/src/info/55746f9e65f85 |  55746f9e65]
<li>Fix a bug in the VDBE code generator that caused crashes when
    doing an INSERT INTO ... SELECT statement where the number of columns
    being inserted is larger than the number of columns in the destination
    table.
    Ticket [http://www.sqlite.org/src/info/e9654505cfda9 | e9654505cfd]
<li>Fix a problem in CSV import in the [command-line shell]

<li>SQLITE_SOURCE_ID:
    "2012-12-12 13:36:53 cd0b37c52658bfdf992b1e3dc467bae1835a94ae"
<li>SHA1 for sqlite3.c: 2b413611f5e3e3b6ef5f618f2a9209cdf25cbcff"
}

chng {2012-10-04 (3.7.14.1)} {
<li>Fix a bug (ticket
<a href="http://www.sqlite.org/src/tktview/d02e1406a58ea02d">&#91;d02e1406a58ea02d]&#93;</a>)
that causes a segfault on a LEFT JOIN that includes an OR in the ON clause.
<li>Work around a bug in the optimizer in the VisualStudio-2012 compiler that
causes invalid code to be generated when compiling SQLite on ARM.
<li>Fix the TCL interface so that the "nullvalue" setting is honored for
TCL implementations of SQL functions.
<li>SQLITE_SOURCE_ID:
    "2012-10-04 19:37:12 091570e46d04e84b67228e0bdbcd6e1fb60c6bdb"

    [sqlite3_strnicmp()].
<li>Added the [sqlite3_db_readonly()] interface.
<li>Added the [SQLITE_FCNTL_PRAGMA] file control, giving [VFS] implementations
    the ability to add new [PRAGMA] statements or to override built-in
    PRAGMAs.
<li>Queries of the form:  "SELECT max(x), y FROM table" returns the
    value of y on the same row that contains the maximum x value.
<li>Added support for the [FTS4 languageid option].
<li>Documented support for the [FTS4 content option].  This feature has
    actually been in the code since [version 3.7.9] but is only now considered
    to be officially supported.
<li>Pending statements no longer block [ROLLBACK].  Instead, the pending
    statement will return SQLITE_ABORT upon next access after the ROLLBACK.
<li>Improvements to the handling of CSV inputs in the [command-line shell]
<li>Fix a [http://www.sqlite.org/src/info/b7c8682cc1|bug] introduced

<li> Added the [sqlite3_stmt_readonly()] interface.
<li> Added [PRAGMA checkpoint_fullfsync].
<li> Added the [SQLITE_FCNTL_FILE_POINTER] option
     to [sqlite3_file_control()].
<li> Added support for [FTS4] and enhancements
     to the FTS [matchinfo()] function.
<li> Added the test_superlock.c module which provides example
     code for obtaining an exclusive lock to a rollback or WAL database.

<li> Added the test_multiplex.c module which provides
     an example VFS that provides multiplexing (sharding)
     of a DB, splitting it over multiple files of fixed size.
<li> A [http://www.sqlite.org/src/info/80ba201079 | very obscure bug]
     associated with the [or optimization] was fixed.
}

<li>Versioning of the SQLite source code has transitioned from CVS to
    [http://www.fossil-scm.org/ | Fossil].
<li>Query planner enhancements.
<li>The [SQLITE_ENABLE_STAT2] compile-time option causes the [ANALYZE]
    command to collect a small histogram of each index, to help SQLite better
    select among competing range query indices.
<li>Recursive triggers can be enabled using the [PRAGMA recursive_triggers]
    statement.
<li>Delete triggers fire when rows are removed due to a
    [ON CONFLICT | REPLACE conflict resolution].  This feature is only
    enabled when recursive triggers are enabled.
<li>Added the [SQLITE_OPEN_SHAREDCACHE] and [SQLITE_OPEN_PRIVATECACHE]
    flags for [sqlite3_open_v2()] used to override the global
    [shared cache mode] settings for individual database connections.
<li>Added improved version identification features:

    the source table.
<li>Resolve race conditions when checking for a hot rollback journal.
<li>The [sqlite3_shutdown()] interface frees all mutexes under windows.
<li>Enhanced robustness against corrupt database files
<li>Continuing improvements to the test suite and fixes to obscure
    bugs and inconsistencies that the test suite improvements are
    uncovering.

}

chng {2009-05-25 (3.6.14.2)} {
<li>Fix a code generator bug introduced in [version 3.6.14].  This bug
    can cause incorrect query results under obscure circumstances.
    Ticket #3879.
}

    <a href="34to35.html">34to35.html</a> for details.
    <font color="red">*** Potentially incompatible change ***</font>
<li>The [sqlite3_release_memory()], [sqlite3_soft_heap_limit()],
    and [sqlite3_enable_shared_cache()] interfaces now work cross all
    threads in the process, not just the single thread in which they
    are invoked.
    <font color="red">*** Potentially incompatible change ***</font>
<li>Added the [sqlite3_open_v2()] interface.
<li>Reimplemented the memory allocation subsystem and made it
    replaceable at compile-time.
<li>Created a new mutex subsystem and made it replicable at
    compile-time.
<li>The same database connection may now be used simultaneously by
    separate threads.
}

<li>Make sure the TCL language interface works correctly with 64-bit
    integers on 64-bit machines.</li>
<li>Allow the value -9223372036854775808 as an integer literal in SQL
    statements.</li>
<li>Add the capability of "hidden" columns in virtual tables.</li>
<li>Use the macro SQLITE_PRIVATE (defaulting to "static") on all
    internal functions in the amalgamation.</li>
<li>Add pluggable tokenizers and [https://icu.unicode.org | ICU]
    tokenization support to FTS2</li>
<li>Other minor bug fixes and documentation enhancements</li>
}

chng {2007-06-18 (3.4.0)} {
<li>Fix a bug that can lead to database corruption if an [SQLITE_BUSY] error
    occurs in the middle of an explicit transaction and that transaction

    and the <a href="lang_expr.html#zeroblob">zeroblob()</a> SQL function.</li>
<li>Added support for <a href="pragma.html#pragma_incremental_vacuum">
    Incremental Vacuum</a>.</li>
<li>Added the SQLITE_MIXED_ENDIAN_64BIT_FLOAT compile-time option to support
    ARM7 processors with goofy endianness.</li>
<li>Removed all instances of sprintf() and strcpy() from the core library.</li>
<li>Added support for
    [https://icu.unicode.org | International Components for Unicode (ICU)]
    to the full-text search extensions.
<li>In the Windows OS driver, reacquire a SHARED lock if an attempt to
    acquire an EXCLUSIVE lock fails.  Ticket #2354</li>
<li>Fix the REPLACE() function so that it returns NULL if the second argument
    is an empty string.  Ticket #2324.</li>
<li>Document the hazards of type conversions in
    [sqlite3_column_blob()]

chng {2006-08-12 (3.3.7)} {
<li>Added support for virtual tables (beta)</li>
<li>Added support for dynamically loaded extensions (beta)</li>
<li>The
<a href="c3ref/interrupt.html">sqlite3_interrupt()</a>
routine can be called for a different thread</li>
<li>Added the <a href="lang_expr.html#match">MATCH</a> operator.</li>
<li>The default file format is now 1.
}

chng {2006-06-06 (3.3.6)} {
<li>Plays better with virus scanners on Windows</li>
<li>Faster :memory: databases</li>
<li>Fix an obscure segfault in UTF-8 to UTF-16 conversions</li>
<li>Added driver for OS/2</li>

    all correct (as far as is known) so everything should work OK if you
    compile with -DNDEBUG=1.  When asserts are not disabled, there
    could be a fault.</li>
}

chng {2002-02-13 (2.3.1)} {
<li>Bug fix: An assertion was failing if "PRAGMA full_column_names=ON;" was
    set and you did a query that used a rowid, like this:
    "SELECT rowid, * FROM ...".</li>
}

chng {2002-02-03 (2.3.0)} {
<li>Fix a serious bug in the INSERT command which was causing data to go
    into the wrong columns if the data source was a SELECT and the INSERT
    clauses specified its columns in some order other than the default.</li>

<li>Added support for default values on columns of a table.</li>
<li>Improved test coverage.  Fixed a few obscure bugs found by the
improved tests.</li>
}

chng {2000-06-02} {
<li>All database files to be modified by an UPDATE, INSERT or DELETE are
now locked before any changes are made to any files.
This makes it safe (I think) to access
the same database simultaneously from multiple processes.</li>
<li>The code appears stable so we are now calling it "beta".</li>
}

chng {2000-06-01} {
<li>Better support for file locking so that two or more processes

CVE 2022-46908 not-in-core {
  This is a bug in the --safe command-line option of the [command-line shell]
  program that is available for accessing SQLite database files.  The bug does
  not exist in the SQLite library.  Nor is it an issue for the [CLI] as long as
  the user does not depend on the --safe option.  It is not serious.  It is
  debatable whether or not this is a security issue.
}
CVE 2022-38627 not-a-bug {
  This is not a bug in SQLite.  This is an 
  [https://en.wikipedia.org/wiki/SQL_injection|SQL injection bug] in a specific
  PHP application.  In other words, the bug is in the PHP application code, not
  in SQLite.  Even though this CVE is not about SQLite, "SQLite" is
  mentioned in the publicity about the bug and so we list it here.
}
CVE 2022-35737 3.39.2 {
  This bug is an array-bounds overflow.  The bug is only accessible when using some
  of the C-language APIs provided by SQLite.  The bug cannot be reached using SQL
  nor can it be reached by providing SQLite with a corrupt database file.
  The bug only comes up when very long string inputs (greater than 2 billion bytes
  in length) are provided as arguments to a few specific C-language interfaces,
  and even then only under special circumstances.  

<title>SQLite Download Page</title>

<tcl>hd_adunit</tcl>

<h2>SQLite Download Page</h2>
<table width="100%" cellpadding="5" cellspacing="0">

<tcl>
set LAST_MAJOR_RELEASE 3.40.0   ; #<<----  Adjust as needed for prerelease snapshot
set SNAPSHOT_BRANCH trunk       ; #<<----  Branch from which the next release will occur
hd_keywords {download page}
set nDownload 0
set BG {}
unset -nocomplain href
unset -nocomplain href_cnt
set href_cnt 0
set disable_more 0

SQLite is the [most widely deployed] database engine in the
world today.

<p>
A few of the better-known users of SQLite are shown below
in alphabetical order.
This is not a complete list.
SQLite is in the
<a href="copyright.html">public domain</a> and so most
developers use it in their projects without ever telling us.
</p>

<tcl>
set lx {}
proc famous_user {sortkey link logo verbage} {
  lappend ::lx [list $sortkey $link $logo $verbage]
}
famous_user adobe http://www.adobe.com/ adobe.gif {
  [http://www.adobe.com/ | Adobe] uses SQLite as the
  <a href="whentouse.html#appfileformat">application
  file format</a> for their
  [http://www.adobe.com/products/photoshoplightroom/ | Photoshop Lightroom]
  product.
  SQLite is also a standard part of the
  [https://airsdk.harman.com | Adobe Integrated Runtime (AIR)].
  It is reported that
  [http://www.adobe.com/products/acrobat/readstep2.html | Acrobat Reader]
  also uses SQLite.
}
famous_user airbus http://www.airbus.com/ airbus2.gif {
  [http://www.airbus.com/ | Airbus] confirms that SQLite is being
  used in the flight software for the
  [https://www.airbus.com/en/products-services/commercial-aircraft/passenger-aircraft/a350-family | A350 XWB] family
  of aircraft.
}
famous_user apple http://www.apple.com/ apple.gif {
  [http://www.apple.com/ | Apple] uses SQLite in many (most?) of the
  native applications running on Mac OS-X desktops and servers and
  on iOS devices such as iPhones and iPods.  SQLite is also used in
  [http://www.apple.com/itunes/ | iTunes], even on non-Apple hardware.

}
famous_user expensify https://www.expensify.com/ expensify.png {
  [https://www.expensify.com/ | Expensify] uses SQLite as a server-side
  database engine for their enterprise-scale expense reporting software.
}
famous_user facebook https://www.facebook.com/ fb.gif {
  [https://www.facebook.com/ | Facebook] uses SQLite as the SQL database
  engine in their
  [https://code.facebook.com/projects/658950180885092 | osquery] product.
}
famous_user mozilla http://www.mozilla.com/ firefox.gif {
  SQLite is the primary meta-data storage format for
  the [http://www.mozilla.com/ | Firefox Web Browser] and the
  [http://www.mozilla.com/thunderbird/ | Thunderbird Email Reader] from
  Mozilla.
}
famous_user ge http://www.ge.com/ ge.gif {
  We believe that [http://www.ge.com/ | General Electric]
  uses SQLite in some product or
  another because they have written to the SQLite developers at
  least four separate times requesting
  the US Export Control Number for SQLite. So presumably GE is using
  SQLite in something that they are exporting. But nobody
  (outside of GE) seems to know what that might be.
}
famous_user google http://www.google.com/ google.gif {
  uses SQLite in their
  [http://code.google.com/android/ | Android] cell-phone
  operating system, and in the
  [http://www.google.com/chrome | Chrome Web Browser].
}
famous_user intuit http://www.intuit.com/ intuit.gif {
  [http://www.intuit.com/ | Intuit] apparently uses SQLite in
  [http://www.quickbooks.com/ | QuickBooks] and in
  [http://turbotax.intuit.com/ | TurboTax] to judge from some error
  reports from users seen
  [http://community.intuit.com/posts/database-error-sqlite-error-code1 | here] and
  [https://ttlc.intuit.com/post/show_full/cJf8mIhC4r4jjracfArQzM/when-i-try-to-update-turbotax-i-receive-an-unexpected-error-message | here].
}
famous_user mcafee http://www.mcafee.com/ mcaffee.gif {
  [http://www.mcafee.com/ | McAfee] uses SQLite in its antivirus
  programs.
  Mentioned [http://www.mail-archive.com/sqlite-users@sqlite.org/msg16931.html | here] and implied
  [https://forums.mcafee.com/t5/WebAdvisor/SQLite-Temporary-Files/m-p/19512 | here].
}
famous_user microsoft http://www.microsoft.com/ microsoft.gif {
  [http://www.microsoft.com/ | Microsoft] uses SQLite as a core component
  of Windows 10, and in other products.
}
famous_user nds http://www.nds-association.org/ nds.png {
  [http://www.nds-association.org/ | The Navigation Data Standard] uses
  SQLite as its [affshort | application file format].
}
famous_user php http://www.php.net/ php.gif {
  The popular [http://www.php.net/ | PHP]
  programming language comes with both SQLite2 and SQLite3 built in.
}
famous_user python http://www.python.org/ python.gif {
  All [http://docs.python.org/lib/module-sqlite3.html | Python]
  distributions since Python 2.5 include SQLite.
}
famous_user xojo http://www.xojo.com/ xojo.png {
  SQLite comes bundled with the [http://www.xojo.com/|Xojo]
  programming environment
  (formerly [http://www.realbasic.com/ | REALbasic])
}
famous_user rpm https://en.wikipedia.org/wiki/RPM_Package_Manager rpm_logo.png {
  The [https://en.wikipedia.org/wiki/RPM_Package_Manager | RedHat Package Manager (RPM)]
  uses SQLite to track its state.
}
famous_user skype http://www.skype.com/ skype.gif {
  There are
  [http://www.mail-archive.com/sqlite-users%40sqlite.org/msg27326.html | multiple]
  [http://www.mail-archive.com/sqlite-users%40sqlite.org/msg27332.html|sightings]
  of SQLite in the Skype client for Mac OS X and Windows.
}
famous_user tcl http://www.tcl-lang.org/ tcl.gif {
  The Tcl/Tk programming language now comes with SQLite built-in.  SQLite works
  particularly well with Tcl since SQLite was originally a Tcl extension that

<tcl>hd_keywords *fts3 FTS3 {full-text search}</tcl>
<title>SQLite FTS3 and FTS4 Extensions</title>

<table_of_contents>

<h2 id=overview style="margin-left:1.0em" notoc> Overview</h2>

<p>
  FTS3 and FTS4 are SQLite virtual table modules that allows users to perform
  full-text searches on a set of documents. The most common (and effective)
  way to describe full-text searches is "what Google, Yahoo, and Bing do
  with documents placed on the World Wide Web". Users input a term, or series
  of terms, perhaps connected by a binary operator or grouped together into a
  phrase, and the full-text query system finds the set of documents that best
  matches those terms considering the operators and groupings the user has
  specified. This article describes the deployment and usage of FTS3 and FTS4.

<p>
  FTS1 and FTS2 are obsolete full-text search modules for SQLite.  There are known
  issues with these older modules and their use should be avoided.
  Portions of the original FTS3 code were contributed to the SQLite project
  by Scott Hess of <a href="http://www.google.com">Google</a>. It is now
  developed and maintained as part of SQLite.

<h1>Introduction to FTS3 and FTS4</h1>

<p>
  The FTS3 and FTS4 extension modules allows users to create special tables with a
  built-in full-text index (hereafter "FTS tables"). The full-text index
  allows the user to efficiently query the database for all rows that contain
  one or more words (hereafter "tokens"), even if the table
  contains many large documents.

<p>
  For example, if each of the 517430 documents in the
  "<a href="http://www.cs.cmu.edu/~enron/">Enron E-Mail Dataset</a>"
  is inserted into both an FTS table and an ordinary SQLite table
  created using the following SQL script:

<codeblock>
  CREATE VIRTUAL TABLE enrondata1 USING fts3(content TEXT);     /* FTS3 table */
  CREATE TABLE enrondata2(content TEXT);                        /* Ordinary table */

</codeblock>

<p>
  Of course, the two queries above are not entirely equivalent. For example
  the LIKE query matches rows that contain terms such as "linuxophobe"
  or "EnterpriseLinux" (as it happens, the Enron E-Mail Dataset does not
  actually contain any such terms), whereas the MATCH query on the FTS3 table
  selects only those rows that contain "linux" as a discrete token. Both
  searches are case-insensitive. The FTS3 table consumes around 2006 MB on
  disk compared to just 1453 MB for the ordinary table. Using the same
  hardware configuration used to perform the SELECT queries above, the FTS3
  table took just under 31 minutes to populate, versus 25 for the ordinary
  table.

<h2>Differences between FTS3 and FTS4</h2>
<tcl>hd_fragment fts4 FTS4</tcl>

<p>
  FTS3 and FTS4 are nearly identical. They share most of their code in common,
  and their interfaces are the same. The differences are:

<ul>
  <li> <p>FTS4 contains query performance optimizations that may significantly
       improve the performance of full-text queries that contain terms that are
       very common (present in a large percentage of table rows).

  <li> <p>FTS4 supports some additional options that may used with the [matchinfo()]
       function.

  <li> <p>Because it stores extra information on disk in two new
       [FTS shadow tables|shadow tables] in order to support the performance
       optimizations and extra matchinfo() options, FTS4 tables may consume more
       disk space than the equivalent table created using FTS3. Usually the overhead
       is 1-2% or less, but may be as high as 10% if the documents stored in the
       FTS table are very small. The overhead may be reduced by specifying the
       directive [FTS4 matchinfo option|"matchinfo=fts3"] as part of the FTS4 table
       declaration, but this comes at the expense of sacrificing some of the
       extra supported matchinfo() options.

  <li> <p>FTS4 provides hooks (the compress and uncompress
       [FTS4 options|options]) allowing data to be stored in a compressed
       form, reducing disk usage and IO.
</ul>

<p>
  FTS4 is an enhancement to FTS3.
  FTS3 has been available since SQLite [version 3.5.0] ([dateof:3.5.0])
  The enhancements for FTS4 were added with SQLite [version 3.7.4]
  ([dateof:3.7.4]).

<p>
  Which module, FTS3 or FTS4, should you use in your application?  FTS4 is
  sometimes significantly faster than FTS3, even orders of magnitude faster
  depending on the query, though in the common case the performance of the two
  modules is similar. FTS4 also offers the enhanced [matchinfo()] outputs which
  can be useful in ranking the results of a [FTS MATCH|MATCH] operation.  On the
  other hand, in the absence of a [FTS4 matchinfo option|matchinfo=fts3] directive FTS4 requires a little
  more disk space than FTS3, though only a percent of two in most cases.

<p>
  For newer applications, FTS4 is recommended; though if compatibility with older
  versions of SQLite is important, then FTS3 will usually serve just as well.

<h2>Creating and Destroying FTS Tables</h2>

<p>
  Like other virtual table types, new FTS tables are created using a
  [CREATE VIRTUAL TABLE] statement. The module name, which follows
  the USING keyword, is either "fts3" or "fts4". The virtual table module arguments may
  be left empty, in which case an FTS table with a single user-defined
  column named "content" is created. Alternatively, the module arguments
  may be passed a list of comma separated column names.

<p>
  If column names are explicitly provided for the FTS table as part of
  the CREATE VIRTUAL TABLE statement, then a datatype name may be optionally
  specified for each column. This is pure syntactic sugar, the
  supplied typenames are not used by FTS or the SQLite core for any
  purpose. The same applies to any constraints specified along with an
  FTS column name - they are parsed but not used or recorded by the system
  in any way.

<codeblock>

  VIRTUAL TABLE statement used to create an FTS table may be used to specify
  a [tokenizer]. This is done by specifying a string of the form
  "tokenize=<tokenizer name> <tokenizer args>" in place of a column
  name, where <tokenizer name> is the name of the tokenizer to use and
  <tokenizer args> is an optional list of whitespace separated qualifiers
  to pass to the tokenizer implementation. A tokenizer specification may be
  placed anywhere in the column list, but at most one tokenizer declaration is
  allowed for each CREATE VIRTUAL TABLE statement. [tokenizer|See below] for a
  detailed description of using (and, if necessary, implementing) a tokenizer.

<codeblock>
  <i>-- Create an FTS table named "papers" with two columns that uses</i>
  <i>-- the tokenizer "porter".</i>
  CREATE VIRTUAL TABLE papers USING fts3(author, document, tokenize=porter);

  <p>
    FTS tables are populated using [INSERT], [UPDATE] and [DELETE]
    statements in the same way as ordinary SQLite tables are.

  <p>
    As well as the columns named by the user (or the "content" column if no
    module arguments were specified as part of the [CREATE VIRTUAL TABLE]
    statement), each FTS table has a "rowid" column. The rowid of an FTS
    table behaves in the same way as the rowid column of an ordinary SQLite
    table, except that the values stored in the rowid column of an FTS table
    remain unchanged if the database is rebuilt using the [VACUUM] command.
    For FTS tables, "docid" is allowed as an alias along with the usual "rowid",
    "oid" and "_oid_" identifiers. Attempting to insert or update a row with a
    docid value that already exists in the table is an error, just as it would
    be with an ordinary SQLite table.

  <p>
    There is one other subtle difference between "docid" and the normal SQLite
    aliases for the rowid column. Normally, if an INSERT or UPDATE statement
    assigns discrete values to two or more aliases of the rowid column, SQLite
    writes the rightmost of such values specified in the INSERT or UPDATE
    statement to the database. However, assigning a non-NULL value to both
    the "docid" and one or more of the SQLite rowid aliases when inserting or
    updating an FTS table is considered an error. See below for an example.

<codeblock>
  <i>-- Create an FTS table</i>

  <i>-- the rowid and docid columns of an FTS table.</i>
  INSERT INTO pages(rowid, docid, title, body) VALUES(1, 2, 'A title', 'A document body');
</codeblock>

  <p>
    To support full-text queries, FTS maintains an inverted index that maps
    from each unique term or word that appears in the dataset to the locations
    in which it appears within the table contents. For the curious, a
    complete description of the [segment btree|data structure] used to store
    this index within the database file appears below. A feature of
    this data structure is that at any time the database may contain not
    one index b-tree, but several different b-trees that are incrementally
    merged as rows are inserted, updated and deleted. This technique improves
    performance when writing to an FTS table, but causes some overhead for
    full-text queries that use the index. Evaluating the special ["optimize" command],
    an SQL statement of the
    form "INSERT INTO &lt;fts-table&gt;(&lt;fts-table&gt;) VALUES('optimize')",
    causes FTS to merge all existing index b-trees into a single large
    b-tree containing the entire index. This can be an expensive operation,
    but may speed up future queries.

  <p>
    For example, to optimize the full-text index for an FTS table named
    "docs":

<codeblock>
  <i>-- Optimize the internal structure of FTS table "docs".</i>
  INSERT INTO docs(docs) VALUES('optimize');
</codeblock>

  <p>
    The statement above may appear syntactically incorrect to some. Refer to
    the section describing the [simple fts queries] for an explanation.

  <p>
    There is another, deprecated, method for invoking the optimize
    operation using a SELECT statement. New code should use statements
    similar to the INSERT above to optimize FTS structures.

<h2 tags="simple fts queries">Simple FTS Queries</h2>

<p>
  As for all other SQLite tables, virtual or otherwise, data is retrieved
  from FTS tables using a [SELECT] statement.

<p>
  FTS tables can be queried efficiently using SELECT statements of two
  different forms:

<ul>
  <li><p>
    <b>Query by rowid</b>. If the WHERE clause of the SELECT statement
    contains a sub-clause of the form "rowid = ?", where ? is an SQL expression,
    FTS is able to retrieve the requested row directly using the equivalent
    of an SQLite [INTEGER PRIMARY KEY] index.

  <li><p>
    <b>Full-text query</b>. If the WHERE clause of the SELECT statement contains
    a sub-clause of the form "&lt;column&gt; MATCH ?", FTS is able to use
    the built-in full-text index to restrict the search to those documents
    that match the full-text query string specified as the right-hand operand
    of the MATCH clause.
</ul>

<p>
  If neither of these two query strategies can be used, all
  queries on FTS tables are implemented using a linear scan of the entire
  table. If the table contains large amounts of data, this may be an
  impractical approach (the first example on this page shows that a linear
  scan of 1.5 GB of data takes around 30 seconds using a modern PC).

<codeblock>
  <i>-- The examples in this block assume the following FTS table:</i>
  CREATE VIRTUAL TABLE mail USING fts3(subject, body);

  SELECT * FROM mail WHERE rowid = 15;                <i>-- Fast. Rowid lookup.</i>
  SELECT * FROM mail WHERE body MATCH 'sqlite';       <i>-- Fast. Full-text query.</i>
  SELECT * FROM mail WHERE mail MATCH 'search';       <i>-- Fast. Full-text query.</i>
  SELECT * FROM mail WHERE rowid BETWEEN 15 AND 20;   <i>-- Fast. Rowid lookup.</i>
  SELECT * FROM mail WHERE subject = 'database';      <i>-- Slow. Linear scan.</i>
  SELECT * FROM mail WHERE subject MATCH 'database';  <i>-- Fast. Full-text query.</i>
</codeblock>

<p>
  In all of the full-text queries above, the right-hand operand of the MATCH
  operator is a string consisting of a single term. In this case, the MATCH
  expression evaluates to true for all documents that contain one or more
  instances of the specified word ("sqlite", "search" or "database", depending
  on which example you look at). Specifying a single term as the right-hand
  operand of the MATCH operator results in the simplest and most common type
  of full-text query possible. However more complicated queries are possible,
  including phrase searches, term-prefix searches and searches for documents
  containing combinations of terms occurring within a defined proximity of each
  other. The various ways in which the full-text index may be queried are
  [FTS MATCH|described below].

<p>
  Normally, full-text queries are case-insensitive. However, this
  is dependent on the specific [tokenizer] used by the FTS table
  being queried. Refer to the section on [tokenizer|tokenizers] for details.

<p>
  The paragraph above notes that a MATCH operator with a simple term as the
  right-hand operand evaluates to true for all documents that contain the
  specified term. In this context, the "document" may refer to either the
  data stored in a single column of a row of an FTS table, or to the contents
  of all columns in a single row, depending on the identifier used as the
  left-hand operand to the MATCH operator. If the identifier specified as
  the left-hand operand of the MATCH operator is an FTS table column name,
  then the document that the search term must be contained in is the value
  stored in the specified column. However, if the identifier is the name
  of the FTS <i>table</i> itself, then the MATCH operator evaluates to true
  for each row of the FTS table for which any column contains the search
  term. The following example demonstrates this:

<codeblock>
  <i>-- Example schema</i>
  CREATE VIRTUAL TABLE mail USING fts3(subject, body);

  <i>-- Example table population</i>
  INSERT INTO mail(docid, subject, body) VALUES(1, 'software feedback', 'found it too slow');
  INSERT INTO mail(docid, subject, body) VALUES(2, 'software feedback', 'no feedback');
  INSERT INTO mail(docid, subject, body) VALUES(3, 'slow lunch order',  'was a software problem');

  <i>-- Example queries</i>
  SELECT * FROM mail WHERE subject MATCH 'software';    <i>-- Selects rows 1 and 2</i>
  SELECT * FROM mail WHERE body    MATCH 'feedback';    <i>-- Selects row 2</i>
  SELECT * FROM mail WHERE mail    MATCH 'software';    <i>-- Selects rows 1, 2 and 3</i>
  SELECT * FROM mail WHERE mail    MATCH 'slow';        <i>-- Selects rows 1 and 3</i>
</codeblock>

<p>
  At first glance, the final two full-text queries in the example above seem
  to be syntactically incorrect, as there is a table name ("mail") used as
  an SQL expression. The reason this is acceptable is that each FTS table
  actually has a [sqlite3_declare_vtab|HIDDEN] column with the same name
  as the table itself (in this case, "mail"). The value stored in this
  column is not meaningful to the application, but can be used as the
  left-hand operand to a MATCH operator. This special column may also be
  passed as an argument to the [snippet()|FTS auxiliary functions].

<p>
  The following example illustrates the above. The expressions "docs",
  "docs.docs" and "main.docs.docs" all refer to column "docs". However, the
  expression "main.docs" does not refer to any column. It could be used to
  refer to a table, but a table name is not allowed in the context in which
  it is used below.

<codeblock>
  <i>-- Example schema</i>
  CREATE VIRTUAL TABLE docs USING fts4(content);

  <i>-- Example queries</i>
  SELECT * FROM docs WHERE docs MATCH 'sqlite';              <i>-- OK.</i>
  SELECT * FROM docs WHERE docs.docs MATCH 'sqlite';         <i>-- OK.</i>
  SELECT * FROM docs WHERE main.docs.docs MATCH 'sqlite';    <i>-- OK.</i>
  SELECT * FROM docs WHERE main.docs MATCH 'sqlite';         <i>-- Error.</i>
</codeblock>

<h2>Summary</h2>

<p>
  From the users point of view, FTS tables are similar to ordinary SQLite
  tables in many ways. Data may be added to, modified within and removed
  from FTS tables using the INSERT, UPDATE and DELETE commands just as
  it may be with ordinary tables. Similarly, the SELECT command may be used
  to query data. The following list summarizes the differences between FTS
  and ordinary tables:

<ol>
  <li><p>
    As with all virtual table types, it is not possible to create indices or
    triggers attached to FTS tables. Nor is it possible to use the ALTER TABLE
    command to add extra columns to FTS tables (although it is possible to use
    ALTER TABLE to rename an FTS table).

  <li><p>
    Data-types specified as part of the "CREATE VIRTUAL TABLE" statement
    used to create an FTS table are ignored completely. Instead of the
    normal rules for applying type [affinity] to inserted values, all
    values inserted into FTS table columns (except the special rowid
    column) are converted to type TEXT before being stored.

  <li><p>
    FTS tables permit the special alias "docid" to be used to refer to the
    rowid column supported by all [virtual tables].

  <li><p>
    The [FTS MATCH] operator is supported for queries based on the built-in
    full-text index.

  <li><p>
    The [FTS auxiliary functions], [snippet()], [offsets()], and [matchinfo()] are
    available to support full-text queries.

  <li><p>
    <tcl>hd_fragment hiddencol {FTS hidden column}</tcl>
    Every FTS table has a [hidden column] with the
    same name as the table itself. The value contained in each row for the
    hidden column is a blob that is only useful as the left operand of a
    [FTS MATCH|MATCH] operator, or as the left-most argument to one
    of the [FTS auxiliary functions].


</ol>


<h1 tags="compile fts">Compiling and Enabling FTS3 and FTS4</h1>

<p>
  Although FTS3 and FTS4 are included with the SQLite core source code, they are not
  enabled by default. To build SQLite with FTS functionality enabled, define
  the preprocessor macro [SQLITE_ENABLE_FTS3] when compiling. New applications
  should also define the [SQLITE_ENABLE_FTS3_PARENTHESIS] macro to enable the
  [enhanced query syntax] (see below). Usually, this is done by adding the
  following two switches to the compiler command line:

<codeblock>
  -DSQLITE_ENABLE_FTS3
  -DSQLITE_ENABLE_FTS3_PARENTHESIS
</codeblock>

<p>
  Because FTS3 and FTS4 are virtual tables, The [SQLITE_ENABLE_FTS3] compile-time option
  is incompatible with the [SQLITE_OMIT_VIRTUALTABLE] option.

<p>
  If a build of SQLite does not include the FTS modules, then any attempt to prepare an
  SQL statement to create an FTS3 or FTS4 table or to drop or access an existing
  FTS table in any way will fail. The error message returned will be similar
  to "no such module: ftsN" (where N is either 3 or 4).

<p>
  If the C version of the <a href="https://icu.unicode.org">ICU library</a>
  is available, then FTS may also be compiled with the SQLITE_ENABLE_ICU
  pre-processor macro defined. Compiling with this macro enables an FTS
  [tokenizer] that uses the ICU library to split a document into terms
  (words) using the conventions for a specified language and locale.

<codeblock>
  -DSQLITE_ENABLE_ICU
</codeblock>


<h1 tags="FTS MATCH">Full-text Index Queries</h1>

<p>
  The most useful thing about FTS tables is the queries that may be
  performed using the built-in full-text index. Full-text queries are
  performed by specifying a clause of the form
  "&lt;column&gt; MATCH &lt;full-text query expression&gt;" as part of the WHERE
  clause of a SELECT statement that reads data from an FTS table.
  [simple fts queries|Simple FTS queries] that return all documents that
  contain a given term are described above. In that discussion the right-hand
  operand of the MATCH operator was assumed to be a string consisting of a
  single term. This section describes the more complex query types supported
  by FTS tables, and how they may be utilized by specifying a more
  complex query expression as the right-hand operand of a MATCH operator.

<p>
  FTS tables support three basic query types:

<ul>
  <tcl>hd_fragment termprefix {prefix query} {prefix queries}</tcl>
  <li><p><b>Token or token prefix queries</b>.
    An FTS table may be queried for all documents that contain a specified
    term (the [simple fts queries|simple case] described above), or for
    all documents that contain a term with a specified prefix. As we have
    seen, the query expression for a specific term is simply the term itself.
    The query expression used to search for a term prefix is the prefix
    itself with a '*' character appended to it. For example:
</ul>

  <i>-- all documents that contain "linux", but also those that contain terms "linear",</i>
  <i>--"linker", "linguistic" and so on.</i>
  SELECT * FROM docs WHERE docs MATCH 'lin*';
</codeblock>

<ul>
  <li style="list-style:none"><p>
    Normally, a token or token prefix query is matched against the FTS table
    column specified as the left-hand side of the MATCH operator. Or, if the
    special column with the same name as the FTS table itself is specified,
    against all columns. This may be overridden by specifying a column-name
    followed by a ":" character before a basic term query. There may be space
    between the ":" and the term to query for, but not between the column-name
    and the ":" character. For example:
</ul>

<codeblock>
  <i>-- Query the database for documents for which the term "linux" appears in</i>
  <i>-- the document title, and the term "problems" appears in either the title</i>
  <i>-- or body of the document.</i>
  SELECT * FROM docs WHERE docs MATCH 'title:linux problems';

  <i>-- Query the database for documents for which the term "linux" appears in</i>

  <i>-- "linux applications", this will match common phrases such as "linoleum appliances"</i>
  <i>-- or "link apprentice".</i>
  SELECT * FROM docs WHERE docs MATCH '"lin* app*"';
</codeblock>

<tcl>hd_fragment near {NEAR queries}</tcl>
<ul>
  <li><p><b>NEAR queries</b>.
    A NEAR query is a query that returns documents that contain a two or
    more nominated terms or phrases within a specified proximity of each
    other (by default with 10 or less intervening terms). A NEAR query is
    specified by putting the keyword "NEAR" between two phrase, token or
    token prefix queries. To specify a proximity other than the default,
    an operator of the form "NEAR/<i>&lt;N&gt;</i>" may be used, where
    <i>&lt;N&gt;</i> is the maximum number of intervening terms allowed.
    For example:
</ul>

<codeblock>
  <i>-- Virtual table declaration.</i>
  CREATE VIRTUAL TABLE docs USING fts4();

  <i>-- Virtual table data.</i>
  INSERT INTO docs VALUES('SQLite is an ACID compliant embedded relational database management system');

  <i>-- Search for a document that contains the terms "sqlite" and "database" with</i>
  <i>-- not more than 10 intervening terms. This matches the only document in</i>
  <i>-- table docs (since there are only six terms between "SQLite" and "database"</i>
  <i>-- in the document)</i>.
  SELECT * FROM docs WHERE docs MATCH 'sqlite NEAR database';

  <i>-- Search for a document that contains the terms "sqlite" and "database" with</i>
  <i>-- not more than 6 intervening terms. This also matches the only document in</i>
  <i>-- table docs. Note that the order in which the terms appear in the document</i>
  <i>-- does not have to be the same as the order in which they appear in the query.</i>

  <li style="list-style: none"><p>
    More than one NEAR operator may appear in a single query. In this case each
    pair of terms or phrases separated by a NEAR operator must appear within the
    specified proximity of each other in the document. Using the same table and
    data as in the block of examples above:
</ul>

<codeblock>
  <i>-- The following query selects documents that contains an instance of the term </i>
  <i>-- "sqlite" separated by two or fewer terms from an instance of the term "acid",</i>
  <i>-- which is in turn separated by two or fewer terms from an instance of the term</i>
  <i>-- "relational".</i>
  SELECT * FROM docs WHERE docs MATCH 'sqlite NEAR/2 acid NEAR/2 relational';

  <i>-- This query matches no documents. There is an instance of the term "sqlite" with</i>
  <i>-- sufficient proximity to an instance of "acid" but it is not sufficiently close</i>
  <i>-- to an instance of the term "relational".</i>
  SELECT * FROM docs WHERE docs MATCH 'acid NEAR/2 sqlite NEAR/2 relational';
</codeblock>

<p>
  Phrase and NEAR queries may not span multiple columns within a row.

<p>
  The three basic query types described above may be used to query the full-text
  index for the set of documents that match the specified criteria. Using the
  FTS query expression language it is possible to perform various set
  operations on the results of basic queries. There are currently three
  supported operations:

<ul>
  <li> The AND operator determines the <b>intersection</b> of two sets of documents.

  <li> The OR operator calculates the <b>union</b> of two sets of documents.

  <li> The NOT operator (or, if using the standard syntax, a unary "-" operator)
       may be used to compute the <b>relative complement</b> of one set of
       documents with respect to another.
</ul>

<p>
  The FTS modules may be compiled to use one of two slightly different versions
  of the full-text query syntax, the "standard" query syntax and the "enhanced"
  query syntax. The basic term, term-prefix, phrase and NEAR queries described
  above are the same in both versions of the syntax. The way in which set
  operations are specified is slightly different. The following two sub-sections
  describe the part of the two query syntaxes that pertains to set operations.
  Refer to the description of how to [compile fts] for compilation notes.

<h2 tags="enhanced query syntax">
  Set Operations Using The Enhanced Query Syntax</h2>

<p>
  The enhanced query syntax supports the AND, OR and NOT binary set operators.
  Each of the two operands to an operator may be a basic FTS query, or the
  result of another AND, OR or NOT set operation. Operators must be entered
  using capital letters. Otherwise, they are interpreted as basic term queries
  instead of set operators.

<p>
  The AND operator may be implicitly specified. If two basic queries appear
  with no operator separating them in an FTS query string, the results are
  the same as if the two basic queries were separated by an AND operator.
  For example, the query expression "implicit operator" is a more succinct
  version of "implicit AND operator".

<codeblock>
  <i>-- Virtual table declaration</i>

  <i>-- be specified using capital letters. In practice, this query will match any documents</i>
  <i>-- that contain each of the three terms "database", "and" and "sqlite" at least once.</i>
  <i>-- No documents in the example data above match this criteria.</i>
  SELECT * FROM docs WHERE docs MATCH 'database and sqlite';
</codeblock>

<p>
  The examples above all use basic full-text term queries as both operands of
  the set operations demonstrated. Phrase and NEAR queries may also be used,
  as may the results of other set operations. When more than one set operation
  is present in an FTS query, the precedence of operators is as follows:

<table striped=1>
  <tr><th>Operator<th>Enhanced Query Syntax Precedence
  <tr><td>NOT <td> Highest precedence (tightest grouping).

  FTS query set operations using the standard query syntax are similar, but
  not identical, to set operations with the enhanced query syntax. There
  are four differences, as follows:

<ol>
  <li value=1><p> Only the implicit version of the AND operator is supported.
    Specifying the string "AND" as part of a standard query syntax query is
    interpreted as a term query for the set of documents containing the term
    "and".
</ol>

<ol>
  <li value=2><p> Parenthesis are not supported.
</ol>

<ol>
  <li value=3><p> The NOT operator is not supported. Instead of the NOT
    operator, the standard query syntax supports a unary "-" operator that
    may be applied to basic term and term-prefix queries (but not to phrase
    or NEAR queries). A term or term-prefix that has a unary "-" operator
    attached to it may not appear as an operand to an OR operator. An FTS
    query may not consist entirely of terms or term-prefix queries with unary
    "-" operators attached to them.
</ol>

<codeblock>
  <i>-- Search for the set of documents that contain the term "sqlite" but do</i>
  <i>-- not contain the term "database".</i>
  SELECT * FROM docs WHERE docs MATCH 'sqlite -database';
</codeblock>

<ol>
  <li value=4><p> The relative precedence of the set operations is different.
   In particular, using the standard query syntax the "OR" operator has a
   higher precedence than "AND". The precedence of operators when using the
   standard query syntax is:
</ol>

<table striped=1>
  <tr><th>Operator<th>Standard Query Syntax Precedence
  <tr><td>Unary "-" <td> Highest precedence (tightest grouping).
  <tr><td>OR  <td>
  <tr><td>AND <td> Lowest precedence (loosest grouping).
</table>

<ol><li style="list-style:none">
  The following example illustrates precedence of operators using the standard
  query syntax:
</ol>

<codeblock>
  <i>-- Search for documents that contain at least one of the terms "database"</i>
  <i>-- and "sqlite", and also contain the term "library". Because of the differences</i>
  <i>-- in operator precedences, this query would have a different interpretation using</i>

  a blob value zero bytes in size.

<p id=matchable>
  All three auxiliary functions extract a set of "matchable phrases" from
  the FTS query expression to work with. The set of matchable phrases for
  a given query consists of all phrases (including unquoted tokens and
  token prefixes) in the expression except those that are prefixed with
  a unary "-" operator (standard syntax) or are part of a sub-expression
  that is used as the right-hand operand of a NOT operator.

<p>
  With the following provisos, each series of tokens in the FTS table that
  matches one of the matchable phrases in the query expression is known as a
  "phrase match":

<ol>
  <li> If a matchable phrase is part of a series of phrases connected by
       NEAR operators in the FTS query expression, then each phrase match
       must be sufficiently close to other phrase matches of the relevant
       types to satisfy the NEAR condition.

  <li> If the matchable phrase in the FTS query is restricted to matching
       data in a specified FTS table column, then only phrase matches that
       occur within that column are considered.
</ol>

<tcl>hd_fragment offsets offsets</tcl>
<h2>The Offsets Function</h2>

<p>
  For a SELECT query that uses the full-text index, the offsets() function
  returns a text value containing a series of space-separated integers. For
  each term in each <a href=#matchable>phrase match</a> of the current row,
  there are four integers in the returned list. Each set of four integers is
  interpreted as follows:

<table striped=1>
  <tr><th>Integer <th>Interpretation
  <tr><td>0
      <td>The column number that the term instance occurs in (0 for the
          leftmost column of the FTS table, 1 for the next leftmost, etc.).
  <tr><td>1
      <td>The term number of the matching term within the full-text query
          expression. Terms within a query expression are numbered starting
          from 0 in the order that they occur.
  <tr><td>2

</codeblock>

<tcl>hd_fragment snippet snippet</tcl>
<h2>The Snippet Function</h2>

<p>
  The snippet function is used to create formatted fragments of document text
  for display as part of a full-text query results report. The snippet function
  may be passed between one and six arguments, as follows:

<table striped=1>
  <tr><th>Argument <th>Default Value <th>Description
  <tr><td>0 <td>N/A
      <td> The first argument to the snippet function must always be the [FTS hidden column]
           of the FTS table being queried and from which the snippet is to be taken.  The

      <td> The "ellipses" text.
  <tr><td>4 <td>-1
      <td> The FTS table column number to extract the returned fragments of
           text from. Columns are numbered from left to right starting with
           zero. A negative value indicates that the text may be extracted
           from any column.
  <tr><td>5 <td>-15
      <td> The absolute value of this integer argument is used as the
           (approximate) number of tokens to include in the returned text
           value. The maximum allowable absolute value is 64. The value of
           this argument is referred to as <i>N</i> in the discussion below.
</table>

<p>
  The snippet function first attempts to find a fragment of text consisting
  of <i>|N|</i> tokens within the current row that contains at least one phrase
  match for each matchable phrase matched somewhere in the current row,
  where <i>|N|</i> is the absolute value of the sixth argument passed to the
  snippet function. If the text stored in a single column contains less than
  <i>|N|</i> tokens, then the entire column value is considered. Text fragments
  may not span multiple columns.

<p>
  If such a text fragment can be found, it is returned with the following
  modifications:

<ul>

  matched by the current row. If this fails, attempts are made to find three
  fragments of <i>N</i>/3 tokens each and finally four <i>N</i>/4 token
  fragments. If a set of four fragments cannot be found that encompasses the
  required phrase matches, the four fragments of <i>N</i>/4 tokens that provide
  the best coverage are selected.

<p>
  If <i>N</i> is a negative value, and no single fragment can be found
  containing the required phrase matches, the snippet function searches
  for two fragments of <i>|N|</i> tokens each, then three, then four. In
  other words, if the specified value of <i>N</i> is negative, the sizes
  of the fragments is not decreased if more than one fragment is required
  to provide the desired phrase match coverage.

<p>
  After the <i>M</i> fragments have been located, where <i>M</i> is between
  two and four as described in the paragraphs above, they are joined together
  in sorted order with the "ellipses" text separating them. The three
  modifications enumerated earlier are performed on the text before it is
  returned.

<codeblock>
  <b>Note: In this block of examples, newlines and whitespace characters have
  been inserted into the document inserted into the FTS table, and the expected
  results described in SQL comments. This is done to enhance readability only,
  they would not be present in actual SQLite commands or output.</b>

  <i>-- Create and populate an FTS table.</i>
  CREATE VIRTUAL TABLE text USING fts4();
  INSERT INTO text VALUES('
    During 30 Nov-1 Dec, 2-3oC drops. Cool in the upper portion, minimum temperature 14-16oC
    and cool elsewhere, minimum temperature 17-20oC. Cold to very cold on mountaintops,
    minimum temperature 6-12oC. Northeasterly winds 15-30 km/hr. After that, temperature
    increases. Northeasterly winds 15-30 km/hr.
  ');

  <i>-- The following query returns the text value:</i>
  <i>--</i>
  <i>--   "&lt;b&gt;...&lt;/b&gt;cool elsewhere, minimum temperature 17-20oC. &lt;b&gt;Cold&lt;/b&gt; to very </i>
  <i>--    &lt;b&gt;cold&lt;/b&gt; on mountaintops, minimum temperature 6&lt;b&gt;...&lt;/b&gt;".</i>
  <i>--</i>

  then the blob is zero bytes in size. Otherwise, the blob consists of zero
  or more 32-bit unsigned integers in machine byte-order. The exact number
  of integers in the returned array depends on both the query and the value
  of the second argument (if any) passed to the matchinfo function.

<p>
  The matchinfo function is called with either one or two arguments. As for
  all auxiliary functions, the first argument must be the special
  [FTS hidden column]. The second argument, if it is specified, must be a text value
  comprised only of the characters 'p', 'c', 'n', 'a', 'l', 's', 'x', 'y' and 'b'.
  If no second argument is explicitly supplied, it defaults to "pcx". The
  second argument is referred to as the "format string" below.

<p>
  Characters in the matchinfo format string are processed from left to right.
  Each character in the format string causes one or more 32-bit unsigned
  integer values to be added to the returned array. The "values" column in
  the following table contains the number of integer values appended to the
  output buffer for each supported format string character. In the formula
  given, <i>cols</i> is the number of columns in the FTS table, and
  <i>phrases</i> is the number of <a href=#matchable>matchable phrases</a> in
  the query.

<table striped=1>
  <tr><th>Character<th>Values<th>Description
  <tr><td>p <td>1 <td>The number of matchable phrases in the query.
  <tr><td>c <td>1 <td>The number of user defined columns in the FTS
    table (i.e. not including the docid or the [FTS hidden column]).
  <tr><td>x <td style="white-space:nowrap">3 * <i>cols</i> * <i>phrases</i>
    <td><tcl>hd_fragment matchinfo-x {matchinfo x flag}</tcl>
      For each distinct combination of a phrase and table column, the
      following three values:
      <ul>
        <li> In the current row, the number of times the phrase appears in
             the column.
        <li> The total number of times the phrase appears in the column in
             all rows in the FTS table.
        <li> The total number of rows in the FTS table for which the
             column contains at least one instance of the phrase.
      </ul>
      The first set of three values corresponds to the left-most column
      of the table (column 0) and the left-most matchable phrase in the
      query (phrase 0). If the table has more than one column, the second
      set of three values in the output array correspond to phrase 0 and
      column 1. Followed by phrase 0, column 2 and so on for all columns of
      the table. And so on for phrase 1, column 0, then phrase 1, column 1
      etc. In other words, the data for occurrences of phrase <i>p</i> in
      column <i>c</i> may be found using the following formula:
<pre>
          hits_this_row  = array&#91;3 * (c + p*cols) + 0&#93;
          hits_all_rows  = array&#91;3 * (c + p*cols) + 1&#93;
          docs_with_hits = array&#91;3 * (c + p*cols) + 2&#93;
</pre>
  <tr><td>y<td style="white-space:nowrap"><i>cols</i> * <i>phrases</i>
    <td><tcl>hd_fragment matchinfo-y {matchinfo y flag}</tcl>
      For each distinct combination of a phrase and table column, the
      number of usable phrase matches that appear in the column. This is
      usually identical to the first value in each set of three returned by the
      [matchinfo 'x' flag]. However, the number of hits reported by the
      'y' flag is zero for any phrase that is part of a sub-expression
      that does not match the current row. This makes a difference for
      expressions that contain AND operators that are descendants of OR
      operators. For example, consider the expression:
<pre>
          a OR (b AND c)
</pre>
      and the document:
<pre>
          "a c d"
</pre>

<pre>
          hits_for_phrase_p_column_c  = array&#91;c + p*cols&#93;
</pre>
      For queries that use OR expressions, or those that use LIMIT or return
      many rows, the 'y' matchinfo option may be faster than 'x'.

<tr><td>b<td style="white-space:nowrap"><i>((cols+31)/32)</i> * <i>phrases</i>
<td><tcl>hd_fragment matchinfo-b {matchinfo b flag}</tcl>

  The matchinfo 'b' flag provides similar information to the
  [matchinfo 'y' flag], but in a more
  compact form. Instead of the precise number of hits, 'b' provides a single
  boolean flag for each phrase/column combination. If the phrase is present in
  the column at least once (i.e. if the corresponding integer output of 'y' would

</pre>

  <tr><td>n <td>1 <td>The number of rows in the FTS4 table. This value is
    only available when querying FTS4 tables, not FTS3.
  <tr><td>a <td><i>cols</i> <td>For each column, the average number of
    tokens in the text values stored in the column (considering all rows in
    the FTS4 table). This value is only available when querying FTS4 tables,
    not FTS3.
  <tr><td>l <td><i>cols</i> <td>
    For each column, the length of the value stored in the current row of the
    FTS4 table, in tokens.  This value is only available when querying
    FTS4 tables, not FTS3. And only if the "matchinfo=fts3" directive was not
    specified as part of the "CREATE VIRTUAL TABLE" statement used to create
    the FTS4 table.
  <tr><td>s <td><i>cols</i> <td>For each column, the length of the longest
    subsequence of phrase matches that the column value has in common
    with the query text. For example, if a table column contains the text
    'a b c d e' and the query is 'a c "d e"', then the length of the longest
    common subsequence is 2 (phrase "c" followed by phrase "d e").


</table>

<p>
  For example:

<codeblock>
  <i>-- Create and populate an FTS4 table with two columns:</i>
  CREATE VIRTUAL TABLE t1 USING fts4(a, b);
  INSERT INTO t1 VALUES('transaction default models default', 'Non transaction reads');
  INSERT INTO t1 VALUES('the default transaction', 'these semantics present');
  INSERT INTO t1 VALUES('single request', 'default data');

  <i>-- In the following query, no format string is specified and so it defaults</i>
  <i>-- to "pcx". It therefore returns a single row consisting of a single blob</i>
  <i>-- value 80 bytes in size (20 32-bit integers - 1 for "p", 1 for "c" and</i>
  <i>-- 3*2*3 for "x"). If each block of 4 bytes in</i> the blob is interpreted</i>
  <i>-- as an unsigned integer in machine byte-order, the values will be:</i>
  <i>--</i>
  <i>--     3 2  1 3 2  0 1 1  1 2 2  0 1 1  0 0 0  1 1 1</i>
  <i>--</i>
  <i>-- The row returned corresponds to the second entry inserted into table t1.</i>
  <i>-- The first two integers in the blob show that the query contained three</i>
  <i>-- phrases and the table being queried has two columns. The next block of</i>

<codeblock>
  SELECT docid, matchinfo(tbl) FROM tbl WHERE tbl MATCH &lt;query expression&gt;;
  SELECT docid, offsets(tbl) FROM tbl WHERE tbl MATCH &lt;query expression&gt;;
</codeblock>

<p>
  The matchinfo function provides all the information required to calculate
  probabilistic "bag-of-words" relevancy scores such as
  <a href=http://en.wikipedia.org/wiki/Okapi_BM25>Okapi BM25/BM25F</a> that may
  be used to order results in a full-text search application. Appendix A of this
  document, "[search application tips]", contains an example of using the
  matchinfo() function efficiently.

<h1 id=fts4aux tags="fts4aux">Fts4aux - Direct Access to the Full-Text Index</h1>

<p>
  As of [version 3.7.6] ([dateof:3.7.6]),
  SQLite includes a new virtual table module called
  "fts4aux", which can be used to inspect the full-text index of an existing
  FTS table directly. Despite its name, fts4aux works just as well with FTS3
  tables as it does with FTS4 tables. Fts4aux tables are read-only. The only
  way to modify the contents of an fts4aux table is by modifying the
  contents of the associated FTS table. The fts4aux module is automatically
  included in all [compile fts|builds that include FTS].

<p>
  An fts4aux virtual table is constructed with one or two arguments.  When
  used with a single argument, that argument is the unqualified name of the
  FTS table that it will be used to access.  To access a table in a different
  database (for example, to create a TEMP fts4aux table that will access an
  FTS3 table in the MAIN database) use the two-argument form and give the
  name of the target database (ex: "main") in the first argument and the name
  of the FTS3/4 table as the second argument.  (The two-argument form of
  fts4aux was added for SQLite [version 3.7.17] ([dateof:3.7.17])
  and will throw an error in prior releases.)
  For example:

<codeblock>
  <i>-- Create an FTS4 table</i>
  CREATE VIRTUAL TABLE ft USING fts4(x, y);

  <i>-- Create an fts4aux table to access the full-text index for table "ft"</i>
  CREATE VIRTUAL TABLE ft_terms USING fts4aux(ft);

  <i>-- Create a TEMP fts4aux table accessing the "ft" table in "main"</i>
  CREATE VIRTUAL TABLE temp.ft_terms_2 USING fts4aux(main,ft);
</codeblock>

<p>
  For each term present in the FTS table, there are between 2 and N+1 rows
  in the fts4aux table, where N is the number of user-defined columns in
  the associated FTS table. An fts4aux table always has the same four columns,
  as follows, from left to right:

<table striped=1>
  <tr><th>Column Name<th>Column Contents
  <tr><td>term<td>
    Contains the text of the term for this row.
  <tr><td>col<td>
    This column may contain either the text value '*' (i.e. a single
    character, U+002a) or an integer between 0 and N-1, where N is
    again the number of user-defined columns in the corresponding FTS table.

  <tr><td>documents<td>
    This column always contains an integer value greater than zero.
    <br><br>
    If the "col" column contains the value '*', then this column
    contains the number of rows of the FTS table that contain at least one
    instance of the term (in any column). If col contains an integer
    value, then this column contains the number of rows of the FTS table that
    contain at least one instance of the term in the column identified by
    the col value. As usual, the columns of the FTS table are numbered
    from left to right, starting with zero.

  <tr><td>occurrences<td>
    This column also always contains an integer value greater than zero.
    <br><br>
    If the "col" column contains the value '*', then this column
    contains the total number of instances of the term in all rows of the
    FTS table (in any column). Otherwise, if col contains an integer
    value, then this column contains the total number of instances of the
    term that appear in the FTS table column identified by the col
    value.

  <tr><td>languageid <i>(hidden)</i><td>
    <tcl>hd_fragment f4alid {fts4aux languageid column}</tcl>

  <i>--     elderberry  |  0  |  1  |  1</i>
  <i>--     elderberry  |  1  |  1  |  1</i>
  <i>--</i>
  SELECT term, col, documents, occurrences FROM ft_terms;
</codeblock>

<p>
  In the example, the values in the "term" column are all lower case,
  even though they were inserted into table "ft" in mixed case. This is because
  an fts4aux table contains the terms as extracted from the document text
  by the [tokenizer]. In this case, since table "ft" uses the
  [tokenizer|simple tokenizer], this means all terms have been folded to
  lower case. Also, there is (for example) no row with column "term"
  set to "apple" and column "col" set to 1. Since there are no instances
  of the term "apple" in column 1, no row is present in the fts4aux table.

<p>
  During a transaction, some of the data written to an FTS table may be
  cached in memory and written to the database only when the transaction is
  committed. However the implementation of the fts4aux module is only able
  to read data from the database. In practice this means that if an fts4aux
  table is queried from within a transaction in which the associated
  FTS table has been modified, the results of the query are likely to reflect
  only a (possibly empty) subset of the changes made.

<h1 id=fts4_options tags="FTS4 options">FTS4 Options</h1>

<p>
  If the "CREATE VIRTUAL TABLE" statement specifies module FTS4 (not FTS3),
  then special directives - FTS4 options - similar to the "tokenize=*" option
  may also appear in place of column names. An FTS4 option consists of the
  option name, followed by an "=" character, followed by the option value.
  The option value may optionally be enclosed in single or double quotes, with
  embedded quote characters escaped in the same way as for SQL literals. There
  may not be whitespace on either side of the "=" character. For example,
  to create an FTS4 table with the value of option "matchinfo" set to "fts3":

  <tr><td>languageid<td>
    ^The languageid option causes the FTS4 table to have an additional hidden
    integer column that identifies the language of the text contained in
    each row.  The use of the languageid option allows the same FTS4 table
    to hold text in multiple languages or scripts, each with different tokenizer
    rules, and to query each language independently of the others.

  <tr><td>matchinfo<td>
    When set to the value "fts3", the matchinfo option reduces the amount of
    information stored by FTS4 with the consequence that the "l" option of
    [matchinfo()] is no longer available.

  <tr><td>notindexed<td>
    This option is used to specify the name of a column for which data is
    not indexed. Values stored in columns that are not indexed are not
    matched by MATCH queries. Nor are they recognized by auxiliary functions.
    A single CREATE VIRTUAL TABLE statement may have any number of notindexed
    options.

  <tr><td>order<td>
    <tcl>hd_fragment fts4order {FTS4 order option}</tcl>
    ^The "order" option may be set to either "DESC" or "ASC" (in upper or
    lower case). ^If it is set to "DESC", then FTS4 stores its data in such
    a way as to optimize returning results in descending order by docid.
    ^If it is set to "ASC" (the default), then the data structures are
    optimized for returning results in ascending order by docid.  ^In other
    words, if many of the queries run against the FTS4 table use "ORDER BY
    docid DESC", then it may improve performance to add the "order=desc"
    option to the CREATE VIRTUAL TABLE statement.

  <tr><td>prefix<td>
    This option may be set to a comma-separated list of positive non-zero
    integers. For each integer N in the list, a separate index is created
    in the database file to optimize [prefix queries] where
    the query term is N bytes in length, not including the '*' character,
    when encoded using UTF-8. [fts4 prefix option|See below] for details.

  <tr><td>uncompress<td>
    This option is used to specify the uncompress function. It is an error to
    specify an uncompress function without also specifying a compress
    function. [fts4 compress option|See below] for details.
</table>

<p>
  When using FTS4, specifying a column name that contains an "=" character
  and is not either a "tokenize=*" specification or a recognized FTS4 option
  is an error. With FTS3, the first token in the unrecognized directive is
  interpreted as a column name. Similarly, specifying multiple "tokenize=*"
  directives in a single table declaration is an error when using FTS4, whereas
  the second and subsequent "tokenize=*" directives are interpreted as column
  names by FTS3. For example:

<codeblock>
  <i>-- An error. FTS4 does not recognize the directive "xyz=abc".</i>

<tcl>hd_fragment *fts4compression {compressed FTS4 content}</tcl>
<h2 tags="fts4 compress option">The compress= and uncompress= options</h2>

<p>
  The compress and uncompress options allow FTS4 content to be stored in
  the database in a compressed form. Both options should be set to the name
  of an SQL scalar function registered using [sqlite3_create_function()]
  that accepts a single argument.

<p>
  The compress function should return a compressed version of the value
  passed to it as an argument. Each time data is written to the FTS4 table,
  each column value is passed to the compress function and the result value
  stored in the database. The compress function may return any type of SQLite
  value (blob, text, real, integer or null).

<p>
  The uncompress function should uncompress data previously compressed by
  the compress function. In other words, for all SQLite values X, it should
  be true that uncompress(compress(X)) equals X. When data that has been
  compressed by the compress function is read from the database by FTS4, it
  is passed to the uncompress function before it is used.

<p>
  If the specified compress or uncompress functions do not exist, the table
  may still be created. An error is not returned until the FTS4 table is
  read (if the uncompress function does not exist) or written (if it is the
  compress function that does not exist).

<codeblock>
  <i>-- Create an FTS4 table that stores data in compressed form. This</i>
  <i>-- assumes that the scalar functions zip() and unzip() have been (or</i>
  <i>-- will be) added to the database handle.</i>
  CREATE VIRTUAL TABLE papers USING fts4(author, document, compress=zip, uncompress=unzip);

<h2 tags="fts4 content option">The content= option </h2>

<p>
  The content option allows FTS4 to forego storing the text being indexed.
  The content option can be used in two ways:

<ul>
<li><p> The indexed documents are not stored within the SQLite database
        at all (a "contentless" FTS4 table), or

<li><p> The indexed documents are stored in a database table created and
        managed by the user (an "external content" FTS4 table).
</ul>

<p>
  Because the indexed documents themselves are usually much larger than
  the full-text index, the content option can be used to achieve
  significant space savings.

<h3 tags="contentless fts4 tables"> Contentless FTS4 Tables </h3>

<p>
  In order to create an FTS4 table that does not store a copy of the indexed
  documents at all, the content option should be set to an empty string.

  SELECT snippet(t1) FROM t1 WHERE t1 MATCH 'xxx';
</codeblock>

<p>
  Errors related to attempting to retrieve column values other than docid
  are runtime errors that occur within sqlite3_step(). In some cases, for
  example if the MATCH expression in a SELECT query matches zero rows, there
  may be no error at all even if a statement does refer to column values
  other than docid.

<h3 tags="external content fts4 tables"> External Content FTS4 Tables </h3>

<p>
  An "external content" FTS4 table is similar to a contentless table, except
  that if evaluation of a query requires the value of a column other than
  docid, FTS4 attempts to retrieve that value from a table (or view, or
  virtual table) nominated by the user (hereafter referred to as the "content
  table"). The FTS4 module never writes to the content table, and writing
  to the content table does not affect the full-text index. It is the
  responsibility of the user to ensure that the content table and the
  full-text index are consistent.

<p>
  An external content FTS4 table is created by setting the content option
  to the name of a table (or view, or virtual table) that may be queried by
  FTS4 to retrieve column values when required. If the nominated table does
  not exist, then an external content table behaves in the same way as
  a contentless table. For example:

<codeblock>
  CREATE TABLE t2(id INTEGER PRIMARY KEY, a, b, c);
  CREATE VIRTUAL TABLE t3 USING fts4(content="t2", a, c);
</codeblock>

<p>
  Assuming the nominated table does exist, then its columns must be the same
  as or a superset of those defined for the FTS table.  The external table
  must also be in the same database file as the FTS table.  In other words,
  The external table cannot be in a different database file connected using
  [ATTACH] nor may one of the FTS table and the external content be in the
  TEMP database when the other is in a persistent database file such as MAIN.

<p>
  When a users query on the FTS table requires a column value other than
  docid, FTS attempts to read the requested value from the corresponding column of
  the row in the content table with a rowid value equal to the current FTS
  docid. Only the subset of content-table columns duplicated in the FTS/34
  table declaration can be queried for - to retrieve values from any other
  columns the content table must be queried directly. Or, if such a row cannot
  be found in the content table, a NULL value is used instead. For example:

<codeblock>
  CREATE TABLE t2(id INTEGER PRIMARY KEY, a, b, c);
  CREATE VIRTUAL TABLE t3 USING fts4(content="t2", b, c);

  INSERT INTO t2 VALUES(2, 'a b', 'c d', 'e f');
  INSERT INTO t2 VALUES(3, 'g h', 'i j', 'k l');
  INSERT INTO t3(docid, b, c) SELECT id, b, c FROM t2;
  <i>-- The following query returns a single row with two columns containing
  -- the text values "i j" and "k l".
  --
  -- The query uses the full-text index to discover that the MATCH
  -- term matches the row with docid=3. It then retrieves the values
  -- of columns b and c from the row with rowid=3 in the content table
  -- to return.
  --</i>
  SELECT * FROM t3 WHERE t3 MATCH 'k';

  <i>-- Following the UPDATE, the query still returns a single row, this

  SELECT * FROM t3 WHERE t3 MATCH 'k';
</codeblock>

<p>
  When a row is deleted from an external content FTS4 table, FTS4 needs to
  retrieve the column values of the row being deleted from the content table.
  This is so that FTS4 can update the full-text index entries for each token
  that occurs within the deleted row to indicate that row has been
  deleted. If the content table row cannot be found, or if it contains values
  inconsistent with the contents of the FTS index, the results can be difficult
  to predict. The FTS index may be left containing entries corresponding to the
  deleted row, which can lead to seemingly nonsensical results being returned
  by subsequent SELECT queries. The same applies when a row is updated, as
  internally an UPDATE is the same as a DELETE followed by an INSERT.

<p>
  This means that in order to keep an FTS in sync with an external content
  table, any UPDATE or DELETE operations must be applied first to the FTS
  table, and then to the external content table. For example:

<codeblock>
  CREATE TABLE t1_real(id INTEGER PRIMARY KEY, a, b, c, d);
  CREATE VIRTUAL TABLE t1_fts USING fts4(content="t1_real", b, c);

  <i>-- This works. When the row is removed from the FTS table, FTS retrieves
  -- the row with rowid=123 and tokenizes it in order to determine the entries
  -- that must be removed from the full-text index.
  --</i>
  DELETE FROM t1_fts WHERE rowid = 123;
  DELETE FROM t1_real WHERE rowid = 123;

  --<i> This <b>does not work</b>. By the time the FTS table is updated, the row
  -- has already been deleted from the underlying content table. As a result
  -- FTS is unable to determine the entries to remove from the FTS index and
  -- so the index and content table are left out of sync.
  --</i>
  DELETE FROM t1_real WHERE rowid = 123;
  DELETE FROM t1_fts WHERE rowid = 123;
</codeblock>

<p>
  Instead of writing separately to the full-text index and the content table,
  some users may wish to use database triggers to keep the full-text index
  up to date with respect to the set of documents stored in the content table.
  For example, using the tables from earlier examples:

<codeblock>
  CREATE TRIGGER t2_bu BEFORE UPDATE ON t2 BEGIN

  When the languageid option is used, SQLite invokes the xLanguageid()
  on the sqlite3_tokenizer_module object immediately after the object
  is created in order to pass in the language id that the
  tokenizer should use.  The xLanguageid() method will never be called
  more than once for any single tokenizer object.  The fact that different
  languages might be tokenized differently is one reason why no single
  FTS query can return rows with different languageid values.



<tcl>hd_fragment fts4matchinfo {FTS4 matchinfo option}</tcl>
<h2 tags="fts4 matchinfo option">The matchinfo= option</h2>

<p>
  The matchinfo option may only be set to the value "fts3".
  Attempting to set matchinfo to anything other than "fts3" is an error.
  If this option is specified, then some of the extra information stored by
  FTS4 is omitted. This reduces the amount of disk space consumed by
  an FTS4 table until it is almost the same as the amount that would
  be used by the equivalent FTS3 table, but also means that the data
  accessed by passing the 'l' flag to the [matchinfo()] function is
  not available.

<tcl>hd_fragment fts4notindexed {FTS4 notindexed option}</tcl>
<h2 tags="fts4 notindexed option">The notindexed= option</h2>

<p>
  Normally, the FTS module maintains an inverted index of all terms in
  all columns of the table. This option is used to specify the name of
  a column for which entries should not be added to the index. Multiple
  "notindexed" options may be used to specify that multiple columns should
  be omitted from the index. For example:

<codeblock>
  <i>-- Create an FTS4 table for which only the contents of columns c2 and c4</i>
  <i>-- are tokenized and added to the inverted index.</i>
  CREATE VIRTUAL TABLE t1 USING fts4(c1, c2, c3, c4, notindexed=c1, notindexed=c3);
</codeblock>

<p>
  Values stored in unindexed columns are not eligible to match MATCH
  operators. They do not influence the results of the offsets() or matchinfo()
  auxiliary functions. Nor will the snippet() function ever return a
  snippet based on a value stored in an unindexed column.

<tcl>hd_fragment fts4prefix {FTS4 prefix option}</tcl>
<h2 tags="fts4 prefix option">The prefix= option</h2>

<p>
  ^The FTS4 prefix option causes FTS to index term prefixes of specified lengths
  in the same way that it always indexes complete terms.  ^The prefix option
  must be set to a comma separated list of positive non-zero integers.
  ^For each value N in the list, prefixes of length N bytes (when encoded
  using UTF-8) are indexed.  ^FTS4 uses term prefix indexes to speed up
  [prefix queries]. The cost, of course, is that indexing term prefixes as
  well as complete terms increases the database size and slows down write
  operations on the FTS4 table.

<p>
  Prefix indexes may be used to optimize [prefix queries] in two cases.
  If the query is for a prefix of N bytes, then a prefix index created
  with "prefix=N" provides the best optimization. Or, if no "prefix=N"
  index is available, a "prefix=N+1" index may be used instead.
  Using a "prefix=N+1" index is less
  efficient than a "prefix=N" index, but is better than no prefix index at all.

<codeblock>
  <i>-- Create an FTS4 table with indexes to optimize 2 and 4 byte prefix queries.</i>
  CREATE VIRTUAL TABLE t1 USING fts4(c1, c2, prefix="2,4");

<tcl>hd_fragment *fts4ickcmd {FTS4 "integrity-check" command}</tcl>
<h2 id=integcheck>The "integrity-check" command</h2>

<p>
  The "integrity-check" command causes SQLite to read and verify
  the accuracy of all inverted indices in an FTS3/4 table by comparing
  those inverted indices against the original content. The
  "integrity-check" command silently succeeds if the inverted
  indices are all ok, but will fail with an SQLITE_CORRUPT error
  if any problems are found.

<p>
  The "integrity-check" command is similar in concept to
 [PRAGMA integrity_check].  In a working system, the "integrity-command"

<tcl>hd_fragment *fts4automergecmd {FTS4 "automerge" command} \
                                   {"automerge" command}</tcl>
<h2 id=automerge">The "automerge=N" command</h2>

<p>
  The "automerge=N" command (where N is an integer between 0 and 15,
  inclusive) is used to configure an FTS3/4 tables "automerge" parameter,
  which controls automatic incremental inverted index merging. The default
  automerge value for new tables is 0, meaning that automatic incremental
  merging is completely disabled. If the value of the automerge parameter
  is modified using the "automerge=N" command, the new parameter value is
  stored persistently in the database and is used by all subsequently
  established database connections.

<p>
  Setting the automerge parameter to a non-zero value enables automatic
  incremental merging. This causes SQLite to do a small amount of inverted
  index merging after every INSERT operation. The amount of merging
  performed is designed so that the FTS3/4 table never reaches a point
  where it has 16 segments at the same level and hence has to do a large
  merge in order to complete an insert.  In other words, automatic
  incremental merging is designed to prevent spiky INSERT performance.

<p>
  The downside of automatic incremental merging is that it makes
  every INSERT, UPDATE, and DELETE operation on an FTS3/4 table run
  a little slower, since extra time must be used to do the incremental
  merge.  For maximum performance, it is recommended that applications
  disable automatic incremental merge and instead use the
  ["merge" command] in an idle process to keep the inverted indices
  well merged.  But if the structure of an application does not easily
  allow for idle processes, the use of automatic incremental merge is
  a very reasonable fallback solution.

<p>
  The actual value of the automerge parameter determines the number of
  index segments merged simultaneously by an automatic inverted index
  merge. If the value is set to N, the system waits until there are at
  least N segments on a single level before beginning to incrementally
  merge them. Setting a lower value of N causes segments to be merged more
  quickly, which may speed up full-text queries and, if the workload
  contains UPDATE or DELETE operations as well as INSERTs, reduce the space
  on disk consumed by the full-text index. However, it also increases the
  amount of data written to disk.

<p>
  For general use in cases where the workload contains few UPDATE or DELETE
  operations, a good choice for automerge is 8. If the workload contains
  many UPDATE or DELETE commands,
  or if query speed is a concern, it may be advantageous to reduce automerge
  to 2.

<p>
  For reasons of backwards compatibility, the "automerge=1" command sets
  the automerge parameter to 8, not 1 (a value of 1 would make no sense
  anyway, as merging data from a single segment is a no-op).


<h1 id=tokenizer tags="tokenizer">Tokenizers</h1>

<p>
  An FTS tokenizer is a set of rules for extracting terms from a document
  or basic FTS full-text query.

<p>
  Unless a specific tokenizer is specified as part of the CREATE
  VIRTUAL TABLE statement used to create the FTS table, the default
  tokenizer, "simple", is used. The simple tokenizer extracts tokens from
  a document or basic FTS full-text query according to the following
  rules:

<ul>
  <li><p> A term is a contiguous sequence of eligible characters, where
    eligible characters are all alphanumeric characters and all characters with
    Unicode codepoint values greater than or equal to 128.
    All other characters are
    discarded when splitting a document into terms. Their only contribution is
    to separate adjacent terms.

  <li><p> All uppercase characters within the ASCII range (Unicode codepoints
    less than 128), are transformed to their lowercase equivalents as part
    of the tokenization process. Thus, full-text queries are
    case-insensitive when using the simple tokenizer.
</ul>

<p>
  For example, when a document containing the text "Right now, they're very
  frustrated.", the terms extracted from the document and added to the
  full-text index are, in order, "right now they re very frustrated". Such
  a document would match a full-text query such as "MATCH 'Frustrated'",
  as the simple tokenizer transforms the term in the query to lowercase
  before searching the full-text index.

<p>
  As well as the "simple" tokenizer, the FTS source code features a tokenizer
  that uses the <a href="http://tartarus.org/~martin/PorterStemmer/">Porter
  Stemming algorithm</a>. This tokenizer uses the same rules to separate
  the input document into terms including folding all terms into lower case,
  but also uses the Porter Stemming algorithm to reduce related English language
  words to a common root. For example, using the same input document as in the
  paragraph above, the porter tokenizer extracts the following tokens:
  "right now thei veri frustrat". Even though some of these terms are not even
  English words, in some cases using them to build the full-text index is more
  useful than the more intelligible output produced by the simple tokenizer.
  Using the porter tokenizer, the document not only matches full-text queries
  such as "MATCH 'Frustrated'", but also queries such as "MATCH 'Frustration'",
  as the term "Frustration" is reduced by the Porter stemmer algorithm to
  "frustrat" - just as "Frustrated" is. So, when using the porter tokenizer,
  FTS is able to find not just exact matches for queried terms, but matches
  against similar English language terms. For more information on the
  Porter Stemmer algorithm, please refer to the page linked above.

<p>
  Example illustrating the difference between the "simple" and "porter"
  tokenizers:

<codeblock>

    <i>-- character by an earlier "tokenchars=" option.</i>
    CREATE VIRTUAL TABLE txt5 USING fts4(
        tokenize=unicode61 "tokenchars=." "separators=X." "tokenchars=="
    );
</codeblock>

<p>
  The arguments passed to the "tokenchars=" or "separators=" options are
  case-sensitive. In the example above, specifying that "X" is a separator
  character does not affect the way "x" is handled.

<tcl>hd_fragment f3tknzr {fts3_tokenizer}</tcl>
<h2>Custom (Application Defined) Tokenizers</h2>

<p>

<p>
  Prior to SQLite [version 3.11.0] ([dateof:3.11.0]), the arguments to
  fts3_tokenizer() could be literal strings or BLOBs. They did not have to
  be [bound parameters].  But that could lead to security problems in the
  event of an SQL injection.  Hence, the legacy behavior is now disabled
  by default.  But the old legacy behavior can be enabled, for backwards
  compatibility in applications that really need it,
  by calling
  [sqlite3_db_config](db,[SQLITE_DBCONFIG_ENABLE_FTS3_TOKENIZER],1,0).

<p>
  The following block contains an example of calling the fts3_tokenizer()
  function from C code:

</codeblock>


<tcl>hd_fragment fts3tok {fts3tokenize} {fts3tokenize virtual table}</tcl>
<h2>Querying Tokenizers</h2>

<p>The "fts3tokenize" virtual table can be used to directly access any
   tokenizer.  The following SQL demonstrates how to create an instance
   of the fts3tokenize virtual table:

<codeblock>
CREATE VIRTUAL TABLE tok1 USING fts3tokenize('porter');
</codeblock>

<p>The name of the desired tokenizer should be substituted in place of
   'porter' in the example, of course.  If the tokenizer requires one or
   more arguments, they should be separated by commas in the fts3tokenize
   declaration (even though they are separated by spaces in declarations
   of regular fts4 tables). The following creates fts4 and fts3tokenize
   tables that use the same tokenizer:
<codeblock>
  CREATE VIRTUAL TABLE text1 USING fts4(tokenize=icu en_AU);
  CREATE VIRTUAL TABLE tokens1 USING fts3tokenize(icu, en_AU);

  CREATE VIRTUAL TABLE text2 USING fts4(tokenize=unicode61 "tokenchars=@." "separators=123");
  CREATE VIRTUAL TABLE tokens2 USING fts3tokenize(unicode61, "tokenchars=@.", "separators=123");
</codeblock>

<p>
   Once the virtual table is created, it can be queried as follows:

<codeblock>
SELECT token, start, end, position
  FROM tok1
 WHERE input='This is a test sentence.';
</codeblock>

<p>The virtual table will return one row of output for each token in the
   input string.  The "token" column is the text of the token.  The "start"
   and "end" columns are the byte offset to the beginning and end of the
   token in the original input string.
   The "position" column is the sequence number
   of the token in the original input string.  There is also an "input"
   column which is simply a copy of the input string that is specified in
   the WHERE clause.  Note that a constraint of the form "input=?" must
   appear in the WHERE clause or else the virtual table will have no input
   to tokenize and will return no rows.  The example above generates
   the following output:

 WHERE input='This is a test sentence.';
</codeblock>

<p>The fts3tokenize virtual table can be used on any tokenizer, regardless
   of whether or not there exists an FTS3 or FTS4 table that actually uses
   that tokenizer.


<h1 tags="segment btree">Data Structures</h1>

<p>
  This section describes at a high-level the way the FTS module stores its
  index and content in the database. It is <b>not necessary to read or
  understand the material in this section in order to use FTS</b> in an
  application. However, it may be useful to application developers attempting
  to analyze and understand FTS performance characteristics, or to developers
  contemplating enhancements to the existing FTS feature set.
</p>

<tcl>hd_fragment *shadowtab {FTS shadow tables}</tcl>
<h2 tags="shadowtabs">Shadow Tables</h2>
<p>
  For each FTS virtual table in a database, three to five real (non-virtual) tables
  are created to store the underlying data.  These real tables are called "shadow tables".
  The real tables are named "%_content",
  "%_segdir", "%_segments", "%_stat", and "%_docsize", where "%" is replaced by the name
  of the FTS virtual table.

<p>
  The leftmost column of the "%_content" table is an INTEGER PRIMARY KEY field
  named "docid". Following this is one column for each column of the FTS
  virtual table as declared by the user, named by prepending the column name
  supplied by the user with "c<i>N</i>", where <i>N</i> is the index of the
  column within the table, numbered from left to right starting with 0. Data
  types supplied as part of the virtual table declaration are not used as
  part of the %_content table declaration. For example:

<codeblock>
  <i>-- Virtual table declaration</i>
  CREATE VIRTUAL TABLE abc USING fts4(a NUMBER, b TEXT, c);

  <i>-- Corresponding %_content table declaration</i>
  CREATE TABLE abc_content(docid INTEGER PRIMARY KEY, c0a, c1b, c2c);
</codeblock>

<p>
  The %_content table contains the unadulterated data inserted by the user
  into the FTS virtual table by the user. If the user does not explicitly
  supply a "docid" value when inserting records, one is selected automatically
  by the system.

<p>
  The %_stat and %_docsize tables are only created if the FTS table uses the
  FTS4 module, not FTS3. Furthermore, the %_docsize table is omitted if the
  FTS4 table is created with the [FTS4 matchinfo option|"matchinfo=fts3"] directive
  specified as part of the CREATE VIRTUAL TABLE statement. If they are created,
  the schema of the two tables is as follows:
<codeblock>
  CREATE TABLE %_stat(
    id INTEGER PRIMARY KEY,
    value BLOB
  );

  CREATE TABLE %_docsize(
    docid INTEGER PRIMARY KEY,
    size BLOB
  );
</codeblock>

<p>
  For each row in the FTS table, the %_docsize table contains a corresponding
  row with the same "docid" value. The "size" field contains a blob consisting
  of <i>N</i> FTS varints, where <i>N</i> is the number of user-defined columns
  in the table. Each varint in the "size" blob is the number of tokens in the
  corresponding column of the associated row in the FTS table. The %_stat table
  always contains a single row with the "id" column set to 0. The "value"
  column contains a blob consisting of <i>N+1</i> FTS varints, where <i>N</i>
  is again the number of user-defined columns in the FTS table. The first
  varint in the blob is set to the total number of rows in the FTS table. The
  second and subsequent varints contain the total number of tokens stored in
  the corresponding column for all rows of the FTS table.

<p>
  The two remaining tables, %_segments and %_segdir, are used to store the
  full-text index. Conceptually, this index is a lookup table that maps each
  term (word) to the set of docid values corresponding to records in the
  %_content table that contain one or more occurrences of the term. To
  retrieve all documents that contain a specified term, the FTS module
  queries this index to determine the set of docid values for records that
  contain the term, then retrieves the required documents from the %_content
  table. Regardless of the schema of the FTS virtual table, the %_segments
  and %_segdir tables are always created as follows:

    end_block INTEGER,                 <i>-- Blockid of last node in %_segments</i>
    root BLOB,                         <i>-- B-tree root node</i>
    PRIMARY KEY(level, idx)
  );
</codeblock>

<p>
  The schema depicted above is not designed to store the full-text index
  directly. Instead, it is used to store one or more b-tree structures. There
  is one b-tree for each row in the %_segdir table. The %_segdir table
  row contains the root node and various meta-data associated with the
  b-tree structure, and the %_segments table contains all other (non-root)
  b-tree nodes. Each b-tree is referred to as a "segment". Once it has
  been created, a segment b-tree is never updated (although it may be
  deleted altogether).

  entry are stored in ascending order.

<p>
  The contents of the logical full-text index is found by merging the
  contents of all segment b-trees. If a term is present in more than one
  segment b-tree, then it maps to the union of each individual doclist. If,
  for a single term, the same docid occurs in more than one doclist, then only
  the doclist that is part of the most recently created segment b-tree is
  considered valid.

<p>
  Multiple b-tree structures are used instead of a single b-tree to reduce
  the cost of inserting records into FTS tables. When a new record is
  inserted into an FTS table that already contains a lot of data, it is
  likely that many of the terms in the new record are already present in
  a large number of existing records. If a single b-tree were used, then
  large doclist structures would have to be loaded from the database,
  amended to include the new docid and term-offset list, then written back
  to the database. Using multiple b-tree tables allows this to be avoided
  by creating a new b-tree which can be merged with the existing b-tree
  (or b-trees) later on. Merging of b-tree structures can be performed as
  a background task, or once a certain number of separate b-tree structures
  have been accumulated. Of course, this scheme makes queries more expensive
  (as the FTS code may have to look up individual terms in more than one
  b-tree and merge the results), but it has been found that in practice this
  overhead is often negligible.

<h2>Variable Length Integer (varint) Format</h2>

<p>
  Integer values stored as part of segment b-tree nodes are encoded using the
  FTS varint format. This encoding is similar, but <b>not identical</b>, to
  the <a href="fileformat.html#varint_format">SQLite varint format</a>.

<p>
  An encoded FTS varint consumes between one and ten bytes of space. The
  number of bytes required is determined by the sign and magnitude of the
  integer value encoded. More accurately, the number of bytes used to store
  the encoded integer depends on the position of the most significant set bit
  in the 64-bit twos-complement representation of the integer value. Negative
  values always have the most significant bit set (the sign bit), and so are
  always stored using the full ten bytes. Positive integer values may be
  stored using less space.

<p>
  The final byte of an encoded FTS varint has its most significant bit
  cleared. All preceding bytes have the most significant bit set. Data
  is stored in the remaining seven least significant bits of each byte.
  The first byte of the encoded representation contains the least significant
  seven bits of the encoded integer value. The second byte of the encoded
  representation, if it is present, contains the seven next least significant
  bits of the integer value, and so on. The following table contains examples
  of encoded integer values:

<table striped=1>
  <tr><th>Decimal<th>Hexadecimal<th width=100%>Encoded Representation
  <tr><td>43<td>0x000000000000002B<td>0x2B
  <tr><td>200815<td>0x000000000003106F<td>0xEF 0xA0 0x0C
  <tr><td>-1<td>0xFFFFFFFFFFFFFFFF<td>0xFF 0xFF 0xFF 0xFF 0xFF 0xFF 0xFF 0xFF 0xFF 0x01
</table>


<h2>Segment B-Tree Format</h2>

<p>
  Segment b-trees are prefix-compressed b+-trees. There is one segment b-tree
  for each row in the %_segdir table (see above). The root node of the segment
  b-tree is stored as a blob in the "root" field of the corresponding row
  of the %_segdir table. All other nodes (if any exist) are stored in the
  "blob" column of the %_segments table. Nodes within the %_segments table are
  identified by the integer value in the blockid field of the corresponding
  row. The following table describes the fields of the %_segdir table:

<table striped=1>
  <tr><th>Column           <th width=100%>Interpretation
  <tr><td>level            <td>
    Between them, the contents of the "level" and "idx" fields define the
    relative age of the segment b-tree. The smaller the value stored in the
    "level" field, the more recently the segment b-tree was created. If two
    segment b-trees are of the same "level", the segment with the larger
    value stored in the "idx" column is more recent. The PRIMARY KEY constraint
    on the %_segdir table prevents any two segments from having the same value
    for both the "level" and "idx" fields.
  <tr><td>idx              <td> See above.
  <tr><td>start_block      <td>
    The blockid that corresponds to the node with the smallest blockid that
    belongs to this segment b-tree. Or zero if the entire segment b-tree
    fits on the root node. If it exists, this node is always a leaf node.
  <tr><td>leaves_end_block <td>
    The blockid that corresponds to the leaf node with the largest blockid
    that belongs to this segment b-tree. Or zero if the entire segment b-tree
    fits on the root node.
  <tr><td>end_block <td>
    This field may contain either an integer or a text field consisting of
    two integers separated by a space character (unicode codepoint 0x20).
<p style="margin-left:0;margin-right:0">
    The first, or only, integer is the blockid that corresponds to the interior

  always stored using a contiguous sequence of blockids. Furthermore, the
  nodes that make up a single level of the b-tree are themselves stored as
  a contiguous block, in b-tree order. The contiguous sequence of blockids
  used to store the b-tree leaves are allocated starting with the blockid
  value stored in the "start_block" column of the corresponding %_segdir row,
  and finishing at the blockid value stored in the "leaves_end_block"
  field of the same row. It is therefore possible to iterate through all the
  leaves of a segment b-tree, in key order, by traversing the %_segments
  table in blockid order from "start_block" to "leaves_end_block".

<h3>Segment B-Tree Leaf Nodes</h3>

<p>
  The following diagram depicts the format of a segment b-tree leaf node.

<center>
  <img src=images/fts3_leaf_node.png>
  <p> Segment B-Tree Leaf Node Format
</center>

<p>
  The first term stored on each node ("Term 1" in the figure above) is
  stored verbatim. Each subsequent term is prefix-compressed with respect
  to its predecessor. Terms are stored within a page in sorted (memcmp)
  order.

<h3>Segment B-Tree Interior Nodes</h3>

<p>
  The following diagram depicts the format of a segment b-tree interior
  (non-leaf) node.

<center>
  <img src=images/fts3_interior_node.png>
  <p> Segment B-Tree Interior Node Format
</center>


<h2>Doclist Format</h2>

<p>
  A doclist consists of an array of 64-bit signed integers, serialized using
  the FTS varint format. Each doclist entry is made up of a series of two
  or more integers, as follows:

<ol>
  <li> The docid value. The first entry in a doclist contains the literal docid
       value. The first field of each subsequent doclist entry contains the
       difference between the new docid and the previous one (always a positive
       number).
  <li> Zero or more term-offset lists. A term-offset list is present for each
       column of the FTS virtual table that contains the term. A term-offset
       list consists of the following:
     <ol>
       <li> Constant value 1. This field is omitted for any term-offset list
            associated with column 0.
       <li> The column number (1 for the second leftmost column, etc.). This
            field is omitted for any term-offset list associated with column 0.
       <li> A list of term-offsets, sorted from smallest to largest. Instead
            of storing the term-offset value literally, each integer stored
            is the difference between the current term-offset and the previous
            one (or zero if the current term-offset is the first), plus 2.
     </ol>
  <li> Constant value 0.
</ol>

<center>
  <img src=images/fts3_doclist2.png>
  <p> FTS3 Doclist Format
</center>

<center>
  <img src=images/fts3_doclist.png>
  <p> FTS Doclist Entry Format
</center>

<p>
  For doclists for which the term appears in more than one column of the FTS
  virtual table, term-offset lists within the doclist are stored in column
  number order. This ensures that the term-offset list associated with
  column 0 (if any) is always first, allowing the first two fields of the
  term-offset list to be omitted in this case.

<h1 tags="bugs">Limitations</h1>

<h2> UTF-16 byte-order-mark problem </h2>

For UTF-16 databases, when using the "simple" tokenizer, it is possible to use
malformed unicode strings to cause the
<a href="#integcheck">integrity-check special command</a> to falsely report
corruption, or for <a href="#snippet">auxiliary functions</a> to return
incorrect results. More specifically, the bug can be triggered by any of the following:

<ul>
  <li><p>A UTF-16 byte-order-mark (BOM) is embedded at the beginning of an SQL string
       literal value inserted into an FTS3 table. For example:

<codeblock>
    INSERT INTO fts_table(col) VALUES(char(0xfeff)||'text...');
</codeblock>

  <li><p>Malformed UTF-8 that SQLite converts to a UTF-16 byte-order-mark is
       embedded at the beginning of an SQL string literal value inserted
       into an FTS3 table.

  <li><p>A text value created by casting a blob that begins with the two
       bytes 0xFF and 0xFE, in either possible order, is inserted into an
       FTS3 table. For example:

<codeblock>
    INSERT INTO fts_table(col) VALUES(CAST(X'FEFF' AS TEXT));
</codeblock>
</ul>

Everything works correctly if any of the following are true:
<ul>

<h1 id=appendix_a nonumber tags="search application tips">
  Appendix A: Search Application Tips
</h1>

<p>
  FTS is primarily designed to support Boolean full-text queries - queries
  to find the set of documents that match a specified criteria. However, many
  (most?) search applications require that results are somehow ranked in order
  of "relevance", where "relevance" is defined as the likelihood that the user
  who performed the search is interested in a specific element of the returned
  set of documents. When using a search engine to find documents on the world
  wide web, the user expects that the most useful, or "relevant", documents
  will be returned as the first page of results, and that each subsequent page
  contains progressively less relevant results. Exactly how a machine can
  determine document relevance based on a users query is a complicated problem
  and the subject of much ongoing research.

<p>
  One very simple scheme might be to count the number of instances of the
  users search terms in each result document. Those documents that contain
  many instances of the terms are considered more relevant than those with
  a small number of instances of each term. In an FTS application, the
  number of term instances in each result could be determined by counting
  the number of integers in the return value of the [offsets] function.
  The following example shows a query that could be used to obtain the
  ten most relevant results for a query entered by the user:

<codeblock>
  <i>-- This example (and all others in this section) assumes the following schema</i>
  CREATE VIRTUAL TABLE documents USING fts3(title, content);

  <i>-- Assuming the application has supplied an SQLite user function named "countintegers"</i>
  <i>-- that returns the number of space-separated integers contained in its only argument,</i>
  <i>-- the following query could be used to return the titles of the 10 documents that contain</i>
  <i>-- the greatest number of instances of the users query terms. Hopefully, these 10</i>
  <i>-- documents will be those that the users considers more or less the most "relevant".</i>
  SELECT title FROM documents
    WHERE documents MATCH &lt;query&gt;
    ORDER BY countintegers(offsets(documents)) DESC
    LIMIT 10 OFFSET 0
</codeblock>

<p>
  The query above could be made to run faster by using the FTS [matchinfo]
  function to determine the number of query term instances that appear in each
  result. The matchinfo function is much more efficient than the offsets
  function. Furthermore, the matchinfo function provides extra information
  regarding the overall number of occurrences of each query term in the entire
  document set (not just the current row) and the number of documents in which
  each query term appears. This may be used (for example) to attach a higher
  weight to less common terms which may increase the overall computed relevancy
  of those results the user considers more interesting.

<codeblock>
  <i>-- If the application supplies an SQLite user function called "rank" that</i>
  <i>-- interprets the blob of data returned by matchinfo and returns a numeric</i>
  <i>-- relevancy based on it, then the following SQL may be used to return the</i>
  <i>-- titles of the 10 most relevant documents in the dataset for a users query.</i>
  SELECT title FROM documents
    WHERE documents MATCH &lt;query&gt;
    ORDER BY rank(matchinfo(documents)) DESC
    LIMIT 10 OFFSET 0
</codeblock>

<p>
  The SQL query in the example above uses less CPU than the first example
  in this section, but still has a non-obvious performance problem. SQLite
  satisfies this query by retrieving the value of the "title" column and
  matchinfo data from the FTS module for every row matched by the users
  query before it sorts and limits the results. Because of the way SQLite's
  virtual table interface works, retrieving the value of the "title" column
  requires loading the entire row from disk (including the "content" field,
  which may be quite large). This means that if the users query matches
  several thousand documents, many megabytes of "title" and "content" data
  may be loaded from disk into memory even though they will never be used
  for any purpose.

<p>
  The SQL query in the following example block is one solution to this
  problem. In SQLite, when a <a href="optoverview.html#flattening">sub-query
  used in a join contains a LIMIT clause</a>, the results of the sub-query are
  calculated and stored in temporary table before the main query is executed.
  This means that SQLite will load only the docid and matchinfo data for each
  row matching the users query into memory, determine the docid values
  corresponding to the ten most relevant documents, then load only the title
  and content information for those 10 documents only. Because both the matchinfo
  and docid values are gleaned entirely from the full-text index, this results
  in dramatically less data being loaded from the database into memory.

<codeblock>
  SELECT title FROM documents JOIN (
      SELECT docid, rank(matchinfo(documents)) AS rank
      FROM documents
      WHERE documents MATCH &lt;query&gt;
      ORDER BY rank DESC
      LIMIT 10 OFFSET 0
  ) AS ranktable USING(docid)
  ORDER BY ranktable.rank DESC
</codeblock>

<p>
  The next block of SQL enhances the query with solutions to two other problems
  that may arise in developing search applications using FTS:

<ol>
  <li> <p>
       The [snippet] function cannot be used with the above query. Because
       the outer query does not include a "WHERE ... MATCH" clause, the snippet
       function may not be used with it. One solution is to duplicate the WHERE
       clause used by the sub-query in the outer query. The overhead associated
       with this is usually negligible.
  <li> <p>
       The relevancy of a document may depend on something other than just
       the data available in the return value of matchinfo. For example
       each document in the database may be assigned a static weight based
       on factors unrelated to its content (origin, author, age, number
       of references etc.). These values can be stored by the application
       in a separate table that can be joined against the documents table
       in the sub-query so that the rank function may access them.
</ol>

<p>
  This version of the query is very similar to that used by the
  <a href="http://www.sqlite.org/search?q=fts3">sqlite.org documentation search</a>
  application.

<codeblock>
  <i>-- This table stores the static weight assigned to each document in FTS table</i>
  <i>-- "documents". For each row in the documents table there is a corresponding row</i>
  <i>-- with the same docid value in this table.</i>
  CREATE TABLE documents_data(docid INTEGER PRIMARY KEY, weight);

  <i>-- This query is similar to the one in the block above, except that:</i>
  <i>--</i>
  <i>--   1. It returns a "snippet" of text along with the document title for display. So</i>
  <i>--      that the snippet function may be used, the "WHERE ... MATCH ..." clause from</i>
  <i>--      the sub-query is duplicated in the outer query.</i>
  <i>--</i>
  <i>--   2. The sub-query joins the documents table with the document_data table, so that</i>
  <i>--      implementation of the rank function has access to the static weight assigned</i>
  <i>--      to each document.</i>
  SELECT title, snippet(documents) FROM documents JOIN (
      SELECT docid, rank(matchinfo(documents), documents_data.weight) AS rank
      FROM documents JOIN documents_data USING(docid)
      WHERE documents MATCH &lt;query&gt;
      ORDER BY rank DESC
      LIMIT 10 OFFSET 0
  ) AS ranktable USING(docid)
  WHERE documents MATCH &lt;query&gt;
  ORDER BY ranktable.rank DESC
</codeblock>

<p>
  All the example queries above return the ten most relevant query results.
  By modifying the values used with the OFFSET and LIMIT clauses, a query
  to return (say) the next ten most relevant results is easy to construct.
  This may be used to obtain the data required for a search applications second
  and subsequent pages of results.

<p>
  The next block contains an example rank function that uses matchinfo data
  implemented in C. Instead of a single weight, it allows a weight to be
  externally assigned to each column of each document. It may be registered
  with SQLite like any other user function using [sqlite3_create_function].

<p><b> Security Warning:</b> Because it is just an ordinary SQL function,
  rank() may be invoked as part of any SQL query in any context. This means
  that the first argument passed may not be a valid matchinfo blob.
  Implementors should take care to handle this case without causing buffer
  overruns or other potential security problems.

<codeblock>
<i>/*</i>

<p>^The unary operator [Operator +] is a no-op.  ^It can be applied
to strings, numbers, blobs or NULL and it always returns a result
with the same value as the operand.</p>"</tcl>

<p>Note that there are two variations of the equals and not equals
operators.  ^Equals can be either


<tcl>hd_puts "[Operator =] or [Operator ==].
^The not-equal operator can be either
[Operator !=] or [Operator {&lt;&gt;}].
^The [Operator ||] operator is \"concatenate\" - it joins together
the two strings of its operands.
^The [Operator {-&gt;}] and [Operator {-&gt;&gt;}] operators are \"extract\";
they extract the RHS component from the LHS."</tcl>
For an example, see
[{the -> and ->> operators}|JSON subcomponent extraction].</p>

<p>
<tcl>hd_resolve "
^The [Operator %] operator \[cast|casts\] both of its operands to type
INTEGER and then computes the remainder after dividing the left integer
by the right integer. The other arithmetic operators perform integer
arithmetic if both operands are integers and no overflow would result,
or floating point arithmetic, per IEEE Standard 754, if either operand
is a real value or integer arithmetic would produce an overflow.
Integer divide yields an integer result, truncated toward zero.
</p>

<p>^The result of any binary operator is either a numeric value or
NULL, except for the [Operator ||] concatenation operator,
and the [Operator {-&gt;}] and [Operator {-&gt;&gt;}] extract operators
which evaluate to either NULL or a text value.</p>

<p>All operators generally evaluate to NULL when any operand is NULL,
with specific exceptions as stated below. This is in accordance with
the SQL92 standard.</p>

<p>
^(If a numeric literal has a decimal point or an exponentiation
clause or if it is less than -9223372036854775808 or
greater than 9223372036854775807, then it is a floating point literal.
Otherwise is it is an  integer literal.)^
^The "E" character that begins the exponentiation
clause of a floating point literal can be either upper or lower case.
^(The "." character is always used
as the decimal point even if the locale setting specifies "," for
this role - the use of "," for the decimal point would result in
syntactic ambiguity.)^

<tcl>hd_fragment hexint {hexadecimal integer literals} {hexadecimal integers}</tcl>
<p>^Hexadecimal integer literals follow the C-language notation of
"0x" or "0X" followed by hexadecimal digits.

^A literal value can also be the token "NULL".
</p>

<tcl>hd_fragment varparam parameter parameters {bound parameter} {bound parameters}</tcl>
<h1>Parameters</h1>
<p>
A "variable" or "parameter" token
specifies a placeholder in the expression for a
value that is filled in at runtime using the
[sqlite3_bind_blob() | sqlite3_bind()] family of C/C++ interfaces.
Parameters can take several forms:
</p>

<blockquote>
<table>

engines.  But because it is easy to miscount the question marks, the
use of this parameter format is discouraged.  Programmers are encouraged
to use one of the symbolic formats below or the ?NNN format above instead.
</td>
</tr>
<tr>
<td align="right" valign="top"><b>:</b><i>AAAA</i></td><td width="20"></td>
<td>^A colon followed by an identifier name holds a spot for a
[sqlite3_bind_parameter_name|named parameter] with the name :AAAA.
^(Named parameters are also numbered. The number assigned is one greater than
the largest parameter number already assigned.)^ ^If this means the parameter
would be assigned a number greater than [SQLITE_MAX_VARIABLE_NUMBER], it is
an error. To avoid confusion, it is best to avoid mixing named and numbered
parameters.</td>
</tr>
<tr>
<td align="right" valign="top"><b>@</b><i>AAAA</i></td><td width="20"></td>
<td>^An "at" sign works exactly like a colon, except that the name of
the parameter created is @AAAA.</td>
</tr>
<tr>
<td align="right" valign="top"><b>$</b><i>AAAA</i></td><td width="20"></td>
<td>^A dollar-sign followed by an identifier name also holds a spot for a named
parameter with the name $AAAA.  ^(The identifier name in this case can include
one or more occurrences of "::" and a suffix enclosed in "(...)" containing
any text at all.)^  This syntax is the form of a variable name in the
[http://www.tcl-lang.org/ | Tcl programming language].  The presence
of this syntax results from the fact that SQLite is really a
[Tcl extension] that has escaped into the wild.</td>
</tr>
</table>
</blockquote>

<p>^Parameters that are not assigned values using
[sqlite3_bind_blob() | sqlite3_bind()] are treated

<p>^The LIKE operator can be made case sensitive using the
[case_sensitive_like pragma].</p>

<tcl>hd_fragment glob GLOB</tcl>
<p>^The GLOB operator is similar to LIKE but uses the Unix
file globbing syntax for its wildcards.  ^Also, GLOB is case
sensitive, unlike LIKE.  ^Both GLOB and LIKE may be preceded by
the NOT keyword to invert the sense of the test.  ^The infix GLOB
operator is implemented by calling the function
[glob(<i>Y</i>,<i>X</i>)] and can be modified by overriding
that function.</p>

<tcl>hd_fragment regexp REGEXP</tcl>
<p>^The REGEXP operator is a special syntax for the regexp()
user function.  ^No regexp() user function is defined by default

raises an exception and is not really useful for anything.
^But extensions can override the match() function with more
helpful logic.</p>

<tcl>hd_fragment extract extract</tcl>
<p>^The extract operators act as a special syntax for functions
"-&gt;"() and "-&gt;&gt;"(). ^Default implementations for these functions
perform [{the -> and ->> operators}|JSON subcomponent extraction],
but extensions can override them for other purposes.</p>

<tcl>hd_fragment between BETWEEN</tcl>
<h1>The BETWEEN operator</h1>
<p>^(The BETWEEN operator is logically equivalent to a pair of comparisons.
"<i>x</i> <b>BETWEEN</b> <i>y</i> <b>AND</b> <i>z</i>" is
equivalent to
"<i>x</i><b>&gt;=</b><i>y</i> <b>AND</b> <i>x</i><b>&lt;=</b><i>z</i>" except
that with BETWEEN, the <i>x</i> expression is only evaluated once.)^

<tcl>hd_fragment case {CASE expression}</tcl>
<h1>The CASE expression</h1>
<p>A CASE expression serves a role similar to IF-THEN-ELSE in other
programming languages.

<p>The optional expression that occurs in between the CASE keyword and the
first WHEN keyword is called the "base" expression. ^There are two
fundamental forms
of the CASE expression: those with a base expression and those without.

<p>^In a CASE without a base expression, each WHEN expression is evaluated
and the result treated as a boolean, starting with the leftmost and continuing
to the right. ^The result of the CASE expression is the evaluation of the THEN
expression that corresponds to the first WHEN expression that evaluates to
true. ^Or, if none of the WHEN expressions evaluate to true, the result of
evaluating the ELSE expression, if any. ^If there is no ELSE expression and
none of the WHEN expressions are true, then the overall result is NULL.

<p>^A NULL result is considered untrue when evaluating WHEN terms.

<p>^In a CASE with a base expression, the base expression is evaluated just
once and the result is compared against the evaluation of each WHEN
expression from left to right. ^The result of the CASE expression is the
evaluation of the THEN expression that corresponds to the first WHEN
expression for which the comparison is true. ^Or, if none of the WHEN
expressions evaluate to a value equal to the base expression, the result
of evaluating the ELSE expression, if any. ^If there is no ELSE expression and
none of the WHEN expressions produce a result equal to the base expression,
the overall result is NULL.

<p>^When comparing a base expression against a WHEN expression, the same
collating sequence, affinity, and NULL-handling rules apply as if the
base expression and WHEN expression are respectively the left- and
right-hand operands of an <big><b>=</b></big> operator.</p>

<p>^If the base
expression is NULL then the result of the CASE is always the result
of evaluating the ELSE expression if it exists, or NULL if it does not.

<p>^Both forms of the CASE expression use lazy, or short-circuit,
evaluation.

<p>^(The only difference between the following two CASE expressions is that
the <i>x</i> expression is evaluated exactly once in the first example but
might be evaluated multiple times in the second:

<ul><pre>
<li>CASE x WHEN w1 THEN r1 WHEN w2 THEN r2 ELSE r3 END
<li>CASE WHEN x=w1 THEN r1 WHEN x=w2 THEN r2 ELSE r3 END
</pre></ul>)^

the [row value] of the left operand.  ^The subquery on the
right of an IN or NOT IN operator must be a scalar subquery if the left
expression is not a [row value] expression.
^If the right operand of an IN or NOT IN operator is a list of values,
each of those values must be scalars and the left expression must also
be a scalar.
^The right-hand side of an IN or NOT IN operator can be a
table <i>name</i> or [table-valued function] <i>name</i> in which
case the right-hand side is understood to be subquery of
the form "(SELECT * FROM <i>name</i>)".
^When the right operand is an empty set, the result of IN is false and the
result of NOT IN is true, regardless of the left operand and even if the
left operand is NULL.
<p>^(The result of an IN or NOT IN operator is determined by the following
matrix:

<h1>Table Column Names</h1>

<p>^A column name can be any of the names defined in the [CREATE TABLE]
statement or one of the following special identifiers: "<b>ROWID</b>",
"<b>OID</b>", or "<b>_ROWID_</b>".
^The three special identifiers describe the
unique integer key (the [rowid]) associated with every
row of every table and so are not available on [WITHOUT ROWID] tables.
^The special identifiers only refer to the row key if the [CREATE TABLE]
statement does not define a real column with the same name.
^The rowid can be used anywhere a regular
column can be used.</p>

<tcl>hd_fragment exists_op {EXISTS operator} {NOT EXISTS operator}</tcl>
<h1>The EXISTS operator</h1>

<p>^The EXISTS operator always evaluates to one of the integer values 0
and 1. ^If executing the SELECT statement specified as the right-hand
operand of the EXISTS operator would return one or more rows, then the
EXISTS operator evaluates to 1. ^If executing the SELECT would return
no rows at all, then the EXISTS operator evaluates to 0.

<p>^The number of columns in each row returned by the SELECT statement
(if any) and the specific values returned have no effect on the results
of the EXISTS operator. ^In particular, rows containing NULL values are
not handled any differently from rows without NULL values.

<tcl>hd_fragment subq {Subqueries}</tcl>

^A subquery that returns two or more columns is a [row value]
subquery and can only be used as an operand of a comparison operator or as
the value in an UPDATE SET clause whose column name list has the same size.

<tcl>hd_fragment cosub {correlated subqueries}</tcl>
<h1>Correlated Subqueries</h1>

<p>^A [SELECT] statement used as either a scalar subquery or as the
right-hand operand of an IN, NOT IN or EXISTS expression may contain
references to columns in the outer query. Such a subquery is known as
a correlated subquery. ^A correlated subquery is reevaluated each time
its result is required. ^An uncorrelated subquery is evaluated only once
and the result reused as necessary.

<tcl>hd_fragment castexpr {CAST expression} {CAST} {cast} {CAST operator}</tcl>
<h1>CAST expressions</h1>

<p>A CAST expression of the form "CAST(<i>expr</i> AS <i>type-name</i>)"
is used to convert the value of <i>expr</i> to
a different [storage class] specified by <yyterm>type-name</yyterm>.
^A CAST conversion is similar to the conversion that takes
place when a [column affinity] is applied to a value except that with
the CAST operator the conversion always takes place even if the conversion
lossy and irreversible, whereas column affinity only changes the data type
of a value if the change is lossless and reversible.

<p>^If the value of <i>expr</i> is NULL, then the result of the CAST
expression is also NULL. ^Otherwise, the storage class of the result
is determined by applying the [rules for determining column affinity] to
the <yyterm>type-name</yyterm>.

<blockquote>
<table border=1>
<tr>
  <th> Affinity of <yyterm><nobr>type-name</nobr></yyterm>
  <th> Conversion Processing
<tr>
  <td> NONE
  <td> ^Casting a value to a <yyterm>type-name</yyterm> with no affinity
  causes the value to
  be converted into a BLOB.  ^Casting to a BLOB consists of first casting
  the value to TEXT in the [encoding] of the database connection, then
  interpreting the resulting byte sequence as a BLOB instead of as TEXT.

<tr>
  <td> TEXT
  <td> ^To cast a BLOB value to TEXT, the sequence of bytes that make up the
  BLOB is interpreted as text encoded using the database encoding.
  <p>
   ^Casting an INTEGER or REAL value into TEXT renders the value as if via
    [sqlite3_snprintf()] except that the resulting TEXT uses the [encoding] of
    the database connection.

<tr>
  <td> REAL
  <td> ^When casting a BLOB value to a REAL, the value is first converted to
        TEXT.

        <p>^When casting to INTEGER, if the text looks like a floating point
        value with an exponent, the exponent will be ignored because it is
        no part of the integer prefix.  ^For example,
        "(CAST '123e+5' AS INTEGER)" results in 123, not in 12300000.

        <p> ^(The CAST operator understands decimal
        integers only &mdash; conversion of [hexadecimal integers] stops
        at the "x" in the "0x" prefix of the hexadecimal integer string
        and thus result of the CAST is always zero.)^

      <p>^A cast of a REAL value into an INTEGER results in the integer
      between the REAL value and zero that is closest to the REAL value.
      ^If a REAL is greater than the greatest possible signed
      integer (+9223372036854775807) then the result is the greatest possible
      signed integer and if the REAL is less than the least possible signed

  <td> NUMERIC
  <td> ^Casting a TEXT or BLOB value into NUMERIC yields either an INTEGER or
   a REAL result.
   ^If the input text looks like an integer (there is no decimal point nor
   exponent) and the value is small enough to fit in a 64-bit signed integer,
   then the result will be INTEGER.
   ^Input text that looks like floating point (there is a decimal point and/or
   an exponent) and the text describes a value that
   can be losslessly converted back and forth between IEEE 754 64-bit float and a
   51-bit signed integer, then the result is INTEGER.
   (In the previous sentence, a 51-bit integer is specified since that is one
   bit less than the length of the mantissa of an IEEE 754 64-bit float and
   thus provides a 1-bit of margin for the text-to-float conversion operation.)
   ^Any text input that describes a value outside the range of a 64-bit
   signed integer yields a REAL result.
   <p> ^Casting a REAL or INTEGER value to NUMERIC is a no-op, even if a real
   value could be losslessly converted to an integer.
</tr>
</table>
</blockquote>

<p>^Note that the result from casting any non-BLOB value into a
BLOB and the result from casting any BLOB value into a non-BLOB value
may be different depending on whether the database [encoding] is UTF-8,
UTF-16be, or UTF-16le.


<tcl>hd_fragment booleanexpr {boolean expression}</tcl>
<h1>Boolean Expressions</h1>

<p>The SQL language features several contexts where an expression is
evaluated and the result converted to a boolean (true or false) value. These
contexts are:

  <ul>
    <li> the WHERE clause of a SELECT, UPDATE or DELETE statement,
    <li> the ON or USING clause of a join in a SELECT statement,
    <li> the HAVING clause of a SELECT statement,
    <li> the WHEN clause of an SQL trigger, and
    <li> the WHEN clause or clauses of some CASE expressions.
  </ul>

<p>^(To convert the results of an SQL expression to a boolean value, SQLite
first casts the result to a NUMERIC value in the same way as a
[CAST expression]. A numeric zero value (integer value 0 or real
value 0.0) is considered to be false.  A NULL value is still NULL.
All other values are considered true.)^

<p>^(For example, the values NULL, 0.0, 0, 'english' and '0' are all considered
to be false.)^ ^(Values 1, 1.0, 0.1, -0.1 and '1english' are considered to
be true.)^

<p>Beginning with SQLite 3.23.0 ([dateof:3.23.0]), SQLite recognizes the
identifiers "TRUE" and "FALSE" as boolean literals, if and only if those
identifiers are not already used for some other meaning.  If there already
exists columns or tables or other objects named TRUE or FALSE, then for
the sake of backwards compatibility, the TRUE and FALSE identifiers refer

      {<a href="releaselog/\2_\3_\4.html">\0</a>} title
  }
  hd_puts "<h3>$date - $title</h3>"
  regsub -all "\n( *\n)+" $text "</p>\n\n<p>" txt
  hd_resolve "<blockquote>$txt</blockquote>"
  hd_puts "<hr width=\"50%\">"
}

newsitem {2022-12-28} {Version 3.40.1} {
Version 3.40.1 is a patch release that fixes some obscure problems
in version 3.40.0.  The problems fixed have no impact on most applications.
Upgrading is only necessary if you encounter problems.
<p>
The two most important fixes are these:
<ul>
<li><p>
Fix the [safe command-line option] on the [CLI] so that it
correctly disallows functions with side-effects.  This is a bug
in the CLI &mdash; <i>not</i> a bug in the
SQLite library &mdash; and it only affects the --safe command-line
option, making that option less than fully "safe".  As the number
of systems that use the --safe command-line option in the CLI is
approximately zero, this is not considered an important bug.  However,
a third-party wrote a CVE against it which caused considerable angst
among maintainers, so it seems good to get the fix into circulation
sooner rather than wait on the next major release.
<li><p>
The optional [memsys5] memory allocator picked up a bug that might
put it into an infinite loop for very large (500MiB) allocations.
Almost all systems use their native memory allocator, not memsys5.
Memsys5 is only used if SQLite is compiled using SQLITE_ENABLE_MEMSYS5
and then initialized using [sqlite3_config]([SQLITE_CONFIG_HEAP]).
Very, very few systems do this, and so the problem is not considered
to be serious.
</ul>
<p>
See the [https://sqlite.org/src/timeline?r=branch-3.40|branch-3.40 timeline]
for a complete listing of changes that have gone into this patch release.
}

newsitem {2022-11-16} {Version 3.40.0} {
Version 3.40.0 is a new feature release of SQLite.  See the
<a href="releaselog/3_40_0.html">change log</a> for details.
Key enhancements in this release include:
<ol>
<li><p>

Upgrade only if you are having problems.
}

newsitem {2015-10-16} {Release 3.9.1} {
<p>SQLite [version 3.9.1] is a
[https://www.sqlite.org/src/vpatch?from=version-3.9.0&to=version-3.9.1|small patch]
to [version 3.9.0] that includes
a few simple build script and #ifdef tweaks to make the code easier to
compile on a wider variety of platform.  There are no functional changes,
except for a single
[https://www.sqlite.org/src/info/57eec374ae1d0a1d4a|minor bug-fix] in
[the json1 extension] to stop it from recognizing form-feed
(ASCII 0x0c) as a whitespace character, in conformance with
[http://www.rfc-editor.org/rfc/rfc7159.txt|RFC7159].
}

newsitem {2015-10-14} {Release 3.9.0} {
<p>SQLite version 3.9.0 is a regularly schedule maintenance release.
Key changes include:
<ul>

newsitem {2015-07-27} {Release 3.8.11} {
<p>SQLite version 3.8.11 is a regularly scheduled maintenance release.
   See the [version 3.8.11|change log] for details.
}

newsitem {2015-05-20} {Release 3.8.10.2} {
<p>Yikes!  Index corruption after a sequence of valid SQL statements!
<p>It has been many years since anything like
   [https://www.sqlite.org/src/info/34cd55d6|this bug] has snuck into
   an official SQLite release.  But for the pasts seven months
   ([version 3.8.7] through [version 3.8.10.1])
   if you do an INSERT into a carefully
   crafted schema in which there are two nested triggers that convert
   an index key value from TEXT to INTEGER and then back
   to TEXT again, the INTEGER value might get inserted as the index

}

newsitem {2015-05-07} {Release 3.8.10} {
<p>SQLite version 3.8.10 is a regularly scheduled maintenance release.
   This release features performance improvements, fixes to several
   arcane bugs found by the AFL fuzzer, the new "sqldiff.exe" command-line
   utility, improvements to the documentation, and other enhancements.
   See the <a href='releaselog/3_8_10.html'>release notes</a> for
   additional information.
}

newsitem {2015-04-08} {Release 3.8.9} {
<p>SQLite version 3.8.9 is a regularly scheduled maintenance release.
   New features in this release include the
   [PRAGMA index_xinfo] command, the [sqlite3_status64()] interface,
   and the ".dbinfo" command of the [command-line shell].
   See the <a href='releaselog/3_8_9.html'>release notes</a> for
   additional information.
}

newsitem {2015-02-25} {Release 3.8.8.3} {

   3.8.8 before it came into widespread use.

<p>See ticket
   [https://www.sqlite.org/src/info/f97c4637102a3ae72b7911|f97c4637102a3ae72b7911]
   for a description of the bug.

<p>The changes between versions 3.8.8 and 3.8.8.1 are minimal.
}

newsitem {2015-01-16} {Release 3.8.8} {
<p>SQLite [version 3.8.8] is a regularly schedule maintenance release of
   SQLite.

<p>There are no dramatic new features or performance enhancements in this
   release, merely incremental improvements.  Most of the performance gain

}

newsitem {2014-11-19} {Release 3.8.7.2} {
<p>SQLite [version 3.8.7.2] is a patch and bug-fix release.  Changes from
   the previous release are minimal.

<p>The primary reason for this release is to enhance the [ROLLBACK] command
   so that it allows running queries on the same database connection to
   continue running as long as the ROLLBACK does not change the schema.
   In all previous versions of SQLite, a ROLLBACK would cause pending
   queries to stop immediately and return [SQLITE_ABORT] or
   [SQLITE_ABORT_ROLLBACK]. Pending queries still abort if the ROLLBACK
   changes the database schema, but as of this patch release, the queries
   are allowed to continue running if the schema is unmodified.

<p>In addition to the ROLLBACK enhancement, this patch release also
   includes fixes for three obscure bugs.  See the
   [version 3.8.7.2|change log] for details.
}

newsitem {2014-10-30} {Release 3.8.7.1} {
<p>SQLite [version 3.8.7.1] is a bug-fix release.

<p>The primary reason for this bug-fix release is to address a problem with

   Upgrading from all prior versions is recommended.

<p>Most of the changes from the previous release have been micro-optimizations
   designed to help SQLite run a little faster.  Each individual optimization
   has an unmeasurably small performance impact.  But the improvements add up.
   Measured on a well-defined workload (which the SQLite developers use
   as a proxy for a typical application workload) using cachegrind on Linux
   and compiled with gcc 4.8.1 and -Os on x64 linux, the current release
   does over 20% more work for the same number of CPU cycles compared to the
   previous release.  Cachegrind is not a real CPU, and the workload
   used for measurement is only a proxy.  So your performance may vary.
   We expect to see about half the measured and reported improvement in
   real-world applications. 10% is less than 20% but it is still pretty
   good, we think.

   for further information.  In addition to fixing this bug, the
   [PRAGMA integrity_check] command has been enhanced to detect
   non-uniqueness in UNIQUE indices, so that if this bug did introduce
   any problems in databases, those problems can be easily detected.

<p>Other noteworthy changes include the addition of support for
   [hexadecimal integers] (ex: 0x1234), and performance enhancements
   to the [IN operator] which, according to
   [http://www.mail-archive.com/sqlite-users%40sqlite.org/msg85350.html|mailing list reports],
   help some queries run up to five times faster.

<p>Version 3.8.6 uses 25% fewer CPU cycles than version 3.8.0 from
   approximately one year ago, according to [http://valgrind.org/|valgrind]
   and the
   [http://www.sqlite.org/src/artifact/d29c8048beb7e|test/speedtest1.c]
   test program.
   On the other hand,
   the compiled binary for version 3.8.6 is about 5% larger than 3.8.0.
   The size increase is
   due in part to the addition of new features such as [WITHOUT ROWID]
   tables and [common table expressions].
}

newsitem {2014-06-04} {Release 3.8.5} {
<p>SQLite [version 3.8.5] is a regularly scheduled maintenance release.
   Upgrading from the previous version is recommended.

<p>Version 3.8.5 fixes more than a dozen obscure bugs.  None of these
   bugs should be a problem for existing applications.  Nor do any of
   the bugs represent a security vulnerability.  Nevertheless, upgrading
   is recommended to prevent future problems.

<p>In addition to bug fixes, the 3.8.5 release adds improvements to the
   query planner, especially regarding sorting using indices and handling
   OR terms
   in the WHERE clause for WITHOUT ROWID tables.  The ".system" and
   ".once" dot-commands were added to the command-line interface.  And
   there were enhancements to the FTS4 and RTREE virtual tables.  See
   the change log for details.
}

newsitem {2014-04-03} {Release 3.8.4.3} {
<p>The optimizations added in [version 3.8.4] caused some queries that involve
   subqueries in the FROM clause, DISTINCT, and ORDER BY clauses, to give an incorrect
   result.  See
   [http://www.sqlite.org/src/info/98825a79ce145686392d8074032ae54863aa21a3| ticket 98825a79ce145]
   for details.
   This release adds a
   [http://www.sqlite.org/src/fdiff?sbs=1&v1=7d539cedb1c&v2=ebad891b7494d&smhdr|one-character change]
   to a single line of code to fix the problem.
}

newsitem {2014-03-26} {Release 3.8.4.2} {
<p>The code changes that resulted in the performance improvements
   in [version 3.8.4] missed a single buffer overflow test, which could

decision was made to do a quick patch release to address both issues.
}

newsitem {2014-03-10} {Release 3.8.4} {
<p>SQLite [version 3.8.4] is a maintenance release featuring performance
   enhancements and fixes for a number of obscure bugs.
   There are no significant new features in SQLite version 3.8.4.
   However, the number of CPU cycles (measured by valgrind) needed to
   do many common operations has be reduced by about 12% relative to the
   previous release, and by about 25% relative to [version 3.7.16]
   from approximately one year ago.

<p>Version 3.8.4 of SQLite fixes several corner-case bugs that were
   found since the previous release.  These bugs were unlikely to appear
   in practice, and none represent a security vulnerability.
   Nevertheless, developers are encouraged to upgrade from all prior releases.
}

newsitem {2014-02-11} {Release 3.8.3.1} {
<p>SQLite [version 3.8.3.1] fixes a bug present in versions 3.8.1,
   3.8.2 and 3.8.3 that can cause queries to omit valid output rows.
   Upgrading from those versions is recommended.

<p>The problem only comes up if SQLite is compiled with either the
   [SQLITE_ENABLE_STAT3] or [SQLITE_ENABLE_STAT4] compile-time options.
   In that case, if a query has a WHERE clause that contains expressions
   like this:
   <blockquote>
WHERE (expr1 OR expr2 OR ... OR exprN) AND column IS NOT NULL
   </blockquote>

newsitem {2014-02-03} {Release 3.8.3} {
  <p>SQLite [version 3.8.3] is a regularly scheduled maintenance release.
  Upgrading from the previous release is optional.

  <p>The most visible change in version 3.8.3 is the addition of
  support for [common table expressions].  It is now possible to write a
  single [SELECT] statement that will query a tree or graph, using either
  a depth-first or a breadth-first search.  A single SQLite query will
  even [solve Sudoku puzzles] or [compute the Mandelbrot set].  As part
  of this change, SQLite now accepts a [VALUES clause] anyplace that
  a [SELECT] statement is valid.

  <p>This release also includes many small performance enhancements which
  should give a small speed boost to legacy applications.  And there are
  other minor enhancements such as the addition of the [printf()] SQL
  function.  See the [version 3.8.3|change log] for details.
}

newsitem {2013-12-06} {Release 3.8.2} {
  <p>SQLite [version 3.8.2] is a regularly scheduled maintenance release.
  Upgrading from the previous release is optional.

  <p>Version 3.8.2 adds support for [WITHOUT ROWID] tables.  This is a
  significant extension to SQLite.  Database files that contain WITHOUT ROWID
  tables are not readable or writable by prior versions of SQLite, however
  databases that do not use WITHOUT ROWID tables are fully backwards
  and forwards compatible.

  <p>The 3.8.2 release contains a potentially incompatible change.  In
  all prior versions of SQLite, a [cast] from a very large positive
  floating point number into an integer resulted in the most negative integer.
  In other words, CAST(+99.9e99 to INT) would yield -9223372036854775808.
  This behavior came about because it is what x86/x64 hardware does
  for the equivalent cast in the C language.  But the behavior is
  bizarre.  And so it has been changed effective with this release so that
  a cast from a floating point number into an integer returns the integer
  between the floating point value and zero that is closest to the floating
  point value.  Hence, CAST(+99.9e99 to INT) now returns +9223372036854775807.
  Since routines like [sqlite3_column_int64()] do an implicit cast if the
  value being accessed is really a floating point number, they are also
  affected by this change.

  <p>Besides the two changes mentioned above, the 3.8.2 release also
  includes a number of performance enhancements.  The
  [skip-scan optimization] is now available for databases that have been
  processed by [ANALYZE].  Constant SQL functions are now factored out of
  inner loops, which can result in a significant speedup for queries that
  contain WHERE clause terms like "date>datetime('now','-2 days')".  And
  various high-runner internal routines have been refactored for reduced
  CPU load.
}

newsitem {2013-10-17} {Release 3.8.1} {
  <p>SQLite [version 3.8.1] is a regularly scheduled maintenance release.
  Upgrading from the previous release is optional, though you should upgrade
  if you are using [partial indices] as there was a
  [http://www.sqlite.org/src/info/a5c8ed66ca|bug] related to partial
  indices in the previous release that could result in an incorrect answer
  for count(*) queries.

  <p>The [next generation query planner] that was premiered in the previous
  release continues to work well.
  The new query planner has been tweaked slightly
  in the current release to help it make better decisions in some
  cases, but is largely unchanged.  Two new SQL functions, [likelihood()] and
  [unlikely()], have been added to allow developers to give hints to the
  query planner without forcing the query planner into a particular decision.

  <p>Version 3.8.1 is the first SQLite release to take into account the

  record for a table or index.  Currently, row sizes are only used to help the
  query planner choose between a table or one of its indices when doing a
  table scan or a count(*) operation, though future releases are likely
  to use the estimated row size in other contexts as well.  The new
  [PRAGMA stats] statement can be used to view row size estimates.

  <p>Version 3.8.1 adds the [SQLITE_ENABLE_STAT4] compile-time option.
  STAT4 is very similar to STAT3 in that it uses samples from indices to
  try to guess how many rows of the index will be satisfy by WHERE clause
  constraints.  The difference is that STAT4 samples all columns of the
  index whereas the older STAT3 only sampled the left-most column.  Users
  of STAT3 are encouraged to upgrade to STAT4.  Application developers should
  use STAT3 and STAT4 with caution since both options, by design, violate
  the [query planner stability guarantee], making it more difficult to ensure
  uniform performance is widely-deployed and mass-produced embedded
  applications.
}

newsitem {2013-09-03} {Release 3.8.0.2} {
  <p>SQLite [version 3.8.0.2] contains a one-line fix to a bug in the

  <p>SQLite [version 3.8.0] might easily have been called "3.7.18" instead.
  However, this release features the cutover of the
  [next generation query planner] or [NGQP], and there is a small chance of
  [hazards of upgrading to the NGQP | breaking legacy programs] that
  rely on undefined behavior in previous SQLite releases, and so the
  minor version number was incremented for that reason.
  But the risks are low and there is a [query planner checklist] is
  available to application developers to aid in avoiding problems.

  <p>SQLite [version 3.8.0] is actually one of the most heavily tested
  SQLite releases ever.  Thousands and thousands of beta copies have
  be downloaded, and presumably tested, and there have been no problem
  reports.

  <p>In addition to the [next generation query planner], the 3.8.0 release
  adds support for [partial indices], as well as several other new features.
  See the [version 3.8.0 | change log] for further detail.
}

newsitem {2013-05-20} {Release 3.7.17} {
  SQLite [version 3.7.17] is a regularly schedule maintenance release.
  Visit the [version 3.7.17 | change log] for a full explanation of the
  changes in this release.

  There are many bug fixes in version 3.7.17.  But this does not indicate
  that 3.7.16 was a problematic release.  All of the bugs in 3.7.17 are
  obscure and are unlikely to impact any particular application.  And most
  of the bugs that are fixed in 3.7.17 predate 3.7.16 and have been in
  the code for years without ever before being noticed.
  Nevertheless, due to the large number of fixes,
  all users are encouraged to upgrade when possible.
}

newsitem {2013-04-12} {Release 3.7.16.2} {
  SQLite [version 3.7.16.2] fixes a long-standing flaw in the Windows
  OS interface that
  can result in database corruption under a rare race condition.
  See [http://www.sqlite.org/src/info/7ff3120e4f] for a full description
  of the problem.

  As far as we know, this bug has never been seen in the wild.  The
  problem was discovered by the SQLite developers while writing stress tests
  for a separate component of SQLite.  Those stress tests have not yet
  found any problems with the component they were intended to verify, but
  they did find the bug which is the subject of this patch release.

  Other than updates to version numbers, the only difference between this
  release and 3.7.16.1 is a two-character change in a single identifier,
  which is contained in the windows-specific OS interface logic.  There
  are no changes in this release (other than version numbers) for platforms
  other than Windows.
}

newsitem {2013-03-29} {Release 3.7.16.1} {
  SQLite [version 3.7.16.1] is a bug fix release that fixes a few problems
  that were present in the previous releases.

  The important bug fix is a problem that can lead to segfaults when using
  [shared cache mode] on a schema that contains a [COLLATE operator] within
  a [CHECK constraint] or within a [view].  Collating functions are associated
  with individual database connections.  But a pointer to the collating function
  was also being cached within expressions.  If an expression was part of the
  schema and contained a cached collating function, it would point to the
  collating function in the database connection that originally parsed the
  schema.  If that database connection closed while other database
  connections using the same shared cache continued to operate, they other
  database connections would try to use the deallocated collating function
  in the database connection that closed.  The fix in version 3.7.15 was to
  not cache collating function pointers in the expression structure but
  instead look them up each time a new statement is prepared.

  This release also contains some important enhancements to the query planner
  which should (we hope) make some queries run faster.  The enhancements
  include:

  <ol><li><p>When doing a full-table scan, try to use an index instead of
  the original table, under the theory that indices contain less information
  and are thus smaller and hence require less disk I/O to scan.</p>

  <li><p>Enhance the [IN operator] to allow it to make use of
  indices that have [affinity | numeric affinity].</p>

  <li><p>Do a better job of recognizing when an ORDER BY clause can be
  implemented using indices - especially in cases where the ORDER BY clause
  contains terms from two or more tables in a join.</p>
  </ol>
}

newsitem {2012-10-04} {Release 3.7.14.1} {
  SQLite [version 3.7.14.1] is a patch release.  Changes from the baseline

  terms in the WHERE clause.
}

newsitem {2012-06-11} {Release 3.7.13} {
  SQLite [version 3.7.13] adds support for WinRT and metro style
  applications for Microsoft Windows 8.  The 3.7.13 release is
  coming sooner than is usual after the previous release in order to get
  this new capability into the hands of developers.  To use SQLite in
  a metro style application, compile with the -DSQLITE_OS_WINRT flag.
  Because of the increased application security and safety requirements
  of WinRT, all database
  filenames should be full pathnames.  Note that SQLite is not capable
  of accessing databases outside the installation directory and application
  data directory.  This restriction is another security and safety feature
  of WinRT. Apart from these restrictions, SQLite should work exactly
  the same on WinRT as it does on every other system.

  Also in this release: when a database is opened using [URI filenames]
  and the [coreqp | mode=memory] query parameter
  then the database is an in-memory database, just as if it had
  been named ":memory:".  But, if shared cache mode is enabled, then
  all other database connections that specify the same URI filename
  will connect to the same in-memory database.  This allows two or more
  database connections (in the same process) to share the same in-memory
  database.

  SQLite [version 3.7.12] is a regularly scheduled maintenance release.
  This release contains several new optimizations and bug fixes and upgrading
  is recommended.  See the [version 3.7.12 | change summary] for details.
}

newsitem {2012-03-20} {Version 3.7.11} {
  SQLite [version 3.7.11] is a regularly scheduled maintenance release
  which was rushed out early due to a
  [http://www.sqlite.org/src/info/b7c8682cc1 | bug in the query optimizer]
  introduced in the previous release.  The bug is obscure - it changes
  a LEFT JOIN into an INNER JOIN in some cases when there is a 3-way join
  and OR terms in the WHERE clause.  But it was considered serious enough to
  rush out a fix.  Apart from this one problem, SQLite [version 3.7.10] has
  not given any trouble.  Upgrading to [version 3.7.11] from versions
  3.7.6.3, 3.7.7, 3.7.7.1, 3.7.8, or 3.7.9 is

newsitem {2012-01-16} {Version 3.7.10} {
  SQLite [version 3.7.10] is a regularly scheduled maintenance release.
  Upgrading from version 3.7.6.3, 3.7.7, 3.7.7.1, 3.7.8, or 3.7.9 is
  optional.  Upgrading from other releases is recommended.

  The [SQLITE_CONFIG_PCACHE] mechanism has been replaced with
  [SQLITE_CONFIG_PCACHE2].  If you do not know what this mechanism
  is (it is an extreme corner-case and is seldom used) then this
  change will not effect you in the least.

  The default [schema format number] for new database files has changed
  from 1 to 4.  SQLite has been able to generate and read database files
  using schema format 4 for six years.  But up unto now, the default
  schema format has been 1 so that older versions of SQLite could read
  and write databases generated by newer versions of SQLite.  But those
  older versions of SQLite have become so scarce now that it seems
  reasonable to make the new format the default.

  SQLite is changing some of the assumptions it makes above the behavior
  of disk drives and flash memory devices during a sudden power loss.
  This change is completely transparent to applications.
  Read about the [powersafe overwrite] property for additional information.

  <li> [sqlite3_uri_int64()]
  </ul>

  The [PRAGMA cache_size] statement has been enhanced.  Formerly, you would
  use this statement to tell SQLite how many pages of the database files it
  should hold in its cache at once.  The total memory requirement would
  depend on the database page size. Now, if you give [PRAGMA cache_size]
  a negative value -N, it will allocate roughly N
  [http://en.wikipedia.org/wiki/Kibibyte | kibibytes] of memory to cache,
  divided up according to page size.  This enhancement allows programs to
  more easily control their memory usage.

  There have been several obscure bug fixes.  One noteworthy bug,
  ticket [http://www.sqlite.org/src/info/ff5be73dee | ff5be73dee],
  could in theory result in a corrupt database file if a power loss

  Upgrading from version 3.7.6.3, 3.7.7, 3.7.7.1, and 3.7.8 is optional.
  Upgrading from other versions is recommended.

  The [SQLITE_ENABLE_STAT2] compile-time option is now a no-op.  The enhanced
  query-planner functionality formerly available using SQLITE_ENABLE_STAT2
  is now available through [SQLITE_ENABLE_STAT3].  The enhanced query planning
  is still disabled by default.  However, future releases of SQLite might
  convert STAT3 from an enable-option to a disable-option so that it is
  available by default and is only omitted upon request.

  The [FTS4] full-text search engine has been enhanced such that tokens in
  the search string that begin with "^" must be the first token in their
  respective columns in order to match.   Formerly, "^" characters in the
  search string were simply ignored.  Hence, if a legacy application was
  including "^" characters in FTS4 search strings, thinking that they would
  always be ignored, then those legacy applications might break with this
  update.  The fix is simply remove the "^" characters from the search
  string.

  See the [version 3.7.9 | change summary] for additional changes associated
  with this release.
}

newsitem {2011-September-19} {Version 3.7.8} {
  SQLite [version 3.7.8] is a quarterly maintenance release.  Upgrading from
  versions 3.7.6.3, 3.7.7, or 3.7.7.1 is optional.  Upgrading from other
  versions is recommended.

  This release features a new "external merge sort" algorithm used to
  implement ORDER BY and GROUP BY and also to presort the content of an
  index for CREATE INDEX.  The new algorithm does approximately the same
  number of comparisons and I/Os as before, but the I/Os are much more
  sequential and so runtimes are greatly reduced when the size of the
  set being sorted is larger than the filesystem cache.  The performance
  improvement can be dramatic - orders of magnitude faster
  for large CREATE INDEX commands.  On the other hand,
  the code is slightly slower (1% or 2%)
  for a small CREATE INDEX.  Since CREATE INDEX is not an
  operation that commonly occurs on a speed-critical path, we feel that
  this tradeoff is a good one.  The slight slowdown for small CREATE INDEX
  statements might be recovered in a future release.  ORDER BY and GROUP BY
  operations should now be faster for all cases, large and small.

  The query planner has been enhanced to do a better job of handling
  the DISTINCT keyword on SELECT statements.

  SQLite [version 3.7.7] is a regularly scheduled bi-monthly maintenance
  release.  Upgrading from version 3.7.6.3 is optional.  Upgrading from all
  prior releases is recommended.

  This release adds support for naming database files using [URI filenames].
  URI filenames are disabled by default (for backwards compatibility) but
  applications are encouraged to enable them since incompatibilities are
  likely to be exceedingly rare and the feature is useful.  See the
  [URI filenames | URI filename documentation] for details.

  Most of the other enhancements in this release involve
  [virtual tables].  The virtual table interface has been enhanced to
  support [SAVEPOINT] and [ON CONFLICT] clause processing, and the built-in
  [RTREE] and [FTS3 | FTS3/FTS4] have been augmented to take advantage of
  the new capability.  This means, for example, that it is now possible
  to use the [REPLACE] command on [FTS3 | FTS3/FTS4] and [RTREE] tables.

  The [FTS4] full-text index extension has been enhanced to support
  the [FTS4 prefix option] and the [FTS4 order option].  These two enhancements
  are provided in support of search-as-you-type interfaces where search
  results begin to appear after the first keystroke in the "search" box

  There has been a fair amount of work done on the FTS4 module for this
  release.  But the core SQLite code has changed little and the previous
  release has not given any problems, so we expect this to be a very
  stable release.
}

newsitem {2011-05-19} {Version 3.7.6.3} {
  SQLite [version 3.7.6.3] is a patch release that fixes a
  [http://www.sqlite.org/src/info/2d1a5c67df | single bug]
  associated with [WAL mode].  The bug has been in SQLite ever since WAL
  was added, but the problem is very obscure and so nobody has noticed
  before now.  Nevertheless, all users are encouraged to upgrade to
  version 3.7.6.3 or later.

  The bug is this:

  Transactions commit in WAL mode by adding a record onto the end of
  the WAL (the write-ahead log) that contains a "commit" flag.  So to
  commit a transaction, SQLite takes all the pages that have changed
  during that transaction, appends them to the WAL, and sets the commit
  flag on the last page.  Now, if SQLite comes under memory pressure, it
  might try to free up memory space by writing changed pages to the WAL
  prior to the commit.  We call this "spilling" the cache to WAL.  There
  is nothing wrong with spilling cache to WAL.  But if the
  memory pressure is severe, it might be that by the time [COMMIT] is run,
  all changed pages for the transaction have already been spilled to WAL
  and there are no pages left to be written to WAL.
  And with no unwritten pages, there was nothing to put the commit flag
  on.  And without a commit flag, the transaction would end up being
  rolled back.

newsitem {2011-04-13} {Version 3.7.6.1} {
  SQLite [version 3.7.6.1] fixes a single bug in 3.7.6 that can cause a
  segfault if [SQLITE_FCNTL_SIZE_HINT] is used on a unix build that has
  SQLITE_ENABLE_LOCKING_MODE set to 0 and is compiled with
  HAVE_POSIX_FALLOCATE.

  Upgrading from 3.7.6 is only needed for users effected by the
  configuration-specific bug described above.  There are no other changes
  to the code.
}

newsitem {2011-04-12} {Version 3.7.6} {
  SQLite [version 3.7.6] is a regularly scheduled bi-monthly maintenance
  release of SQLite.  Upgrading from version 3.7.5 is optional.  Upgrading
  releases prior to 3.7.5 is recommended.
}

newsitem {2011-02-01} {Version 3.7.5} {
  SQLite [version 3.7.5] is a regularly scheduled bi-monthly maintenance
  release of SQLite.  Due to the discovery and fix of
  [http://www.sqlite.org/src/tktview?name=5d863f876e | an obscure bug]
  that could cause database corruption, upgrading from all prior
  releases of SQLite is recommended.  This bug was found during code
  review and has not been observed in the wild.

  This release adds new [SQLITE_DBSTATUS_LOOKASIDE_HIT | opcodes] for the
  [sqlite3_db_status()] interface that allow more precise measurement of

}

newsitem {2010-12-08} {Version 3.7.4} {
  SQLite [version 3.7.4] is a regularly scheduled bi-monthly maintenance
  release of SQLite.  Upgrading from [version 3.7.2] and [version 3.7.3]
  is optional.  Upgrading from all other SQLite releases is recommended.

  This release features [full-text search] enhancements.  The older
  [FTS3] virtual table is still fully supported, and should also run
  faster.  In addition, the new [FTS4] virtual table is added.  FTS4
  follows the same syntax as FTS3 but holds additional metadata which
  facilitates some performance improvements and more advanced
  [matchinfo()] output.  Look for further full-text search enhancements
  in subsequent releases.

  Also in this release, the [EXPLAIN QUERY PLAN] output has been enhanced
  and new documentation is provided so that application developers can
  more easily understand how SQLite is performing their queries.

  Thanks to an account from the folks at [http://www.devio.us/], OpenBSD
  has been added to the list of platforms upon which we
  [tested | test SQLite] prior to every release.  That list of platforms
  now includes:

  <ul>
  <li> Linux x86 &amp; x86_64
  <li> MacOS 10.5 &amp; 10.6
  <li> MacOS 10.2 PowerPC
  <li> WinXP and Win7
  <li> Android 2.2
  <li> OpenBSD 4.7
  </ul>

  The previous release of SQLite ([version 3.7.3]) has proven to be very
  robust.  The only serious issue discovered was
  [http://www.sqlite.org/src/info/80ba201079 | ticket 80ba201079] that
  describes an incorrect query result that can occur under very
  unusual circumstances.  The ticket description contains details of the
  problem.  Suffice it to say here that the problem is very obscure and
  is unlikely to effect most applications and so upgrading is optional.
  The problem is fixed, of course, in this release.
}

newsitem {2010-October-08}  {Version 3.7.3} {
  SQLite [version 3.7.3] is a regularly scheduled bi-monthly maintenance
  release of SQLite.  Upgrading from [version 3.7.2] is optional.
  Upgrading from all other releases is recommended.

  This release adds two new interfaces (really just variations on existing
  interfaces).  The [sqlite3_create_function_v2()] interface adds a
  destructor for the application-data pointer.  The new
  [sqlite3_soft_heap_limit64()] interface allows the soft heap limit to
  be set to a value greater than 2<sup>31</sup>.

  The [RTREE] extension has been enhanced with the ability to have an
  [custom r-tree queries | application-defined query region].  This might
  be used, for example, to locate all objects within
  the field of view of a camera.

  The 3.7.3 release also includes some performance enhancements, including
  query planner improvements, documentation updates,
  and fixes to some very obscure bugs.
}

newsitem {2010-August-24} {Version 3.7.2} {
  SQLite [version 3.7.2] fixes a long-standing bug that can cause the
  database [free-page list] to go corrupt if [incremental_vacuum] is used
  multiple times to
  partially reduce the size of a database file that contains many hundreds
  of unused database pages.  The original bug reports together with links
  to the patch that fixes it can be seen
  <a href="http://www.sqlite.org/src/info/5e10420e8d">here</a>.

  This bug has been in the code for at least a year and possibly longer.
  The bug has nothing to do with the versions 3.7.1 or 3.7.0 or any other
  recent release.  The fact that the bug was discovered (and fixed)
  within hours of the 3.7.1 release is purely a coincidence.

  The bug is impossible to hit without using [incremental_vacuum] and is
  very difficult to hit even with incremental_vacuum.  And the kind of
  corruption that the bug causes can usually be fixed
  simply by running [VACUUM].  Nevertheless, because the bug can result
  in database corruption, it is recommended that all SQLite users upgrade
  to version 3.7.2 or later.
}

newsitem {2010-August-23} {Version 3.7.1} {
  SQLite [version 3.7.1] is a stabilization release for the 3.7.x series.
  Other than the filesize-in-header bug that was fixed in [version 3.7.0.1],
  no major problems have been seen in 3.7.0.  Some minor corner-case
  performance regressions have been fixed.  A typo in the OS/2 interface
  has been repaired.

  A biggest part of the 3.7.1 release is a cleanup and refactoring of
  the pager module within SQLite.  This refactoring should have no
  application-visible effects.  The purpose was to reorganize the code
  in ways that make it easier to prove correctness.

  The 3.7.1 release adds new experimental methods for obtained more
  detailed memory usage information and for controlling database file
  fragmentation.  And the query planner now does a better job of
  optimizing the [LIKE] and [GLOB] operators.

  This release increases the maximum size of database pages from 32KiB to
  64KiB.  A database with 64KiB pages will not be readable or writable by
  older versions of SQLite.  Note that further increases in page size

  SQLite version 3.7.0 also contains some query planner enhancements and
  a few obscure bug fixes, but the only really big change is the addition
  of WAL mode.
}

newsitem {2010-03-30} {Version 3.6.23.1} {
  SQLite [version 3.6.23.1] is a patch release to fix a bug in the
  offsets() function of [FTS3] at the request of the Mozilla.
}

newsitem {2010-03-09} {Version 3.6.23} {
  SQLite [version 3.6.23] is a regular bimonthly release of SQLite.
  Upgrading from the prior release is purely optional.

  This release contains new pragmas: the [secure_delete pragma], and

  removed.  SQLite has supported standard SQL [foreign key constraints]
  since [version 3.6.19] and so the ".genfkey" command was seen as
  an anachronism.
}

newsitem {2010-01-06} {Version 3.6.22} {
  SQLite [version 3.6.22] is a bug-fix release.  Two bugs have been fixed
  that might cause incorrect query results.
  <ul>
  <li>Ticket [http://www.sqlite.org/src/info/31338dca7e | 31338dca7e]
  describes a
  problem with queries that have a WHERE clause of the form (x AND y) OR z
  where x and z come from one table of a join and y comes from a different
  table.
  <li> Ticket [http://www.sqlite.org/src/info/eb5548a849 | eb5548a849]

newsitem {2009-12-07} {Version 3.6.21} {
  SQLite [version 3.6.21] focuses on performance optimization.  For
  a certain set of traces, this version uses 12% fewer CPU instructions
  than the previous release (as measured by Valgrind).  In addition, the
  [FTS3] extension has been through an extensive cleanup and rework and
  the [sqlite3_trace()] interface has been modified to insert
  [bound parameter] values into its output.
}

newsitem {2009-11-04} {Version 3.6.20} {
  SQLite [version 3.6.20] is a general maintenance release.  The
  query planner has been enhanced to work better with bound parameters
  in LIKE and GLOB operators and in range constraints and various minor

  trigger behavior will need to use the [recursive_triggers pragma]
  to disable recursive triggers.

  This version of SQLite also contains bug fixes, though none of the
  bugs are serious and all are obscure, so upgrading is optional.

  The SQLite core continues to have [test coverage | 100% branch test coverage]
  and so despite the many changes in this release, the developers
  believe that this version of SQLite is stable and ready for production
  use.
}

newsitem {2009-08-10} {Version 3.6.17} {
  This is a monthly maintenance release with a focus of bug fixes,
  performance improvements, and increased test coverage.  This is the
  first release of SQLite since [test coverage | 100% branch test coverage]
  was achieved on the SQLite core.

  In addition, a new interface [sqlite3_strnicmp()] is provided for the
  convenience of extension writers.

  None of the bugs fixed in this release are serious.  All bugs are
  obscure.  Upgrading is optional.
}

newsitem {2009-07-25} {100% Branch Test Coverage} {
  A subset of the [TH3] test suite was measured by gcov to provide
  [test coverage | 100% branch test coverage] over the SQLite core
  (exclusive of the VFS backend and of extensions such as FTS3 and RTREE)
  when compiled for SuSE 10.1 Linux on x86.  The SQLite developers pledge
  to maintain branch test coverage at 100% in all future releases.
  Ongoing work will strive for 100% branch test coverage on the
  operating-system backends and extensions as well.
}

newsitem {2009-06-27} {Version 3.6.16} {
  SQLite [version 3.6.16] is another general maintenance release containing
  performance and robustness enhancements.  A single notable bug was fixed
  (ticket #3929).  This bug can cause INSERT or UPDATE statements to fail
  on indexed tables that have AFTER triggers that modify the same table and
  index.
}

newsitem {2009-06-15} {Version 3.6.15} {
  SQLite [version 3.6.15] is a general maintenance release containing
  performance and robustness enhancements and fixes for various obscure
  bugs.
}

newsitem {2009-05-25} {Version 3.6.14.2} {
  SQLite [version 3.6.14.2] fixes an obscure bug in the code generator
  (ticket #3879)
  section of SQLite which can potentially cause incorrect query results.
  The changes from the prior release consist of only this one bug fix,
  check-in [6676]
  and a change to the version number text.

  The bug was introduced in version 3.6.14.  It is recommended that
  users of version 3.6.14 and 3.6.14.1 upgrade to this release.  Applications
  are unlikely to hit this bug, but since it is difficult to predict which
  applications might hit it and which might not, we recommend that all
  users of 3.6.14 and 3.5.14.1 upgrade to this release.
}

newsitem {2009-05-19} {Version 3.6.14.1} {
  SQLite [version 3.6.14.1] is a patch release to [version 3.6.14] with
  minimal changes that fixes three bugs.  Upgrading is only necessary
  for users who are impacted by one or more of those bugs.
}

newsitem {2009-05-07} {Version 3.6.14} {
  SQLite [version 3.6.14] provides new performance enhancements in
  the btree and pager layers and in the query optimizer.  Certain
  workloads can be as much as twice as fast as the previous release,

  can occur following a disk I/O error.
}

newsitem {2008-11-22} {Version 3.6.6.1} {
  This release fixes a bug that was introduced into SQLite [version 3.6.4]
  and that can cause database corruption in obscure cases.  This bug has
  never been seen in the wild; it was first detected by internal stress
  tests and required substantial analysis before it could be shown to
  potentially lead to corruption.  So we feel that SQLite versions 3.6.4,
  3.6.5, and 3.6.6 are safe to use for development work.  But upgrading
  to this patch release or later is recommended prior to deploying
  products that incorporate SQLite.

  We have taken the unusual step of issuing a patch release in order to
  get the fix for this bug into circulation quickly.  SQLite version 3.6.7
  will continue on its normal path of development with an anticipated
  release in mid December.
}


newsitem {2008-11-19} {Version 3.6.6} {
  SQLite [version 3.6.5] is released.  This is a quick turn-around release
  that fixes a bug in virtual tables and FTS3 that snuck into
  [version 3.6.5].  This release also adds the new
  application-defined page cache mechanism.
}

newsitem {2008-11-12} {Version 3.6.5} {
  SQLite [version 3.6.5] is released.  There are various minor feature
  enhancements and numerous obscure bug fixes.
  The [version 3.6.5|change log] contains the details.  Upgrading is
  optional.
}

newsitem {2008-11-01} {Bloomberg Joins SQLite Consortium} {
  The SQLite developers are honored to announce that
  [http://www.bloomberg.com/ | Bloomberg] has joined the

  recommended for all applications that make use of DISTINCT.
}

newsitem {2008-08-30} {Version 3.6.2} {
  SQLite version 3.6.2 contains rewrites of the page-cache subsystem and
  the procedures for matching identifiers to table columns in SQL statements.
  These changes are designed to better modularize the code and make it more
  maintainable and reliable moving forward.  Nearly 5000 non-comment lines
  of core code (about 11.3%) have changed
  from the previous release.  Nevertheless, there should be no
  application-visible changes, other than bug fixes.
}

newsitem {2008-08-06} {Version 3.6.1} {
  SQLite version 3.6.1 is a stabilization and performance enhancement
  release.
}

newsitem {2008-07-16} {Version 3.6.0 beta} {
  Version 3.6.0 makes changes to the [sqlite3_vfs | VFS] object in order
  to make SQLite more easily portable to a wider variety of platforms.
  There are potential incompatibilities with some legacy applications.
  See the [35to36 | 35to36.html] document for details.

  Version 3.5.9 is intended to be the last stable release prior to
  version 3.6.0.  Version 3.6.0 will make incompatible changes to the
  [sqlite3_vfs] VFS layer in order to address deficiencies in the original
  design.  These incompatibilities will only effect programmers who
  write their own custom VFS layers (typically embedded device builders).
  The planned VFS changes will be much smaller
  than the changes that occurred on the
  [34to35 | 3.4.2 to 3.5.0 transaction] that occurred last
  September.

  This release of SQLite is considered stable and ready for production use.
}

newsitem {2008-04-16} {Version 3.5.8} {

  an application-defined SQL function implementation to retrieve
  its [database connection] handle.

  This release of SQLite is considered stable and ready for production use.
}

newsitem {2008-03-17} {Version 3.5.7} {
  Version 3.5.7 fixes several minor and obscure bugs, especially
  in the autoconf-generated makefile.  Upgrading is optional.
  This release of SQLite is considered stable and ready for production use.
}

newsitem {2008-02-06} {Version 3.5.6} {
  Version 3.5.6 fixes a minor regression in 3.5.5 - a regression that
  had nothing to do with the massive change of the virtual machine

newsitem {2007-12-12} {SQLite Consortium Announced} {
  The <a href="consortium.html">SQLite Consortium</a> was launched
  today with [http://www.mozilla.org/ | Mozilla] and
  [http://www.symbian.com/ | Symbian] as charter members.
  As noted in the <a href="pressrelease-20071212.html">press release</a>,
  the Consortium's goal is to promote the continuing vitality and
  independence of SQLite.
}
newsitem {2007-11-27} {Version 3.5.3} {
  This is an incremental release that fixes several minor problems.
  Upgrading is optional.  If Version 3.5.2 or 3.5.1 is working fine
  for you, then there is no pressing need to change to 3.5.3.

  The prebuilt binaries and the amalgamation found on the
  <a href="download.html">download</a> page include the FTS3 fulltext
  search extension module.  We are doing this on an experimental
  basis and are not promising to provide prebuilt binaries with
  FTS3 in the future.
}

newsitem {2007-11-05} {Version 3.5.2} {
  This is an incremental release that fixes several minor problems,
  adds some obscure features, and provides some performance tweaks.
  Upgrading is optional.

  The experimental compile-time option
  [omitfeatures | SQLITE_OMIT_MEMORY_ALLOCATION] is no longer supported.  On the other
  hand, it is now possible to compile SQLite so that it uses a static
  array for all its dynamic memory allocation needs and never calls
  malloc.  Expect to see additional radical changes to the memory
  allocation subsystem in future releases.
}

newsitem {2007-10-04} {Version 3.5.1} {
  Fix a long-standing bug that might cause database corruption if a
  disk-full error occurs in the middle of a transaction and that
  transaction is not rolled back.
  Ticket #2686.

  The new VFS layer is stable.  However, we still reserve the right to
  make tweaks to the interface definition of the VFS if necessary.
}

newsitem {2007-09-04} {Version 3.5.0 alpha} {
  The OS interface layer and the memory allocation subsystems in
  SQLite have been reimplemented.  The published API is largely unchanged
  but the (unpublished) OS interface has been modified extensively.
  Applications that implement their own OS interface will require
  modification.  See
  <a href="34to35.html">34to35.html</a> for details.<p>

  This is a large change.  Approximately 10% of the source code was
  modified.  We are calling this first release "alpha" in order to give
  the user community time to test and evaluate the changes before we
  freeze the new design.
}

newsitem {2007-08-13} {Version 3.4.2} {
  While stress-testing the
  <a href="c3ref/soft_heap_limit.html">soft_heap_limit</a>
  feature, a bug that could lead to
  database corruption was discovered and fixed.
  Though the consequences of this bug are severe, the chances of hitting
  it in a typical application are remote.  Upgrading is recommended
  only if you use the
  <a href="c3ref/soft_heap_limit.html">sqlite3_soft_heap_limit</a>
  interface.
}

newsitem {2007-07-20} {Version 3.4.1} {
  This release fixes a bug in <a href="lang_vacuum.html">VACUUM</a> that
  can lead to database corruption.  The bug was introduced in version
  <a href="changes.html#version_3_3_14">3.3.14</a>.
  Upgrading is recommended for all users.  Also included are a slew of
  other more routine
  <a href="changes.html#version_3_4_1">enhancements and bug fixes</a>.
}

newsitem {2007-06-18} {Version 3.4.0} {
  This release fixes two separate bugs either of which
  can lead to database corruption.  Upgrading
  is strongly recommended.  If you must continue using an older version
  of SQLite, please at least read about how to avoid these bugs
  at CorruptionFollowingBusyError and ticket #2418
  <p>
  This release also adds explicit <a href="limits.html">limits</a> on the
  sizes and quantities of things SQLite will handle.  The new limits might
  causes compatibility problems for existing applications that
  use excessively large strings, BLOBs, tables, or SQL statements.
  The new limits can be increased at compile-time to work around any problems
  that arise.  Nevertheless, the version number of this release is
  3.4.0 instead of 3.3.18 in order to call attention to the possible
  incompatibility.
  </p>
  There are also new features, including
  <a href="c3ref/blob_open.html">incremental BLOB I/O</a> and
  <a href="pragma.html#pragma_incremental_vacuum">incremental vacuum</a>.
  See the <a href="changes.html#version_3_4_0">change log</a>
  for additional information.
}

newsitem {2007-04-25} {Version 3.3.17} {
  This version fixes a bug in the forwards-compatibility logic of SQLite
  that was causing a database to become unreadable when it should have
  been read-only.  Upgrade from 3.3.16 only if you plan to deploy into
  a product that might need to be upgraded in the future.  For day to day
  use, it probably does not matter.
}

newsitem {2007-04-18} {Version 3.3.16} {
  Performance improvements added in 3.3.14 but mistakenly turned off
  in 3.3.15 have been reinstated.  A bug has been fixed that prevented
  VACUUM from running if a NULL value was in a UNIQUE column.
}

newsitem {2007-04-09} {Version 3.3.15} {
  An annoying bug introduced in 3.3.14 has been fixed.  There are
  also many enhancements to the test suite.
}

newsitem {2007-04-02} {Version 3.3.14} {
  This version focuses on performance improvements.  If you recompile
  the amalgamation using GCC option -O3 (the precompiled binaries
  use -O2) you may see performance
  improvements of 35% or more over version 3.3.13 depending on your
  workload.  This version also
  adds support for <a href="pragma.html#pragma_locking_mode">
  exclusive access mode</a>.
}

newsitem {2007-02-13} {Version 3.3.13} {
  This version fixes a subtle bug in the ORDER BY optimizer that can
  occur when using joins.  There are also a few minor enhancements.
  Upgrading is recommended.
}

newsitem {2007-01-27} {Version 3.3.12} {
  The first published build of the previous version used the wrong
  set of source files.  Consequently, many people downloaded a build

  <a href="c3ref/prepare.html">sqlite3_prepare_v2()</a>
  API and includes important bug fixes in the command-line
  shell and enhancements to the query optimizer.  Upgrading is
  recommended.
}

newsitem {2006-10-09} {Version 3.3.8} {
  Version 3.3.8 adds support for full-text search using the
  FTS1 module.  There are also minor bug fixes.  Upgrade only if
  you want to try out the new full-text search capabilities or if
  you are having problems with 3.3.7.
}

newsitem {2006-08-12} {Version 3.3.7} {
  Version 3.3.7 includes support for loadable extensions and virtual
  tables.  But both features are still considered "beta" and their
  APIs are subject to change in a future release.  This release is
  mostly to make available the minor bug fixes that have accumulated
  since 3.3.6.  Upgrading is not necessary.  Do so only if you encounter
  one of the obscure bugs that have been fixed or if you want to try
  out the new features.
}

newsitem {2006-06-19} {New Book About SQLite} {
  <i>[https://link.springer.com/book/10.1007/978-1-4302-3226-1 | The Definitive Guide to SQLite]</i>,
  a 2nd edition book by Mike Owens and Grant Allen,

  is now available from [http://www.apress.com | Apress].
  The books covers the latest SQLite internals as well as
  the native C interface and bindings for PHP, Python,
  Perl, Ruby, Tcl, and Java.  Recommended.
}


newsitem {2006-06-6} {Version 3.3.6} {
  Changes include improved tolerance for Windows virus scanners
  and faster :memory: databases.  There are also fixes for several
  obscure bugs.  Upgrade if you are having problems.
}

newsitem {2006-04-5} {Version 3.3.5} {
  This release fixes many minor bugs and documentation typos and
  provides some minor new features and performance enhancements.
  Upgrade only if you are having problems or need one of the new features.
}

newsitem {2006-02-11} {Version 3.3.4} {
  This release fixes several bugs, including a
  blunder that might cause a deadlock on multithreaded systems.
  Anyone using SQLite in a multithreaded environment should probably upgrade.
}

newsitem {2006-01-31} {Version 3.3.3 stable} {
  There have been no major problems discovered in version 3.3.2, so
  we hereby declare the new APIs and language features to be stable

  of the default "PRAGMA synchronous=full".  There was an attempt to add
  this capability in 3.1.4 but it did not work due to a spelling error.
}

newsitem {2005-02-19} {Version 3.1.3 Released} {
  Version 3.1.3 cleans up some minor issues discovered in version 3.1.2.
}

newsitem {2005-02-15} {Versions 2.8.16 and 3.1.2 Released} {
  A critical bug in the VACUUM command that can lead to database
  corruption has been fixed in both the 2.x branch and the main
  3.x line.  This bug has existed in all prior versions of SQLite.
  Even though it is unlikely you will ever encounter this bug,
  it is suggested that all users upgrade.  See
  ticket #1116 for additional information.

  Version 3.1.2 is also the first stable release of the 3.1
  series.  SQLite 3.1 features added support for correlated
  subqueries, autovacuum, autoincrement, ALTER TABLE, and
  other enhancements.  See the
  <a href="releaselog/3_1_0.html">release notes
  for version 3.1.0</a> for a detailed description of the
  changes available in the 3.1 series.
}

newsitem {2005-02-01} {Version 3.1.1 (beta) Released} {
  Version 3.1.1 (beta) is now available on the
  website.  Version 3.1.1 is fully backwards compatible with the 3.0 series
  and features many new features including Autovacuum and correlated
  subqueries.  The
  <a href="releaselog/3_1_1.html">release notes</a>
  From version 3.1.0 apply equally to this release beta.  A stable release
  is expected within a couple of weeks.
}

newsitem {2005-01-21} {Version 3.1.0 (alpha) Released} {
  Version 3.1.0 (alpha) is now available on the
  website.  Version 3.1.0 is fully backwards compatible with the 3.0 series
  and features many new features including Autovacuum and correlated
  subqueries.  See the
  <a href="releaselog/3_1_0.html">release notes</a>
  for details.

  This is an alpha release.  A beta release is expected in about a week
  with the first stable release to follow after two more weeks.
}

newsitem {2004-11-09} {SQLite at the 2004 International PHP Conference} {
  There was a talk on the architecture of SQLite and how to optimize
  SQLite queries at the 2004 International PHP Conference in Frankfurt,
  Germany.<br>
  Obsolete URL: http://www.sqlite.org/php2004/page-001.html <br>
  Slides from that talk are available.
}

newsitem {2004-10-11} {Version 3.0.8} {
  Version 3.0.8 of SQLite contains several code optimizations and minor
  bug fixes and adds support for DEFERRED, IMMEDIATE, and EXCLUSIVE
  transactions.  This is an incremental release.  There is no reason
  to upgrade from version 3.0.7 if that version is working for you.
}


newsitem {2004-10-10} {SQLite at the 11<sup><small>th</small></sup>
Annual Tcl/Tk Conference} {
  There will be a talk on the use of SQLite in Tcl/Tk at the
  11<sup><small>th</small></sup> Tcl/Tk Conference this week in
  New Orleans.  Visit [http://www.tcl-lang.org/community/tcl2004/]
  for details.<br>
  Obsolete URL: http://www.sqlite.org/tclconf2004/page-001.html <br>
  Slides from the talk are available.
}

newsitem {2004-09-18} {Version 3.0.7} {
  Version 3.0 has now been in use by multiple projects for several
  months with no major difficulties.   We consider it stable and
  ready for production use.
}

newsitem {2004-09-02} {Version 3.0.6 (beta)} {
  Because of some important changes to sqlite3_step(),
  we have decided to
  do an additional beta release prior to the first "stable" release.
  If no serious problems are discovered in this version, we will

newsitem {2004-07-22} {Version 3.0.3 (beta)} {
  The second beta release of SQLite version 3.0 is now available.
  This new beta fixes many bugs and adds support for databases with
  varying page sizes.  The next 3.0 release will probably be called
  a final or stable release.

  Version 3.0 adds support for internationalization and a new
  more compact file format.
  <a href="version3.html">Details.</a>
  The API and file format have been fixed since 3.0.2.  All
  regression tests pass (over 100000 tests) and the test suite
  exercises over 95% of the code.

  SQLite version 3.0 is made possible in part by AOL
  developers supporting and embracing great Open-Source Software.
}

newsitem {2004-07-22} {Version 2.8.15} {
  SQLite version 2.8.15 is a maintenance release for the version 2.8
  series.  Version 2.8 continues to be maintained with bug fixes, but
  no new features will be added to version 2.8.  All the changes in
  this release are minor.  If you are not having problems, there is
  there is no reason to upgrade.
}

newsitem {2004-06-30} {Version 3.0.2 (beta) Released} {
  The first beta release of SQLite version 3.0 is now available.
  Version 3.0 adds support for internationalization and a new
  more compact file format.
  <a href="version3.html">Details.</a>
  As of this release, the API and file format are frozen.  All
  regression tests pass (over 100000 tests) and the test suite
  exercises over 95% of the code.

  SQLite version 3.0 is made possible in part by AOL
  developers supporting and embracing great Open-Source Software.
}


newsitem {2004-06-25} {Website hacked} {
  The www.sqlite.org website was hacked sometime around 2004-06-22
  because the lead SQLite developer failed to properly patch CVS.
  Evidence suggests that the attacker was unable to elevate privileges
  above user "cvs".  Nevertheless, as a precaution the entire website
  has been reconstructed from scratch on a fresh machine.  All services

  A new file format results in databases that are 25% smaller (depending
  on content).  The code is also a little faster.  In spite of the many
  new features, the library footprint is still less than 240KB
  (x86, gcc -O1).
  <a href="version3.html">Additional information</a>.

  Our intent is to freeze the file format and API on 2004-07-01.
  Users are encouraged to review and evaluate this alpha release carefully
  and submit any feedback prior to that date.

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

newsitem {2004-06-09} {Version 2.8.14 Released} {

</tcl>

<h2>Licenses
<font size=2>(<a href="#ext">details below</a>)</font></h2>

<tcl>
support_entry 5 {SQLite License} {
  Warranty of title and perpetual right-to-use for the SQLite source code.
} {$6000 one time fee} \
  {{More Info} https://sqlite.org/copyright.html} \
  {{Purchase} https://sqlite.org/purchase/license}

support_entry 6 {SQLite Encryption Extension (SEE)} {
   A drop-in replacement for public-domain SQLite source code that has
   the added ability to read/write AES-encrypted databases.
} {$2000 one time fee} \
  {{More Info} https://sqlite.org/see/doc/release/www/readme.wiki} \
  {{Purchase} https://sqlite.org/purchase/see}

support_entry 7 {Compressed And Encrypted Read-Only Database (CEROD)} {
   A drop-in replacement for public-domain SQLite source code
   that has the added ability to read database that has been both
   compressed and encrypted.
} {$2000 one time fee} \
  {{More Info} https://sqlite.org/cerod/doc/release/www/readme.wiki} \
  {{Buy Now!} https://sqlite.org/purchase/cerod}

support_entry 8 {The ZIPVFS Read/Write Compressed Database Extension} {
   The ZIPVFS extension automatically compresses and encrypts
   your data as it is written to disk, and decompressed and decrypts

<h2>Special Services
<font size=2>(<a href="#th3">details below</a>)</font></h2>

<tcl>
support_entry 9 {TH3 Testing Support} {
  The <a href="https://sqlite.org/th3.html">TH3 test harness</a>
  is an aviation-grade test suite for SQLite. SQLite developers
  can run TH3 on specialized hardware and/or using specialized
  compile-time options, according to customer specification,
  either remotely or on customer premises. Pricing for this
  services is on a case-by-case basis depending on requirements.
} {call} \
  {{More Info} https://sqlite.org/th3.html} \
  {{Request A Quote} https://sqlite.org/com/contact.html}

generate_support_html
</tcl>

<a href="https://sqlite.org/forum/forummain">SQLite Forum</a>.
The forum is monitored by a large
community of experts, including the core SQLite development team,
who are able to resolve just about
any problems with SQLite that you are likely to have.</p>

<p>If you do not want to discuss your issues on a public and open forum, an
<a href="https://sqlite.org/com/ams.html">Annual Maintenance
Subscription</a> or "AMS" might
serve your needs better.
An AMS entitles you to private email support directly from the developers of
SQLite.  The level of support with an AMS is similar to the support one
gets from the public forum except that an AMS includes a unilateral
promise of non-disclosure, so with an AMS you can discuss your issues
with the SQLite developers in private, without the whole world watching.
An AMS costs $1500 per year.</p>

<p>Users with more advanced support needs can opt for a
<a href="https://sqlite.org/com/tech.html">Technical Support
Agreement</a>.
Technical support agreements are customized to the needs of each
individual client, but generally include direct telephone support
and priority handling of issues and bugs.  Guaranteed response time
is available as an option.  The cost of
technical support varies but is generally
in the range of $8000 to $35000 per year.</p>

<p>If SQLite is "mission critical" to your company, then you may
want to become an
<a href="https://sqlite.org/com/member.html">SQLite Consortium
Member</a>.  The SQLite
Consortium is a collaboration of companies who sponsor ongoing development
of SQLite in exchange for enterprise-level technical support, on-site
visits from the SQLite developers, unlimited access to all licensed
products, and strong guarantees that SQLite will remain in the public
domain, free and independent, and will not come under the control of
a competitor.</p>

<a name="ext"></a>
<h2>Software Licenses</h2>

<p>The SQLite source code is in the
<a href="https://sqlite.org/copyright.html">public domain</a>,
and is free for use
by anyone and for any purpose.  No license is required.  However, some
users desire a license so that they can have warranty of title, or just
because their company lawyers say they need one.  A
<a href="https://sqlite.org/purchase/license">perpetual license
and warranty of title</a>
for the core SQLite source code is available for this purpose.</p>

<p>The
<a href="https://sqlite.org/purchase/see">SQLite Encryption
Extension (SEE)</a>,
the <a href="https://sqlite.org/purchase/zipvfs">ZIPVFS Extension</a>,
and the <a href="https://sqlite.org/purchase/cerod">Compressed and
Encrypted ReadOnly Database (CEROD)</a> extension are enhanced versions
of SQLite that handle encrypted
and/or compressed databases.  SEE can read and write encrypted databases.
SEE encrypts all database content, including metadata, so that the database
file appears as white noise.  ZIPVFS
compresses the database on-the-fly using application-supplied
compression and decompression functions.
CEROD reads a compressed database that is
also optionally encrypted.  All of SEE, ZIPVFS, and CEROD are
supplied in source code form only; the licensee is responsible for
compiling the products for their chosen platform.  It is not difficult
to compile any of these extension.  All products come in the form of an
[amalgamation|{amalgamated source file}]
named "sqlite3.c".  So compiling SEE, ZIPVFS, or CEROD into an application
is simply a matter of substituting the SEE-, ZIPVFS-, or CEROD-enabled sqlite3.c
source file in place of the public-domain sqlite3.c source file and recompiling.
Licenses for SEE, ZIPVFS, and CEROD are perpetual.
All three extension can read and write ordinary,
uncompressed and unencrypted database files.</p>

<a name="th3"></a>
<h2>Testing Services</h2>

<p>The <a href="https://sqlite.org/th3.html">Test Harness #3 (TH3)</a> is
a suite of test cases for SQLite that provide 100% branch test coverage
(and 100% modified condition/decision coverage) for the core SQLite in
an as-deployed configuration using only published and documented interfaces.
TH3 is designed for use with embedded devices, and is compatible with
DO-178B.  Every release of the public-domain SQLite is tested using TH3,
and so all users benefit from the TH3 tests.  But the TH3 tests are not
themselves public.  Hardware or system manufactures who want to have
TH3 test run on their systems can negotiate a service agreement to have
the SQLite Developers run those tests.</p>

<h2>About The SQLite Team</h2>

<p>Paid support options and products are provided by
Hipp, Wyrick &amp; Company, Inc., (Hwaci), a
<a href="http://en.wikipedia.org/wiki/Georgia_(U.S._state)">Georgia</a>
corporation
with headquarters in
<a href="http://en.wikipedia.org/wiki/Charlotte,_North_Carolina">
Charlotte, North Carolina</a> and has been in business since
1992.
Hwaci has an international team of
employees and associates representing the best available talent.
We are a 100% engineering company.  There is
no sales staff.
Our goal is to provide outstanding service and honest advice
without spin or sales-talk.</p>

<p>Hwaci is a small company but it is
also closely held and debt-free and has low
fixed costs, which means that it is largely immune to buy-outs,
take-overs, and market down-turns.  Hwaci intends to
continue operating in its current form, and at roughly its current
size until at least the year 2050.
We expect to be here when you need us,
even if that need is many years in the future.</p>
</body></html>

<p>
The fact that SQLite is embedded and [serverless] instead of being
client/server is a feature, not a bug.

<p>
Client/server databases like MySQL, PostgreSQL, SQL Server, Oracle, and
others are an important component of modern systems.
These systems solve an important problem.
But SQLite solves a different problem.
Both SQLite and client/server databases have their role.
Developers who are comparing SQLite against other SQL database engines need
to clearly understand this distinction.

<p>
See the [Appropriate Uses For SQLite] document for additional information.

<h1>Flexible Typing</h1>

<p>
SQLite is flexible with regard to datatypes.  Datatypes are advisory
rather than mandatory.

<p>
Some commentators say that SQLite is "weakly typed" and that other
SQL databases are "strongly typed".  We consider these terms to be
inaccurate and even pejorative.  We prefer to say that SQLite is
"flexibly typed" and that other SQL database engines are
"rigidly typed".

<p>
See the [datatype|Datatypes in SQLite] document for a detailed
discussion of the type system in SQLite.

<p>
SQLite has no DATETIME datatype.
Instead, dates and times can be stored in any of these ways:

<ul>
<li> As a TEXT string in the ISO-8601 format.  Example: '2018-04-02 12:13:46'.
<li> As an INTEGER number of seconds since 1970 (also known as "unix time").
<li> As a REAL value that is the fractional
     [https://en.wikipedia.org/wiki/Julian_day|Julian day number].
</ul>

<p>
The built-in [date and time functions] of SQLite understand date/times in
all of the formats above, and can freely change between them.
Which format you use, is entirely up to your application.

[WITHOUT ROWID] tables) a PRIMARY KEY in an SQLite table is really
the same as a UNIQUE constraint.  Due to an historical oversight,
the column values of such a PRIMARY KEY are allowed to be NULL.
This is a bug, but by the time the problem was discovered there
where so many databases in circulation that depended on the bug that
the decision was made to support the buggy behavior moving forward.
<p>
The value of an [INTEGER PRIMARY KEY] column must always be a
non-NULL integer.  The PRIMARY KEY columns of a [WITHOUT ROWID]
table are also required to be non-NULL.

<h1>Aggregate Queries Can Contain Non-Aggregate Result Columns
That Are Not In The GROUP BY Clause</h1>

<p>

     applications and corrupting indexes in the process.
<li> The tables necessary to do full and proper unicode case folding are
     larger than the whole SQLite library.
</ol>
<p>
Full unicode case folding is supported in SQLite if it is compiled
with the [-DSQLITE_ENABLE_ICU] option and linked against the
[https://icu.unicode.org|International Components for Unicode]
library.

<tcl>hd_fragment dblquote {double-quoted string literal}</tcl>
<h1>Double-quoted String Literals Are Accepted</h1>

<p>
The SQL standard requires double-quotes around identifiers

  The SQLITE_DONE result code indicates that an operation has completed.
  The SQLITE_DONE result code is most commonly seen as a return value
  from [sqlite3_step()] indicating that the SQL statement has run to
  completion.  But SQLITE_DONE can also be returned by other multi-step
  interfaces such as [sqlite3_backup_step()].
}
RESCODE SQLITE_OK_LOAD_PERMANENTLY     {SQLITE_OK | (1<<8)} {
  The [sqlite3_load_extension()] interface loads an
  [loadable extension|extension] into a single
  database connection.  The default behavior is for that extension to be
  automatically unloaded when the database connection closes.  However,
  if the extension entry point returns SQLITE_OK_LOAD_PERMANENTLY instead
  of SQLITE_OK, then the extension remains loaded into the process address
  space after the database connection closes.  In other words, the
  xDlClose methods of the [sqlite3_vfs] object is not called for the
  extension when the database connection closes.
  <p>
  The SQLITE_OK_LOAD_PERMANENTLY return code is useful to
  [loadable extensions] that register new [VFSes], for example.
}
RESCODE SQLITE_AUTH_USER               {SQLITE_AUTH  | (1<<8)} {
  The SQLITE_AUTH_USER error code is an [ext-v-prim|extended error code]
  for [SQLITE_AUTH] indicating that an operation was attempted on a
  database for which the logged in user lacks sufficient authorization.
}

  The SQLITE_IOERR_CHECKRESERVEDLOCK error code is
  an [ext-v-prim|extended error code]
  for [SQLITE_IOERR] indicating an I/O error
  within the xCheckReservedLock method on the [sqlite3_io_methods] object.
}
RESCODE SQLITE_IOERR_LOCK              {SQLITE_IOERR | (15<<8)} {
  The SQLITE_IOERR_LOCK error code is an [ext-v-prim|extended error code]
  for [SQLITE_IOERR] indicating an I/O error in the
  advisory file locking logic.
  Usually an SQLITE_IOERR_LOCK error indicates a problem obtaining
  a [PENDING lock].  However it can also indicate miscellaneous
  locking errors on some of the specialized [VFSes] used on Macs.
}
RESCODE SQLITE_IOERR_CLOSE             {SQLITE_IOERR | (16<<8)} {
  The SQLITE_IOERR_ACCESS error code is an [ext-v-prim|extended error code]

  rather than an ordinary device failure. This often indicates a
  corrupt filesystem.
}
RESCODE SQLITE_LOCKED_SHAREDCACHE      {SQLITE_LOCKED |  (1<<8)} {
  The SQLITE_LOCKED_SHAREDCACHE error code is
  an [ext-v-prim|extended error code] for [SQLITE_LOCKED]
  indicating that the locking conflict has occurred due to contention
  with a different [database connection] that happens to hold a
  [shared cache] with the database connection to which the error was
  returned.  For example, if the other database connection is holding
  an [exclusive lock] on the database, then the database connection
  that receives this error will be unable to read or write any part
  of the database file unless it has the [read_uncommitted pragma]
  enabled.
  <p>
  The SQLITE_LOCKED_SHARECACHE error code works very much like the
  [SQLITE_BUSY] error code except that SQLITE_LOCKED_SHARECACHE is
  for separate database connections that share a cache whereas
  SQLITE_BUSY is for the much more common case of separate database
  connections that do not share the same cache.  Also, the
  [sqlite3_busy_handler()] and [sqlite3_busy_timeout()] interfaces
  do not help in resolving SQLITE_LOCKED_SHAREDCACHE conflicts.
}
RESCODE SQLITE_BUSY_RECOVERY           {SQLITE_BUSY   |  (1<<8)} {
  The SQLITE_BUSY_RECOVERY error code is an [ext-v-prim|extended error code]

    incr nExtCode
    set ext_rc($name) $val
  }
}

</tcl>
<h1>Primary Result Code List</h1>
<p>The <tcl>hd_puts $nPrimCode</tcl> result codes are
   [result code definitions|defined in sqlite3.h] and are listed in
   alphabetical order below:
<tcl>
set lx {}
foreach name [lsort [array names prim_rc]] {
  lappend lx [list $name "$name&nbsp;($prim_rc($name))" 0]
}
hd_list_of_links {} 320 $lx

find all events that were active at any time during a given
time interval, or all events that started during a particular time interval,
or all events that both started and ended within a given time interval.
And so forth.
</p>

<p>
The R-Tree concept originated with
[https://link.springer.com/referenceworkentry/10.1007/978-0-387-35973-1_1151 | Toni Guttman]:
<em>R-Trees: A Dynamic Index Structure for Spatial Searching</em>,
Proc. 1984 ACM SIGMOD International Conference on Management of Data,
pp. 47-57.
The implementation found in SQLite is a refinement of Guttman's original
idea, commonly called "R*Trees", that was described by
Norbert Beckmann, Hans-Peter Kriegel, Ralf Schneider, Bernhard Seeger:
<em>The R*-Tree: An Efficient and Robust Access Method for Points
and Rectangles.</em> SIGMOD Conference 1990: 322-331.
</p>

<h1>Compiling The R*Tree Module</h1>

<p>
The source code to the SQLite R*Tree module is included as part
of the [amalgamation] but is disabled by default.  To enable the
R*Tree module, simply compile with the [SQLITE_ENABLE_RTREE]
C-preprocessor macro defined.  With many compilers, this is accomplished
by adding the option "-DSQLITE_ENABLE_RTREE=1" to the compiler
command-line.
</p>

<h1>Using the R*Tree Module</h1>

<p>
The SQLite R*Tree module is implemented as a
[sqlite3_create_module | virtual table].  ^Each R*Tree index is a
virtual table with an odd number of columns between 3 and 11.
^The first column is always a 64-bit signed integer primary key.
^The other columns are pairs, one pair per dimension, containing the
minimum and maximum values for that dimension, respectively.
^A 1-dimensional R*Tree thus has 3 columns.
^A 2-dimensional R*Tree has 5 columns.
^A 3-dimensional R*Tree has 7 columns.
^A 4-dimensional R*Tree has 9 columns.
^And a 5-dimensional R*Tree has 11 columns.  ^The SQLite R*Tree implementation
does not support R*Trees wider than 5 dimensions.
</p>

<p>
^The first column of an SQLite R*Tree is similar to an integer primary
key column of a normal SQLite table. ^It may only store a 64-bit signed
integer value. ^Inserting a NULL value into this column causes SQLite
to automatically generate a new unique primary key value. ^If an attempt
is made to insert any other non-integer value into this column,
the r-tree module silently converts it to an integer before writing it
into the database.
<p>
^The min/max-value pair columns are stored as 32-bit floating point values for
"rtree" virtual tables or as 32-bit signed integers in "rtree_i32" virtual
tables.  ^Unlike regular SQLite tables which can store data in a variety of
datatypes and formats, the R*Tree rigidly enforce these storage types.
^If any other type of value is inserted into such a column, the r-tree
module silently converts it to the required type before writing the
new record to the database.

<h2>Creating An R*Tree Index</h2>

<p>

<em>&lt;name&gt;</em><strong>_node</strong><br>
<em>&lt;name&gt;</em><strong>_rowid</strong><br>
<em>&lt;name&gt;</em><strong>_parent</strong>
</codeblock>
)^

<p>
^The shadow tables are ordinary SQLite data tables.  ^You can query them
directly if you like, though this unlikely to reveal anything particularly
useful.
^And you can [UPDATE], [DELETE], [INSERT] or even [DROP TABLE | DROP]
the shadow tables, though doing so will corrupt your R*Tree index.
So it is best to simply ignore the shadow tables.  Recognize that they
hold your R*Tree index information and let it go as that.
</p>

<p>
^(As an example, consider creating a two-dimensional R*Tree index for use in
spatial queries:
</p>

<codeblock>
CREATE VIRTUAL TABLE demo_index USING rtree(
   id,              -- Integer primary key
   minX, maxX,      -- Minimum and maximum X coordinate

<p>
Of course, an ordinary SQLite table will also do a query against its
integer primary key efficiently, so the previous is not important.
^The big reason for using an R*Tree is so that
you can efficiently do range queries against the coordinate
ranges.  ^(For example, the main office of the SQLite project is
located at 35.37785, -80.77470.
To find which zipcodes might service that office, one could right:
</p>

<codeblock>
SELECT id FROM demo_index
 WHERE minX&lt;=-80.77470 AND maxX&gt;=-80.77470
   AND minY&lt;=35.37785  AND maxY&gt;=35.37785;

and the upper-bound coordinates are rounded up.  ^Thus, bounding boxes might
be slightly larger than specified, but will never be any smaller.  This
is exactly what is desired for doing the more common "overlapping" queries
where the application wants to find every entry in the R*Tree that overlaps
a query bounding box.  Rounding the entry bounding boxes outward might cause a
few extra entries to appears in an overlapping query if the edge of the
entry bounding box corresponds to an edge of the query bounding box.  But
the overlapping query will never miss a valid table entry.

<p>However, for a "contained-within" style query, rounding the bounding
boxes outward might cause some entries to be excluded from the result set
if the edge of the entry bounding box corresponds to the edge of the query
bounding box.  To guard against this, applications should expand their
contained-within query boxes slightly (by 0.000012%) by rounding down the
lower coordinates and rounding up the top coordinates, in each dimension.

<codeblock>
SELECT id FROM demo_index
 WHERE maxY&gt;=35.0  AND minY&lt;=35.0;
</codeblock>

<p>
Then for each "id" value returned, suppose the application creates an
UPDATE statement like the following and binds the "id" value returned against
the "?1" parameter:

<codeblock>
UPDATE demo_index SET maxY=maxY+0.5 WHERE id=?1;
</codeblock>)^

Auxiliary columns can be used in place of
secondary tables such as "demo_data".)^

<p>
^Auxiliary columns are marked with a "+" symbol before the column name.
^Auxiliary columns must come after all of the coordinate boundary columns.
^(An RTREE table can have no more than 100 columns total.  In other words,
the count of columns including the integer primary key column,
the coordinate boundary columns, and all auxiliary columns must be 100 or less.)^
The following example shows an r-tree table with auxiliary columns that
is equivalent to the two tables "demo_index" and "demo_data" above:

^(<codeblock>
CREATE VIRTUAL TABLE demo_index2 USING rtree(
   id,              -- Integer primary key

are desired, declare the table using "rtree_i32" instead:

<codeblock>
CREATE VIRTUAL TABLE intrtree USING rtree_i32(id,x0,x1,y0,y1,z0,z1);
</codeblock>)^

<p>
^An rtree_i32 stores coordinates as 32-bit signed integers.
^Even though it stores values using integer, the rtree_i32 virtual
table still uses floating point computations internally as part of
the r-tree algorithm.

<tcl>hd_fragment {customquery} {custom r-tree queries}</tcl>
<h1>Custom R-Tree Queries</h1>

<p>By using standard SQL expressions in the WHERE clause of a SELECT query,
a programmer can query for all R*Tree entries that
intersect with or are contained within a particular bounding-box.
Custom R*Tree queries, using the MATCH
operator in the WHERE clause of a SELECT, allow the programmer to query for
the set of R*Tree entries that intersect any arbitrary region or shape, not
just a box.  This capability is useful, for example, in computing the
subset of objects in the R*Tree that are visible from a camera positioned
in 3-D space.

<p>Regions for custom R*Tree queries are defined by R*Tree geometry callbacks
implemented by the application and registered with SQLite via a call to one
of the following two APIs:

<codeblock>

[version 3.8.5] ([dateof:3.8.5]) and is the preferred interface.
The sqlite3_rtree_geometry_callback() is an older and less flexible
interface that is supported for backwards compatibility.

<p>^A call to one of the above APIs creates a new SQL function named by the
second parameter (zQueryFunc or zGeom).  ^When that SQL function appears
on the right-hand side of the MATCH operator and the left-hand side of the
MATCH operator is any column in the R*Tree virtual table, then the callback
defined by the third argument (xQueryFunc or xGeom) is invoked to determine
if a particular object or subtree overlaps the desired region.

<p>^(For example, a query like the following might be used to find all
R*Tree entries that overlap with a circle centered a 45.3,22.9 with a
radius of 5.0:

<p>^The legacy xGeom callback is invoked with four arguments.  ^The first
argument is a pointer to an sqlite3_rtree_geometry structure which provides
information about how the SQL function was invoked.  ^The second argument
is the number of coordinates in each r-tree entry, and is always the same
for any given R*Tree.  ^The number of coordinates is 2 for a 1-dimensional R*Tree,
4 for a 2-dimensional R*Tree, 6 for a 3-dimensional R*Tree, and so forth.
^The third argument, aCoord[], is an array of nCoord coordinates that defines
a bounding box to be tested.  ^The last argument is a pointer into which
the callback result should be written.  The result is zero
if the bounding-box defined by aCoord[] is completely outside
the region defined by the xGeom callback and the result is non-zero if
the bounding-box is inside or overlaps with the xGeom region.  The xGeom
callback should normally return SQLITE_OK.  ^If xGeom returns anything other
than SQLITE_OK, then the r-tree query will abort with an error.

  /* The following fields are only available in 3.8.11 and later */
  sqlite3_value **apSqlParam;       /* Original SQL values of parameters */
};
</codeblock>

<p>The first five fields of the sqlite3_rtree_query_info structure are identical
to the sqlite3_rtree_geometry structure, and have exactly the same meaning.
The sqlite3_rtree_query_info structure also contains nCoord and aCoord fields
which have the same meaning as the parameter of the same name in the xGeom callback.

<p>The xQueryFunc must set the eWithin field of sqlite3_rtree_query_info to
one of the values NOT_WITHIN, PARTLY_WITHIN, or FULLY_WITHIN depending on whether
or not the bounding box defined by aCoord[] is completely outside the region,
overlaps the region, or is completely inside the region, respectively.  In
addition, the xQueryFunc must set the rScore field to a non-negative value that

entries.

<p>^An R*Tree query is initialized by making the root node the only entry
in a priority queue sorted by rScore.
^The query proceeds by extracting the entry from the priority queue that has
the lowest score.  ^If that entry is a leaf (meaning that it is an actual
R*Tree entry and not a subtree) then that entry
is returned as one row of the query result.
^If the extracted priority queue entry is a node (a subtree),
then the next child of that node is passed to the xQueryFunc callback.
^(If the node has more children then it is returned to the priority queue.
Otherwise it is discarded.)^ ^Those subelements for which the xQueryFunc
callback sets eWithin to PARTLY_WITHIN or FULLY_WITHIN are added to the
priority queue using the score supplied by the callback.  ^Subelements that
return NOT_WITHIN are discarded.  ^The query runs until the priority queue is

<h2>Additional Considerations for Custom Queries</h2>

<p>
^The MATCH operator of a custom R*Tree query function must be a top-level
AND-connected term of the WHERE clause, or else it will not be usable
by the R*Tree query optimizer and the query will not be runnable.
^If the MATCH operator is connected to other terms of the WHERE clause
via an OR operator, for example, the query will fail with an error.

<p>
^Two or more MATCH operators are allowed in the same WHERE clause, as long
as they are connected by AND operators.  However,
the R*Tree query engine only contains a single priority queue.  ^The priority
assigned to each node in the search is the lowest priority returned by any

<p>^There is one entry in the %_node table for each R*Tree node.  An
R*Tree node consists of one or more entries that are proximate to one another.
The nodes of an R*Tree for a tree.  ^All nodes other than the root have an
entry in the %_parent shadow table that identifies the parent node.
Each entry in an R*Tree has a rowid.  ^The %_rowid shadow table maps entry
rowids to the node that contains that entry.

<p>^Extra columns appended to the %_rowid table hold the
content of [auxiliary columns].  ^The names of these extra
%_rowid columns are probably not the same as the
actual auxiliary column names.

<tcl>hd_fragment rtreecheck rtreecheck()</tcl>
<h2>Integrity Check using the rtreecheck() SQL function</h2>

<ol>
<li><p>
For each cell in the r-tree structure (%_node table), that:
<ol type=a>
<li><p> ^for each dimension, (coord1 &lt;= coord2).
<li><p> ^unless the cell is on the root node, that the cell is bounded
        by the parent cell on the parent node.
<li><p> ^for leaf nodes, that there is an entry in the %_rowid
        table corresponding to the cell's rowid value that
        points to the correct node.
<li><p> ^for cells on non-leaf nodes, that there is an entry in the
        %_parent table mapping from the cell's child node to the
        node that it resides on.
</ol>
<li><p>
^That there are the same number of entries in the %_rowid table
as there are leaf cells in the r-tree structure, and that there
is a leaf cell that corresponds to each entry in the %_rowid table.
<li><p>
^That there are the same number of entries in the %_parent table
as there are non-leaf cells in the r-tree structure, and that
there is a non-leaf cell that corresponds to each entry in the
%_parent table.
</ol>

      lappend objlist [list $k $kw $s]
    }
  }
}
hd_open_aux session/objlist.html
hd_header {List Of SQLite Objects}
hd_enable_main 0
hd_putsnl {<a href="../session/intro.html"><h2>Session Module C Interface</h2></a>}
hd_enable_main 1
</tcl>
<h2>Objects:</h2>
<tcl>
hd_list_of_links {} 300 [lsort $objlist]
hd_enable_main 0
hd_putsnl {<p>Other lists:

      lappend clist [list $k $kw $s]
    }
  }
}
hd_open_aux session/constlist.html
hd_header {List Of SQLite Constants}
hd_enable_main 0
hd_putsnl {<a href="../session/intro.html"><h2>Session Module C Interface</h2></a>}
hd_enable_main 1
</tcl>
<h2>Constants:</h2>
<tcl>
set clist [lsort -index 1 $clist]
hd_list_of_links {} 400 $clist
hd_enable_main 0

    }
  }
}
hd_open_aux session/funclist.html
hd_header {List Of SQLite Functions}
hd_keywords *session_funclist {Session Module C-API function list}
hd_enable_main 0
hd_putsnl {<a href="../session/intro.html"><h2>Session Module C Interface</h2></a>}
hd_enable_main 1
</tcl>
<h2>Functions:</h2>
<tcl>
set funclist [lsort -index 1 $funclist]
hd_list_of_links {} 300 $funclist
hd_enable_main 0

foreach c [lsort $content] {
  foreach {key title type keywords body code} $c break
  set kw [preferred_keyword [lsort $keywords]]
  hd_fragment $kw
  hd_open_aux session/[convert_keyword_to_filename $kw]
  hd_header $title
  hd_enable_main 0
  hd_puts {<a href="../session/intro.html"><h2>Session Module C Interface</h2></a>}
  hd_enable_main 1
  eval hd_keywords $keywords

  hd_puts "<h2>$title</h2>"
  hd_puts "<blockquote><pre>"
  hd_puts "$code"
  hd_puts "</pre></blockquote>"

<title>SQLite Shared-Cache Mode</title>
<tcl>hd_keywords {SQLite Shared-Cache Mode} \
        {shared cache} {shared cache mode}</tcl>

<table_of_contents>

<tcl>hd_fragment sqlite_shared_cache_mode</tcl>
<h1>SQLite Shared-Cache Mode</h1>

<p>Starting with [version 3.3.0] ([dateof:3.3.0]),
SQLite includes a special "shared-cache"
mode (disabled by default) intended for use in embedded servers. If
shared-cache mode is enabled and a thread establishes multiple connections
to the same database, the connections share a single data and schema cache.
This can significantly reduce the quantity of memory and IO required by
the system.</p>

<p>In [version 3.5.0] ([dateof:3.5.0]),
shared-cache mode was modified so that the same
cache can be shared across an entire process rather than just within
a single thread.  Prior to this change, there were restrictions on
passing database connections between threads.  Those restrictions were
dropped in 3.5.0 update.  This document describes shared-cache mode
as of version 3.5.0.</p>

is discouraged.  Most use cases for shared-cache are better served by
[WAL mode].

<p>Shared-cache mode was invented in 2006 at the request of developers
of [https://en.wikipedia.org/wiki/Symbian|Symbian].  Their problem was that
if the contacts database on the phone was being synced, that would lock the
database file.  Then if a call came in, the database lock would prevent them
from querying the contacts database in order to find the appropriate
ring-tone for the incoming call, or a photo of the caller to show on screen,
and so forth.
[WAL mode] (circa 2010) is a better solution to this problem as it permits
simultaneous access without breaking transaction isolation.

<p>Applications that build their own copy of SQLite from source code
are encouraged to use the [-DSQLITE_OMIT_SHARED_CACHE] compile-time option,
as the resulting binary will be both smaller and faster.

<p>The shared-cache interfaces described here will continue to be supported
in SQLite, to insure full backwards compatibility.  However, the use of
shared-cache is discouraged.

<h1>Shared-Cache Locking Model</h1>

<p>Externally, from the point of view of another process or thread, two
or more [sqlite3|database connections] using a shared-cache appear as a single
connection. The locking protocol used to arbitrate between multiple
shared-caches or regular database users is described elsewhere.
</p>

<table style="margin:auto">
<tr><td>
<img src="images/shared.gif">
<!-- <pre>

          +----------------+
          |    Database    |
          +----------------+
</pre> -->
</table>
<p style="font-style:italic;text-align:center">Figure 1</p>

<p>Figure 1 depicts an example runtime configuration where three
database connections have been established. Connection 1 is a normal
SQLite database connection. Connections 2 and 3 share a cache
The normal locking
protocol is used to serialize database access between connection 1 and
the shared cache. The internal protocol used to serialize (or not, see
"Read-Uncommitted Isolation Mode" below) access to the shared-cache by
connections 2 and 3 is described in the remainder of this section.
</p>

<p>There are three levels to the shared-cache locking model,
transaction level locking, table level locking and schema level locking.
They are described in the following three sub-sections.</p>

<h2>Transaction Level Locking</h2>

<p>SQLite connections can open two kinds of transactions, read and write
transactions. This is not done explicitly, a transaction is implicitly a
read-transaction until it first writes to a database table, at which point
it becomes a write-transaction.
</p>
<p>At most one connection to a single shared cache may open a
write transaction at any one time. This may co-exist with any number of read
transactions.
</p>

<h2>Table Level Locking</h2>

<p>When two or more connections use a shared-cache, locks are used to
serialize concurrent access attempts on a per-table basis. Tables support
two types of locks, "read-locks" and "write-locks". Locks are granted to
connections - at any one time, each database connection has either a
read-lock, write-lock or no lock on each database table.
</p>

<p>At any one time, a single table may have any number of active read-locks
or a single active write lock. To read data from a table, a connection must
first obtain a read-lock. To write to a table, a connection must obtain a
write-lock on that table. If a required table lock cannot be obtained,
the query fails and SQLITE_LOCKED is returned to the caller.
</p>

<p>Once a connection obtains a table lock, it is not released until the
current transaction (read or write) is concluded.
</p>

<h3>Read-Uncommitted Isolation Mode</h3>

<p>The behaviour described above may be modified slightly by using the
[read_uncommitted] pragma to change the isolation level from serialized
(the default), to read-uncommitted.</p>

<p> A database connection in read-uncommitted mode does not attempt
to obtain read-locks before reading from database tables as described
above. This can lead to inconsistent query results if another database
connection modifies a table while it is being read, but it also means that
a read-transaction opened by a connection in read-uncommitted mode can
neither block nor be blocked by any other connection.</p>

<p>Read-uncommitted mode has no effect on the locks required to write to
database tables (i.e. read-uncommitted connections must still obtain
write-locks and hence database writes may still block or be blocked).
Also, read-uncommitted mode has no effect on the [sqlite_schema]
locks required by the rules enumerated below (see section
"Schema (sqlite_schema) Level Locking").
</p>

<blockquote><pre>
  /* Set the value of the read-uncommitted flag:
  **
  **   True  -> Set the connection to read-uncommitted mode.
  **   False -> Set the connection to serialized (the default) mode.
  */
  PRAGMA read_uncommitted = &lt;boolean&gt;;

  /* Retrieve the current value of the read-uncommitted flag */
  PRAGMA read_uncommitted;
</pre></blockquote>

<h2>Schema (sqlite_schema) Level Locking</h2>

<p>The [sqlite_schema table] supports shared-cache read and write
locks in the same way as all other database tables (see description
above). The following special rules also apply:
</p>

<ul>
<li>A connection must obtain a read-lock on <i>sqlite_schema</i> before
accessing any database tables or obtaining any other read or write locks.</li>
<li>Before executing a statement that modifies the database schema (i.e.
a CREATE or DROP TABLE statement), a connection must obtain a write-lock on
<i>sqlite_schema</i>.
</li>
<li>A connection may not compile an SQL statement if any other connection
is holding a write-lock on the <i>sqlite_schema</i> table of any attached
database (including the default database, "main").
</li>
</ul>

<h1>Thread Related Issues</h1>

<p>In SQLite versions 3.3.0 through 3.4.2 when shared-cache mode is enabled,
a database connection may only be
used by the thread that called [sqlite3_open()] to create it.
And a connection could only share cache with another connection in the
same thread.
These restrictions were dropped beginning with SQLite
[version 3.5.0] ([dateof:3.5.0]).
</p>

<h1>Shared Cache And Virtual Tables</h1>

<p>
In older versions of SQLite,
shared cache mode could not be used together with virtual tables.
This restriction was removed in SQLite [version 3.6.17] ([dateof:3.6.17]).

<h1>Enabling Shared-Cache Mode</h1>

<p>Shared-cache mode is enabled on a per-process basis. Using the C
interface, the following API can be used to globally enable or disable
shared-cache mode:
</p>

<blockquote><pre>
int sqlite3_enable_shared_cache(int);
</pre></blockquote>

ATTACH 'file:aux.db?cache=shared' AS aux;
</pre></blockquote>

<tcl> hd_fragment inmemsharedcache {in-memory shared-cache} </tcl>
<h1>Shared Cache And In-Memory Databases</h1>

<p>
Beginning with SQLite [version 3.7.13] ([dateof:3.7.13]),
shared cache can be used on
[in-memory databases], provided that the database is created using
a [URI filename].  For backwards compatibility, shared cache is always
disabled for in-memory
databases if the unadorned name ":memory:" is used to open the database.
Prior to version 3.7.13, shared cache was always
disabled for in-memory databases regardless of the database name used,

<title>SQLite Database Speed Comparison</title>

<h2>Database Speed Comparison</h2>

<font color="red"><b>
Note:  This document is very very old.  It describes a speed comparison between
archaic versions of SQLite, MySQL and PostgreSQL.
<p>
The numbers here have become meaningless.  This page has been retained only
as an historical artifact.
</b></font>

<h3>Executive Summary</h3>

<p>A series of tests were run to measure the relative performance of
SQLite 2.7.6, PostgreSQL 7.1.3, and MySQL 3.23.41.
The following are general
conclusions drawn from these experiments:
</p>

<ul>
<li><p>
  SQLite 2.7.6 is significantly faster (sometimes as much as 10 or
  20 times faster) than the default PostgreSQL 7.1.3 installation
  on RedHat 7.2 for most common operations.
</p></li>
<li><p>
  SQLite 2.7.6 is often faster (sometimes
  more than twice as fast) than MySQL 3.23.41
  for most common operations.
</p></li>
<li><p>

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>

<blockquote>
Obsolete URL: http://www.sergeant.org/sqlite_vs_pgsync.html
</blockquote>

<p>
SQLite was tested in the same configuration that it appears
on the website.  It was compiled with -O6 optimization and with
the -DNDEBUG=1 switch which disables the many "assert()" statements
in the SQLite code.  The -DNDEBUG=1 compiler option roughly doubles
the speed of SQLite.
</p>

<p>
All tests are conducted on an otherwise quiescent machine.
A simple Tcl script was used to generate and run all the tests.
A copy of this Tcl script can be found in the SQLite source tree
in the file <b>tools/speedtest.tcl</b>.
</p>

<p>
The times reported on all tests represent wall-clock time
in seconds.  Two separate time values are reported for SQLite.
The first value is for SQLite in its default configuration with
full disk synchronization turned on.  With synchronization turned
on, SQLite executes
an <b>fsync()</b> system call (or the equivalent) at key points
to make certain that critical data has
actually been written to the disk drive surface.  Synchronization
is necessary to guarantee the integrity of the database if the
operating system crashes or the computer powers down unexpectedly
in the middle of a database update.  The second time reported for SQLite is
when synchronization is turned off.  With synchronization off,
SQLite is sometimes much faster, but there is a risk that an
operating system crash or an unexpected power failure could
damage the database.  Generally speaking, the synchronous SQLite
times are for comparison against PostgreSQL (which is also
synchronous) and the asynchronous SQLite times are for
comparison against the asynchronous MySQL engine.
</p>

<h3>Test 1: 1000 INSERTs</h3>
<blockquote>
CREATE TABLE t1(a INTEGER, b INTEGER, c VARCHAR(100));<br>
INSERT INTO t1 VALUES(1,13153,'thirteen thousand one hundred fifty three');<br>

<p>
Because it does not have a central server to coordinate access,
SQLite must close and reopen the database file, and thus invalidate
its cache, for each transaction.  In this test, each SQL statement
is a separate transaction so the database file must be opened and closed
and the cache must be flushed 1000 times.  In spite of this, the asynchronous
version of SQLite is still nearly as fast as MySQL.  Notice how much slower
the synchronous version is, however.  SQLite calls <b>fsync()</b> after
each synchronous transaction to make sure that all data is safely on
the disk surface before continuing.  For most of the 13 seconds in the
synchronous test, SQLite was sitting idle waiting on disk I/O to complete.</p>


<h3>Test 2: 25000 INSERTs in a transaction</h3>
<blockquote>

<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;0.975</td></tr>
<tr><td>SQLite 2.7.6:</td><td align="right">&nbsp;&nbsp;&nbsp;4.004</td></tr>
<tr><td>SQLite 2.7.6 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;0.560</td></tr>
</table>

<p>
The synchronous version of SQLite is the slowest of the group in this test,
but the asynchronous version is the fastest.
The difference is the extra time needed to execute fsync().
</p>

<h3>Test 13: DELETE with an index</h3>
<blockquote>
DELETE FROM t2 WHERE a>10 AND a<20000;
</blockquote><table border=0 cellpadding=0 cellspacing=0>

This probably is because when SQLite drops a table, it has to go through and
erase the records in the database file that deal with that table.  MySQL and
PostgreSQL, on the other hand, use separate files to represent each table
so they can drop a table simply by deleting a file, which is much faster.
</p>

<p>
On the other hand, dropping tables is not a very common operation
so if SQLite takes a little longer, that is not seen as a big problem.
</p>

<title>SQLite Archive Files</title>
<tcl>hd_keywords {SQLAR} {SQLite Archive} {SQL Archive} \
     {SQLite Archive format} {SQLite Archive files} </tcl>
<table_of_contents>

<h1>Introduction</h1>

<p>
An "SQLite Archive" is a file container similar to a
[https://en.wikipedia.org/wiki/Zip_(file_format)|ZIP archive] or
[https://en.wikipedia.org/wiki/Tar_(computing)|Tarball] but
based on an SQLite database.

<p>
An SQLite Archive is an ordinary SQLite database file that contains the
following table as part of its schema:

and decompression of content.  Even this tiny extension can be omitted
if the files in the archive are uncompressed.  In contrast, supporting
ZIP Archives and/or Tarballs requires either separate libraries or
lots of extra custom code, or sometimes both.

<li><p>
An SQLite Archive can work around firewall-imposed censorship.
For example, certain file types that are considered "dangerous"
(examples: DLLs) will be
[https://support.google.com/mail/answer/6590|blocked by Gmail]
and probably many other email services and firewalls, even if the
files are wrapped inside a ZIP Archive or Tarball.
But these firewalls usually do not (yet) know about SQLite Archives and
so content can be put inside an SQLite Archive to evade censorship.
</ol>

</ol>

<tcl>hd_fragment cltools {managing SQLite Archives from the command-line}</tcl>
<h1>Managing An SQLite Archive From The Command-Line</h1>

<p>
The recommended way of creating, updating, listing, and extracting
an SQLite Archive is to use the [sqlite3.exe command-line shell]
for SQLite [version 3.23.0] ([dateof:3.23.0]) or later.  This CLI
supports the -A command-line option that allows easy management
of SQLite Archives.
The CLI for SQLite [version 3.22.0] ([dateof:3.22.0]) has the
[.archive command] for managing SQLite Archives, but that requires
interacting with the shell.

All of these commands work the same way if the filename argument is
is a ZIP Archive instead of an SQLite database.

<h2>Other command-line tools</h2>

<p>
Just as there is the "zip" program to manage ZIP Archives, and the
"tar" program to manage Tarballs, the
[https://sqlite.org/sqlar|"sqlar" program] exists to manage SQL Archives.
The "sqlar" program is able to create a new SQLite Archive, list the
content of an existing archive, add or remove files from the archive,
and/or extract files from the archive.
A separate "sqlarfs" program is able to mount the SQLite Archive as
a [https://github.com/libfuse/libfuse|Fuse Filesystem].

<h1>Managing SQLite Archives From Application Code</h1>

<p>
Applications can easily read or write SQLite Archives by linking against
SQLite and including the
[https://sqlite.org/src/file/ext/misc/sqlar.c|ext/misc/sqlar.c] extension
to handle the compression and decompression.  The sqlar.c extension
creates two new SQL functions.

<dl>
<dt><b>sqlar_compress(X)</b></dt>
<dd><p>

# facilitate that, all the size values are defined by variables here
# which are then used as needed through the document.
#
# NOTE:  Also update the version number in the text!!!
#

# sloc sqlite3.c
set stat(coreSLOC)   151268  ;# Non-comment lines of amalgamation code
# sloc test*.c
set stat(tclcSLOC)    27667  ;# Non-comment lines of test C code
# ls test*.c tclsqlite.c | wc
set stat(tclcNfile)      50  ;# Number of files of TCL C testcode + tclsqlite.c
# ls -l test*.c tclsqlite.c | awk '{sum+=$5}END{print sum}'
set stat(tclcNByte) 1279224  ;# Number of bytes of TCL C testcode + tclsqlite.c
# sloc `find . -name '*.test' -print` test/*.tcl

set stat(totalSLOC) [expr {$stat(tclcSLOC)+$stat(tclsSLOC)+
                           $stat(th3SLOC)+$stat(sltcSLOC)+$stat(sltsSLOC)}]

proc GB {expr} {
  set n [uplevel #0 expr $expr]
  hd_puts [format %.2f [expr {$n/(1000.0*1000.0*1000.0)}]]
}
proc MiB {expr} {
  set n [uplevel #0 expr $expr]
  hd_puts [format %.1f [expr {$n/(1024.0*1024.0)}]]
}
proc MB {expr} {
  set n [uplevel #0 expr $expr]
  hd_puts [format %.1f [expr {$n/(1000.0*1000.0)}]]
}
proc KiB {expr} {
  set n [uplevel #0 expr $expr]
  hd_puts [format %.1f [expr {$n/(1024.0)}]]
}
proc KB {expr} {
  set n [uplevel #0 expr $expr]
  hd_puts [format %.1f [expr {$n/(1000.0)}]]
}
proc N {expr} {
  hd_puts [uplevel #0 expr $expr]
}

<tcl>KB {$stat(coreSLOC)}</tcl> KSLOC of C code.
(KSLOC means thousands of "Source Lines Of Code" or, in other words,
lines of code excluding blank lines and comments.)
By comparison, the project has
<tcl>
hd_puts "[expr {int($stat(totalSLOC)/$stat(coreSLOC))}] times as much"
</tcl>
test code and test scripts -
<tcl>KB {$stat(totalSLOC)}</tcl> KSLOC.</p>

<h2>Executive Summary</h2>

<ul>
<li> Four independently developed test harnesses
<li> 100% branch test coverage in an as-deployed configuration

<li> Undefined behavior checks
<li> Checklists
</ul>

<tcl>hd_fragment {harnesses} {test harness} {three test harnesses}</tcl>
<h1>Test Harnesses</h1>

<p>There are four independent test harnesses used for testing the
core SQLite library.
Each test harness is designed, maintained, and managed separately
from the others.
</p>

<ol>
<li><p>
<tcl>hd_fragment tcl {TCL test suite}</tcl>
The <b>TCL Tests</b> are the original tests for SQLite.
They are contained in the same source tree as the
SQLite core and like the SQLite core are in the public domain.  The
TCL tests are the primary tests used during development.
The TCL tests are written using the
[http://www.tcl-lang.org/ | TCL scripting language].
The TCL test harness itself consists of <tcl>KB {$stat(tclcSLOC)}</tcl> KSLOC
of C code used to create the TCL interface.  The test scripts are contained
in <tcl>N {$stat(tclsNFile)}</tcl> files totaling
<tcl>MiB {$stat(tclsNByte)}</tcl>MB in size.  There are
<tcl>N {$stat(tclNTest)}</tcl> distinct test cases, but many of the test
cases are parameterized and run multiple times (with different parameters)
so that on a full test run millions of
separate tests are performed.
</p>
</li>

<li><p>
The <b>[TH3]</b> test harness is a set of proprietary tests, written in
C that provide 100% branch test coverage
(and [MC/DC|100% MC/DC test coverage]) to
the core SQLite library.  The TH3 tests are designed to run
on embedded and specialized platforms that would not easily support
TCL or other workstation services.  TH3 tests use only the published
SQLite interfaces. TH3 consists of about
<tcl>MB {$stat(th3NByte)}</tcl> MB or <tcl>KB {$stat(th3SLOC)}</tcl> KSLOC
of C code implementing <tcl>N {$stat(th3NTest)}</tcl> distinct test cases.
TH3 tests are heavily parameterized, though, so a full-coverage test runs
about <tcl>MB {$stat(th3NECov)}</tcl> million different test
instances.  The cases that provide 100% branch test coverage constitute
a subset of the total TH3 test suite.  A soak test

SQLite against PostgreSQL, MySQL, Microsoft SQL Server, and Oracle 10g.
SLT runs <tcl>MB {$stat(sltNTest)}</tcl> million queries comprising
<tcl>GB {$stat(sltsNByte)}</tcl>GB of test data.
</p></li>

<li><p>
The <a href="#dbsqlfuzz"><b>dbsqlfuzz</b></a> engine is a
proprietary fuzz tester.  Other [fuzz testing|fuzzers for SQLite]
mutate either the SQL inputs or the database file.  Dbsqlfuzz mutates
both the SQL and the database file at the same time, and is thus able
to reach new error states.  Dbsqlfuzz is built using the
[http://llvm.org/docs/LibFuzzer.html|libFuzzer] framework of LLVM
with a custom mutator.  There are
<tcl>hd_puts $stat(nfuzzseed)</tcl> seed files. The dbsqlfuzz fuzzer
runs about one billion test mutations per day.
Dbsqlfuzz helps ensure
that SQLite is robust against attack via malicious SQL or database
inputs.
</ol>

<p>In addition to the four main test harnesses, there several other
small programs that implement specialized tests.
<ol>
<li value="5">The "speedtest1.c" program
estimates the performance of SQLite under a typical workload.
<li>The "mptester.c" program is a stress test for multiple processes
concurrently reading and writing a single database.
<li>The "threadtest3.c" program is a stress test for multiple threads using
SQLite simultaneously.
<li>The "fuzzershell.c" program is used to
run some <a href="#fuzztesting">fuzz tests</a>.
</ol>
</p>

<p>All of the tests above must run successfully, on multiple platforms
and under multiple compile-time configurations,
before each release of SQLite.</p>

<p>Prior to each check-in to the SQLite source tree, developers
typically run a subset (called "veryquick") of the Tcl tests
consisting of about
<tcl>KB {$stat(vqNEval)}</tcl> thousand test cases.
The veryquick tests include most tests other than the anomaly, fuzz, and
soak tests.  The idea behind the veryquick tests are that they are
sufficient to catch most errors, but also run in only a few minutes
instead of a few hours.</p>

<tcl>hd_fragment anomaly</tcl>
<h1>Anomaly Testing</h1>

SQLite is frequently used on embedded devices, it is important that
SQLite be able to gracefully handle OOM errors.</p>

<p>OOM testing is accomplished by simulating OOM errors.
SQLite allows an application to substitute an alternative malloc()
implementation using the [sqlite3_config]([SQLITE_CONFIG_MALLOC],...)
interface.  The TCL and TH3 test harnesses are both capable of
inserting a modified version of malloc() that can be rigged to fail
after a certain number of allocations.  These instrumented mallocs
can be set to fail only once and then start working again, or to
continue failing after the first failure.  OOM tests are done in a
loop.  On the first iteration of the loop, the instrumented malloc
is rigged to fail on the first allocation.  Then some SQLite operation
is carried out and checks are done to make sure SQLite handled the
OOM error correctly.  Then the time-to-failure counter
on the instrumented malloc is increased by one and the test is
repeated.  The loop continues until the entire operation runs to
completion without ever encountering a simulated OOM failure.
Tests like this are run twice, once with the instrumented malloc
set to fail only once, and again with the instrumented malloc set
to fail continuously after the first failure.</p>

<tcl>hd_fragment ioerrtesting</tcl>
<h2>I/O Error Testing</h2>

<p>I/O error testing seeks to verify that SQLite responds sanely
to failed I/O operations.  I/O errors might result from a full disk drive,
malfunctioning disk hardware, network outages when using a network
file system, system configuration or permission changes that occur in the
middle of an SQL operation, or other hardware or operating system
malfunctions.  Whatever the cause, it is important that SQLite be able
to respond correctly to these errors and I/O error testing seeks to
verify that it does.</p>

<p>I/O error testing is similar in concept to OOM testing; I/O errors
are simulated and checks are made to verify that SQLite responds
correctly to the simulated errors.  I/O errors are simulated in both

new behavior are retained and further mutated.  In this way, AFL is able
to "discover" new behaviors of the program under test, including behaviors
that were never envisioned by the designers.

<p>AFL proved adept at finding arcane bugs in SQLite.
Most of the findings have been assert() statements where the conditional
was false under obscure circumstances.  But AFL has also found
a fair number of crash bugs in SQLite, and even a few cases where SQLite
computed incorrect results.

<p>Because of its past success, AFL became a standard part of the testing
strategy for SQLite beginning with [version 3.8.10] ([dateof:3.8.10]) until
it was superseded by better fuzzers in [version 3.29.0] ([dateof:3.29.0]).

<tcl>hd_fragment ossfuzz {OSS Fuzz}</tcl>
<h3>Google OSS Fuzz</h3>

<p>Beginning in 2016, a team of engineers at Google started the
[https://github.com/google/oss-fuzz|OSS Fuzz] project.
OSS Fuzz uses a AFL-style guided fuzzer running on Google's infrastructure.
The Fuzzer automatically downloads the latest check-ins for participating
projects, fuzzes them, and sends email to the developers reporting any
problems.  When a fix is checked in, the fuzzer automatically detects this
and emails a confirmation to the developers.

<p>SQLite is one of many open-source projects that OSS Fuzz tests. The

[https://www.sqlite.org/src/timeline?y=ci&c=0a2eb949f8a759e5|[2]]
[https://www.sqlite.org/src/timeline?y=ci&c=62f2235adf796c72|[3]].

<tcl>hd_fragment dbsqlfuzz {dbsqlfuzz}</tcl>
<h3>The dbsqlfuzz fuzzer</h3>

<p>Beginning in late 2018, SQLite has been fuzzed using a proprietary
fuzzer called "dbsqlfuzz".  Dbsqlfuzz is built using the
[http://llvm.org/docs/LibFuzzer.html|libFuzzer] framework of LLVM.

<p>The dbsqlfuzz fuzzer mutates both the SQL input and the database file
at the same time.  Dbsqlfuzz uses a custom
[https://github.com/google/fuzzing/blob/master/docs/structure-aware-fuzzing.md|Structure-Aware Mutator]
on a specialized input file that defines both an input database and SQL
text to be run against that database. Because it mutates both the input
database and the input SQL at the same time, dbsqlfuzz has been able to
find some obscure faults in SQLite that were missed by prior fuzzers that
mutated only SQL inputs or only the database file.
The SQLite developers keep dbsqlfuzz running against trunk in about
16 cores at all times.  Each instance of dbsqlfuzz program is able to
evalutes about 400 test cases per second, meaning that about 500 million
cases are checked every day.</p>

<p>The dbsqlfuzz fuzzer has been very successful at hardening the
SQLite code base against malicious attack.  Since dbsqlfuzz has been
added to the SQLite internal test suite, bug reports from external
fuzzers such as OSSFuzz have all but stopped.

and they do occasionally get bug reports found by independent
fuzzers.  All such reports are promptly fixed, so the product is
improved and that the entire SQLite user community benefits.
This mechanism of having many independent testers is similar to
[https://en.wikipedia.org/wiki/Linus%27s_law|Linus's law]:
"given enough eyeballs, all bugs are shallow".

<p>One fuzzing researcher of particular note is
<a href="https://www.manuelrigger.at/">Manuel Rigger</a>, currently
(as this paragraph is written on 2019-12-21)
at <a href="https://ethz.ch/en.html">ETH Zurich</a>.
Most fuzzers only look for assertion faults, crashes, undefined behavior (UB),
or other easily detected anomalies.  Dr. Rigger's fuzzers, on the other hand,
are able to find cases where SQLite computes an incorrect answer.
Rigger has found

collected in a set of database files in the main SQLite source tree
and then rerun by the "fuzzcheck" utility program whenever one runs
"make test".  Fuzzcheck only runs a few thousand "interesting" cases
out of the billions of cases that the various fuzzers have
examined over the years.  "Interesting" cases are cases that exhibit
previously unseen behavior.  Actual bugs found by fuzzers are always
included among the interesting test cases, but most of the cases run
by fuzzcheck were never actual bugs.

<tcl>hd_fragment tension {coverage testing vs. fuzz testing}</tcl>
<h3>Tension Between Fuzz Testing And 100% MC/DC Testing</h3>

<p>Fuzz testing and [MC/DC|100% MC/DC testing] are in tension with
one another.
That is to say, code tested to 100% MC/DC will tend to be
more vulnerable to problems found by fuzzing and code that performs
well during fuzz testing will tend to have (much) less than
100% MC/DC.
This is because MC/DC testing discourages [defensive code] with
unreachable branches, but without defensive code, a fuzzer is
more likely to find a path that causes problems.  MC/DC testing
seems to work well for building code that is robust during
normal use, whereas fuzz testing is good for building code that is
robust against malicious attack.

<p>Of course, users would prefer code that is both robust in normal
use and resistant to malicious attack.  The SQLite developers are
dedicated to providing that.  The purpose of this section is merely
to point out that doing both at the same time is difficult.

<p>For much of its history SQLite has been focused on 100% MC/DC testing.
Resistance to fuzzing attacks only became a concern with the introduction
of AFL in 2014.  For a while there, fuzzers were finding many problems
in SQLite.  In more recent years, the testing strategy of SQLite has
evolved to place more emphasis on fuzz testing.  We still maintain
100% MC/DC of the core SQLite code, but most testing CPU cycles are
now devoted to fuzzing.

<p>While fuzz testing and 100% MC/DC testing are in tension, they
are not completely at cross-purposes.  The fact that the SQlite test
suite does test to 100% MC/DC means that when fuzzers do find problems,
those problems can be fixed quickly and with little risk of introducing
new errors.

<p>The [dbsqlfuzz] fuzzer also does an excellent job of verifying
that SQLite responds sanely to malformed database files.</p>

<h2>Boundary Value Tests</h2>

<p>SQLite defines certain [limits] on its operation, such as the
maximum number of columns in a table, the maximum length of an
SQL statement, or the maximum value of an integer.  The TCL and TH3 test
suites both contains numerous tests that push SQLite right to the edge
of its defined limits and verify that it performs correctly for
all allowed values.  Additional tests go beyond the defined limits
and verify that SQLite correctly returns errors.  The source code
contains [testcase macros] to verify that both sides of each boundary
have been tested.</p>

<tcl>hd_fragment regressiontesting</tcl>
<h1>Regression Testing</h1>

<p>Whenever a bug is reported against SQLite, that bug is not considered
fixed until new test cases that would exhibit the bug have been added
to either the TCL or TH3 test suites.
Over the years,
this has resulted in thousands and thousands of new tests.
These regression tests ensure that bugs that have
been fixed in the past are not reintroduced into future versions of
SQLite.</p>

test coverage - 100% branch test coverage.</p>

<tcl>hd_fragment defcode {defensive code}</tcl>
<h2>Coverage testing of defensive code</h2>

<p>A well-written C program will typically contain some defensive
conditionals which in practice are always true or always false.
This leads to a
programming dilemma:  Does one remove defensive code in order to obtain
100% branch coverage?</p>

<p>In SQLite, the answer to the previous question is "no".
For testing purposes, the SQLite source code defines
macros called ALWAYS() and NEVER().   The ALWAYS() macro
surrounds conditions

<tt>testcase()</tt> macro is a no-op:</p>

<codeblock>
#define testcase(X)
</codeblock>

<p>But in a coverage measuring build, the <tt>testcase()</tt> macro
generates code that evaluates the conditional expression in its argument.
Then during analysis, a check
is made to ensure tests exist that evaluate the conditional to both true
and false.  <tt>Testcase()</tt> macros are used, for example, to help verify
that boundary values are tested.  For example:</p>

<codeblock>
testcase( a==b );

  /* ... */
}
</codeblock>

<p>For bitmask tests, <tt>testcase()</tt> macros are used to verify that every
bit of the bitmask affects the outcome.  For example, in the following block
of code, the condition is true if the mask contains either of two bits
indicating either a MAIN_DB or a TEMP_DB is being opened.
The <tt>testcase()</tt>
macros that precede the if statement verify that both cases are tested:</p>

<codeblock>
testcase( mask & SQLITE_OPEN_MAIN_DB );
testcase( mask & SQLITE_OPEN_TEMP_DB );
if( (mask & (SQLITE_OPEN_MAIN_DB|SQLITE_OPEN_TEMP_DB))!=0 ){ ... }
</codeblock>

<p>The SQLite source code contains <tcl>N {$stat(nTestcase)}</tcl>
uses of the <tt>testcase()</tt> macro.</p>

<tcl>hd_fragment {mcdc} *MC/DC {MC/DC testing}</tcl>
<h2>Branch coverage versus MC/DC</h2>

<p>Two methods of measuring test coverage were described above:
"statement" and "branch" coverage.  There are many other test coverage
metrics besides these two.  Another popular metric is "Modified
Condition/Decision Coverage" or MC/DC.
[http://en.wikipedia.org/wiki/Modified_Condition/Decision_Coverage | Wikipedia]
defines MC/DC as follows:</p>

<ul>
<li> Each decision tries every possible outcome.
<li> Each condition in a decision takes on every possible outcome.
<li> Each entry and exit point is invoked.
<li> Each condition in a decision is shown to independently
     affect the outcome of the decision.
</ul>

<p>In the C programming language
where <b><tt>&amp;&amp;</tt></b> and <b><tt>||</tt></b>
are "short-circuit" operators, MC/DC and branch coverage are very nearly
the same thing.  The primary difference is in boolean vector tests.
One can test for any of several bits in bit-vector and still obtain
100% branch test coverage even though the second element of MC/DC - the
requirement that each condition in a decision take on every possible outcome -
might not be satisfied.</p>

<p>SQLite uses <tt>testcase()</tt> macros as described in the previous
subsection to make sure that every condition in a bit-vector decision takes
on every possible outcome.  In this way, SQLite also achieves 100% MC/DC
in addition to 100% branch coverage.</p>

<h2>Measuring branch coverage</h2>

<p>Branch coverage in SQLite is currently measured
using [https://gcc.gnu.org/onlinedocs/gcc/Gcov.html|gcov] with the "-b"
option.  First the test program is compiled using options
"-g -fprofile-arcs -ftest-coverage" and then the test program is run.
Then "gcov -b" is run to generate a coverage report.
The coverage report is verbose and inconvenient to read,
so the gcov-generated report is processed using
some simple scripts to put it into a more human-friendly format.
This entire process is automated using scripts, of course.

<p>Note that running SQLite with gcov is not a test of SQLite &mdash;
it is a test of the test suite.  The gcov run does not test SQLite because
the -fprofile-args and -ftest-coverage options cause the compiler to
generate different code.
The gcov run merely verifies that the test suite provides 100% branch test
coverage.  The gcov run is a test of the test - a meta-test.

<p>After gcov has been run to verify 100% branch test coverage,
then the test program is recompiled using delivery compiler options
(without the special -fprofile-arcs and -ftest-coverage options)
and the test program is rerun.
This second run is the actual test of SQLite.

<p>It is important to verify that the gcov test run
and the second real test run both give the same output.  Any
differences in output indicate either the use of undefined or
indeterminate behavior in the SQLite code (and hence a bug),
or a bug in the compiler.
Note that SQLite has, over the previous decade, encountered bugs
in each of GCC, Clang, and MSVC.  Compiler bugs, while rare, do happen,
which is why it is so important to test the code in an as-delivered
configuration.

<tcl>hd_fragment mutationtests {mutation testing}</tcl>
<h2>Mutation testing</h2>

<p>Using gcov (or similar) to show that every branch instruction is taken
at least once in both directions is good measure of test suite quality.
But even better is showing that every branch instruction makes
a difference in the output.  In other words, we want to show
not only that every branch instruction both jumps and falls through but also
that every branch is doing useful work and that the test suite is able
to detect and verify that work.  When a branch is found that does not
make a difference in the output, that suggests that the code associated
the branch can be removed (reducing the size of the library and perhaps
making it run faster) or that the test suite is inadequately testing the
feature that the branch implements.

<p>SQLite strives to verify that every branch instruction makes a difference
using [https://en.wikipedia.org/wiki/Mutation_testing|mutation testing].
[mutation test script|A script]
first compiles the SQLite source code into assembly language
(using, for example, the -S option to gcc).  Then the script steps through
the generated assembly language and, one by one, changes each branch
instruction into either an unconditional jump or a no-op, compiles the
result, and verifies that the test suite catches the mutation.

<p>
Unfortunately, SQLite contains many branch instructions that
help the code run faster without changing the output.
Such branches generate false-positives during mutation testing.
As an example, consider the following
[https://www.sqlite.org/src/artifact/55b5fb474?ln=55-62 | hash function]
used to accelerate table-name lookup:

<codeblock>
55  static unsigned int strHash(const char *z){
56    unsigned int h = 0;
57    unsigned char c;

If the branch instruction that implements the "c!=0" test on line 58
is changed into a no-op, then the while-loop will loop forever and the
test suite will fail with a time-out.  But if that branch is changed
into an unconditional jump, then the hash function will always return 0.
The problem is that 0 is a valid hash.  A hash function that always
returns 0 still works in the sense that SQLite still always gets the correct
answer.  The table-name hash table degenerates into a linked-list
and so the table-name lookups that occur while parsing SQL statements
might be a little slower, but the end result will be the same.

<p>
To work around this problem, comments of the form
"<code>/*OPTIMIZATION-IF-TRUE*/</code>" and
"<code>/*OPTIMIZATION-IF-FALSE*/</code>" are inserted into the SQLite
source code to tell the mutation testing script to ignore some branch

true.  If the assertion is false, the program prints an error message
and halts.</p>

<p>Assert() macros are disabled by compiling with the NDEBUG macro defined.
In most systems, asserts are enabled by default.  But in SQLite, the
asserts are so numerous and are in such performance critical places, that
the database engine runs about three times slower when asserts are enabled.
Hence, the default (production) build of SQLite disables asserts.
Assert statements are only enabled when SQLite is compiled with the
SQLITE_DEBUG preprocessor macro defined.</p>

<p>See the [The Use Of assert In SQLite|Use Of assert in SQLite] document
for additional information about how SQLite uses assert().</p>

<tcl>hd_fragment valgrind</tcl>
<h2>Valgrind</h2>

<p>[http://valgrind.org/ | Valgrind] is perhaps the most amazing
and useful developer tool in the world.  Valgrind is a simulator - it simulates
an x86 running a Linux binary.  (Ports of Valgrind for platforms other
than Linux are in development, but as of this writing, Valgrind only
works reliably on Linux, which in the opinion of the SQLite developers
means that Linux should be the preferred platform for all software development.)
As Valgrind runs a Linux binary, it looks for all kinds of interesting
errors such as array overruns, reading from uninitialized memory,
stack overflows, memory leaks, and so forth.  Valgrind finds problems
that can easily slip through all of the other tests run against SQLite.
And, when Valgrind does find an error, it can dump the developer directly
into a symbolic debugger at the exact point where the error occur, to
facilitate a quick fix.</p>

<p>Because it is a simulator, running a binary in Valgrind is slower than
running it on native hardware.  (To a first approximation, an application
running in Valgrind on a workstation will perform about the same as it
would running natively on a smartphone.)  So it is impractical to run the full
SQLite test suite through Valgrind.  However, the veryquick tests and
the coverage of the TH3 tests are run through Valgrind prior to every
release.</p>

<tcl>hd_fragment memtesting</tcl>
<h2>Memsys2</h2>

<p>SQLite contains a pluggable
[memory allocation | memory allocation subsystem].
The default implementation uses system malloc() and free().
However, if SQLite is compiled with [SQLITE_MEMDEBUG], an alternative
memory allocation wrapper ([memsys2])
is inserted that looks for memory allocation
errors at run-time.  The memsys2 wrapper checks for memory leaks, of
course, but also looks for buffer overruns, uses of uninitialized memory,
and attempts to use memory after it has been freed.  These same checks
are also done by valgrind (and, indeed, Valgrind does them better)
but memsys2 has the advantage of being much faster than Valgrind, which
means the checks can be done more often and for longer tests.</p>

<tcl>hd_fragment mutextesting</tcl>
<h2>Mutex Asserts</h2>

<p>SQLite contains a pluggable mutex subsystem.  Depending on
compile-time options, the default mutex system contains interfaces
[sqlite3_mutex_held()] and [sqlite3_mutex_notheld()] that detect
whether or not a particular mutex is held by the calling thread.
These two interfaces are used extensively within assert() statements
in SQLite to verify mutexes are held and released at all the right
moments, in order to double-check that SQLite does work correctly
in multi-threaded applications.</p>

<tcl>hd_fragment intoverflow</tcl>
<h2>Undefined Behavior Checks</h2>

<p>In the C programming language, it is very easy to write code that
has "undefined" or "implementation defined" behavior.
That means that the code might work during development, but then give
a different answer on a different system, or when recompiled using different
compiler options.
Examples of undefined and implementation-defined behavior in
ANSI C include:
<ul>
<li>Signed integer overflow.  (Signed integer overflow does <u>not</u>
necessarily wrap around, as most people expect.)
<li>Shifting an N-bit integer by more than N bits.
<li>Shifting by a negative amount.

<p>One verification technique used on SQLite is to run an entire test suite
twice, once with optimizations left on and a second time with optimizations
turned off, and verify that the same output is obtained both times.  This
shows that the optimizations do not introduce errors.</p>

<p>Not all test cases can be handled this way.  Some test cases check
to verify that the optimizations really are reducing the amount of
computation by counting the number of disk accesses, sort operations,
full-scan steps, or other processing steps that occur during queries.
Those test cases will appear to fail when optimizations are disabled.
But the majority of test cases simply check that the correct answer
was obtained, and all of those cases can be run successfully with and
without the optimizations, in order to show that the optimizations do not
cause malfunctions.</p>

<tcl>hd_fragment staticanalysis</tcl>
<h1>Static Analysis</h1>

<p>Static analysis means analyzing source code at compile-time to
check for correctness.  Static analysis includes compiler
warning messages and more in-depth analysis engines such as the
[http://clang-analyzer.llvm.org/ | Clang Static Analyzer].
SQLite compiles without warnings on GCC and Clang using
the -Wall and -Wextra flags on Linux and Mac and on MSVC on Windows.
No valid warnings are generated by the Clang Static Analyzer tool "scan-build"
either (though recent versions of clang seem to generate many false-positives.)
Nevertheless, some warnings might be generated by other
static analyzers.  Users are encouraged not to stress over these
warnings and to instead take solace in the intense testing of SQLite
described above.
</p>

<p>Static analysis has not been helpful in finding
bugs in SQLite.  Static analysis has found a few bugs in SQLite, but
those are the exceptions.  More bugs have been
introduced into SQLite while trying to get it to compile without
warnings than have been found by static analysis.</p>

<tcl>hd_fragment summary</tcl>
<h1>Summary</h1>

<p>SQLite is open source.  This gives many people the idea that
it is not well tested as commercial software and is perhaps unreliable.
But that impression is false.
SQLite has exhibited very high reliability in the field and
a very low defect rate, especially considering how rapidly it is evolving.
The quality of SQLite is achieved in part by careful code design and
implementation.  But extensive testing also plays a vital role in
maintaining and improving the quality of SQLite.  This document has
summarized the testing procedures that every release of SQLite undergoes
with the hope of inspiring confidence that SQLite is
suitable for use in mission-critical applications.</p>

Beginning with [version 3.7.7] ([dateof:3.7.7]),
the SQLite database file argument to the
[sqlite3_open()], [sqlite3_open16()], and [sqlite3_open_v2()] interfaces
and to the [ATTACH] command can be specified
either as an ordinary filename or as a Uniform Resource Identifier or URI.
The advantage of using a URI filename is that query parameters on the URI can
be used to control details of the newly created database connection.
For example, an alternative [VFS] can be specified using a
"vfs=" query parameter.
Or the database can be opened read-only by using "mode=ro" as a query
parameter.
</p>

<h1>Backwards Compatibility</h1>

is first opened, they will not be recognized by [ATTACH].
</p>

<p>
Since SQLite always interprets any filename that does not begin
with "<tt>file:</tt>"
as an ordinary filename regardless of the URI setting, and because it is
very unusual to have an actual file begin with "<tt>file:</tt>",
it is safe for most applications to enable URI processing even if URI
filenames are not currently being used.
</p>

<h1>URI Format</h1>

<p>
According to [http://tools.ietf.org/html/rfc3986 | RFC 3986], a URI consists
of a scheme, an authority, a path, a query string, and a fragment.  The
scheme is always required.  One of either the authority or the path is also
always required.  The query string and fragment are optional.
</p>

<p>
SQLite uses the "<tt>file:</tt>" URI syntax to identify database files.
SQLite strives to interpret file: URIs in exactly the same way as
popular web-browsers such as
[http://www.mozilla.com/en-US/firefox/new/ | Firefox],
[http://www.google.com/chrome/ | Chrome],
[http://www.apple.com/safari/ | Safari],
[http://windows.microsoft.com/en-US/internet-explorer/products/ie/home | Internet Explorer], and
[http://www.opera.com/ | Opera],
and command-line programs such as Windows
[https://learn.microsoft.com/en-us/windows-server/administration/windows-commands/start | "cmd start"]
or
[https://learn.microsoft.com/en-us/powershell/module/microsoft.powershell.management/start-process?view=powershell-7.3 | "powershell start"],
or the macOS "open" or Linux "xdg-open" commands.
A succinct summary of the URI parsing rules follows:
</p>

<ul>
<li> ^(The scheme of the URI must be "<tt>file:</tt>".  Any other scheme
     results in the input being treated as an ordinary filename.)^
<li> ^(The authority may be omitted, may be blank, or may be
      "<tt>localhost</tt>".  Any other authority results in an error.)^
      Exception: If SQLite is compiled with [SQLITE_ALLOW_URI_AUTHORITY]
      then any authority value other than "localhost" is passed through to the
      underlying operating system as a UNC filename.
<li> ^The path is optional if the authority is present.  ^If the authority
     is omitted then the path is required.
<li> ^The query string is optional.  ^If the query string is present, then
      all query parameters are passed through into the xOpen method of
      the underlying [VFS].
<li> ^(The fragment is optional.  If present, it is ignored.)^
</ul>

<p>^Zero or more escape sequences of the form  "<b>%<i>HH</i></b>"
(where <b><i>H</i></b> represents any hexadecimal digit) can occur
in the path, query string, or fragment.</p>

<p>^A filename that is not a well-formed URI is interpreted as an
ordinary filename.</p>

<p>^URIs are processed as UTF8 text.
^The filename argument sqlite3_open16() is converted from UTF16
native byte order into UTF8 prior to processing.

<h2>The URI Path</h2>

<p>^The path component of the URI specifies the disk file that is the
SQLite database to be opened.  ^(If the path component is omitted, then
the database is stored in a temporary file that will be automatically
deleted when the database connection closes.)^  ^If the authority section
is present, then the path is always an absolute pathname.  ^If the
authority section is omitted, then the path is an absolute pathname if it
begins with the "/" character (ASCII code 0x2f) and is a relative
pathname otherwise.  ^(On windows, if the absolute path begins with
"<b>/<i>X</i>:/</b>" where <b><i>X</i></b> is any single ASCII alphabetic
character ("a" through "z" or "A" through "Z") then the "<b><i>X:</i></b>"
is understood to be the drive letter of the volume containing the file,
not the toplevel directory.)^

<p>An ordinary filename can usually be converted into an equivalent URI
by the steps shown below.  The one exception is that a relative windows
pathname with a drive letter cannot be converted directly into a URI; it must
be changed into an absolute pathname first.</p>

<ol>
<li>Convert all "<tt>?</tt>" characters into "<tt>%3f</tt>".
<li>Convert all "<tt>#</tt>" characters into "<tt>%23</tt>".

</p>

<tcl>hd_fragment coreqp *coreqp {standard query parameters} {URI query parameters} \
    {query parameters with special meaning to SQLite}</tcl>
<h2>Recognized Query Parameters</h2>

<p>
Some query parameters are interpreted by the SQLite core and used to
modify the characteristics of the new connection.  ^All query parameters
are always passed through into the xOpen method of the [VFS] even if
they are previously read and interpreted by the SQLite core.
</p>

<p>
The following query parameters are recognized by SQLite as of
[version 3.15.0] ([dateof:3.15.0]).
New query parameters might be added in the future.
</p>

<dl>
<tcl>hd_fragment uricache {"cache" query parameter}</tcl>
<dt><b>cache=shared<br>cache=private</b></dt>
<dd><p>^The cache query parameter determines if the new database is opened
using [shared cache mode] or with a private cache.
</dd>

<tcl>hd_fragment uriimmutable {"immutable" query parameter}</tcl>
<dt><b>immutable=1</b></dt>
<dd><p>^The immutable query parameter is a boolean that signals to
SQLite that the underlying database file is held on read-only media
and cannot be modified, even by another process with elevated
privileges.  ^SQLite always opens immutable database files
read-only and it skips all file locking and change detection
on immutable database files.  If this query parameter (or
the [SQLITE_IOCAP_IMMUTABLE] bit in xDeviceCharacteristics)
asserts that a database file is immutable and that file
changes anyhow, then SQLite might return incorrect query
results and/or [SQLITE_CORRUPT] errors.
</dd>

<tcl>hd_fragment urimode {"mode" query parameter}</tcl>
<dt><b>mode=ro<br>mode=rw<br>mode=rwc<br>mode=memory</b></dt>
<dd><p>^The mode query parameter determines if the new database is opened
read-only, read-write, read-write and created if it does not exist, or

<tcl>hd_fragment urinolock {"nolock" query parameter}</tcl>
<dt><b>nolock=1</b></dt>
<dd><p>^The nolock query parameter is a boolean that disables all calls
to the xLock, xUnlock, and xCheckReservedLock methods of the VFS when true.
The nolock query parameter might be used, for example, when trying to
access a file on a filesystem that does not support file locking.
Caution:  If two or more [database connections] try to interact with
the same SQLite database and one or more of those connections has
enabled "nolock", then database corruption can result.  The "nolock"
query parameter should only be used if the application can guarantee
that writes to the database are serialized.

<tcl>hd_fragment uripsow {"psow" query parameter}</tcl>
<dt><b>psow=0<br>psow=1</b></dt>

/* Container for an image */
.imgcontainer img {
  max-height: 100%;
  max-width: 100%;
}

/* Default fill and stroke for SVG paths */
polygon { fill: black; stroke:#111111; stroke-width:1 }
path { fill: none; stroke:#111111; stroke-width:1 }

.doccat a {
  color: #044a64 ;
  text-decoration: none;
}
.doccat h {
  font-weight: bold;

#
#    $doc parsenode HTML
#      Parse return a new node or nodes.
#
# NODE OBJECT API:
#
#    $node tag
#      Get or set the nodes tag type. Always lower-case. Empty string
#      for text.
#
#    $node children
#      Return a list of the nodes children.
#
#    $node text
#      For a text node, return the text. For any other node, return the
#      concatenation of the text belonging to all descendent text nodes
#      (in document order).
#
#    $node parent
#      Return the nodes parent node.
#
#    $node offset
#      Return the byte offset of the node within the document (if any).
#
#    $node foreach_descendent VARNAME SCRIPT
#      Iterate through all nodes in the sub-tree headed by $node. $node
#      itself is not visited.
#
#    $node attr ?-default VALUE? ATTR ?NEWVALUE?
#
#    $node search PATTERN
#      Return a list of descendent nodes that match pattern PATTERN.
#

  variable iNextid 0

  # Ignore all tags in the aIgnore[] array.
  variable aIgnore
  set aIgnore(html) 1
  set aIgnore(/html) 1
  set aIgnore(!doctype) 1

  # All inline tags.
  variable aInline
  foreach x {
    tt i b big small u
    em strong dfn code samp kbd var cite abbr acronym
    a img object br script map q sub sup span bdo
    input select textarea label button tcl yyterm yynonterm

  set aContentChecker(li)       HtmlLiContent
  set aContentChecker(dt)       HtmlLiContent
  set aContentChecker(dd)       HtmlLiContent
  set aContentChecker(dl)       HtmlDlContent

  # Add content checkers for all self-closing tags.
  foreach x {
    area base br hr iframe img input isindex link meta
    param script style embed nextid wbr bgsound
  } {
    set aContentChecker($x) HtmlEmptyContent
    set aSelfClosing($x) 1
  }

  namespace export parse
  namespace ensemble create
}

  upvar $arrayname O
  foreach c $O($id,children) { create_node_command $arrayname $c }
  return $O($id,children)
}

proc ::hdom::foreach_desc {arrayname id varname script level} {
  upvar $arrayname O
  foreach c $O($id,children) {
    create_node_command $arrayname $c
    uplevel $level [list set $varname $c]

    set rc [catch { uplevel $level $script } msg info]
    if {$rc == 0 || $rc == 4} {
      # TCL_OK or TCL_CONTINUE Do nothing
    } elseif {$rc == 3} {

# Node method [$node offset]
#
proc ::hdom::nm_offset {arrayname id} {
  upvar $arrayname O
  return $O($id,offset)
}

# Node method: $node attr ?-default VALUE? ?ATTR?
#
#   $node attr
#   $node attr ATTR
#   $node attr ATTR NEWVALUE
#   $node attr -default VALUE ATTR
#
proc ::hdom::nm_attr {arrayname id args} {

  if {$P!=""} {
    set idx [lsearch $O($P,children) $id]
    if {$idx<0} {error "internal error!"}
    set O($P,children) [lreplace $O($P,children) $idx $idx]
  }
}

# Node method:
#
#   $node addChild CHILD
#   $node addChild -before BEFORE CHILD
#
proc ::hdom::nm_addChild {arrayname id args} {
  upvar $arrayname O

    set newidx [lsearch $O($id,children) $before]
    if {$newidx < 0 } {error "$before is not a child of $id"}
    set newchild [lindex $args 2]
  }

  # Unlink $newchild from its parent:
  $newchild detach

  # Link $newchild to new parent ($id):
  set O($id,children) [linsert $O($id,children) $newidx $newchild]
  set O($newchild,parent) $id
}

# Document method [$doc root]
#

  #
  set O(current) $root
  set O(root) $root
  set O(cmdlist) [list]

  parsehtml $html [list parsehtml_cb O]

  # Create the document object command.
  #
  proc $doc {method args} [subst -nocommands {
    uplevel ::hdom::document_method $doc [set method] [set args]
  }]
  return $doc
}

    while( *z && *z!='<' ) z++;

    /* Invoke the callback script for the chunk of text just parsed. */
    rc = doTextCallback(interp,aCall,nElem,zText,z-zText,zText-zHtml,z-zHtml);
    if( rc!=TCL_OK ) return rc;

    /* Unless is at the end of the document, z now points to the start of a
    ** markup tag. Either an opening or a closing tag. Parse it up and
    ** invoke the callback script. */
    if( *z ){
      int nTag;
      char *zTag;
      int iOffset;                /* Offset of open tag (the '<' character) */

      assert( *z=='<' );
      iOffset = z - zHtml;
      z++;
      if( *z && 0==strncmp("!--", z, 3) ){
        while( *z && strncmp("-->", z, 3 ) ) z++;
        z += 3;
        continue;
      }
      while( ISSPACE(*z) ) z++;
      zTag = z;

      while( *z && !ISSPACE(*z) && *z!='>' ) z++;
      nTag = z-zTag;

      if( nTag==5 && 0==strncasecmp("style", zTag, 5) ){
        while( *z && strncasecmp("/style>", z, 7 ) ) z++;
        z += 7;
        continue;
      } else if( nTag>=6 && 0==memcmp("script", zTag, 6) ){
        while( *z && strncasecmp("/script>", z, 8 ) ) z++;
        z += 8;
        continue;
      } else if( nTag>=3 && 0==memcmp("svg", zTag, 3) ){
        /* Special cases:
        ** parse <svg>...</svg> as if it were a single big block of text.
        ** This allows the geopoly.html document to be rendered correctly,
        ** and allows the search indexes to be computed in a reasonable
        ** amount of time.
        */

              nVal = z-zVal;
            }
            Tcl_ListObjAppendElement(interp,pParam,Tcl_NewStringObj(zVal,nVal));
          }else if( zAttr ){
            Tcl_ListObjAppendElement(interp, pParam, Tcl_NewIntObj(1));
          }
        }

        rc = doTagCallback(interp,
            aCall, nElem, zTag, nTag, iOffset, 1+z-zHtml, pParam
        );
        if( rc!=TCL_OK ) return rc;

        if( nTag==3 && memcmp(zTag, "tcl", 3)==0 ){
          const char *zText = &z[1];
          while( *z && strncasecmp("</tcl>", z, 6) ) z++;

/*
** 2022 December 31
**
** The author disclaims copyright to this source code.  In place of
** a legal notice, here is a blessing:
**
**    May you do good and not evil.
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file implements a URL checker. See zUsage below for operation.
** It should compile on Linux systems having a certain Curl library.
**   apt install libcurl4-openssl-dev
** Build executable thusly:
**   gcc urlcheck.c -Os -o urlcheck -lcurl
*/

const char *zUsage = "\
Usage: urlcheck [<options>] [<urls>]|[--help]\n\
If URL arguments are provided, they are checked for HTTP server responses.\n\
If no URLs are provided, URLs are read 1 per line from stdin and checked.\n\
Output is bar-separated 3-tuple lines of URL, response_code, code_as_text.\n\
Option --ok-silent suppresses output for URLs yielding an HTTP 200 response.\n\
";
#include <stdio.h>
#include <stdlib.h>
#ifdef __STDC_ALLOC_LIB__
#define __STDC_WANT_LIB_EXT2__ 1
#else
#define _POSIX_C_SOURCE 200809L
#endif
#include <string.h>
#include <curl/curl.h>

typedef struct XfrStatus {
  int iStatus;
  size_t nAlloc;
  char *zText;
} XfrStatus;

static size_t header_sift(char *buf, size_t sz, size_t ni, void *pv){
  XfrStatus *pxs = (XfrStatus *)pv;
  if( ni>6 && strncmp(buf, "HTTP", 4) == 0 ){
    int respcode = 0, i;
    int nr = 0;
    char c = buf[ni-1], cjunk;
    buf[ni-1] = 0;
    for( i=4; i<ni; ++i){
      if( buf[i] == ' ' ) break;
    }
    if( 2 == sscanf(buf+i, "%d%c%n", &respcode, &cjunk, &nr) ){
      pxs->iStatus = respcode;
      if( pxs->zText != 0 ) free(pxs->zText);
      pxs->zText = strdup( &buf[i+nr] );
    }
    buf[ni-1] = c;
  }
  return sz * ni;
}

static size_t body_toss(char *buf, size_t sz, size_t ni, void *pv){
  (void)buf;
  (void)pv;
  return sz * ni;
}

/* Say whether response code is one given by servers rejecting HEAD requests. */
int is_picky_no_response( int rcode ){
  static int aprc[] = { 403, 405, 502, 503 };
  int ix = 0;
  while( ix < sizeof(aprc)/sizeof(int) ){
    if( rcode == aprc[ix] ) return 1;
    ++ix;
  }
  return 0;
}

#define SAY_USER_AGENT "libcurl-agent/1.0"

void one_url( CURL *pCurl, char *zUrl, XfrStatus *pxs, int okhush, int depth){
  CURLcode crc;
  curl_easy_setopt(pCurl, CURLOPT_URL, zUrl);
  pxs->iStatus = 0;
  if( CURLE_OK != (crc = curl_easy_perform(pCurl)) ){
    fprintf(stdout, "%s|%d|%s\n", zUrl, -1, curl_easy_strerror(crc));
  }else{
    char *zRS = pxs->zText;
    if( zRS == 0 ) zRS = "?";
    else if( *zRS < ' ' ){
      switch( pxs->iStatus ){
      case 200: zRS = "OK"; break;
      case 404: zRS = "Not Found"; break;
      case 405: zRS = "Not Allowed"; break;
      case 503: zRS = "Service Unavailable"; break;
      default: zRS = "?";
      }
    }
    /* If request of header only is rejected, do the whole GET anyway. */
    if( pxs->iStatus!=200 && is_picky_no_response(pxs->iStatus) && depth==0 ){
      if( pxs->iStatus == 503 ){
        curl_easy_setopt(pCurl, CURLOPT_USERAGENT, "Mozilla 5.0");
      }
      curl_easy_setopt(pCurl, CURLOPT_NOBODY, 0);
      one_url(pCurl, zUrl, pxs, okhush, depth+1);
      curl_easy_setopt(pCurl, CURLOPT_USERAGENT, SAY_USER_AGENT);
      curl_easy_setopt(pCurl, CURLOPT_NOBODY, 1);
    }else if( !okhush || pxs->iStatus != 200 ){
      fprintf(stdout, "%s|%d|%s\n", zUrl, pxs->iStatus, zRS);
    }
  }
  if( pxs->zText != 0 ){
    free(pxs->zText);
    pxs->zText = 0;
  }
}

int main(int na, char **av){
  int ok_silent = 0;
  XfrStatus xs = { 0, 0, 0 };
  int aix;
  CURL *pCurl = curl_easy_init();

  if( na>=2 && 0==strcmp(av[1], "--ok-silent") ){
    ok_silent = 1;
    --na; ++av;
  }

  curl_easy_setopt(pCurl, CURLOPT_USERAGENT, SAY_USER_AGENT);
  curl_easy_setopt(pCurl, CURLOPT_FOLLOWLOCATION, 1);
  curl_easy_setopt(pCurl, CURLOPT_NOPROGRESS, 1);
  curl_easy_setopt(pCurl, CURLOPT_TIMEOUT_MS, 5000L);
  curl_easy_setopt(pCurl, CURLOPT_SSL_VERIFYHOST, 0);
  curl_easy_setopt(pCurl, CURLOPT_SSL_VERIFYPEER, 0);
  curl_easy_setopt(pCurl, CURLOPT_NOBODY, 1);
  curl_easy_setopt(pCurl, CURLOPT_HEADERDATA, &xs);
  curl_easy_setopt(pCurl, CURLOPT_HEADERFUNCTION, header_sift);
  curl_easy_setopt(pCurl, CURLOPT_WRITEDATA, 0);
  curl_easy_setopt(pCurl, CURLOPT_WRITEFUNCTION, body_toss);
  if( na < 2 ){
    char lbuf[1000];
    while( 0 != fgets(lbuf, sizeof(lbuf), stdin) ){
      int nbi, nbe;
      for( nbi=0; nbi<sizeof(lbuf) && lbuf[nbi]; ++nbi )
        if( lbuf[nbi]!=' ' ) break;
      for( nbe=nbi; nbe<sizeof(lbuf) && lbuf[nbe]; ++nbe )
        if( lbuf[nbe]==' ' || lbuf[nbe] == '\n' ) break;
      if( nbi==sizeof(lbuf) || nbe==nbi ) continue;
      lbuf[nbe--] = 0;
      if( nbe==nbi ) continue;
      one_url( pCurl, &lbuf[nbi], &xs, ok_silent, 0 );
    }
  }else{
    if( na==2 && strcmp(av[1],"--help")==0 ){
      fprintf(stdout, "%s", zUsage);
    }else{
      for( aix=1; aix < na; ++aix ){
        one_url( pCurl, av[aix], &xs, ok_silent, 0 );
      }
    }
  }
  curl_easy_cleanup(pCurl);
  return 0;
}

  puts $hd(main) "<a name=\"$name\"></a>"
  if {$hd(enable-aux)} {
    puts $hd(aux) "<a name=\"$name\"></a>"
    set hd(aux-fragment) $name
  }
  eval hd_keywords $args
}

# Current output doc path sans tail.
proc out_dir {} {
  global hd
  if {$hd(enable-aux)} {
    return $hd(rootpath-aux)
  } else {
    return $hd(rootpath-main)
  }
}

# Pre-filtering and funneling for added backlinks.
proc backlink_add {t r} {
  # Filter out self-references for obviousness.
  if {$t eq $r} return
  gather_link $t $r BACK
}

# Pre-filtering and funneling for added pagelinks.
proc pagelink_add {t r} {
  # Do not add compendium refs to the compendia. Too obvious and useless.
  if {[regexp {^doc_.*} $r]} return
  if {$t eq $r} return
  # Fixup (useless and duplicate-generating) relative-to-. page links.
  set t [regsub {^\.\/} $t ""]
  gather_link $t $r PAGE
}

# Write raw output to both the main file and the auxiliary.
# Only write after first pass to files that are enabled.
#
proc hd_puts {text} {
  if {$::scan_pass < 2} return

  # ::llink, ::glink, and ::backlink generated during hd_resolve
  # processing doesn't catch links output directly with hd_puts.
  # This code adds those links to our pagelink array, ::pagelink.
  set refs [regexp -all -inline {href=\"(.*?)\"} $text]
  foreach {href ref} $refs {
    regsub {#.*} $ref {} ref2
    regsub {http:\/\/www\.sqlite\.org\/} $ref2 {} ref3
    if {[regexp {^checklists\/} $ref3]} continue
    regsub {\.\.\/} $ref3 {} ref4
    if {[regexp {^http} $ref4]} continue
    if {$ref4==""} continue
    if {[regexp {\.html$} $ref4]} {
      pagelink_add $ref4 $fn
    }
  }

  global hd
  hd_close_aux
  if {[info exists hd(main)]} {
    puts $hd(main) $hd(mtime-msg)
    puts $hd(main) $hd(footer)
    close $hd(main)
    unset hd(main)
    set hd(rootpath-main) ""
  }
}

# Open the auxiliary output file.
#
# Most documents have only a main file and no auxiliary.  However, some
# large documents are broken up into smaller pieces where each smaller piece

proc hd_close_aux {} {
  global hd
  if {[info exists hd(aux)]} {
    puts $hd(aux) $hd(mtime-msg)
    puts $hd(aux) $hd(footer)
    close $hd(aux)
    unset hd(aux)
    unset hd(fn-aux)
    set hd(enable-aux) 0
    set hd(enable-main) 1
  }
}

# Pages call this routine to suppress the bottom "Page Last Modified" message.
#

  if {[catch {eval hd_puts "\173$in\175"} err]} {
    puts "\nERROR in $infile"
    puts [string range "$::errorInfo\n$in" 0 200]
    exit 1
  }
  cd $::HOMEDIR
  hd_close_main
  set ::currentInfid 0
  set ::currentInfile ""
}
if {!($::doc_build_stats & 1)} {puts ""}

# Undo the proc define/use instrumentation setup above.
if {($::doc_build_stats & 8) != 0} {
  rename proc ""
  rename built-in-proc proc

  hd_header $title $infile
  regsub -all {<tcl>} $in "\175; eval \173" in
  regsub -all {</tcl>} $in "\175; hd_resolve \173" in
  eval "hd_resolve \173$in\175"
  footer_too
  cd $::HOMEDIR
  hd_close_main
  set ::currentInfid 0
  set ::currentInfile ""
}
if {!($::doc_build_stats & 1)} {puts ""}

set ::scan_pass 3
# Processing from here on uses only variables already set.
# No further link sets are kept in the docinfo.db DB.

normalize_apparray ::pagelink

foreach y [lsort [array names ::pagelink]] {
  if {[tossable_xref $y]} continue
  if {$::doc_build_stats > 0 && ![file exists "doc/$y"]} {
    puts stderr "Goofy pagelink $y"
  }
  set plo [list]
  foreach ref [lsort -unique $::pagelink($y)] {
    if {$ref==$y || [tossable_xref $ref]} continue
    lappend plo $ref
  }
  if {[llength $plo] == 0} continue
  regsub {^\.\./} $y {} sy
  hd_putsnl "<li><a href=\"$y\">$sy</a> &rarr; "
  foreach ref $plo {
    regsub {^\.\./} $ref {} sref
    hd_puts "<a href=\"$ref\">$sref</a> "
  }
  hd_putsnl "</li>"
}
hd_puts "</ul>"
hd_close_main

if {$::doc_build_stats > 0} {