Doing FTS using external sqlite client

[Amos Bird]

Hi,

I'm trying to do FTS via external sqlite client. I tested this query

```

select value, case substr(path,1,8)

when 'storage:'

then '/home/amos/Zotero/storage/'||its.key||'/'||substr(path,9)

else path

end

from itemData itd, itemDataValues itv, fields fs, items its, itemAttachments a, fulltextWords w, fulltextItemWords iw , fulltextItems i

where fs.fieldName = "title" and

itd.fieldID = fs.fieldID and

itd.valueID = itv.valueID and

itd.itemID = a.parentItemID and

w.wordID = iw.wordID and

iw.itemID = i.itemID and

a.itemID = i.itemID and

its.itemID = a.itemID and

itd.itemID not in (select itemID from deletedItems) and

word in ("test");

```

It has at least two problems.

1. It retrieves deleted items 

2. I cannot specify full text search terms approprietely. (I cannot do "foo and bar").

How does zotero achieve this internally? Is it possible to apply the method outside zotero?

best regards,

Amos