Understanding FTS3/FTS4: What are virtual tables a

问题:

I am very confuse now having read the first part of the documentation on SQLite3's FTS3/FTS4. The reason why I am confuse is the example scattered all over the net which I believe doesn't cover all of the possible use cases and another reason is the current situation I am in. Having said that I have a table named Note which contains two attribute whose type are TEXT and other attributes which is very important for the entire app.

It is a very simple table. I wanted to provide a Full Text search so I could search on both short note and long notes and that is when I encountered this FTS3/FTS4. You see, storing information as such is simple but I am having problem with the design especially whatever is going on under the hood of my application. Examples on the web, especially the SearchableDictionary example provided from the sdk directory, only shows the creation of virtual tables and virtually nothing more. This is not true and the case for me. I already have an existing table whose other attributes are very important to the application and I will never plan to store them as text on a virtual table! Nor have single virtual table whose types are all TEXT. I can reason to separate ShortNote and LongNote to a separate virtual table and conduct fts here via:

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

I am not comfortable on this approach for some few reasons:

1. If I do this, I will have to maintain two tables, a) the ordinary SQLite table which is easy to comprehend, b)the virtual table which I have limited knowledge of how it will work given my situation.

2. What I am thinking is just a create a separate virtual table and fill it up when I am going to conduct search and drop the table when I am over. The source of the content will be coming from the table which I already have. But I am not sure if this is the best way to do things since I am worried about the space overhead.

3. Lets assume I take the original thought I have before, that is to remove ShortNote and LongNote completely from note table and just maintain a reference. It seems this is not possible at all to join a virtual table to a non-virtual table. Its a terrible concept and I never saw any article or write up about this so maybe I just completely misunderstood FTS3/FTS4 virtual tables completely!

I am totally lost and needed help regarding the design. I badly need the feature of completely searching through the data(ShortNote and LongNote). I am thinking my best shot is #2.

I would appreciate any advice.

回答1:

Copying all text to a (temporary) FTS table would be rather slow.

To avoid the overhead of storing duplicates of the text in the two tables, you can use an external content table.

To keep the data in the two table synchronized, you can use triggers; see the link above for an example.