Untitled query #46396

SQL

import reports

class report(reports.report):
    def get_title(self):
        return 'Blank single-author pages'

    def get_preamble_template(self):
        return 'Blank pages with a single author; data as of <onlyinclude>%s</onlyinclude>.'

    def get_table_columns(self):
        return ['Page']

    def get_table_rows(self, conn):
        cursor = conn.cursor()
        cursor.execute('''
        /* blankpages.py SLOW_OK */
        SELECT
          page_namespace,
          CONVERT(page_title USING utf8)
        FROM page
        WHERE page_len = 0
        AND page_namespace <> 8
        AND (page_namespace NOT IN (2, 3) OR page_title RLIKE '^(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)$')
        AND (SELECT
               COUNT(DISTINCT rev_user_text)
             FROM revision
             WHERE rev_page = page_id) = 1
        LIMIT 2000;
        ''')

        for page_namespace, page_title in cursor:
            if page_namespace in (6, 14):
                page_title = u'{{plh|1={{subst:ns:%s}}:%s}}' % (page_namespace, page_title)
            elif page_namespace == 0:
                page_title = u'{{plh|1=%s}}' % (page_title)
            else:
                page_title = u'{{plh|1={{subst:ns:%s}}:%s}}' % (page_namespace, page_title)
            yield [page_title]

        cursor.close()
SQL

Checking query status...
