Bug 268204

Sorry, "Reproducible: " should be: ALWAYS

Holger, we need to set this option at some point when creating the database?

Hi Daniel,

thanks for your detailed report!
I would like to add the COLLATE keyword to the table definition.
As collation type I would sugest to use utf8_bin since we use UTF-8 as default charset. Is this okay for you (and can you try to use this and give us a feedback - in my tests it seems to work)?

Marcel, do we need to update the database schema to version 7 or can I extend the current (version 6) schema definition (since the gsoc is not official released).

Hi, I just changed to collation of the whole table to utf8_bin, and it shows all images

BUT

digikam shows problems when importing files containing "Umlaute" (ä, ö, ü) in the file-name, eventually with other special characters, too (don't know). I exportet my original "images" table, renamed it, and created a new images table as follows:

CREATE TABLE IF NOT EXISTS `Images` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `album` int(11) DEFAULT NULL,
  `name` longtext NOT NULL,
  `status` int(11) NOT NULL,
  `category` int(11) NOT NULL,
  `modificationDate` datetime DEFAULT NULL,
  `fileSize` int(11) DEFAULT NULL,
  `uniqueHash` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `album` (`album`,`name`(332)),
  KEY `dir_index` (`album`),
  KEY `hash_index` (`uniqueHash`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=63295 ;


I have no problems when using latin1 collation and charset on the filed "name":

CREATE TABLE IF NOT EXISTS `Images` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `album` int(11) DEFAULT NULL,
  `name` longtext CHARACTER SET latin1 COLLATE latin1_general_cs NOT NULL,
  `status` int(11) NOT NULL,
  `category` int(11) NOT NULL,
  `modificationDate` datetime DEFAULT NULL,
  `fileSize` int(11) DEFAULT NULL,
  `uniqueHash` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `album` (`album`,`name`(332)),
  KEY `dir_index` (`album`),
  KEY `hash_index` (`uniqueHash`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=63295 ;

--------------
slightly off-topic:
why does field "album" allow NULL?
I have lots of entries in images with album name NULL.
Where do they come from?
I guess I can delete them???
--------------

> Marcel, do we need to update the database schema to version 7 or can I extend
> the current (version 6) schema definition (since the gsoc is not official
> released).

I would really like to avoid incrementing the schema for this, unless really necessary. Is it possible to solve via ALTER TABLE statements? Then we'd have a workaround for existing MySQL users.

> I have no problems when using latin1 collation and charset on the filed "name":

We use UTF8 everywhere internally and never latin1. Maybe something went wrong during import/export?

> why does field "album" allow NULL?

Information about deleted images is kept for some time, then the album is null. If you moved photos from A to B, then scan first A then B, information would be lost.

> As collation type I would sugest to use utf8_bin 

Weird that MySQL does not provide any utf8 _cs collations...

Hi Marcel, thanks for the info about the album = NULL, so I just ignore that...

> We use UTF8 everywhere internally and never latin1. Maybe something went wrong
> during import/export?

I've done several tests with several database collation settings. I always added a new folder in digikam and imported the images from a folder on my desktop. 

Whenever the table "images" (or just the field "name") had COLLATE=utf8_bin digikam didn't show the correct file-name for a file called "img_123_oberkörper.jpg" allthough the file was correctly saved in the database. In digikam album the image was replaced by the placeholder-image, clicking on it gave "could not load images" (or alike..) and renaming the file within digikam also did not work...

A "SELECT FROM images WHERE name LIKE '%oberk%'" gave the correct results. Don't know what happens within digikam...

Please try to import a file "nö.jpg" while collation for "name" is set to utf8_bin and see if you can reproduce...

> Is it possible to solve via ALTER TABLE statements?

When the database has collation utf8_unicode_ci, as far as I know you can always use "WHERE BINARY" or "COLLATE utf8_bin" in the select statements where case sensitive behaviour is necessary....

> When the database has collation utf8_unicode_ci, as far as I know you can
> always use "WHERE BINARY" or "COLLATE utf8_bin" in the select statements where
> case sensitive behaviour is necessary....

That could be the easier solution... Images.name, Albums.relativePath, AlbumRoots.specificPath, and in some cases also AlbumRoots.identifier are critical. Need to check if there are more fields involved. At least until now, no other obscure bugs have been discovered.

*** This bug has been marked as a duplicate of bug 364131 ***

Not reproducible with 7.0.0 beta1.