Skip to content

Instantly share code, notes, and snippets.

What would you like to do?
A bash script which connects to the database and generates a report on the latest activity dates for every bureaucrat across every Wikimedia wiki.
#!/usr/bin/env bash
# Retrieves the dates of the latest edit and log action for every bureaucrat
# across every Wikimedia wiki. This generates a TSV dataset that can be imported
# into Google Fusion Tables or processed into another format.
# SQL which generates TSV data (with fields for Google Fusion)
wikiSql="SELECT dbname, server, lang, family, domain, size, is_closed FROM ORDER BY dbname"
scanSql="SELECT /* SLOW_OK */ *, GREATEST(last_edit, last_log) AS last_edit_or_log, COALESCE(DATE_FORMAT(last_edit, '%d/%m/%y %H:%i'), 'never') AS last_edit_us, COALESCE(DATE_FORMAT(last_log, '%d/%m/%y %H:%i'), 'never') AS last_log_us, COALESCE(DATE_FORMAT(GREATEST(last_edit, last_log), '%d/%m/%y %H:%i'), 'never') AS last_edit_or_log_us FROM (SELECT user_name, (SELECT COALESCE(MAX(rev_timestamp), 'never') FROM revision WHERE rev_user = user_id) AS last_edit, (SELECT COALESCE(MAX(log_timestamp), 'never') FROM logging_ts_alternative WHERE log_user = user_id AND log_type IN ('abusefilter', 'block', 'delete', 'protect', 'renameuser', 'rights')) AS last_log FROM user INNER JOIN user_groups ON user_id = ug_user AND ug_group = 'bureaucrat') AS raw"
# scan & generate TSV
echo "wiki language family domain size closed user last_edit last_log last_edit_or_log last_edit_us last_log_us last_edit_or_log_us" > ${target}
mysql -e "${wikiSql}" --host=sql-s1-rr --skip-column-names --comments | while read dbName server lang family domain size closed; do
echo "scanning ${dbName}..."
mysql -e "${scanSql}" --host=sql-s${server}-rr --skip-column-names --comments --database=${dbName} --delimiter=" " | while IFS=" " read userName lastEdit lastLog lastEditOrLog lastEditUS lastLogUS lastEditOrLogUS; do
entry="${dbName} ${lang} ${family} ${domain} ${size} ${closed} ${userName} ${lastEdit} ${lastLog} ${lastEditOrLog} ${lastEditUS} ${lastLogUS} ${lastEditOrLogUS}"
echo ${entry}
echo "${entry}" >> ${target}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment