SQL
AخA
#USE stopthisrun;
USE commonswiki_p;
SELECT CONCAT ('[[Category:', REPLACE (page_title,'_',' '), ']]') AS touchlist FROM page
INNER JOIN categorylinks ON cl_from = page_id
WHERE page_namespace = 14
AND page_is_redirect = 0
AND page_title NOT IN ('Disambiguation_galleries',
'Disambiguation_categories_of_churches_in_Sweden',
'Disambiguation_categories_of_populated_places',
'Disambiguation_categories_of_temples_in_Japan',
'Files_moved_from_de.wikipedia_to_Commons_requiring_review_-_No_timestamp_given',
'Non-empty_disambiguation_categories',
'Tagged_ambiguous')
AND page_title NOT LIKE 'Undelete_in%'
AND
(
(cl_to = 'Disambiguation' #cats from here
AND EXISTS (SELECT 1 FROM categorylinks #which are not empty
WHERE cl_to = page_title)
AND NOT EXISTS (SELECT 1 FROM categorylinks #and are not yet listed...
WHERE cl_from = page_id
AND cl_to = 'Non-empty_disambiguation_categories') #...here
)
OR
(cl_to = 'Non-empty_disambiguation_categories' #or cats from here
AND NOT EXISTS (SELECT 1 FROM categorylinks #which are already empty
WHERE cl_to = page_title)
)
OR
(cl_to = 'Non-empty_category_redirects' #or cats from here
AND NOT EXISTS (SELECT 1 FROM categorylinks #which are already empty
WHERE cl_to = page_title)
)
OR
(cl_to = 'Images_from_the_Geograph_British_Isles_project_needing_categories_by_grid_square' #or cats from here
AND NOT EXISTS (SELECT 1 FROM categorylinks #which are already empty
WHERE cl_to = page_title)
AND NOT EXISTS (SELECT 1 FROM categorylinks #and are not already listed...
WHERE cl_from = page_id
AND cl_to = 'Other_speedy_deletions') #...here
AND EXISTS (SELECT 1 FROM templatelinks #and are...
WHERE tl_from = page_id
AND tl_namespace = 2
AND tl_title = 'Avicennasis/emptycat') #...transcluded tagged speedy
)
OR
(cl_to = 'Files_moved_from_de.wikipedia_to_Commons_requiring_review' #or cats from here
AND NOT EXISTS (SELECT 1 FROM categorylinks #which are already empty
WHERE cl_to = page_title)
AND NOT EXISTS (SELECT 1 FROM categorylinks #and are not yet listed...
WHERE cl_from = page_id
AND cl_to = 'Other_speedy_deletions') #...here
AND EXISTS (SELECT 1 FROM templatelinks #and are...
WHERE tl_from = page_id
AND tl_namespace = 10
AND tl_title = 'BotMoveToCommonsHeader') #...transcluded tagged speedy
)
OR
(cl_to = 'OTRS_pending' #or cats from here
AND NOT EXISTS (SELECT 1 FROM categorylinks #which are already empty
WHERE cl_to = page_title)
AND NOT EXISTS (SELECT 1 FROM categorylinks #and are not yet listed...
WHERE cl_from = page_id
AND cl_to = 'Other_speedy_deletions') #...here
AND EXISTS (SELECT 1 FROM templatelinks #and are...
WHERE tl_from = page_id
AND tl_namespace = 10
AND tl_title = 'OTRS_pending_header') #...transcluded tagged speedy
)
OR
(cl_to IN ('Media_needing_categories_in_use_in_galleries',
'Media_needing_categories_requiring_human_attention') #or cats from here
AND NOT EXISTS (SELECT 1 FROM categorylinks #which are already empty
WHERE cl_to = page_title)
AND NOT EXISTS (SELECT 1 FROM categorylinks #and are not yet listed...
WHERE cl_from = page_id
AND cl_to = 'Other_speedy_deletions') #...here
AND EXISTS (SELECT 1 FROM templatelinks #and are...
WHERE tl_from = page_id
AND tl_namespace = 10
AND tl_title = 'UncategorizedHeader') #...transcluded tagged speedy
)
OR
(cl_to = 'Media_needing_categories_requiring_human_attention' #or cats from here
AND NOT EXISTS (SELECT 1 FROM categorylinks #which are already empty
WHERE cl_to = page_title)
AND NOT EXISTS (SELECT 1 FROM categorylinks #and are not already listed...
WHERE cl_from = page_id
AND cl_to = 'Other_speedy_deletions') #...here
)
OR cl_to = 'Cfd_requests_with_missing_discussion_page' #or from here precautionary
# OR cl_to = "ASI_monuments_with_known_ID's" #single run for moved cat
# OR cl_to = 'Category_pages_with_broken_file_links' #single run after template change
)
GROUP BY page_title
LIMIT 2000;
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.