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.

Checking query status...