Documentation Source Text

  matchinfo() function efficiently.

<h1 id=fts4aux tags="fts4aux">Fts4aux - Direct Access to the Full-Text Index</h1>

<p>
  As of version 3.7.6, SQLite includes a new virtual table module called 
  "fts4aux", which can be used to inspect the full-text index of an exiting
  FTS3 or FTS4 table directly (despite its name, fts4aux works just as well
  with FTS3 tables as it does with FTS4 tables). Fts4aux tables are read-only.
  The only way to modify the contents of an fts4aux table is by modifying the
  contents of the associated FTS table. The fts4aux module is automatically
  included in all [compile fts|builds that include FTS].

<p>
  An fts4aux virtual table is constructed with a single argument - the 
  unqualified name of the FTS table that it will be used to access.
  For example:

<codeblock>
  <i>-- Create an FTS4 table</i>
  CREATE VIRTUAL TABLE ft USING fts4;

  <i>-- Create an fts4aux table to access the full-text index for table "ft"</i>
  CREATE VIRTUAL TABLE ft_terms USING fts4aux(ft);
</codeblock>

<p>
  There is one row in an fts4aux table for each distinct term in the

  associated FTS table. An fts4aux table always has the same three columns, 
  as follows, from left to right:

<table striped=1>
  <tr><th>Column Name<th>Column Contents
  <tr><td>term<td> 
    Contains the text of the term for this row.





  <tr><td>documents<td>



    Contains the number of rows of the FTS table that contain at 
    least one instance of the term (in any column). This column always 
    contains an integer value.





  <tr><td>occurrences<td>



    Contains the total number of instances of the term in all rows of the 
    FTS table. This column also always contains an integer value.




</table>

<p>
  For example, using the tables created above:

<codeblock>
  INSERT INTO ft VALUES('Apple banana Cherry');
  INSERT INTO ft VALUES('Banana Date Fig');
  INSERT INTO ft VALUES('Cherry Grapefruit Cherry');

  <i>-- The following query returns this data:</i>
  <i>--</i>
  <i>--     apple       |  1  |  1</i>

  <i>--     banana      |  2  |  2</i>

  <i>--     cherry      |  2  |  3</i>


  <i>--     date        |  1  |  1</i>
  <i>--     fig         |  1  |  1</i>
  <i>--     grapefruit  |  1  |  1</i>




  <i>--</i>
  SELECT term, documents, occurrences FROM ft_terms;
</codeblock>

<p>
  In the example, the values in the "term" column are all lower case, even
  though they were inserted into table "ft" in mixed case. This is because
  an fts3aux table contains the terms as extracted from the document text
  by the [tokenizer]. In this case, since table "ft" uses the 
  [tokenizer|simple tokenizer], this means all terms have been folded to
  lower case.



<p>
  During a transaction, some of the data written to an FTS table may be 
  cached in memory and written to the database only when the transaction is 
  committed. However the implementation of the fts4aux module is only able 
  to read data from the database. In practice this means that if an fts4aux 
  table is queried from within a transaction in which the associated 

  matchinfo() function efficiently.

<h1 id=fts4aux tags="fts4aux">Fts4aux - Direct Access to the Full-Text Index</h1>

<p>
  As of version 3.7.6, SQLite includes a new virtual table module called 
  "fts4aux", which can be used to inspect the full-text index of an exiting
  FTS table directly. Despite its name, fts4aux works just as well with FTS3
  tables as it does with FTS4 tables. Fts4aux tables are read-only. The only
  way to modify the contents of an fts4aux table is by modifying the
  contents of the associated FTS table. The fts4aux module is automatically
  included in all [compile fts|builds that include FTS].

<p>
  An fts4aux virtual table is constructed with a single argument - the 
  unqualified name of the FTS table that it will be used to access.
  For example:

<codeblock>
  <i>-- Create an FTS4 table</i>
  CREATE VIRTUAL TABLE ft USING fts4(x, y);

  <i>-- Create an fts4aux table to access the full-text index for table "ft"</i>
  CREATE VIRTUAL TABLE ft_terms USING fts4aux(ft);
</codeblock>

<p>
  For each term present in the FTS table, there are between 2 and N+1 rows
  in the fts4aux table, where N is the number of user-defined columns in
  the associated FTS table. An fts4aux table always has the same four columns, 
  as follows, from left to right:

<table striped=1>
  <tr><th>Column Name<th>Column Contents
  <tr><td>term<td> 
    Contains the text of the term for this row.
  <tr><td>col<td> 
    This column may contain either the text value '*' (i.e. a single 
    character, UTF codepoint 42) or an integer between 0 and N-1, where N is
    again the number of user-defined columns in the corresponing FTS table.

  <tr><td>documents<td>
    This column always contains an integer value greater than zero.
    <br><br>
    If the "col" column contains the value '*', then this column
    contains the number of rows of the FTS table that contain at least one
    instance of the term (in any column). If col contains an integer

    value, then this column contains the number of rows of the FTS table that
    contain at least one instance of the term in the column identified by
    the col value. As usual, the columns of the FTS table are numbered
    from left to right, starting with zero.

  <tr><td>occurrences<td>
    This column also always contains an integer value greater than zero.
    <br><br>
    If the "col" column contains the value '*', then this column
    contains the total number of instances of the term in all rows of the 

    FTS table (in any column). Otherwise, if col contains an integer
    value, then this column contains the total number of instances of the
    term that appear in the FTS table column identified by the col
    value.
</table>

<p>
  For example, using the tables created above:

<codeblock>
  INSERT INTO ft(x, y) VALUES('Apple banana', 'Cherry');
  INSERT INTO ft(x, y) VALUES('Banana Date Date', 'cherry');
  INSERT INTO ft(x, y) VALUES('Cherry Elderberry', 'Elderberry');

  <i>-- The following query returns this data:</i>
  <i>--</i>
  <i>--     apple       |  *  |  1  |  1</i>
  <i>--     apple       |  0  |  1  |  1</i>
  <i>--     banana      |  *  |  2  |  2</i>
  <i>--     banana      |  0  |  2  |  2</i>
  <i>--     cherry      |  *  |  3  |  3</i>
  <i>--     cherry      |  0  |  1  |  1</i>
  <i>--     cherry      |  1  |  2  |  2</i>
  <i>--     date        |  *  |  1  |  2</i>


  <i>--     date        |  0  |  1  |  2</i>
  <i>--     elderberry  |  *  |  1  |  2</i>
  <i>--     elderberry  |  1  |  1  |  1</i>
  <i>--     elderberry  |  1  |  1  |  1</i>
  <i>--</i>
  SELECT term, col, documents, occurrences FROM ft_terms;
</codeblock>

<p>
  In the example, the values in the "term" column are all lower case, 
  even though they were inserted into table "ft" in mixed case. This is because
  an fts3aux table contains the terms as extracted from the document text
  by the [tokenizer]. In this case, since table "ft" uses the 
  [tokenizer|simple tokenizer], this means all terms have been folded to
  lower case. Also, there is (for example) no row with column "term"
  set to "apple" and column "col" set to 1. Since there are no instances
  of the term "apple" in column 1, no row is present in the fts4aux table.

<p>
  During a transaction, some of the data written to an FTS table may be 
  cached in memory and written to the database only when the transaction is 
  committed. However the implementation of the fts4aux module is only able 
  to read data from the database. In practice this means that if an fts4aux 
  table is queried from within a transaction in which the associated