1.Dd March 11, 2017 2.Dt SQLITE3_INDEX_INFO 3 3.Os 4.Sh NAME 5.Nm sqlite3_index_info 6.Nd Virtual Table Indexing Information 7.Sh SYNOPSIS 8.Vt struct sqlite3_index_info ; 9.Sh DESCRIPTION 10The sqlite3_index_info structure and its substructures is used as part 11of the virtual table interface to pass information into 12and receive the reply from the xBestIndex method of a virtual table module. 13The fields under **Inputs** are the inputs to xBestIndex and are read-only. 14xBestIndex inserts its results into the **Outputs** fields. 15.Pp 16The aConstraint[] array records WHERE clause constraints of the form: 17.Bd -ragged 18column OP expr 19.Ed 20.Pp 21where OP is =, <, <=, >, or >=. 22The particular operator is stored in aConstraint[].op using one of 23the SQLITE_INDEX_CONSTRAINT_ values. 24The index of the column is stored in aConstraint[].iColumn. 25aConstraint[].usable is TRUE if the expr on the right-hand side can 26be evaluated (and thus the constraint is usable) and false if it cannot. 27.Pp 28The optimizer automatically inverts terms of the form "expr OP column" 29and makes other simplifications to the WHERE clause in an attempt to 30get as many WHERE clause terms into the form shown above as possible. 31The aConstraint[] array only reports WHERE clause terms that are relevant 32to the particular virtual table being queried. 33.Pp 34Information about the ORDER BY clause is stored in aOrderBy[]. 35Each term of aOrderBy records a column of the ORDER BY clause. 36.Pp 37The colUsed field indicates which columns of the virtual table may 38be required by the current scan. 39Virtual table columns are numbered from zero in the order in which 40they appear within the CREATE TABLE statement passed to sqlite3_declare_vtab(). 41For the first 63 columns (columns 0-62), the corresponding bit is set 42within the colUsed mask if the column may be required by SQLite. 43If the table has at least 64 columns and any column to the right of 44the first 63 is required, then bit 63 of colUsed is also set. 45In other words, column iCol may be required if the expression (colUsed 46& ((sqlite3_uint64)1 << (iCol>=63 ? 63 : iCol))) evaluates to non-zero. 47.Pp 48The xBestIndex method must fill aConstraintUsage[] with information 49about what parameters to pass to xFilter. 50If argvIndex>0 then the right-hand side of the corresponding aConstraint[] 51is evaluated and becomes the argvIndex-th entry in argv. 52If aConstraintUsage[].omit is true, then the constraint is assumed 53to be fully handled by the virtual table and is not checked again by 54SQLite. 55.Pp 56The idxNum and idxPtr values are recorded and passed into the xFilter 57method. 58sqlite3_free() is used to free idxPtr if and only if 59needToFreeIdxPtr is true. 60.Pp 61The orderByConsumed means that output from xFilter/xNext 62will occur in the correct order to satisfy the ORDER BY clause so that 63no separate sorting step is required. 64.Pp 65The estimatedCost value is an estimate of the cost of a particular 66strategy. 67A cost of N indicates that the cost of the strategy is similar to a 68linear scan of an SQLite table with N rows. 69A cost of log(N) indicates that the expense of the operation is similar 70to that of a binary search on a unique indexed field of an SQLite table 71with N rows. 72.Pp 73The estimatedRows value is an estimate of the number of rows that will 74be returned by the strategy. 75.Pp 76The xBestIndex method may optionally populate the idxFlags field with 77a mask of SQLITE_INDEX_SCAN_* flags. 78Currently there is only one such flag - SQLITE_INDEX_SCAN_UNIQUE. 79If the xBestIndex method sets this flag, SQLite assumes that the strategy 80may visit at most one row. 81.Pp 82Additionally, if xBestIndex sets the SQLITE_INDEX_SCAN_UNIQUE flag, 83then SQLite also assumes that if a call to the xUpdate() method is 84made as part of the same statement to delete or update a virtual table 85row and the implementation returns SQLITE_CONSTRAINT, then there is 86no need to rollback any database changes. 87In other words, if the xUpdate() returns SQLITE_CONSTRAINT, the database 88contents must be exactly as they were before xUpdate was called. 89By contrast, if SQLITE_INDEX_SCAN_UNIQUE is not set and xUpdate returns 90SQLITE_CONSTRAINT, any database changes made by the xUpdate method 91are automatically rolled back by SQLite. 92.Pp 93IMPORTANT: The estimatedRows field was added to the sqlite3_index_info 94structure for SQLite version 3.8.2 (dateof:3.8.2). 95If a virtual table extension is used with an SQLite version earlier 96than 3.8.2, the results of attempting to read or write the estimatedRows 97field are undefined (but are likely to included crashing the application). 98The estimatedRows field should therefore only be used if sqlite3_libversion_number() 99returns a value greater than or equal to 3008002. 100Similarly, the idxFlags field was added for version 3.9.0 101(dateof:3.9.0). 102It may therefore only be used if sqlite3_libversion_number() returns 103a value greater than or equal to 3009000. 104.Sh SEE ALSO 105.Xr sqlite3_malloc 3 , 106.Xr sqlite3_version 3 , 107.Xr SQLITE_INDEX_CONSTRAINT_EQ 3 , 108.Xr sqlite3_module 3 109