Documentation Source Text

<p> The fts5vocab virtual table module allows users to extract information from
an FTS5 full-text index directly. The fts5vocab module is a part of FTS5 - it 
is available whenever FTS5 is.

<p> Each fts5vocab table is associated with a single FTS5 table. An fts5vocab
table is usually created by specifying two arguments in place of column names
in the CREATE VIRTUAL TABLE statement - the name of the associated FTS5 table
and the type of fts5vocab table. Currently there are three types of fts5vocab
table; "row", "col" and "instance". Unless the fts5vocab table is created
within the "temp" database, it must be part of the same database as the
associated FTS5 table.

<codeblock>
  <i>-- Create an fts5vocab "row" table to query the full-text index belonging
  -- to FTS5 table "ft1".</i>
  CREATE VIRTUAL TABLE ft1_v USING fts5vocab('ft1', 'row');

  <i>-- Create an fts5vocab "col" table to query the full-text index belonging
  -- to FTS5 table "ft2".</i>
  CREATE VIRTUAL TABLE ft2_v USING fts5vocab(ft2, col);

  <i>-- Create an fts5vocab "instance" table to query the full-text index
  -- belonging to FTS5 table "ft3".</i>
  CREATE VIRTUAL TABLE ft3_v USING fts5vocab(ft3, instance);
</codeblock>

<p> If an fts5vocab table is created in the temp database, it may be associated
with an FTS5 table in any attached database. In order to attach the fts5vocab
table to an FTS5 table located in a database other than "temp", the name of the
database is inserted before the FTS5 table name in the CREATE VIRTUAL TABLE 
arguments. For example:

<codeblock>
  <i>-- Create an fts5vocab "row" table to query the full-text index belonging
  -- to FTS5 table "ft1" in database "main".</i>
  CREATE VIRTUAL TABLE temp.ft1_v USING fts5vocab(main, 'ft1', 'row');

  <i>-- Create an fts5vocab "col" table to query the full-text index belonging
  -- to FTS5 table "ft2" in attached database "aux".</i>
  CREATE VIRTUAL TABLE temp.ft2_v USING fts5vocab('aux', ft2, col);

  <i>-- Create an fts5vocab "instance" table to query the full-text index 
  -- belonging to FTS5 table "ft3" in attached database "other".</i>
  CREATE VIRTUAL TABLE temp.ft2_v USING fts5vocab('aux', ft3, 'instance');
</codeblock>

<p> Specifying three arguments when creating an fts5vocab table in any database
other than "temp" results in an error.

<p> An fts5vocab table of type "row" contains one row for each distinct term
in the associated FTS5 table. The table columns are as follows:

  <tr><td>col<td>  The name of the FTS5 table column that contains the term.
  <tr><td>doc<td>  The number of rows in the FTS5 table for which column $col
                   contains at least one instance of the term.
  <tr><td>cnt<td>  The total number of instances of the term that appear in
                   column $col of the FTS5 table (considering all rows). 
</table>

<p> An fts5vocab table of type "instance" contains one row for each term
instance stored in the associated FTS index. Assuming the FTS5 table is
created with the 'detail' option set to 'full', table columns are as follows:

<table striped=1>
  <tr><th>Column<th>Contents
  <tr><td>term<td>   The term, as stored in the FTS5 index.
  <tr><td>doc<td>    The rowid of the document that contains the term instance.
  <tr><td>col<td>    The name of the column that contains the term instance.
  <tr><td>offset<td> The index of the term instance within its column. Terms 
                     are numbered in order of occurence starting from 0.
</table>

<p> If the FTS5 table is created with the 'detail' option set to 'col', then
the <i>offset</i> column of an instance virtual table always contains NULL.
In this case there is one row in the table for each unique term/doc/col 
combination. Or, if the FTS5 table is created with 'detail' set to 'none',
then both <i>offset</i> and <i>col</i> always contain NULL values. For
detail=none FTS5 tables, there is one row in the fts5vocab table for each
unique term/doc combination.

<p>Example:

<codeblock>
  <i>-- Assuming a database created using:</i>
  CREATE VIRTUAL TABLE ft1 USING fts5(c1, c2);
  INSERT INTO ft1 VALUES('apple banana cherry', 'banana banana cherry');
  INSERT INTO ft1 VALUES('cherry cherry cherry', 'date date date');

  --
  --    apple  | 1 | 1
  --    banana | 1 | 3
  --    cherry | 2 | 5
  --    date   | 1 | 3
  --</i>
  CREATE VIRTUAL TABLE ft1_v_row USING fts5vocab(ft1, row);

  <i>-- And, for type "instance"
  INSERT INTO ft1 VALUES('apple banana cherry', 'banana banana cherry');
  INSERT INTO ft1 VALUES('cherry cherry cherry', 'date date date');
  --
  --    apple  | 1 | c1 | 0
  --    banana | 1 | c1 | 1
  --    banana | 1 | c2 | 0
  --    banana | 1 | c2 | 1
  --    cherry | 1 | c1 | 2
  --    cherry | 1 | c2 | 2
  --    cherry | 2 | c1 | 0
  --    cherry | 2 | c1 | 1
  --    cherry | 2 | c1 | 2
  --    date   | 2 | c2 | 0
  --    date   | 2 | c2 | 1
  --    date   | 2 | c2 | 2
  --</i>
  CREATE VIRTUAL TABLE ft1_v_instance USING fts5vocab(ft1, instance);
</codeblock>


<h1 id=appendix_a nonumber tags="comparison with fts4">
  Appendix A: Comparison with FTS3/4
</h1>