1 drop procedure if exists k_neurons
;
2 drop function if exists k_get_node_weigth
;
4 create function k_get_node_weigth (node
INT, user INT) returns DOUBLE
6 declare node2
,len
,n_owner
,offset int;
7 declare final,n_weight
,o_weight
,s_weight
double;
8 declare vector
varchar(240);
10 select node_vector
into vector
from nodes
where node_id
= node
;
12 /* select k from nodes into final where node_id = node;*/
14 set len
= length(vector
);
17 /* XXX node length is hardcoded */
18 set node2
= substring(vector
,offset,8);
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?)
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
;
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;
33 set final = final * s_weight
* o_weight
* n_weight
;
38 /* Procedure itself with additional informations (parent name and owner name)*/
39 create procedure k_neurons ( IN user_id
INT, IN day_int
INT)
41 if day_int
= NULL or day_int
= 0 then set day_int
=20; end if;
46 nodes.node_parent
,(k_get_node_weigth(nodes.node_id
,user_id
)*nodes.k
) as weight_k
,
47 users.
login as creator_name
,
48 parent.node_name
as parent_name
50 left join users
on users.user_id
=nodes.node_creator
51 left join nodes
as parent
on nodes.node_parent
=parent.node_id
52 where nodes.k
>0 and nodes.node_created
>now()-interval day_int
day
53 order by weight_k
desc;
This page took 0.284609 seconds and 4 git commands to generate.