Documentation Source Text

Check-in [43d4cb5785]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Add information about the sqlite3_index_info.colUsed field.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 43d4cb5785a45d4299cbd4e5214de70ab8b749cc
User & Date: drh 2015-11-30 19:36:59
Context
2015-12-02
20:59
Update the testing document to indicate that the unix VFS now has 100% branch test coverage. check-in: c345c5933b user: drh tags: trunk
2015-11-30
19:36
Add information about the sqlite3_index_info.colUsed field. check-in: 43d4cb5785 user: drh tags: trunk
2015-11-25
18:42
Update the documentation for the LIKE/GLOB/REGEXP enhancement to virtual tables. check-in: 6b8138c898 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to pages/changes.in.

19
20
21
22
23
24
25


26
27
28
29
30
31
32
  set aChng($nChng) [list $date $desc $options]
  incr nChng
}

chng {2016-00-00 (3.10.0)} {
<p><b>General improvements:</b>
<li>Added support for [LIKE], [GLOB], and [REGEXP] operators on [virtual tables].


<li>Enhance the [PRAGMA cache_spill] statement to accept a 32-bit integer
    parameter which is the threshold below which cache spilling is prohibited.
<li>On unix, if a symlink to a database file is opened, then the corresponding
    journal files are based on the actual filename, not the symlink name.
<li>Added the "--transaction" option to [sqldiff].
<li>Added the [sqlite3_db_cacheflush()] interface.
<li>Added the [sqlite3_strlike()] interface.







>
>







19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
  set aChng($nChng) [list $date $desc $options]
  incr nChng
}

chng {2016-00-00 (3.10.0)} {
<p><b>General improvements:</b>
<li>Added support for [LIKE], [GLOB], and [REGEXP] operators on [virtual tables].
<li>Added the [colUsed field] to [sqlite3_index_info] for use by
    the [sqlite3_module.xBestIndex] method.
<li>Enhance the [PRAGMA cache_spill] statement to accept a 32-bit integer
    parameter which is the threshold below which cache spilling is prohibited.
<li>On unix, if a symlink to a database file is opened, then the corresponding
    journal files are based on the actual filename, not the symlink name.
<li>Added the "--transaction" option to [sqldiff].
<li>Added the [sqlite3_db_cacheflush()] interface.
<li>Added the [sqlite3_strlike()] interface.

Changes to pages/vtab.in.

603
604
605
606
607
608
609


610
611
612
613
614
615
616
617

618
619
620
621
622
623
624
...
736
737
738
739
740
741
742











743
744
745
746
747
748
749
    int needToFreeIdxStr;      /* Free idxStr using sqlite3_free() if true */
    int orderByConsumed;       /* True if output is already ordered */
    double estimatedCost;      /* Estimated cost of using this index */
    <b>/* Fields below are only available in SQLite 3.8.2 and later */</b>
    sqlite3_int64 estimatedRows;    /* Estimated number of rows returned */
    <b>/* Fields below are only available in SQLite 3.9.0 and later */</b>
    int idxFlags;              /* Mask of SQLITE_INDEX_SCAN_* flags */


  };
</pre></blockquote>

<p>Please note the warnings on the "estimatedRows" and "idxFlags" field.
These fields were added with SQLite versions 3.8.2 and 3.9.0, respectively. 
Any extension that reads or writes these fields must first check that the 
version of the SQLite library in use is greater than or equal to 3.8.2 or
3.9.0 - perhaps using a call to [sqlite3_version()]. The result of attempting 

to access these fields in an sqlite3_index_info structure created by an 
older version of SQLite are undefined.

<p>In addition, there are some defined constants:

<blockquote><pre>
  #define SQLITE_INDEX_CONSTRAINT_EQ      2
................................................................................
(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.












<h4>2.3.2 Outputs</h4>

<p>Given all of the information above, the job of the xBestIndex 
method it to figure out the best way to search the virtual table.

<p>The xBestIndex method fills the idxNum and idxStr fields with 







>
>



|
|

|
|
>







 







>
>
>
>
>
>
>
>
>
>
>







603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
...
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
    int needToFreeIdxStr;      /* Free idxStr using sqlite3_free() if true */
    int orderByConsumed;       /* True if output is already ordered */
    double estimatedCost;      /* Estimated cost of using this index */
    <b>/* Fields below are only available in SQLite 3.8.2 and later */</b>
    sqlite3_int64 estimatedRows;    /* Estimated number of rows returned */
    <b>/* Fields below are only available in SQLite 3.9.0 and later */</b>
    int idxFlags;              /* Mask of SQLITE_INDEX_SCAN_* flags */
    <b>/* Fields below are only available in SQLite 3.10.0 and later */</b>
    sqlite3_uint64 colUsed;    /* Input: Mask of columns used by statement */
  };
</pre></blockquote>

<p>Note the warnings on the "estimatedRows", "idxFlags", and colUsed fields.
These fields were added with SQLite versions 3.8.2, 3.9.0, and 3.10.0, respectively. 
Any extension that reads or writes these fields must first check that the 
version of the SQLite library in use is greater than or equal to appropriate
version - perhaps comparing the value returned from [sqlite3_libversion_number()]
against constants 3008002, 3009000, and/or 3010000. The result of attempting 
to access these fields in an sqlite3_index_info structure created by an 
older version of SQLite are undefined.

<p>In addition, there are some defined constants:

<blockquote><pre>
  #define SQLITE_INDEX_CONSTRAINT_EQ      2
................................................................................
(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.

<tcl>hd_fragment colUsed {colUsed field}</tcl>
<p>In SQLite version 3.10.0 and later, the colUsed field is available
to indicate which fields of the virtual table are actually used by the
statement being prepared.  If the lowest bit of colUsed is set, that
means that the first column is used.  The second lowest bit corresponds
to the second column.  And so forth.  If the most significant bit of
colUsed is set, that means that one or more columns other than the 
first 63 columns are used.  If column usage information is needed by the
[xFilter] method, then the required bits must be encoded into either
the idxNum or idxStr output fields.

<h4>2.3.2 Outputs</h4>

<p>Given all of the information above, the job of the xBestIndex 
method it to figure out the best way to search the virtual table.

<p>The xBestIndex method fills the idxNum and idxStr fields with