[Digikam-devel] Update query fails
Gilles Caulier
caulier.gilles at gmail.com
Sun Jun 5 07:11:26 BST 2016
The PR ???
I see your commit in your git developpement branch.
I think this simple fixes can be committed directly in git master for
production. There are no intrusive and do not introduce new feature.
On remark about your commits : make it more atomic as possible. Your commit
introduce 2 fixes : one for all missing ";" at end of SQL statements, one
other to fix the mess between image and video metadata DB scan.
Gilles Caulier
2016-06-05 0:46 GMT+02:00 Swati Lodha <swatilodha27 at gmail.com>:
> 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
>>>
>>>
>>
>
> _______________________________________________
> 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/4008eb95/attachment.html>
More information about the Digikam-devel
mailing list