[Digikam-devel] [Bug 160966] some searches don't work properly with the new sqlite3-3.5.8

[Andi Clemens]

------- You are receiving this mail because: -------
You are the assignee for the bug, or are watching the assignee.
         
http://bugs.kde.org/show_bug.cgi?id=160966         




------- Additional Comments From andi.clemens gmx net  2008-04-26 10:48 -------
Hi,

after my compile odyssey I'm happy to present some results... finally.
I was not able to compile the SVN version, I don't know why but greycstoration just ignored my attempts to compile it.
So I downloaded the 0.9.4beta3 from sf.net and tried to compile this version, and IT WORKED!! Yeah!
Here is my little bugreport, hope it is somehow useful to you.

I did not build kipi-plugins and the other related libs by myself because I don't want to mess up my system too much.
I installed most of the packages from the testing repository of archlinux, only libkdcraw had to be build from SVN.
These are the libs and programs I'm using for the tests in this bugreport:
    
    exiv2-0.16
    libkexiv2-0.1.6
    kipi-plugins-0.1.5beta1
    libkipi-0.1.5
    dcraw-1.399
    libkdcraw (current SVN)
    sqlite3-3.5.8
    digikam-0.9.4beta3
    KDE-3.5.9
    
and some build tools used for compiling:
    
    autoconf-2.62
    automake-1.10.1
    gcc-4.3.0
    libtool-2.2
    m4-1.4.10
    make-3.81
    patch-2.5.4
    
Hope all important libs were mentioned above, if not just ask.

So first of all I did the sqlite analyze stuff but it did not fix the problem. I created the two dumps, but it doesn't seem to change a lot in the database structure. I don't know if it is useful but here is the diff:

=========================
before.dump -> after.dump
=========================
> INSERT INTO "Settings" VALUES('Scanned','2008-04-25T00:00:11');
> ANALYZE sqlite_master;
> INSERT INTO "sqlite_stat1" VALUES('Settings','sqlite_autoindex_Settings_1','4 1');
> INSERT INTO "sqlite_stat1" VALUES('Searches','sqlite_autoindex_Searches_1','4 1');
> INSERT INTO "sqlite_stat1" VALUES('ImageProperties','sqlite_autoindex_ImageProperties_1','5080 1 1');
> INSERT INTO "sqlite_stat1" VALUES('ImageTags','tag_index','8708 103');
> INSERT INTO "sqlite_stat1" VALUES('ImageTags','sqlite_autoindex_ImageTags_1','8708 2 1');
> INSERT INTO "sqlite_stat1" VALUES('Images','dir_index','5465 59');
> INSERT INTO "sqlite_stat1" VALUES('Images','sqlite_autoindex_Images_1','5465 2 1');
> INSERT INTO "sqlite_stat1" VALUES('TagsTree','sqlite_autoindex_TagsTree_1','207 3 1');
> INSERT INTO "sqlite_stat1" VALUES('Tags','sqlite_autoindex_Tags_1','92 1 1');
> INSERT INTO "sqlite_stat1" VALUES('Albums','sqlite_autoindex_Albums_1','111 1');
===============================================================================

After that I tried to compile the SVN version of digikam for KDE3, but failed miserably. I don't know why but it just didn't work out.

After compiling the 0.9.4beta3 source code from sf.net everything was up and I added debugging output to the digikamsearch-kioslave like Arnd mentioned before in this bugreport:

==============================================================
    kdWarning() << "SQL Query " << sqlQuery << endl;
    if (!m_db.execSql(sqlQuery, &values))
    {
        error(KIO::ERR_INTERNAL, errMsg);
        return;
    }
==============================================================

and

==============================================================
    kdWarning() << "SQL Query " << sqlQuery << endl;
    if (!m_db.execSql(sqlQuery, &values, &errMsg))
    {
        error(KIO::ERR_INTERNAL, errMsg);
        return;
    }
==============================================================

After installing digikam, no debugging infos were written to the console, even rebooting didn't do the trick, but Arnd's trick with restarting kdeinit did it, so I wrote a little startup script for running digikam with these new settings:

==============================================================
    #!/bin/bash
    
    export DIGIKAMDEST=/opt/kde
    export KDEDIRS=$DIGIKAMDEST:/usr
    export LD_LIBRARY_PATH=$DIGIKAMDEST/lib:$LD_LIBRARY_PATH
    export LDPATH=$DIGIKAMDEST/lib:$LDPATH
    export PATH=$DIGIKAMDEST/bin:$PATH
    kdeinit
    digikam
==============================================================

Now I was able to see the SQL queries that might be responsible for the slowdown. THIS query is my saved search called "top rated" that looks for images with a rating of 5 stars. It is NOT working properly anymore, I have to wait nearly 15 seconds to get a result, although I have only 6 images with a rating of 5:
        
    SELECT Images.id, Images.name, Images.dirid, Images.datetime, Albums.url FROM Images, Albums LEFT JOIN ImageProperties ON Images.id = Imageproperties.imageid WHERE (  (ImageProperties.value >= '5' and ImageProperties.property='Rating')  )  AND (Albums.id=Images.dirid);
        
The next query I wanted to check is the quick filter at the bottom of digikam, set to a rating of 5 stars, executed on a folder with 2000 images in it (recursively). Like I mentioned in my first post, this search is not slow. Unfortunately the quick search does not output a SQL query, so I can not give you any results.

The next step was to fire up sqlite and enter this query manually. So I pasted this query into sqlite and, as expected, it took nearly 15 seconds as well. So this must definitely be the problem!!
        
Hope somebody is still with me, this report is a little longer but I wanted to show all the steps taken to get to these results, so maybe another person might test it on a system with sqlite3-3.5.8
        
Andi