Hello All, (my first post here)
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.
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
The solution I use now is a subselect:
select * from sound where samplerate=44100 and id in (select sound_id
from search where content match "bass drum");
but this will get me into trouble for large datasets - I suppose.
Is there a better solution for this problem or not? I also know FTS2
uses only text-data, so I suppose my join will be slower (or am I
assuming too much) than a join between two tables with integer keys?
thanks a lot,
- bram
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
