~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;
#SET @w = 'dewiki'; SET @pr = 'de'; SET @prs = @pr;
#SET @w = 'enwiki'; SET @pr = 'en'; SET @prs = @pr;
#SET @w = 'eswiki'; SET @pr = 'es'; SET @prs = @pr;
#SET @w = 'frwiki'; SET @pr = 'fr'; SET @prs = @pr;
#SET @w = 'nlwiki'; SET @pr = 'nl'; SET @prs = @pr;
#SET @w = 'ruwiki'; SET @pr = 'ru'; SET @prs = @pr;
#SET @l = (SELECT lang FROM meta_p.wiki WHERE dbname = CONCAT(@w, '_p'));
#SELECT now(), @w, @pr, @l;
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)
/*
AND page_title NOT REGEXP "(?:^Accueil|^Acuèlh|^Åejjiebielie|^Anasayfa|^Azala|^Bogga_Hore|^Calīxatl|^Ĉefpaĝo|^Cifapad"
"|^Degemer|^Destpêk|^Entamu|^Esileht|^Etusivu|^Fandraisana|^Faqja_kryesore|^Forsíða|^Forside|^Glavna_stran|^Glavna_stranica"
"|^Haadside|^Haaptsäit|^Hafan|^Halaman_Utama|^Haudsiede_Commons|^Hauptsaitn|^Hauptseite|^Hēafodtramet|^Hlavná_stránka|^Hlavní_strana"
"|^Hłowna_strona|^Hoofdpagina|^Hööftsiet|^Houptsyte|^Hovedside|^Hovudside|^Huvudsida|^Il-Paġna_prinċipali|^Kezdőlap|^Laman_Utama"
"|^Main_Page|^Main_page|^MediaWiki|^Mwanzo|^Ojúewé_Àkọ́kọ́|^Pache_picarte|^Paggena_prencepale|^Pàggina_principali|^Pagina_prima|^Pagina_principal"
"|^Página_principal|^Pàgina_principal|^Pagina_principală|^Pagina_principale|^Pàgina_printzipale|^Pagrindinis_puslapis|^Páhina_prencipal"
"|^Páigina_percipal|^Pajina_prinsipałe|^Pamuklat|^Pela_Seri|^Početna_strana|^Portada|^Portada_galega|^Portalada|^Príomhleathanach"
"|^Przodńo_zajta|^Qhapaq_p'anqa|^Sākumlapa|^Scots_Commons|^Simple_English|^Strona_główna|^Syahan_nga_Pakli|^Tàu-hiĕk|^Tepas|^Thâu-ia̍h"
"|^Thèu-ya̍p|^Trang_Chính|^Tuisblad|^Tungkaran_Tatambaian|^Unang_Pahina|^User_|^Veurblad|^Vöärblad|^Wikimedia|^Wikipedia"
"|Wikipedia_screenshots$|^Κύρια_Σελίδα|^Басты_бет|^БетӀераб_гьумер|^Галоўная_старонка|^Главна_страна|^Главна_страница"
"|^Головна_сторінка|^Заглавная_страница|^Каталог_|^Кутскон_бам|^Начална_страница|^Першая_старонка|^Сæйраг_фарс|^Саҳифаи_Аслӣ|^Саҳифаи_аслӣ"
"|^Գլխավոր_Էջ|^მთავარი_გვერდი|^メインページ"
"|مک صفحو|سرآسونٱ|^لومړی_مخ|^مُک صفحو|עמוד_ראשי|^ערשטע_זײַט|^الصفحة_الرئيسية|^دەستپێک|^صفحهٔ_اصلی|^صفحۂ_اول|^گت_ولگ|^لومړی_مخ^"
"|^गृह_पृष्ठ|^मुखपृष्ठ|^मुखपुटम्|^मुख्य_पन्ना|^मुख्यपृष्ठम्|^প্রধান_পাতা|^ਮੁੱਖ_ਸਫ਼ਾ|^મુખપૃષ્ઠ|^ପ୍ରଧାନ_ପୃଷ୍ଠା|^முதற்_பக்கம்|^మొదటి_పేజీ|^ಮುಖ್ಯ_ಪುಟ|^మొదటి_పేజీ"
"|^പ്രധാന_താൾ|^මුල්_පිටුව|^หน้าหลัก|^ဗဟိုစာမျက်နှာ|^ទំព័រដើម|^대문|^조선말|^첫페지|^卷首|^頭版|^首頁|^首页"
"|^SeaMonkey$|^Biblioteca_Pública_do_Estado_\(Rio_Grande_do_Sul\)$|^Catedral_Nueva_de_Salamanca_at_night$)"
*/
#WHERE iwl_prefix = @pr
#WHERE iwl_prefix IN ('de', 'es', 'nl', 'ru')
WHERE iwl_prefix NOT IN ('b', 'wikibooks', 'm', 'meta', 'species', 'wikispecies', 'wmf', 'wikimedia', 'foundation')
AND iwl_title REGEXP '^ *$'
)/*
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...