Untitled query #87005

SQL

#! /usr/bin/env python
# Public domain; MZMcBride; 2015

import sys
try:
    target_user = sys.argv[1]
except IndexError:
    target_user = 'Lugnuts'

import oursql

import settings

conn = oursql.connect(host=settings.host,
                      db=settings.dbname,
                      read_default_file='~/.my.cnf')
cursor = conn.cursor()

cursor.execute('''
/* verify-article-counts.py */
SELECT
  rev_user_text,
  SUM(CASE WHEN p1.page_is_redirect = 0 THEN 1 ELSE 0 END) AS notredir,
  SUM(CASE WHEN p1.page_is_redirect = 1 THEN 1 ELSE 0 END) AS redir,
  SUM(CASE WHEN p1.page_is_redirect = 0 THEN 1 ELSE 0 END) + 
  SUM(CASE WHEN p1.page_is_redirect = 1 THEN 1 ELSE 0 END) AS total
FROM revision_userindex
JOIN s51334__enwiki_first_page_revisions_p.page AS p2
ON rev_id = p2.page_first_revision_id
AND rev_user_text = ?
JOIN page AS p1
ON p1.page_id = p2.page_id
GROUP BY rev_user_text;
''', (target_user,))

cached_count = cursor.fetchall()
for i in cached_count:
    user_name = i[0]
    counts = [
        str(i[1]),
        str(i[2]),
        str(i[3])
    ]

print('Cached counts for {0}:   {1}'.format(
    user_name,
    '   '.join(counts)
))

cursor.execute('''
/* verify-article-counts.py */
SELECT
  r1.rev_user_text,
  SUM(CASE WHEN p1.page_is_redirect = 0 THEN 1 ELSE 0 END) AS notredir,
  SUM(CASE WHEN p1.page_is_redirect = 1 THEN 1 ELSE 0 END) AS redir,
  SUM(CASE WHEN p1.page_is_redirect = 0 THEN 1 ELSE 0 END) + 
  SUM(CASE WHEN p1.page_is_redirect = 1 THEN 1 ELSE 0 END) AS total
FROM revision_userindex AS r1
JOIN page AS p1
ON r1.rev_page = p1.page_id
WHERE r1.rev_user_text = ?
AND p1.page_namespace = 0
AND r1.rev_timestamp = (SELECT
                          r2.rev_timestamp
                        FROM revision_userindex AS r2
                        JOIN page AS p2
                        ON p2.page_id = r2.rev_page
                        WHERE r2.rev_page = r1.rev_page
                        ORDER BY r2.rev_timestamp ASC
                        LIMIT 1)
GROUP BY r1.rev_user_text;
''', (target_user,))

calced_count = cursor.fetchall()
for i in calced_count:
    user_name = i[0]
    counts = [
        str(i[1]),
        str(i[2]),
        str(i[3])
    ]

print('Calced counts for {0}:   {1}'.format(
    user_name,
    '   '.join(counts)
))

cursor.close()
conn.close()
SQL

Checking query status...
