Bug 338050

Summary:	MYSQL : nested set fields not updating when moving tag within hierarchy [patch]
Product:	[Applications] digikam	Reporter:	Martin Nichol <bugz>
Component:	Database-Mysql	Assignee:	Digikam Developers <digikam-bugs-null>
Status:	RESOLVED FIXED
Severity:	normal	CC:	caulier.gilles, swatilodha27
Priority:	NOR
Version:	5.0.0
Target Milestone:	---
Platform:	RedHat Enterprise Linux
OS:	Linux
Latest Commit:		Version Fixed In:	5.1.0
Sentry Crash Report:
Attachments:	Please test this. Please see this one too. Which of these approaches would be fine? Please test with this patch.

Description Martin Nichol 2014-08-05 12:55:52 UTC

Found this in 3.5.0 and reproduced in 4.0.0.

Looking at the Tags table, it appears to use a Nest Set (lft,rgt fields) for managing tag hierarchy in addition to the pid field.

When moving a tag within the hierarchy, the lft and rgt fields are not updated.

Given this initial tree:
+----+------+----------------------------+------+---------+-----+-----+
| id | pid  | name                       | icon | iconkde | lft | rgt |
+----+------+----------------------------+------+---------+-----+-----+
|  8 |    0 | Parent                     |    0 | NULL    |   4 |  15 |
| 10 |    8 | Child1                     |    0 | NULL    |   9 |  14 |
| 11 |    8 | Child2                     |    0 | NULL    |   5 |   8 |
| 12 |   10 | SubChild1A                 |    0 | NULL    |  12 |  13 |
| 13 |   10 | SubChild1B                 |    0 | NULL    |  10 |  11 |
| 14 |   11 | SubChild2A                 |    0 | NULL    |   6 |   7 |
+----+------+----------------------------+------+---------+-----+-----+

Moving SubChild1A under Child2 results in this:
+----+------+----------------------------+------+---------+-----+-----+
| id | pid  | name                       | icon | iconkde | lft | rgt |
+----+------+----------------------------+------+---------+-----+-----+
|  8 |    0 | Parent                     |    0 | NULL    |   4 |  15 |
| 10 |    8 | Child1                     |    0 | NULL    |   9 |  14 |
| 11 |    8 | Child2                     |    0 | NULL    |   5 |   8 |
| 12 |   11 | SubChild1A                 |    0 | NULL    |  12 |  13 |
| 13 |   10 | SubChild1B                 |    0 | NULL    |  10 |  11 |
| 14 |   11 | SubChild2A                 |    0 | NULL    |   6 |   7 |
+----+------+----------------------------+------+---------+-----+-----+

The pid is properly updated, but the lft and rgt columns still indicate is a child of Child1.

I suspect this also explains bug 273852.

Comment 1 caulier.gilles 2016-07-06 20:34:46 UTC

This file still valid using last digiKam 5.0.0 ?

Gilles Caulier

Comment 2 swatilodha27 2016-07-09 17:54:04 UTC

Martin,

How exactly did you move the tag? Did you first remove and then assigned the tag again?

Comment 3 Martin Nichol 2016-07-10 19:40:28 UTC

(In reply to swatilodha27 from comment #2)
> Martin,
> 
> How exactly did you move the tag? Did you first remove and then assigned the
> tag again?
It was drag and drop within the tags hierarchy.  

Martin

Comment 4 Martin Nichol 2016-07-10 19:43:10 UTC

(In reply to caulier.gilles from comment #1)
> This file still valid using last digiKam 5.0.0 ?
> 
> Gilles Caulier

I'll have to get back to you on that. 

Martin

Comment 5 swatilodha27 2016-07-12 11:51:59 UTC

I can reproduce the problem with 5.0.0 version.

Comment 6 swatilodha27 2016-07-12 11:56:46 UTC

Valid for both Internal and Remote.

Comment 7 swatilodha27 2016-07-12 21:22:05 UTC

Created attachment 100043 [details]
Please test this.

Comment 8 swatilodha27 2016-07-13 12:13:09 UTC

Created attachment 100063 [details]
Please see this one too. Which of these approaches would be fine?

Comment 9 swatilodha27 2016-07-14 17:44:02 UTC

Created attachment 100102 [details]
Please test with this patch.

Comment 10 caulier.gilles 2016-07-16 09:31:07 UTC

Swaty,

Just to be clear, what's the clear procedure to check if patch work as expected ?

Which mysql commands do you use to check tags hierarchy ?

Gilles

Comment 11 swatilodha27 2016-07-16 09:35:51 UTC

Steps to reproduce:

1) Add a new image and assign it 2-3 tags.
2) Check in Tags table, and observe the pid, lft, rgt values in it for the Tags assigned.
3) Back in DK, under "Tags" view (in left sidebar), drap and drop tags such that they're in hierarchy. (one tag becomes child of another)
4) Check back the Tags table.

(without patch)
--->parent tag id (pid) is updated, but lft and rgt values are still the same.

(with patch)
---> all three values updated according to hierarchy.

Comment 12 caulier.gilles 2016-07-16 13:57:21 UTC

I tested the Mysql Database into Tags table with Tora frontend.

I confirm that without the patch lft/rgt id are not updated in database when i move a tags from hierarchy in another place.

I confirm also that your patch do the job as expected.

Gilles Caulier

Comment 13 caulier.gilles 2016-07-16 20:37:47 UTC

Git commit b8df6225e3cafdd720120f4bad4150fd78525e02 by Swati Lodha.
Committed on 16/07/2016 at 20:33.
Pushed by swatil into branch 'master'.

Set fields in Tag table now updates BUG:338050 CCBUG:338050 FIXED-IN:5.1.0

M  +2    -0    app/dragdrop/tagdragdrop.cpp
M  +12   -0    libs/database/coredb/coredb.cpp
M  +8    -0    libs/database/coredb/coredb.h
M  +2    -0    libs/database/coredb/coredbchangesets.h

http://commits.kde.org/digikam/b8df6225e3cafdd720120f4bad4150fd78525e02