Documentation Source Text

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

chng {2015-11-01 (3.8.12)} {
<p><b>New Features:</b>
<li>The [CREATE VIEW] statement is enhanced to accept 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.





















}

chng {2015-07-29 (3.8.11.1)} {
<li>Restore an undocumented side-effect of [PRAGMA cache_size]: force
    the database schema to be parsed if the database has not been previously accessed.
<li>Fix a long-standing problem in [sqlite3_changes()] for [WITHOUT ROWID]
    tables that was reported a few hours after the 3.8.11 release.

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

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
    AND-connected terms in the WHERE clause.
<li>The sqlite3_analyzer.exe utility is updated to report the depth of
    each btree and to show the average fanout for indexes and 
    WITHOUT ROWID tables.
<p><b>Other changes:</b>
<li>The [sqlite3_memory_alarm()] interface, which has been deprecated and
    undocumented for 8 years, is changed into a no-op.
<p><b>Fixes:</b>
<li>Added a memory barrier in the implementation of
    [sqlite3_initialize()] to help ensure that it is thread-safe.
<li>Fix the [OR optimization] so that it always ignores subplans that
    do not use an index.
<li>Do not apply the WHERE-clause pushdown optimization on terms that originate
    in the ON or USING clause of a LEFT JOIN.  Fix for ticket
    [https://www.sqlite.org/src/info/c2a19d81652f40568c|c2a19d81652f40568c].
}

chng {2015-07-29 (3.8.11.1)} {
<li>Restore an undocumented side-effect of [PRAGMA cache_size]: force
    the database schema to be parsed if the database has not been previously accessed.
<li>Fix a long-standing problem in [sqlite3_changes()] for [WITHOUT ROWID]
    tables that was reported a few hours after the 3.8.11 release.

<p>
^The <b>dbstat</b> virtual table is available on all 
[database connections] when SQLite is built using the
[SQLITE_ENABLE_DBSTAT_VTAB] compile-time option.
^The dbstat virtual table provides low-level information 
about btree and overflow pages in a database file.


^The dbstat virtual table itself can be create as a TEMP
table while providing information about the main database.









<p>

The recommanded way to create an instance of the dbstat
virtual table is as follows:

<blockquote><pre>
CREATE VIRTUAL TABLE temp.stat USING dbstat(main);
</pre></blockquote>

<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 attached database
for which information is to be provided.  The default is "main", and
so the use of "main" in the example above is redundant.


<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

<p>
^The <b>dbstat</b> virtual table is available on all 
[database connections] when SQLite is built using the
[SQLITE_ENABLE_DBSTAT_VTAB] compile-time option.
^The dbstat virtual table provides low-level information 
about btree and overflow pages in a database file.

<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>
CREATE VIRTUAL TABLE temp.stat USING dbstat(main);
</pre></blockquote>

<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

<p>Failure to specify the schema name on the target table could result
in the TEMP trigger being reattached to a table with the same name in
another database whenever any schema change occurs.</p>

<tcl>
###############################################################################
Section {CREATE VIEW} {createview} {{CREATE VIEW} view *views}

RecursiveBubbleDiagram create-view-stmt
</tcl>

<p>^The CREATE VIEW command assigns a name to a pre-packaged 
[SELECT] statement. 
^Once the view is created, it can be used in the FROM clause

<p>Failure to specify the schema name on the target table could result
in the TEMP trigger being reattached to a table with the same name in
another database whenever any schema change occurs.</p>

<tcl>
###############################################################################
Section {CREATE VIEW} {createview} {{CREATE VIEW} view VIEW *views}

RecursiveBubbleDiagram create-view-stmt
</tcl>

<p>^The CREATE VIEW command assigns a name to a pre-packaged 
[SELECT] statement. 
^Once the view is created, it can be used in the FROM clause

<tcl>hd_keywords {virtual table} {virtual tables}</tcl>

<h2>1.0 Introduction</h2>

<p>A virtual table is an object that is registered with an open SQLite
[database connection]. From the perspective of an SQL statement,
the virtual table object looks like any other table or view. 
But behind the scenes, queries from and updates to a virtual table
invoke callback methods on the virtual table object instead of
reading and writing to the database file.

<p>The virtual table mechanism allows an application to publish
interfaces that are accessible from SQL statements as if they were
tables. SQL statements can in general do anything to a
virtual table that they can do to a real table, with the following
exceptions:

<p>
<ul>
<li> One cannot create a trigger on a virtual table.
<li> One cannot create additional indices on a virtual table. 
................................................................................
     (Virtual tables can have indices but that must be built into
     the virtual table implementation.  Indices cannot be added
     separately using [CREATE INDEX] statements.)
<li> One cannot run [ALTER TABLE | ALTER TABLE ... ADD COLUMN]
     commands against a virtual table.
</ul>

<p>Particular virtual table implementations might impose additional
constraints. For example, some virtual implementations might provide
read-only tables. Or some virtual table implementations might allow
[INSERT] or [DELETE] but not [UPDATE].  Or some virtual table implementations
might limit the kinds of UPDATEs that can be made.

<p>A virtual table might represent an in-memory data structures. 
Or it might represent a view of data on disk that is not in the
SQLite format. Or the application might compute the content of the 
virtual table on demand.

<p>Here are some postulated uses for virtual tables:

<ul>
<li> A [full-text search] interface
<li> Spatial indices using [R-Trees]


<li> Read and/or write the content of a comma-separated value (CSV)
     file
<li> Access to the filesystem of the host computer
<li> Enabling SQL manipulation of data in statistics packages like R
</ul>

<h3>1.1 Usage</h3>

<p>A virtual table is created using a [CREATE VIRTUAL TABLE] statement.
This statement creates a table with a particular name and associates the
................................................................................
arguments.

<p>Once a virtual table has been created, it can be used like any other 
table with the exceptions noted above and imposed by specific virtual
table implementations. A virtual table is destroyed using the ordinary
[DROP TABLE] syntax.






















































<h2>1.2 Implementation</h2>

<p>Several new C-level objects are used by the virtual table implementation:

<blockquote><pre>
  typedef struct sqlite3_vtab sqlite3_vtab;
  typedef struct sqlite3_index_info sqlite3_index_info;
  typedef struct sqlite3_vtab_cursor sqlite3_vtab_cursor;
................................................................................
An error message may optionally be returned in *pzErr if unsuccessful.
Space to hold the error message string must be allocated using
an SQLite memory allocation function like 
[sqlite3_malloc()] or [sqlite3_mprintf()] as the SQLite core will
attempt to free the space using [sqlite3_free()] after the error has
been reported up to the application.

<p>The xCreate method is required for every virtual table implementation, 
though the xCreate and [xConnect] pointers of the [sqlite3_module] object










may point to the same function if the virtual table does not need to
initialize backing store.



<tcl>hd_fragment hiddencol {hidden column} {hidden columns}</tcl>
<h4>2.1.1 Hidden columns in virtual tables</h4>
<p>If a column datatype contains the special keyword "HIDDEN"
(in any combination of upper and lower case letters) then that keyword
it is omitted from the column datatype name and the column is marked 
as a hidden column internally. 
................................................................................
connection to an existing virtual table whereas xCreate is called 
to create a new virtual table from scratch.

<p>The xCreate and xConnect methods are only different when the
virtual table has some kind of backing store that must be initialized 
the first time the virtual table is created. The xCreate method creates 
and initializes the backing store. The xConnect method just connects 
to an existing backing store.


<p>As an example, consider a virtual table implementation that 
provides read-only access to existing comma-separated-value (CSV)
files on disk. There is no backing store that needs to be created 
or initialized for such a virtual table (since the CSV files already 
exist on disk) so the xCreate and xConnect methods will be identical 
for that module.

<tcl>hd_keywords {virtual table} {virtual tables}</tcl>

<h2>1.0 Introduction</h2>

<p>A virtual table is an object that is registered with an open SQLite
[database connection]. From the perspective of an SQL statement,
the virtual table object looks like any other table or view. 
But behind the scenes, queries and updates on a virtual table
invoke callback methods of the virtual table object instead of
reading and writing to the database file.

<p>The virtual table mechanism allows an application to publish
interfaces that are accessible from SQL statements as if they were
tables. SQL statements can do almost anything to a
virtual table that they can do to a real table, with the following
exceptions:

<p>
<ul>
<li> One cannot create a trigger on a virtual table.
<li> One cannot create additional indices on a virtual table. 
................................................................................
     (Virtual tables can have indices but that must be built into
     the virtual table implementation.  Indices cannot be added
     separately using [CREATE INDEX] statements.)
<li> One cannot run [ALTER TABLE | ALTER TABLE ... ADD COLUMN]
     commands against a virtual table.
</ul>

<p>Individual virtual table implementations might impose additional
constraints. For example, some virtual implementations might provide
read-only tables. Or some virtual table implementations might allow
[INSERT] or [DELETE] but not [UPDATE].  Or some virtual table implementations
might limit the kinds of UPDATEs that can be made.

<p>A virtual table might represent an in-memory data structures. 
Or it might represent a view of data on disk that is not in the
SQLite format. Or the application might compute the content of the 
virtual table on demand.

<p>Here are some existing and postulated uses for virtual tables:

<ul>
<li> A [full-text search] interface
<li> Spatial indices using [R-Trees]
<li> Introspect the disk content of an SQLite database file
     (the [dbstat virtual table])
<li> Read and/or write the content of a comma-separated value (CSV)
     file
<li> Access the filesystem of the host computer as if it were a database table
<li> Enabling SQL manipulation of data in statistics packages like R
</ul>

<h3>1.1 Usage</h3>

<p>A virtual table is created using a [CREATE VIRTUAL TABLE] statement.
This statement creates a table with a particular name and associates the
................................................................................
arguments.

<p>Once a virtual table has been created, it can be used like any other 
table with the exceptions noted above and imposed by specific virtual
table implementations. A virtual table is destroyed using the ordinary
[DROP TABLE] syntax.

<h4>1.1.1 Temporary virtual tables</h4>

<p>There is no "CREATE TEMP VIRTUAL TABLE" statement.  To create a
temporary virtual table, add the "temp" schema name before the tablename:

<blockcuqote><pre>
   CREATE VIRTUAL TABLE <b>temp.</b>tablename USING module(arg1, ...);
</pre></blockquote>

<tcl>hd_fragment epovtab {eponymous virtual tables}  \
        {eponymous virtual table}</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.)

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

<p>A virtual table is eponymous if its [xCreate] method is the exact same
function as the [xConnect] method, or if the [xCreate] method is NULL.
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.

<h3>1.2 Implementation</h3>

<p>Several new C-level objects are used by the virtual table implementation:

<blockquote><pre>
  typedef struct sqlite3_vtab sqlite3_vtab;
  typedef struct sqlite3_index_info sqlite3_index_info;
  typedef struct sqlite3_vtab_cursor sqlite3_vtab_cursor;
................................................................................
An error message may optionally be returned in *pzErr if unsuccessful.
Space to hold the error message string must be allocated using
an SQLite memory allocation function like 
[sqlite3_malloc()] or [sqlite3_mprintf()] as the SQLite core will
attempt to free the space using [sqlite3_free()] after the error has
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

store.  Such a virtual table can be used as an [eponymous virtual table]
or as a named virtual table using [CREATE VIRTUAL TABLE] or both.

<tcl>hd_fragment hiddencol {hidden column} {hidden columns}</tcl>
<h4>2.1.1 Hidden columns in virtual tables</h4>
<p>If a column datatype contains the special keyword "HIDDEN"
(in any combination of upper and lower case letters) then that keyword
it is omitted from the column datatype name and the column is marked 
as a hidden column internally. 
................................................................................
connection to an existing virtual table whereas xCreate is called 
to create a new virtual table from scratch.

<p>The xCreate and xConnect methods are only different when the
virtual table has some kind of backing store that must be initialized 
the first time the virtual table is created. The xCreate method creates 
and initializes the backing store. The xConnect method just connects 
to an existing backing store.  When xCreate and xConnect are the same,
the table is an [eponymous virtual table].

<p>As an example, consider a virtual table implementation that 
provides read-only access to existing comma-separated-value (CSV)
files on disk. There is no backing store that needs to be created 
or initialized for such a virtual table (since the CSV files already 
exist on disk) so the xCreate and xConnect methods will be identical 
for that module.