1.Dd January 24, 2024 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.In sqlite3.h 9.Vt struct sqlite3_index_info ; 10.Sh DESCRIPTION 11The sqlite3_index_info structure and its substructures is used as part 12of the virtual table interface to pass information into 13and receive the reply from the xBestIndex method of a virtual table module. 14The fields under **Inputs** are the inputs to xBestIndex and are read-only. 15xBestIndex inserts its results into the **Outputs** fields. 16.Pp 17The aConstraint[] array records WHERE clause constraints of the form: 18.Bd -ragged 19column OP expr 20.Ed 21.Pp 22where OP is =, <, <=, >, or >=. 23The particular operator is stored in aConstraint[].op using one of 24the SQLITE_INDEX_CONSTRAINT_ values. 25The index of the column is stored in aConstraint[].iColumn. 26aConstraint[].usable is TRUE if the expr on the right-hand side can 27be evaluated (and thus the constraint is usable) and false if it cannot. 28.Pp 29The optimizer automatically inverts terms of the form "expr OP column" 30and makes other simplifications to the WHERE clause in an attempt to 31get as many WHERE clause terms into the form shown above as possible. 32The aConstraint[] array only reports WHERE clause terms that are relevant 33to the particular virtual table being queried. 34.Pp 35Information about the ORDER BY clause is stored in aOrderBy[]. 36Each term of aOrderBy records a column of the ORDER BY clause. 37.Pp 38The colUsed field indicates which columns of the virtual table may 39be required by the current scan. 40Virtual table columns are numbered from zero in the order in which 41they appear within the CREATE TABLE statement passed to sqlite3_declare_vtab(). 42For the first 63 columns (columns 0-62), the corresponding bit is set 43within the colUsed mask if the column may be required by SQLite. 44If the table has at least 64 columns and any column to the right of 45the first 63 is required, then bit 63 of colUsed is also set. 46In other words, column iCol may be required if the expression (colUsed 47& ((sqlite3_uint64)1 << (iCol>=63 ? 63 : iCol))) evaluates to non-zero. 48.Pp 49The xBestIndex method must fill aConstraintUsage[] with information 50about what parameters to pass to xFilter. 51If argvIndex>0 then the right-hand side of the corresponding aConstraint[] 52is evaluated and becomes the argvIndex-th entry in argv. 53If aConstraintUsage[].omit is true, then the constraint is assumed 54to be fully handled by the virtual table and might not be checked again 55by the byte code. 56The aConstraintUsage[].omit flag is an optimization hint. 57When the omit flag is left in its default setting of false, the constraint 58will always be checked separately in byte code. 59If the omit flag is change to true, then the constraint may or may 60not be checked in byte code. 61In other words, when the omit flag is true there is no guarantee that 62the constraint will not be checked again using byte code. 63.Pp 64The idxNum and idxStr values are recorded and passed into the xFilter 65method. 66.Fn sqlite3_free 67is used to free idxStr if and only if needToFreeIdxStr is true. 68.Pp 69The orderByConsumed means that output from xFilter/xNext 70will occur in the correct order to satisfy the ORDER BY clause so that 71no separate sorting step is required. 72.Pp 73The estimatedCost value is an estimate of the cost of a particular 74strategy. 75A cost of N indicates that the cost of the strategy is similar to a 76linear scan of an SQLite table with N rows. 77A cost of log(N) indicates that the expense of the operation is similar 78to that of a binary search on a unique indexed field of an SQLite table 79with N rows. 80.Pp 81The estimatedRows value is an estimate of the number of rows that will 82be returned by the strategy. 83.Pp 84The xBestIndex method may optionally populate the idxFlags field with 85a mask of SQLITE_INDEX_SCAN_* flags. 86Currently there is only one such flag - SQLITE_INDEX_SCAN_UNIQUE. 87If the xBestIndex method sets this flag, SQLite assumes that the strategy 88may visit at most one row. 89.Pp 90Additionally, if xBestIndex sets the SQLITE_INDEX_SCAN_UNIQUE flag, 91then SQLite also assumes that if a call to the xUpdate() method is 92made as part of the same statement to delete or update a virtual table 93row and the implementation returns SQLITE_CONSTRAINT, then there is 94no need to rollback any database changes. 95In other words, if the xUpdate() returns SQLITE_CONSTRAINT, the database 96contents must be exactly as they were before xUpdate was called. 97By contrast, if SQLITE_INDEX_SCAN_UNIQUE is not set and xUpdate returns 98SQLITE_CONSTRAINT, any database changes made by the xUpdate method 99are automatically rolled back by SQLite. 100.Pp 101IMPORTANT: The estimatedRows field was added to the sqlite3_index_info 102structure for SQLite version 3.8.2 (dateof:3.8.2). 103If a virtual table extension is used with an SQLite version earlier 104than 3.8.2, the results of attempting to read or write the estimatedRows 105field are undefined (but are likely to include crashing the application). 106The estimatedRows field should therefore only be used if 107.Fn sqlite3_libversion_number 108returns a value greater than or equal to 3008002. 109Similarly, the idxFlags field was added for version 3.9.0 110(dateof:3.9.0). 111It may therefore only be used if sqlite3_libversion_number() returns 112a value greater than or equal to 3009000. 113.Sh IMPLEMENTATION NOTES 114These declarations were extracted from the 115interface documentation at line 7331. 116.Bd -literal 117struct sqlite3_index_info { 118 /* Inputs */ 119 int nConstraint; /* Number of entries in aConstraint */ 120 struct sqlite3_index_constraint { 121 int iColumn; /* Column constrained. -1 for ROWID */ 122 unsigned char op; /* Constraint operator */ 123 unsigned char usable; /* True if this constraint is usable */ 124 int iTermOffset; /* Used internally - xBestIndex should ignore */ 125 } *aConstraint; /* Table of WHERE clause constraints */ 126 int nOrderBy; /* Number of terms in the ORDER BY clause */ 127 struct sqlite3_index_orderby { 128 int iColumn; /* Column number */ 129 unsigned char desc; /* True for DESC. False for ASC. */ 130 } *aOrderBy; /* The ORDER BY clause */ 131 /* Outputs */ 132 struct sqlite3_index_constraint_usage { 133 int argvIndex; /* if >0, constraint is part of argv to xFilter */ 134 unsigned char omit; /* Do not code a test for this constraint */ 135 } *aConstraintUsage; 136 int idxNum; /* Number used to identify the index */ 137 char *idxStr; /* String, possibly obtained from sqlite3_malloc */ 138 int needToFreeIdxStr; /* Free idxStr using sqlite3_free() if true */ 139 int orderByConsumed; /* True if output is already ordered */ 140 double estimatedCost; /* Estimated cost of using this index */ 141 /* Fields below are only available in SQLite 3.8.2 and later */ 142 sqlite3_int64 estimatedRows; /* Estimated number of rows returned */ 143 /* Fields below are only available in SQLite 3.9.0 and later */ 144 int idxFlags; /* Mask of SQLITE_INDEX_SCAN_* flags */ 145 /* Fields below are only available in SQLite 3.10.0 and later */ 146 sqlite3_uint64 colUsed; /* Input: Mask of columns used by statement */ 147}; 148.Ed 149.Sh SEE ALSO 150.Xr sqlite3_malloc 3 , 151.Xr sqlite3_module 3 , 152.Xr sqlite3_version 3 , 153.Xr SQLITE_INDEX_CONSTRAINT_EQ 3 154