Documentation Source Text

<title>The SQLite Amalgamation</title>
<tcl>hd_keywords {amalgamation} {the amalgamation}</tcl>

<h2>1.0 The SQLite Amalgamation</h2>

<p>The core SQLite library consists of 102 files of C code
(as of [Version 3.8.8]) in the core with 18 additional files

in the [FTS3] and [RTREE] extensions.
Most of these are "source" files in the sense that they are stored 

in the configuration management system and are maintained directly. 
But 6 of the core C files are generated automatically using scripts
or auxiliary programs during the compilation process.  Of the 102 
core files, 75 are C code and 26 are C header files.</p>


<p>The standard makefiles for SQLite have a target for building
an object we call the "amalgamation".  
The amalgamation is a single C code file, named "sqlite3.c",
that contains all C code 
for the core SQLite library and the [FTS3] and [RTREE] extensions.

This file contains about 153K lines of code 
(93K if you omit blank lines and comments) and is over 5.4 megabytes
in size.</p>













<p>The amalgamation contains everything you need to integrate SQLite 
into a larger project.  Just copy the amalgamation into your source 
directory and compile it along with the other C code files in your project.
(A [how to compile|more detailed discussion] of the compilation process is
available.)
You may also want to make use of the "sqlite3.h" header file that 
................................................................................
function inlining which tends to make the size of the resulting
binary image larger.</p>

<tcl>hd_fragment amal32k {split amalgamation}</tcl>
<h2>2.0 The Split Amalgamation</h2>

<p>Developers sometimes experience difficulting debugging the
150,000-line-long amalgamation source file because some debuggers
are only able to handle source code line numbers less than 32,768.
The amalgamation source code runs fine.  One just cannot single-step
through it in a debugger.

<p>To circumvent this limitation, the amalgamation is also available in
a split form, consisting of files "sqlite3-1.c", "sqlite3-2.c", and
so forth, where each file is less than 32,768 lines in length and
................................................................................

<blockquote><pre>
#include "sqlite3-1.c"
#include "sqlite3-2.c"
#include "sqlite3-3.c"
#include "sqlite3-4.c"
#include "sqlite3-5.c"


</pre></blockquote>

<p>Applications using the split amalgamation simply compile against
"sqlite3-all.c" instead of "sqlite3.c".  The two files work exactly
the same.  But with "sqlite3-all.c", no single source file contains more
than 32,767 lines of code, and so it is more convenient to use some
debuggers.  The downside of the split amalgamation is that it consists

<title>The SQLite Amalgamation</title>
<tcl>hd_keywords {amalgamation} {the amalgamation}</tcl>

<h2>1.0 The SQLite Amalgamation</h2>

<p>The SQLite library consists of 102 files of C code
(as of [Version 3.9.0]) in the core with 32 additional files that
implement the [FTS3], [FTS5], [RTREE], [dbstat|DBSTAT], [json1|JSON1], and
[RBU] extensions.
Most of these are "source" files in the sense that they are stored 
in the [https://www.sqlite.org/src | SQLite version control system]
and are maintained directly. 
But some of the C files are generated automatically using scripts
or auxiliary programs during the compilation process.  Of the 102 

main source files, about 75% are C code and about 25% are C header files.</p>

<p>The standard makefiles for SQLite have a target for building
an object we call the "amalgamation".  
The amalgamation is a single C code file, named "sqlite3.c",
that contains all C code 
for the core SQLite library and the [FTS3], [FTS5], [RTREE],
[dbstat|DBSTAT], [json1|JSON1], and [RBU] extensions.
This file contains about 184K lines of code 
(113K if you omit blank lines and comments) and is over 6.4 megabytes
in size.
Though the the various extensions are included in the
"sqlite3.c" amalgamation file, they are disabled using #ifdef statements.
Activate the extensions using [compile-time options] like:

<ul>
<li> [-DSQLITE_ENABLE_FTS3]
<li> [-DSQLITE_ENABLE_FTS5]
<li> [-DSQLITE_ENABLE_RTREE]
<li> [-DSQLITE_ENABLE_DBSTAT_VTAB]
<li> [-DSQLITE_ENABLE_JSON1]
<li> [-DSQLITE_ENABLE_RBU]
</ul>

<p>The amalgamation contains everything you need to integrate SQLite 
into a larger project.  Just copy the amalgamation into your source 
directory and compile it along with the other C code files in your project.
(A [how to compile|more detailed discussion] of the compilation process is
available.)
You may also want to make use of the "sqlite3.h" header file that 
................................................................................
function inlining which tends to make the size of the resulting
binary image larger.</p>

<tcl>hd_fragment amal32k {split amalgamation}</tcl>
<h2>2.0 The Split Amalgamation</h2>

<p>Developers sometimes experience difficulting debugging the
185,000-line-long amalgamation source file because some debuggers
are only able to handle source code line numbers less than 32,768.
The amalgamation source code runs fine.  One just cannot single-step
through it in a debugger.

<p>To circumvent this limitation, the amalgamation is also available in
a split form, consisting of files "sqlite3-1.c", "sqlite3-2.c", and
so forth, where each file is less than 32,768 lines in length and
................................................................................

<blockquote><pre>
#include "sqlite3-1.c"
#include "sqlite3-2.c"
#include "sqlite3-3.c"
#include "sqlite3-4.c"
#include "sqlite3-5.c"
#include "sqlite3-6.c"
#include "sqlite3-7.c"
</pre></blockquote>

<p>Applications using the split amalgamation simply compile against
"sqlite3-all.c" instead of "sqlite3.c".  The two files work exactly
the same.  But with "sqlite3-all.c", no single source file contains more
than 32,767 lines of code, and so it is more convenient to use some
debuggers.  The downside of the split amalgamation is that it consists

with non-standard compilation options.
</p>

<tcl>
proc COMPILE_OPTION {name text} {
  if {[regexp {(SQLITE|HAVE)_([A-Z0-9_]+)} $name all prefix label]} {
    hd_fragment [string tolower $label]
    hd_keywords $all
  }
  if {[regexp {^YY([A-Z0-9_]+)} $name all label]} {
    hd_fragment [string tolower $all]
    hd_keywords $all
  }
  hd_puts <p><b>$name</b></p>
  regsub -all "\n\\s*\n" $text "</p>\n\n<p>" text

with non-standard compilation options.
</p>

<tcl>
proc COMPILE_OPTION {name text} {
  if {[regexp {(SQLITE|HAVE)_([A-Z0-9_]+)} $name all prefix label]} {
    hd_fragment [string tolower $label]
    hd_keywords $all -D$all
  }
  if {[regexp {^YY([A-Z0-9_]+)} $name all label]} {
    hd_fragment [string tolower $all]
    hd_keywords $all
  }
  hd_puts <p><b>$name</b></p>
  regsub -all "\n\\s*\n" $text "</p>\n\n<p>" text

<li> <a href="quickstart.html">Getting Started</a> </li>
<li> <a href="lang.html">SQL Syntax</a>
<ul>
<li> <a href="pragma.html#toc">Pragmas</a>
<li> <a href="lang_corefunc.html">SQL functions</a>
<li> <a href="lang_datefunc.html">Date &amp; time functions</a>
<li> <a href="lang_aggfunc.html">Aggregate functions</a>

</ul>
</li>
<li> <a href="c3ref/intro.html">C/C++ Interface Spec</a>
<ul>
<li> <a href="cintro.html">Introduction</a>
<li> <a href="c3ref/funclist.html">List of C-language APIs</a>
</ul>

<li> <a href="quickstart.html">Getting Started</a> </li>
<li> <a href="lang.html">SQL Syntax</a>
<ul>
<li> <a href="pragma.html#toc">Pragmas</a>
<li> <a href="lang_corefunc.html">SQL functions</a>
<li> <a href="lang_datefunc.html">Date &amp; time functions</a>
<li> <a href="lang_aggfunc.html">Aggregate functions</a>
<li> <a href="json1.html">JSON functions</a>
</ul>
</li>
<li> <a href="c3ref/intro.html">C/C++ Interface Spec</a>
<ul>
<li> <a href="cintro.html">Introduction</a>
<li> <a href="c3ref/funclist.html">List of C-language APIs</a>
</ul>

<tcl>hd_keywords json1 {the json1 extension} {JSON SQL functions}</tcl>
<h2>The JSON1 Extension</h2>

<p>
The <b>json1</b> extension is a [loadable extension] that
implements eleven [application-defined SQL functions] and
two [table-valued functions] that are useful for
managing JSON content stored in an SQLite database.
These are the SQL functions implemented by json1:

<blockquote>
<center><table border=0 cellpadding=5>
<tcl>
set tabcnt 0
proc tabentry {fx desc lnk} {
................................................................................

<p>
The [loadable extensions] documentation contains instructions on 
how to [compile loadable extensions] as shared libraries.  The
techniques described there work fine for the json1 module.

<p>
The json1 module can also be statically linked against SQLite in
either of two ways:

<ol>
<li><p>
Compile the ext/misc/json1.c source file separately using the
additional -DSQLITE_CORE compile-time option.  Then link the
resulting json1.o object file with the application.

<li><p>
Append the ext/misc/json1.c source file onto the end of a standard
[amalgamation|sqlite3.c amalgamation] source file and compile them
together.  No -DSQLITE_CORE compile-time option is needed in this
case since the SQLITE_CORE C preprocessor macro is defined 
by the code in sqlite3.c.
</ol>

<p>
In both cases, one can add the -DSQLITE_ENABLE_JSON1 compile-time
option while compiling sqlite3.c file.  The SQLITE_ENABLE_JSON1 option
causes SQLite to automatically register the json1 extension with each
new connection that is opened.

<p>
Note that the [command-line shell] already statically links json1 when
built using any of the standard makefiles.  So the JSON functions described
here are automatically available in the command-line shell.



<h2>2.0 Interface Overview</h2>

<p>
The json1 extension (currently) stores JSON as ordinary text.

<p>
................................................................................
string is passed into json1 function, that function will usually throw
an error.  (An exception is the json_valid(X) 
function which returns 1 if X is well-formed JSON and 0 if it is not.)

<p>
For the purposes of determining validity, leading and trailing whitespace
on JSON inputs is ignored.  Interior whitespace is also ignored, in accordance
with the JSON spec.  These routines accept exactly the rfc-7159 JSON syntax

&mdash; no more and no less.

<h3>2.2 PATH arguments</h3>

<p>
For functions that accept PATH arguments, that PATH must be well-formed or
else the function will throw an error.
................................................................................
come directly from another json1 function.

<h3>2.4 Compatibility</h3>

<p>
The json1 extension uses the [sqlite3_value_subtype()] and
[sqlite3_result_subtype()] interfaces that were introduced with
SQLite version 3.8.12.  Therefore the json1 extension will not work
in earlier versions of SQLite.

<h2>3.0 Function Details</h2>

<p>The following sections provide additional detail on the operation of
the various functions that are part of the json1 extension.

<tcl>hd_keywords json1 {the json1 extension} {JSON SQL functions}</tcl>
<h2>The JSON1 Extension</h2>

<p>
The <b>json1</b> extension is a [loadable extension] that
implements eleven [application-defined SQL functions] and
two [table-valued functions] that are useful for
managing [http://json.org/ | JSON] content stored in an SQLite database.
These are the SQL functions implemented by json1:

<blockquote>
<center><table border=0 cellpadding=5>
<tcl>
set tabcnt 0
proc tabentry {fx desc lnk} {
................................................................................

<p>
The [loadable extensions] documentation contains instructions on 
how to [compile loadable extensions] as shared libraries.  The
techniques described there work fine for the json1 module.

<p>
The json1 source code is included with the SQLite [amalgamation], though
it is turned off by default.  Add the [-DSQLITE_ENABLE_JSON1] compile-time


























option to enable the json1 extension that is built into the [amalgamation].

<h2>2.0 Interface Overview</h2>

<p>
The json1 extension (currently) stores JSON as ordinary text.

<p>
................................................................................
string is passed into json1 function, that function will usually throw
an error.  (An exception is the json_valid(X) 
function which returns 1 if X is well-formed JSON and 0 if it is not.)

<p>
For the purposes of determining validity, leading and trailing whitespace
on JSON inputs is ignored.  Interior whitespace is also ignored, in accordance
with the JSON spec.  These routines accept exactly the 
[http://www.rfc-editor.org/rfc/rfc7159.txt | rfc-7159 JSON syntax]
&mdash; no more and no less.

<h3>2.2 PATH arguments</h3>

<p>
For functions that accept PATH arguments, that PATH must be well-formed or
else the function will throw an error.
................................................................................
come directly from another json1 function.

<h3>2.4 Compatibility</h3>

<p>
The json1 extension uses the [sqlite3_value_subtype()] and
[sqlite3_result_subtype()] interfaces that were introduced with
SQLite version 3.9.0.  Therefore the json1 extension will not work
in earlier versions of SQLite.

<h2>3.0 Function Details</h2>

<p>The following sections provide additional detail on the operation of
the various functions that are part of the json1 extension.

<p>^If a <yyterm>column-name</yyterm> list follows 
the <yyterm>view-name</yyterm>, then that list determines
the names of the columns for the view.  ^If the <yyterm>column-name</yyterm>
list is omitted, then the names of the columns in the view are derived
from the names of the result-set columns in the [select-stmt].
Note that the <yyterm>column-name</yyterm> list syntax is only
supported in SQLite versions 3.8.12 and later.

<tcl>
##############################################################################
Section {CREATE VIRTUAL TABLE} {createvtab} {{CREATE VIRTUAL TABLE}}

RecursiveBubbleDiagram create-virtual-table-stmt
</tcl>
................................................................................
  hd_puts {<td valign="top">}
  hd_resolve $desc
  hd_puts {</td></tr>}
}
</tcl>

<p>The core functions shown below are available by default. 
[datefunc | Date &amp; Time functions] and

[aggfunc | aggregate functions] are documented separately.  An
application may define additional
functions written in C and added to the database engine using
the [sqlite3_create_function()] API.</p>

<table border=0 cellpadding=10>
<tcl>
funcdef {abs(X)} {} {

<p>^If a <yyterm>column-name</yyterm> list follows 
the <yyterm>view-name</yyterm>, then that list determines
the names of the columns for the view.  ^If the <yyterm>column-name</yyterm>
list is omitted, then the names of the columns in the view are derived
from the names of the result-set columns in the [select-stmt].
Note that the <yyterm>column-name</yyterm> list syntax is only
supported in SQLite versions 3.9.0 and later.

<tcl>
##############################################################################
Section {CREATE VIRTUAL TABLE} {createvtab} {{CREATE VIRTUAL TABLE}}

RecursiveBubbleDiagram create-virtual-table-stmt
</tcl>
................................................................................
  hd_puts {<td valign="top">}
  hd_resolve $desc
  hd_puts {</td></tr>}
}
</tcl>

<p>The core functions shown below are available by default. 
[datefunc | Date &amp; Time functions],
[aggfunc | aggregate functions], and
[json1 | JSON functions] are documented separately.  An
application may define additional
functions written in C and added to the database engine using
the [sqlite3_create_function()] API.</p>

<table border=0 cellpadding=10>
<tcl>
funcdef {abs(X)} {} {

  rbu_control argument with the first character (the one corresponding to
  column "i", which is not required by the FTS table) removed.

<tcl>hd_fragment sqldiff {sqldiff --rbu}</tcl>
<h3>Automatically Generating RBU Updates with sqldiff</h3>

<p>
  As of SQLite version 3.8.12, the [sqldiff] utility is able to generate
  RBU databases representing the difference between two databases with
  identical schemas. For example, the following command:

<tcl>CODE {
sqldiff --rbu t1.db t2.db
}</tcl>

  rbu_control argument with the first character (the one corresponding to
  column "i", which is not required by the FTS table) removed.

<tcl>hd_fragment sqldiff {sqldiff --rbu}</tcl>
<h3>Automatically Generating RBU Updates with sqldiff</h3>

<p>
  As of SQLite version 3.9.0, the [sqldiff] utility is able to generate
  RBU databases representing the difference between two databases with
  identical schemas. For example, the following command:

<tcl>CODE {
sqldiff --rbu t1.db t2.db
}</tcl>

<p>If the [xCreate] method is NULL, then
[CREATE VIRTUAL TABLE] statements are prohibited for that virtual table,
and the virtual table is an "eponymous-only virtual table".
Eponymous-only virtual tables are useful as 
[table-valued functions].

<p>
Note that SQLite versions prior to 3.8.12 did not check the xCreate method
for NULL before invoking it.  So if an eponymous-only virtual table is
registered with SQLite version 3.8.11.1 or earlier and a [CREATE VIRTUAL TABLE]
command is attempted against that virtual table module, a jump to a NULL
pointer will occur, resulting in a crash.

<h3>1.2 Implementation</h3>

................................................................................
been reported up to the application.

<p>
If the xCreate method is omitted (left as a NULL pointer) then the
virtual table is an [eponymous-only virtual table].  New instances of
the virtual table cannot be created using [CREATE VIRTUAL TABLE] and the
virtual table can only be used via its module name.
Note that SQLite versions prior to 3.8.12 do not understand
eponymous-only virtual tables and will segfault if an attempt is made
to [CREATE VIRTUAL TABLE] on an eponymous-only virtual table because
the xCreate method was not checked for null.

<p>
If the xCreate method is the exact same pointer as the [xConnect] method,
that indicates that the virtual table does not need to initialize backing
................................................................................
    int idxNum;                /* Number used to identify the index */
    char *idxStr;              /* String, possibly obtained from sqlite3_malloc */
    int needToFreeIdxStr;      /* Free idxStr using sqlite3_free() if true */
    int orderByConsumed;       /* True if output is already ordered */
    double estimatedCost;      /* Estimated cost of using this index */
    <b>/* Fields below are only available in SQLite 3.8.2 and later */</b>
    sqlite3_int64 estimatedRows;    /* Estimated number of rows returned */


  };
</pre></blockquote>

<p>Please note the warning above the "estimatedRows" field. This field was
added to the structure beginning with SQLite version 3.8.2. This means that any
extension that reads or writes this field must first check that the version of
the SQLite library in use is greater than or equal to 3.8.2 - perhaps using
a call to [sqlite3_version()]. The results of attempting to access this field

within a structure created by an older version of SQLite are undefined.

<p>In addition, there are some defined constants:

<blockquote><pre>
  #define SQLITE_INDEX_CONSTRAINT_EQ    2
  #define SQLITE_INDEX_CONSTRAINT_GT    4
  #define SQLITE_INDEX_CONSTRAINT_LE    8
  #define SQLITE_INDEX_CONSTRAINT_LT    16
  #define SQLITE_INDEX_CONSTRAINT_GE    32
  #define SQLITE_INDEX_CONSTRAINT_MATCH 64

</pre></blockquote>

<p>The SQLite core calls the xBestIndex method when it is compiling a query
that involves a virtual table. In other words, SQLite calls this method 
when it is running [sqlite3_prepare()] or the equivalent. 
By calling this method, the 
SQLite core is saying to the virtual table that it needs to access 
................................................................................
multiple times with different constraints, obtain multiple cost
estimates, then choose the query plan that gives the lowest estimate.

<p>If the current version of SQLite is 3.8.2 or greater, the estimatedRows
field may be set to an estimate of the number of rows returned by the
proposed query plan. If this value is not explicitly set, the default 
estimate of 25 rows is used.






<p>The aConstraintUsage[] array contains one element for each of 
the nConstraint constraints in the inputs section of the 
[sqlite3_index_info] structure. 
The aConstraintUsage[] array is used by xBestIndex to tell the 
core how it is using the constraints.

<p>If the [xCreate] method is NULL, then
[CREATE VIRTUAL TABLE] statements are prohibited for that virtual table,
and the virtual table is an "eponymous-only virtual table".
Eponymous-only virtual tables are useful as 
[table-valued functions].

<p>
Note that SQLite versions prior to 3.9.0 did not check the xCreate method
for NULL before invoking it.  So if an eponymous-only virtual table is
registered with SQLite version 3.8.11.1 or earlier and a [CREATE VIRTUAL TABLE]
command is attempted against that virtual table module, a jump to a NULL
pointer will occur, resulting in a crash.

<h3>1.2 Implementation</h3>

................................................................................
been reported up to the application.

<p>
If the xCreate method is omitted (left as a NULL pointer) then the
virtual table is an [eponymous-only virtual table].  New instances of
the virtual table cannot be created using [CREATE VIRTUAL TABLE] and the
virtual table can only be used via its module name.
Note that SQLite versions prior to 3.9.0 do not understand
eponymous-only virtual tables and will segfault if an attempt is made
to [CREATE VIRTUAL TABLE] on an eponymous-only virtual table because
the xCreate method was not checked for null.

<p>
If the xCreate method is the exact same pointer as the [xConnect] method,
that indicates that the virtual table does not need to initialize backing
................................................................................
    int idxNum;                /* Number used to identify the index */
    char *idxStr;              /* String, possibly obtained from sqlite3_malloc */
    int needToFreeIdxStr;      /* Free idxStr using sqlite3_free() if true */
    int orderByConsumed;       /* True if output is already ordered */
    double estimatedCost;      /* Estimated cost of using this index */
    <b>/* Fields below are only available in SQLite 3.8.2 and later */</b>
    sqlite3_int64 estimatedRows;    /* Estimated number of rows returned */
    <b>/* Fields below are only available in SQLite 3.9.0 and later */</b>
    int idxFlags;              /* Mask of SQLITE_INDEX_SCAN_* flags */
  };
</pre></blockquote>

<p>Please note the warnings on the "estimatedRows" and "idxFlags" field.
These fields were added with SQLite versions 3.8.2 and 3.9.0, respectively. 
Any extension that reads or writes these fields must first check that the 
version of the SQLite library in use is greater than or equal to 3.8.2 or
3.9.0 - perhaps using a call to [sqlite3_version()]. The result of attempting 
to access these fields in an sqlite3_index_info structure created by an 
older version of SQLite are undefined.

<p>In addition, there are some defined constants:

<blockquote><pre>
  #define SQLITE_INDEX_CONSTRAINT_EQ    2
  #define SQLITE_INDEX_CONSTRAINT_GT    4
  #define SQLITE_INDEX_CONSTRAINT_LE    8
  #define SQLITE_INDEX_CONSTRAINT_LT    16
  #define SQLITE_INDEX_CONSTRAINT_GE    32
  #define SQLITE_INDEX_CONSTRAINT_MATCH 64
  #define SQLITE_INDEX_SCAN_UNIQUE      1     /* Scan visits at most 1 row */
</pre></blockquote>

<p>The SQLite core calls the xBestIndex method when it is compiling a query
that involves a virtual table. In other words, SQLite calls this method 
when it is running [sqlite3_prepare()] or the equivalent. 
By calling this method, the 
SQLite core is saying to the virtual table that it needs to access 
................................................................................
multiple times with different constraints, obtain multiple cost
estimates, then choose the query plan that gives the lowest estimate.

<p>If the current version of SQLite is 3.8.2 or greater, the estimatedRows
field may be set to an estimate of the number of rows returned by the
proposed query plan. If this value is not explicitly set, the default 
estimate of 25 rows is used.

<p>If the current version of SQLite is 3.9.0 or greater, the idxFlags field
may be set to SQLITE_INDEX_SCAN_UNIQUE to indicate that the virtual table
will return only zero or one rows given the input constraints.  Additional
bits of the idxFlags field might be understood in later versions of SQLite.

<p>The aConstraintUsage[] array contains one element for each of 
the nConstraint constraints in the inputs section of the 
[sqlite3_index_info] structure. 
The aConstraintUsage[] array is used by xBestIndex to tell the 
core how it is using the constraints.

    set gurl {}
    if {$hd(fragment)!=""} {
      set lurl $hd(fn-main)#$hd(fragment)
    }
  }
  set override_flag 0
  foreach a $args {
    if {[regexp {^-+(.*)} $a all param]} {
      switch $param {
        "override" {
           set override_flag 1
        }
        default {
           puts stderr "ERROR: unknown parameter: $a"
        }

    set gurl {}
    if {$hd(fragment)!=""} {
      set lurl $hd(fn-main)#$hd(fragment)
    }
  }
  set override_flag 0
  foreach a $args {
    if {[regexp {^-+(.*)} $a all param] && ![regexp {^-D} $a]} {
      switch $param {
        "override" {
           set override_flag 1
        }
        default {
           puts stderr "ERROR: unknown parameter: $a"
        }