[digikam] [Bug 305104] Digikam does not show some jpg images in files

[Johannes via KDE Bugzilla]

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

--- Comment #19 from Johannes <johannes.lists+bugs.kde.org at tannenhof-imshausen.de> ---
Finally I was able to correct the database.
What I did trying to figure it out (using digikam 4.12 on Ubuntu 16.04): 
I set up a new digikam collection with only the images from dropbox and dumped
the sqlite database.  
Then I removed the ImageUniqueID from the images were it was not unique with 
exiv2 -M'del Exif.Photo.ImageUniqueID'  /home/user/dropbox/images/*.jpg
Then I removed the database-file to let digikam create it again and dumped it,
too. (Now all images were already shown and no wrong images were assigned as
identical.) Then I looked at the diff of the two database dumps. By that I
figured out that the table ImageTags is also involved: Images wrongly
identified as identical to others have the tagid which has the name "Original
Version" in tags table. 
With this information I were able to edit my original digikam database and now
everything works right.

Follow these steps to solve the problem:
0. Close digikam and make a bakup of the collection database. Make sure to have
sqlite3 or SQLiteManager for Firefox or a similar tool available to check and
edit the database.

1. Identifying photos which have identical values in: Exif.Photo.ImageUniqueID
This is easily done by 
SELECT *,  COUNT(*) as c FROM ImageHistory GROUP BY uuid HAVING     c > 1 ORDER
BY  c DESC
(I also saw only photos affected which have no history entries: 
SELECT *, COUNT(*) as c FROM ImageHistory GROUP BY uuid HAVING c > 1 AND
history IS NULL ORDER BY c DESC
)
I removed the entries with unique uuids after copying the imageids to a file
for further usage.

2. Remove the ImageUniqueID from the images where it was not unique with 
exiv2 -M'del Exif.Photo.ImageUniqueID'  /home/user/dropbox/images/*.jpg

3. Then DELETE the offending entries in the tables ImageRelations and ImageTags
using the imageids from step 1. (Because I edited some images I couldn't simply
delete all entries claiming to be an "Original Version", so I looked through
them manually, comparing the name or tagid.)

For example I used queries like 
SELECT imageid, name FROM ImageTags LEFT JOIN Images ON Images.id =
ImageTags.imageid WHERE name LIKE '20160624%.jpg'  AND  tagid = 18
to check the database for images of a certain name and the corresponding tagid
for "Original Version".
Look in the table Tags for tags containing "version" in the name to find out
the right tagid:  
SELECT * FROM Tags WHERE name LIKE'%version%'

4. Close the database and start digikam again. That's it!

-- 
You are receiving this mail because:
You are the assignee for the bug.