Fork of
Bad links from wikis to m, mw: redirects
by Achim55
This query is marked as a draft
This query has been published
by Achim55.
SQL
AخA
#USE dewiki_p;
#USE dewikiquote_p;
#USE dewikisource_p;
#USE dewikivoyage_p;
#USE enwiki_p;
#USE frwiki_p;
#USE itwiki_p;
USE nlwiki_p;
#USE metawiki_p;
#USE specieswiki_p;
SET @w = LEFT (DATABASE(), LENGTH(DATABASE()) - 2); SET @l = (SELECT lang FROM meta_p.wiki WHERE dbname = @w);
SET @lenc = LENGTH('https://commons.wikimedia.org/wiki/');
#white lists
SET @pc = '^$'; #dummy
IF (@w = 'dewiki') THEN
SET @pc = '(?:\:|#|^HkDE-wt_Legende$|^Angus_Young$)';
ELSEIF (@w = 'enwiki') THEN
SET @pc = '(^Atlas_of_|^Angus_Young$)';
END IF;
SELECT now() AS timestamp;
#interwiki links to category redirects
(SELECT CONCAT('# [[', IF (wp.page_namespace = 14, ':{{ns:14}}:', ''), 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)
INNER JOIN commonswiki_p.page cp
ON cp.page_title = SUBSTRING(iwl_title, 10)
AND cp.page_namespace = 14
INNER JOIN commonswiki_p.categorylinks ccl
ON ccl.cl_from = cp.page_id
WHERE iwl_prefix IN ('c', 'commons')
AND iwl_title LIKE 'Category:%'
AND (cp.page_is_redirect = 1 OR ccl.cl_to = 'Category_redirects')
)
UNION
#interwiki links to gallery redirects
(SELECT CONCAT('# [[', IF (wp.page_namespace = 14, ':{{ns:14}}:', ''), 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)
INNER JOIN commonswiki_p.page cp
ON cp.page_title = iwl_title
AND cp.page_namespace = 0
AND cp.page_is_redirect = 1
AND cp.page_title NOT REGEXP @pc
WHERE iwl_prefix IN ('c', 'commons')
AND iwl_title NOT LIKE 'Category:%'
)
UNION
#external links to category redirects
(SELECT CONCAT('# [[', IF (wp.page_namespace = 14, ':{{ns:14}}:', ''), REPLACE (wp.page_title, '_', ' '), ']] ',
'→ [[c:Category:', REPLACE (cp.page_title, '_', ' '), ']]') AS wikipage
FROM externallinks
INNER JOIN page wp
ON wp.page_id = el_from
AND wp.page_namespace IN (0, 14)
INNER JOIN commonswiki_p.page cp
ON cp.page_title = SUBSTRING(REPLACE (REPLACE (el_to, '#mw-subcategories', ''), CONCAT('?uselang=', @l), ''), IF ((LEFT (el_to, 5) = 'https'), @lenc + 10, @lenc + 9))
AND cp.page_namespace = 14
INNER JOIN commonswiki_p.categorylinks ccl
ON ccl.cl_from = cp.page_id
WHERE ((el_to LIKE 'https://commons.wikimedia.org/wiki/Category:%') OR (el_to LIKE 'http://commons.wikimedia.org/wiki/Category:%'))
AND el_to NOT LIKE '%\%%' #temp
AND (cp.page_is_redirect = 1 OR ccl.cl_to = 'Category_redirects')
)
UNION
#external links to gallery redirects
(SELECT CONCAT('# [[', IF (wp.page_namespace = 14, ':{{ns:14}}:', ''), REPLACE (wp.page_title, '_', ' '), ']] ',
'→ [[c:', REPLACE (cp.page_title, '_', ' '), ']]') AS wikipage
FROM externallinks
INNER JOIN page wp
ON wp.page_id = el_from
AND wp.page_namespace IN (0, 14)
INNER JOIN commonswiki_p.page cp
ON cp.page_title = SUBSTRING(REPLACE (el_to, CONCAT('?uselang=', @l), ''), IF ((LEFT (el_to, 5) = 'https'), @lenc + 1, @lenc))
AND cp.page_namespace = 0
AND cp.page_is_redirect = 1
AND cp.page_title NOT REGEXP @pc
WHERE ((el_to LIKE 'https://commons.wikimedia.org/wiki/%') OR (el_to LIKE 'http://commons.wikimedia.org/wiki/%'))
AND el_to NOT LIKE '%//commons.wikimedia.org/wiki/Category:%'
AND el_to NOT LIKE '%\%%' #temp
)
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.
All SQL code is licensed under CC0 License.