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 ;