def getListOfTopCrashersBySignature(connection, dbParams):
"""
Answers a generator of tcbs rows
"""
assertPairs = {
'startDate': (datetime.date, datetime.datetime),
'to_date': (datetime.date, datetime.datetime),
'product': basestring,
'version': basestring,
'limit': int
}
for param in assertPairs:
if not isinstance(dbParams[param], assertPairs[param]):
raise BadArgumentError(type(dbParams[param]))
order_by = 'report_count' # default order field
where = [''] # trick for the later join
if dbParams['crash_type'] != 'all':
where.append(
"process_type = %s" % (
sqlutils.quote_value(dbParams['crash_type']),
)
)
if dbParams['os']:
abbreviated_os = dbParams['os'][0:3].lower()
if abbreviated_os not in ('win', 'lin', 'mac'):
# this check prevents possible SQL injections
raise BadArgumentError('Invalid OS to order on')
order_by = '%s_count' % abbreviated_os
where.append("%s > 0" % order_by)
where = ' AND '.join(where)
table_to_use = 'tcbs'
date_range_field = 'report_date'
if dbParams['date_range_type'] == 'build':
table_to_use = 'tcbs_build'
date_range_field = 'build_date'
sql = """
WITH tcbs_r as (
SELECT tcbs.signature_id,
signature,
pv.product_name,
version_string,
sum(report_count) as report_count,
sum(win_count) as win_count,
sum(lin_count) as lin_count,
sum(mac_count) as mac_count,
sum(hang_count) as hang_count,
plugin_count(process_type,report_count) as plugin_count,
content_count(process_type,report_count) as content_count,
first_report,
version_list,
sum(startup_count) as startup_count,
sum(is_gc_count) as is_gc_count
FROM %s tcbs
JOIN signatures USING (signature_id)
JOIN product_versions AS pv USING (product_version_id)
JOIN signature_products_rollup AS spr
ON spr.signature_id = tcbs.signature_id
AND spr.product_name = pv.product_name
WHERE pv.product_name = %%s
AND version_string = %%s
AND tcbs.%s BETWEEN %%s AND %%s
%s
GROUP BY tcbs.signature_id, signature, pv.product_name, version_string,
first_report, spr.version_list
),
tcbs_window AS (
SELECT tcbs_r.*,
sum(report_count) over () as total_crashes,
dense_rank() over (order by report_count desc) as ranking
FROM
tcbs_r
)
SELECT signature,
report_count,
win_count,
lin_count,
mac_count,
hang_count,
plugin_count,
content_count,
first_report,
version_list,
%s / total_crashes::float as percent_of_total,
startup_count / %s::float as startup_percent,
is_gc_count,
total_crashes::int
FROM tcbs_window
ORDER BY %s DESC
LIMIT %s
""" % (
table_to_use,
date_range_field,
where,
order_by,
order_by,
order_by,
dbParams["limit"]
)
params = (
dbParams['product'],
dbParams['version'],
dbParams['startDate'],
dbParams['to_date'],
)
try:
cursor = connection.cursor()
return db.execute(cursor, sql, params)
except Exception:
connection.rollback()
raise
else:
connection.commit()
def test_quote_value():
eq_(sqlutils.quote_value('name'), "'name'")
eq_(sqlutils.quote_value("'name'"), "'''name'''")
eq_(sqlutils.quote_value("o'clock"), "'o''clock'")
def getListOfTopCrashersBySignature(connection, dbParams):
"""
Answers a generator of tcbs rows
"""
assertPairs = {
'startDate': (datetime.date, datetime.datetime),
'to_date': (datetime.date, datetime.datetime),
'product': basestring,
'version': basestring,
'limit': int
}
for param in assertPairs:
if not isinstance(dbParams[param], assertPairs[param]):
raise BadArgumentError(type(dbParams[param]))
order_by = 'report_count' # default order field
where = [''] # trick for the later join
if dbParams['crash_type'] != 'all':
where.append("process_type = %s" %
(sqlutils.quote_value(dbParams['crash_type']), ))
if dbParams['os']:
abbreviated_os = dbParams['os'][0:3].lower()
if abbreviated_os not in ('win', 'lin', 'mac'):
# this check prevents possible SQL injections
raise BadArgumentError('Invalid OS to order on')
order_by = '%s_count' % abbreviated_os
where.append("%s > 0" % order_by)
where = ' AND '.join(where)
table_to_use = 'tcbs'
date_range_field = 'report_date'
if dbParams['date_range_type'] == 'build':
table_to_use = 'tcbs_build'
date_range_field = 'build_date'
sql = """
WITH tcbs_r as (
SELECT tcbs.signature_id,
signature,
pv.product_name,
version_string,
sum(report_count) as report_count,
sum(win_count) as win_count,
sum(lin_count) as lin_count,
sum(mac_count) as mac_count,
sum(hang_count) as hang_count,
plugin_count(process_type,report_count) as plugin_count,
content_count(process_type,report_count) as content_count,
first_report,
version_list,
sum(startup_count) as startup_count,
sum(is_gc_count) as is_gc_count
FROM %s tcbs
JOIN signatures USING (signature_id)
JOIN product_versions AS pv USING (product_version_id)
JOIN signature_products_rollup AS spr
ON spr.signature_id = tcbs.signature_id
AND spr.product_name = pv.product_name
WHERE pv.product_name = %%s
AND version_string = %%s
AND tcbs.%s BETWEEN %%s AND %%s
%s
GROUP BY tcbs.signature_id, signature, pv.product_name, version_string,
first_report, spr.version_list
),
tcbs_window AS (
SELECT tcbs_r.*,
sum(report_count) over () as total_crashes,
dense_rank() over (order by report_count desc) as ranking
FROM
tcbs_r
)
SELECT signature,
report_count,
win_count,
lin_count,
mac_count,
hang_count,
plugin_count,
content_count,
first_report,
version_list,
%s / total_crashes::float as percent_of_total,
startup_count / %s::float as startup_percent,
is_gc_count,
total_crashes::int
FROM tcbs_window
ORDER BY %s DESC
LIMIT %s
""" % (table_to_use, date_range_field, where, order_by, order_by, order_by,
dbParams["limit"])
params = (
dbParams['product'],
dbParams['version'],
dbParams['startDate'],
dbParams['to_date'],
)
try:
cursor = connection.cursor()
return db.execute(cursor, sql, params)
except Exception:
connection.rollback()
raise
else:
connection.commit()
