[Digikam-devel] [PATCH 0/5] MySQL schema improvements

[Richard Mortimer]

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