On Tue, Feb 12, 2008 at 10:06 AM, Bram - Smartelectronix <[EMAIL PROTECTED]> wrote: > I have various tables which all relate to the same central object, all > of them contain various pieces of information about this object. And I > want to full-text-search for these objects. > > I.e. in my case sounds (which have tags, comments, metadata, categories, > ratings, etc etc). I wanted to use FTS2 to search through sounds, in the > end I settled for a trigger approach: adding a new tag for example will > trigger an update of the search-table. > > create virtual table search using fts2(sound_id, content); > > create trigger if not exists soundtag_insert after insert on tag for > each row > begin > update search set content=(....) where search.sound_id=new.sound_id; > end; > > etcetera for all my tables related to the sound.
Don't use fts2. There's a known design flaw which can lead to index corruption. fts3 fixes the flaw, and otherwise works pretty much the same. If you do you fts2, never ever run VACUUM! Suggest that rather than have a sound_id column, you instead use the implicit docid/rowid column. docid is an fts3 thing. If new.sound_id is unique (I assume it is), you can just set search.docid = new.sound_id. That will make joins faster because the docid acts as an implicit UNIQUE INTEGER index. > All well, but then I tried: > > select sound.* from sound left join search on sound.id=search.sound_id > where search.content match "bass drum" and sound.samplerate=44100; > > and got: SQL error: unable to use function MATCH in the requested context Hmm. Doesn't make sense to me - it's possible that the left join is causing the system to not be able to use the fts index. I think the subselect should work just fine, though, and shouldn't be hideously inefficient. Still, I'll put it on my list of things to eventually look at. I'd have expected it to work as-is. -scott _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
