[Digikam-devel] [Bug 283323] a new label _Digikam_root_tag_ is created every time

[Francesco Riosa]

https://bugs.kde.org/show_bug.cgi?id=283323





--- Comment #7 from Francesco Riosa <francesco+kde pnpitalia it>  2011-10-10 18:31:11 ---
(In reply to comment #6)
> CREATE TABLE `Tags` (
>   `id` int(11) NOT NULL AUTO_INCREMENT,
>   `pid` int(11) DEFAULT NULL,
>   `name` longtext CHARACTER SET utf8 NOT NULL,
>   `icon` int(11) DEFAULT NULL,
>   `iconkde` longtext CHARACTER SET utf8,
>   `lft` int(11) NOT NULL,
>   `rgt` int(11) NOT NULL,
>   PRIMARY KEY (`id`)
> ) ENGINE=MyISAM AUTO_INCREMENT=47 DEFAULT CHARSET=latin1
> 
> 
> show create table TagsTree;
> CREATE ALGORITHM=UNDEFINED DEFINER=`andor`@`localhost` SQL SECURITY DEFINER
> VIEW `TagsTree` AS select `Tags`.`id` AS `id`,`Tags`.`pid` AS `pid` from `Tags`

bad, the record is created by the following statements:

-- 8< 8< 8< 8< 8< 8< 8< 8< 8< 8< 8< 8< 8< 8< 8< 8<

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';

REPLACE INTO Tags
    (id, pid, name, icon, iconkde, lft, rgt)
    VALUES
    (0, -1, '_Digikam_root_tag_', 0, NULL, @minLeft, @maxRight )
SET SQL_MODE=@OLD_SQL_MODE;

-- 8< 8< 8< 8< 8< 8< 8< 8< 8< 8< 8< 8< 8< 8< 8< 8<

The first select store in two variables the bounds of our tags tree.

The set stmt tell mysql not to assign a value to the id field even if it's
zero, by default it increment a primary key column if the value inserted is
zero or NULL

the replace stmt then should only alter (or insert) the record 0, never
inserting a duplicate one.

Could I abuse of your kindeness and ask to manually run the statements between
the scissors manually from a console client (phpmyadmin is not an option, it
sometimes mangle @vars)

Does it add a new record?
If yes could you try replacing the replace with the following two?

INSERT IGNORE INTO Tags
    (id, pid, name, icon, iconkde, lft, rgt)
    VALUES
    (0, -1, '_Digikam_root_tag_', 0, NULL, @minLeft, @maxRight );

UPDATE Tags
SET
  lft = @minLeft,
  rgt = @maxRight
WHERE id = 0
;

Also the mysql version could be interesting.

just a closing note:
do _not_ delete the records from the table, it would broke the tree structure,
do it from digikam if possible, if not we need to work on it too

thanks,
Francesco

-- 
Configure bugmail: https://bugs.kde.org/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.