1241a428 |
1 | drop procedure if exists k_neurons; |
2 | drop function if exists k_get_node_weigth; |
1241a428 |
3 | delimiter // |
1241a428 |
4 | create function k_get_node_weigth (node INT, user INT) returns DOUBLE |
5 | BEGIN |
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 |
b17a1e9b |
23 | - all nodes between themselves (safe?) XXX removed |
1241a428 |
24 | */ |
b17a1e9b |
25 | select /*node_weight,*/node_creator into /*n_weight,*/n_owner from nodes where node_id=node2; |
1241a428 |
26 | select synapse_weight into s_weight from neurons where src=user and dst=node2; |
b17a1e9b |
27 | if found_rows() = 0 then set s_weight=1; end if; |
1241a428 |
28 | select synapse_weight into o_weight from neurons where src=user and dst=n_owner; |
b17a1e9b |
29 | if found_rows() = 0 then set o_weight=1; end if; |
30 | |
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;*/ |
1241a428 |
34 | |
b17a1e9b |
35 | set final = final * s_weight * o_weight;/* * n_weight;*/ |
1241a428 |
36 | END WHILE; |
1241a428 |
37 | RETURN final; |
38 | END// |
62eccaa3 |
39 | |
40 | /* Procedure itself with additional informations (parent name and owner name)*/ |
a1e1fcea |
41 | create procedure k_neurons ( IN user_id INT, IN day_int INT) |
9ed37a87 |
42 | BEGIN |
a1e1fcea |
43 | if day_int = NULL or day_int = 0 then set day_int=20; end if; |
62eccaa3 |
44 | select nodes.node_id, |
45 | nodes.node_name, |
46 | nodes.node_creator, |
47 | nodes.node_content, |
b17a1e9b |
48 | nodes.k, |
62eccaa3 |
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 |
52 | from nodes |
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; |
57 | |
9ed37a87 |
58 | END// |
1241a428 |
59 | delimiter ; |