SQLite FTS tokenizers
Related to Create FTS5 virtual table in the SQLite CLI .
Documentation: https://www.sqlite.org/fts5.html .
The SQLite CREATE VIRTUAL TABLE statement accepts a “tokenize” option that is used to configure the specific tokenizer used by the FTS5 table. The tokenizer is the component that decides how text is broken into searchable terms.
FTS5 built-in tokenizer modules #
FTS5 includes four built-in tokenizer modules:
- unicode61: based on the Unicode 6.1 standard, the default tokenizer
- ascii: assumes all characters outside of the ASCII codepoint range (0-127) are to be treated as token characters
- porter: implements the porter stemming algorithm , more on this later/elsewhere
- trigram: treats each contiguous sequence of three characters as a token, allowing FTS5 to support more general substring matching
Given the text “Move to the previously active pane”, is it possible to see how different tokenizers will break it down?
In the tests below, I’m just taking the ftsvocab virtual table as a given. The results are interesting.
unicode61 tokenizer #
sqlite> CREATE VIRTUAL TABLE temp.token_test
...> USING fts5(text, tokenize='unicode61');
sqlite> INSERT INTO temp.token_test
...> VALUES ('Move to the previously active pane');
sqlite> CREATE VIRTUAL TABLE temp.token_vocab
...> USING fts5vocab(token_test, instance);
sqlite> SELECT term, offset
...> FROM temp.token_vocab
...> ORDER BY offset;
╭────────────┬────────╮
│ term │ offset │
╞════════════╪════════╡
│ move │ 0 │
│ to │ 1 │
│ the │ 2 │
│ previously │ 3 │
│ active │ 4 │
│ pane │ 5 │
╰────────────┴────────╯
porter tokenizer #
I’ve included “unicode61” in the tokenizer argument. I think that’s expected—porter uses unicode61 by default, so it could be left out.
sqlite> CREATE VIRTUAL TABLE temp.token_test
...> USING fts5(text, tokenize='porter unicode61');
sqlite> INSERT INTO temp.token_test
...> VALUES ('Move to the previously active pane');
sqlite> CREATE VIRTUAL TABLE temp.token_vocab
...> USING fts5vocab(token_test, instance);
sqlite> SELECT term, offset
...> FROM temp.token_vocab
...> ORDER BY offset;
╭──────────┬────────╮
│ term │ offset │
╞══════════╪════════╡
│ move │ 0 │
│ to │ 1 │
│ the │ 2 │
│ previous │ 3 │
│ activ │ 4 │
│ pane │ 5 │
╰──────────┴────────╯
trigram tokenizer #
sqlite> CREATE VIRTUAL TABLE temp.token_test
...> USING fts5(text, tokenize='trigram');
sqlite> INSERT INTO temp.token_test
...> VALUES ('Move to the previously active pane');
sqlite> CREATE VIRTUAL TABLE temp.token_vocab
...> USING fts5vocab(token_test, instance);
sqlite> SELECT term, offset
...> FROM temp.token_vocab
...> ORDER BY offset;
╭───────┬────────╮
│ term │ offset │
╞═══════╪════════╡
│ mov │ 0 │
│ ove │ 1 │
│ 've ' │ 2 │
│ e t │ 3 │
│ ' to' │ 4 │
│ 'to ' │ 5 │
│ o t │ 6 │
│ ' th' │ 7 │
│ the │ 8 │
│ 'he ' │ 9 │
│ e p │ 10 │
│ ' pr' │ 11 │
│ pre │ 12 │
│ rev │ 13 │
│ evi │ 14 │
│ vio │ 15 │
│ iou │ 16 │
│ ous │ 17 │
│ usl │ 18 │
│ sly │ 19 │
│ 'ly ' │ 20 │
│ y a │ 21 │
│ ' ac' │ 22 │
│ act │ 23 │
│ cti │ 24 │
│ tiv │ 25 │
│ ive │ 26 │
│ 've ' │ 27 │
│ e p │ 28 │
│ ' pa' │ 29 │
│ pan │ 30 │
│ ane │ 31 │
╰───────┴────────╯
How does tokenization deal with non-ordinary words? #
The tmux keybinding database that I’m fooling around with has a “key” column, with entries like “C-b”, “C-o”, “M-Up”. What will a tokenizer do to them?
sqlite> CREATE VIRTUAL TABLE temp.token_test
...> USING fts5(text, tokenize='unicode61');
sqlite> INSERT INTO temp.token_test
...> VALUES ('C-b C-o M-Up split-window');
sqlite> CREATE VIRTUAL TABLE temp.token_vocab
...> USING fts5vocab(token_test, instance);
sqlite> SELECT term, offset
...> FROM temp.token_vocab
...> ORDER BY offset;
╭────────┬────────╮
│ term │ offset │
╞════════╪════════╡
│ c │ 0 │
│ b │ 1 │
│ c │ 2 │
│ o │ 3 │
│ m │ 4 │
│ up │ 5 │
│ split │ 6 │
│ window │ 7 │
╰────────┴────────╯
This seems to be indicating that FTS5 would not be ideal for matching queries for tmux keybinding names. An SQL LIKE query would do better.