[Digikam-devel] Update query fails
Swati Lodha
swatilodha27 at gmail.com
Thu Jun 2 16:27:26 BST 2016
Hello
In coredb.cpp:4547 ";" is missing after the UPDATE query. This change
should be committed?
Regards
ᐧ
On Wed, Jun 1, 2016 at 6:14 PM, Richard Mortimer <
richm+digikam at oldelvet.org.uk> wrote:
> 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
> >
> _______________________________________________
> Digikam-devel mailing list
> Digikam-devel at kde.org
> https://mail.kde.org/mailman/listinfo/digikam-devel
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.kde.org/pipermail/digikam-devel/attachments/20160602/dff08541/attachment.html>
More information about the Digikam-devel
mailing list