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 stop;
USE dewiki_p;
SELECT now() AS timestamp;
#empty links, no target given
(SELECT CONCAT ('# [[', REPLACE (wp.page_title, '_', ' '), ']]') AS wpage,
CONCAT('→ [[:c:', REPLACE (iwl_title, '_', ' '), "]] → '''leer'''") AS commonscat
FROM iwlinks
INNER JOIN page wp
ON wp.page_id = iwl_from
AND wp.page_namespace = 0
WHERE iwl_prefix ='commons'
AND iwl_title = 'Category:'
)
UNION
#broken interwikilinks
(SELECT CONCAT ('# [[', REPLACE (wp.page_title, '_', ' '), ']]') AS wpage,
CONCAT('→ [[:c:', REPLACE (iwl_title, '_', ' '), ']]',
IF (iwl_title LIKE 'Category:', " → '''leer'''", ''),
IF (iwl_title LIKE 'Category: %', " → '''Leerzeichen'''", ''),
IF (iwl_title REGEXP '^Category\:Q\d+$', " → '''Murks'''", ''),
IF (iwl_title REGEXP '\#', " → '''Murks'''", ''),
IF (iwl_title REGEXP '[\,\.\?\"\(]$', " → '''Murks'''", ''),
IF (iwl_title REGEXP '^Category\:[a-z]', " → '''Kleinbuchstabe'''", '')
) AS commonscat
FROM iwlinks
INNER JOIN page wp
ON wp.page_id = iwl_from
AND wp.page_namespace = 0
AND wp.page_is_redirect = 0
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('# [[', REPLACE (wp.page_title, '_', ' '), ']]') AS wpage,
CONCAT('→ [[:c:', REPLACE ((SUBSTRING(REPLACE (el_to, '?uselang=de', ''), 36)), '_', ' '), ']]',
IF (REPLACE (el_to, '?uselang=de', '') = 'https://commons.wikimedia.org/wiki/Category:', " → '''leer'''", ''),
IF (el_to = 'https://commons.wikimedia.org/wiki/Category:?uselang=de', " → '''leer'''", ''),
IF (el_to REGEXP '^https\:\/\/commons\.wikimedia\.org\/wiki\/Category\:Q\d+\?uselang\=de$', " → '''Murks'''", ''),
IF (el_to REGEXP '^https\:\/\/commons\.wikimedia\.org\/wiki\/Category\:.*\#', " → '''Murks'''", ''),
IF (el_to REGEXP '^https\:\/\/commons\.wikimedia\.org\/wiki\/Category\:[a-z]', " → '''Kleinbuchstabe'''", '')
) AS commonscat
FROM externallinks
INNER JOIN page wp
ON wp.page_id = el_from
AND wp.page_namespace = 0
AND wp.page_is_redirect = 0
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 (el_to, '?uselang=de', ''), 45)
)
)
ORDER BY wpage
LIMIT 3000;
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.