sqlite3 fts5 contentless or content=external table, how store and read a non-FTS column value

Question

I am working with an SQLite3 database. And I want to create an FTS5 content-less (content="") or content="external table" table to save space and prevent duplication of data (which occurs if I don't use a content-less or external-content table).

My scenario =>

tableA (textColumn, idColumn)
tableB (textColumn, idColumn)
tableFTS5 (textColumn, textIdColumn, tableNameColumn)

What I do =>

I insert data into tableA and tableB. Then I read all rows from tableA and insert into tableFTS5 table as

tableFTS5.textColumn = tableA.textColumn, 
tableFTS5.textIdColumn = tableA.idColumn,
tableFTS5.tableNameColumn = "tableA"

I repeat this for tableB also with tableFTS5.tableNameColumn = "tableB".

What I need is =>

I want to query tableFTS5 for some text and I want to retrieve just tableFTS5.textIdColumn and tableFTS5.tableNameColumn. With these two column values in hand, I can retrieve the original data from tableA or tableB.

Is it possible to retrieve these columns from tableFTS5 if tableFTS5 is a content-less or content="external" table, maybe with some triggers or any other method ?

My source of info about this Link to SQLite3 FTS5 docs

Answer 1

Ok. So one approach I can take is =>

I have two main tables with text data (I don't want to duplicate this data).

And I create one mapping table to map the main table names and their text ids to rowids of FTS table.

And I have one FTS content-less table which only returns rowids.

But with this approach I have to manage insert, delete operations for FTS table and mapping table too. This got too complicated to manage so I decided to go for creating individual FTS table for every main table and setting content="that main table name"

But I would really prefer if there is an option for content-less FTS table to store and return some user specified columns for the purpose of storing some ids and not the text content columns (because that would create duplication of text).

What I was trying to achieve is called one to one polymorphic relationship so I don't have to create too many tables.

If I had this option for content-less FTS table then for 6 text data tables I would create only one FTS table, so total tables = 7. But now without this option I would create 12 tables, 6 main and 6 FTS

I request developers to add this option to sqlite. Many thanks

Answer 2

Create a contentless fts5 table:

CREATE VIRTUAL TABLE IF NOT EXISTS fts_tbl USING fts5(a, b, c, content='')

Create triggers so it updates as your table you want indexed updates:

CREATE TRIGGER IF NOT EXISTS tbl_ai AFTER INSERT ON tbl BEGIN
  INSERT INTO fts_tbl(rowid, a, b, c) VALUES (new.ROWID, new.a, new.b, new.c);
END
CREATE TRIGGER IF NOT EXISTS tbl_ad AFTER DELETE ON tbl BEGIN
  INSERT INTO fts_tbl(fts_tbl, rowid, a, b ,c) VALUES('delete', old.ROWID, old.a, old.b, old.c);
END
CREATE TRIGGER IF NOT EXISTS tbl_au AFTER UPDATE ON tbl BEGIN
  INSERT INTO fts_tbl(fts_tbl, rowid, a, b, c) VALUES('delete', old.ROWID, old.a, old.b, old.c);
  INSERT INTO fts_tbl(rowid, a, b, c) VALUES (new.ROWID, new.a, new.b, new.c);
END

Then fill it with data from your table of interest:

INSERT INTO fts5_tbl (rowid, a, b, c) SELECT ROWID, a, b, c FROM tbl

Now you can query it and return data from your table like this:

SELECT rank, t.a, t.b, t.c FROM tbl t JOIN fts_tbl f ON t.ROWID = f.ROWID WHERE fts_tbl MATCH "search" ORDER BY rank;