Redmine

For those who had the nested set disordered in MySQL DataBase, I develop a procedure that recalculate the nested set base on the parent_id. It's not a solution, just a way to rearange the mess. It can be better.
Watch out to the max_sp_recursion_depth variable inside the procedure.
Here is:

-- Procedure BEGIN
DROP PROCEDURE `recalculateNetstedSet`//
CREATE PROCEDURE `recalculateNetstedSet`(parent INT, INOUT left_value INT)
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE son_ID INTEGER;
  DECLARE sons CURSOR FOR SELECT id FROM projects WHERE parent_id IS NULL;
  DECLARE sons2 CURSOR FOR SELECT id FROM projects WHERE parent_id = parent;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;  
  set max_sp_recursion_depth=10;

  IF parent IS NOT NULL THEN
    UPDATE projects set lft = left_value where id = parent;
    SET left_value = left_value+1;
  END IF;

  OPEN sons;
  OPEN sons2;

  IF parent IS NULL THEN
    FETCH sons INTO son_ID;
    REPEAT
      IF son_ID IS NOT NULL THEN
        CALL recalculateNetstedSet(son_ID,left_value);
        SET left_value = left_value+1;
      END IF;
    FETCH sons INTO son_ID;
    UNTIL done END REPEAT;
  ELSE
    FETCH sons2 INTO son_ID;
    REPEAT      
      IF son_ID IS NOT NULL THEN
        CALL recalculateNetstedSet(son_ID,left_value);
        SET left_value = left_value+1;
      END IF;
    FETCH sons2 INTO son_ID;
    UNTIL done END REPEAT;
  END IF;

  UPDATE projects set rgt = left_value where id = parent;

  CLOSE sons;
  CLOSE sons2;
END
-- Procedure END

To call the procedure:
set @parent = NULL;
set @left = 1;
CALL recalculateNetstedSet(@parent,@left);

I think the procedure got wrong for the wiki sintax. I will try again, just the procedure this time.
Sorry I should have seen the preview ;)

DROP PROCEDURE `recalculateNetstedSet`//
CREATE PROCEDURE `recalculateNetstedSet`(parent INT, INOUT left_value INT)
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE son_ID INTEGER;
  DECLARE sons CURSOR FOR SELECT id FROM projects WHERE parent_id IS NULL;
  DECLARE sons2 CURSOR FOR SELECT id FROM projects WHERE parent_id = parent;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;  
  set max_sp_recursion_depth=10;

  IF parent IS NOT NULL THEN
    UPDATE projects set lft = left_value where id = parent;
    SET left_value = left_value+1;
  END IF;

  OPEN sons;
  OPEN sons2;

  IF parent IS NULL THEN
    FETCH sons INTO son_ID;
    REPEAT
      IF son_ID IS NOT NULL THEN
        CALL recalculateNetstedSet(son_ID,left_value);
        SET left_value = left_value+1;
      END IF;
    FETCH sons INTO son_ID;
    UNTIL done END REPEAT;
  ELSE
    FETCH sons2 INTO son_ID;
    REPEAT      
      IF son_ID IS NOT NULL THEN
        CALL recalculateNetstedSet(son_ID,left_value);
        SET left_value = left_value+1;
      END IF;
    FETCH sons2 INTO son_ID;
    UNTIL done END REPEAT;
  END IF;

  UPDATE projects set rgt = left_value where id = parent;

  CLOSE sons;
  CLOSE sons2;
END

I too experienced this, I removed the "dependent_destroy" declaration from app/project.rb as I believe this is not applicable for the awesome_nested_set gem. This seems to have made things better.

app/project.rb Line 51

-- acts_as_nested_set :order => 'name', :dependent => :destroy
++   acts_as_nested_set :order => 'name'

Hope this helps,

Paul

Here's another version of the stored proc which tries to keep a sane alphabetical sort order on projects and subprojects. It also corrects the typo (recalculate Netsted Set) :)

CREATE DEFINER=`redmine`@`` PROCEDURE `recalculateNestedSet`(parent INT, INOUT left_value INT)
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE son_ID INTEGER;
  DECLARE sons CURSOR FOR SELECT id FROM projects WHERE parent_id IS NULL ORDER BY name;
  DECLARE sons2 CURSOR FOR SELECT id FROM projects WHERE parent_id = parent ORDER BY name;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;  
  set max_sp_recursion_depth=10;
  IF parent IS NOT NULL THEN
    UPDATE projects set lft = left_value where id = parent;
    SET left_value = left_value+1;
  END IF;
  OPEN sons;
  OPEN sons2;
  IF parent IS NULL THEN
    FETCH sons INTO son_ID;
    REPEAT
      IF son_ID IS NOT NULL THEN
        CALL recalculateNestedSet(son_ID,left_value);
        SET left_value = left_value+1;
      END IF;
    FETCH sons INTO son_ID;
    UNTIL done END REPEAT;
  ELSE
    FETCH sons2 INTO son_ID;
    REPEAT      
      IF son_ID IS NOT NULL THEN
        CALL recalculateNestedSet(son_ID,left_value);
        SET left_value = left_value+1;
      END IF;
    FETCH sons2 INTO son_ID;
    UNTIL done END REPEAT;
  END IF;
  UPDATE projects set rgt = left_value where id = parent;
  CLOSE sons;
  CLOSE sons2;
END 

Why not: Project.update_all(:lft=>nil,:rgt=>nil); Project.rebuild! or something like that ?

Introducing stored procedure is limited to your own database system, while Redmine tries to support sqlite3, Postgresql and Mysql the same time.

This is what I did to get the order back to work:

1. Go to your database
2. Make a backup of your "projects" table
3. Set the value for "lft" and "rgt" to "0"
4. Go to you console and use "ruby script/runner -e production 'Project.rebuild!'" to rebuild the project order

In case anything goes wrong you still have the backup.

Hi all,

I did what Sven suggests (zero lft and rgt on projects and then Project.rebuild). Nested projects appear alphabetically inside toplevel projects, but toplevel projects are out of order. How can I make the toplevel projects appear in order?

Thanks!

Sven Culley wrote:

This is what I did to get the order back to work:

1. Go to your database
2. Make a backup of your "projects" table
3. Set the value for "lft" and "rgt" to "0"
4. Go to you console and use "ruby script/runner -e production 'Project.rebuild!'" to rebuild the project order

In case anything goes wrong you still have the backup.

in redmine 2,

4. Go to you console and use "ruby script/runner -e production 'Project.rebuild!'" to rebuild the project order

the command above doesn't work. maybe :

ruby script/rails runner -e production 'Project.rebuild!'