SQL
AخA
#interwiki file links
USE dewiki_p;
SELECT now() AS timestamp;/*
#interwiki links
(SELECT CONCAT ('# [[', REPLACE (wp.page_title, '_', ' '), ']]') AS wpage,
CONCAT ('→ [[:c:', REPLACE (iwl_title, '_', ' '), ']]') 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 REGEXP '^[Ff]ile:'
AND iwl_title NOT LIKE '%\%%'
) UNION*/
#broken external interwiki links
(SELECT CONCAT('# [[', REPLACE (wp.page_title, '_', ' '), ']]') AS wpage,
CONCAT('→ [[:c:', REPLACE ((SUBSTRING(REPLACE (el_to, '?uselang=de', ''), 36)), '_', ' '), ']]') 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 REGEXP '^https\:\/\/commons\.wikimedia\.org\/wiki\/[Ff]ile\:'
AND el_to NOT LIKE '%\%%' #temp
)
ORDER BY wpage
LIMIT 100;
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.