[Digikam-devel] Update query fails
Gilles Caulier
caulier.gilles at gmail.com
Fri Jun 3 19:14:29 BST 2016
This patch sound fine.
I would to know the Marcel viewpoint here, just to be sure.
Take a care, the patch version 1 is also included in this one.
Gilles Caulier
2016-06-02 21:29 GMT+02:00 Swati Lodha <swatilodha27 at gmail.com>:
> Check this patch too.
>
> Thank you.
>
> ᐧ
>
> On Fri, Jun 3, 2016 at 12:37 AM, Swati Lodha <swatilodha27 at gmail.com>
> wrote:
>
>> Please check this patch.
>>
>> Sorry for inconvenience.
>>
>>
>> ᐧ
>>
>> On Thu, Jun 2, 2016 at 11:56 PM, Swati Lodha <swatilodha27 at gmail.com>
>> wrote:
>>
>>> Please find attached patch file for the same.
>>>
>>> Thank you.
>>>
>>>
>>> ᐧ
>>>
>>> On Thu, Jun 2, 2016 at 8:57 PM, Swati Lodha <swatilodha27 at gmail.com>
>>> wrote:
>>>
>>>> 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
>>>>>
>>>>
>>>>
>>>
>>
>
> _______________________________________________
> 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/20160603/39b4d75b/attachment.html>
More information about the Digikam-devel
mailing list