[Digikam-devel] Update query fails
Swati Lodha
swatilodha27 at gmail.com
Thu Jun 2 20:29:58 BST 2016
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
>>>>
>>>
>>>
>>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.kde.org/pipermail/digikam-devel/attachments/20160603/1f656cc5/attachment.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: coredb2.patch
Type: text/x-patch
Size: 3904 bytes
Desc: not available
URL: <http://mail.kde.org/pipermail/digikam-devel/attachments/20160603/1f656cc5/attachment.bin>
More information about the Digikam-devel
mailing list