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



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




[Date Prev][Date Next]   [Thread Prev][Thread Next]   [Thread Index] [Date Index] [Author Index]