System.Data.SQLite

</div>
</div>


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

<a name="altertabrename"></a>

<h3>ALTER TABLE RENAME</h3>

<p> The RENAME TO syntax changes the name of <span class='yyterm'>table-name</span>
to <span class='yyterm'>new-table-name</span>.
This command 
cannot be used to move a table between attached databases, only to rename 
a table within the same database.
If the table being renamed has triggers or indices, then these remain
attached to the table after it has been renamed.

<blockquote style='background-color: #ffd0d0;'>
<b>Compatibility Note:</b>
The behavior of ALTER TABLE when renaming a table was enhanced
in versions 3.25.0 (2018-09-15) and 3.26.0 (2018-12-01)
in order to carry the rename operation forward into triggers and
views that reference the renamed table.  This is considered an
improvement. Applications that depend on the older (and
arguably buggy) behavior can use the
<a href="pragma.html#pragma_legacy_alter_table">PRAGMA legacy_alter_table=ON</a> statement to make ALTER TABLE RENAME
behavior as it did prior to version 3.25.0.
</blockquote>

<p>
Beginning with release 3.25.0 (2018-09-15), references to the table
within trigger bodies and view definitions are also renamed.
</p>

<p> Prior to version 3.26.0 (2018-12-01), FOREIGN KEY references
to a table that is renamed were only edited if the
<a href="pragma.html#pragma_foreign_keys">PRAGMA foreign_keys=ON</a>, or in other words if
<a href="foreignkeys.html">foreign key constraints</a> were begin enforced.  With
<a href="pragma.html#pragma_foreign_keys">PRAGMA foreign_keys=OFF</a>, FOREIGN KEY constraints would not be changed
when the table that the foreign key referred to (the "<a href="foreignkeys.html#parentchild">parent table</a>")
was renamed.  Beginning with version 3.26.0, FOREIGN KEY constraints
are always converted when a table is renamed, unless the
<a href="pragma.html#pragma_legacy_alter_table">PRAGMA legacy_alter_table=ON</a> setting is engaged.  The following
table summaries the difference:</p>

<blockquote>
<table border="1" cellpadding="2" cellspacing="0">
<tr>
<th>PRAGMA foreign_keys
<th>PRAGMA legacy_alter_table
<th><a href="foreignkeys.html#parentchild">Parent Table</a>
references are updated
<th>SQLite version
<tr><td align="center">Off<td align="center">Off<td align="center">No<td align="center">&lt; 3.26.0
<tr><td align="center">Off<td align="center">Off<td align="center">Yes<td align="center">&gt;= 3.26.0
<tr><td align="center">On<td align="center">Off<td align="center">Yes<td align="center">all
<tr><td align="center">Off<td align="center">On<td align="center">No<td align="center">all
<tr><td align="center">On<td align="center">On<td align="center">Yes<td align="center">all
</table>
</blockquote>

<a name="altertabmvcol"></a>

<h3>ALTER TABLE RENAME COLUMN</h3>

<p> The RENAME COLUMN TO syntax changes the
<span class='yyterm'>column-name</span> of table <span class='yyterm'>table-name</span>
into <span class='yyterm'>new-column-name</span>.  The column name is changed both
within the table definition itself and also within all indexes, triggers,
and views that reference the column.  If the column name change would
result in a semantic ambiguity in a trigger or view, then the RENAME
COLUMN fails with an error and no changes are applied.

<a name="altertabaddcol"></a>

<h3>ALTER TABLE ADD COLUMN</h3>

<p> The ADD COLUMN syntax
is used to add a new column to an existing table.
The new column is always appended to the end of the list of existing columns.
The <a href="syntax/column-def.html">column-def</a> rule defines the characteristics of the new column.
The new column may take any of the forms permissible in a <a href="lang_createtable.html">CREATE TABLE</a>
statement, with the following restrictions:
<ul>

<p>Note also that when adding a <a href="lang_createtable.html#ckconst">CHECK constraint</a>, the CHECK constraint
is not tested against preexisting rows of the table.
This can result in a table that contains data that
is in violation of the CHECK constraint.  Future versions of SQLite might
change to validate CHECK constraints as they are added.</p>

<p>The ALTER TABLE command works by modifying the SQL text of the schema
stored in the <a href="fileformat2.html#sqlite_master">sqlite_master table</a>.
No changes are made to table content.
Because of this,
the execution time of the ALTER TABLE command is independent of
the amount of data in the table.  The ALTER TABLE command runs as quickly
on a table with 10 million rows as it does on a table with 1 row.
</p>

<p>After ADD COLUMN has been run on a database, that database will not
be readable by SQLite version 3.1.3 (2005-02-20) and earlier.</p>

<a name="otheralter"></a>

<h3>Making Other Kinds Of Table Schema Changes</h3>

<p> The only schema altering commands directly supported by SQLite are the
"<a href="lang_altertable.html#altertabrename">rename table</a>", "<a href="lang_altertable.html#altertabmvcol">rename column</a>", and "<a href="lang_altertable.html#altertabaddcol">add column</a>" commands shown above.  
However, applications
can make other arbitrary changes to the format of a table using a simple
sequence of operations.
The steps to make arbitrary changes to the schema design of some table X
are as follows:

<ol>
<li><p>

If foreign key constraints are enabled, disable them using <a href="pragma.html#pragma_foreign_keys">PRAGMA foreign_keys=OFF</a>.

<li><p>
Start a transaction.

<li><p>
Remember the format of all indexes and triggers associated with table X.
This information will be needed in step 8 below.  One way to do this is

<li><p>
Commit the transaction started in step 2.

<li><p>
If foreign keys constraints were originally enabled, reenable them now.
</ol>

<a name="caution"></a>

<p>
<b>Caution:</b>
Take care to follow the procedure above precisely.  The boxes below
summarize two procedures for modifying a table definition.  At first
glance, they both appear to accomplish the same thing.  However, the
procedure on the right does not always work, especially with the
enhanced <a href="lang_altertable.html#altertabrename">rename table</a> capabilities added by versions 3.25.0 and
3.26.0.  In the procedure on the right, the initial rename of the
table to a temporary name might corrupt references to that table in
triggers, views, and foreign key constraints.  The safe procedure on
the left constructs the revised table definition using a new temporary
name, then renames the table into its final name, which does not break
links.

<center>
<table border="1" cellpadding="10" cellspacing="0">
<tr>
<td valign="top">
<ol>
<li>Create new table
<li>Copy data
<li>Drop old table
<li>Rename new into old
</ol>
<td valign="top">
<ol>
<li>Rename old table
<li>Create new table
<li>Copy data
<li>Drop old table
</ol>
<tr>
<th>&uarr;<br>Correct
<th>&uarr;<br>Incorrect
</table>
</center>

<p>The 12-step <a href="lang_altertable.html#otheralter">generalized ALTER TABLE procedure</a>
above will work even if the
schema change causes the information stored in the table to change.
So the full 12-step procedure above is appropriate for dropping a column,
changing the order of columns, adding or removing a UNIQUE constraint
or PRIMARY KEY, adding CHECK or FOREIGN KEY or NOT NULL constraints,
or changing the datatype for a column, for example.  However, a simpler
and faster procedure can optionally be used for
some changes that do no affect the on-disk content in any way.
The following simpler procedure is appropriate for removing
CHECK or FOREIGN KEY or NOT NULL constraints,

with the <a href="lang_dropview.html">DROP VIEW</a> command.</p>

<p>If a <span class='yyterm'>column-name</span> list follows 
the <span class='yyterm'>view-name</span>, then that list determines
the names of the columns for the view.  If the <span class='yyterm'>column-name</span>
list is omitted, then the names of the columns in the view are derived
from the names of the result-set columns in the <a href="syntax/select-stmt.html">select-stmt</a>.
The use of <span class='yyterm'>column-name</span> list is recommended.  Or, if
<span class='yyterm'>column-name</span> list is omitted, then the result
columns in the <a href="lang_select.html">SELECT</a> statement that defines the view should have
well-defined names using the 
"<a href="syntax/result-column.html">AS column-alias</a>" syntax.
SQLite allows you to create views that depend on automatically 
generated column names, but you should avoid using them since the 
rules used to generate column names are not a defined part of the
interface and might change in future releases of SQLite.

<p>The <span class='yyterm'>column-name</span> list syntax was added in
SQLite versions 3.9.0 (2015-10-14).

{"u":"pragma.html#pragma_secure_delete","x":"secure_delete","s":0},
{"u":"pragma.html#pragma_short_column_names","x":"short_column_names","s":3},
{"u":"pragma.html#pragma_shrink_memory","x":"shrink_memory","s":0},
{"u":"pragma.html#pragma_soft_heap_limit","x":"soft_heap_limit","s":0},
{"u":"pragma.html#pragma_stats","x":"stats","s":5},
{"u":"pragma.html#pragma_synchronous","x":"synchronous","s":0},
{"u":"pragma.html#pragma_table_info","x":"table_info","s":0},
{"u":"pragma.html#pragma_table_xinfo","x":"table_xinfo","s":0},
{"u":"pragma.html#pragma_temp_store","x":"temp_store","s":0},
{"u":"pragma.html#pragma_temp_store_directory","x":"temp_store_directory","s":3},
{"u":"pragma.html#pragma_threads","x":"threads","s":0},
{"u":"pragma.html#pragma_user_version","x":"user_version","s":0},
{"u":"pragma.html#pragma_vdbe_addoptrace","x":"vdbe_addoptrace","s":4},
{"u":"pragma.html#pragma_vdbe_debug","x":"vdbe_debug","s":4},
{"u":"pragma.html#pragma_vdbe_listing","x":"vdbe_listing","s":4},

  using a single PRAGMA statement.  The size limit must be set separately for
  each attached database.
<a name="pragma_legacy_alter_table"></a>
<h _id=pragma_legacy_alter_table style="display:none"> PRAGMA legacy_alter_table</h><hr>
   <p><b>PRAGMA legacy_alter_table;
       <br>PRAGMA legacy_alter_table = <i>boolean</i></b></p>
    <p>This pragma sets or queries the value of the legacy_alter_table
    flag.  When this flag is on, the <a href="lang_altertable.html#altertabrename">ALTER TABLE RENAME</a>
    command (for changing the name of a table) works as it did
    in SQLite 3.24.0 (2018-06-04) and earlier.  More specifically,
    when this flag is on
    the <a href="lang_altertable.html#altertabrename">ALTER TABLE RENAME</a> command only rewrites the initial occurrence
    of the table name in its <a href="lang_createtable.html">CREATE TABLE</a> statement and in any associated
    <a href="lang_createindex.html">CREATE INDEX</a> and <a href="lang_createtrigger.html">CREATE TRIGGER</a> statements.  Other references to the
    table are unmodifed, including:
    <ul>
    <li> References to the table within the bodies of triggers and views.
    <li> References to the table within CHECK constraints in the original
         CREATE TABLE statement.
    <li> References to the table within the WHERE clauses of <a href="partialindex.html">partial indexes</a>.
    </ul>
    The default setting for this pragma is OFF, which means that all
    references to the table anywhere in the schema are converted to the new name.
    <p>This pragma is provided as a work-around for older programs that
    contain code that expect the incomplete behavior
    of <a href="lang_altertable.html#altertabrename">ALTER TABLE RENAME</a> found in older versions of SQLite.
    New applications should leave this flag turned off.
    <p>For compatibility with older <a href="vtab.html">virtual table</a> implementations,
    this flag is turned on temporarily while the <a href="vtab.html#xrename">sqlite3_module.xRename</a>
    method is being run.  The value of this flag is restore after the 
    <a href="vtab.html#xrename">sqlite3_module.xRename</a> method finishes.
<a name="pragma_legacy_file_format"></a>
<h _id=pragma_legacy_file_format style="display:none"> PRAGMA legacy_file_format</h><hr>
   <p><b>PRAGMA legacy_file_format;
       <br>PRAGMA legacy_file_format = <i>boolean</i></b></p>

    Columns in the result set include the column name,
    data type, whether or not the column can be NULL, and the default
    value for the column.  The "pk" column in the result set is zero
    for columns that are not part of the primary key, and is the index of
    the column in the primary key for columns that are part of the primary
    key.</p>
    <p>The table named in the table_info pragma can also be a view.</p>
    <p>See also: <a href="pragma.html#pragma_table_xinfo">PRAGMA table_xinfo</a>
<a name="pragma_table_xinfo"></a>
<h _id=pragma_table_xinfo style="display:none"> PRAGMA table_xinfo</h><hr>
    <p><b>PRAGMA </b><i>schema.</i><b>table_xinfo(</b><i>table-name</i><b>);</b></p>
    <p>This pragma returns one row for each column in the named table,
    including <a href="vtab.html#hiddencol">hidden columns</a> in virtual tables.
    The output is the same as for <a href="pragma.html#pragma_table_info">PRAGMA table_info</a> except that
    hidden columns are shown rather than being omitted.
<a name="pragma_temp_store"></a>
<h _id=pragma_temp_store style="display:none"> PRAGMA temp_store</h><hr>
    <p><b>PRAGMA temp_store;
        <br>PRAGMA temp_store = </b>
            <i>0 | DEFAULT | 1 | FILE | 2 | MEMORY</i><b>;</b></p>

    <p>Query or change the setting of the "<b>temp_store</b>" parameter.

   The second and third column are -1 if there is no
    write-ahead log, for example if this pragma is invoked on a database
    connection that is not in <a href="wal.html">WAL mode</a>.</p>
<a name="pragma_writable_schema"></a>
<h _id=pragma_writable_schema style="display:none"> PRAGMA writable_schema</h><hr>
    <p><b>PRAGMA writable_schema  = </b><i>boolean</i><b>;</b></p>

    <p>When this pragma is on, and the <a href="c3ref/c_dbconfig_defensive.html#sqlitedbconfigdefensive">SQLITE_DBCONFIG_DEFENSIVE</a> flag
    is off, then the <a href="fileformat2.html#sqlite_master">sqlite_master</a> table
    can be changed using ordinary <a href="lang_update.html">UPDATE</a>, <a href="lang_insert.html">INSERT</a>, and <a href="lang_delete.html">DELETE</a>
    statements.  <span style='background-color: #ffff60;'><b>Warning:</b>
    misuse of this pragma can easily result in
    a <a href="howtocorrupt.html#cfgerr">corrupt database file</a>.</span>

<hr>

See also:&nbsp;&nbsp; <a href="lang_createtable.html">lang_createtable.html</a>
</div>

<a name="column-def"></a><h4>column-def:</h4><div class='imgcontainer'>
<img src="images/syntax/column-def.gif"></img><br></br>
Used by:&nbsp;&nbsp; <a href="#alter-table-stmt">alter-table-stmt</a>&nbsp;&nbsp; <a href="#create-table-stmt">create-table-stmt</a><br></br>
References:&nbsp;&nbsp; <a href="#column-constraint">column-constraint</a>&nbsp;&nbsp; <a href="#type-name">type-name</a><br></br>
See also:&nbsp;&nbsp; <a href="lang_altertable.html">lang_altertable.html</a>&nbsp;&nbsp; <a href="lang_altertable.html#altertabaddcol">lang_altertable.html#altertabaddcol</a>&nbsp;&nbsp; <a href="lang_createtable.html">lang_createtable.html</a>&nbsp;&nbsp; <a href="lang_createtable.html#tablecoldef">lang_createtable.html#tablecoldef</a>
</div>

<a name="type-name"></a><h4>type-name:</h4><div class='imgcontainer'>
<img src="images/syntax/type-name.gif"></img><br></br>
Used by:&nbsp;&nbsp; <a href="#column-def">column-def</a>&nbsp;&nbsp; <a href="#expr">expr</a><br></br>
References:&nbsp;&nbsp; <a href="#signed-number">signed-number</a><br></br>
See also:&nbsp;&nbsp; <a href="lang_UPSERT.html">lang_UPSERT.html</a>&nbsp;&nbsp; <a href="lang_altertable.html">lang_altertable.html</a>&nbsp;&nbsp; <a href="lang_attach.html">lang_attach.html</a>&nbsp;&nbsp; <a href="lang_createindex.html">lang_createindex.html</a>&nbsp;&nbsp; <a href="lang_createtable.html">lang_createtable.html</a>&nbsp;&nbsp; <a href="lang_createtrigger.html">lang_createtrigger.html</a>&nbsp;&nbsp; <a href="lang_createview.html">lang_createview.html</a>&nbsp;&nbsp; <a href="lang_delete.html">lang_delete.html</a>&nbsp;&nbsp; <a href="lang_expr.html">lang_expr.html</a>&nbsp;&nbsp; <a href="lang_insert.html">lang_insert.html</a>&nbsp;&nbsp; <a href="lang_select.html">lang_select.html</a>&nbsp;&nbsp; <a href="lang_select.html#compound">lang_select.html#compound</a>&nbsp;&nbsp; <a href="lang_select.html#simpleselect">lang_select.html#simpleselect</a>&nbsp;&nbsp; <a href="lang_update.html">lang_update.html</a>&nbsp;&nbsp; <a href="lang_with.html">lang_with.html</a>&nbsp;&nbsp; <a href="partialindex.html">partialindex.html</a>

References:&nbsp;&nbsp; <a href="#conflict-clause">conflict-clause</a>&nbsp;&nbsp; <a href="#expr">expr</a>&nbsp;&nbsp; <a href="#foreign-key-clause">foreign-key-clause</a>&nbsp;&nbsp; <a href="#indexed-column">indexed-column</a><br></br>
See also:&nbsp;&nbsp; <a href="lang_createtable.html">lang_createtable.html</a>&nbsp;&nbsp; <a href="lang_createtable.html#primkeyconst">lang_createtable.html#primkeyconst</a>&nbsp;&nbsp; <a href="lang_createtable.html#tablecoldef">lang_createtable.html#tablecoldef</a>&nbsp;&nbsp; <a href="lang_createtable.html#uniqueconst">lang_createtable.html#uniqueconst</a>
</div>

<a name="foreign-key-clause"></a><h4>foreign-key-clause:</h4><div class='imgcontainer'>
<img src="images/syntax/foreign-key-clause.gif"></img><br></br>
Used by:&nbsp;&nbsp; <a href="#column-constraint">column-constraint</a>&nbsp;&nbsp; <a href="#table-constraint">table-constraint</a><br></br>
See also:&nbsp;&nbsp; <a href="lang_altertable.html">lang_altertable.html</a>&nbsp;&nbsp; <a href="lang_altertable.html#altertabaddcol">lang_altertable.html#altertabaddcol</a>&nbsp;&nbsp; <a href="lang_createtable.html">lang_createtable.html</a>
</div>

<a name="conflict-clause"></a><h4>conflict-clause:</h4><div class='imgcontainer'>
<img src="images/syntax/conflict-clause.gif"></img><br></br>
Used by:&nbsp;&nbsp; <a href="#column-constraint">column-constraint</a>&nbsp;&nbsp; <a href="#table-constraint">table-constraint</a><br></br>
See also:&nbsp;&nbsp; <a href="lang_altertable.html">lang_altertable.html</a>&nbsp;&nbsp; <a href="lang_conflict.html">lang_conflict.html</a>&nbsp;&nbsp; <a href="lang_createtable.html">lang_createtable.html</a>&nbsp;&nbsp; <a href="lang_createtable.html#notnullconst">lang_createtable.html#notnullconst</a>
</div>

<div class="fancy-toc3"><a href="#hidden_columns_in_virtual_tables">2.1.1. Hidden columns in virtual tables</a></div>
<div class="fancy-toc3"><a href="#table_valued_functions">2.1.2. Table-valued functions</a></div>
<div class="fancy-toc3"><a href="#_without_rowid_virtual_tables_">2.1.3.  WITHOUT ROWID Virtual Tables </a></div>
<div class="fancy-toc2"><a href="#the_xconnect_method">2.2. The xConnect Method</a></div>
<div class="fancy-toc2"><a href="#the_xbestindex_method">2.3. The xBestIndex Method</a></div>
<div class="fancy-toc3"><a href="#inputs">2.3.1. Inputs</a></div>
<div class="fancy-toc3"><a href="#outputs">2.3.2. Outputs</a></div>
<div class="fancy-toc3"><a href="#return_value">2.3.3. Return Value</a></div>
<div class="fancy-toc2"><a href="#the_xdisconnect_method">2.4. The xDisconnect Method</a></div>
<div class="fancy-toc2"><a href="#the_xdestroy_method">2.5. The xDestroy Method</a></div>
<div class="fancy-toc2"><a href="#the_xopen_method">2.6. The xOpen Method</a></div>
<div class="fancy-toc2"><a href="#the_xclose_method">2.7. The xClose Method</a></div>
<div class="fancy-toc2"><a href="#the_xeof_method">2.8. The xEof Method</a></div>
<div class="fancy-toc2"><a href="#the_xfilter_method">2.9. The xFilter Method</a></div>
<div class="fancy-toc2"><a href="#the_xnext_method">2.10. The xNext Method</a></div>
<div class="fancy-toc2"><a href="#the_xcolumn_method">2.11. The xColumn Method</a></div>
<div class="fancy-toc2"><a href="#the_xrowid_method">2.12. The xRowid Method</a></div>
<div class="fancy-toc2"><a href="#the_xupdate_method">2.13. The xUpdate Method</a></div>
<div class="fancy-toc2"><a href="#the_xfindfunction_method">2.14. The xFindFunction Method</a></div>
<div class="fancy-toc2"><a href="#the_xbegin_method">2.15. The xBegin Method</a></div>
<div class="fancy-toc2"><a href="#the_xsync_method">2.16. The xSync Method</a></div>
<div class="fancy-toc2"><a href="#the_xcommit_method">2.17. The xCommit Method</a></div>
<div class="fancy-toc2"><a href="#the_xrollback_method">2.18. The xRollback Method</a></div>
<div class="fancy-toc2"><a href="#the_xrename_method">2.19. The xRename Method</a></div>
<div class="fancy-toc2"><a href="#the_xsavepoint_xrelease_and_xrollbackto_methods">2.20. The xSavepoint, xRelease, and xRollbackTo Methods</a></div>
<div class="fancy-toc2"><a href="#the_xshadowname_method">2.21. The xShadowName Method</a></div>
</div>
</div>
<script>
function toggle_toc(){
var sub = document.getElementById("toc_sub")
var mk = document.getElementById("toc_mk")
if( sub.style.display!="block" ){

                     void **ppArg);
  int (*Rename)(sqlite3_vtab *pVtab, const char *zNew);
  /* The methods above are in version 1 of the sqlite_module object. Those 
  ** below are for version 2 and greater. */
  int (*xSavepoint)(sqlite3_vtab *pVTab, int);
  int (*xRelease)(sqlite3_vtab *pVTab, int);
  int (*xRollbackTo)(sqlite3_vtab *pVTab, int);
  /* The methods above are in versions 1 and 2 of the sqlite_module object.
  ** Those below are for version 3 and greater. */
  int (*xShadowName)(const char*);
};
</pre></div>

<p>The module structure defines all of the methods for each virtual 
table object. The module structure also contains the iVersion field which
defines the particular edition of the module table structure. Currently, 
iVersion is always 3 or less, but in future releases of SQLite the module
structure definition might be extended with additional methods and in 
that case the maximum iVersion value will be increased.

</p><p>The rest of the module structure consists of methods used to implement
various features of the virtual table. Details on what each of these 
methods do are provided in the sequel.

</p><h2 id="virtual_tables_and_shared_cache"><span>1.3. </span>Virtual Tables And Shared Cache</h2>

<a href="c3ref/index_info.html">sqlite3_index_info</a> structure, it should make a copy.  Care must be
take to store the copy in a place where it will be deallocated, such
as in the idxStr field with needToFreeIdxStr set to 1.

</p><p>Note that xBestIndex will always be called before <a href="vtab.html#xfilter">xFilter</a>, since
the idxNum and idxStr outputs from xBestIndex are required inputs to
xFilter.  However, there is no guarantee that xFilter will be called
following a successful xBestIndex.

</p><p>The xBestIndex method is required for every virtual table implementation.

</p><h3 id="inputs"><span>2.3.1. </span>Inputs</h3>

<p>The main thing that the SQLite core is trying to communicate to 
the virtual table is the constraints that are available to limit 

<p>The query optimizer might translate this into three separate constraints:

</p><div class="codeblock"><pre>x &gt;= 10
x &lt;= 100
y &lt; 999
</pre></div>

<p>For each such constraint, the aConstraint[].iColumn field indicates which 
column appears on the left-hand side of the constraint.
The first column of the virtual table is column 0. 
The rowid of the virtual table is column -1. 
The aConstraint[].op field indicates which operator is used. 
The SQLITE_INDEX_CONSTRAINT_* constants map integer constants 
into operator values.
Columns occur in the order they were defined by the call to

agree on what that meaning is.

</p><p>The idxStr value may be a string obtained from an SQLite
memory allocation function such as <a href="c3ref/mprintf.html">sqlite3_mprintf()</a>. 
If this is the case, then the needToFreeIdxStr flag must be set to 
true so that the SQLite core will know to call <a href="c3ref/free.html">sqlite3_free()</a> on 
that string when it has finished with it, and thus avoid a memory leak.
The idxStr value may also be a static constant string, in which case
the needToFreeIdxStr boolean should remain false.

</p><p>If the virtual table will output rows in the order specified by 
the ORDER BY clause, then the orderByConsumed flag may be set to 
true. If the output is not automatically in the correct order 
then orderByConsumed must be left in its default false setting. 
This will indicate to the SQLite core that it will need to do a 
separate sorting pass over the data after it comes out of the virtual table.

</p><p>The estimatedCost field should be set to the estimated number
of disk access operations required to execute this query against 
the virtual table. The SQLite core will often call xBestIndex 
multiple times with different constraints, obtain multiple cost
estimates, then choose the query plan that gives the lowest estimate.
The SQLite core initializes estimatedCost to a very large value
prior to invoking xBestIndex, so if xBestIndex determines that the
current combination of parameters is undesirable, it can leave the
estimatedCost field unchanged to discourage its use.

</p><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><p>If the current version of SQLite is 3.9.0 or greater, the idxFlags field

when xFilter is called, the argv[0] passed to xFilter will have 
the EXPR value of the aConstraint[3] constraint.

</p><p>By default, the SQLite core double checks all constraints on 
each row of the virtual table that it receives. If such a check 
is redundant, the xBestFilter method can suppress that double-check by 
setting aConstraintUsage[].omit.

</p><h3 id="return_value"><span>2.3.3. </span>Return Value</h3>

<p>The xBestIndex method should return SQLITE_OK on success.  If any
kind of fatal error occurs, an appropriate error code (ex: <a href="rescode.html#nomem">SQLITE_NOMEM</a>)
should be returned instead.

</p><p>If xBestIndex returns <a href="rescode.html#constraint">SQLITE_CONSTRAINT</a>, that does not indicate an
error.  Rather, SQLITE_CONSTRAINT indicates that the particular combination
of input parameters specified should not be used in the query plan.
The SQLITE_CONSTRAINT return is useful for <a href="vtab.html#tabfunc2">table-valued functions</a> that
have required parameters.  If the aConstraint[].usable field is false
for one of the required parameter, then the xBestIndex method should
return SQLITE_CONSTRAINT.

</p><p>The following example will better illustrate the use of SQLITE_CONSTRAINT
as a return value from xBestIndex:

</p><div class="codeblock"><pre>SELECT * FROM realtab, tablevaluedfunc(realtab.x);
</pre></div>

<p>Assuming that the first hidden column of "tablevaluedfunc" is "param1",
the query above is semantically equivalent to this:

</p><div class="codeblock"><pre>SELECT * FROM realtab, tablevaluedfunc
 WHERE tablevaluedfunc.param1 = realtab.x;
</pre></div>

<p>The query planner must decide between many possible implementations
of this query, but two plans in particular are of note:

</p><ol>
<li><p>Scan all
rows of realtab and for each row, find rows in tablevaluedfunc where
param1 is equal to realtab.x

</p></li><li><p>Scan all rows of tablevalued func and for each row find rows
in realtab where x is equal to tablevaluedfunc.param1.
</p></li></ol>

<p>The xBestIndex method will be invoked once for each of the potential
plans above.  For plan 1, the aConstraint[].usable flag for for the
SQLITE_CONSTRAINT_EQ constraint on the param1 column will be true because
the right-hand side value for the "param1 = ?" constraint will be known,
since it is determined by the outer realtab loop.
But for plan 2, the aConstraint[].usable flag for "param1 = ?" will be false
because the right-hand side value is determined by an inner loop and is thus
an unknown quantity.  Because param1 is a required input to the table-valued
functions, the xBestIndex method should return SQLITE_CONSTRAINT when presented 
with plan 2, indicating that a required input is missing.  This forces the
query planner to select plan 1.

<a name="xdisconnect"></a>

</p><h2 id="the_xdisconnect_method"><span>2.4. </span>The xDisconnect Method</h2>

<div class="codeblock"><pre>int (*xDisconnect)(sqlite3_vtab *pVTab);
</pre></div>

</p><p>The xRename method is optional.  If omitted, then the virtual
table may not be renamed using the ALTER TABLE RENAME command.

</p><p>The <a href="pragma.html#pragma_legacy_alter_table">PRAGMA legacy_alter_table</a> setting is enabled prior to invoking this
method, and the value for legacy_alter_table is restored after this
method finishes.  This is necessary for the correct operation of virtual
tables that make use of <a href="vtab.html#xshadowname">shadow tables</a> where the shadow tables must be
renamed to match the new virtual table name.  If the legacy_alter_format is
off, then the xConnect method will be invoked for the virtual table every
time the xRename method tries to change the name of the shadow table.

<a name="xsavepoint"></a>

</p><h2 id="the_xsavepoint_xrelease_and_xrollbackto_methods"><span>2.20. </span>The xSavepoint, xRelease, and xRollbackTo Methods</h2>

<p>
None of the xSavepoint(), xRelease(), or xRollbackTo() methods will ever
be called except in between calls to xBegin() and 
either xCommit() or xRollback().
</p>

<a name="xshadowname"></a>

<h2 id="the_xshadowname_method"><span>2.21. </span>The xShadowName Method</h2>

<p>Some virtual table implementations (ex: <a href="fts3.html">FTS3</a>, <a href="fts5.html">FTS5</a>, and <a href="rtree.html">RTREE</a>) make
use of real (non-virtual) database tables to store content.  For example,
when content is inserted into the FTS3 virtual table, the data is ultimately
stored in real tables named "%_content", "%_segdir", "%_segments", "%_stat",
and "%_docsize" where "%" is the name of the original virtual table.  This
auxiliary real tables that store content for a virtual table are called
"shadow tables".  See
(<a href="fts3.html#*shadowtab">1</a>),
(<a href="fts5.html#appendix_b">2</a>), and
(<a href="rtree.html#xshadow">3</a>) for additional information.

</p><p>The xShadowName method exists to allow SQLite to determine whether a
certain real table is in fact a shadow table for a virtual table.

</p><p>SQLite understands a real table to be a shadow table if all of
the following are true:
</p><p>
</p><ul>
<li> The name of the table contains one or more "_" characters.
</li><li> The part of the name prior to the last "_" exactly matches
     the name of a virtual table that was created using <a href="lang_createvtab.html">CREATE VIRTUAL TABLE</a>.
     (Shadow tables are not recognized for <a href="vtab.html#epovtab">eponymous virtual tables</a>
     and <a href="vtab.html#tabfunc2">table-valued functions</a>.)
</li><li> The virtual table contains an xShadowName method.
</li><li> The xShadowName method returns true when its input is the part
     of the table name past the last "_" character.
</li></ul>

<p>
If SQLite recognizes a table as a shadow table, and if the
<a href="c3ref/c_dbconfig_defensive.html#sqlitedbconfigdefensive">SQLITE_DBCONFIG_DEFENSIVE</a> flag is set, then the shadow table is read-only
for ordinary SQL statements.  The shadow table can still be written, but
only by SQL that is invoked from within one of the methods of
some virtual table implementation.

</p><p>
The whole point of the xShadowName method is to protect the content of
shadow tables from being corrupted by hostile SQL.  Every virtual table
implementation that uses shadow tables should be able to detect and cope
with corrupted shadow table content.  However, bugs in particular virtual 
table implementation might allow a deliberately corrupted shadow table to
cause a crash or other malfunction.  The xShadowName mechanism seeks to 
avoid zero-day exploits by preventing ordinary SQL statements from
deliberately corrupting shadow tables.

</p><p>
Shadow tables are read/write by default.
Shadow tables only become read-only when the <a href="c3ref/c_dbconfig_defensive.html#sqlitedbconfigdefensive">SQLITE_DBCONFIG_DEFENSIVE</a>
flag is set using <a href="c3ref/db_config.html">sqlite3_db_config()</a>.
Shadow tables need to be read/write by default in order to maintain
backwards compatibility.
For example, the SQL text generated by the <a href="cli.html#dump">.dump</a> command of the <a href="cli.html">CLI</a>
writes directly into shadow tables.
</p>

        /// take to store the copy in a place where it will be deallocated, such
        /// as in the idxStr field with needToFreeIdxStr set to 1.
        /// </para>
        /// <para>
        /// Note that xBestIndex will always be called before xFilter, since
        /// the idxNum and idxStr outputs from xBestIndex are required inputs to
        /// xFilter.  However, there is no guarantee that xFilter will be called
        /// following a successful xBestIndex.
        /// </para>
        /// <para>
        /// The xBestIndex method is required for every virtual table implementation.
        /// </para>
        /// <para>
        /// The main thing that the SQLite core is trying to communicate to 
        /// the virtual table is the constraints that are available to limit 

        /// </para>
        /// <para><code>
        /// x &gt;= 10
        /// x &lt;= 100
        /// y &lt; 999
        /// </code></para>
        /// <para>
        /// For each such constraint, the aConstraint[].iColumn field indicates which 
        /// column appears on the left-hand side of the constraint.
        /// The first column of the virtual table is column 0. 
        /// The rowid of the virtual table is column -1. 
        /// The aConstraint[].op field indicates which operator is used. 
        /// The SQLITE_INDEX_CONSTRAINT_* constants map integer constants 
        /// into operator values.
        /// Columns occur in the order they were defined by the call to

        /// </para>
        /// <para>
        /// The idxStr value may be a string obtained from an SQLite
        /// memory allocation function such as sqlite3_mprintf(). 
        /// If this is the case, then the needToFreeIdxStr flag must be set to 
        /// true so that the SQLite core will know to call sqlite3_free() on 
        /// that string when it has finished with it, and thus avoid a memory leak.
        /// The idxStr value may also be a static constant string, in which case
        /// the needToFreeIdxStr boolean should remain false.
        /// </para>
        /// <para>
        /// If the virtual table will output rows in the order specified by 
        /// the ORDER BY clause, then the orderByConsumed flag may be set to 
        /// true. If the output is not automatically in the correct order 
        /// then orderByConsumed must be left in its default false setting. 
        /// This will indicate to the SQLite core that it will need to do a 
        /// separate sorting pass over the data after it comes out of the virtual table.
        /// </para>
        /// <para>
        /// The estimatedCost field should be set to the estimated number
        /// of disk access operations required to execute this query against 
        /// the virtual table. The SQLite core will often call xBestIndex 
        /// multiple times with different constraints, obtain multiple cost
        /// estimates, then choose the query plan that gives the lowest estimate.
        /// The SQLite core initializes estimatedCost to a very large value
        /// prior to invoking xBestIndex, so if xBestIndex determines that the
        /// current combination of parameters is undesirable, it can leave the
        /// estimatedCost field unchanged to discourage its use.
        /// </para>
        /// <para>
        /// 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.
        /// </para>

        /// </para>
        /// <para>
        /// By default, the SQLite core double checks all constraints on 
        /// each row of the virtual table that it receives. If such a check 
        /// is redundant, the xBestFilter method can suppress that double-check by 
        /// setting aConstraintUsage[].omit.
        /// </para>
        /// <para>
        /// The xBestIndex method should return SQLITE_OK on success.  If any
        /// kind of fatal error occurs, an appropriate error code (ex: SQLITE_NOMEM)
        /// should be returned instead.
        /// </para>
        /// <para>
        /// If xBestIndex returns SQLITE_CONSTRAINT, that does not indicate an
        /// error.  Rather, SQLITE_CONSTRAINT indicates that the particular combination
        /// of input parameters specified should not be used in the query plan.
        /// The SQLITE_CONSTRAINT return is useful for table-valued functions that
        /// have required parameters.  If the aConstraint[].usable field is false
        /// for one of the required parameter, then the xBestIndex method should
        /// return SQLITE_CONSTRAINT.
        /// </para>
        /// <para>
        /// The following example will better illustrate the use of SQLITE_CONSTRAINT
        /// as a return value from xBestIndex:
        /// </para>
        /// <para><code>
        /// SELECT * FROM realtab, tablevaluedfunc(realtab.x);
        /// </code></para>
        /// <para>
        /// Assuming that the first hidden column of "tablevaluedfunc" is "param1",
        /// the query above is semantically equivalent to this:
        /// </para>
        /// <para><code>
        /// SELECT * FROM realtab, tablevaluedfunc
        ///  WHERE tablevaluedfunc.param1 = realtab.x;
        /// </code></para>
        /// <para>
        /// The query planner must decide between many possible implementations
        /// of this query, but two plans in particular are of note:
        /// </para>
        /// <![CDATA[<ol>]]>
        /// <![CDATA[<li>]]>Scan all
        /// rows of realtab and for each row, find rows in tablevaluedfunc where
        /// param1 is equal to realtab.x
        /// <![CDATA[</li>]]><![CDATA[<li>]]>Scan all rows of tablevalued func and for each row find rows
        /// in realtab where x is equal to tablevaluedfunc.param1.
        /// <![CDATA[</li>]]><![CDATA[</ol>]]>
        /// <para>
        /// The xBestIndex method will be invoked once for each of the potential
        /// plans above.  For plan 1, the aConstraint[].usable flag for for the
        /// SQLITE_CONSTRAINT_EQ constraint on the param1 column will be true because
        /// the right-hand side value for the "param1 = ?" constraint will be known,
        /// since it is determined by the outer realtab loop.
        /// But for plan 2, the aConstraint[].usable flag for "param1 = ?" will be false
        /// because the right-hand side value is determined by an inner loop and is thus
        /// an unknown quantity.  Because param1 is a required input to the table-valued
        /// functions, the xBestIndex method should return SQLITE_CONSTRAINT when presented 
        /// with plan 2, indicating that a required input is missing.  This forces the
        /// query planner to select plan 1.
        /// </para>
        /// </summary>
        /// <param name="pVtab">
        /// The native pointer to the sqlite3_vtab derived structure.
        /// </param>
        /// <param name="pIndex">
        /// The native pointer to the sqlite3_index_info structure.
        /// </param>