Intro

[edit]

Templates that are not redirects and that have titles ending in '-stub' that are not linked to from any category page that transcludes ((stub category)), ((regional stub category)) or ((parent-only stub category)). Links from redirects to the template are counted also. Only those templates with more than 50 transclusions (directly or via redirect) are listed.

Generated by TB (talk) 14:08, 7 March 2015 (UTC)[reply]

Note: Manual update completed February 24, 2022

List

[edit]

Rebuilding

[edit]

Mysql transcript to aid in regenerating: 

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

DROP TABLE IF EXISTS rep1;
DROP TABLE IF EXISTS rep2;

-- First, capture all potentially interesting stub templates
CREATE TABLE rep1 AS
SELECT page_title, page_is_redirect
FROM   enwiki_p.page
WHERE  page_namespace = 10 
AND    page_title LIKE '%-stub'
AND    page_is_redirect = 0;

-- Add in any templates that redirect to one of these.
ALTER TABLE rep1 ADD COLUMN redir varbinary(255);

INSERT INTO rep1
SELECT p.page_title, p.page_is_redirect, r.page_title
FROM   enwiki_p.page p
INNER JOIN enwiki_p.redirect ON rd_from = p.page_id
INNER JOIN rep1 r ON rd_namespace = 10 AND rd_title = r.page_title
WHERE  p.page_namespace = 10 
AND    p.page_is_redirect = 1
AND    r.page_is_redirect = 0;


-- Now count how many times each is transcluded
alter table rep1 add column trans int(8);

-- Process in chunks - repeat as necessary
UPDATE rep1
SET trans = (
  SELECT count(*)
  FROM   enwiki_p.templatelinks
  WHERE  tl_namespace = 10
  AND    tl_title = page_title )
WHERE trans IS NULL
LIMIT 5000;

alter table rep1 add index( redir );

-- Now we need to credit transclusions of redirects to
-- the targets of those redirects
UPDATE rep1 r1
INNER JOIN rep1 r2 ON r1.page_title = r2.redir	
SET r1.trans = r1.trans + r2.trans
WHERE r1.page_is_redirect = 0
AND   r2.page_is_redirect = 1;


-- Now find categories of interest
CREATE table rep2 AS
SELECT page_id, page_title 
FROM enwiki_p.page 
INNER JOIN enwiki_p.templatelinks ON tl_from = page_id 
WHERE tl_namespace = 10 
AND tl_title = "Stub_category" 
AND page_namespace = 14;

REPLACE INTO rep2
SELECT page_id, page_title 
FROM enwiki_p.page 
INNER JOIN enwiki_p.templatelinks ON tl_from = page_id 
WHERE tl_namespace = 10 
AND tl_title = "Parent-only_stub_category" 
AND page_namespace = 14;

REPLACE INTO rep2
SELECT page_id, page_title 
FROM enwiki_p.page 
INNER JOIN enwiki_p.templatelinks ON tl_from = page_id 
WHERE tl_namespace = 10 
AND tl_title = "Regional_stub_category" 
AND page_namespace = 14;

-- Index these
ALTER TABLE rep2 ADD INDEX( page_id );
ALTER TABLE rep1 ADD INDEX ( page_title );


-- Now count how many time each interesting template is linked from an interesting category
ALTER TABLE rep1 ADD COLUMN cats int(8);

-- Process in chunks - repeat as necessary
UPDATE rep1 r1
SET cats = (
  SELECT count(*)
  FROM enwiki_p.pagelinks l
  INNER JOIN rep2 r2 ON l.pl_from = r2.page_id
  WHERE l.pl_namespace = 10
  AND l.pl_title = r1.page_title )
WHERE Cats IS NULL
LIMIT 5000;

-- Now we need to credit any category links to redirects to
-- the targets of those redirects
UPDATE rep1 r1
INNER JOIN rep1 r2 ON r1.page_title = r2.redir	
SET r1.cats = r1.cats + r2.cats
WHERE r1.page_is_redirect = 0
AND   r2.page_is_redirect = 1;


-- Test the results
SELECT count(*) FROM rep1 WHERE page_is_redirect = 0 AND cats = 0 AND trans >= 60;

-- Generate some output
SELECT CONCAT( '*((tl|', rep1.page_title, ')) - ', trans, ' transclusions' )
FROM rep1
WHERE page_is_redirect = 0
AND cats = 0
AND trans >= 50;
Category