Android search : Use FTS tables along with normal SQLite DB Tables

This is more of a design question - not just a coding question.

I already have an app that stores data in SQLite DB tables. Now I want to add Search feature into this.

I understand that in order to enable search feature I need FTS tables.

The tables that I have are (not exactly the same but giving very similar example):

Content table - has columns like: Id, Title, Content, Created By, Created On, Changed By, Changed on etc ...

Comments table - has columns like: Id, Comments, Created By, Created On, In Reply to etc ...

Other tables like User Data, Meta Data, Categories, Tags etc.

Here I only want search capability on Title, Content and Comments. Obviously I don't need search capability on other columns like created by etc.

Which is the best option ?

  1. Should I discard my old tables and create ONLY FTS table ?
  2. Should I continue to use old tables and create new FTS tables that only support the search feature.

Problems that I see with option 1 are:

  • It is a disruptive change !
  • I have a set of multiple tables with many non-text columns on which I do not intend to perform search. How do I model them in FTS ?
  • Right now I am utilizing the features of RDBMS like Join etc which I think I wont be able to do if I switch fully to FTS tables !

Problems that I see with option 2 are:

  • It will result in duplicate tables - and will consume more memory / space !
  • Every time I insert / update / modify a entry in my SQL tables, I have to do the same change in the FTS tables.

So, what is the best option ? Has any body faced similar challenges ?

Answers


FTS tables cannot be efficiently queried for non-FTS searches, so option 1 is out.

The entire FTS table essentially is an index. This is a speed/space tradeoff, and the ability to do full-text searches usually is worth it. To avoid storing the original text twice, use external content tables.

To keep the original and FTS tables in sync, use triggers.


Need Your Help

<a href=“”> root of folder rather than index.html

html

I have tried googling but all of the links I find are about linking resources, for example images with ../, Might be an easy question to solve - but internet searches aren't helpful...

Need to update xml tags which would be given as input to the perl script?

xml perl xml-parsing

I am planning to write a script which would update xml tag value.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.