Proposed suffrage, May'23 Arbpol amendment

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

Checks all users who've edited [[Wikipedia:Arbitration/Policy/Proposed amendment (May 2023)]] against arbcom election suffrage requirements, replacing the "November 1" and "October 1" dates as one and two months respectively prior to the edit making the vote live.

This check all users who've edited the page, not just those who've explicitly voted.  This is why it includes e.g. AnomieBot.  Going the other way, I suppose it wouldn't see any forged signatures in the vote list.

For [[User talk:Barkeep49/ARBPOL amendment sandbox#Voter qualifications]] circa 19 May 2023.

SQL

SET @ns = 4; -- Wikipedia
SET @title = 'Arbitration/Policy/Proposed amendment (May 2023)';
SET @start_time = 20230517110303;

SET @title = REPLACE(@title, ' ', '_');
SET @start_time = CAST(@start_time AS DATETIME);
SET @one_month_timestamp = DATE_FORMAT(@start_time - INTERVAL 1 MONTH, '%Y%m%d%H%i%s');
SET @one_year_timestamp = DATE_FORMAT(@start_time - INTERVAL 1 MONTH - INTERVAL 1 YEAR, '%Y%m%d%H%i%s');

-- DELIMITER |
BEGIN NOT ATOMIC
  DECLARE r INT DEFAULT 0;
  DECLARE a_id BIGINT(20) UNSIGNED;
  DECLARE a_user INT(10) UNSIGNED;
  DECLARE a_name VARBINARY(255);
  DECLARE errs VARCHAR(1024);

  l1: LOOP
    SET r = r + 1;
    SET errs = '';
    SET a_id = NULL;
    -- This select is run on every iteration.  (At least it's fast.)
    -- Normally we'd use a temp table, but we don't have the permissions.
    -- Failing that, we'd use a cursor, but mariadb disallows that outside of stored procedures
    -- and we don't have the permissions to create those either.
    SELECT actor_id, actor_name, actor_user
    INTO a_id, a_name, a_user
    FROM (SELECT actor_id, actor_name, actor_user, ROW_NUMBER() OVER (ORDER BY first_rev) AS rn
          FROM
          (SELECT actor_id, actor_name, actor_user, MIN(rev_timestamp) AS first_rev
           FROM revision
           JOIN page ON page_id = rev_page
           JOIN actor_revision ON actor_id = rev_actor
           WHERE page_namespace = @ns
             AND page_title = @title
           GROUP BY actor_id, actor_name, actor_user) sq1) sq2
    WHERE rn = r;
    IF a_id IS NULL THEN
      LEAVE l1;
    END IF;

    IF a_user IS NULL THEN
      SET errs = CONCAT(errs, '; unregistered');
    ELSE
      BEGIN NOT ATOMIC
        DECLARE reg DATETIME;
        SELECT CAST(user_registration AS DATETIME) INTO reg FROM user WHERE user_id = a_user;
        IF reg > @start_time - INTERVAL 2 MONTH THEN
          SET errs = CONCAT(errs, '; recent registration: ', reg);
        END IF;
      END;
    END IF;

    -- Performance here is why we can't just do one big query
    BEGIN NOT ATOMIC
      DECLARE edits_live, edits_del INT;

      SELECT COUNT(*)
      INTO edits_live
      FROM (SELECT 1
            FROM revision_userindex
            JOIN page ON page_id = rev_page
            WHERE page_namespace = 0
              AND rev_actor = a_id
              AND rev_timestamp <= @one_month_timestamp
            LIMIT 150) sq3;

      SELECT COUNT(*)
      INTO edits_del
      FROM (SELECT 1
            FROM archive_userindex
            WHERE ar_namespace = 0
              AND ar_actor = a_id
              AND ar_timestamp <= @one_month_timestamp
            LIMIT 150) sq4;

      IF edits_live + edits_del < 150 THEN
        SET errs = CONCAT(errs, '; mainspace edits: ', edits_live + edits_del);
      END IF;
    END;

    -- Performance here in one big query probably an issue too
    BEGIN NOT ATOMIC
      DECLARE edits_live INT;

      SELECT COUNT(*)
      INTO edits_live
      FROM (SELECT 1
            FROM revision_userindex
            JOIN page ON page_id = rev_page
            WHERE rev_actor = a_id
              AND rev_timestamp BETWEEN @one_year_timestamp AND @one_month_timestamp
            LIMIT 10) sq3;

      IF edits_live < 10 THEN
        SET errs = CONCAT(errs, '; recent live edits: ', edits_live);
      END IF;
    END;    

    -- For simplicity this won't see rangeblocks including unregistered editors, but they're disallowed above anyway
    IF EXISTS (SELECT 1 FROM ipblocks_ipindex WHERE ipb_address = a_name AND ipb_sitewide = 1) THEN
      SET errs = CONCAT(errs, '; blocked');
    END IF;

    IF EXISTS (SELECT 1 FROM user_groups WHERE ug_user = a_user AND ug_group = 'bot')
       OR EXISTS (SELECT 1
                  FROM categorylinks
                  JOIN page ON page_id = cl_from
                  WHERE cl_to = 'All_Wikipedia_bots'
                    AND page_namespace = 2
                    AND page_title = REPLACE(a_name, ' ', '_')) THEN
      SET errs = CONCAT(errs, '; bot');
    END IF;

    IF a_name RLIKE '[vV]anished|[rR]enamed' THEN
      SET errs = CONCAT(errs, '; vanished/renamed');
    END IF;

    IF errs != '' THEN
      SELECT a_name, SUBSTR(errs, 3) AS errs;
    END IF;
  END LOOP;
END -- |
-- DELIMITER ;

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...