| 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(230) |
| 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 ''; |
| 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 ; |