/* Moves by non-autoconfirmed (Not worth querying, debatable whether it is actually a violation)

use enwiki_p;

select log_id, log_type, log_action, user_name, user_editcount, user_registration from logging

join actor on actor_id = log_actor join user on user_id=actor_user where log_type="move" 

and (user_editcount < 10 || user_registration > (DATE_FORMAT((NOW() - INTERVAL 4 DAY),'%Y%m%d%H%i%S'))) 

and not exists (select 1 from user_groups where ug_user=user_id and ug_group="confirmed")

and log_timestamp > "20190501010101" limit 100;

*/ 

/* Moves without redirect */

use enwiki_p;

set @by_date = "20240410010101";

select log_id, log_type, log_action, user_name, user_editcount, user_registration, log_params from logging

join actor_logging on actor_id = log_actor join user on user_id = actor_user join comment_logging on comment_id = log_comment_id 

where log_type="move" and log_params like '%"5::noredir";s:1:"1%' and log_timestamp > @by_date  

and comment_text not like "Automatically moved page while renaming the user%" and log_namespace != 828

and not (log_namespace=10 and log_title like '%.css')

and not exists (select 1 from user_groups where ug_user = actor_user and (ug_group="extendedmover" || ug_group="sysop" || ug_group="bot"));

/* Other common admin actions */

select log_id, log_timestamp, log_type, log_action, user_name, user_editcount, user_registration, log_params from logging

join actor_logging on actor_id = log_actor join user on user_id = actor_user

where log_type in ("protect","block","delete","contentmodel") and log_action not in ("move_prot","delete_redir","delete_redir2")

and not exists (select 1 from user_groups where ug_user = actor_user and ug_group="sysop") and log_timestamp > @by_date;

/* Edits to protected pages *

/* Semi-protection not worth querying and of debatable applicability */

/* Extended-confirmed protection */

select user_name, user_editcount, user_registration, rev_id, page_namespace, page_title from revision 

join page_restrictions on pr_page=rev_page join page on page_id=rev_page 

join actor_revision on rev_actor=actor_id join user on user_id=actor_user

where pr_type="edit" and pr_level="extendedconfirmed" and rev_timestamp > @by_date

and not exists (select 1 from user_groups where ug_user=user_id and (ug_group="extendedconfirmed" || ug_group="sysop" || ug_group="bot"))

and not exists (select 1 from logging_logindex where log_namespace=page_namespace 

                and log_title=page_title and log_type="protect" and log_timestamp > rev_timestamp);

/* Template protection */

select user_name, user_editcount, user_registration, rev_id, page_namespace, page_title from revision 

join page_restrictions on pr_page=rev_page join page on page_id=rev_page 

join actor_revision on rev_actor=actor_id join user on user_id=actor_user

where pr_type="edit" and pr_level="templateeditor" and rev_timestamp > @by_date

and not exists (select 1 from user_groups where ug_user=user_id and (ug_group="templateeditor" || ug_group="sysop"))

and not exists (select 1 from logging_logindex where log_namespace=page_namespace 

                and log_title=page_title and log_type="protect" and log_timestamp > rev_timestamp);

/* Full protection */

select user_name, user_editcount, user_registration, rev_id, page_namespace, page_title from revision 

join page_restrictions on pr_page=rev_page join page on page_id=rev_page 

join actor_revision on rev_actor=actor_id join user on user_id=actor_user

where pr_type="edit" and pr_level="sysop" and rev_timestamp > @by_date

and not exists (select 1 from user_groups where ug_user=user_id and ug_group="sysop")

and not exists (select 1 from logging_logindex where log_namespace=page_namespace 

                and log_title=page_title and log_type="protect" and log_timestamp > rev_timestamp);

/* MediaWiki namespace */

select user_name, user_editcount, user_registration, rev_id, page_namespace, page_title from revision 

join page on page_id=rev_page join actor_revision on rev_actor=actor_id join user on user_id=actor_user

where page_namespace=8 and rev_timestamp > @by_date

and not exists (select 1 from user_groups where ug_user=user_id and ug_group="sysop")

and page_title != "GrowthMentors.json";

/* Edits to site CSS/JS */

select user_name, user_editcount, user_registration, rev_id, page_namespace, page_title from revision 

join page on page_id=rev_page join actor_revision on rev_actor=actor_id join user on user_id=actor_user

where page_namespace=8 and (page_content_model="css" || page_content_model="javascript")

and rev_timestamp > @by_date and not exists (select 1 from user_groups where ug_user=user_id and ug_group="interface-admin");

/* Autopatrolled */

select page_title, user_name from pagetriage_page  join page on page_id=ptrp_page_id join user on user_id=ptrp_last_reviewed_by

where ptrp_reviewed=3 and page_namespace=0 and page_is_redirect=0

and not exists (select 1 from user_groups where ug_user=user_id and (ug_group="autoreviewer" or ug_group="bot"))

order by ptrp_last_reviewed_by desc;

/* Not at all related to global rights, but a paranoia check in a query I run sporadically */

select page_title, user_name from pagetriage_page  join page on page_id=ptrp_page_id join user on user_id=ptrp_last_reviewed_by

where ptrp_last_reviewed_by=(select user_id from user where user_name="KHarlan (WMF)");

/* Also not at all related to global rights, but fits in with the autopatrolled misuse check above */

select * from logging_userindex join page on page_namespace=log_namespace and page_title=log_title

where log_actor=(select actor_id from actor_logging where actor_name="'zinbot")

and page_is_redirect=0

and log_timestamp > @by_date

and not exists (select 1 from templatelinks join linktarget on lt_id=tl_target_id where 

                tl_from=page_id and ((lt_namespace=828 and lt_title="RfD") 

                or (lt_namespace=10 and lt_title in ( "Disambiguation", "Set_index_article","Surname") -- Technical a protocol violation,

                    -- but these are common enough and need no action

                   )))

and not exists (select 1 from logging_logindex  where logging_logindex.log_type="pagetriage-curation" 

                and logging_logindex.log_namespace=logging_userindex.log_namespace

                and logging_logindex.log_title = logging_userindex.log_title

                and logging_logindex.log_timestamp > logging_userindex.log_timestamp)

order by log_timestamp desc limit 10000;

/* And more random stuff to check sporadically 

select * from page where page_namespace = 0 and page_is_redirect=0 and page_title regexp "^[A-Z0-9_\\W]*_\\([^a-z]*\\)$" 

and page_title not regexp "\\(([0-9]+_)?AM|FM|EP|TV|[0-9]+\\)$" -- Exclude various broadcasting stations per WP:NCBC

and page_title not regexp "\\([0-9]+([–-][0-9]+)?" -- Date based or hull number disambiguators are fine

and page_title not like "%C/20%" -- Exclude comet names per [[WP:NCASTRO]]. I disagree with this convention

and page_title not like "%C/19%" -- Exclude comet names per [[WP:NCASTRO]]. I disagree with this convention

and page_title not regexp "^VK_[0-9]+" -- Exclude some German tanks

and page_title not regexp "\\(ZVV|TILO\\)" -- Exclude some train lines that could probably have better names but whatever

and not exists (select 1 from pagelinks where pl_from=74115810 and pl_namespace=0 and pl_title=page_title) 

 -- Exclude things I've manually judged to be OK

order by page_title desc limit 1000;

/* Rate-limit */

SELECT (SELECT COUNT(*) from logging_userindex l2 

    WHERE l2.log_timestamp >= l1.log_timestamp and l2.log_timestamp < (l1.log_timestamp+86400) and l2.log_actor=l1.log_actor

        and l2.log_type="newusers") as creation_count, 

actor_user, actor_name, l1.log_timestamp from logging l1 join actor_logging on log_actor=actor_id 

where l1.log_timestamp > @by_date and l1.log_type="newusers" 

and not exists (select 1 from user_groups where ug_user=actor_user and (ug_group="sysop" || ug_group="accountcreator" || ug_group="eventcoordinator"))

and (SELECT COUNT(*) from logging_userindex l2 

    WHERE l2.log_timestamp >= l1.log_timestamp and l2.log_timestamp < (l1.log_timestamp+86400) and l2.log_actor=l1.log_actor

        and l2.log_type="newusers") > 6;

/* XXX there are a few more ratelimits, but some are unlogged, and I don't consider it within scope of this project to 

call out users for doing something as trivial as editing or moving slightly too fast, so only focusing on the 

4 accounts/day limit" */