Il 01/05/2012 13:54, Markus Leuthold ha scritto:
>>> What is the meaning of the new colums Tags.lft and Tags.rgt?
>>
>> used to build the tree of tags, see
>>
http://www.sitepoint.com/hierarchical-data-database-2/>>
>> Regards,
>> Francesco
>
> Thanks for the link, really helpful! Well, it looks like I messed up my
> hierarchical data structure in Tags. lft and rgt of the parent "City" doesn't
> include all children "Paris" "Athens" anymore. Is it possible to recalculate
> the lft and rgt colums from the Tags table?
>
> thanks for your help!
> Kusi
the field `pid` kinda duplicate the tree structure pointing at the
parent tag, but I've no magic sql to do that, one solution would be to:
1) close digikam
2) backup database
3) check the backup
4) check again
5) drop Tags table
6) start digikam and close it shortly after
7) don't check the backup now, if it's gone it's gone
8) populate the newly created table `Tags` with the ones in the backup
table _keeping_ the ids, the dbaction is "InsertTag", copyed below
9) check ImageTags, TagProperties, ImageTagProperties for any mess,
compare also with backupped ones
10) start digikam
DO _NOT_ EXECUTE THESE QUERYES IN PHPMYADMIN OR ANY OTHER WEB CLIENT,
@VARIABLES ARE LIMITED TO CURRENT CONNECTION AND GENERALLY
APACHE/PHP/PMA MAY USE MULTIPLE ONES FOR DIFFERENT QUERIES EVEN IN THE
SAME TEXTBOX.
Keep in mind that there should be a root tag "_Digikam_root_tag_"
created by the following SQLs:
SELECT
@minLeft := IF(ISNULL(MIN(lft)), 1, MIN(lft)-1),
@maxRight := IF(ISNULL(MAX(rgt)), 2, MAX(rgt)+1)
FROM Tags
WHERE id >= 0 AND pid>=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO';
INSERT INTO Tags
(id, pid, name, icon, iconkde, lft, rgt)
VALUES
(0, -1, '_Digikam_root_tag_', 0, NULL, @minLeft, @maxRight )
here is the query to insert the tags:
<dbaction name="InsertTag" mode="transaction">
<statement mode="plain">LOCK TABLE Tags WRITE;</statement>
<statement mode="query">SELECT @myLeft := lft FROM Tags WHERE id =
:tagPID;</statement>
<statement mode="query">SELECT @myLeft := IF (@myLeft is null, 0,
@myLeft);</statement>
<statement mode="query">UPDATE Tags SET rgt = rgt + 2 WHERE rgt >
@myLeft;</statement>
<statement mode="query">UPDATE Tags SET lft = lft + 2 WHERE lft >
@myLeft;</statement>
<statement mode="query">INSERT INTO Tags(name, pid, lft, rgt)
VALUES(:tagname, :tagPID, @myLeft + 1, @myLeft + 2);</statement>
<statement mode="plain">UNLOCK TABLES;</statement>
</dbaction>
Regards,
Francesco
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel
Locked
