wwwroot/backend/mysql/functions.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 node2,len,n_owner,offset int;
   7         declare final,n_weight,o_weight,s_weight double;
   8         declare vector varchar(240);
   9
  10         select node_vector into vector from nodes where node_id = node;
  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
  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?)
  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
  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;
  32
  33                 set final = final * s_weight * o_weight * n_weight;
  34         END WHILE;
  35         RETURN final;
  36 END//
  37
  38 /* Procedure itself with additional informations (parent name and owner name)*/
  39 create procedure k_neurons ( IN user_id INT, IN day_int INT)
  40 BEGIN
  41         if day_int = NULL or day_int = 0 then set day_int=20; end if;
  42         select  nodes.node_id,
  43                 nodes.node_name,
  44                 nodes.node_creator,
  45                 nodes.node_content,
  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
  49         from nodes
  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;
  54
  55 END//
  56 delimiter ;