2

The full-text search extensions of SQLite (FTS3, FTS4, FTS5) create shadow tables. E.g., if I create FTS5-table

CREATE VIRTUAL TABLE test USING fts5(textData);

that will also automatically create several real (non-virtual) tables, with test_content among them, which (apparently) stores the actual data inserted into the original virtual table test.

I know SQLite authors suggest that these shadow tables "should not be accessed directly by the user". But it's not clear from the docs if there are no any guarantee at all about behavior of these tables, or this advise concerns primarily attempts to directly INSERT or UPDATE on them. But what are the risks of reading from these tables?

Specifically - I need another (regular) table to have a FOREIGN KEY which references rowid of the virtual FTS5 table:

CREATE VIRTUAL TABLE test USING fts5(textData);
CREATE TABLE myTable (col1 INTEGER  REFERENCES test(rowid));

I couldn't find hints on that in the docs, but my own experiments showed that foreign key just doesn't work here - I still can delete records from the test table even though they are referenced from myTable. However, if instead I do

CREATE VIRTUAL TABLE test USING fts5(textData);
CREATE TABLE myTable (col1 INTEGER  REFERENCES test_content(id));

then everything seems to work as needed - I can't delete records from the virtual test table if they are referenced from myTable.

This works because, as follows from direct inspection, the rowid value of the test table is always equal to the id column value of the test_content table. Even when I specify rowid manually like in INSERT INTO test (rowid, value) VALUES (424242, 'foobar'); - then a new row appears in the test_content with the corresponding content and the corresponding id equal to 424242 (even if no preceding records were ever in the table).

So basically, my question is - is this correspondence between the rowid of an FTS5-table and the id of the corresponding <name>_content shadow table guaranteed to hold? Or might this break in some cases or in future versions?

Maximko
  • 627
  • 8
  • 20
  • Can you give a hint on how to implement a `foreign key`-like constraint on FTS5 table? Say, I have a normal table with document IDs, and an FTS5 table with contents of those documents. The question is how to prevent deletion of a content from the FTS5-table if the corresponding document ID is present in the first table? I imagine this could be done with a `trigger`, but triggers also not supported by virtual tables (afaik). – Maximko Sep 01 '19 at 06:09
  • Triggers do work, as I have used fts5 myself to create a "search" table for my database and used triggers to keep its data in sync with the rest of the database. HOWEVER, generally it's a better idea to solve this type of thing not on the database, but on whatever is calling your database. If you do web development and thus use the MVC pattern, do this in your controller, not your model (aka database). The main reason for this would be readability and troubleshooting. Controller code tends to be easier to fix. If you need performance, triggers may be faster, you'd need to test that one. – Philipp Doerner Aug 12 '21 at 08:22

0 Answers0