[Digikam-users] sql queries digikam

[Arnd Baecker]

Hi,

On Mon, 2 Jul 2007, depot @ xs4all. nl wrote:

> > Alright, next try (need not be better ;-):
> >
> > Can you compile from source?
> > If so you could just add in digikamsearch.cpp
> >   kdWarning() << "QUERY" << sqlQuery << endl;
> > in a line before the two calls
> >   m_db.execSql(sqlQuery, &values)
> >
> > This should give you the full search string on the konsole
> > when running searches in digikam.
> >
> > Best, Arnd
>
> Hi Arnd. Thanks a lot again!
> I did as you suggested. Even though I don't understand what your code does.

OK, a bit of explanation is needed:
The above command will output a string (through the Warning handler)
to the konsole which will start with
QUERY
and then the actual sqlite query string.
So you have to look for lines with QUERY ...

> This is a sample of the output on the console when I did some searches:
>
> digikam: Dirty: /
> digikam: digikamsearch:1?1.key=album&1.op=EQ&1.val=1&name=Last%20Search&count=1
> digikam: digikamsearch:1?1.key=tag&1.op=EQ&1.val=1&name=Last%20Search&count=1
> digikam: digikamsearch:1?1.key=tag&1.op=EQ&1.val=1&name=Last%20Search&count=1
> digikam: digikamsearch:1?1.key=tag&1.op=EQ&1.val=2&name=Last%20Search&count=1
> digikam: digikamsearch:1 AND
> 2?1.key=tag&1.op=EQ&1.val=2&2.key=album&2.op=EQ&2.val=1&name=Last%20Search&count=2
> digikam: digikamsearch:1 AND
> 2?1.key=tag&1.op=EQ&1.val=2&2.key=imagedate&2.op=GT&2.val=2007-07-02&name=Last%20Search&count=2
> digikam: digikamsearch:1 AND
> 2?1.key=tag&1.op=EQ&1.val=2&2.key=imagedate&2.op=LT&2.val=2007-07-02&name=Last%20Search&count=2
> digikam: digikamsearch:1 AND
> 2?1.key=tag&1.op=EQ&1.val=2&2.key=imagedate&2.op=LT&2.val=2007-07-02&name=Last%20Searchjkj&count=2
> digikam: Dirty: /
>
> Still it's not what I am looking for.

Yep, you will have to do a search and look for lines
which have the text "QUERY" in them,
but they will only appear if you do a search.

> Maybe I was not clear enough with my question. Let me rephrase it.

Your question was crystal clear - we will get there ...

> Digikam internally produces sql queries to question the digikam3.db when a user performs
> actions like filtering on a tag, searching in a date range, finding a specific caption or
> comment etc.
>
> These queries look something like:
> sqlQuery = "SELECT Images.id, Images.name, Images.dirid, Images.datetime, Albums.url "
>                    "FROM Images, Albums LEFT JOIN ImageProperties ON Images.id = Imageproperties.imageid "
>                    "WHERE ( ";
>
> (taken from digikamsearch.cpp).
> The above sample is not complete. It's just the header of a generic digikam query. Complete and valid sql queries are produced by digikam on
> the fly constantly whenever a user sorts, filters or finds his pictures.
>
> I am looking for valid and complete sql queries for the most common tasks like the
> simple search option, selecting on tags, dates, captions etc.
>
> If there was a way to monitor all communication from digikam to sqlite3 then it would be easy
> to find out.
>
> Anyone?

Hej, don't give me up to quickly:

I just added the two kdWarning()
So that the code looks like
 ...
        kdWarning() << "QUERY" << sqlQuery << endl;
        if (!m_db.execSql(sqlQuery, &values))
 ...

This gives me for example:

kio_digikamsearch: WARNING: QUERYSELECT Images.id, Images.name,
Images.dirid, Images.datetime, Albums.url FROM Images, Albums LEFT JOIN
ImageProperties ON Images.id = Imageproperties.imageid WHERE (  (Images.id
IN   (SELECT imageid FROM ImageTags    WHERE tagid IN    (SELECT id FROM
Tags WHERE name NOT LIKE '%A%')))  )  AND (Albums.id=Images.dirid);

Does this look better for you?

Well, clearly,
        kdWarning() << "QUERY:  " << sqlQuery << endl;
        if (!m_db.execSql(sqlQuery, &values))
would have been better (i.e. to  have a bit of space before
the actual query.

HTH, Arnd