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