How to exclude column from FTS3 table search

Question

I have such table:

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

I would like user to be able to searh over 'text1' and 'text2' columns, so the query is

SELECT docid FROM t WHERE t MATCH ?

And possible requests are:

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 'hidden' column from search, so that user can't find rows by hidden value?

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

Dominance

Answer 1

An FTS3 table gets a free 64 bit integer column called docid which is not indexed. Simply put additional data in a separate table where the Primary Key for that 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';

Akkerman

Answer 2

Old thread, but if you're using a newer build of SQLite (> 3.8), you can now use the notindexed option, e.g.:

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

This will exclude the column from match queries.

I've also got this working on iOS by embedding my own build of SQLite 3.8.

Documentation for notindexed option

Donofrio