Re: [sqlite] fts2 data in a different table

[Scott Hess]

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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] fts2 data in a different table

[Bram - Smartelectronix]

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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users