Yes, the databases are case insensitive. Otherwise all searches would have to be done with a binary search, which is much slower. Collection on FAT32 or NTFS file systems are also case insensitive. This problem can not be fixed.

Maik

Yeah, adding the binary operator to the queries would not be a good idea. Not only do you end up with full table scans, but also issues if you take an ISO 8859-1/latin1 string with non-ASCII characters and try to compare that to a column containing utf-8 values.

mysql> SELECT table_schema, table_name, table_collation FROM information_schema.tables where table_schema like '%digikam%';
+--------------------+----------------------+-------------------+
| table_schema       | table_name           | table_collation   |
+--------------------+----------------------+-------------------+
| digikam            | AlbumRoots           | utf8_general_ci   |
| digikam            | Albums               | utf8_general_ci   |
| digikam            | DownloadHistory      | latin1_swedish_ci |
| digikam            | ImageComments        | latin1_swedish_ci |
| digikam            | ImageCopyright       | latin1_swedish_ci |
| digikam            | ImageHistory         | latin1_swedish_ci |
| digikam            | ImageInformation     | latin1_swedish_ci |
| digikam            | ImageMetadata        | latin1_swedish_ci |
| digikam            | ImagePositions       | latin1_swedish_ci |
| digikam            | ImageProperties      | latin1_swedish_ci |
| digikam            | ImageRelations       | latin1_swedish_ci |
| digikam            | ImageTagProperties   | latin1_swedish_ci |
| digikam            | ImageTags            | latin1_swedish_ci |
| digikam            | Images               | latin1_swedish_ci |
| digikam            | Searches             | latin1_swedish_ci |
| digikam            | Settings             | latin1_swedish_ci |
| digikam            | TagProperties        | latin1_swedish_ci |
| digikam            | Tags                 | latin1_swedish_ci |
| digikam            | TagsTree             | NULL              |
| digikam            | VideoMetadata        | latin1_swedish_ci |
| faces_digikam      | FaceMatrices         | latin1_swedish_ci |
| faces_digikam      | FaceSettings         | latin1_swedish_ci |
| faces_digikam      | Identities           | latin1_swedish_ci |
| faces_digikam      | IdentityAttributes   | latin1_swedish_ci |
| faces_digikam      | OpenCVLBPHRecognizer | latin1_swedish_ci |
| faces_digikam      | OpenCVLBPHistograms  | latin1_swedish_ci |
| similarity_digikam | ImageHaarMatrix      | latin1_swedish_ci |
| similarity_digikam | ImageSimilarity      | latin1_swedish_ci |
| similarity_digikam | SimilaritySettings   | latin1_swedish_ci |
| thumbnails_digikam | CustomIdentifiers    | latin1_swedish_ci |
| thumbnails_digikam | FilePaths            | latin1_swedish_ci |
| thumbnails_digikam | ThumbSettings        | latin1_swedish_ci |
| thumbnails_digikam | Thumbnails           | latin1_swedish_ci |
| thumbnails_digikam | UniqueHashes         | latin1_swedish_ci |
+--------------------+----------------------+-------------------+
34 rows in set (0.00 sec)

Is the application logic dependent on case insensitive behavior of Albums, AlbumRoots, and Images tables? If not, setting the collation of those to utf8_general_cs would be a better approach.

The collation "utf8_general_cs" does not exist under MySQL. That would be something like "utf8_bin". We had it before, but there were problems. I think the search is then case sensitive. I will test it again the days.

Maik

digiKam 7.0.0 stable release is now published and now available as FlatPak:

https://www.digikam.org/news/2020-07-19-7.0.0_release_announcement/

We need a fresh feedback on this file using this version.

Thanks in advance

Gilles Caulier

The situation is the same with 7.0.0. Here is what I observe with the 7.0.0 appimage:

If two files in the same subdirectory have names that only differ by the case of one or more characters, only one is displayed in the album. The image displayed switches each time you restart digikam.

If two subdirectories inside of a directory have names that only differ by the case of one or more characters, only one album is displayed. The previews in the album are displayed with the broken image, and attempting to view the images shows the "Failed to load image" message. Refreshing the album corrects this problem.


Steps to reproduce:
convert -size 50x50 plasma: /home/john/test_collection/file1.jpg
convert -size 50x50 plasma: /home/john/test_collection/File1.jpg
mkdir /home/john/test_collection/album1
mkdir /home/john/test_collection/Album1
convert -size 50x50 plasma: /home/john/test_collection/album1/file_in_album1.jpg
convert -size 50x50 plasma: /home/john/test_collection/Album1/file_in_Album1.jpg


Relevant log output:
--- On starting digikam ---
...
::NewItemsFinder::slotStart: scan mode: ScanDeferredFiles
Digikam::NewItemsFinder::slotTotalFilesToScan: total scan value :  11642
Digikam::DImg::load: "/home/john/test_collection/File1.jpg" : "JPEG" file identified
Digikam::ItemScanner::prepareAddImage: Adding new item "/home/john/test_collection/File1.jpg"
Digikam::ItemScanner::commit: Scanning took 19 ms
Digikam::ItemScanner::~ItemScanner: Finishing took 27 ms
Digikam::DImg::load: "/home/john/test_collection/file1.jpg" : "JPEG" file identified
Digikam::ItemScanner::prepareAddImage: Adding new item "/home/john/test_collection/file1.jpg"
Digikam::ItemScanner::commit: Scanning took 4 ms
Digikam::ThumbnailCreator::loadImagePreview: Trying to get thumbnail with Exiv2 for "/home/john/test_collection/File1.jpg"
Digikam::ThumbnailCreator::loadImagePreview: Trying to get thumbnail with DImg preview for "/home/john/test_collection/File1.jpg"
Digikam::DImg::load: "/home/john/test_collection/File1.jpg" : "JPEG" file identified
Digikam::ItemScanner::~ItemScanner: Finishing took 8 ms
...
Digikam::DImg::load: "/home/john/test_collection/Album1/file_in_Album1.jpg" : "JPEG" file identified
Digikam::ItemScanner::prepareAddImage: Adding new item "/home/john/test_collection/Album1/file_in_Album1.jpg"
Digikam::ItemScanner::commit: Scanning took 11 ms
Digikam::ItemScanner::~ItemScanner: Finishing took 8 ms
Digikam::CollectionScanner::itemsWereRemoved: Removed items: (24048) related items: ()
Digikam::ThumbnailCreator::loadImagePreview: Trying to get thumbnail with Exiv2 for "/home/john/test_collection/Album1/file_in_Album1.jpg"
Digikam::ThumbnailCreator::loadImagePreview: Trying to get thumbnail with DImg preview for "/home/john/test_collection/Album1/file_in_Album1.jpg"
Digikam::DImg::load: "/home/john/test_collection/album1/file_in_album1.jpg" : "JPEG" file identified
Digikam::ItemScanner::prepareAddImage: Adding new item "/home/john/test_collection/album1/file_in_album1.jpg"
Digikam::ItemScanner::commit: Scanning took 8 ms
Digikam::DImg::load: "/home/john/test_collection/Album1/file_in_Album1.jpg" : "JPEG" file identified
Digikam::ItemScanner::~ItemScanner: Finishing took 8 ms
Digikam::CollectionScanner::itemsWereRemoved: Removed items: (24064) related items: ()
Digikam::ThumbnailCreator::load: Thumbnail is null for  "/home/john/test_collection/Album1/file_in_album1.jpg
...

--- On selecting Album 1 ---
Digikam::PreviewLoadingTask::execute: Try to get preview from "/home/john/test_collection/Album1/file_in_album1.jpg"
Digikam::PreviewLoadingTask::execute: Preview quality:  0
Digikam::MetaEngine::Private::printExiv2ExceptionError: Cannot load metadata from file /home/john/test_collection/Album1/file_in_album1.jpg  (Error # 9 :  /home/john/test_collection/Album1/file_in_album1.jpg: Failed to open the data source: No such file or directory (errno = 2)
Digikam::PreviewLoadingTask::loadImagePreview: Try to load DImg preview from: "/home/john/test_collection/Album1/file_in_album1.jpg"
Digikam::DImg::load: File "/home/john/test_collection/Album1/file_in_album1.jpg" does not exist
Digikam::DImg::load: File "/home/john/test_collection/Album1/file_in_album1.jpg" does not exist
Digikam::PreviewLoadingTask::execute: Cannot extract preview for "/home/john/test_collection/Album1/file_in_album1.jpg"
Digikam::StackedView::setViewMode: Stacked View Mode :  1
Digikam::ThumbnailCreator::load: Thumbnail is null for  "/home/john/test_collection/Album1/file_in_album1.jpg"
Digikam::StackedView::setViewMode: Stacked View Mode :  0
...

--- On refreshing Album 1 ---
Digikam::MaintenanceThread::generateThumbs: Creating a thumbnails task for generating thumbnails
Digikam::MaintenanceThread::generateThumbs: Creating a thumbnails task for generating thumbnails
Digikam::MaintenanceThread::generateThumbs: Creating a thumbnails task for generating thumbnails
Digikam::MaintenanceThread::generateThumbs: Creating a thumbnails task for generating thumbnails
Digikam::NewItemsFinder::slotStart: scan mode: ScheduleCollectionScan ::  ("/home/john/test_collection/Album1/")
Digikam::ActionThreadBase::run: Action Thread run  4  new jobs
Digikam::ActionThreadBase::slotJobFinished: One job is done
Digikam::ActionThreadBase::slotJobFinished: One job is done
Digikam::ActionThreadBase::slotJobFinished: One job is done
Digikam::ThumbnailCreator::load: Thumbnail is null for  "/home/john/test_collection/Album1/file_in_album1.jpg"
Digikam::ThumbnailCreator::load: Thumbnail is null for  "/home/john/test_collection/Album1/file_in_album1.jpg"
Digikam::ActionThreadBase::slotJobFinished: One job is done
Digikam::MaintenanceThread::slotThreadFinished: List of Pending Jobs is empty
Digikam::adjustedEnvironmentForAppImage: Adjusting environment variables for AppImage bundle
Digikam::DNotificationWrapper: Event is dispatched through a passive pop-up
Digikam::ActionThreadBase::cancel: Cancel Main Thread
Digikam::ActionThreadBase::cancel: Cancel Main Thread
Digikam::DImg::load: "/home/john/test_collection/Album1/file_in_Album1.jpg" : "JPEG" file identified
Digikam::ItemScanner::prepareAddImage: Adding new item "/home/john/test_collection/Album1/file_in_Album1.jpg"
Digikam::ItemScanner::commit: Scanning took 4 ms
Digikam::ItemScanner::~ItemScanner: Finishing took 10 ms
Digikam::CollectionScanner::itemsWereRemoved: Removed items: (24072) related items: ()

Yes, "utf8_general_ci" is known to be case insensitive. We are planning a major change to the tags table for MySQL. In this context we can switch to "utf8_bin" for paths. This change will only be made with digiKam-7.2.0 or later.

Maik

*** Bug 435767 has been marked as a duplicate of this bug. ***

*** Bug 445948 has been marked as a duplicate of this bug. ***

Fixed with commit:

https://invent.kde.org/graphics/digikam/-/commit/56c96d9aadca8a2108c08b09888541e52a0eedea

Maik

Git commit 56c96d9aadca8a2108c08b09888541e52a0eedea by Maik Qualmann.
Committed on 20/12/2021 at 07:16.
Pushed by mqualmann into branch 'master'.

use COLLATE utf8_bin for album path and image name
Related: bug 435767, bug 445948, bug 447233
FIXED-IN: 7.5.0

M  +4    -4    NEWS
M  +11   -2    core/data/database/dbconfig.xml.cmake.in
M  +9    -1    core/libs/database/coredb/coredbschemaupdater.cpp

https://invent.kde.org/graphics/digikam/commit/56c96d9aadca8a2108c08b09888541e52a0eedea