wwwroot/backend/mysql/sys.sql

   1 drop procedure if exists fix_all_vectors;
   2 drop function if exists get_fix_vector;
   3 delimiter //
   4 /* compute correct vector for node */
   5 create function get_fix_vector (node INT) returns varchar(80)
   6 BEGIN
   7         declare np,mynode,safe int;
   8         declare vector varchar(80);
   9
  10         set safe=100; /* loop detection, max tree depth*/
  11         set mynode=node;
  12         set vector='';
  13         REPEAT
  14                 select node_parent into np from nodes where node_id = mynode;
  15                 select concat(lpad(mynode,8,'00000000'),vector) into vector;
  16                 set mynode=np;
  17                 set safe=safe - 1;
  18                 UNTIL np = NULL or np = 0 or safe = 0 or np = node
  19         END REPEAT;
  20
  21         if safe = 0 then
  22                 return 'loooped';
  23         end if;
  24         return vector;
  25
  26 END//
  27
  28 /* fix vector for all the nodes*/
  29 create procedure fix_all_vectors ()
  30 begin
  31         update nodes set node_vector=get_fix_vector(node_id);
  32 end//
  33 delimiter ;