Re: [Tracker] How to search on matching multiple tags (DC:Keywords)?

[Jamie McCracken <jamie mccrack googlemail com>]

On Fri, 2008-07-25 at 09:17 +0300, Urho Konttori wrote:
> ext Jimmy Huang wrote:
> > Jamie,
> >
> > I wrote a C client using the tracker_keywords_search() function in
> > libtracker, and I passed in an array of two words {"first", "second"}
> > and it returns results of either first or second, but not only those
> > with both.  Is this a bug?
> >
> > Can you show me the equivalent SQL query for searching the Sqlite3
> > database instead using keywords?
> >
> > like
> >
> > select filepath from database
> > where keywords contains "first"
> > and keywords contains "second"
> >   
> Hi,
>
> As far as I remember, the DC:keywords are stored as a combined string 
> with a space as the separator and in the ServiceKeywordMetadata table. 
> Anyway, this reply is based on that hypothesis. My apologies if it's not 
> correct.

there is a separate row for each keyword


> I recommend using sqlite mozilla extension in firefox to get used to the 
> tracker database files, database contents and to be able to make the sql 
> queries before applying them to your actual application: 
> https://addons.mozilla.org/en-US/firefox/addon/5817
>
> First, open ~/.cache/tracker/file-meta.db with sqlite (or with the 
> extension).
>
> Then get the id of the DC:keywords.
> select id from MetadataTypes where Metaname='DC:Keywords'
>
> You will get id. On my db that's 15.
>
> Then do a:
> select ServiceId, MetadataValue
> from ServiceKeywordMetadata
> where Metadataid=<your id from previous query in here>
> and metadatavalue like '%keyword1%'
> and metadatavalue like '%keyword2%'


keywords are exact matches so do not use "like"

the bug is in tracker_keywords_search function in tracker-keywords.c

the query needs to join to a separate ïServiceKeywordMetadata table for
each keyword

EG for 2 keywords:

Select distinct S.Path || '/' || S.Name as EntityName from Services S,
ServiceKeywordMetaData M1, ïServiceKeywordMetaData M2 where  S.ID =
M1.ServiceID and ïM1.MetaDataValue = 'first' and M2.ServiceID = S.ID
and ï ïM2.MetaDataValue = 'second'

jamie