[Digikam-devel] [digikam] [Bug 355831] MySQL Schema Improvements

[via KDE Bugzilla]

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

caulier.gilles at gmail.com changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
  Attachment #95686|0                           |1
        is obsolete|                            |

--- Comment #9 from caulier.gilles at gmail.com ---
Comment on attachment 95686
  --> https://bugs.kde.org/attachment.cgi?id=95686
[PATCH 0/5] MySQL schema improvements

>From 8cfb15da5f4de65a734aa864c68871822e144de1 Mon Sep 17 00:00:00 2001
>From: Richard Mortimer <richm at oldelvet.org.uk>
>Date: Tue, 24 Nov 2015 08:56:22 +0000
>Subject: [PATCH 0/5] MySQL schema improvements
>
>Rewrite the MySQL database schema to remove the need for SUPER privileges
>when creating/amending the digikam database.
>
>Use foreign key referential integrity to achieve the behaviour of the
>triggers. Specifically use ON DELETE to cascade deletion of a Tag, Image,
>Album or AlbumRoot any items that depend upon it.
>
>This change does make the database less tolerant of bad data that references
>non-existant entries. But that can be argued to be a good thing because
>it causes a fast fail in the case of bugs/issues that would have otherwise
>caused silent data loss.
>
>The schema changes are not fully completed yet and is intended as a proof of
>concept patch series. Comments most welcome!
>
>Notes:
>
>Testing performed using the following MySQL setup.
>
>GRANT USAGE ON *.* TO 'digiuser'@'localhost' IDENTIFIED BY 'abc123';
>GRANT ALL PRIVILEGES ON `digikam`.* TO 'digiuser'@'localhost';
>CREATE DATABASE digikam;
>
>The conversion code was failing to catch SQL errors in the migration
>process. I corrected this by returning an error if a query fails but it is
>not clear if this is the intended behaviour and it maybe that this will
>uncover other issues both in SQLite and MySQL.
>
>The Albums table has an icon field that causes a circular reference to
>the Images table. This needs special handling both during table deletion
>and data migration. I put a hack in place for the table deletion case but
>did not address the migration where images will initially need creating
>without the icon being set and then the icon values need copying over
>once the Images have been migrated.
>
>The migration will fail if any orphan records exist that do not reference an
>existing AlbumRoot, Album, Image or Tag. Normally that would be due to
>some previous uncaught error due to failed creation or partial deletion.
>
>I did notice that in my newly created SQLite database with only a few test
>images the Tags "icon" field was populated with a zero instead of a null.
>This causes the migration to fail. I worked around this by executing the
>following against the SQLite database.
>
>update Tags set icon = null where icon = 0;
>
>The thumbnail and face schema have not been fully examined/converted yet.
>The triggers have been disabled to allow migration testing.
>
>Similarly I have not looked at database upgrade from the previous version
>of the MySQL schema. There is at least one user (myself) who has been
>using MySQL so having a proper upgrade to add the referential integrity
>would seem to be needed in a final solution.
>
>
>Richard Mortimer (5):
>  Ensure that an error is returned if the query fails.
>  Delete tables in reverse creation order.
>  Ensure that data is migrated in line with foreign key dependencies.
>  Remove trigger dependency for MySQL.
>  Temporary workaround to break MySQL references loop with the Albums
>    icon entry.
>
> data/database/dbconfig.xml.cmake.in        | 82 +++++++++++++++---------------
> libs/database/coredb/coredbcopymanager.cpp |  9 ++--
> libs/database/engine/dbenginebackend.cpp   |  2 +
> 3 files changed, 48 insertions(+), 45 deletions(-)
>
>-- 
>2.5.0
>

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