[Digikam-devel] Update query fails
Richard Mortimer
richm+digikam at oldelvet.org.uk
Wed Jun 1 13:44:32 BST 2016
On 01/06/2016 10:12, Swati Lodha wrote:
> I understand this now. Thank you for clarification.
>
> So as it mentioned around coredb.cpp:549
>
> 549 // We need to work around the table constraint, no we want to
> delete older stale albums with
> 550 // the same relativePath, and adjust relativePaths depending on
> albumRoot.
>
>
> I think that this needs to be done?
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.
>
> Regards
> ᐧ
>
> On Wed, Jun 1, 2016 at 2:28 PM, Marcel Wiesweg <marcel.wiesweg at gmx.de
> <mailto:marcel.wiesweg at gmx.de>> wrote:
>
> Have a look at the (rather complex) CollectionScanner.
>
> The problem is the nature of our scan, which will always run in all
> kinds of
> race conditions with the file system.
>
> When a folder is moved, we may notice that it is removed at the previous
> location. Later, we see it appear at the new location. It would be
> unfortunate
> to have completely deleted all album metadata in the meantime.
> Therefore, it
> is made a stale album, which can be resurrected later if a new place
> can be
> identified.
>
> This is done in a similar way for images. Here, we can identify them by
> content, so the storage of "removed" images is even more extensively
> done.
>
>
>
> > This query is executed in core/libs/database/coredb.cpp:576 in the function
> > to create stale Albums, that is why the albumRoot has been set to 0.
> >
> > I would like to ask why is there a need to create the stale Albums?
> >
> > Regards
> > ᐧ
> >
> > On Wed, Jun 1, 2016 at 1:19 AM, Marcel Wiesweg
> <marcel.wiesweg at gmx.de <mailto:marcel.wiesweg at gmx.de>>
> >
> > wrote:
> > > Please check the context where it is called, but I am quite sure
> that
> > > there is
> > > a special meaning in setting album root to 0.
> > > I'm sure there is a similar case with deleted images where album
> is set to
> > > 0,
> > > but the entry is preserved in case the image reappears at a
> different
> > > place
> > > (moving files where the removal is noticed first)
> > >
> > > Marcel
> > >
> > > > Hello.
> > > >
> > > > I was facing this error from past few days:
> > > >
> > > > MariaDB [digikam]> update Albums set albumRoot=0 where id=10;
> > > >
> > > > > ERROR 1452 (23000): Cannot add or update a child row: a
> foreign key
> > > > > constraint fails (`digikam`.`Albums`, CONSTRAINT
> `Albums_AlbumRoot
> > > > > s` FOREIGN KEY (`albumRoot`) REFERENCES `AlbumRoots` (`id`)
> ON DELETE
> > > > > CASCADE ON UPDATE CASCADE)
> > > >
> > > > I figured out that as 'id' is AlbumRoots table is not 0. So
> 'albumRoot'
> > >
> > > in
> > >
> > > > Images table can't be set to 0. This is I think the possible
> reason for
> > >
> > > FK
> > >
> > > > constraint failing.
> > > >
> > > > I used this statement instead & it worked:
> > > >
> > > > MariaDB [digikam]> update Albums set albumRoot=1 where id=10;
> > > >
> > > > > Query OK, 0 rows affected (0.06 sec)
> > > > > Rows matched: 1 Changed: 0 Warnings: 0
> > > >
> > > > Please look into this.
> > > >
> > > > Regards
> > > > ᐧ
> > >
> > > _______________________________________________
> > > Digikam-devel mailing list
> > > Digikam-devel at kde.org <mailto:Digikam-devel at kde.org>
> > > https://mail.kde.org/mailman/listinfo/digikam-devel
>
> _______________________________________________
> Digikam-devel mailing list
> Digikam-devel at kde.org <mailto:Digikam-devel at kde.org>
> https://mail.kde.org/mailman/listinfo/digikam-devel
>
>
>
>
> _______________________________________________
> Digikam-devel mailing list
> Digikam-devel at kde.org
> https://mail.kde.org/mailman/listinfo/digikam-devel
>
More information about the Digikam-devel
mailing list