Fork of
~testing de sister projects broken
by Achim55
This query is marked as a draft
This query has been published
by Achim55.
SQL
AخA
#USE dewiki_p;
#USE dewikisource_p;
#USE dewikiquote_p;
#USE dewikivoyage_p;
#USE enwiki_p;
USE frwiki_p;
#USE itwiki_p;
#USE nlwiki_p;
#USE svwiki_p;
#USE commonswiki_p;
#USE metawiki_p;
#USE mediawikiwiki_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/Category:');
SET @lenm = LENGTH('https://meta.wikimedia.org/wiki/');
SET @lenmw = LENGTH('https://www.mediawiki.org/wiki/');
SET @lenspec = LENGTH('https://species.wikimedia.org/wiki/');
SET @c = '^$'; SET @pc = '^$'; SET @pspec = '^$'; #dummies
IF (@w = 'dewiki') THEN
SET @c = '(?:^Begriffsklärung$|^Schiffsname_der_United_States_Navy$)';
SET @pc = '(?:^Abgegangenes_Bauwerk$|^Agneskirche$|^Fährverbindung_Kiel–Göteborg$|^Fiat_500$|^HMS_Liverpool$|^Katharinenkirche$|^Liste_von_Schiffen_mit_dem_Namen_Rainbow_Warrior$|^Margarethenkirche$|^Maybach$|^Paraskevikirche$|^Salsa$|^Samsung_Galaxy_A5$)';
SET @pspec = '^Homonym_.Taxonomie.$';
ELSEIF (@w = 'dewikisource') THEN
SET @c = '(?:^Begriffsklärung$|^Personenunterscheidung$|^Textunterscheidung$)';
ELSEIF (@w = 'dewikiquote') THEN
SET @c = '^Aufteilung$';
ELSEIF (@w = 'dewikivoyage') THEN
SET @c = '^Wegweiser$';
ELSEIF (@w = 'enwiki') THEN
SET @c = '(?:[Dd]isambiguation_pages|^Disambiguation_categories$)';
SET @pc = '(?:^Amagi_Station$|^Football$|^Hull_to_York_Line$)';
SET @pspec = '^Homonym_.biology.$';
ELSEIF (@w = 'frwiki') THEN
SET @c = '^Homonymie';
ELSEIF (@w = 'itwiki') THEN
SET @c = '^Pagine_di_disambiguazione$';
SET @pc = "^Chiese_dedicate_a_sant'Agnese_martire$";
ELSEIF (@w = 'nlwiki') THEN
SET @c = '^Wikipedia:Doorverwijspagina$';
SET @pc = '^Aquaduct$';
ELSEIF (@w = 'svwiki') THEN
SET @c = '^Förgreningssidor$';
ELSEIF (@w = 'commonswiki') THEN
SET @c = '^Disambiguation';
ELSEIF (@w = 'metawiki') THEN
SET @c = '^Disambiguation';
ELSEIF (@w = 'mediawikiwiki') THEN
SET @c = '^Disambiguation_pages$';
ELSEIF (@w = 'specieswiki') THEN
SET @c = '^Disambiguation_pages$';
END IF;
SELECT now() AS timestamp;
##### Commons #####
#interwiki links pointing to disambiguations
(SELECT CONCAT('# [[', IF (wp.page_namespace = 14, ':{{ns:14}}:', ''), REPLACE (wp.page_title, '_', ' '), ']] ',
'→ [[c:', REPLACE (iwl_title, '_', ' '), ']]') AS wpage
FROM iwlinks
INNER JOIN page wp
ON wp.page_id = iwl_from
AND wp.page_namespace IN (0, 14)
AND wp.page_title NOT REGEXP @pc
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
AND ccl.cl_to = 'Disambiguation'
WHERE iwl_prefix IN ('c', 'commons')
AND iwl_title LIKE 'Category:%'
AND NOT EXISTS (SELECT 1 FROM categorylinks wcl
WHERE wcl.cl_from = wp.page_id
AND wcl.cl_to REGEXP @c) #from disambig to disambig is ok
)
UNION
#external links pointing to disambiguations
(SELECT CONCAT('# [[', IF (wp.page_namespace = 14, ':{{ns:14}}:', ''), REPLACE (wp.page_title, '_', ' '), ']] ',
'→ [[c:Category:', REPLACE (cp.page_title, '_', ' '), ']]') AS wpage
FROM externallinks
INNER JOIN page wp
ON wp.page_id = el_from
AND wp.page_namespace IN (0, 14)
AND wp.page_title NOT REGEXP @pc
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 + 1, @lenc))
AND cp.page_namespace = 14
INNER JOIN commonswiki_p.categorylinks ccl
ON ccl.cl_from = cp.page_id
AND ccl.cl_to = 'Disambiguation'
WHERE ((el_to LIKE 'https://commons.wikimedia.org/wiki/Category:%') OR (el_to LIKE 'http://commons.wikimedia.org/wiki/Category:%'))
AND NOT EXISTS (SELECT 1 FROM categorylinks wcl
WHERE wcl.cl_from = wp.page_id
AND wcl.cl_to REGEXP @c) #from disambig to disambig is ok
)
UNION
##### Meta #####
#interwiki links pointing to disambiguations
(SELECT CONCAT('# [[', IF (wp.page_namespace = 14, ':{{ns:14}}:', ''), REPLACE (wp.page_title, '_', ' '), ']] ',
'→ [[m:', REPLACE (iwl_title, '_', ' '), ']]') AS wpage
FROM iwlinks
INNER JOIN page wp
ON wp.page_id = iwl_from
AND wp.page_namespace IN (0, 14)
#AND wp.page_title NOT REGEXP @p
INNER JOIN metawiki_p.page cp
ON cp.page_title = iwl_title
AND cp.page_namespace = 0
INNER JOIN metawiki_p.categorylinks ccl
ON ccl.cl_from = cp.page_id
AND ccl.cl_to = 'Disambiguation'
WHERE iwl_prefix IN ('m', 'meta', 'metawikipedia')
AND iwl_title NOT LIKE 'Category:%'
AND NOT EXISTS (SELECT 1 FROM categorylinks wcl
WHERE wcl.cl_from = wp.page_id
AND wcl.cl_to REGEXP @c) #from disambig to disambig is ok
)
UNION
#external links pointing to disambiguations
(SELECT CONCAT('# [[', IF (wp.page_namespace = 14, ':{{ns:14}}:', ''), REPLACE (wp.page_title, '_', ' '), ']] ',
'→ [[m:', REPLACE (cp.page_title, '_', ' '), ']]') AS wpage
FROM externallinks
INNER JOIN page wp
ON wp.page_id = el_from
AND wp.page_namespace IN (0, 14)
#AND wp.page_title NOT REGEXP @p
INNER JOIN metawiki_p.page cp
ON cp.page_title = SUBSTRING(REPLACE (REPLACE (el_to, '#mw-subcategories', ''), CONCAT('?uselang=', @l), ''), IF ((LEFT (el_to, 5) = 'https'), @lenm + 1, @lenm))
AND cp.page_namespace = 0
INNER JOIN metawiki_p.categorylinks ccl
ON ccl.cl_from = cp.page_id
AND ccl.cl_to = 'Disambiguation'
WHERE ((el_to LIKE 'https://meta.wikimedia.org/wiki/%') OR (el_to LIKE 'http://meta.wikimedia.org/wiki/%')) #faster than regexp
AND el_to NOT LIKE '%//meta.wikimedia.org/wiki/Category:%'
AND NOT EXISTS (SELECT 1 FROM categorylinks wcl
WHERE wcl.cl_from = wp.page_id
AND wcl.cl_to REGEXP @c) #from disambig to disambig is ok
)
UNION
##### MediaWiki #####
#interwiki links pointing to disambiguations
(SELECT CONCAT('# [[', IF (wp.page_namespace = 14, ':{{ns:14}}:', ''), REPLACE (wp.page_title, '_', ' '), ']] ',
'→ [[mw:', REPLACE (iwl_title, '_', ' '), ']]') AS wpage
FROM iwlinks
INNER JOIN page wp
ON wp.page_id = iwl_from
AND wp.page_namespace IN (0, 14)
#AND wp.page_title NOT REGEXP @p
INNER JOIN mediawikiwiki_p.page cp
ON cp.page_title = iwl_title
AND cp.page_namespace = 0
INNER JOIN mediawikiwiki_p.categorylinks ccl
ON ccl.cl_from = cp.page_id
AND ccl.cl_to = 'Disambiguation_pages'
WHERE iwl_prefix IN ('mw', 'mediawiki', 'mediawikiwiki')
AND iwl_title NOT LIKE 'Category:%'
AND NOT EXISTS (SELECT 1 FROM categorylinks wcl
WHERE wcl.cl_from = wp.page_id
AND wcl.cl_to REGEXP @c) #from disambig to disambig is ok
)
UNION
#external links pointing to disambiguations
(SELECT CONCAT('# [[', IF (wp.page_namespace = 14, ':{{ns:14}}:', ''), REPLACE (wp.page_title, '_', ' '), ']] ',
'→ [[mw:', REPLACE (cp.page_title, '_', ' '), ']]') AS wpage
FROM externallinks
INNER JOIN page wp
ON wp.page_id = el_from
AND wp.page_namespace IN (0, 14)
#AND wp.page_title NOT REGEXP @p
INNER JOIN mediawikiwiki_p.page cp
ON cp.page_title = SUBSTRING(REPLACE (REPLACE (el_to, '#mw-subcategories', ''), CONCAT('?uselang=', @l), ''), IF ((LEFT (el_to, 5) = 'https'), @lenmw + 1, @lenmw))
AND cp.page_namespace = 0
INNER JOIN mediawikiwiki_p.categorylinks ccl
ON ccl.cl_from = cp.page_id
AND ccl.cl_to = 'Disambiguation_pages'
WHERE ((el_to LIKE 'https://www.mediawiki.org/wiki/%') OR (el_to LIKE 'http://www.mediawiki.org/wiki/%')) #faster than regexp
AND el_to NOT LIKE '%//www.mediawiki.org/wiki/Category:%'
AND NOT EXISTS (SELECT 1 FROM categorylinks wcl
WHERE wcl.cl_from = wp.page_id
AND wcl.cl_to REGEXP @c) #from disambig to disambig is ok
)
UNION
##### Wikispecies #####
#interwiki links pointing to disambiguations
(SELECT CONCAT('# [[', IF (wp.page_namespace = 14, ':{{ns:14}}:', ''), REPLACE (wp.page_title, '_', ' '), ']] ',
'→ [[species:', REPLACE (iwl_title, '_', ' '), ']]') AS wpage
FROM iwlinks
INNER JOIN page wp
ON wp.page_id = iwl_from
AND wp.page_namespace IN (0, 14)
AND wp.page_title NOT REGEXP @pspec
INNER JOIN specieswiki_p.page cp
ON cp.page_title = iwl_title
AND cp.page_namespace = 0
INNER JOIN specieswiki_p.categorylinks ccl
ON ccl.cl_from = cp.page_id
AND ccl.cl_to = 'Disambiguation_pages'
WHERE iwl_prefix IN ('species', 'wikispecies')
AND iwl_title NOT LIKE 'Category:%'
AND NOT EXISTS (SELECT 1 FROM categorylinks wcl
WHERE wcl.cl_from = wp.page_id
AND wcl.cl_to REGEXP @c) #from disambig to disambig is ok
)
UNION
#external links pointing to disambiguations
(SELECT CONCAT('# [[', IF (wp.page_namespace = 14, ':{{ns:14}}:', ''), REPLACE (wp.page_title, '_', ' '), ']] ',
'→ [[species:', REPLACE (cp.page_title, '_', ' '), ']]') AS wpage
FROM externallinks
INNER JOIN page wp
ON wp.page_id = el_from
AND wp.page_namespace IN (0, 14)
AND wp.page_title NOT REGEXP @pspec
INNER JOIN specieswiki_p.page cp
ON cp.page_title = SUBSTRING(REPLACE (REPLACE (el_to, '#mw-subcategories', ''), CONCAT('?uselang=', @l), ''), IF ((LEFT (el_to, 5) = 'https'), @lenspec + 1, @lenspec))
AND cp.page_namespace = 0
INNER JOIN specieswiki_p.categorylinks ccl
ON ccl.cl_from = cp.page_id
AND ccl.cl_to = 'Disambiguation_pages'
WHERE ((el_to LIKE 'https://species.wikimedia.org/wiki/%') OR (el_to LIKE 'http://species.wikimedia.org/wiki/%')) #faster than regexp
AND el_to NOT LIKE '%//species.wikimedia.org/wiki/Category:%'
AND NOT EXISTS (SELECT 1 FROM categorylinks wcl
WHERE wcl.cl_from = wp.page_id
AND wcl.cl_to REGEXP @c) #from disambig to disambig is ok
)
ORDER BY wpage
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.