How do you extract all the tokens in a SQLite FTS table?

Question

For debugging purposes I want to see all the tokens that exist in a Full Text Search virtual table in SQLite.

When I look at the database structure for my FTS table (named fts_table) I see the following:

But browsing the data in these tables doesn't show the the list of tokens (not that I can find, anyway).

How do I extract a simple list of tokens?

Answer 1

You can do this with ftx4aux, which gives direct access to the full text index.

Use the following SQLite commands:

CREATE VIRTUAL TABLE search_terms USING fts4aux(fts_table);
SELECT term FROM search_terms WHERE col='*';

Read the documentation for a better understanding of how this works, but basically the term column stores the tokens and every instance of an asterisk (*) in the col column is a unique term.

If you need to export this to a text file, you can do something like this from the command line:

sqlite> .mode csv
sqlite> .output test.csv
sqlite> SELECT term FROM search_terms WHERE col='*';
sqlite> .output stdout

See also:

• SQLite FTS3 and FTS4 Extensions
• The Spellfix1 Virtual Table
• Command Line Shell For SQLite