[digikam] [Bug 339607] New: Images not showing if corresponding ImageInformation entry does not exist in database

[Leonardo Montecchi]

https://bugs.kde.org/show_bug.cgi?id=339607

            Bug ID: 339607
           Summary: Images not showing if corresponding ImageInformation
                    entry does not exist in database
           Product: digikam
           Version: 4.3.0
          Platform: Debian testing
                OS: Linux
            Status: UNCONFIRMED
          Severity: major
          Priority: NOR
         Component: Albums GUI
          Assignee: [hidden email]
          Reporter: [hidden email]

I had some recently added pictures to my collections which were not showing
correctly in the album view of Digikam, while they were showing correctly in
the file system. In particular I had three folders:

A - with 88 CR2 (Canon RAW) files, showing only 81 images
B - with 471 JPG, showing only 452 images
C - with 3 AVI files, showing 0 files

I am not sure what caused this problem, maybe some hang or crash while
searching for new pictures. This may also be related to these bugs:
https://bugs.kde.org/show_bug.cgi?id=320466
https://bugs.kde.org/show_bug.cgi?id=324134

I tried to move the folders, to rebuild image fingerprints, to rebuild
thumbnails, but nothing helped.

I then looked into the database (I'm using MySQL) and noticed that missing
images did not have a corresponding entry in the ImageInformation table. So
probably they are excluded because of a JOIN with this table. To fix it, I
simply:

1) added empty rows in ImageInformation with "imageid" corresponding to missing
images
2) images then were shown in Digikam
3) I instructed Digikam to re-read data from those images

I would expect Digikam to re-create the row in case it does not exist, at least
when doing rebuild fingerprint/rebuild thumbnails tasks. I consdier this a
major bug, since someone that does not know/want to deal with the database will
not be able to make images reappear easily, and may also not notice it, loosing
the ability to access some images from Digikam.

Reproducible: Always

Steps to Reproduce:
1. Find a target image, and find its 'id' from the database
2. Delete the row in ImageInformation having the corresponding 'imageid'
3. Open digikam and look at the album containing the image, image will not show

Actual Results:  
The image is not showing in the album view

Expected Results:  
Image should show in the album view, since it exists in the filesystem.

Occurs both on 4.1 (current Debian testing), and 4.3 (current Debian unstable).
Actually I tried to upgrade to 4.3 to see if it would solve the poblem, but no
luck.

Workaround:
- In the database, identify "Images" entries corresponding to the missing
images
- Identify "Images.Id"
- For each missing image, add an empty row in ImageInformation table, having
"imageid" corresponding to those of missing images, other field may be NULL
- Make Digikam re-read the image information of the involved images from the
GUI

--
You are receiving this mail because:
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[Leonardo Montecchi]

https://bugs.kde.org/show_bug.cgi?id=339607

Leonardo Montecchi <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
           Platform|Debian testing              |Debian unstable

--
You are receiving this mail because:
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[Gilles Caulier-4]

https://bugs.kde.org/show_bug.cgi?id=339607

Gilles Caulier <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
                 CC|                            |[hidden email]
            Summary|Images not showing if       |MYSQL : Images not show if
                   |corresponding               |corresponding
                   |ImageInformation entry does |ImageInformation entry does
                   |not exist in database       |not exist in database

--
You are receiving this mail because:
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[Gilles Caulier-4]

https://bugs.kde.org/show_bug.cgi?id=339607

Gilles Caulier <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
          Component|Albums GUI                  |Database

--
You are receiving this mail because:
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[Gilles Caulier-4]

https://bugs.kde.org/show_bug.cgi?id=339607

--- Comment #1 from Gilles Caulier <[hidden email]> ---
New digiKam 4.11.0 is available with official PKG installer for OSX.

https://www.digikam.org/node/740

Can you reproduce the problem with this release ?

--
You are receiving this mail because:
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[Gilles Caulier-4]

https://bugs.kde.org/show_bug.cgi?id=339607

Gilles Caulier <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
          Component|Database                    |Database-Mysql

--
You are receiving this mail because:
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[bugzilla_noreply]

https://bugs.kde.org/show_bug.cgi?id=339607

[hidden email] changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
                 CC|                            |[hidden email]

--- Comment #2 from [hidden email] ---
(Used digikam version 5.0.0-beta6)

I'm able to reproduce this error using MySQL Server.
image is view-able using SQLite.

--
You are receiving this mail because:
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[bugzilla_noreply]

https://bugs.kde.org/show_bug.cgi?id=339607

[hidden email] changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
            Version|4.3.0                       |5.0.0

--
You are receiving this mail because:
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[bugzilla_noreply]

https://bugs.kde.org/show_bug.cgi?id=339607

--- Comment #3 from [hidden email] ---
Using "LEFT JOIN" instead of "INNER JOIN" in the SQL queries involving Images
and ImageInformation tables, would be useful here?

--
You are receiving this mail because:
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[bugzilla_noreply]

https://bugs.kde.org/show_bug.cgi?id=339607

[hidden email] changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
                 CC|                            |[hidden email]

--- Comment #4 from [hidden email] ---
Good question. Why it must be different between Mysql ans SQlite for a common
statement in database ?

Richard, your expertise here will be welcome. Thanks in advance.

Gilles Caulier

--
You are receiving this mail because:
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[bugzilla_noreply]

https://bugs.kde.org/show_bug.cgi?id=339607

--- Comment #5 from [hidden email] ---
Richard,

Do you see my previous comment ?

Thanks in advance

Gilles Caulier

--
You are receiving this mail because:
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[bugzilla_noreply]

https://bugs.kde.org/show_bug.cgi?id=339607

--- Comment #6 from Richard Mortimer <[hidden email]> ---
Sorry. Had missed this one.

I cannot see an obvious reason why there would be a difference between SQLite
and MySQL. It should be easy to test the queries from the commandline in both
database types to see if they really do return different results.

The only possible theory that springs to mind would be related to the fact that
in getItemURLsInAlbumByItemDate and getItemURLsInAlbumByItemRating the
ImageInformation.rating order by field is not included in the selected columns.
I know that some databases (PostgresQL for instance) are very strict about that
but MySQL and SQLite tend to be much looser in enforcing that type of SQL
behaviour.

Whatever of the cause I would certainly be in favour of Swati's suggestion of
using a LEFT JOIN instead of an INNER JOIN in the 5 queries that are
referencing ImageInformation within them. Of course the calling code would need
auditing to ensure that it could cope with those fields having null values if
the ImageInformation row is not present.

--
You are receiving this mail because:
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[bugzilla_noreply]

https://bugs.kde.org/show_bug.cgi?id=339607

--- Comment #7 from [hidden email] ---
MySQL and SQLite return the same results. Both do not show images if the entry
for that image id is removed from Image Information table.

--
You are receiving this mail because:
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[bugzilla_noreply]

https://bugs.kde.org/show_bug.cgi?id=339607

--- Comment #8 from [hidden email] ---
Created attachment 100320
  --> https://bugs.kde.org/attachment.cgi?id=100320&action=edit
Patch to fix this issue.

I've changed INNER JOIN to LEFT JOIN at several places in the source code to
ensure that images are visible in UI, even if entry is removed from Image
Information table.

Please test and provide feedback.

--
You are receiving this mail because:
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[bugzilla_noreply]

https://bugs.kde.org/show_bug.cgi?id=339607

[hidden email] changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
            Summary|MYSQL : Images not show if  |MYSQL : Images not show if
                   |corresponding               |corresponding
                   |ImageInformation entry does |ImageInformation entry does
                   |not exist in database       |not exist in database
                   |                            |[patch]

--- Comment #9 from [hidden email] ---
Swati,

In your patch, did you touch the sqlite schema or only mysql one ?

The patch is a little bit intrusive. Do you test this patch to see if side
effects are introduced in general digiKam use cases ?

Gilles Caulier

--
You are receiving this mail because:
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[bugzilla_noreply]

https://bugs.kde.org/show_bug.cgi?id=339607

--- Comment #10 from [hidden email] ---
Richard,

This patch from Swati sound acceptable for you ?

Gilles Caulier

--
You are receiving this mail because:
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[bugzilla_noreply]

https://bugs.kde.org/show_bug.cgi?id=339607

--- Comment #11 from [hidden email] ---
(In reply to caulier.gilles from comment #9)
> Swati,
>
> In your patch, did you touch the sqlite schema or only mysql one ?

I've changed the queries for both MySQL and SQLite in XML schema file, and of
course the common queries for both in source code.

> The patch is a little bit intrusive.

Could you please tell me which part?

 Do you test this patch to see if side
> effects are introduced in general digiKam use cases ?

As far as I did, it worked fine for me!

--
You are receiving this mail because:
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[bugzilla_noreply]

https://bugs.kde.org/show_bug.cgi?id=339607

--- Comment #12 from [hidden email] ---
By intrusive, i want mean it touch code in different place.

As sqlite code in XML file is touched, the sqlite schema ID need to be updated
? No schema updater need to be written ?

Gilles Caulier

--
You are receiving this mail because:
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[bugzilla_noreply]

https://bugs.kde.org/show_bug.cgi?id=339607

--- Comment #13 from [hidden email] ---
(In reply to caulier.gilles from comment #12)
> By intrusive, i want mean it touch code in different place.
>
> As sqlite code in XML file is touched, the sqlite schema ID need to be
> updated ? No schema updater need to be written ?

Do you think it should be updated? Since it is only a fix for a issue?

--
You are receiving this mail because:
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[bugzilla_noreply]

https://bugs.kde.org/show_bug.cgi?id=339607

--- Comment #14 from [hidden email] ---
Well, if the schema of tables are untouched no, and it sound the case. Only the
way to handle data from table with SQL action are touched.

But the revision of XML file need to be increased of course.

Gilles

--
You are receiving this mail because:
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel