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