~Bad links from Commons to other wikis

Fork of Bad links from wikis to Commons categories: broken by Achim55

This query is marked as a draft This query has been published by Achim55.

work in progress

SQL

USE commonswiki_p;
SELECT now();
#empty links, no target given
(SELECT CONCAT('# [[', IF (page_namespace = 14, ':Category:', ''), REPLACE (page_title, '_', ' '), ']] ',
               '→ [[:', iwl_prefix, ':', REPLACE (iwl_title, '_', ' '), ']]') AS result
FROM iwlinks
INNER JOIN page
ON page_id = iwl_from
AND page_namespace IN (0, 14)
AND page_id NOT IN (SELECT p2.page_id FROM page p2 INNER JOIN templatelinks
                    ON tl_from = p2.page_id AND tl_from_namespace IN (0, 14)
                    WHERE tl_title = 'Lang-mp' AND tl_namespace = 10)
#whitelisting false positives:
AND page_id NOT IN (139945, #MediaWiki
                    885242, #SeaMonkey
                    194001, #Wikimedia local chapters
                  67772844, #Wikipedia/1
                  10309740, #Wikipedia/2.0/worksheet
                  33998312, #Category:Furry conventions
                   4422810, #Category:German Wikipedia main page images
                   4610149, #Category:Graphic Lab
                  48270890, #Category:Ladino Wikipedia logos
                  61994214, #Category:Legavolley
                  32798070, #Category:Low German language
                  50232142, #Category:Mappiam
                   1595549, #Category:Notice-mr
                  71291374, #Category:Photographs by A. E. (Ted) Hill
                  32063819, #Category:Photographs by John Hill
                  10253438, #Category:Picture of the day on the Karachay-Balkar Wikipedia
                   8365823, #Category:Picture of the day on the Ossetian Wikipedia
                   5315472, #Category:R setup
                  34803153, #Category:Ray Saintonge at Wikimania 2014
                  15097922, #Category:Screenshots of MediaWiki Upload Wizard
                  45353304, #Category:Screenshots of the Wikimedia Blog
                  54996748, #Category:Spoken Wikipedia - Macedonian
                  25545941, #Category:V/Line line symbols
                   7792932, #Category:Venetian language
                  46945602, #Category:Vietnamese Wikiquote screenshots
                   6845645) #Category:Wikiquote screenshots
AND page_title NOT LIKE 'Stamps\_of\_the\_Soviet\_Union,\_%'
AND page_title NOT LIKE 'User\_%'
AND page_title NOT LIKE 'Wikimania%'
AND page_title NOT LIKE 'Wikimedia%'
AND page_title NOT LIKE 'Wikipedia%'
AND page_title NOT LIKE 'Wikivoyage%'
WHERE iwl_title REGEXP '^ *$'
AND iwl_prefix NOT IN ('b', 'wikibooks', 'd', 'm', 'meta', 'species', 'wikispecies', 'wmf', 'wikimedia', 'foundation', 'oeis', 'phab', 'phabricator', 'wmbe', 'wmbr', 'wmit', 'wmru', 'wmua')
)/*
UNION
#broken interwikilinks
(SELECT CONCAT('# [[', IF (wp.page_namespace = 14, ':Category:', ''), REPLACE (wp.page_title, '_', ' '), ']] ',
               '→ [[:c:', REPLACE (iwl_title, '_', ' '), ']]') AS wikipage
FROM iwlinks
INNER JOIN page wp
ON wp.page_id = iwl_from
AND wp.page_namespace IN (0, 14)
AND wp.page_is_redirect = 0
AND wp.page_title NOT REGEXP @p
WHERE iwl_prefix ='commons'
AND iwl_title LIKE 'Category:%'
AND NOT EXISTS (SELECT 1 FROM commonswiki_p.page cp
                WHERE cp.page_title = SUBSTRING(iwl_title, 10)
                AND cp.page_namespace = 14)
)
UNION
#broken external interwiki links
(SELECT CONCAT('# [[', IF (wp.page_namespace = 14, ':Category:', ''), REPLACE (wp.page_title, '_', ' '), ']] ',
               '→ [[:c:', REPLACE ((SUBSTRING(REPLACE (REPLACE (el_to, '#mw-subcategories', ''), CONCAT('?uselang=', @l), ''), 36)), '_', ' '), ']]') AS wikipage
FROM externallinks
INNER JOIN page wp
ON wp.page_id = el_from
AND wp.page_namespace IN (0, 14)
AND wp.page_is_redirect = 0
AND wp.page_title NOT REGEXP @p
WHERE el_to LIKE 'https://commons.wikimedia.org/wiki/Category:%'
AND el_to NOT LIKE '%\%%' #temp
AND NOT EXISTS (SELECT 1 FROM commonswiki_p.page cp
                WHERE cp.page_namespace = 14
                AND cp.page_title = SUBSTRING(REPLACE (REPLACE (el_to, '#mw-subcategories', ''), CONCAT('?uselang=', @l), ''), 45)
               )
)*/
#ORDER BY wikipage
LIMIT 1000;

By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.

Submit Query Stop Query

All SQL code is licensed under CC0 License.

SQL

Checking query status...