def _get_versions(self, params): """ Return product information for one or more product:version combinations """ products = [] (params["products_versions"], products) = self.parse_versions(params["versions"], []) sql_select = """ SELECT product_name as product, version_string as version, start_date, end_date, is_featured, build_type, throttle::float, has_builds FROM product_info """ sql_where = [] versions_list = [] products_list = [] for x in range(0, len(params["products_versions"]), 2): products_list.append(params["products_versions"][x]) versions_list.append(params["products_versions"][x + 1]) sql_where = [ "(product_name = %(product" + str(x) + ")s AND version_string = %(version" + str(x) + ")s)" for x in range(len(products_list)) ] sql_params = {} sql_params = add_param_to_dict(sql_params, "product", products_list) sql_params = add_param_to_dict(sql_params, "version", versions_list) if len(sql_where) > 0: sql_query = " WHERE ".join((sql_select, " OR ".join(sql_where))) else: sql_query = sql_select sql_query = """ /* socorro.external.postgresql.Products.get_versions */ %s """ % sql_query error_message = "Failed to retrieve products versions from PostgreSQL" results = self.query(sql_query, sql_params, error_message=error_message) products = [] for product in results.zipped(): product['start_date'] = datetimeutil.date_to_string( product['start_date']) product['end_date'] = datetimeutil.date_to_string( product['end_date']) products.append(product) return {"hits": products, "total": len(products)}
def get_featured(self, **kwargs): """Return a list of featured versions for one, several or all products. """ filters = [ ("products", None, ["list", "str"]), ] params = external_common.parse_arguments(filters, kwargs) sql = """ SELECT product_name, version_string FROM product_info WHERE is_featured = true """ sql_params = {} if params.products and params.products[0]: sql_where = [] for i in xrange(len(params.products)): sql_where.append("%%(product%s)s" % i) sql = "%s AND product_name IN (%s)" % (sql, ", ".join(sql_where)) sql_params = add_param_to_dict(sql_params, "product", params.products) sql_results = [] try: connection = self.database.connection() cur = connection.cursor() cur.execute(sql, sql_params) sql_results = cur.fetchall() except psycopg2.Error: logger.error("Failed updating featured versions in PostgreSQL") raise finally: connection.close() hits = {} total = 0 for row in sql_results: total += 1 version = dict(zip(("product", "version"), row)) if version["product"] not in hits: hits[version["product"]] = [version["version"]] else: hits[version["product"]].append(version["version"]) return { "total": total, "hits": hits }
def get_featured(self, **kwargs): """Return a list of featured versions for one, several or all products. """ filters = [ ("products", None, ["list", "str"]), ] params = external_common.parse_arguments(filters, kwargs) sql = """ SELECT product_name, version_string FROM product_info WHERE is_featured = true """ sql_params = {} if params.products and params.products[0]: sql_where = [] for i in xrange(len(params.products)): sql_where.append("%%(product%s)s" % i) sql = "%s AND product_name IN (%s)" % (sql, ", ".join(sql_where)) sql_params = add_param_to_dict(sql_params, "product", params.products) error_message = "Failed to retrieve featured versions from PostgreSQL" sql_results = self.query(sql, sql_params, error_message=error_message) hits = {} total = 0 for row in sql_results: total += 1 version = dict(zip(("product", "version"), row)) if version["product"] not in hits: hits[version["product"]] = [version["version"]] else: hits[version["product"]].append(version["version"]) return { "total": total, "hits": hits }
def _get_versions(self, params): """ Return product information for one or more product:version combinations """ products = [] (params["products_versions"], products) = self.parse_versions(params["versions"], []) sql_select = """ SELECT product_name as product, version_string as version, start_date, end_date, is_featured, build_type, throttle::float, has_builds FROM product_info """ sql_where = [] versions_list = [] products_list = [] for x in range(0, len(params["products_versions"]), 2): products_list.append(params["products_versions"][x]) versions_list.append(params["products_versions"][x + 1]) sql_where = ["(product_name = %(product" + str(x) + ")s AND version_string = %(version" + str(x) + ")s)" for x in range(len(products_list))] sql_params = {} sql_params = add_param_to_dict(sql_params, "product", products_list) sql_params = add_param_to_dict(sql_params, "version", versions_list) if len(sql_where) > 0: sql_query = " WHERE ".join((sql_select, " OR ".join(sql_where))) else: sql_query = sql_select sql_query = """ /* socorro.external.postgresql.Products.get_versions */ %s """ % sql_query error_message = "Failed to retrieve products versions from PostgreSQL" results = self.query(sql_query, sql_params, error_message=error_message) products = [] for row in results: product = dict(zip(( "product", "version", "start_date", "end_date", "is_featured", "build_type", "throttle", "has_builds" ), row)) product["start_date"] = datetimeutil.date_to_string( product["start_date"]) product["end_date"] = datetimeutil.date_to_string( product["end_date"]) products.append(product) return { "hits": products, "total": len(products) }
def get(self, **kwargs): """ Return urls for signature """ filters = [ ("signature", None, "str"), ("start_date", None, "datetime"), ("end_date", None, "datetime"), ("products", None, ["list", "str"]), ("versions", None, ["list", "str"]), ] params = external_common.parse_arguments(filters, kwargs) #Because no parameters are optional, we need to loop through #all parameters to ensure each has been set and is not None missingParams = [] for param in params: if not params[param]: missingParams.append(param) if len(missingParams) > 0: raise MissingOrBadArgumentException( "Mandatory parameter(s) '%s' is missing or empty" % ", ".join(missingParams)) # Decode double-encoded slashes in signature params["signature"] = params["signature"].replace("%2F", "/") all_products_versions_sql = """ /* socorro.external.postgresql.signature_urls.SignatureURLs.get */ SELECT url, count(*) as crash_count FROM reports_clean JOIN reports_user_info USING ( UUID ) JOIN signatures USING ( signature_id ) WHERE reports_clean.date_processed BETWEEN %(start_date)s AND %(end_date)s AND reports_user_info.date_processed BETWEEN %(start_date)s AND %(end_date)s AND signature = %(signature)s AND url <> '' """ sql = """ /* socorro.external.postgresql.signature_urls.SignatureURLs.get */ SELECT url, count(*) as crash_count FROM reports_clean JOIN reports_user_info USING ( UUID ) JOIN signatures USING ( signature_id ) JOIN product_versions USING ( product_version_id ) WHERE reports_clean.date_processed BETWEEN %(start_date)s AND %(end_date)s AND reports_user_info.date_processed BETWEEN %(start_date)s AND %(end_date)s AND signature = %(signature)s AND url <> '' AND ( """ sql_group_order = """ GROUP BY url ORDER BY crash_count DESC LIMIT 100""" sql_params = { "start_date": params.start_date, "end_date": params.end_date, "signature": params.signature } # if this query is for all products the 'ALL' keyword will be # the only item in the products list and this will then also # be for all versions. if 'ALL' in params['products']: sql_query = " ".join((all_products_versions_sql, sql_group_order)) # if this query is for all versions the 'ALL' keyword will be # the only item in the versions list. elif 'ALL' in params['versions']: sql_products = " product_name IN ('%s') )" % ( "', '".join([product for product in params.products])) sql_date_range_limit = """AND '%s' BETWEEN product_versions.build_date AND product_versions.sunset_date""" % params.end_date sql_query = " ".join((sql, sql_products, sql_date_range_limit, sql_group_order)) else: products = [] (params["products_versions"], products) = self.parse_versions(params["versions"], []) versions_list = [] products_list = [] for x in range(0, len(params["products_versions"]), 2): products_list.append(params["products_versions"][x]) versions_list.append(params["products_versions"][x + 1]) product_version_list = [] for prod in params["products"]: versions = [] [versions.append(versions_list[i]) for i, x in enumerate(products_list) if x == prod] product_version_list.append(tuple(versions)) sql_product_version_ids = [ """( product_name = %%(product%s)s AND version_string IN %%(version%s)s ) """ % (x, x) for x in range(len(product_version_list))] sql_params = add_param_to_dict(sql_params, "version", product_version_list) sql_params = add_param_to_dict(sql_params, "product", params.products) sql_query = " ".join((sql, " OR ".join(sql_product_version_ids), " ) " + sql_group_order)) json_result = { "total": 0, "hits": [] } connection = None try: connection = self.database.connection() cur = connection.cursor() results = db.execute(cur, sql_query, sql_params) except psycopg2.Error: logger.error( "Failed retrieving urls for signature data from PostgreSQL", exc_info=True) else: for url in results: row = dict(zip(( "url", "crash_count"), url)) json_result["hits"].append(row) json_result["total"] = len(json_result["hits"]) return json_result finally: if connection: connection.close()
def get(self, **kwargs): """ Return urls for signature """ filters = [ ("signature", None, "str"), ("start_date", None, "datetime"), ("end_date", None, "datetime"), ("products", None, ["list", "str"]), ("versions", None, ["list", "str"]), ] params = external_common.parse_arguments(filters, kwargs) #Because no parameters are optional, we need to loop through #all parameters to ensure each has been set and is not None missingParams = [] for param in params: if not params[param]: missingParams.append(param) if len(missingParams) > 0: raise MissingOrBadArgumentError( "Mandatory parameter(s) '%s' is missing or empty" % ", ".join(missingParams)) all_products_versions_sql = """ /* socorro.external.postgresql.signature_urls.SignatureURLs.get */ SELECT url, count(*) as crash_count FROM reports_clean JOIN reports_user_info USING ( UUID ) JOIN signatures USING ( signature_id ) WHERE reports_clean.date_processed BETWEEN %(start_date)s AND %(end_date)s AND reports_user_info.date_processed BETWEEN %(start_date)s AND %(end_date)s AND signature = %(signature)s AND url <> '' """ sql = """ /* socorro.external.postgresql.signature_urls.SignatureURLs.get */ SELECT url, count(*) as crash_count FROM reports_clean JOIN reports_user_info USING ( UUID ) JOIN signatures USING ( signature_id ) JOIN product_versions USING ( product_version_id ) WHERE reports_clean.date_processed BETWEEN %(start_date)s AND %(end_date)s AND reports_user_info.date_processed BETWEEN %(start_date)s AND %(end_date)s AND signature = %(signature)s AND url <> '' AND ( """ sql_group_order = """ GROUP BY url ORDER BY crash_count DESC LIMIT 100""" sql_params = { "start_date": params.start_date, "end_date": params.end_date, "signature": params.signature } # if this query is for all products the 'ALL' keyword will be # the only item in the products list and this will then also # be for all versions. if 'ALL' in params['products']: sql_query = " ".join((all_products_versions_sql, sql_group_order)) # if this query is for all versions the 'ALL' keyword will be # the only item in the versions list. elif 'ALL' in params['versions']: sql_products = " product_name IN ('%s') )" % ("', '".join( [product for product in params.products])) sql_date_range_limit = """AND '%s' BETWEEN product_versions.build_date AND product_versions.sunset_date""" % params.end_date sql_query = " ".join( (sql, sql_products, sql_date_range_limit, sql_group_order)) else: products = [] (params["products_versions"], products) = self.parse_versions(params["versions"], []) versions_list = [] products_list = [] for x in range(0, len(params["products_versions"]), 2): products_list.append(params["products_versions"][x]) versions_list.append(params["products_versions"][x + 1]) product_version_list = [] for prod in params["products"]: versions = [] [ versions.append(versions_list[i]) for i, x in enumerate(products_list) if x == prod ] product_version_list.append(tuple(versions)) sql_product_version_ids = [ """( product_name = %%(product%s)s AND version_string IN %%(version%s)s ) """ % (x, x) for x in range(len(product_version_list)) ] sql_params = add_param_to_dict(sql_params, "version", product_version_list) sql_params = add_param_to_dict(sql_params, "product", params.products) sql_query = " ".join((sql, " OR ".join(sql_product_version_ids), " ) " + sql_group_order)) json_result = {"total": 0, "hits": []} connection = None try: connection = self.database.connection() cur = connection.cursor() results = db.execute(cur, sql_query, sql_params) except psycopg2.Error: logger.error( "Failed retrieving urls for signature data from PostgreSQL", exc_info=True) else: for url in results: row = dict(zip(("url", "crash_count"), url)) json_result["hits"].append(row) json_result["total"] = len(json_result["hits"]) return json_result finally: if connection: connection.close()
def get(self, **kwargs): """ Return urls for signature """ filters = [ ("signature", None, "str"), ("start_date", None, "datetime"), ("end_date", None, "datetime"), ("products", None, ["list", "str"]), ("versions", None, ["list", "str"]), ] params = external_common.parse_arguments(filters, kwargs) #Because no parameters are optional, we need to loop through #all parameters to ensure each has been set and is not None missingParams = [] for param in params: if not params[param]: if param == 'versions': # force versions parameter to being 'ALL' if empty params[param] = 'ALL' continue missingParams.append(param) if len(missingParams) > 0: raise MissingArgumentError(", ".join(missingParams)) all_products_versions_sql = """ /* socorro.external.postgresql.signature_urls.SignatureURLs.get */ SELECT url, count(*) as crash_count FROM reports_clean JOIN reports_user_info USING ( UUID ) JOIN signatures USING ( signature_id ) WHERE reports_clean.date_processed BETWEEN %(start_date)s AND %(end_date)s AND reports_user_info.date_processed BETWEEN %(start_date)s AND %(end_date)s AND signature = %(signature)s AND url <> '' """ sql = """ /* socorro.external.postgresql.signature_urls.SignatureURLs.get */ SELECT url, count(*) as crash_count FROM reports_clean JOIN reports_user_info USING ( UUID ) JOIN signatures USING ( signature_id ) JOIN product_versions USING ( product_version_id ) WHERE reports_clean.date_processed BETWEEN %(start_date)s AND %(end_date)s AND reports_user_info.date_processed BETWEEN %(start_date)s AND %(end_date)s AND signature = %(signature)s AND url <> '' AND ( """ sql_group_order = """ GROUP BY url ORDER BY crash_count DESC LIMIT 100""" sql_params = { "start_date": params.start_date, "end_date": params.end_date, "signature": params.signature } # if this query is for all products the 'ALL' keyword will be # the only item in the products list and this will then also # be for all versions. if 'ALL' in params['products']: sql_query = " ".join((all_products_versions_sql, sql_group_order)) # if this query is for all versions the 'ALL' keyword will be # the only item in the versions list. elif 'ALL' in params['versions']: sql_products = " product_name IN ('%s') )" % ( "', '".join([product for product in params.products])) sql_date_range_limit = """AND '%s' BETWEEN product_versions.build_date AND product_versions.sunset_date""" % params.end_date sql_query = " ".join((sql, sql_products, sql_date_range_limit, sql_group_order)) else: products = [] (params["products_versions"], products) = self.parse_versions(params["versions"], []) versions_list = [] products_list = [] for x in range(0, len(params["products_versions"]), 2): products_list.append(params["products_versions"][x]) versions_list.append(params["products_versions"][x + 1]) product_version_list = [] for prod in params["products"]: versions = [] [versions.append(versions_list[i]) for i, x in enumerate(products_list) if x == prod] product_version_list.append(tuple(versions)) sql_product_version_ids = [ """( product_name = %%(product%s)s AND version_string IN %%(version%s)s ) """ % (x, x) for x in range(len(product_version_list))] sql_params = add_param_to_dict(sql_params, "version", product_version_list) sql_params = add_param_to_dict(sql_params, "product", params.products) sql_query = " ".join((sql, " OR ".join(sql_product_version_ids), " ) " + sql_group_order)) error_message = "Failed to retrieve urls for signature from PostgreSQL" results = self.query(sql_query, sql_params, error_message=error_message) urls = [] for row in results: url = dict(zip(("url", "crash_count"), row)) urls.append(url) return { "hits": urls, "total": len(urls) }
def _get_versions(self, params): """ Return product information for one or more product:version combinations """ products = [] (params["products_versions"], products) = self.parse_versions(params["versions"], []) sql_select = """ SELECT product_name as product, version_string as version, start_date, end_date, is_featured, build_type, throttle::float, has_builds FROM product_info """ sql_where = [] versions_list = [] products_list = [] for x in range(0, len(params["products_versions"]), 2): products_list.append(params["products_versions"][x]) versions_list.append(params["products_versions"][x + 1]) sql_where = [ "(product_name = %(product" + str(x) + ")s AND version_string = %(version" + str(x) + ")s)" for x in range(len(products_list)) ] sql_params = {} sql_params = add_param_to_dict(sql_params, "product", products_list) sql_params = add_param_to_dict(sql_params, "version", versions_list) if len(sql_where) > 0: sql_query = " WHERE ".join((sql_select, " OR ".join(sql_where))) else: sql_query = sql_select sql_query = ( """ /* socorro.external.postgresql.Products.get_versions */ %s """ % sql_query ) json_result = {"total": 0, "hits": []} try: connection = self.database.connection() cur = connection.cursor() results = db.execute(cur, sql_query, sql_params) except psycopg2.Error: logger.error("Failed retrieving products_versions data from PostgreSQL", exc_info=True) else: for product in results: row = dict( zip( ( "product", "version", "start_date", "end_date", "is_featured", "build_type", "throttle", "has_builds", ), product, ) ) json_result["hits"].append(row) row["start_date"] = datetimeutil.date_to_string(row["start_date"]) row["end_date"] = datetimeutil.date_to_string(row["end_date"]) json_result["total"] = len(json_result["hits"]) return json_result finally: connection.close()
def get(self, **kwargs): """ Return urls for signature """ filters = [ ("signature", None, "str"), ("start_date", None, "datetime"), ("end_date", None, "datetime"), ("products", None, ["list", "str"]), ("versions", None, ["list", "str"]), ] params = external_common.parse_arguments(filters, kwargs) #Because no parameters are optional, we need to loop through #all parameters to ensure each has been set and is not None missingParams = [] for param in params: if not params[param]: if param == 'versions': # force versions parameter to being 'ALL' if empty params[param] = 'ALL' continue missingParams.append(param) if len(missingParams) > 0: raise MissingArgumentError(", ".join(missingParams)) all_products_versions_sql = """ /* socorro.external.postgresql.signature_urls.SignatureURLs.get */ SELECT url, count(*) as crash_count FROM reports_clean JOIN reports_user_info USING ( UUID ) JOIN signatures USING ( signature_id ) WHERE reports_clean.date_processed BETWEEN %(start_date)s AND %(end_date)s AND reports_user_info.date_processed BETWEEN %(start_date)s AND %(end_date)s AND signature = %(signature)s AND url <> '' """ sql = """ /* socorro.external.postgresql.signature_urls.SignatureURLs.get */ SELECT url, count(*) as crash_count FROM reports_clean JOIN reports_user_info USING ( UUID ) JOIN signatures USING ( signature_id ) JOIN product_versions USING ( product_version_id ) WHERE reports_clean.date_processed BETWEEN %(start_date)s AND %(end_date)s AND reports_user_info.date_processed BETWEEN %(start_date)s AND %(end_date)s AND signature = %(signature)s AND url <> '' AND ( """ sql_group_order = """ GROUP BY url ORDER BY crash_count DESC LIMIT 100""" sql_params = { "start_date": params.start_date, "end_date": params.end_date, "signature": params.signature } # if this query is for all products the 'ALL' keyword will be # the only item in the products list and this will then also # be for all versions. if 'ALL' in params['products']: sql_query = " ".join((all_products_versions_sql, sql_group_order)) # if this query is for all versions the 'ALL' keyword will be # the only item in the versions list. elif 'ALL' in params['versions']: sql_products = " product_name IN %(products)s )" sql_params['products'] = tuple(params.products) sql_date_range_limit = """AND %(end_date)s BETWEEN product_versions.build_date AND product_versions.sunset_date""" sql_query = " ".join((sql, sql_products, sql_date_range_limit, sql_group_order)) else: products = [] (params["products_versions"], products) = self.parse_versions(params["versions"], []) if len(params["products_versions"]) == 0: raise BadArgumentError(", ".join(params["versions"])) versions_list = [] products_list = [] for x in range(0, len(params["products_versions"]), 2): products_list.append(params["products_versions"][x]) versions_list.append(params["products_versions"][x + 1]) product_version_list = [] for prod in params["products"]: versions = [] [versions.append(versions_list[i]) for i, x in enumerate(products_list) if x == prod] product_version_list.append(tuple(versions)) sql_product_version_ids = [ """( product_name = %%(product%s)s AND version_string IN %%(version%s)s ) """ % (x, x) for x in range(len(product_version_list))] sql_params = add_param_to_dict(sql_params, "version", product_version_list) sql_params = add_param_to_dict(sql_params, "product", params.products) sql_query = " ".join((sql, " OR ".join(sql_product_version_ids), " ) " + sql_group_order)) error_message = "Failed to retrieve urls for signature from PostgreSQL" results = self.query(sql_query, sql_params, error_message=error_message) urls = results.zipped() return { "hits": urls, "total": len(urls) }
def _get_versions(self, params): """ Return product information for one or more product:version combinations """ products = [] (params["products_versions"], products) = self.parse_versions(params["versions"], []) sql_select = """ SELECT product_name as product, version_string as version, start_date, end_date, is_featured, build_type, throttle::float, has_builds FROM product_info """ sql_where = [] versions_list = [] products_list = [] for x in range(0, len(params["products_versions"]), 2): products_list.append(params["products_versions"][x]) versions_list.append(params["products_versions"][x + 1]) sql_where = [ "(product_name = %(product" + str(x) + ")s AND version_string = %(version" + str(x) + ")s)" for x in range(len(products_list)) ] sql_params = {} sql_params = add_param_to_dict(sql_params, "product", products_list) sql_params = add_param_to_dict(sql_params, "version", versions_list) if len(sql_where) > 0: sql_query = " WHERE ".join((sql_select, " OR ".join(sql_where))) else: sql_query = sql_select sql_query = """ /* socorro.external.postgresql.Products.get_versions */ %s """ % sql_query json_result = {"total": 0, "hits": []} try: connection = self.database.connection() cur = connection.cursor() results = db.execute(cur, sql_query, sql_params) except psycopg2.Error: logger.error( "Failed retrieving products_versions data from PostgreSQL", exc_info=True) else: for product in results: row = dict( zip(("product", "version", "start_date", "end_date", "is_featured", "build_type", "throttle", "has_builds"), product)) json_result["hits"].append(row) row["start_date"] = datetimeutil.date_to_string( row["start_date"]) row["end_date"] = datetimeutil.date_to_string(row["end_date"]) json_result["total"] = len(json_result["hits"]) return json_result finally: connection.close()