USE tewiki_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

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

WHERE iwl_prefix ='commons'

AND iwl_title LIKE 'Category:%'

AND (cp.page_is_redirect = 1 OR ccl.cl_to = 'Category_redirects')

)

UNION

#external interwiki links

(SELECT CONCAT('# [[', REPLACE (wp.page_title, '_', ' '), ']]') AS wpage,

        CONCAT('→ [[:c:Category:', REPLACE (cp.page_title, '_', ' '), ']]') AS commonscat

FROM externallinks

INNER JOIN page wp

ON wp.page_id = el_from

AND wp.page_namespace = 0

INNER JOIN commonswiki_p.page cp

ON cp.page_title = SUBSTRING(REPLACE (el_to, '?uselang=de', ''), 45)

AND cp.page_namespace = 14

INNER JOIN commonswiki_p.categorylinks ccl

ON ccl.cl_from = cp.page_id

WHERE el_to LIKE 'https://commons.wikimedia.org/wiki/Category:%'

AND (cp.page_is_redirect = 1 OR ccl.cl_to = 'Category_redirects')

)

ORDER BY wpage

LIMIT 1000;