Jump to content

Wikipedia:WikiProject Stub sorting/Uncatted stubs

From Wikipedia, the free encyclopedia

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;