[Digikam-devel] documentation for database scheme

[Francesco Riosa]

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