Post-10/2009 move protections, main ns, !sysop edit prot

Fork of Post-10/2009 move protections by Cryptic

This query is marked as a draft This query has been published by Cryptic.

Currently move-protected pages that have had a protection-related log entry since 7 October 2009, restricted to pages currently in the main namespace and not currently edit-protected sysop.

There's no hard guarantee that the most recent protection log entry is related to the move protection, and it's hard to filter for that specifically.

See query 54423 for move protections before 7 October 2009.

For [[WP:RAQ#Indefinite move-protections placed long ago on articles]] circa 27 April 2021.

SQL

SELECT log_title,
       pr1.pr_expiry AS 'Move expiry',
       pr2.pr_level AS 'Edit protection',
       page_is_redirect,
       log_timestamp,
       log_action,
       actor_name,
       comment_text
FROM logging
LEFT JOIN actor_logging ON actor_id = log_actor
LEFT JOIN comment_logging ON comment_id = log_comment_id
LEFT JOIN page_restrictions AS pr1 ON pr1.pr_page = log_page AND pr1.pr_type = 'move'
LEFT JOIN page_restrictions AS pr2 ON pr2.pr_page = log_page AND pr2.pr_type = 'edit'
LEFT JOIN page ON page_id = log_page
WHERE log_id IN (SELECT MAX(log_id)
                 FROM page
                 JOIN page_restrictions AS pr3 ON pr3.pr_page = page_id
                 JOIN logging_logindex ON log_page = page_id AND log_type = 'protect' AND log_action != 'unprotect'
                 WHERE pr3.pr_type = 'move'
                   AND pr3.pr_level = 'sysop'
                 GROUP BY log_page)
  AND pr1.pr_level = 'sysop'
  AND log_namespace = 0
  AND COALESCE(pr2.pr_level, '') != 'sysop';

AخA
 
SELECT log_title,       pr1.pr_expiry AS 'Move expiry',       pr2.pr_level AS 'Edit protection',       page_is_redirect,       log_timestamp,       log_action,       actor_name,       comment_textFROM loggingLEFT JOIN actor_logging ON actor_id = log_actorLEFT JOIN comment_logging ON comment_id = log_comment_idLEFT JOIN page_restrictions AS pr1 ON pr1.pr_page = log_page AND pr1.pr_type = 'move'LEFT JOIN page_restrictions AS pr2 ON pr2.pr_page = log_page AND pr2.pr_type = 'edit'LEFT JOIN page ON page_id = log_pageWHERE log_id IN (SELECT MAX(log_id)                 FROM page                 JOIN page_restrictions AS pr3 ON pr3.pr_page = page_id                 JOIN logging_logindex ON log_page = page_id AND log_type = 'protect' AND log_action != 'unprotect'                 WHERE pr3.pr_type = 'move'                   AND pr3.pr_level = 'sysop'                 GROUP BY log_page)  AND pr1.pr_level = 'sysop'  AND log_namespace = 0  AND COALESCE(pr2.pr_level, '') != 'sysop';

By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.

Submit Query Stop Query

All SQL code is licensed under CC0 License.

SQL

Checking query status...