Wikipedia:Most-wanted articles/wantedness.sql

DELIMITER //

DROP FUNCTION IF EXISTS p50380g50491_rlrl_enwiki.wantedness //

CREATE FUNCTION p50380g50491_rlrl_enwiki.wantedness( namespace INT, title VARCHAR(255) )
  RETURNS INT
  READS SQL DATA
  NOT DETERMINISTIC
BEGIN  
   DECLARE done INT DEFAULT FALSE;
   DECLARE w, pns, t INT;
   DECLARE ptit VARCHAR(255);
   DECLARE links CURSOR FOR
      SELECT page_namespace, page_title
      FROM enwiki_p.pagelinks
      INNER JOIN enwiki_p.page ON pl_from = page_id
      WHERE pl_namespace = namespace
      AND   pl_title = title;
   DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

   SET w = 0;

   OPEN links;
   read_loop: LOOP
   
      FETCH links INTO pns, ptit;
      IF done THEN
         LEAVE read_loop;
      END IF;
      
      -- Count one for any link in namespace 0
      IF pns = 0 THEN
        SET w = w + 1;
      END IF;

      -- For each linked page, check how many times is has been transcluded into namespace 0
      SELECT count(*)
      INTO t
      FROM enwiki_p.templatelinks
      INNER JOIN enwiki_p.page ON tl_from = page_id
      WHERE page_namespace = 0
      AND  tl_namespace = pns
      AND  tl_title = ptit;
      
      SET w = w - t;
      
   END LOOP;
   CLOSE links;
  
   RETURN w;
END;   
//

DELIMITER ;


SELECT wantedness( 0, 'Throscidae' );