def UpdateTrackingTables(eventname, b, projectname, timestamp, user):
subcursor = feedutils.GetCursor()
subcursor.execute(
'insert ignore into bug%s%s '
'(id, component, timestamp, '
'username) '
'values(%s, "%s", %s, "%s");' %
(eventname, VERSION, b.bug.id, projectname, timestamp, user))
summary = {'__total__': 0}
subcursor.execute('select * from bug%s%s where '
'username = "******" and date(timestamp) = %s '
'order by timestamp asc;' %
(eventname, VERSION, user, timestamp))
for triage in subcursor:
summary.setdefault(triage['component'], 0)
summary[triage['component']] += 1
summary['__total__'] += 1
subcursor.execute('delete from bug%ssummary%s where '
'username="******" and day=date(%s);' %
(eventname, VERSION, user, timestamp))
subcursor.execute('insert into bug%ssummary%s'
'(day, username, data, epoch) '
'values (date(%s), "%s", \'%s\', %d);' %
(eventname, VERSION, timestamp, user,
json.dumps(summary), int(time.time())))
subcursor.execute('commit;')
def FindDuplicates():
global DUPLICATES
cursor = feedutils.GetCursor()
cursor.execute('select * from bugs where duplicateof is not null;')
for row in cursor:
DUPLICATES[row['id']] = row['duplicateof']
def Execute(last_change, cmd_name, cmd, arg, cleanup=False):
actual_cmd = cmd % arg
print 'Executing: %s' % actual_cmd
cursor = feedutils.GetCursor()
try:
cursor.execute('create table commands_%s (arg varchar(500), '
'timestamp datetime, epoch int, result longtext, '
'primary key(arg, timestamp), index(timestamp), '
'index(epoch)) '
'engine=innodb;' % cmd_name)
except:
pass
cursor.execute('delete from commands_%s where arg="%s" and epoch < %s;' %
(cmd_name, arg, last_change))
print ' ... Cleaned up %d old cache entries' % cursor.rowcount
cursor.execute('commit;')
cursor.execute('select timestamp, epoch, result from commands_%s '
'where arg="%s" order by timestamp desc limit 1;' %
(cmd_name, arg))
if cursor.rowcount > 0:
row = cursor.fetchone()
if row['epoch'] > last_change:
print ' ... Using cached result'
return base64.decodestring(row['result']).split('\n')
p = subprocess.Popen(actual_cmd,
shell=True,
stdout=subprocess.PIPE,
stderr=subprocess.PIPE)
out = p.stdout.read()
print ' ... Got %d bytes' % len(out)
cursor.execute('insert into commands_%s (arg, timestamp, epoch, result) '
'values ("%s", now(), %d, "%s");' %
(cmd_name, arg, int(time.time()), base64.encodestring(out)))
cursor.execute('commit;')
if cleanup:
too_old = datetime.datetime.now()
too_old -= datetime.timedelta(days=14)
cursor.execute('delete from commands_%s where timestamp < %s;' %
(cmd_name, sql.FormatSqlValue('timestamp', too_old)))
cursor.execute('commit;')
return out.split('\n')
def RebuildSummary():
users_cursor = feedutils.GetCursor()
dates_cursor = feedutils.GetCursor()
close_cursor = feedutils.GetCursor()
users_cursor.execute('select distinct(username) from bugclose;')
for user_row in users_cursor:
print user_row['username']
dates_cursor.execute('select distinct(date(timestamp)) '
'from bugclose where username = "******";' %
user_row['username'])
for date_row in dates_cursor:
print ' %s' % date_row['(date(timestamp))']
timestamp = sql.FormatSqlValue('timestamp',
date_row['(date(timestamp))'])
summary = {'__total__': 0}
close_cursor.execute('select * from bugclose where '
'username = "******" and date(timestamp) = %s '
'order by timestamp asc;' %
(user_row['username'], timestamp))
for close in close_cursor:
summary.setdefault(close['component'], 0)
summary[close['component']] += 1
summary['__total__'] += 1
epoch = time.mktime(close['timestamp'].timetuple())
close_cursor.execute('delete from bugclosesummary where '
'username="******" and day=date(%s);' %
(user_row['username'], timestamp))
close_cursor.execute(
'insert into bugclosesummary'
'(day, username, data, epoch) '
'values (date(%s), "%s", \'%s\', %d);' %
(timestamp, user_row['username'], json.dumps(summary), epoch))
close_cursor.execute('commit;')
import datetime
import json
import random
import sys
import time
import MySQLdb
import feedutils
import sql
if __name__ == '__main__':
print 'Content-Type: text/plain\r'
print '\r'
sys.stdout.flush()
cursor = feedutils.GetCursor()
showusers = ['mikalstill']
form = cgi.FieldStorage()
if form.has_key('reviewers'):
showusers = feedutils.ResolveGroupMembers(cursor,
form['reviewers'].value)
# Fetch the last seven days of results to start off with
initial_size = 30
one_day = datetime.timedelta(days=1)
last_timestamp = datetime.datetime(1970, 1, 1)
feedutils.SendGroups(cursor)
feedutils.SendReviewers(cursor, '__total__', initial_size)
feedutils.SendPacket({'type': 'users-present', 'payload': showusers})
def ScrapeProject(projectname, days):
launchpad = Launchpad.login_with('openstack-lp-scripts', 'production',
CACHEDIR)
proj = launchpad.projects[projectname]
cursor = feedutils.GetCursor()
subcursor = feedutils.GetCursor()
now = datetime.datetime.now()
since = datetime.datetime(now.year, now.month, now.day)
since -= datetime.timedelta(days=days)
bugs = proj.searchTasks(modified_since=since,
status=[
"New", "Incomplete", "Invalid", "Won't Fix",
"Confirmed", "Triaged", "In Progress",
"Fix Committed", "Fix Released"
])
for b in bugs:
if ONLY and b.bug.id not in ONLY:
continue
status_toucher = None
importance_toucher = None
triage_timestamp = None
print '\n%s' % b.title
print 'Reported by: %s' % b.bug.owner.name
if WRITE:
cursor.execute(
'insert ignore into bugs%s '
'(id, title, reporter, timestamp, component) '
'values(%s, %s, "%s", %s, "%s");' %
(VERSION, b.bug.id, sql.FormatSqlValue(
'title', b.bug.title), b.bug.owner.name,
sql.FormatSqlValue('timestamp',
b.bug.date_created), projectname))
cursor.execute('commit;')
for dup in getattr(b.bug, 'duplicates', []):
print 'Duplicate: %s' % dup.id
cursor.execute(
'update bugs%s set duplicateof=%s where id=%s;' %
(VERSION, b.bug.id, dup.id))
cursor.execute('commit;')
for bugtask in b.bug.bug_tasks:
print(' Targetted to %s on %s by %s' %
(bugtask.bug_target_name, bugtask.date_created,
bugtask.owner.name))
if WRITE:
timestamp = sql.FormatSqlValue('timestamp',
bugtask.date_created)
cursor.execute(
'insert ignore into bugevents%s '
'(id, component, timestamp, username, '
'field, pre, post) '
'values(%s, "%s", %s, "%s", "targetted", NULL, '
'"%s");' %
(VERSION, b.bug.id, bugtask.bug_target_name, timestamp,
bugtask.owner.name, bugtask.bug_target_name))
cursor.execute(
'insert ignore into bugevents%s '
'(id, component, timestamp, username, '
'field, pre, post) '
'values(%s, "%s", %s, "%s", "importance", "-", '
'"%s");' %
(VERSION, b.bug.id, bugtask.bug_target_name, timestamp,
bugtask.owner.name, bugtask.importance))
cursor.execute('insert ignore into bugevents%s '
'(id, component, timestamp, username, '
'field, pre, post) '
'values(%s, "%s", %s, "%s", "status", "-", '
'"%s");' %
(VERSION, b.bug.id, bugtask.bug_target_name,
timestamp, bugtask.owner.name, bugtask.status))
cursor.execute('commit;')
if bugtask.assignee:
time_diff = bugtask.date_assigned - bugtask.date_created
if time_diff.seconds < 60:
print(
' *** special case: bug assigned to %s on task '
'creation ***' % bugtask.assignee)
if WRITE:
timestamp = sql.FormatSqlValue('timestamp',
bugtask.date_assigned)
cursor.execute(
'insert ignore into bugevents%s '
'(id, component, timestamp, username, '
'field, pre, post) '
'values(%s, "%s", %s, "%s", "assignee", "-", '
'"%s");' %
(VERSION, b.bug.id, bugtask.bug_target_name,
timestamp, bugtask.owner.name,
bugtask.assignee.name))
cursor.execute('commit;')
for activity in b.bug.activity:
if activity.whatchanged.startswith('%s: ' % projectname):
timestamp = sql.FormatSqlValue('timestamp',
activity.datechanged)
oldvalue = activity.oldvalue
newvalue = activity.newvalue
try:
m = PERSON_RE.match(oldvalue)
if m:
oldvalue = m.group(1)
except:
pass
try:
m = PERSON_RE.match(newvalue)
if m:
newvalue = m.group(1)
except:
pass
print(' %s :: %s -> %s :: %s on %s' %
(activity.whatchanged, oldvalue, newvalue,
activity.person.name, activity.datechanged))
if WRITE:
cursor.execute('insert ignore into bugevents%s '
'(id, component, timestamp, username, '
'field, pre, post) '
'values(%s, "%s", %s, "%s", "%s", "%s", '
'"%s");' %
(VERSION, b.bug.id, projectname, timestamp,
activity.person.name,
activity.whatchanged.split(': ')[1],
oldvalue, newvalue))
cursor.execute('commit;')
# We define a triage as changing the status from New, and
# changing the importance from Undecided. You must do both
# to earn a cookie.
if (activity.whatchanged.endswith(' status')
and (activity.oldvalue in ['New', 'Incomplete'])
and (activity.newvalue in ['Confirmed', 'Triaged'])):
status_toucher = activity.person.name
if (not triage_timestamp
or activity.datechanged > triage_timestamp):
triage_timestamp = activity.datechanged
if (activity.whatchanged.endswith(' importance')
and (activity.oldvalue == 'Undecided')):
importance_toucher = activity.person.name
if (not triage_timestamp
or activity.datechanged > triage_timestamp):
triage_timestamp = activity.datechanged
# Marking a bug as invalid is a special super cookie!
if (activity.whatchanged.endswith(' status')
and (activity.newvalue in ['Invalid', 'Opinion'])):
status_toucher = activity.person.name
importance_toucher = activity.person.name
triage_timestamp = activity.datechanged
# Code review sent for a bug
if (activity.whatchanged.endswith(' status')
and (activity.newvalue in ['In Progress'])):
# Find out who the bug was assigned to
cursor.execute('select * from bugevents%s where '
'id=%s and component="%s" and '
'field="assignee" and '
'timestamp < %s '
'order by timestamp desc limit 1;' %
(VERSION, b.bug.id, projectname, timestamp))
for row in cursor:
user = row['post']
if WRITE:
UpdateTrackingTables('progress', b, projectname,
timestamp, user)
subcursor.execute('commit;')
# A bug was marked as fixed
if (activity.whatchanged.endswith(' status')
and (activity.newvalue in ['Fix Committed'])):
# Find out who the bug was assigned to
cursor.execute('select * from bugevents%s where '
'id=%s and component="%s" and '
'field="assignee" and '
'timestamp < %s '
'order by timestamp desc limit 1;' %
(VERSION, b.bug.id, projectname, timestamp))
for row in cursor:
user = row['post']
if WRITE:
subcursor.execute(
'update bugs%s set '
'closedby="%s" where id=%s '
'and component="%s";' %
(VERSION, user, b.bug.id, projectname))
subcursor.execute('commit;')
print ' *** %s closed this bug ***' % user
UpdateTrackingTables('close', b, projectname,
timestamp, user)
subcursor.execute('commit;')
# A bug was unfixed
if (activity.whatchanged.endswith(' status')
and (activity.oldvalue in ['Fix Committed'])
and (not activity.newvalue in ['Fix Released'])):
if WRITE:
cursor.execute('update bugs%s set closedby = null '
'where id=%s and component="%s";' %
(VERSION, b.bug.id, projectname))
cursor.execute('commit;')
print ' *** This bug was unclosed ***'
if (status_toucher and importance_toucher
and (status_toucher == importance_toucher)):
print ' *** %s triaged this bug ***' % status_toucher
timestamp = sql.FormatSqlValue('timestamp', triage_timestamp)
if WRITE:
UpdateTrackingTables('triage', b, projectname, timestamp,
status_toucher)
def Reviews(component):
cursor = feedutils.GetCursor()
for l in dbcachingexecute.Execute(time.time() - 60,
'gerrit_query_approvals_json',
('ssh -i ~/.ssh/id_gerrit '
'review.openstack.org gerrit query '
'project:%s '
'--all-approvals --patch-sets '
'--format JSON'),
component,
cleanup=True):
try:
d = json.loads(l)
except:
continue
if d.has_key('id'):
b64 = base64.encodestring(l)
checksum = hashlib.sha1(l).hexdigest()
last_updated = datetime.datetime.fromtimestamp(d['lastUpdated'])
timestamp = sql.FormatSqlValue('timestamp', last_updated)
insert = (
'insert ignore into changes (changeid, timestamp, parsed, '
'checksum) values ("%s", %s, "%s", "%s");' %
(d['id'], timestamp, b64, checksum))
cursor.execute(insert)
if cursor.rowcount == 0:
cursor.execute('select * from changes where changeid="%s";' %
d['id'])
stored_checksum = cursor.fetchone()['checksum']
if checksum != stored_checksum:
cursor.execute('delete from changes where changeid="%s";' %
d['id'])
cursor.execute(insert)
cursor.execute('commit;')
for ps in d.get('patchSets', {}):
patchset = ps.get('number')
for review in ps.get('approvals', []):
# Deliberately leave the timezone alone here so its consistant
# with reports others generate.
updated_at = datetime.datetime.fromtimestamp(
review['grantedOn'])
username = review['by'].get('username', 'unknown')
if username in ['jenkins', 'smokestack']:
continue
timestamp = sql.FormatSqlValue('timestamp', updated_at)
score = review.get('value', 0)
cursor.execute(
'insert ignore into reviews '
'(changeid, username, timestamp, day, component, '
'patchset, score) '
'values ("%s", "%s", %s, date(%s), "%s", %s, %s);' %
(d['id'], username, timestamp, timestamp, component,
patchset, score))
if cursor.rowcount > 0:
# This is a new review, we assume we're the only writer
print 'New review from %s' % username
cursor.execute('select * from reviewsummary where '
'username="******" and day=date(%s);' %
(username, timestamp))
if cursor.rowcount > 0:
row = cursor.fetchone()
summary = json.loads(row['data'])
else:
summary = {}
summary.setdefault(component, 0)
summary.setdefault('__total__', 0)
summary[component] += 1
summary['__total__'] += 1
cursor.execute('delete from reviewsummary where '
'username="******" and day=date(%s);' %
(username, timestamp))
cursor.execute('insert into reviewsummary'
'(day, username, data, epoch) '
'values (date(%s), "%s", \'%s\', %d);' %
(timestamp, username, json.dumps(summary),
int(time.time())))
cursor.execute('commit;')
def RebuildStates():
global DUPLICATES
cursor = feedutils.GetCursor()
cursor.execute('select distinct(id) from bugevents;')
bugids = []
for row in cursor:
bugids.append(row['id'])
cursor.execute('select distinct(component) from bugevents;')
components = []
for row in cursor:
components.append(row['component'])
for id in bugids:
if id in DUPLICATES:
continue
for component in components:
opened = None
importance = 'Undecided'
status = 'Unknown'
cursor.execute('select * from bugevents where '
'id=%s and component="%s";'
%(id, component))
for row in cursor:
# Opened, reopened
if row['field'] == 'targetted':
if not status in CLOSED_STATES:
opened = row['timestamp']
elif row['field'] == 'status' and row['pre'] in CLOSED_STATES and not row['post'] in CLOSED_STATES:
opened = row['timestamp']
# Closed
elif row['field'] == 'status' and row['post'] in CLOSED_STATES:
EmitState(id, component, importance, opened, row['timestamp'])
opened = None
status = row['post']
elif row['field'] == 'status':
status = row['post']
# Changed importance
elif row['field'] == 'importance':
if not status in CLOSED_STATES:
EmitState(id, component, importance, opened, row['timestamp'])
opened = row['timestamp']
importance = row['post']
# Don't care
elif row['field'] == 'assignee':
pass
elif row['field'] == 'milestone':
pass
# Unknown row!
else:
print row
if opened:
EmitState(id, component, importance, opened, datetime.datetime.now())
