SQL
AخA
#USE dewiki_p;
#USE dewikisource_p;
#USE dewikivoyage_p;
#USE dewiktionary_p;
#USE enwiki_p;
#USE frwiki_p;
#USE itwiki_p;
USE nlwiki_p;
#USE specieswiki_p;
SET @w = LEFT (DATABASE(), LENGTH(DATABASE()) - 2); SET @l = (SELECT lang FROM meta_p.wiki WHERE dbname = @w);
SET @p = '^$'; #dummy
IF (@w = 'dewikivoyage') THEN
SET @p = '^Dateien:Nach_Commons_verschieben_';
ELSEIF (@w = 'enwiki') THEN
SET @p = '(?:^Convert_to_SVG_and_copy_to_Wikimedia_Commons$|^Copy_to_Wikimedia_Commons$|^PD-India_images_with_URAA-restored_copyright$|^Wikimedia_Commons_license_reviewers$|Wikipedia_files_with_.+_name_on_Wikimedia_Commons|^Wikipedia_requested_videos$|^Wikipedia_requests_related_to_images$)';
ELSEIF (@w = 'frwiki') THEN
SET @p = '^[0-9]{4}';
ELSEIF (@w = 'nlwiki') THEN
SET @p = 'Wikipedia:Commonscat_';
END IF;
SELECT now(), @w, @l AS timestamp;
#empty links, no target given
#/* species, it:wp, s:de
(SELECT CONCAT('# [[', IF (wp.page_namespace = 14, ':{{ns:14}}:', ''), 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_title NOT REGEXP @p
WHERE iwl_prefix IN ('c', 'commons')
AND iwl_title REGEXP '^ *$'
)
UNION
#*/
#broken interwikilinks
(SELECT CONCAT('# [[', IF (wp.page_namespace = 14, ':{{ns:14}}:', ''), REPLACE (wp.page_title, '_', ' '), ']] ',
'→ [[c:', REPLACE (iwl_title, '_', ' '), ']]',
IF (EXISTS (SELECT 1 FROM commonswiki_p.page cp
WHERE cp.page_namespace = 14
AND cp.page_title = iwl_title), '?cat?', '')) 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 IN ('c', 'commons')
AND iwl_title NOT LIKE '%:%' #temp
AND iwl_title NOT LIKE 'Atlas_of_%' #temp, special fix needed
#species, it:wp, s:de
#AND iwl_title NOT LIKE '' #species, it:wp, s:de
AND NOT EXISTS (SELECT 1 FROM commonswiki_p.page cp
WHERE cp.page_title = iwl_title
AND cp.page_namespace = 0)
)
UNION
#broken external interwiki links
(SELECT CONCAT('# [[', IF (wp.page_namespace = 14, ':{{ns:14}}:', ''), REPLACE (wp.page_title, '_', ' '), ']] ',
'→ [[c:', REPLACE ((SUBSTRING(REPLACE (el_to, CONCAT('?uselang=', @l), ''), 36)), '_', ' '), ']]',
IF (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), ''), 36))), '?cat?', '')) 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/%'
#species, it:wp, s:de
#AND (REPLACE (REPLACE (el_to, '#mw-subcategories', ''), CONCAT('?uselang=', @l), '')) NOT LIKE 'https://commons.wikimedia.org/wiki/' #species, it:wp, s:de
AND el_to NOT LIKE 'https://commons.wikimedia.org/wiki/%:%' #temp
AND el_to NOT LIKE '%#%' #temp
AND el_to NOT LIKE 'https://commons.wikimedia.org/wiki/Atlas_of_%' #temp, special fix needed
AND el_to NOT LIKE '%\%%' #temp
AND NOT EXISTS (SELECT 1 FROM commonswiki_p.page cp
WHERE cp.page_namespace = 0
AND cp.page_title = SUBSTRING(REPLACE (REPLACE (el_to, '#mw-subcategories', ''), CONCAT('?uselang=', @l), ''), 36)
)
)
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.
All SQL code is licensed under CC0 License.