[SOLVED] Use two columns for FTS4 from sqlite3

Use two columns for FTS4 from sqlite3

I have a table with two columns (ldap, name). I want to be able to full text search any of those columns with the library FTS4. Here I have a couple of statements I'm using to create the virtual table but when I create a statement using Match the result is empty although it should return data.

CREATE VIRTUAL TABLE IF NOT EXISTS sales_rep USING FTS4(ldap,name, content="__sales_rep");
CREATE TRIGGER IF NOT EXISTS __sales_rep___after_insert AFTER INSERT ON __sales_rep BEGIN INSERT INTO sales_rep (ldap, name) VALUES (new.ldap, new.name);END;

I am inserting a row (ldap, name) VALUES ('test', 'Bryan'); But using

SELECT * FROM sales_rep where name MATCH 'Bry';

The result is empty

Solution

Inserting data in an external content FTS table requires to provide explicitly a value for the docid, which should be the rowid of the content table.

In your case you need to change the trigger :

CREATE TRIGGER  __sales_rep___after_insert
AFTER INSERT ON __sales_rep
BEGIN
  INSERT INTO sales_rep (docid, ldap, name)
  VALUES (new.rowid, new.ldap, new.name);
END;