3

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:

enter image description here

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?

Suragch
  • 484,302
  • 314
  • 1,365
  • 1,393

1 Answers1

3

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:

Community
  • 1
  • 1
Suragch
  • 484,302
  • 314
  • 1,365
  • 1,393
  • It's strange that when there is only one indexed column, the fts4aux table has every row duplicated: one with col='*', the other col='0'. Is it better to then do `WHERE col='*'` or `SELECT DISTINCT` (omitting the WHERE)? – Mark Dec 02 '18 at 08:18
  • @Mark, I haven't worked with fts tables for a while now so I can't answer your question at this time. If you continue playing around with this and answer your own question, please leave a comment or add a new answer. – Suragch Dec 02 '18 at 17:12