Documentation Source Text

}
RESCODE SQLITE_CONSTRAINT  19   {
  The SQLITE_CONSTRAINT error code means that an SQL constraint violation
  occurred while trying to process an SQL statement.  Additional information
  about the failed constraint can be found by consulting the
  accompanying error message (returned via [sqlite3_errmsg()] or
  [sqlite3_errmsg16()]) or by looking at the [extended error code].
  <p>
  The SQLITE_CONSTRAINT code can also be used as the return value from
  the [xBestIndex()] method of a [virtual table] implementation.  When
  xBestIndex() returns SQLITE_CONSTRAINT, that indicates that the particular
  combination of inputs submitted to xBestIndex() cannot result in a
  usable query plan and should not be given further consideration.
}
RESCODE SQLITE_MISMATCH    20   {
  The SQLITE_MISMATCH error code indicates a datatype mismatch.
  <p>
  SQLite is normally very forgiving about mismatches between the type of
  a value and the declared type of the container in which that value is
  to be stored.  For example, SQLite allows the application to store

[sqlite3_index_info] 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>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.

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

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

<codeblock>
     x &gt;= 10
     x &lt;= 100
     y &lt; 999
</codeblock>

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

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

the EXPR value of the aConstraint[3] constraint.

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

<h3>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: [SQLITE_NOMEM])
should be returned instead.

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

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

<codeblock>
SELECT * FROM realtab, tablevaluedfunc(realtab.x);
</codeblock>

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

<codeblock>
SELECT * FROM realtab, tablevaluedfunc
 WHERE tablevaluedfunc.param1 = realtab.x;
</codeblock>

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

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

<li><p>Scan all rows of tablevalued func and for each row find rows
in realtab where x is equal to tablevaluedfunc.param1.
</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.
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.  Hence,
the xBestIndex method should return SQLITE_CONSTRAINT when presented with
plan 2, forcing SQLite to choose plan 1.

<tcl>hd_fragment xdisconnect {sqlite3_module.xDisconnect} {xDisconnect}</tcl>
<h2>The xDisconnect Method</h2>

<codeblock>
  int (*xDisconnect)(sqlite3_vtab *pVTab);
</codeblock>