Wikipedia:Most-wanted articles/wantedness.sql
Appearance
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' );