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?) XXX removed
  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                 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;
  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;*/
  34
  35                 set final = final * s_weight * o_weight;/* * n_weight;*/
  36         END WHILE;
  37         RETURN final;
  38 END//
  39
  40 /* Procedure itself with additional informations (parent name and owner name)*/
  41 create procedure k_neurons ( IN user_id INT, IN day_int INT)
  42 BEGIN
  43         if day_int = NULL or day_int = 0 then set day_int=20; end if;
  44         select  nodes.node_id,
  45                 nodes.node_name,
  46                 nodes.node_creator,
  47                 nodes.node_content,
  48                 nodes.k,
  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
  58 END//
  59 delimiter ;