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()