[Digikam-devel] Update query fails
Swati Lodha
swatilodha27 at gmail.com
Sat Jun 4 23:11:26 BST 2016
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/8fcc0e97/attachment.html>
More information about the Digikam-devel
mailing list