Documentation Source Text

<tcl>
set nChng 0
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.
<li>SQLITE_SOURCE_ID: "2015-07-29 20:00:57 cf538e2783e468bbc25e7cb2a9ee64d3e0e80b2f"

</td>
<td width="20"></td><td bgcolor="#044a64" width="1"></td><td width="20"></td>
<td valign="top">
<h3>Current Status</h3>

<p><ul>
<li><a href="releaselog/3_8_12.html">Version 3.8.12</a>
of SQLite is recommended for all new development.
</li>
</ul></p>

<h3>Common Links</h3>

<p><ul>

<p>SQLite supports a limited subset of ALTER TABLE.
The ALTER TABLE command in SQLite allows the user to rename a table
or to add a new column to an existing table.
</p>

<p> ^(The RENAME TO syntax is used to rename the table identified by 
<i>&#91;schema-name.&#93;table-name</i> to <i>new-table-name</i>.)^
This command 
cannot be used to move a table between attached databases, only to rename 
a table within the same database.</p>

<p> ^If the table being renamed has triggers or indices, then these remain
attached to the table after it has been renamed.  ^However, if there are
any view definitions, or statements executed by triggers that refer to

</tcl>

<p> ^The ANALYZE command gathers statistics about tables and
indices and stores the collected information
in [internal tables] of the database where the query optimizer can
access the information and use it to help make better query planning choices.
^If no arguments are given, all attached databases are
analyzed.  ^If a schema name is given as the argument, then all tables
and indices in that one database are analyzed.  
^If the argument is a table name, then only that table and the
indices associated with that table are analyzed.  ^If the argument
is an index name, then only that one index is analyzed.</p>

<p> ^The default implementation stores all statistics in a single
table named "[sqlite_stat1]".  ^If SQLite is compiled with the

^The filename argument can be a [URI filename] if URI filename processing
is enable on the database connection.  The default behavior is for
URI filenames to be disabled, however that might change in a future release
of SQLite, so application developers are advised to plan accordingly.

<p>The name that occurs after the AS keyword is the name of the database
used internally by SQLite.
^The schema-names 'main' and 
'temp' refer to the main database and the database used for 
temporary tables.  ^The main and temp databases cannot be attached or
detached.</p>

<p> ^(Tables in an attached database can be referred to using the syntax 
<i>schema-name.table-name</i>.)^  ^If the name of the table is unique
across all attached databases and the main and temp databases, then the
<i>schema-name</i> prefix is not required.  ^If two or more tables in
different databases have the same name and the 
<i>schema-name</i> prefix is not used on a table reference, then the
table chosen is the one in the database that was least recently attached.</p>

<p>
^Transactions involving multiple attached databases are atomic,
assuming that the main database is not "[:memory:]" and the 
[journal_mode] is not [WAL].  ^(If the main
database is ":memory:" or if the journal_mode is WAL, then 

RecursiveBubbleDiagram create-index-stmt
</tcl>

<p>^The CREATE INDEX command consists of the keywords "CREATE INDEX" followed
by the name of the new index, the keyword "ON", the name of a previously
created table that is to be indexed, and a parenthesized list of names of
columns in the table and/or expressions that are used for the index key.
If the optional WHERE clause is included, then the index is a "[partial index]".
</p>

<tcl>hd_fragment {descidx} {descending indices} {descending index} {descending indexes}</tcl>
<p>^Each column name or expression can be followed by one
of the "ASC" or "DESC" keywords to indicate sort order.
^The sort order may or may not be ignored depending
on the database file format, and in particular the [schema format number].
^The "legacy" schema format (1) ignores index
sort order.  ^The descending index schema format (4) takes index sort order
into account.  Only versions of SQLite 3.3.0 and later are able to understand
the descending index format. For compatibility, version of SQLite between 3.3.0
and 3.7.9 use the legacy schema format by default.  The newer schema format is
used by default in version 3.7.10 and later.
^The [legacy_file_format pragma] can be used to change set the specific
behavior for any version of SQLite.</p>

<p>^The COLLATE clause optionally following each column name
or expression defines a
collating sequence used for text entries in that column.
^The default collating
sequence is the collating sequence defined for that column in the
[CREATE TABLE] statement.  ^Or if no collating sequence is otherwise defined,
the built-in BINARY collating sequence is used.</p>

<tcl>hd_fragment indexexpr {indexes on expressions}</tcl>
<p>^Expressions in an index may not reference other tables
and may not use subqueries nor functions whose result might
change (ex: [random()] or [sqlite_version()]).
^Expressions in an index may only refer to columns in the table
that is being indexed.
Indexes on expression will not work with versions of SQLite prior
to [version 3.8.12].

<p>There are no arbitrary limits on the number of indices that can be
attached to a single table.  ^(The number of columns in an index is 
limited to the value set by
[sqlite3_limit]([SQLITE_LIMIT_COLUMN],...).)^</p>

<tcl>hd_fragment uniqueidx {unique index}</tcl>

</ul>

<p>Every CREATE TABLE statement must specify a name for the new table.
  ^(Table names that begin with "sqlite_" are reserved for internal use. It
  is an error to attempt to create a table with a name that starts with
  "sqlite_".)^

<p> ^If a &lt;schema-name&gt; is specified, it must be either "main", 
  "temp", or the name of an [ATTACH DATABASE|attached database]. ^In this case
  the new table is created in the named database. ^If the "TEMP" or "TEMPORARY"
  keyword occurs between the "CREATE" and "TABLE" then the new table is
  created in the temp database. ^It is an error to specify both a 
  &lt;schema-name&gt; and the TEMP or TEMPORARY keyword, unless the
  &lt;schema-name&gt; is "temp". ^If no schema name is specified and the
  TEMP keyword is not present then the table is created in the main
  database.

<p>
  ^It is usually an error to attempt to create a new table in a database that
  already contains a table, index or view of the same name. ^However, if the
  "IF NOT EXISTS" clause is specified as part of the CREATE TABLE statement and

<tcl>hd_fragment primkeyconst {PRIMARY KEY} {PRIMARY KEY constraint}</tcl>
<p>^Each table in SQLite may have at most one <b>PRIMARY KEY</b>. ^If the
  keywords PRIMARY KEY are added to a column definition, then the primary key
  for the table consists of that single column. ^Or, if a PRIMARY KEY clause 
  is specified as a [table-constraint], then the primary key of the table
  consists of the list of columns specified as part of the PRIMARY KEY clause.
  ^The PRIMARY KEY clause must contain only column names &mdash; the use of 
  expressions in an &lt;indexed-column&gt; of a PRIMARY KEY is not supported.
  ^An error is raised if more than one PRIMARY KEY clause appears in a
  CREATE TABLE statement.  ^The PRIMARY KEY is optional for ordinary tables
  but is required for [WITHOUT ROWID] tables.

<p>If a table has a single column primary key and the declared type of that
  column is "INTEGER" and the table is not a [WITHOUT ROWID] table,
  then the column is known as an [INTEGER PRIMARY KEY].

<tcl>hd_fragment uniqueconst {UNIQUE} {unique constraint} {UNIQUE constraint}</tcl>
<p>^A <b>UNIQUE</b> constraint is similar to a PRIMARY KEY constraint, except
  that a single table may have any number of UNIQUE constraints. ^For each
  UNIQUE constraint on the table, each row must contain a unique combination
  of values in the columns identified by the UNIQUE constraint. 
  ^For the purposes of UNIQUE constraints, NULL values
  are considered distinct from all other values, including other NULLs.
  ^As with PRIMARY KEYs, a UNIQUE [table-constraint] clause must contain
  only column names &mdash; the use of 
  expressions in an &lt;indexed-column&gt; of a UNIQUE [table-constraint]
  is not supported.
  

<p>^In most cases, UNIQUE and PRIMARY KEY
  constraints are implemented by creating a unique index in the database.
  (The exceptions are [INTEGER PRIMARY KEY] and PRIMARY KEYs on 
  [WITHOUT ROWID] tables.)
  Hence, the following schemas are logically equivalent:

in the following statement, it is important to say "main.tab1" instead
of just "tab1":</p>

<blockquote><pre>
CREATE TEMP TRIGGER ex1 AFTER INSERT ON <b>main.</b>tab1 BEGIN ...
</pre></blockquote>

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

</p>

<p>^If the "TEMP" or "TEMPORARY" keyword occurs in between "CREATE"
and "VIEW" then the view that is created is only visible to the
[database connection] that created it and is automatically deleted when
the database connection is closed.</p>

<p> ^If a &lt;schema-name&gt; is specified, then the view is created in 
the specified database. ^It is an error to specify both a &lt;schema-name&gt;
and the TEMP keyword on a VIEW, unless the &lt;schema-name&gt; is "temp".
^If no schema name is specified, and the TEMP keyword is not present,
the VIEW is created in the main database.</p>

<p>^You cannot [DELETE], [INSERT], or [UPDATE] a view.  ^Views are read-only 
in SQLite.  ^However, in many cases you can use an
[INSTEAD OF trigger] on the view to accomplish 
the same thing.  ^Views are removed 
with the [DROP VIEW] command.</p>

<p>^If a list of column names follows the &lt;view-name&gt;, then those
becomes the names of the columns for the view.  ^If no list of column
names is provided, then the names of the columns in the view are derived
from the names of the result-set columns in the &lt;select-stmt&gt;.

<p>Note that the list of column names following the view-name is only
supported in SQLite versions 3.8.12 and later.  Views with specified column
names will not work in earlier versions of SQLite.

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

RecursiveBubbleDiagram create-virtual-table-stmt
</tcl>

<p>The following restrictions apply to DELETE statements that occur within the
   body of a [CREATE TRIGGER] statement:

<ul>
  <li><p>^The <i>table-name</i> specified as part of a DELETE statement within
    a trigger body must be unqualified.  ^(In other words, the
    <i>schema-name</i><b>.</b> prefix on the table name is not allowed 
    within triggers.)^ ^If the table to which the trigger is attached is
    not in the temp database, then DELETE statements within the trigger
    body must operate on tables within the same database as it. ^If the table
    to which the trigger is attached is in the TEMP database, then the
    unqualified name of the table being deleted is resolved in the same way as
    it is for a top-level statement (by searching first the TEMP database, then
    the main database, then any other databases in the order they were

</tcl>

<p>^The DROP VIEW statement removes a view created by the [CREATE VIEW] 
  statement. ^The view definition is removed from the database schema, but 
  no actual data in the underlying base tables is modified.

<p>^(The view to drop is identified by the view-name and optional 
  schema-name specified as part of the DROP VIEW statement. This 
  reference is resolved using the standard procedure for [object resolution].)^

<p>
  ^If the specified view cannot be found and the IF EXISTS clause is not 
  present, it is an error. ^If the specified view cannot be found and an IF
  EXISTS clause is present in the DROP VIEW statement, then the statement
  is a no-op.

      DROP TABLE t1;         /* Drop table in temp database */
      DROP TABLE t1;         /* Drop table in main database */
      DROP TABLE t1;         /* Drop table in aux database */
)^</pre>

<p>
  ^If a schema name is specified as part of an object reference, it must be
  either "main", or "temp" or the schema-name of an attached database.
  ^Like other SQL identifiers, schema names are case-insensitive.
  ^If a schema name is specified, then only that one schema is searched for
  the named object.

<p>
  Most object references may only resolve to a specific type of object (for
  example a reference that is part of a DROP TABLE statement may only resolve
  to a table object, not an index, trigger or view). However in some contexts 
  (e.g. [REINDEX]) an object reference may be resolve to more than one type
  of object. ^When searching database schemas for a named object, objects of

<p>^The "REPLACE" and "INSERT OR <i>action</i>" forms specify an alternative
constraint conflict resolution algorithm to use during this one INSERT command.
See the section titled [ON CONFLICT] for additional information.
For compatibility with MySQL, ^the parser allows the use of the
single keyword <a href="lang_replace.html">REPLACE</a> as an 
alias for "INSERT OR REPLACE".

<p>^(The optional "<i>schema-name</i><b>.</b>" prefix on the <i>table-name</i>
is support for top-level INSERT statements only.)^  ^The table name must be
unqualified for INSERT statements that occur within [CREATE TRIGGER] statements.
^Similarly, the "DEFAULT VALUES" form of the INSERT statement is supported for
top-level INSERT statements only and not for INSERT statements within
triggers.
<p>

all indices in all attached databases that use the named collation sequences
are recreated. 

<p>^Or, if the argument attached to the REINDEX identifies a specific 
database table, then all indices attached to the database table are rebuilt. 
^If it identifies a specific database index, then just that index is recreated.

<p>^If no <i>schema-name</i> is specified and there exists both a table or
index and a collation sequence of the specified name, SQLite interprets
this as a request to rebuild the indices that use the named collation sequence.
This ambiguity in the syntax may be avoided by always specifying a
<i>schema-name</i> when reindexing a specific table or index.

<tcl>
###############################################################################
Section REPLACE replace REPLACE

</tcl>

<h3>The WITH Clause</h3>

<p>^SELECT statements may be optionally preceded by a single
[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>

<p>The following additional syntax restrictions apply to UPDATE statements that
occur within the body of a [CREATE TRIGGER] statement.  

<ul>
  <li><p>^The <i>table-name</i> specified as part of an UPDATE statement within
      a trigger body must be unqualified. ^(In other words, the
      <i>schema-name</i><b>.</b> prefix on the table name of the UPDATE is
      not allowed within triggers.)^ ^Unless the table to which the trigger
      is attached is in the TEMP database, the table being updated by the
      trigger program must reside in the same database as it. ^If the table
      to which the trigger is attached is in the TEMP database, then the
      unqualified name of the table being updated is resolved in the same way
      as it is for a top-level statement (by searching first the TEMP database,
      then the main database, then any other databases in the order they were

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. 
A hidden column differs from a normal column in three respects:

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>
  int (*xConnect)(sqlite3*, void *pAux,