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?) XXX removed
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 if found_rows() = 0 then set s_weight
=1; end if;
28 select synapse_weight
into o_weight
from neurons
where src
=user and dst
=n_owner
;
29 if found_rows() = 0 then set o_weight
=1; end if;
31 /* if o_weight = NULL or o_weight=0 then set o_weight=1; end if;
32 if s_weight = NULL or s_weight=0 then set s_weight=1; end if;*/
33 /*if n_weight = NULL or n_weight=0 then set n_weight=1; end if;*/
35 set final = final * s_weight
* o_weight
;/* * n_weight;*/
40 /* Procedure itself with additional informations (parent name and owner name)*/
41 create procedure k_neurons ( IN user_id
INT, IN day_int
INT)
43 if day_int
= NULL or day_int
= 0 then set day_int
=20; end if;
49 nodes.node_parent
,(k_get_node_weigth(nodes.node_id
,user_id
)*nodes.k
) as weight_k
,
50 users.
login as creator_name
,
51 parent.node_name
as parent_name
53 left join users
on users.user_id
=nodes.node_creator
54 left join nodes
as parent
on nodes.node_parent
=parent.node_id
55 where nodes.k
>0 and nodes.node_created
>now()-interval day_int
day
56 order by weight_k
desc;
This page took 0.275634 seconds and 4 git commands to generate.