9ed37a87 |
1 | drop procedure if exists fix_all_vectors; |
2 | drop function if exists get_fix_vector; |
3 | delimiter // |
4 | /* compute correct vector for node */ |
a1e1fcea |
5 | create function get_fix_vector (node INT) returns varchar(230) |
9ed37a87 |
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 |
a1e1fcea |
22 | return ''; |
9ed37a87 |
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 ; |