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

[Urho Konttori <urho konttori nokia com>]

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.

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%'

Notice the % signs.

If you want to do that all in one query, you can do:

select skm.serviceId as serviceid, skm.metadatavalue as metadatavalue
from ServiceKeywordMetadata skm, MetadataTypes mt
where mt.Metaname='DC:Keywords' and mt.id=skm.metadataid
and skm.metadatavalue like '%keyword1%'
and skm.metadatavalue like '%keyword2%'

Anyway, as I don't have any DC:keywords on my system at the moment, I 
only tested that query with syntax checking and ran a similar query to 
validate that the query mode *should* work:
select skm.serviceId as serviceid, skm.metadatavalue as metadatavalue
from ServiceKeywordMetadata skm, MetadataTypes mt
where mt.Metaname='App:Mimetype' and mt.id=skm.metadataid
and skm.metadatavalue like '%application%'
and skm.metadatavalue like '%power%'

Kind regards,
Urho Konttori


> Sorry, I am not database expert here.
>
> Jimmy
>
> On Wed, 2008-07-23 at 17:37 -0400, Jamie McCracken wrote:
>   
> > On Wed, 2008-07-23 at 14:26 -0700, Jimmy Huang wrote:
> >     
> > > Jamie,
> > >
> > > Maybe I didn't describe it clearly.  tracker-tag -s first -s second will
> > > return the files that match either "first" or "second", so basically a
> > > union of the two.  I want just the files that contains both tags, an
> > > intersection of A and B.  So the each result will contain both tag
> > > "first" and "second".
> > >
> > >       
> > ok thats a bug
> >
> >     
> > > Is there a way to do this?
> > >       
> > should be - see search method in the keywords dbus interface. It takes
> > keywords to search on as an array of string
> >
> > jamie
> >
> >     
>
> _______________________________________________
> tracker-list mailing list
> tracker-list gnome org
> http://mail.gnome.org/mailman/listinfo/tracker-list
>