How to exclude column from FTS3 table search

Question

I have a table like this:

CREATE VIRTUAL TABLE t USING FTS3(hidden, text1, text2) 

I would like the user to be able to search for the columns "text1" and "text2", so the query

 SELECT docid FROM t WHERE t MATCH ? 

And the following queries are possible:

 SELECT docid FROM t WHERE t MATCH 'foo' SELECT docid FROM t WHERE t MATCH 'text1:foo OR text2:bar' 

Q: how can I exclude the "hidden" column from the search so that the user cannot find rows by the hidden value?

I am going to use a "hidden" column to refer to rows in a secondary table with additional information.

Answer 1

The FTS3 table gets a free 64-bit integer column called docid that is not indexed. Just add additional data to a separate table where the primary key for this table is the same as the docid for the FTS3 table.

 CREATE VIRTUAL TABLE t USING FTS3(text1, text2); CREATE TABLE additional (id INTEGER PRIMARY KEY, hidden VARCHAR); INSERT INTO t (docid, text1, text2) VALUES (243, 'foo', 'bar'); INSERT INTO additional VALUES (243, 'bas'); SELECT docid, text1, text2, hidden FROM t JOIN additional ON t.docid = additional.id WHERE t MATCH 'foo'; 

Answer 2

The old thread, but if you are using the new version of SQLite (> 3.8), now you can use the notindexed parameter, for example:

 CREATE VIRTUAL TABLE t USING FTS4(uuid, document, notindexed=uuid); 

This excludes the column from matching requests.

I also have work on iOS, embedding my own build of SQLite 3.8.

Documentation for optional version