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
cannot work _today_ on this but it's very high on my priority list
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) | +----------------------+
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
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
commited changes to master, migration of the following tables need investigation: - ImageMetadata - ImageTagProperties - TagProperties
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
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
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
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