Documentation Source Text

<tclscript>
  set res ""
  unset -nocomplain ::extract_api_docs_mode 
  catch { set res [source [file join $::SRC ext/fts5/extract_api_docs.tcl]] }
  set res
</tclscript>

<h1>The fts5vocab Virtual Table Module</h1>

<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 two types of fts5vocab
table, "row" and "col". 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);
</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);
</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:

<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 number of rows that contain at least one instance of the term.
  <tr><td>cnt<td>  The total number of instances of the term in the entire FTS5 table.
</table>

<p> An fts5vocab table of type "col" contains one row for each distinct term/column
combination in the associated FTS5 table. 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>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>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');

  <i>-- Then querying the following fts5vocab table (type "col") returns:
  --
  --    apple  | c1 | 1 | 1
  --    banana | c1 | 1 | 1
  --    banana | c2 | 1 | 2
  --    cherry | c1 | 2 | 4
  --    cherry | c2 | 1 | 1
  --    date   | c3 | 1 | 3
  --</i>
  CREATE VIRTUAL TABLE ft1_v_col USING fts5vocab(ft1, col);

  <i>-- Querying an fts5vocab table of type "row" returns:
  --
  --    apple  | 1 | 1
  --    banana | 1 | 3
  --    cherry | 2 | 5
  --    date   | 1 | 3
  --</i>
  CREATE VIRTUAL TABLE ft1_v_row USING fts5vocab(ft1, row);
</codeblock>


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

<p> Also available is the similar but more mature [fts3 | FTS3/4] module. 
FTS5 is a new version of FTS4 that includes various fixes and solutions for