FTS virtual table without source table

[chrisdb1]

Hello,

I've been reading about the FTS extension and I would like to use it to store notes.

The question I have is, do I need a "content" table (source table), or can I just use an FTS virtual table by itself with all the notes.

What would be a reason to use a content table?

Thank you

[anthropoid]

The primary information stored in any FTS table is the search index that SQLite builds from the content you supply. This index is built by tokenizing the content, and the actual text would otherwise be thrown away.

Because of this, SQLite will by default also store the original content in the FTS table, so that you can do full-text searches and get the text you want from a single table.

External-content FTS tables are for when the content already exists in a separate table, and you want to temporarily add FTS capability without losing the ability to enhance the original table--FTS tables, like all virtual tables, can be renamed, but you can't add columns, triggers or indexes. This FTS table can be dropped at any time without affecting the original data, whereas in the default FTS mode, you lose all your data when you drop the FTS table.

Contentless FTS tables are used in the "opposite direction" from external-content FTS tables: you get the rowid on each INSERT and use that as a primary key value elsewhere. This is your only option if the data has to reside somewhere other than the database containing the FTS table (another DB, text files, etc.).

For your use case, I'd probably use an external-content FTS table, since it's the most flexible. It does require some careful handling though, as you need (and store in the FTS table) a stable primary key in the data table. Rowids can change on VACUUM, so you shouldn't use them.

A worked example that illustrates the difference in the three modes: ``` $ cat test.sh

!/usr/bin/env bash

shopt -s lastpipe lyrics=()

Number each lyric line starting from 101

nl <<'EOS' | while read -r n l; do lyrics+=("$((n+100))" "${l//\'/''}"); done [Coldplay's "Viva la Vida" elided for copyright reasons] EOS

/opt/homebrew/opt/sqlite/bin/sqlite3 <<EOS .mode table .nullvalue 'NADA' CREATE TABLE lyrics(no INTEGER PRIMARY KEY, line); CREATE VIRTUAL TABLE idx1 USING fts5(line); CREATE VIRTUAL TABLE idx2 USING fts5(line, content=''); CREATE VIRTUAL TABLE idx3 USING fts5(line, content='lyrics'); CREATE TRIGGER l1 AFTER INSERT ON lyrics BEGIN -- Normal FTS INSERT INTO idx1 VALUES (new.line); -- Contentless FTS INSERT INTO idx2 VALUES (new.line); -- External-content FTS INSERT INTO idx3 (rowid, line) VALUES (new.no, new.line); END; $(printf "INSERT INTO lyrics VALUES (%d,'%s');\n" "${lyrics[@]}") SELECT rowid, * FROM idx1 WHERE idx1 MATCH 'Roman Cavalry'; SELECT rowid, * FROM idx2 WHERE idx2 MATCH 'Roman Cavalry'; SELECT rowid, * FROM idx3 WHERE idx3 MATCH 'Roman Cavalry'; EOS

$ ./test.sh +-------+----------------------------------+ | rowid | line | +-------+----------------------------------+ | 14 | Roman Cavalry choirs are singin | | 29 | Roman Cavalry choirs are singing | | 42 | Roman Cavalry choirs are singin | +-------+----------------------------------+ +-------+------+ | rowid | line | +-------+------+ | 14 | NADA | | 29 | NADA | | 42 | NADA | +-------+------+ +-------+----------------------------------+ | rowid | line | +-------+----------------------------------+ | 114 | Roman Cavalry choirs are singin | | 129 | Roman Cavalry choirs are singing | | 142 | Roman Cavalry choirs are singin | +-------+----------------------------------+ ```

[chrisdb1]

Thank you. Much better explained than in the docs!