[Digikam-devel] Update query fails

Swati Lodha swatilodha27 at gmail.com
Sat Jun 4 23:46:53 BST 2016


I've sent the PR. Please review.

ᐧ

On Sun, Jun 5, 2016 at 3:41 AM, Swati Lodha <swatilodha27 at gmail.com> wrote:

> Yes.
>
> I'll commit now. Was waiting for verification.
>
> Thank you.
>>
> On Sun, Jun 5, 2016 at 3:39 AM, Gilles Caulier <caulier.gilles at gmail.com>
> wrote:
>
>> The patch sound good.
>>
>> Did you have a working ssh connexion now to commit yourself in KDE
>> repository ?
>>
>> `Gilles Caulier
>>
>> 2016-06-04 20:43 GMT+02:00 Swati Lodha <swatilodha27 at gmail.com>:
>>
>>> Final patch for coredb.cpp
>>>
>>> Thank you.
>>>>>>
>>> On Fri, Jun 3, 2016 at 11:44 PM, Gilles Caulier <
>>> caulier.gilles at gmail.com> wrote:
>>>
>>>> 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
>>>>>
>>>>>
>>>>
>>>> _______________________________________________
>>>> 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/20160605/f65ae36b/attachment.html>


More information about the Digikam-devel mailing list