了解FTS3 / FTS4：什么是虚拟表，并从具有可搜索非虚表它来搜索？ [英] Understanding FTS3/FTS4: What are virtual tables and to search from it having a searchable non-virtual table?
问题描述
我很迷惑，现在已经读的SQLite3的FTS3 / FTS4文件的第一部分。为什么我迷惑的原因是散落各地，我相信没有覆盖所有可能的用例的净例子，另一个原因是目前的情况我在说了这么多我有一个表叫注其中包含两个属性，其类型是文本和这对整个应用程序的非常重要的其他属性。
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.
这是一个非常简单的表格。我想提供全文检索，所以我可以搜索两个短信和长期票据，这是当我遇到这个FTS3 / FTS4。你看，存储信息本身是简单的，但我有问题，设计特别是无论是我的应用程序的引擎盖下回事。网络，特别是从SDK目录中提供的SearchableDictionary例如上例中，只显示了创建虚拟表，几乎仅此而已。这是不正确的，对我的情况。我已经有一个现成的表的其他属性是应用非常重要的，我永远不会打算将它们存储作为虚拟表的文字！也有一个虚拟表，其类型都是文本。我的理由ShortNote和LongNote分离到一个单独的虚拟表和行为通过FTS位置：
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:
-
如果我这样做，我将不得不维持两个表，一个）普通SQLite表这是很容易融为一体prehend，B）虚表，我已经限制了它是如何工作的知识给我的情况。
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.
我在想什么只是一个创建一个单独的虚拟表并填写了，当我准备进行搜索和删除该表，当我结束了。该内容的来源将来自表，我已经到来。但我不知道这是否是做的事情，因为我担心空间开销的最佳方式。
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.
让我们假设我把原来以为我有过，那是从音符表完全删除ShortNote和LongNote，只是保持一个参考。看来这是不可能在所有加入一个虚拟表到一个非虚表。它是一个可怕的概念，我从来没有见过任何物品或写了这个，也许我只是完全误解FTS3 / FTS4虚表的彻底！
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!
我就设计完全失去了，需要帮助。我迫切需要通过数据（ShortNote和LongNote）完全搜索功能。我想我最好的拍摄是＃2。
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.
我会AP preciate任何意见。
I would appreciate any advice.
推荐答案
所有文本复制到（临时）FTS表将是相当缓慢的。
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.
这篇关于了解FTS3 / FTS4：什么是虚拟表，并从具有可搜索非虚表它来搜索？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
