*** 51,55 ****
#define SQLITE_INDEX_CONSTRAINT_GE 32
#define SQLITE_INDEX_CONSTRAINT_MATCH 64
! The sqlite3_index_info and the constants shown above
! are all defined in the sqlite3.h header file.
--- 51,206 ----
#define SQLITE_INDEX_CONSTRAINT_GE 32
#define SQLITE_INDEX_CONSTRAINT_MATCH 64
! The SQLite core calls the xBestIndex method when it is compiling a
! query that involves a virtual table. In other words,
! SQLite calls this method when it is running sqlite3_prepare().
! By calling this method, the SQLite core is saying to the
! virtual table that it needs to access some subset of the
! rows in the virtual table and it wants to know the most
! efficient way to do that access. This method replies
! with information that the SQLite core can then use to
! conduct an efficient search of the virtual table.
!
! *Inputs*
!
! Before calling this method, the SQLite core initializes
! an instance of the sqlite3_index_info structure with
! information about the query that it is currently trying
! to process. This information derives mainly from
! the WHERE clause and ORDER BY or GROUP BY clauses
! of the query, but also from any ON or USING clauses
! if the query is a join. The information that the
! SQLite core provides to the xBestIndex method is
! held in the part of the structure that is marked
! as "Inputs". The "Outputs" section is initialized
! to zero.
!
! The main thing that the SQLite core is trying to
! communicate to the virtual table is the constraints
! that are available to limit the number of rows
! that need to be searched. The aConstraint[] array
! contains one entry for each constraint. There will
! be exactly nConstraint entries in that array.
!
! Each constraint will correspond to a term in the
! WHERE clause or in a USING or ON clause that is
! of the form
!
! column OP EXPR
!
! Where "column" is a column in the virtual table,
! OP is an operator like "=" or "<", and EXPR is
! an arbitrary expression. So, for example, if
! the WHERE clause contained a term like this:
!
! a = 5
!
! Then one of the constraints would be on the "a"
! column with operator "=" and an expression of "5".
! Constraints are not a literal representation of
! the WHERE clause. The query optimizer translates
! the WHERE clause in order to extract as many
! constraints as it can. So, for example, if the
! WHERE clause contained something like this:
!
! x BETWEEN 10 AND 100 AND 999>y
!
! The query optimizer would translate this into
! three separate constraints:
!
! x >= 10
! x <= 100
! y < 999
!
! For each 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.
!
! The aConstraint[] array contains information about
! all constraints that apply to the virtual table.
! But some of the constraints might not be usable
! because of the way tables are ordered in a join.
! The xBestIndex method should therefore only consider
! constraints that have a aConstraint[].usable flag
! which is true.
!
! In addition to WHERE clause constraints, the
! SQLite core also tells the xBestIndex method about
! the ORDER BY clause. (In an aggregate query, the
! SQLite core might put in GROUP BY clause information
! in place of the ORDER BY clause information, but this
! fact should not make any difference to the xBestIndex
! method.) If all terms of the ORDER BY clause are
! columns in the virtual table, then nOrderBy will
! be the number of terms in the ORDER BY clause
! and the aOrderBy[] array will identify the column
! for each term in the order by clause and whether or
! not that column is ASC or DESC.
!
! *Outputs*
!
! Given all of the information above, the job of the
! xBestIndex method it to figure out the best way to
! search the virtual table given the available constraints
! and the desired output order of the rows.
!
! The idxNum and idxStr fields are filled with information
! that communicates an indexing strategy to to the xFilter method.
! The information in idxNum and idxStr is arbitrary as far as
! the SQLite core is concerned. The SQLite core just copies
! the information through to the xFilter method. Any
! desired meaning can be assigned to idxNum and idxStr
! as long as xBestIndex and xFilter agree on what that
! meaning is.
!
! The idxStr value can be a string obtained from
! sqlite3_mprintf(). If this is the case, then the
! needToFreeIdxStr flag should 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.
!
! If the virtual table will output rows in the order
! specified by the ORDER BY clause, then the
! orderByConsumed flag should be set to true. If
! the output is not automatically in the correct order
! then orderByConsumed should 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.
!
! 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 aConstraintUsage[] array contains one element for
! each of the nConstraint constraints in the inputs section
! of the sqlite3_index_info structure. The aConstraintUsage[]
! array is used by xBestIndex to tell the core how it is
! using the constraints.
!
! The xBestIndex method may set aConstraintUsage[].argvIndex
! entries to values greater than one. Exactly one should
! entry should be set to 1, another to 2, another to 3,
! and so forth up to as many or as few as the xBestIndex
! method wants. The EXPR of the corresponding constraints
! will then be passed in as the argv[] parameters to xFilter.
!
! For example, if the aConstraint[3].argvIndex is set to 1,
! then when xFilter is called, the argv[0] passed to
! xFilter will have the EXPR value of the aConstraint[3]
! constraint.
!
! 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 the that check by setting aConstraintUsage[].omit.
Small. Fast. Reliable.
Choose any three.
Choose any three.
|
|