Documentation Source Text

}

chng {2015-11-01 (3.8.12)} {
<p><b>New Features And Enhancements:</b>
<li>The [CREATE VIEW] statement now accepts an optional list of
    column names following the view name.
<li>Added support for [indexes on expressions].
<li>Added support for [table-valued functions in the FROM clause] of a
    [SELECT] statement.
<li>Added support for [eponymous virtual tables].
<li>Added the ext/misc/json1.c extension module in the source tree.
<li>A [VIEW] may now reference undefined tables and functions when
    initially created.  Missing tables and functions are reported when
    the VIEW is used in a query.
<li>The query planner is now able to use [partial indexes] that contain

}

chng {2015-11-01 (3.8.12)} {
<p><b>New Features And Enhancements:</b>
<li>The [CREATE VIEW] statement now accepts an optional list of
    column names following the view name.
<li>Added support for [indexes on expressions].
<li>Added support for [table-valued functions] in the FROM clause of a
    [SELECT] statement.
<li>Added support for [eponymous virtual tables].
<li>Added the ext/misc/json1.c extension module in the source tree.
<li>A [VIEW] may now reference undefined tables and functions when
    initially created.  Missing tables and functions are reported when
    the VIEW is used in a query.
<li>The query planner is now able to use [partial indexes] that contain

<p>
^The dbstat virtual table is an [eponymous virtual table], meaning
that is not necessary to run [CREATE VIRTUAL TABLE] to create an
instance of the dbstat virtual table before using it.  The "dbstat"
module name can be used as if it were a table name to query the
dbstat virtual table directly.  For example:

<blockquote>
SELECT * FROM dbstat;
</blockquote>

<p>
If a named virtual table that uses the dbstat module is desired,
then the recommanded way to create an instance of the dbstat
virtual table is as follows:

<blockquote><pre>
................................................................................
<p>
Note the "temp." qualifier before the virtual table name ("stat").  This
qualifier causes the virtual table to be temporary - to only exist for
the duration of the current database connection.  This is the
recommended approach.

<p>
The "main" argument to dbstat is the named of the schema
for which information is to be provided.  The default is "main", and
so the use of "main" in the example above is redundant.  When using
the eponymous dbstat virtual table, the schema is always "main".




<p>
The schema for the dbstat virtual table is this:
<blockquote><pre>
CREATE TABLE dbstat(
  name       STRING,     -- Name of table or index
  path       INTEGER,    -- Path to page from root
................................................................................
  pageno     INTEGER,    -- Page number
  pagetype   STRING,     -- 'internal', 'leaf' or 'overflow'
  ncell      INTEGER,    -- Cells on page (0 for overflow)
  payload    INTEGER,    -- Bytes of payload on this page
  unused     INTEGER,    -- Bytes of unused space on this page
  mx_payload INTEGER,    -- Largest payload size of all cells on this page
  pgoffset   INTEGER,    -- Offset of page in file
  pgsize     INTEGER     -- Size of the page

);
</pre></blockquote>

<p>
There is a single row of the dbstat table for each page in the
database file.  Freelist pages, the lock page, and
pointer-map pages of the database file do not appear in the
................................................................................
<blockquote><pre>
'/1c2/000/'               // Left-most child of 451st child of root
</pre></blockquote>

<h3>Example uses of the dbstat virtual table</h3>

<p>
To find the total number of pages used to store table "xyz", use:


<blockquote><pre>
CREATE VIRTUAL TABLE IF NOT EXISTS temp.stat USING dbstat;
SELECT count(*) FROM stat WHERE name='xyz';
</pre></blockquote>

<p>
To see how efficiently the content of a table is stored on disk,
compute the amount of space used to hold actual content divided
by the total amount of disk space used.  The closer this number
is to 100%, the more efficient the packing.  


<blockquote><pre>
CREATE VIRTUAL TABLE IF NOT EXISTS temp.stat USING dbstat;
SELECT sum(pgsize-unused)/sum(pgsize) FROM stat WHERE name='xyz';
</pre></blockquote>

<p>
To find the average fan-out for a table, run:

<blockquote><pre>
CREATE VIRTUAL TABLE IF NOT EXISTS temp.stat USING dbstat;
SELECT avg(ncell) FROM stat WHERE name='xyz' AND pagetype='internal';
</pre></blockquote>


<p>
Modern filesystems operate faster when disk accesses are sequential.
Hence, SQLite will run faster if the content of the database file
is on sequential pages.  To find out what fraction of the pages in
a database are sequential (and thus obtain a measurement that might
be useful in determining when to [VACUUM]), run a query like the following:

<blockquote><pre>
CREATE VIRTUAL TABLE IF NOT EXISTS temp.stat USING dbstat;
CREATE TEMP TABLE s(rowid INTEGER PRIMARY KEY, pageno INT);
INSERT INTO s(pageno) SELECT pageno FROM stat ORDER BY path;
SELECT sum(s1.pageno+1==s2.pageno)*1.0/count(*)
  FROM s AS s1, s AS s2
 WHERE s1.rowid+1=s2.rowid;
DROP TABLE s;
</pre></blockquote>

<p>
^The dbstat virtual table is an [eponymous virtual table], meaning
that is not necessary to run [CREATE VIRTUAL TABLE] to create an
instance of the dbstat virtual table before using it.  The "dbstat"
module name can be used as if it were a table name to query the
dbstat virtual table directly.  For example:

<blockquote><pre>
SELECT * FROM dbstat;
</pre></blockquote>

<p>
If a named virtual table that uses the dbstat module is desired,
then the recommanded way to create an instance of the dbstat
virtual table is as follows:

<blockquote><pre>
................................................................................
<p>
Note the "temp." qualifier before the virtual table name ("stat").  This
qualifier causes the virtual table to be temporary - to only exist for
the duration of the current database connection.  This is the
recommended approach.

<p>
The "main" argument to dbstat is default schema
for which information is to be provided.  The default is "main", and
so the use of "main" in the example above is redundant.  For any
particular query, the schema can be changed by specifying the
alternative schema as a function argument to the virtual table
name in the FROM clause of the query.  (See further discussion of
[table-valued functions in the FROM clause] for more details.)

<p>
The schema for the dbstat virtual table is this:
<blockquote><pre>
CREATE TABLE dbstat(
  name       STRING,     -- Name of table or index
  path       INTEGER,    -- Path to page from root
................................................................................
  pageno     INTEGER,    -- Page number
  pagetype   STRING,     -- 'internal', 'leaf' or 'overflow'
  ncell      INTEGER,    -- Cells on page (0 for overflow)
  payload    INTEGER,    -- Bytes of payload on this page
  unused     INTEGER,    -- Bytes of unused space on this page
  mx_payload INTEGER,    -- Largest payload size of all cells on this page
  pgoffset   INTEGER,    -- Offset of page in file
  pgsize     INTEGER,    -- Size of the page
  schema     TEXT HIDDEN -- The database being analyzed
);
</pre></blockquote>

<p>
There is a single row of the dbstat table for each page in the
database file.  Freelist pages, the lock page, and
pointer-map pages of the database file do not appear in the
................................................................................
<blockquote><pre>
'/1c2/000/'               // Left-most child of 451st child of root
</pre></blockquote>

<h3>Example uses of the dbstat virtual table</h3>

<p>
To find the total number of pages used to store table "xyz" in schema "aux1",
use:

<blockquote><pre>

SELECT count(*) FROM dbstat('aux1') WHERE name='xyz';
</pre></blockquote>

<p>
To see how efficiently the content of a table is stored on disk,
compute the amount of space used to hold actual content divided
by the total amount of disk space used.  The closer this number
is to 100%, the more efficient the packing.  (In this example, the
'xyz' table is assumed to be in the 'main' schema.)

<blockquote><pre>

SELECT sum(pgsize-unused)/sum(pgsize) FROM dbstat WHERE name='xyz';
</pre></blockquote>

<p>
To find the average fan-out for a table, run:

<blockquote><pre>

SELECT avg(ncell) FROM dbstat WHERE name='xyz' AND pagetype='internal';
</pre></blockquote>


<p>
Modern filesystems operate faster when disk accesses are sequential.
Hence, SQLite will run faster if the content of the database file
is on sequential pages.  To find out what fraction of the pages in
a database are sequential (and thus obtain a measurement that might
be useful in determining when to [VACUUM]), run a query like the following:

<blockquote><pre>

CREATE TEMP TABLE s(rowid INTEGER PRIMARY KEY, pageno INT);
INSERT INTO s(pageno) SELECT pageno FROM dbstat ORDER BY path;
SELECT sum(s1.pageno+1==s2.pageno)*1.0/count(*)
  FROM s AS s1, s AS s2
 WHERE s1.rowid+1=s2.rowid;
DROP TABLE s;
</pre></blockquote>

[WITH clause] that defines one or more [common table expressions]
for use within the SELECT statement.

<tcl>hd_fragment tabfunc1 {table-valued functions in the FROM clause}</tcl>
<h3>Table-valued Functions In The FROM Clause</h3>

<p>A [virtual table] that contains [hidden columns] can be used like
a table-valued function in the FROM clause.  The arguments to the
table-valued function become constraints on the HIDDEN columns of
the virtual table.

<p>For example, the "generate_series" extension (located in the
ext/misc/series.c file in source tree) implements a [virtual table]
with the following schema:

<blockquote><pre>
CREATE TABLE generate_series(
  value,
  start HIDDEN,
  stop HIDDEN,
  step HIDDEN
);
</pre></blockquote>

<p>The [sqlite3_module.xBestIndex] method in the implementation of this
table checks for equality constraints against the HIDDEN columns, and uses
those as input parameters to determine the range of integer "value" outputs
to generate.  For example, to list all integers between 5 and 50:

<blockquote><pre>
SELECT value FROM generate_series(5,50);
</pre></blockquote>

<p>This query is logically equivalent to the following:

<blockquote><pre>
SELECT value FROM generate_series WHERE start=5 AND stop=50;
</pre></blockquote>

<p>Arguments on the virtual table name are matched to [hidden columns]
in order.  The number of arguments can be less than the
number of hidden columns, in which case the latter hidden columns are
unconstrained.  However, an error results if there are more arguments
than there are hidden columns in the virtual table.


<tcl>
##############################################################################
Section UPDATE update {UPDATE *UPDATEs}

RecursiveBubbleDiagram update-stmt
</tcl>

[WITH clause] that defines one or more [common table expressions]
for use within the SELECT statement.

<tcl>hd_fragment tabfunc1 {table-valued functions in the FROM clause}</tcl>
<h3>Table-valued Functions In The FROM Clause</h3>

<p>A [virtual table] that contains [hidden columns] can be used like
a [table-valued function] in the FROM clause.  The arguments to the
table-valued function become constraints on the HIDDEN columns of
the virtual table.  Additional information can be found in the


































[table-valued functions|virtual table documentation].

<tcl>
##############################################################################
Section UPDATE update {UPDATE *UPDATEs}

RecursiveBubbleDiagram update-stmt
</tcl>

<h4>1.1.2 Eponymous virtual tables</h4>

<p>Some virtual tables exist automatically in every schema in which their
module is registered, even without a [CREATE VIRTUAL TABLE] statement.
Such virtual tables are called "eponymous virtual tables".
To use an eponymous virtual table, simple use the module name as if
it were a table.



<p>An example of an eponymous virtual table is the [dbstat virtual table].
To use the dbstat virtual table as an eponymous virtual table, 
simply query against the "dbstat"
module name, as if it were an ordinary table.  (Note that SQLite
must be compiled with the [SQLITE_ENABLE_DBSTAT_VTAB] option to include
the dbstat virtual table in the build.)
................................................................................
The [xCreate] method is called when a virtual table is first created
using the [CREATE VIRTUAL TABLE] statement.  The [xConnect] method whenever
a database connection attaches to or reparses a schema. When these two methods
are the same, that indicates that the virtual table has no persistent
state that needs to be created and destroyed.

<tcl>hd_fragment epoonlyvtab {eponymous-only virtual table}</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 in the FROM clause] of a [SELECT] statement.

<p>
Note that SQLite versions prior to 3.8.12 did not check the xCreate method
for NULL before to 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.
................................................................................
and with datatypes of "VARCHAR(12)" and "INTEGER".

<p>An example use of hidden columns can be seen in the [FTS3] virtual 
table implementation, where every FTS virtual table
contains an [FTS hidden column] that is used to pass information from the
virtual table into [FTS auxiliary functions] and to the [FTS MATCH] operator.

<tcl>hd_fragment tabfunc2 {table-valued functions}</tcl>

<h4>2.1.2 Hidden columns used to implement table-valued functions</h4>

<p>Hidden columns in virtual tables can also be used to implement
[table-valued functions in the FROM clause] of a [SELECT] statement.
When a virtual table with hidden columns appears in the FROM clause of
a SELECT statement, the table name can be followed by a parenthesized
list of expresssion which become constraints on the hidden columns.





































<tcl>############################################################# xConnect
hd_fragment xconnect {sqlite3_module.xConnect} {xConnect}</tcl>
<h3>2.2 The xConnect Method</h3>

<blockquote><pre>

<h4>1.1.2 Eponymous virtual tables</h4>

<p>Some virtual tables exist automatically in every schema in which their
module is registered, even without a [CREATE VIRTUAL TABLE] statement.
Such virtual tables are called "eponymous virtual tables".
To use an eponymous virtual table, simple use the module name as if
it were a table.
Eponymous virtual tables exist in the "main" schema only, so they will
not work if prefixed with a different schema name.

<p>An example of an eponymous virtual table is the [dbstat virtual table].
To use the dbstat virtual table as an eponymous virtual table, 
simply query against the "dbstat"
module name, as if it were an ordinary table.  (Note that SQLite
must be compiled with the [SQLITE_ENABLE_DBSTAT_VTAB] option to include
the dbstat virtual table in the build.)
................................................................................
The [xCreate] method is called when a virtual table is first created
using the [CREATE VIRTUAL TABLE] statement.  The [xConnect] method whenever
a database connection attaches to or reparses a schema. When these two methods
are the same, that indicates that the virtual table has no persistent
state that needs to be created and destroyed.

<tcl>hd_fragment epoonlyvtab {eponymous-only virtual table}</tcl>
<h5>1.1.2.1 Eponymous-only virtual tables</h5>
<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 to 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.
................................................................................
and with datatypes of "VARCHAR(12)" and "INTEGER".

<p>An example use of hidden columns can be seen in the [FTS3] virtual 
table implementation, where every FTS virtual table
contains an [FTS hidden column] that is used to pass information from the
virtual table into [FTS auxiliary functions] and to the [FTS MATCH] operator.

<tcl>hd_fragment tabfunc2 {table-valued functions} \
         {table-valued function}</tcl>
<h4>2.1.2 Table-valued functions</h4>


<p>A [virtual table] that contains [hidden columns] can be used like
a table-valued function in the FROM clause of a [SELECT] statement.
The arguments to the table-valued function become constraints on 
the HIDDEN columns of the virtual table.

<p>For example, the "generate_series" extension (located in the
ext/misc/series.c file in source tree) implements an 
[eponymous virtual table] with the following schema:

<blockquote><pre>
CREATE TABLE generate_series(
  value,
  start HIDDEN,
  stop HIDDEN,
  step HIDDEN
);
</pre></blockquote>

<p>The [sqlite3_module.xBestIndex] method in the implementation of this
table checks for equality constraints against the HIDDEN columns, and uses
those as input parameters to determine the range of integer "value" outputs
to generate.  Reasonable defaults are used for any unconstrainted columns.
For example, to list all integers between 5 and 50:

<blockquote><pre>
SELECT value FROM generate_series(5,50);
</pre></blockquote>

<p>The previous query is equivalent to the following:

<blockquote><pre>
SELECT value FROM generate_series WHERE start=5 AND stop=50;
</pre></blockquote>

<p>Arguments on the virtual table name are matched to [hidden columns]
in order.  The number of arguments can be less than the
number of hidden columns, in which case the latter hidden columns are
unconstrained.  However, an error results if there are more arguments
than there are hidden columns in the virtual table.


<tcl>############################################################# xConnect
hd_fragment xconnect {sqlite3_module.xConnect} {xConnect}</tcl>
<h3>2.2 The xConnect Method</h3>

<blockquote><pre>