Fork of
Empty categories on Commons
by Achim55
This query is marked as a draft
This query has been published
by Achim55.
SQL
x
#USE stop;
USE commonswiki_p;
SELECT now() AS timestamp;
SELECT CONCAT('# [[:Category:', REPLACE(c.page_title,'_',' '), ']]',
IF(c.page_len < 5," '''blank'''",''),
IF(page_title LIKE '%_monuments_and_memorials', ' consider adding {{tl|Prospective category}} if it might be useful for WLM', ''))
FROM (SELECT * FROM categorylinks
RIGHT JOIN page
ON cl_to = page_title
WHERE cl_to IS NULL
AND page_namespace = 14
AND page_is_redirect = 0
AND page_title NOT LIKE 'ACC_containing%' #Chinese characters
AND page_title NOT LIKE 'BSicon/%'
AND page_title NOT LIKE 'Commons:%'
AND page_title NOT LIKE 'Collection_of_%'
AND page_title NOT LIKE 'FoP-%'
AND page_title NOT LIKE 'FOP-%'
AND page_title NOT LIKE 'Images_from_Wiki_Loves_%'
AND page_title NOT LIKE 'Images_%/Reports/%'
AND page_title NOT LIKE 'IMO_%'
AND page_title NOT LIKE 'Naturschutzgebiet%'
AND page_title NOT LIKE 'Motd_%'
AND page_title NOT LIKE 'NoFoP-%'
AND page_title NOT LIKE 'Potd_%'
AND page_title NOT LIKE 'RCE_suggested:%'
AND page_title NOT LIKE 'Uncategorized_%'
AND page_title NOT LIKE 'Unidentified_%'
AND page_title NOT LIKE 'User_%'
AND page_title NOT LIKE 'WikiProject%'
# AND page_title NOT LIKE '%_monuments_and_memorials' #temp
AND page_title NOT LIKE '%Berlin%' #temp
AND page_title NOT REGEXP '(check_needed|Expedition_Wikipedia|FOP_cases|requiring_review|to_be_categorised|to_be_checked|to_be_classified|unidentified)'
) AS c
LEFT JOIN (SELECT cl_from FROM categorylinks
WHERE cl_to IN ('Broken_category_redirects',
'Categories_with_authority_control_data',
'Category_redirects',
'Commons_maintenance_content',
'Disambiguation',
'Hidden_categories', #temp
'Images_for_cleanup',
'Interwiki_from_wikidata',
'No_more_recognized_taxon',
'NoUploads',
'Sites_of_Community_Importance_in_Spain_by_ID',
'Taxon_categories',
'Undeletion_requests',
'Unicode-CJK', #Chinese/Japanese/Korean characters
'UTC_offsets')
OR cl_to LIKE 'Biosphere_reserves_%'
OR cl_to LIKE 'Cultural_heritage_%'
OR cl_to LIKE 'FoP-%'
OR cl_to LIKE 'GLAM_%'
OR cl_to LIKE 'Images_from_Wiki_Loves_%'
OR cl_to LIKE 'Natural_heritage%'
OR cl_to LIKE 'Nature_reserves%'
OR cl_to LIKE 'NoFoP-%'
OR cl_to LIKE 'Protected_area%'
OR cl_to LIKE 'Protected_localities%'
OR cl_to LIKE 'Radical_%' #Chinese characters
OR cl_to LIKE 'User_categories%'
) AS cl
ON cl.cl_from = c.page_id
LEFT JOIN (SELECT tl_from FROM templatelinks
WHERE tl_namespace = 10
AND tl_title IN ('CatDevelop',
'Category_for_discussion',
'Creator_possible',
'Empty_category',
'Emptycat',
'EmptyCatGood',
'Fotothek_category_header',
'Global_maintenance_category',
'IMOcat',
'Local_maintenance_category',
'NoFoP',
'On_Wikidata',
'Prospective_category',
'Unresolved',
'WDPA',
'Wikidata')
) AS tl
ON tl.tl_from = c.page_id
WHERE cl.cl_from IS NULL
AND tl.tl_from IS NULL
#AND page_id NOT IN (SELECT r.rev_page FROM revision r WHERE r.rev_user IN (8018, 318877)) #Fridolin freudenfett, OTFW
AND ((c.page_len < 5) #blank page
OR (DATEDIFF(CURRENT_TIMESTAMP, (SELECT MAX(rev_timestamp) FROM revision WHERE rev_page = c.page_id)) > 274) #last edit >9 months
)
#ORDER BY c.page_title
LIMIT 400;
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.