0

I have a table:

CREATE TABLE Tests (col1 INTEGER, col2 INTEGER, col3 INTEGER, col4 INTEGER, col5 INTEGER);

with values:

INSERT INTO Tests (col1, col2, col3, col4, col5)
VALUES (1, 2, 3, 4, 5);
INSERT INTO Tests (col1, col2, col3, col4, col5)
VALUES (6, 7, 8, 9, 10);
INSERT INTO Tests (col1, col2, col3, col4, col5)
VALUES (11, 12, 13, 14, 15);
INSERT INTO Tests (col1, col2, col3, col4, col5)
VALUES (16, 17, 18, 19, 20);

I created an FTS5 table for it:

CREATE VIRTUAL TABLE TestFTS5 USING fts5(col1,
                                         col2,
                                         col3,
                                         tokenize = "ascii separators ' '",
                                         content='');

INSERT INTO TestFTS5 (rowid, col1, col2, col3)
SELECT DISTINCT rowid, col1,
                       col2,
                       col3
FROM Tests;

Without any delete statements, the following query:

SELECT COUNT(1)
FROM TestFTS5
WHERE TestFTS5 MATCH '1* OR 7';

returns 4.
However, even after deleteing a row from the FTS5 table:

INSERT INTO TestFTS5 (TestFTS5, rowid, col1, col2, col3)
VALUES ('delete', 1, NULL, NULL, NULL);

the SELECT query still returns 4.
I tried deleting from both the main table and the FTS5 table:

DELETE
FROM Tests
WHERE rowid = 4;

INSERT INTO TestFTS5 (TestFTS5, rowid, col1, col2, col3)
VALUES ('delete', 4, NULL, NULL, NULL);

but the SELECT query still returns 4.
Thinking it had something to do with the transaction not being committed, I saved the changes but the SELECT query still returned 4. I did notice that after every 'delete' INSERT the TestFTS5_docsize table was losing a row corresponding to the "deleted" row ID. Am I doing something wrong here? I think I'm following what the documentation for 'delete' says to a T, but the deleted row is still included in the results.

1 Answers1

0

The content='' option creates a contentless table, the columns values are integrated in the index but not stored. This is why they are always null :

Attempting to read any column value except the rowid from a contentless FTS5 table returns an SQL NULL value.

When deleting rows you have to provide again the values, so that the index can be updated :

INSERT INTO TestFTS5 (TestFTS5, rowid, col1, col2, col3)
VALUES ('delete', 1, 1, 2, 3);

If you are actually storing the values in another table, you can use it as external content, with content='Tests'

You can then use the standard delete

delete from TestFTS5 where rowid = 2

SQLite will use the row in Tests (with the same rowid) to retrieve the needed values.

This means that if you also delete the row from external table, you must do it after deleting the row in the FTS table.

In fact you are responsible for maintaining the both tables in sync.

bwt
  • 17,292
  • 1
  • 42
  • 60
  • Thank you for the answer! I switched to using a view as the content table because it better suited my needs. Say the view I created is `TestsView`, while I did notice that views get updated when any changes (`UPDATE`, `DELETE`, `INSERT`) are made to the main table (in this case `Tests`), but, just to be sure, am I correct in assuming that my trigger: `CREATE TRIGGER Tests_befxxx BEFORE XXX ON Tests BEGIN ... END;` needs to only delete/update/insert rows to/from `TestsFTS5` and the database will always manage the view's data automatically? –  Aug 16 '22 at 11:57
  • 1
    A view always sees the last value from the underlying tables. You only need to care about the FTS index and ensure that when something changes, the old value is removed and the new one is added. I believe triggers are a good way to do this, for example in case of updates you have access to both the old and the new values – bwt Aug 16 '22 at 12:25