Understanding FTS3 / FTS4: What are Virtual Tables and Searching from It Using a Non-Virtual Table?

Question

I got really confused reading the first part of the SQLite3 FTS3 / FTS4 documentation. The reason I got confused is the example scattered across the network, which, in my opinion, does not cover all possible use cases, and the other reason is the current situation. I said that I have a table called Note that contains two attributes whose type is TEXT and other attributes, which is very important for the entire application.

This is a very simple table. I wanted to provide a full-text search so that I could search for both short notes and long notes, and that was when I came across this FTS3 / FTS4. You see, storing information as such is simple, but I have a design problem, especially what happens under the hood of my application. Examples on the Internet, especially the SearchableDictionary example provided in the sdk directory, show only the creation of virtual tables and practically nothing else. This is not true and the case for me. I already have an existing table, other attributes of which are very important for the application, and I never plan to store them as text on a virtual table! There is also no single virtual table whose types are all TEXT. I can split ShortNote and LongNote into a separate virtual table and pass fts here through:

create virtual table virtual_note_table using fts3(_ID, ShortNote, LongNote); 

I do not like this approach for several reasons:

• If I do this, I will need to maintain two tables: a) a regular SQLite table that is easy to understand, b) a virtual table, with which I have limited knowledge of how it will work, given my situation.

• I think it’s easy to create a separate virtual table and populate it when I am going to search and drop the table when it is finished. The content source will come from a table that I already have. But I'm not sure if this is the best way to do something, because I'm worried about running out of space.

• Suppose I take the initial thought that I had before, that is, completely remove ShortNote and LongNote from the notes table and just save the link. It seems that it is generally impossible to connect to a virtual table to a non-virtual table. This is a terrible concept, and I never saw a single article or wrote about it, so maybe I didn’t completely understand the FTS3 / FTS4 virtual tables!

I am completely lost and need help in design. I really need the full data search function (ShortNote and LongNote). I think my best shot is # 2.

I would be grateful for any advice.

Answer 1

Copying all text to the (temporary) FTS table will be rather slow.

To avoid the overhead of saving duplicate text in two tables, you can use a table of external content .

To synchronize data in two tables, you can use triggers; see the link above for an example.