[Digikam-devel] Update query fails
Gilles Caulier
caulier.gilles at gmail.com
Sun Jun 5 07:16:09 BST 2016
Another remark : A fix done in master can be simply synchronized in your
devel branch easily with git. No need to make a new dedicated commit in
your branch. Later, when we merge back your branch to master, this will be
more problematic.
I explain well in this wiki page :
https://community.kde.org/Digikam/GSoC2014#Branches_Creation_and_Maintenance
Take a care yo your path in git repository about your devel banch...
Gilles Caulier
2016-06-05 8:11 GMT+02:00 Gilles Caulier <caulier.gilles at gmail.com>:
> 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/6f3e8f3d/attachment.html>
More information about the Digikam-devel
mailing list