[Digikam-devel] Update query fails
Gilles Caulier
caulier.gilles at gmail.com
Sat Jun 4 23:09:03 BST 2016
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
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.kde.org/pipermail/digikam-devel/attachments/20160605/41df56a0/attachment.html>
More information about the Digikam-devel
mailing list