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

[Jimmy Huang <jimmy huang linux intel com>]

Thank you all, that was very helpful, I'll try it out.

Jimmy


On Fri, 2008-07-25 at 09:15 -0400, Jamie McCracken wrote:
> 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