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 dewikiquote_p;
#USE dewikisource_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 @pm = '^$'; SET @pmw = '^$'; 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$|^Kratersee$|^Liste_von_Schiffen_mit_dem_Namen_Rainbow_Warrior$|^Margarethenkirche$|^Maybach$|^Paraskevikirche$|^Salsa$|^Samsung_Galaxy_A5$|^Säuresee$)';
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$|^Congo$|^Durbar_Square$|^Football$|^Hull_to_York_Line$)';
SET @pspec = '(?:^Adeno-associated_virus$|^Homonym_.biology.$)';
ELSEIF (@w = 'frwiki') THEN
SET @c = '^Homonymie';
SET @pc = 'Armorial_de_la_noblesse_britannique';
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$|^Ferrocarril_de_Sóller$|^Gasturbine$|^Guggenheim_.familie.$|^Halve_Maen_.schip.$|^Hoogstraat$|^Krafla$|^Krater$|^Kratermeer$|^Langerei$|^Limburg$|^Maybach$|^Nationaal_Museum_.Praag.$)';
SET @pspec = '(?:^Chorasus_subcaecus$|^Coxsackievirus$|^Dinotrema_latitergum$|^Duinvogelmuur$|^Grote_kaardebol$|^Hiv$|^Papovavirus$)';
ELSEIF (@w = 'svwiki') THEN
SET @c = '^Förgreningssidor$';
ELSEIF (@w = 'commonswiki') THEN
SET @c = '^Disambiguation';
SET @pm ='Book_management';
ELSEIF (@w = 'metawiki') THEN
SET @c = '^Disambiguation';
SET @pmw = '(?:^Glossary|^MediaWiki)';
ELSEIF (@w = 'mediawikiwiki') THEN
SET @c = '^Disambiguation_pages$';
ELSEIF (@w = 'specieswiki') THEN
SET @c = '^Disambiguation_pages$';
END IF;
SELECT now() AS timestamp;
#interwiki links to commons
(SELECT CONCAT('# [[', IF (wp.page_namespace = 14, ':{{ns:14}}:', ''), REPLACE (wp.page_title, '_', ' '), ']] ',
'• [{{fullurl:', IF (wp.page_namespace = 14, '{{ns:14}}:', ''), REPLACE (wp.page_title, '_', ' '), '|action=edit}} edit] ',
'→ [[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)
)
UNION
#external links to commons
(SELECT CONCAT('# [[', IF (wp.page_namespace = 14, ':{{ns:14}}:', ''), REPLACE (wp.page_title, '_', ' '), ']] ',
'• [{{fullurl:', IF (wp.page_namespace = 14, '{{ns:14}}:', ''), REPLACE (wp.page_title, '_', ' '), '|action=edit}} edit] ',
'→ [[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)
)
UNION
#interwiki links to meta
(SELECT CONCAT('# [[', IF (wp.page_namespace = 14, ':{{ns:14}}:', ''), REPLACE (wp.page_title, '_', ' '), ']] ',
'• [{{fullurl:', IF (wp.page_namespace = 14, '{{ns:14}}:', ''), REPLACE (wp.page_title, '_', ' '), '|action=edit}} edit] ',
'→ [[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 @pm
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)
)
UNION
#external links to meta
(SELECT CONCAT('# [[', IF (wp.page_namespace = 14, ':{{ns:14}}:', ''), REPLACE (wp.page_title, '_', ' '), ']] ',
'• [{{fullurl:', IF (wp.page_namespace = 14, '{{ns:14}}:', ''), REPLACE (wp.page_title, '_', ' '), '|action=edit}} edit] ',
'→ [[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 @pm
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/%'))
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)
)
UNION
#interwiki links to mediawiki
(SELECT CONCAT('# [[', IF (wp.page_namespace = 14, ':{{ns:14}}:', ''), REPLACE (wp.page_title, '_', ' '), ']] ',
'• [{{fullurl:', IF (wp.page_namespace = 14, '{{ns:14}}:', ''), REPLACE (wp.page_title, '_', ' '), '|action=edit}} edit] ',
'→ [[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 @pmw
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)
)
UNION
#external links to mediawiki
(SELECT CONCAT('# [[', IF (wp.page_namespace = 14, ':{{ns:14}}:', ''), REPLACE (wp.page_title, '_', ' '), ']] ',
'• [{{fullurl:', IF (wp.page_namespace = 14, '{{ns:14}}:', ''), REPLACE (wp.page_title, '_', ' '), '|action=edit}} edit] ',
'→ [[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 @pmw
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/%'))
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)
)
UNION
#interwiki links to wikispecies
(SELECT CONCAT('# [[', IF (wp.page_namespace = 14, ':{{ns:14}}:', ''), REPLACE (wp.page_title, '_', ' '), ']] ',
'• [{{fullurl:', IF (wp.page_namespace = 14, '{{ns:14}}:', ''), REPLACE (wp.page_title, '_', ' '), '|action=edit}} edit] ',
'→ [[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)
)
UNION
#external links to wikispecies
(SELECT CONCAT('# [[', IF (wp.page_namespace = 14, ':{{ns:14}}:', ''), REPLACE (wp.page_title, '_', ' '), ']] ',
'• [{{fullurl:', IF (wp.page_namespace = 14, '{{ns:14}}:', ''), REPLACE (wp.page_title, '_', ' '), '|action=edit}} edit] ',
'→ [[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/%'))
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)
)
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.