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

[Richard Mortimer via KDE Bugzilla]

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

            Bug ID: 355831
           Summary: MySQL Schema Improvements
           Product: digikam
           Version: 5.0.0
          Platform: Compiled Sources
                OS: Linux
            Status: UNCONFIRMED
          Severity: wishlist
          Priority: NOR
         Component: Database-Schema
          Assignee: digikam-devel at kde.org
          Reporter: richm+kde at oldelvet.org.uk

Schema improvements to better support MySQL.


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.


Reproducible: Always

Steps to Reproduce:
1. Proof of concept patch series

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