1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
|
#!/usr/bin/python
#
# Pull some data on an individual bug, e.g. creation_time, resolution, etc.
# from the Bugzilla API
# Usage: ./bugdata <bug_id>
import sys, string, time, re, sys, os, MySQLdb
import simplejson as json
from urllib2 import urlopen
from settings import *
if "--debug" in sys.argv:
DEBUG = True
else:
DEBUG = False
# set up database connection
db = MySQLdb.connect(host=DB_HOST, user=DB_USER, passwd=DB_PASS, db=DB_NAME)
db.autocommit(True)
cur = db.cursor()
# list of bug fields we care about
my_bug_fields = ["creation_time"]
# list of history fields we care about
my_history_fields = ["keywords","resolution"]
def bugurl(bug):
return "https://api-dev.bugzilla.mozilla.org/latest/bug/%d?%s" % (bug, BZ_AUTH)
def histurl(bug):
return "https://api-dev.bugzilla.mozilla.org/latest/bug/%s/history?%s" % (bug, BZ_AUTH)
# convert API timestamp a MySQL datetime
# d fmt: '2010-06-22T14:56:08Z'
def convertDate(d):
old = time.strptime(d, "%Y-%m-%dT%H:%M:%SZ")
return time.strftime("%Y-%m-%d %H:%M:%S", old)
# parse the severity from the keywords text
# e.g. [sg:high][OOPP]
def getSeverity(s):
if DEBUG: print "getSeverity: ", s
for keyword in s:
if re.match("^sec-", keyword):
sevmatch = re.search("(?<=sec-)[\w]+", keyword)
if DEBUG: print "--> "+sevmatch.group(0)
return sevmatch.group(0)
if DEBUG: print "--> <none>"
return ""
# get the bug number to process
try:
BUGID = int(sys.argv[1])
except:
print "Usage: "+sys.argv[0]+" <bug_id>"
sys.exit()
# get fields from bug table
if DEBUG: print "Fetching %s" % (bugurl(BUGID))
resp = urlopen( bugurl(BUGID) )
bugobj = json.loads( resp.read() )
# bugobj.keys() == ['cf_blocking_193', 'cf_blocking_192', 'cf_blocking_191',
# 'attachments', 'classification', 'cc', 'depends_on',
# 'creation_time', 'is_reporter_accessible', 'keywords',
# 'summary', 'id', 'cf_status_192', 'severity', 'platform',
# 'priority', 'cf_status_193', 'cf_status_191', 'version',
# 'ref', 'status', 'product', 'blocks', 'qa_contact',
# 'reporter', 'is_everconfirmed', 'component', 'groups',
# 'target_milestone', 'is_cc_accessible', 'whiteboard',
# 'last_change_time', 'token', 'flags', 'assigned_to',
# 'resolution', 'op_sys', 'cf_blocking_fennec']
opendate = convertDate(bugobj["creation_time"])
summary = bugobj["summary"]
# last severity rating in Bugs table (could be blank)
severity = getSeverity(bugobj["keywords"])
# get fields from bug history
resp = urlopen( histurl(BUGID) )
histobj = json.loads( resp.read() )
history = histobj["history"]
# last change to Bugs table
sql = "SELECT updated from secbugs_Bugs where bugid=%s;" % (BUGID)
if DEBUG: print sql
cur.execute(sql)
row = cur.fetchone()
if row:
updated = str(row[0])
else:
updated = opendate
# date bug was resolved
closedate = ""
# history is organized in groups of changes
for group in history:
# group.keys() == ['changes', 'changer', 'change_time']
# store change time
change_time = convertDate(group["change_time"])
for change in group["changes"]:
# change ex. {'removed': 'unspecified', 'field_name': 'version',
# 'added': 'Trunk'}
# skip changes we don't care about
if change["field_name"] not in my_history_fields:
continue
# Look for resolution time
# e.g. resolution - old: '', new: 'FIXED'
if change["field_name"] == "resolution":
if len(change["added"]):
closedate = change_time
# bug was reopened
else:
closedate = ""
# NOTE: for items that will change one of the Bugs fields,
# make sure to check if change_time > secbugs_Bugs.updated and if so
# update that field with the change time. Right now, only
# keywords is doing so...
# Use most recent sec- keywords marking to determine severity
elif change["field_name"] == "keywords":
# keep track of last update to Bugs table
# e.g. last severity assigned
#if DEBUG: print "change_time: %s, updated: %s" % (str(change_time), updated)
if change_time > updated:
updated = str(change_time)
severity = getSeverity([change["added"]])
# default case: log the change to a field we care about
else:
sql = "INSERT INTO secbugs_BugHistory VALUES (%s, '%s', '%s', '%s', '%s');" % (BUGID, change_time, db.escape_string(change["field_name"]), db.escape_string(change["added"]), db.escape_string(change["removed"]))
if DEBUG: print sql
else: cur.execute(sql)
# check if our bug has a closedate. If not, we may need to set all open bugs'
# "closedate" with the current date so they align right on the chart (maybe not)
if not len(closedate):
closedate = "0000-00-00 00:00:00"
sql = "INSERT INTO secbugs_Bugs VALUES (%s, '%s', '%s', '%s', '%s', '%s') ON DUPLICATE KEY UPDATE opendate='%s', closedate='%s', severity='%s', summary='%s', updated='%s';" % (BUGID, opendate, closedate, db.escape_string(severity), db.escape_string(summary), updated, opendate, closedate, db.escape_string(severity), db.escape_string(summary), updated)
if DEBUG: print sql
else: cur.execute(sql)
|
