SQLite Query - Need Help with Full Text Search

Question

Here's what I'm trying to do.

User (a): Enters data in two fields (description-1) and (description-2).

User (b) Enters similar data in opposite fields.

User (a) or (b) search on both fields would find a match.

A good analogy would be a dating search. User (a) enters a description of themselves and the match they are looking for, and User (b) enters a description of themselves and the match they are looking for and Both would be able to do a search and find a match.

So in psuedo query english...

Select name from data where me = 'target' and target = 'me'

The catch would be, some of the words in the field would match, but not all.

Answer 1

This type of matching is hard no matter what the technology. You may have bitten off more than you can chew.

My recommendation to you is to read up on the Text Search data types in PostgreSQL.
PostgreSQL offers a flexible and powerful solution for full-text search, and it may do what you need, whereas SQLite probably won't.

Using the PostgreSQL tsquery and tsvector data types, you could turn one user's description into a form that queries the description of another user. Both tsquery and tsvector can be generated dynamically or saved in database columns and indexed.

If you still need to use SQLite, you need to learn about the various FTS virtual table types. These are all experimental and are not enabled by default. So you need to recompile SQLite, enabling FTS1, FTS2, or FTS3.

Documentation for these features is pretty sparse. Here's all I have found:

• http://www.sqlite.org/cvstrac/wiki?p=FullTextIndex
• http://www.sqlite.org/cvstrac/wiki?p=FtsUsage
• http://www.sqlite.org/cvstrac/wiki?p=FtsOne
• http://www.sqlite.org/cvstrac/wiki?p=FtsTwo
• http://www.sqlite.org/cvstrac/wiki?p=CompilingFts
• http://www.sqlite.org/cvstrac/wiki?p=CompilingFtsThree