Bug 276356

Summary: tags should have a root (maybe hidden)
Product: [Applications] digikam Reporter: Francesco Riosa <vivo75+kde>
Component: Database-SchemaAssignee: Digikam Developers <digikam-bugs-null>
Status: RESOLVED FIXED    
Severity: normal CC: caulier.gilles
Priority: NOR    
Version: 2.0.0   
Target Milestone: ---   
Platform: Compiled Sources   
OS: Linux   
Latest Commit: Version Fixed In: 2.0.0
Sentry Crash Report:

Description Francesco Riosa 2011-06-23 20:48:25 UTC 
Version:           2.0.0 (using KDE 4.6.4) 
OS:                Linux

the "The Nested Set Model" used for the tags tree require to have one and only one root. DK instead put many tags at the root level breaking this fundamental assumption.

One example is the following query which should return the tree of tags by name and depth

SELECT node.name, (COUNT(parent.name) - 1) AS depth
FROM tags AS node, tags AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
ORDER BY node.lft;

There are a pair of ways to overcome the current situation for existing and new installations:
1) Use a different algorithm, I've in mind one that while has some limitation for the tree depth is faster but need implementation
2) update existing trees at startup, put a root in place, update the parents of each node w/o root and live with it. Since it would be a very bad idea have a node with id 0 (it's a magic number in databases and math) we need to have a root with id < 0 (or to dump and reload the table).

While at the moment I'm not able to point out specific bug introduced by this behaviour it may cause many.


Reproducible: Always
Comment 1 Francesco Riosa 2011-06-23 20:50:41 UTC 
cannot work _today_ on this but it's very high on my priority list
Comment 2 Francesco Riosa 2011-06-23 21:03:54 UTC 
An example:
if we delete "tagl2b" then we delete erroneously "tl"* tags

mysql> SELECT CONCAT(SPACE(COUNT(parent.name) - 1), " + ", node.name, "(", node.pid, ":", node.id, ")") AS node
    -> FROM tags AS node, tags AS parent
    -> WHERE node.lft BETWEEN parent.lft AND parent.rgt
    -> GROUP BY node.name
    -> ORDER BY node.lft;

+----------------------+
| node                 |
+----------------------+
|  + tagl0a(0:1)       |
|   + tagl1c(1:4)      |
|   + tagl1b(1:3)      |
|    + tagl2d(3:8)     |
|    + tagl2c(3:7)     |
|    + tagl2b(3:6)     |
|     + tl0(0:11)      |
|      + tl0b(11:13)   |
|      + tl0a(11:12)   |
|       + tl0a1(12:15) |
|       + tl0a0(12:14) |
|     + tagl3b(6:10)   |
|     + tagl3a(6:9)    |
|    + tagl2a(3:5)     |
|   + tagl1a(1:2)      |
+----------------------+

Should be

+----------------------+
| node                 |
+----------------------+
|  + tagl0a(0:1)       |
|   + tagl1c(1:4)      |
|   + tagl1b(1:3)      |
|    + tagl2d(3:8)     |
|    + tagl2c(3:7)     |
|    + tagl2b(3:6)     |
|     + tagl3b(6:10)   |
|     + tagl3a(6:9)    |
|    + tagl2a(3:5)     |
|   + tagl1a(1:2)      |
|  + tl0(0:11)      |
|   + tl0b(11:13)   |
|   + tl0a(11:12)   |
|    + tl0a1(12:15) |
|    + tl0a0(12:14) |
+----------------------+
Comment 3 Francesco Riosa 2011-06-24 23:00:07 UTC 
Git commit ef9afa84ba358ec335816ed345163de9de884cc4 by Francesco Riosa.
Committed on 24/06/2011 at 22:32.
Pushed by riosa into branch 'sql/2.0'.

Add root to Tree table and fix some related query

As explained in bug 276356 tree table need a root for sql to work well.
The startup query now insert an element with id = 0

During the cleanup I've decided to get rid of the confusing and mostly
redundant TagsTree table.

A bug found is that tags query had named parameter :tagID mentioned
multiple times in the query which is forbidden by QT implementation.

This patch still need testing and as such will be commited in sql2.0
branch but should arrive in master soon

TODO:
 - check the SQLite version
 - alter the c++ code where needed
 - DELETE in the tags tree is not managed by triggers anymore, will be
   managed by multiple query.
CCBUG: 276356

M  +99   -54   data/database/dbconfig.xml.cmake     

http://commits.kde.org/digikam/ef9afa84ba358ec335816ed345163de9de884cc4
Comment 4 Francesco Riosa 2011-06-24 23:11:39 UTC 
All seem to work, also migration from database created in DK 1.9.
I'm AFK until monday, this changes will be commited then.
Also all bugs regarding tags must be verified again and eventually closed
Comment 5 Francesco Riosa 2011-06-27 20:00:33 UTC 
commited changes to master, migration of the following tables need investigation:
- ImageMetadata
- ImageTagProperties
- TagProperties
Comment 6 Francesco Riosa 2011-06-28 04:14:19 UTC 
Git commit f8bb78f09de515dfa6794dc9e9fc99fd0948e073 by Francesco Riosa.
Committed on 28/06/2011 at 01:36.
Pushed by riosa into branch 'master'.

Added migration for ImageHistory and ImageRelations

CCBUG: 276356

M  +28   -0    data/database/dbconfig.xml.cmake     
M  +16   -0    libs/database/databasecopymanager.cpp     

http://commits.kde.org/digikam/f8bb78f09de515dfa6794dc9e9fc99fd0948e073
Comment 7 Francesco Riosa 2011-06-28 04:14:19 UTC 
Git commit ccee7de27e57e23bbb3688578ffd79df03f6c1b4 by Francesco Riosa.
Committed on 28/06/2011 at 00:10.
Pushed by riosa into branch 'master'.

fix typo: ImageMetadat[ea]

CCBUG: 276356

M  +2    -2    data/database/dbconfig.xml.cmake     

http://commits.kde.org/digikam/ccee7de27e57e23bbb3688578ffd79df03f6c1b4
Comment 8 Francesco Riosa 2011-06-28 11:45:21 UTC 
Git commit 18c301bdd2d070e6e7ec0911924caee8101e5780 by Francesco Riosa.
Committed on 28/06/2011 at 13:44.
Pushed by riosa into branch 'master'.

fix a bug preventing index creation in mysql db

create_index_if_not_exists() did check for indexes of the same name even
in other schemas, thus preventing index creation if more than one
digikam
database were hosted on the databases server.

CCBUGS: 276356

M  +2    -1    data/database/dbconfig.xml.cmake     

http://commits.kde.org/digikam/18c301bdd2d070e6e7ec0911924caee8101e5780
Comment 9 Francesco Riosa 2011-06-28 18:52:40 UTC 
Git commit f97d339ef90214c6f4d4418dc748d9b259f234f6 by Francesco Riosa.
Committed on 28/06/2011 at 20:46.
Pushed by riosa into branch 'sql/2.0'.

DB schema converted from 1.9 to 2.0

Much better now.

Still: indexes changed are not re-created. To do a better work a
procedure drop_index_if_exists() should be create to complement
create_index_if_not_exists().
Then changing an index will follow this path:
1) create a new index with a different name
2) add a call to drop_index_if_exists() to dbconfig.xml, with the old
name

CCBUGS: 276356

A  +557  -0    tests/databases/20/mysql19to20_digikam.sql     

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