node weights update
[mirrors/Kyberia-bloodline.git] / wwwroot / backend / mysql / test.sql
1 drop procedure if exists k_neurons;
2 drop function if exists k_get_node_weigth;
3 delimiter //
4 create function k_get_node_weigth (node INT, user INT) returns DOUBLE
5 BEGIN
6 declare vector,node2,len,n_owner,offset int;
7 declare final,n_weight,o_weight,s_weight double;
8
9 select node_vector into vector from nodes where node_id = node;
10 set final = 1;
11 /* select k from nodes into final where node_id = node;*/
12
13 set len = length(vector);
14 set offset = 1;
15 WHILE offset < len DO
16 /* XXX node length is hardcoded */
17 set node2 = substring(vector,offset,8);
18 set offset = offset + 8;
19 /* weigths from user to:
20 - all nodes from node to root node
21 - all node owners from node to root node
22 - all nodes between themselves (safe?)
23 */
24 select node_weight,node_creator into n_weight,n_owner from nodes where node_id=node2;
25 select synapse_weight into s_weight from neurons where src=user and dst=node2;
26 select synapse_weight into o_weight from neurons where src=user and dst=n_owner;
27
28 if o_weight = NULL or o_weight=0 then set o_weight=1; end if;
29 if s_weight = NULL or s_weight=0 then set s_weight=1; end if;
30 if n_weight = NULL or n_weight=0 then set n_weight=1; end if;
31
32 set final = final * s_weight * o_weight * n_weight;
33 END WHILE;
34 RETURN final;
35 END//
36 create procedure k_neurons ()
37 begin
38 -- select k_get_node_weigth(node_id,904) from nodes where k>0
39 -- and node_created>now()-interval 20 day;
40 select k,node_id,node_name,k_get_node_weigth(node_id,904) from nodes where k>0
41 and node_created>now()-interval 20 day;
42
43 -- select * from tmp_table order by k_weight desc;
44 end//
45 delimiter ;
This page took 0.354445 seconds and 4 git commands to generate.