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' );