Digikam does not pick up the same picture if it is present twice in a folder, one file with a lowercase and one file with an uppercase extension: Only one variant will be displayed in the database. This is the problem as it occurs in my use case. I have to look further (if I find the time...), whether the problem is one of the following: - the same file with the same content/metadata - the same file name, only with a varying extension - not only the extension, but the case of the filename itself - MySQL-specific or also for SQLite (I use MySQL!) Reproducible: Always Steps to Reproduce: 1. Create a new folder. 2. Copy a picture in it. Name it pic.JPG. 3. Copy the same picture in the folder. Now name it pic.jpg Actual Results: Digikam will only display pic.JPG. pic.jpg is missing. Expected Results: pic.jpg and pic.JPG should be present. MySQL-Backend
Bug still reproducible in digiKam Version5.0.0-Beta7 Steps to reproduce: 1) Create a new folder. 2) Add image nature.jpg 3) Add same image by name nature.JPG 1) Create a another new folder. 2) Add image nature.jpg 3) Add same image by name NATURE.jpg In both the cases, two images with respective names are present in each folder (in case of SQLite). But images aren't present with MySQL (both Internal and Remote)
The "case-sensitivity" is by default in SQLite, and not in MySQL ( http://dev.mysql.com/doc/refman/5.7/en/charset-mysql.html ) Collation value that compares string in case-sensitive manner needs to be used. Replacing dbconfig.xml.cmake.in:955 with // name LONGTEXT CHARACTER SET utf8 COLLATE utf8_bin NOT NULL // Would it work?
Please provide an exact patch against git/master... Gilles
Created attachment 99691 [details] To remove case insensitivity in MySQL
Yes, the patch works, if a new database is created. Maik
What in case of existing DB?
A migration rules must be written. There are a lots of at end of DB XML schema. Of course the schema revision for Mysql must increased. Gilles
Created attachment 99700 [details] To improve schema version
Swaty, The 2nd patch sound changes a lots of code. Why ? Can you details the patch ? Gilles
Two functions are basically added to update the version in schemaupdater file 1) copyV4toV5() 2) updateV7toV8() And a dbaction "UpdateSchemaFromV7ToV8" in XML schema file.
*** Bug 268204 has been marked as a duplicate of this bug. ***
Created attachment 99735 [details] Updated patch
Richard, I would to have your viewpoint about Swati patch to fix this issue, especially the schema updater part to fix table properties. Thanks in advance Gilles Caulier
Changing the collation is the correct thing to do. Regarding the patch the schema update is required but the v7 to v8 patch needs to perform an "alter table" command. The update has to assume that the v7 schema is intact and any changes are as specified. That said before 5.0 is released we need to update the MySQL v7 schema. Whilst MySQL has never been a stable/supported database the existing 4.x series uses a base v7 and the existing new database v7 schema has the changes for referential integrity included. IMHO the v7 to v8 updater should convert the 4.x schema to the latest schema. Anyone who has been using the 5.x beta series will be able to "fixup" their schema with a simple version update command. I will dig out my notes and generate update commands to include in Swati's patch so that the schema update gets done in a single commit.
Swati, I think we need case-sensitivity in almost all text fields. I have tested it once for Albums.relativePath. It does the problem here on, if the path is different only in case-sensitivity. Is there a global statement for the DB? Maik
(In reply to Maik Qualmann from comment #15) > Swati, > I think we need case-sensitivity in almost all text fields. I have tested it > once for Albums.relativePath. It does the problem here on, if the path is > different only in case-sensitivity. Is there a global statement for the DB? I think in mysql-global.conf file, you could change "collation_server=utf8_general_ci" to "collation-server=utf8_bin" Or in etc/mysql/my.cnf file, under [mysqld] section, you need to change the collation, since MySQL is case insensitive by default.
Created attachment 99791 [details] Final patch Please test this patch.
I think the text fields from AlbumRoots need it too: (line 934) label LONGTEXT, identifier LONGTEXT, specificPath LONGTEXT, Maik
Created attachment 99808 [details] schemaupdater6.patch Ok, this patch works for a new MySQL DB and for an update of the DB. Swati, please test the patch. I will this patch now commit to git/master and close this bug. Maik
Git commit d18903fa644affd5f2b3c275b2544a54a9d64782 by Maik Qualmann. Committed on 02/07/2016 at 17:30. Pushed by mqualmann into branch 'master'. make MySQL text fields case-sensitive FIXED-IN: 5.0.0 M +1 -2 NEWS M +81 -37 data/database/dbconfig.xml.cmake.in M +4 -1 libs/database/coredb/coredbschemaupdater.cpp http://commits.kde.org/digikam/d18903fa644affd5f2b3c275b2544a54a9d64782
Patch works for me.