Documentation Source Text

<li>Add support for [generated columns].
<li>Add the [sqlite3_hard_heap_limit64()] interface and the corresponding
    [PRAGMA hard_heap_limit] command.
<li>Add the [DBSTAT aggregated mode|aggregated mode] feature to the
    [DBSTAT virtual table].
<li>Add the [SQLITE_OPEN_NOFOLLOW] option to [sqlite3_open_v2()] that
    prevents SQLite from opening symbolic links.
<li>Added the "#-N" array notation for [JSON function path arguments].
<li>Faster response to [sqlite3_interrupt()].
<li>Added the [https://sqlite.org/src/file/ext/misc/uuid.c|uuid.c] extension module
    implementing functions for processing RFC-4122 UUIDs.
<li>The [legacy_file_format pragma] is deactivated.  It is now a no-op.  In its place,
    the [SQLITE_DBCONFIG_LEGACY_FILE_FORMAT] option to [sqlite3_db_config()] is
    provided.  The legacy_file_format pragma is deactivated because (1) it is
    rarely useful and (2) it is incompatible with [VACUUM] in schemas that have

<title>The DBSTAT Virtual Table</title>
<tcl>hd_keywords dbstat {dbstat virtual table} {DBSTAT virtual table}</tcl>
<fancy_format>


<h1>Overview</h1>

<p>
The DBSTAT virtual table is a read-only [eponymous virtual table] that returns
information about the amount of disk space used to store the content
of an SQLite database.
Example use cases for the
DBSTAT virtual table include the [sqlite3_analyzer.exe]
utility program and the
[https://www.sqlite.org/src/repo-tabsize|table size pie-chart] in
the [https://www.fossil-scm.org/|Fossil-implemented] version control system
for SQLite.
</p>

<p>
^The DBSTAT virtual table is available on all 
[database connections] when SQLite is built using the
[SQLITE_ENABLE_DBSTAT_VTAB] compile-time option.

<p>
The schema for the DBSTAT virtual table looks like this:
<codeblock>
CREATE TABLE dbstat(
  name       TEXT,        -- Name of table or index
  path       TEXT,        -- Path to page from root
  pageno     INTEGER,     -- Page number, or page count
  pagetype   TEXT,        -- 'internal', 'leaf', 'overflow', or NULL
  ncell      INTEGER,     -- Cells on page (0 for overflow pages)
  payload    INTEGER,     -- Bytes of payload on this page or btree
  unused     INTEGER,     -- Bytes of unused space on this page or btree
  mx_payload INTEGER,     -- Largest payload size of all cells on this row
  pgoffset   INTEGER,     -- Byte offset of the page in the database file
  pgsize     INTEGER,     -- Size of the page, in bytes
  schema     TEXT HIDDEN, -- Database schema being analyzed
  aggregate  BOOL HIDDEN  -- True to enable aggregate mode
);
</codeblock>

<p>
The DBSTAT table only reports on the content of btrees within the database file.
Freelist pages, pointer-map pages, and the lock page are omitted from
the analysis.

<p>
By default, there is a single row in the DBSTAT table for each
btree page the database file.  Each row provides
information about the space utilization of that one page of the
database.  However, if the hidden column "aggregate" is TRUE, then
results are aggregated and there is a single row in the DBSTAT table
for each btree in the database, providing information about space
utilization across the entire btree.

<a name="dbstatpath"></a>

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

<tcl>hd_fragment jsonpath {JSON paths} {JSON function path arguments}</tcl>
<h2>PATH arguments</h2>

<p>
For functions that accept PATH arguments, that PATH must be well-formed or
else the function will throw an error.
A well-formed PATH is a text value that begins with exactly one
'$' character followed by zero or more instances
of ".<i>objectlabel</i>" or "&#91<i>arrayindex</i>&#93".

<p>
The <i>arrayindex</i> is usually a non-negative integer <i>N</i>.  In
that case, the array element selected is the <i>N</i>-th element
of the array, starting with zero on the left.
The <i>arrayindex</i> can also be of the form "<b>#-</b><i>N</i>"
in which case the element selected is the <i>N</i>-th from the
right.  The last element of the array is "<b>#-1</b>".  Think of
the "#" characters as the "number of elements in the array".  Then
the expression "#-1" evaluates is the integer that corresponds to 
the last entry in the array.

<h2>VALUE arguments</h2>

<p>
For functions that accept "<i>value</i>" arguments (also shown as
"<i>value1</i>" and "<i>value2</i>"),
those arguments is usually understood
to be a literal strings that are quoted and becomes JSON string values

      {'{"a":2,"c":[4,5,{"f":7}]}'} \
  {json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.c')} \
      {'[4,5,{"f":7}]'} \
  {json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.c[2]')} \
      {'{"f":7}'} \
  {json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.c[2].f')} {7} \
  {json_extract('{"a":2,"c":[4,5],"f":7}','$.c','$.a')} {'[[4,5],2]'} \
  {json_extract('{"a":2,"c":[4,5],"f":7}','$.c[#-1]'} {5} \
  {json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.x')} NULL \
  {json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.x', '$.a')} {'[null,2]'}
</tcl>  

<tcl>hd_fragment jins {json_insert SQL function} {json_insert}</tcl>
<tcl>hd_fragment jrepl {json_replace SQL function} {json_replace}</tcl>
<tcl>hd_fragment jset {json_set SQL function} {json_set}</tcl>

then it is interpreted as JSON and is inserted as JSON retaining all
of its substructure.

<p>These routines throw an error if the first JSON argument is not
well-formed or if any PATH argument is not well-formed or if any
argument is a BLOB.

<p>To append an element onto the end of an array, using json_insert()
with an array index of "#".  Examples:

<tcl>
jexample \
  {json_insert('[1,2,3,4]','$[#]',99)} {'[1,2,3,4,99]'} \
  {json_insert('[1,[2,3],4]','$[1][#]',99)} {'[1,[2,3,99],4]'}
</tcl>

<p>Other examples:

<tcl>
jexample \
  {json_insert('{"a":2,"c":4}', '$.a', 99)} {'{"a":2,"c":4}'} \
  {json_insert('{"a":2,"c":4}', '$.e', 99)} {'{"a":2,"c":4,"e":99}'} \
  {json_replace('{"a":2,"c":4}', '$.a', 99)} {'{"a":99,"c":4}'} \
  {json_replace('{"a":2,"c":4}', '$.e', 99)} {'{"a":2,"c":4}'} \

<p>Examples:

<tcl>
jexample \
  {json_remove('[0,1,2,3,4]','$[2]')} {'[0,1,3,4]'} \
  {json_remove('[0,1,2,3,4]','$[2]','$[0]')} {'[1,3,4]'} \
  {json_remove('[0,1,2,3,4]','$[0]','$[2]')} {'[1,2,4]'} \
  {json_remove('[0,1,2,3,4]','$[#-1]','$[0]')} {'[1,2,3]'} \
  {json_remove('{"x":25,"y":42}')} {'{"x":25,"y":42}'} \
  {json_remove('{"x":25,"y":42}','$.z')} {'{"x":25,"y":42}'} \
  {json_remove('{"x":25,"y":42}','$.y')} {'{"x":25}'} \
  {json_remove('{"x":25,"y":42}','$')} NULL
</tcl>

<tcl>hd_fragment jtype {json_type SQL function} {json_type}</tcl>