[Digikam-devel] Update query fails

Marcel Wiesweg marcel.wiesweg at gmx.de
Tue Jun 7 10:43:55 BST 2016


Hi,

I was also thinking along the lines of a solution "b" to have proper and clean 
support.
This would require some trivial support to filter out special values at the 
right places.

Using 0 as magic values usually comes handy and works fast and easy, which is 
the reason why it was used, but is not 100% clean.

Marcel


> It is all part of the bigger picture that needs considering to allow
> referential integrity to be applied across the (MySQL) database. I don't
> have the years of experience with digikam that Marcel does but I think
> the following is required:
> 
> 1 - apply "ideal" referential integrity to the MySQL digikam schema
> (mostly done).
> 
> 2 - now identify where things break down due to interaction with the
> outside world (file systems, other photo editors/tools). Generally I
> think this is easy to spot and is normally the root of an information
> hierarchy. (tags tree, albums, album roots spring to mind).
> 
> I think that digikam has been pretty consistent with using a magic "0"
> value to represent where something no-longer fits into a hierarchy but
> is likely to re-appear in due course.
> 
> 3 - Once identified the architectural decision is how to handle it in a
> database configured to enforce referential integrity. Bear in mind that
> this needs to continue to support SQLite too without having to add too
> many special cases. Note that SQLite is capable of supporting
> referential integrity so the choice may be to adopt the same solution
> for both.
> 
> Really the choices for handling the orphaned items are:
> 
> a - disable referential integrity on those affected fields. That is
> almost trivial to implement but does negate some of the benefits.
> 
> b - add "special" internal rows that can be used to collect these
> temporarily orphaned nodes. I know that there is one at the root of the
> tags tree for MySQL. Note they are a bit tricky to arrange because they
> really need to be created with a primary key of zero to match the
> existing behaviour of putting zero in there.
> 
> c - change to use null instead of a magic placeholder value. That is
> probably a purer solution but does create differences between SQLite and
> MySQL.
> 
> I'd probably suggest that option b is the right one to minimise chances
> of short/medium term breakage but would be interested to know what
> others think.
> 
> Regards
> 
> Richard
> 
> P.S. even if b & c are chosen there are some potential issues that may
> occur if multiple orphaned tags, folders, images have the same name.
> That will trip up with existing "unique" constraints on the database. I
> suspect that this should just be left as "good enough" for now.
> 



More information about the Digikam-devel mailing list