Jump to content

User:DWalden (WMF)/IPInfo/Permission Testing Data

From mediawiki.org

Revisions

[edit]

This finds edits which have all the combinations (if present in the database) of:

  • Whether the editor is hidden or not
  • Whether the revision is suppressed or not
  • Whether the edit is anonymous (i.e. from an IP) or from a logged in user
  • Whether the user who edited is blocked or suppressed

It returns up to 5 edits for each of these combinations. 

WITH random_revisions AS
(SELECT foo.*, row_number() OVER(PARTITION BY `Archived?`, `Editor Suppressed`, `Editor Blocked`, `Editor Is IP`, `Editor Hidden`, `Revision Suppressed`, `Text Hidden`, `Comment Hidden` ORDER BY RAND()) AS random_sort FROM
(SELECT rev_id AS rev_id,
        FALSE AS "Archived?",
        CASE WHEN 1 & rev_deleted THEN "Yes" ELSE "No" END AS "Text Hidden",
        CASE WHEN 2 & rev_deleted THEN "Yes" ELSE "No" END AS "Comment Hidden",
        CASE WHEN 4 & rev_deleted THEN "Yes" ELSE "No" END AS "Editor Hidden",
        CASE WHEN 8 & rev_deleted THEN "Yes" ELSE "No" END AS "Revision Suppressed",
        actor_name AS Editor,
        CASE WHEN actor_name REGEXP "^[0-9]+\.[0-9]+\.[0-9]+\.[0-9]+$|^[0-9A-Z]+\:[0-9A-Z]+\:[0-9A-Z]+\:[0-9A-Z]+\:[0-9A-Z]+\:[0-9A-Z]+\:[0-9A-Z]+\:[0-9A-Z]+$" THEN "Yes" ELSE "No" END AS "Editor Is IP",
        CASE WHEN editor_block.ipb_id THEN "Yes" ELSE "No" END AS "Editor Blocked",
        CASE WHEN editor_block.ipb_deleted THEN "Yes" ELSE "No" END AS "Editor Suppressed"
FROM revision
INNER JOIN actor ON revision.rev_actor = actor.actor_id
LEFT JOIN ipblocks AS editor_block ON actor.actor_user = editor_block.ipb_user OR actor.actor_name = editor_block.ipb_address) AS foo)
SELECT * FROM random_revisions WHERE random_sort <= 5;

Archived revisions

[edit]
WITH random_revisions AS
(SELECT foo.*, row_number() OVER(PARTITION BY `Archived?`, `Editor Suppressed`, `Editor Blocked`, `Editor Is IP`, `Editor Hidden`, `Revision Suppressed`, `Text Hidden`, `Comment Hidden` ORDER BY RAND()) AS random_sort FROM
(SELECT ar_rev_id AS rev_id,
       TRUE AS "Archived?",
       CASE WHEN 1 & ar_deleted THEN "Yes" ELSE "No" END AS "Text Hidden",
       CASE WHEN 2 & ar_deleted THEN "Yes" ELSE "No" END AS "Comment Hidden",
       CASE WHEN 4 & ar_deleted THEN "Yes" ELSE "No" END AS "Editor Hidden",
       CASE WHEN 8 & ar_deleted THEN "Yes" ELSE "No" END AS "Revision Suppressed",
       actor_name AS Editor,
       CASE WHEN actor_name RLIKE "^[0-9]+\.[0-9]+\.[0-9]+\.[0-9]+$|^[0-9A-Z]+\:[0-9A-Z]+\:[0-9A-Z]+\:[0-9A-Z]+\:[0-9A-Z]+\:[0-9A-Z]+\:[0-9A-Z]+\:[0-9A-Z]+$" THEN "Yes" ELSE "No" END AS "Editor Is IP",
       CASE WHEN editor_block.ipb_id THEN "Yes" ELSE "No" END AS "Editor Blocked",
       CASE WHEN editor_block.ipb_deleted THEN "Yes" ELSE "No" END AS "Editor Suppressed"
FROM archive
INNER JOIN actor ON archive.ar_actor = actor.actor_id
LEFT JOIN ipblocks AS editor_block ON actor.actor_user = editor_block.ipb_user OR actor.actor_name = editor_block.ipb_address
) AS foo)
SELECT * FROM random_revisions WHERE random_sort <= 5;

Revisions and Archived revisions combined

[edit]
WITH random_revisions AS
(SELECT foo.*, row_number() OVER(PARTITION BY `Archived?`, `Editor Suppressed`, `Editor Blocked`, `Editor Is IP`, `Editor Hidden`, `Revision Suppressed`, `Text Hidden`, `Comment Hidden` ORDER BY RAND()) AS random_sort FROM
(SELECT rev_id AS rev_id,
        FALSE AS "Archived?",
        CASE WHEN 1 & rev_deleted THEN "Yes" ELSE "No" END AS "Text Hidden",
        CASE WHEN 2 & rev_deleted THEN "Yes" ELSE "No" END AS "Comment Hidden",
        CASE WHEN 4 & rev_deleted THEN "Yes" ELSE "No" END AS "Editor Hidden",
        CASE WHEN 8 & rev_deleted THEN "Yes" ELSE "No" END AS "Revision Suppressed",
        actor_name AS Editor,
        CASE WHEN actor_name REGEXP "^[0-9]+\.[0-9]+\.[0-9]+\.[0-9]+$|^[0-9A-Z]+\:[0-9A-Z]+\:[0-9A-Z]+\:[0-9A-Z]+\:[0-9A-Z]+\:[0-9A-Z]+\:[0-9A-Z]+\:[0-9A-Z]+$" THEN "Yes" ELSE "No" END AS "Editor Is IP",
        CASE WHEN editor_block.ipb_id THEN "Yes" ELSE "No" END AS "Editor Blocked",
        CASE WHEN editor_block.ipb_deleted THEN "Yes" ELSE "No" END AS "Editor Suppressed"
FROM revision
INNER JOIN actor ON revision.rev_actor = actor.actor_id
LEFT JOIN ipblocks AS editor_block ON actor.actor_user = editor_block.ipb_user OR actor.actor_name = editor_block.ipb_address
UNION
SELECT ar_rev_id AS rev_id,
       TRUE AS "Archived?",
       CASE WHEN 1 & ar_deleted THEN "Yes" ELSE "No" END AS "Text Hidden",
       CASE WHEN 2 & ar_deleted THEN "Yes" ELSE "No" END AS "Comment Hidden",
       CASE WHEN 4 & ar_deleted THEN "Yes" ELSE "No" END AS "Editor Hidden",
       CASE WHEN 8 & ar_deleted THEN "Yes" ELSE "No" END AS "Revision Suppressed",
       actor_name AS Editor,
       CASE WHEN actor_name REGEXP "^[0-9]+\.[0-9]+\.[0-9]+\.[0-9]+$|^[0-9A-Z]+\:[0-9A-Z]+\:[0-9A-Z]+\:[0-9A-Z]+\:[0-9A-Z]+\:[0-9A-Z]+\:[0-9A-Z]+\:[0-9A-Z]+$" THEN "Yes" ELSE "No" END AS "Editor Is IP",
       CASE WHEN editor_block.ipb_id THEN "Yes" ELSE "No" END AS "Editor Blocked",
       CASE WHEN editor_block.ipb_deleted THEN "Yes" ELSE "No" END AS "Editor Suppressed"
FROM archive
INNER JOIN actor ON archive.ar_actor = actor.actor_id
LEFT JOIN ipblocks AS editor_block ON actor.actor_user = editor_block.ipb_user OR actor.actor_name = editor_block.ipb_address
) AS foo)
SELECT * FROM random_revisions WHERE random_sort <= 5;

Logged actions

[edit]

This finds logged actions which have all the combinations (if present in the database) of:

  • Whether the target of the logged action is hidden or not
  • Whether the performer of the logged action is hidden or not
  • Whether the logged action is suppressed or not
  • Whether the performer or target are anonymous (i.e. from an IP) or are logged in users
  • Whether the performer or target is blocked or suppressed

It returns up to 5 edits for each of these combinations. 

WITH random_logs AS
(SELECT foo.*, row_number() OVER(PARTITION BY `Action`, `Performer Suppressed`, `Performer Blocked`, `Performer Is IP`, `Target Suppressed`, `Target Blocked`, `Target Is IP`, `Performer Hidden`, `Comment Hidden`, `Target Hidden`, `Log Suppressed` ORDER BY RAND()) AS random_sort FROM
(SELECT log_id,
        CASE WHEN 1 & log_deleted THEN "Yes" ELSE "No" END AS "Target Hidden",
        CASE WHEN 2 & log_deleted THEN "Yes" ELSE "No" END AS "Comment Hidden",
        CASE WHEN 4 & log_deleted THEN "Yes" ELSE "No" END AS "Performer Hidden",
        CASE WHEN 8 & log_deleted THEN "Yes" ELSE "No" END AS "Log Suppressed",
        actor_name AS Performer,
        CASE WHEN actor_name REGEXP "^[0-9]+\.[0-9]+\.[0-9]+\.[0-9]+$|^[0-9A-Z]+\:[0-9A-Z]+\:[0-9A-Z]+\:[0-9A-Z]+\:[0-9A-Z]+\:[0-9A-Z]+\:[0-9A-Z]+\:[0-9A-Z]+$" THEN "Yes" ELSE "No" END AS "Performer Is IP",
        CASE WHEN performer_block.ipb_id THEN "Yes" ELSE "No" END AS "Performer Blocked",
        CASE WHEN performer_block.ipb_deleted THEN "Yes" ELSE "No" END AS "Performer Suppressed",
        log_title AS Target,
        CASE WHEN log_title REGEXP "^[0-9]+\.[0-9]+\.[0-9]+\.[0-9]+$|^[0-9A-Z]+\:[0-9A-Z]+\:[0-9A-Z]+\:[0-9A-Z]+\:[0-9A-Z]+\:[0-9A-Z]+\:[0-9A-Z]+\:[0-9A-Z]+$" THEN "Yes" ELSE "No" END AS "Target Is IP",
        CASE WHEN target_block.ipb_id THEN "Yes" ELSE "No" END AS "Target Blocked",
        CASE WHEN target_block.ipb_deleted THEN "Yes" ELSE "No" END AS "Target Suppressed",
        log_type AS Action
FROM logging
INNER JOIN actor ON logging.log_actor = actor.actor_id
LEFT JOIN ipblocks AS performer_block ON actor.actor_user = performer_block.ipb_user
LEFT JOIN ipblocks AS target_block ON logging.log_title = target_block.ipb_address
) AS foo)
SELECT * FROM random_logs WHERE random_sort <= 5;
Retrieved from "https://www.mediawiki.org/w/index.php?title=User:DWalden_(WMF)/IPInfo/Permission_Testing_Data&oldid=5751516"