def get(self, **kwargs):
"""
Search for crashes and return them.
See http://socorro.readthedocs.org/en/latest/middleware.html#search
Optional arguments: see SearchCommon.get_parameters()
"""
# change aliases from the web to the implementation's need
if "for" in kwargs and "terms" not in kwargs:
kwargs["terms"] = kwargs.get("for")
if "from" in kwargs and "from_date" not in kwargs:
kwargs["from_date"] = kwargs.get("from")
if "to" in kwargs and "to_date" not in kwargs:
kwargs["to_date"] = kwargs.get("to")
if "in" in kwargs and "fields" not in kwargs:
kwargs["fields"] = kwargs.get("in")
params = search_common.get_parameters(kwargs)
# Get information about the versions
versions_service = Util(config=self.context)
params["versions_info"] = versions_service.versions_info(**params)
# Changing the OS ids to OS names
for i, elem in enumerate(params["os"]):
for platform in self.config.platforms:
if platform["id"][:3] == elem[:3]:
# the split is here to remove 'nt' from 'windows nt'
# and 'os x' from 'mac os x'
params["os"][i] = platform["name"].split(' ')[0]
query = Search.build_query_from_params(params, self.config)
# For signatures mode, we need to collect more data with facets
if params["data_type"] == "signatures":
# No need to get crashes, we only want signatures
query["size"] = 0
query["from"] = 0
# Using a fixed number instead of the needed number.
# This hack limits the number of distinct signatures to process,
# and hugely improves performances with long queries.
query["facets"] = Search.get_signatures_facet(
self.config.searchMaxNumberOfDistinctSignatures)
json_query = json.dumps(query)
logger.debug("Query the crashes or signatures: %s", json_query)
es_result = self.query(params["from_date"], params["to_date"],
json_query)
# Executing the query and returning the result
if params["data_type"] == "signatures":
return self.search_for_signatures(params, es_result, query)
else:
return es_result
def get(self, **kwargs):
filters = [
("report_types", None, ["list", "str"]),
("report_type", None, "str"),
("signature", None, "str"),
("start_date", None, "datetime"),
("end_date", None, "datetime"),
("versions", None, ["list", "str"]),
]
params = external_common.parse_arguments(filters, kwargs)
if not params.get('report_types') and params.get('report_type'):
# to support the legacy case
individual_report = True
report_types = [params['report_type']]
else:
individual_report = False
report_types = params['report_types']
# check that all the report types are recognized
for report_type in report_types:
query_params = report_type_sql.get(report_type, {})
known_report_types = ('products', 'distinct_install',
'exploitability', 'devices', 'graphics')
if (report_type not in known_report_types
and 'first_col' not in query_params):
raise BadArgumentError(report_type)
products = []
versions = []
# Get information about the versions
util_service = Util(config=self.context)
versions_info = util_service.versions_info(**params)
if versions_info:
for i, elem in enumerate(versions_info):
products.append(versions_info[elem]["product_name"])
versions.append(str(versions_info[elem]["version_string"]))
# This MUST be a tuple otherwise it gets cast to an array
params['product'] = tuple(products)
params['version'] = tuple(versions)
all_results = {}
with self.get_connection() as connection:
for report_type in report_types:
result_cols, query_string, query_parameters = self._get_query(
report_type, params)
sql_results = self.query(query_string,
params=query_parameters,
connection=connection)
results = [dict(zip(result_cols, row)) for row in sql_results]
all_results[report_type] = results
if individual_report:
return all_results.values()[0]
else:
return {'reports': all_results}
def test_versions_info(self):
"""Test function which returns information about versions"""
util_service = Util(config=self.config)
# Test Firefox version
param = {"versions": "Firefox:8.0"}
expected = {
'Firefox:8.0': {
'product_version_id': 1,
'product_name': 'Firefox',
'version_string': '8.0',
'major_version': '8.0',
'release_channel': 'Release',
'build_id': [1],
'is_rapid_beta': False,
'is_from_rapid_beta': False,
'from_beta_version': 'Firefox:8.0',
}
}
versions_info = util_service.versions_info(**param)
self.assertEqual(versions_info, expected)
# Test Fennec version
param = {"versions": "Fennec:12.0b1"}
expected = {
'Fennec:12.0b1': {
'product_version_id': 3,
'product_name': 'Fennec',
'version_string': '12.0b1',
'major_version': '12.0',
'release_channel': 'Beta',
'build_id': [3],
'is_rapid_beta': False,
'is_from_rapid_beta': False,
'from_beta_version': 'Fennec:12.0b1',
}
}
versions_info = util_service.versions_info(**param)
self.assertEqual(versions_info, expected)
# Test empty versions
param = {"versions": ""}
expected = None
versions_info = util_service.versions_info(**param)
self.assertEqual(versions_info, expected)
# Test wrong product names
param = {"versions": ["Firefox:99.9", "Scoobidoo:99.9"]}
expected = {}
versions_info = util_service.versions_info(**param)
self.assertEqual(versions_info, expected)
def get(self, **kwargs):
"""
Search for crashes and return them.
See http://socorro.readthedocs.org/en/latest/middleware.html#search
Optional arguments: see SearchCommon.get_parameters()
"""
# change aliases from the web to the implementation's need
if "for" in kwargs and "terms" not in kwargs:
kwargs["terms"] = kwargs.get("for")
if "from" in kwargs and "from_date" not in kwargs:
kwargs["from_date"] = kwargs.get("from")
if "to" in kwargs and "to_date" not in kwargs:
kwargs["to_date"] = kwargs.get("to")
if "in" in kwargs and "fields" not in kwargs:
kwargs["fields"] = kwargs.get("in")
params = search_common.get_parameters(kwargs)
# Get information about the versions
versions_service = Util(config=self.context)
params["versions_info"] = versions_service.versions_info(**params)
query = Search.build_query_from_params(params, self.config)
# For signatures mode, we need to collect more data with facets
if params["data_type"] == "signatures":
# No need to get crashes, we only want signatures
query["size"] = 0
query["from"] = 0
# Using a fixed number instead of the needed number.
# This hack limits the number of distinct signatures to process,
# and hugely improves performances with long queries.
query["facets"] = Search.get_signatures_facet(
self.config.searchMaxNumberOfDistinctSignatures
)
json_query = json.dumps(query)
logger.debug("Query the crashes or signatures: %s", json_query)
es_result = self.query(params["from_date"],
params["to_date"],
json_query)
# Executing the query and returning the result
if params["data_type"] == "signatures":
return self.search_for_signatures(params, es_result, query)
else:
return es_result
def prepare_search_params(self, **kwargs):
"""Return a dictionary of parameters for a search-like SQL query.
Uses socorro.lib.search_common.get_parameters() for arguments
filtering.
"""
params = search_common.get_parameters(kwargs)
if not params["signature"]:
raise MissingOrBadArgumentError(
"Mandatory parameter 'signature' is missing or empty"
)
params["terms"] = params["signature"]
params["search_mode"] = "is_exactly"
# Default mode falls back to starts_with for postgres
if params["plugin_search_mode"] == "default":
params["plugin_search_mode"] = "starts_with"
# Searching for terms in plugins
if params["report_process"] == "plugin" and params["plugin_terms"]:
params["plugin_terms"] = " ".join(params["plugin_terms"])
params["plugin_terms"] = Crashes.prepare_terms(
params["plugin_terms"],
params["plugin_search_mode"]
)
# Get information about the versions
util_service = Util(config=self.context)
params["versions_info"] = util_service.versions_info(**params)
# Parsing the versions
params["versions_string"] = params["versions"]
(params["versions"], params["products"]) = Crashes.parse_versions(
params["versions"],
params["products"]
)
# Changing the OS ids to OS names
if hasattr(self.context, 'webapi'):
context = self.context.webapi
else:
# old middleware
context = self.context
for i, elem in enumerate(params["os"]):
for platform in context.platforms:
if platform["id"] == elem:
params["os"][i] = platform["name"]
return params
def prepare_search_params(self, **kwargs):
"""Return a dictionary of parameters for a search-like SQL query.
Uses socorro.lib.search_common.get_parameters() for arguments
filtering.
"""
params = search_common.get_parameters(kwargs)
if not params["signature"]:
raise MissingArgumentError('signature')
params["terms"] = params["signature"]
params["search_mode"] = "is_exactly"
# Default mode falls back to starts_with for postgres
if params["plugin_search_mode"] == "default":
params["plugin_search_mode"] = "starts_with"
# Searching for terms in plugins
if params["report_process"] == "plugin" and params["plugin_terms"]:
params["plugin_terms"] = " ".join(params["plugin_terms"])
params["plugin_terms"] = Crashes.prepare_terms(
params["plugin_terms"],
params["plugin_search_mode"]
)
# Get information about the versions
util_service = Util(config=self.context)
params["versions_info"] = util_service.versions_info(**params)
# Parsing the versions
params["versions_string"] = params["versions"]
(params["versions"], params["products"]) = Crashes.parse_versions(
params["versions"],
params["products"]
)
# Changing the OS ids to OS names
if hasattr(self.context, 'webapi'):
context = self.context.webapi
else:
# old middleware
context = self.context
for i, elem in enumerate(params["os"]):
for platform in context.platforms:
if platform["id"] == elem:
params["os"][i] = platform["name"]
return params
def search(self, **kwargs):
"""
Search for crashes and return them.
See http://socorro.readthedocs.org/en/latest/middleware.html#search
Optional arguments: see SearchCommon.get_parameters()
"""
params = search_common.get_parameters(kwargs)
# Get information about the versions
versions_service = Util(config=self.context)
params["versions_info"] = versions_service.versions_info(**params)
query = Search.build_query_from_params(params, self.context)
# For signatures mode, we need to collect more data with facets
if params["data_type"] == "signatures":
# No need to get crashes, we only want signatures
query["size"] = 0
query["from"] = 0
# Using a fixed number instead of the needed number.
# This hack limits the number of distinct signatures to process,
# and hugely improves performances with long queries.
try:
context = self.context.webapi
except KeyError:
# old middleware
context = self.context
query["facets"] = Search.get_signatures_facet(
context.searchMaxNumberOfDistinctSignatures)
json_query = json.dumps(query)
logger.debug("Query the crashes or signatures: %s", json_query)
es_result = self.query(params["from_date"],
params["to_date"],
json_query)
# Executing the query and returning the result
if params["data_type"] == "signatures":
return self.search_for_signatures(params, es_result, query)
else:
return es_result
def test_versions_info_with_rapid_betas(self):
"""Test that versions_info returns consistent data about rapid beta
versions. """
util_service = Util(config=self.config)
param = {"versions": "WaterWolf:3.0b"}
expected = {
'WaterWolf:3.0b': {
'product_version_id': 5,
'product_name': 'WaterWolf',
'version_string': '3.0b',
'major_version': '3.0b',
'release_channel': 'Beta',
'build_id': [5],
'is_rapid_beta': True,
'is_from_rapid_beta': True,
'from_beta_version': 'WaterWolf:3.0b',
},
'WaterWolf:3.0b1': {
'product_version_id': 6,
'product_name': 'WaterWolf',
'version_string': '3.0b1',
'major_version': '3.0b',
'release_channel': 'Beta',
'build_id': [6],
'is_rapid_beta': False,
'is_from_rapid_beta': True,
'from_beta_version': 'WaterWolf:3.0b',
},
'WaterWolf:3.0b2': {
'product_version_id': 7,
'product_name': 'WaterWolf',
'version_string': '3.0b2',
'major_version': '3.0b',
'release_channel': 'Beta',
'build_id': [7],
'is_rapid_beta': False,
'is_from_rapid_beta': True,
'from_beta_version': 'WaterWolf:3.0b',
}
}
versions_info = util_service.versions_info(**param)
self.assertEqual(versions_info, expected)
def test_versions_info_with_rapid_betas(self):
"""Test that versions_info returns consistent data about rapid beta
versions. """
util_service = Util(config=self.config)
param = {"versions": "WaterWolf:3.0b"}
expected = {
'WaterWolf:3.0b': {
'product_version_id': 5,
'product_name': 'WaterWolf',
'version_string': '3.0b',
'major_version': '3.0b',
'release_channel': 'Beta',
'build_id': [5],
'is_rapid_beta': True,
'is_from_rapid_beta': True,
'from_beta_version': 'WaterWolf:3.0b',
},
'WaterWolf:3.0b1': {
'product_version_id': 6,
'product_name': 'WaterWolf',
'version_string': '3.0b1',
'major_version': '3.0b',
'release_channel': 'Beta',
'build_id': [6],
'is_rapid_beta': False,
'is_from_rapid_beta': True,
'from_beta_version': 'WaterWolf:3.0b',
},
'WaterWolf:3.0b2': {
'product_version_id': 7,
'product_name': 'WaterWolf',
'version_string': '3.0b2',
'major_version': '3.0b',
'release_channel': 'Beta',
'build_id': [7],
'is_rapid_beta': False,
'is_from_rapid_beta': True,
'from_beta_version': 'WaterWolf:3.0b',
}
}
versions_info = util_service.versions_info(**param)
eq_(versions_info, expected)
def test_versions_info(self):
"""Test function which returns information about versions"""
util_service = Util(config=self.config)
# Test Firefox version
param = {"versions": "Firefox:8.0"}
expected = {
'Firefox:8.0': {
'product_version_id': 1,
'product_name': 'Firefox',
'version_string': '8.0',
'major_version': '8.0',
'release_channel': 'Release',
'build_id': [1],
'is_rapid_beta': False,
'is_from_rapid_beta': False,
'from_beta_version': 'Firefox:8.0',
}
}
versions_info = util_service.versions_info(**param)
eq_(versions_info, expected)
# Test Fennec version
param = {"versions": "Fennec:12.0b1"}
expected = {
'Fennec:12.0b1': {
'product_version_id': 3,
'product_name': 'Fennec',
'version_string': '12.0b1',
'major_version': '12.0',
'release_channel': 'Beta',
'build_id': [3],
'is_rapid_beta': False,
'is_from_rapid_beta': False,
'from_beta_version': 'Fennec:12.0b1',
}
}
versions_info = util_service.versions_info(**param)
eq_(versions_info, expected)
# Test empty versions
param = {"versions": ""}
expected = None
versions_info = util_service.versions_info(**param)
eq_(versions_info, expected)
# Test wrong product names
param = {"versions": ["Firefox:99.9", "Scoobidoo:99.9"]}
expected = {}
versions_info = util_service.versions_info(**param)
eq_(versions_info, expected)
def get_list(self, **kwargs):
"""
List all crashes with a given signature and return them.
Optional arguments: see SearchCommon.get_parameters()
"""
# aliases
if "from" in kwargs and "from_date" not in kwargs:
kwargs["from_date"] = kwargs.get("from")
if "to" in kwargs and "to_date" not in kwargs:
kwargs["to_date"] = kwargs.get("to")
params = search_common.get_parameters(kwargs)
if params["signature"] is None:
return None
params["terms"] = params["signature"]
params["search_mode"] = "is_exactly"
# Default mode falls back to starts_with for postgres
if params["plugin_search_mode"] == "default":
params["plugin_search_mode"] = "starts_with"
# Limiting to a signature
if params["terms"]:
params["terms"] = self.prepare_terms(params["terms"],
params["search_mode"])
# Searching for terms in plugins
if params["report_process"] == "plugin" and params["plugin_terms"]:
params["plugin_terms"] = " ".join(params["plugin_terms"])
params["plugin_terms"] = self.prepare_terms(
params["plugin_terms"], params["plugin_search_mode"])
# Get information about the versions
util_service = Util(config=self.context)
params["versions_info"] = util_service.versions_info(**params)
# Parsing the versions
params["versions_string"] = params["versions"]
(params["versions"],
params["products"]) = self.parse_versions(params["versions"],
params["products"])
if hasattr(self.context, 'webapi'):
context = self.context.webapi
else:
# old middleware
context = self.context
# Changing the OS ids to OS names
for i, elem in enumerate(params["os"]):
for platform in context.platforms:
if platform["id"] == elem:
params["os"][i] = platform["name"]
# Creating the parameters for the sql query
sql_params = {}
# Preparing the different parts of the sql query
sql_select = """
SELECT
r.date_processed,
r.uptime,
r.user_comments,
r.uuid,
r.product,
r.version,
r.build,
r.signature,
r.url,
r.os_name,
r.os_version,
r.cpu_name,
r.cpu_info,
r.address,
r.reason,
r.last_crash,
r.install_age,
r.hangid,
r.process_type,
(r.client_crash_date - (r.install_age * INTERVAL '1 second'))
AS install_time,
rd.duplicate_of
"""
sql_from = self.build_reports_sql_from(params)
sql_from = """%s
LEFT OUTER JOIN reports_duplicates rd ON r.uuid = rd.uuid
""" % sql_from
(sql_where,
sql_params) = self.build_reports_sql_where(params, sql_params,
self.context)
sql_order = """
ORDER BY r.date_processed DESC
"""
(sql_limit,
sql_params) = self.build_reports_sql_limit(params, sql_params)
# Assembling the query
sql_query = " ".join(
("/* socorro.external.postgresql.report.Report.list */",
sql_select, sql_from, sql_where, sql_order, sql_limit))
# Query for counting the results
sql_count_query = " ".join(
("/* socorro.external.postgresql.report.Report.list */",
"SELECT count(*)", sql_from, sql_where))
# Querying the DB
try:
connection = self.database.connection()
total = self.count(
sql_count_query,
sql_params,
error_message="Failed to count crashes from PostgreSQL.",
connection=connection)
results = []
# No need to call Postgres if we know there will be no results
if total != 0:
results = self.query(
sql_query,
sql_params,
error_message="Failed to retrieve crashes from PostgreSQL",
connection=connection)
except psycopg2.Error:
raise DatabaseError("Failed to retrieve crashes from PostgreSQL")
finally:
if connection:
connection.close()
# Transforming the results into what we want
crashes = []
for row in results:
crash = dict(
zip(("date_processed", "uptime", "user_comments", "uuid",
"product", "version", "build", "signature", "url",
"os_name", "os_version", "cpu_name", "cpu_info",
"address", "reason", "last_crash", "install_age",
"hangid", "process_type", "install_time", "duplicate_of"),
row))
for i in crash:
try:
crash[i] = datetimeutil.date_to_string(crash[i])
except TypeError:
pass
crashes.append(crash)
return {"hits": crashes, "total": total}
def get_list(self, **kwargs):
"""
List all crashes with a given signature and return them.
Optional arguments: see SearchCommon.get_parameters()
"""
# Creating the connection to the DB
self.connection = self.database.connection()
cur = self.connection.cursor()
params = search_common.get_parameters(kwargs)
if params["signature"] is None:
return None
params["terms"] = params["signature"]
params["search_mode"] = "is_exactly"
# Default mode falls back to starts_with for postgres
if params["plugin_search_mode"] == "default":
params["plugin_search_mode"] = "starts_with"
# Limiting to a signature
if params["terms"]:
params["terms"] = self.prepare_terms(params["terms"],
params["search_mode"])
# Searching for terms in plugins
if params["report_process"] == "plugin" and params["plugin_terms"]:
params["plugin_terms"] = " ".join(params["plugin_terms"])
params["plugin_terms"] = self.prepare_terms(
params["plugin_terms"],
params["plugin_search_mode"])
# Get information about the versions
util_service = Util(config=self.context)
params["versions_info"] = util_service.versions_info(**params)
# Parsing the versions
params["versions_string"] = params["versions"]
(params["versions"], params["products"]) = self.parse_versions(
params["versions"],
params["products"])
# Changing the OS ids to OS names
for i, elem in enumerate(params["os"]):
for platform in self.context.platforms:
if platform["id"] == elem:
params["os"][i] = platform["name"]
# Creating the parameters for the sql query
sql_params = {
}
# Preparing the different parts of the sql query
sql_select = """
SELECT
r.date_processed,
r.uptime,
r.user_comments,
r.uuid,
r.product,
r.version,
r.build,
r.signature,
r.url,
r.os_name,
r.os_version,
r.cpu_name,
r.cpu_info,
r.address,
r.reason,
r.last_crash,
r.install_age,
r.hangid,
r.process_type,
(r.client_crash_date - (r.install_age * INTERVAL '1 second'))
AS install_time,
rd.duplicate_of
"""
sql_from = self.build_reports_sql_from(params)
sql_from = """%s
LEFT OUTER JOIN reports_duplicates rd ON r.uuid = rd.uuid
""" % sql_from
(sql_where, sql_params) = self.build_reports_sql_where(params,
sql_params,
self.context)
sql_order = """
ORDER BY r.date_processed DESC
"""
(sql_limit, sql_params) = self.build_reports_sql_limit(params,
sql_params)
# Assembling the query
sql_query = " ".join((
"/* socorro.external.postgresql.report.Report.list */",
sql_select, sql_from, sql_where, sql_order, sql_limit))
# Query for counting the results
sql_count_query = " ".join((
"/* socorro.external.postgresql.report.Report.list */",
"SELECT count(*)", sql_from, sql_where))
# Debug
logger.debug(sql_count_query)
logger.debug(cur.mogrify(sql_count_query, sql_params))
# Querying the DB
try:
total = db.singleValueSql(cur, sql_count_query, sql_params)
except db.SQLDidNotReturnSingleValue:
total = 0
util.reportExceptionAndContinue(logger)
results = []
# No need to call Postgres if we know there will be no results
if total != 0:
try:
results = db.execute(cur, sql_query, sql_params)
except psycopg2.Error:
util.reportExceptionAndContinue(logger)
json_result = {
"total": total,
"hits": []
}
# Transforming the results into what we want
for crash in results:
row = dict(zip((
"date_processed",
"uptime",
"user_comments",
"uuid",
"product",
"version",
"build",
"signature",
"url",
"os_name",
"os_version",
"cpu_name",
"cpu_info",
"address",
"reason",
"last_crash",
"install_age",
"hangid",
"process_type",
"install_time",
"duplicate_of"), crash))
for i in row:
if isinstance(row[i], datetime.datetime):
row[i] = str(row[i])
json_result["hits"].append(row)
self.connection.close()
return json_result
def get(self, **kwargs):
filters = [
("report_type", None, "str"),
("signature", None, "str"),
("start_date", None, "datetime"),
("end_date", None, "datetime"),
("versions", None, ["list", "str"]),
]
params = external_common.parse_arguments(filters, kwargs)
products = []
versions = []
# Get information about the versions
util_service = Util(config=self.context)
versions_info = util_service.versions_info(**params)
if versions_info:
for i, elem in enumerate(versions_info):
products.append(versions_info[elem]["product_name"])
versions.append(str(versions_info[elem]["product_version_id"]))
params['versions'] = versions
params['product'] = products
if params['versions'] and params['report_type'] is not 'products':
glue = ','
version_search = ' AND reports_clean.product_version_id IN (%s)'
version_search = version_search % glue.join(params['versions'])
else:
version_search = ''
if params['product'] and params['report_type'] is not 'products':
glue = ','
product_list = ' AND product_name IN %s'
else:
product_list = ''
query_params = report_type_sql.get(params['report_type'], {})
if (params['report_type'] != 'products'
and 'first_col' not in query_params):
raise Exception('Invalid report type')
self.connection = self.database.connection()
cursor = self.connection.cursor()
if params['report_type'] == 'products':
result_cols = [
'product_name', 'version_string', 'report_count', 'percentage'
]
query_string = """WITH counts AS (
SELECT product_version_id, product_name, version_string,
count(*) AS report_count
FROM reports_clean
JOIN product_versions USING (product_version_id)
WHERE
signature_id = (SELECT signature_id FROM signatures
WHERE signature = %s)
AND date_processed >= %s
AND date_processed < %s
GROUP BY product_version_id, product_name, version_string
),
totals as (
SELECT product_version_id, product_name, version_string,
report_count,
sum(report_count) OVER () as total_count
FROM counts
)
SELECT product_name, version_string,
report_count::INT,
round((report_count * 100::numeric)/total_count,3)::TEXT
as percentage
FROM totals
ORDER BY report_count DESC"""
query_parameters = (params['signature'], params['start_date'],
params['end_date'])
else:
result_cols = ['category', 'report_count', 'percentage']
query_string = ["""WITH counts AS ( SELECT """]
query_string.append(query_params['first_col'])
query_string.append(""" as category, count(*) AS report_count
FROM reports_clean
JOIN product_versions USING (product_version_id)
""")
query_string.append(query_params.get('extra_join', ''))
query_string.append("""
WHERE
signature_id = (SELECT signature_id FROM signatures
WHERE signature = %s)
AND date_processed >= %s
AND date_processed < %s
""")
query_string.append(product_list)
query_string.append(version_search)
query_string.append(""" GROUP BY """)
query_string.append(query_params['first_col'])
query_string.append("""),
totals as (
SELECT category, report_count,
sum(report_count) OVER () as total_count
FROM counts
)
SELECT """)
query_string.append(query_params['first_col_format'])
query_string.append(""",
report_count::INT,
round((report_count::numeric)/total_count,5)::TEXT
as percentage
FROM totals
ORDER BY report_count DESC""")
query_string = " ".join(query_string)
query_parameters = [
params['signature'],
params['start_date'],
params['end_date'],
]
if (product_list):
# This MUST be a tuple otherwise it gets cast to an array.
query_parameters.append(tuple(params['product']))
query_parameters = tuple(query_parameters)
sql_results = db.execute(cursor, query_string, query_parameters)
results = []
for row in sql_results:
newrow = dict(zip(result_cols, row))
results.append(newrow)
return results
def get_list(self, **kwargs):
"""
List all crashes with a given signature and return them.
Both `from_date` and `to_date` (and their aliases `from` and `to`)
are required and can not be greater than 30 days apart.
Optional arguments: see SearchCommon.get_parameters()
"""
# aliases
if "from" in kwargs and "from_date" not in kwargs:
kwargs["from_date"] = kwargs.get("from")
if "to" in kwargs and "to_date" not in kwargs:
kwargs["to_date"] = kwargs.get("to")
if not kwargs.get('from_date'):
raise MissingArgumentError('from_date')
if not kwargs.get('to_date'):
raise MissingArgumentError('to_date')
from_date = datetimeutil.datetimeFromISOdateString(kwargs['from_date'])
to_date = datetimeutil.datetimeFromISOdateString(kwargs['to_date'])
span_days = (to_date - from_date).days
if span_days > 30:
raise BadArgumentError(
'Span between from_date and to_date can not be more than 30')
# start with the default
sort_order = {'key': 'date_processed', 'direction': 'DESC'}
if 'sort' in kwargs:
sort_order['key'] = kwargs.pop('sort')
_recognized_sort_orders = (
'date_processed',
'uptime',
'user_comments',
'uuid',
'uuid_text',
'product',
'version',
'build',
'signature',
'url',
'os_name',
'os_version',
'cpu_name',
'cpu_info',
'address',
'reason',
'last_crash',
'install_age',
'hangid',
'process_type',
'release_channel',
'install_time',
'duplicate_of',
)
if sort_order['key'] not in _recognized_sort_orders:
raise BadArgumentError(
'%s is not a recognized sort order key' %
sort_order['key'])
sort_order['direction'] = 'ASC'
if 'reverse' in kwargs:
if kwargs.pop('reverse'):
sort_order['direction'] = 'DESC'
include_raw_crash = kwargs.get('include_raw_crash') or False
params = search_common.get_parameters(kwargs)
if not params["signature"]:
raise MissingArgumentError('signature')
params["terms"] = params["signature"]
params["search_mode"] = "is_exactly"
# Default mode falls back to starts_with for postgres
if params["plugin_search_mode"] == "default":
params["plugin_search_mode"] = "starts_with"
# Limiting to a signature
if params["terms"]:
params["terms"] = self.prepare_terms(params["terms"],
params["search_mode"])
# Searching for terms in plugins
if params["report_process"] == "plugin" and params["plugin_terms"]:
params["plugin_terms"] = " ".join(params["plugin_terms"])
params["plugin_terms"] = self.prepare_terms(
params["plugin_terms"], params["plugin_search_mode"])
# Get information about the versions
util_service = Util(config=self.context)
params["versions_info"] = util_service.versions_info(**params)
# Parsing the versions
params["versions_string"] = params["versions"]
(params["versions"],
params["products"]) = self.parse_versions(params["versions"],
params["products"])
if hasattr(self.context, 'webapi'):
context = self.context.webapi
else:
# old middleware
context = self.context
# Changing the OS ids to OS names
for i, elem in enumerate(params["os"]):
for platform in context.platforms:
if platform["id"][:3] == elem[:3]:
params["os"][i] = platform["name"]
# Creating the parameters for the sql query
sql_params = {}
# Preparing the different parts of the sql query
sql_select = """
SELECT
r.date_processed,
r.uptime,
r.user_comments,
r.uuid::uuid,
r.uuid as uuid_text,
r.product,
r.version,
r.build,
r.signature,
r.url,
r.os_name,
r.os_version,
r.cpu_name,
r.cpu_info,
r.address,
r.reason,
r.last_crash,
r.install_age,
r.hangid,
r.process_type,
r.release_channel,
(r.client_crash_date - (r.install_age * INTERVAL '1 second'))
AS install_time
"""
if include_raw_crash:
pass
else:
sql_select += """
, rd.duplicate_of
"""
wrapped_select = """
WITH report_slice AS (
%s
), dupes AS (
SELECT
report_slice.uuid,
rd.duplicate_of
FROM reports_duplicates rd
JOIN report_slice ON report_slice.uuid_text = rd.uuid
WHERE
rd.date_processed BETWEEN %%(from_date)s AND %%(to_date)s
)
SELECT
rs.*,
dupes.duplicate_of,
rc.raw_crash
FROM report_slice rs
LEFT OUTER JOIN dupes USING (uuid)
LEFT OUTER JOIN raw_crashes rc ON
rs.uuid = rc.uuid
AND
rc.date_processed BETWEEN %%(from_date)s AND %%(to_date)s
"""
sql_from = self.build_reports_sql_from(params)
if not include_raw_crash:
sql_from = """%s
LEFT OUTER JOIN reports_duplicates rd ON r.uuid = rd.uuid
""" % sql_from
sql_where, sql_params = self.build_reports_sql_where(
params, sql_params, self.context)
sql_order = """
ORDER BY %(key)s %(direction)s
""" % sort_order
sql_limit, sql_params = self.build_reports_sql_limit(
params, sql_params)
# Assembling the query
if include_raw_crash:
sql_query = "\n".join(
("/* socorro.external.postgresql.report.Report.list */",
sql_select, sql_from, sql_where, sql_order, sql_limit))
else:
sql_query = "\n".join(
("/* socorro.external.postgresql.report.Report.list */",
sql_select, sql_from, sql_where, sql_order, sql_limit))
# Query for counting the results
sql_count_query = "\n".join(
("/* socorro.external.postgresql.report.Report.list */",
"SELECT count(*)", sql_from, sql_where))
# Querying the DB
with self.get_connection() as connection:
total = self.count(
sql_count_query,
sql_params,
error_message="Failed to count crashes from reports.",
connection=connection)
# No need to call Postgres if we know there will be no results
if total:
if include_raw_crash:
sql_query = wrapped_select % sql_query
results = self.query(
sql_query,
sql_params,
error_message="Failed to retrieve crashes from reports",
connection=connection)
else:
results = []
# Transforming the results into what we want
fields = (
"date_processed",
"uptime",
"user_comments",
"uuid",
"uuid", # the uuid::text one
"product",
"version",
"build",
"signature",
"url",
"os_name",
"os_version",
"cpu_name",
"cpu_info",
"address",
"reason",
"last_crash",
"install_age",
"hangid",
"process_type",
"release_channel",
"install_time",
"duplicate_of",
)
if include_raw_crash:
fields += ("raw_crash", )
crashes = []
for row in results:
crash = dict(zip(fields, row))
if include_raw_crash and crash['raw_crash']:
crash['raw_crash'] = json.loads(crash['raw_crash'])
for i in crash:
try:
crash[i] = datetimeutil.date_to_string(crash[i])
except TypeError:
pass
crashes.append(crash)
return {"hits": crashes, "total": total}
def get_list(self, **kwargs):
"""
List all crashes with a given signature and return them.
Optional arguments: see SearchCommon.get_parameters()
"""
# aliases
if "from" in kwargs and "from_date" not in kwargs:
kwargs["from_date"] = kwargs.get("from")
if "to" in kwargs and "to_date" not in kwargs:
kwargs["to_date"] = kwargs.get("to")
params = search_common.get_parameters(kwargs)
if not params["signature"]:
raise MissingOrBadArgumentError("Mandatory parameter 'signature' is missing or empty")
params["terms"] = params["signature"]
params["search_mode"] = "is_exactly"
# Default mode falls back to starts_with for postgres
if params["plugin_search_mode"] == "default":
params["plugin_search_mode"] = "starts_with"
# Limiting to a signature
if params["terms"]:
params["terms"] = self.prepare_terms(params["terms"], params["search_mode"])
# Searching for terms in plugins
if params["report_process"] == "plugin" and params["plugin_terms"]:
params["plugin_terms"] = " ".join(params["plugin_terms"])
params["plugin_terms"] = self.prepare_terms(params["plugin_terms"], params["plugin_search_mode"])
# Get information about the versions
util_service = Util(config=self.context)
params["versions_info"] = util_service.versions_info(**params)
# Parsing the versions
params["versions_string"] = params["versions"]
(params["versions"], params["products"]) = self.parse_versions(params["versions"], params["products"])
if hasattr(self.context, "webapi"):
context = self.context.webapi
else:
# old middleware
context = self.context
# Changing the OS ids to OS names
for i, elem in enumerate(params["os"]):
for platform in context.platforms:
if platform["id"][:3] == elem[:3]:
params["os"][i] = platform["name"]
# Creating the parameters for the sql query
sql_params = {}
# Preparing the different parts of the sql query
sql_select = """
SELECT
r.date_processed,
r.uptime,
r.user_comments,
r.uuid,
r.product,
r.version,
r.build,
r.signature,
r.url,
r.os_name,
r.os_version,
r.cpu_name,
r.cpu_info,
r.address,
r.reason,
r.last_crash,
r.install_age,
r.hangid,
r.process_type,
(r.client_crash_date - (r.install_age * INTERVAL '1 second'))
AS install_time,
rd.duplicate_of
"""
sql_from = self.build_reports_sql_from(params)
sql_from = (
"""%s
LEFT OUTER JOIN reports_duplicates rd ON r.uuid = rd.uuid
"""
% sql_from
)
(sql_where, sql_params) = self.build_reports_sql_where(params, sql_params, self.context)
sql_order = """
ORDER BY r.date_processed DESC
"""
(sql_limit, sql_params) = self.build_reports_sql_limit(params, sql_params)
# Assembling the query
sql_query = " ".join(
(
"/* socorro.external.postgresql.report.Report.list */",
sql_select,
sql_from,
sql_where,
sql_order,
sql_limit,
)
)
# Query for counting the results
sql_count_query = " ".join(
("/* socorro.external.postgresql.report.Report.list */", "SELECT count(*)", sql_from, sql_where)
)
# Querying the DB
try:
connection = self.database.connection()
total = self.count(
sql_count_query,
sql_params,
error_message="Failed to count crashes from PostgreSQL.",
connection=connection,
)
results = []
# No need to call Postgres if we know there will be no results
if total != 0:
results = self.query(
sql_query,
sql_params,
error_message="Failed to retrieve crashes from PostgreSQL",
connection=connection,
)
except psycopg2.Error:
raise DatabaseError("Failed to retrieve crashes from PostgreSQL")
finally:
if connection:
connection.close()
# Transforming the results into what we want
crashes = []
for row in results:
crash = dict(
zip(
(
"date_processed",
"uptime",
"user_comments",
"uuid",
"product",
"version",
"build",
"signature",
"url",
"os_name",
"os_version",
"cpu_name",
"cpu_info",
"address",
"reason",
"last_crash",
"install_age",
"hangid",
"process_type",
"install_time",
"duplicate_of",
),
row,
)
)
for i in crash:
try:
crash[i] = datetimeutil.date_to_string(crash[i])
except TypeError:
pass
crashes.append(crash)
return {"hits": crashes, "total": total}
def get(self, **kwargs):
"""
Search for crashes and return them.
See http://socorro.readthedocs.org/en/latest/middleware.html#search
Optional arguments: see SearchCommon.get_parameters()
"""
# Creating the connection to the DB
self.connection = self.database.connection()
cur = self.connection.cursor()
params = search_common.get_parameters(kwargs)
# change aliases from the web to the implementation's need
if "for" in params and "terms" not in params:
params["terms"] = params.get("for")
if "from" in params and "from_date" not in params:
params["from_date"] = params.get("from")
if "to" in params and "to_date" not in params:
params["to_date"] = params.get("to")
if "in" in params and "fields" not in params:
params["fields"] = params.get("in")
# Default mode falls back to starts_with for postgres
if params["search_mode"] == "default":
params["search_mode"] = "starts_with"
if params["plugin_search_mode"] == "default":
params["plugin_search_mode"] = "starts_with"
# For Postgres, we never search for a list of terms
if params["terms"]:
params["terms"] = " ".join(params["terms"])
params["terms"] = Search.prepare_terms(params["terms"],
params["search_mode"])
# Searching for terms in plugins
if params["report_process"] == "plugin" and params["plugin_terms"]:
params["plugin_terms"] = " ".join(params["plugin_terms"])
params["plugin_terms"] = Search.prepare_terms(
params["plugin_terms"],
params["plugin_search_mode"])
# Get information about the versions
util_service = Util(config=self.context)
params["versions_info"] = util_service.versions_info(**params)
# Parsing the versions
params["versions_string"] = params["versions"]
(params["versions"], params["products"]) = Search.parse_versions(
params["versions"],
params["products"])
if hasattr(self.context, 'webapi'):
context = self.context.webapi
else:
# old middleware
context = self.context
# Changing the OS ids to OS names
for i, elem in enumerate(params["os"]):
for platform in context.platforms:
if platform["id"] == elem:
params["os"][i] = platform["name"]
# Creating the parameters for the sql query
sql_params = {
}
# Preparing the different parts of the sql query
sql_select = self.generate_sql_select(params)
# Adding count for each OS
for i in context.platforms:
sql_params["os_%s" % i["id"]] = i["name"]
sql_from = self.build_reports_sql_from(params)
(sql_where, sql_params) = self.build_reports_sql_where(params,
sql_params,
self.context)
sql_group = self.generate_sql_group(params)
sql_order = """
ORDER BY total DESC, signature
"""
(sql_limit, sql_params) = self.build_reports_sql_limit(params,
sql_params)
# Assembling the query
sql_query = " ".join(("/* socorro.search.Search search */",
sql_select, sql_from, sql_where, sql_group,
sql_order, sql_limit))
# Query for counting the results
sql_count_query = " ".join((
"/* socorro.external.postgresql.search.Search search.count */",
"SELECT count(DISTINCT r.signature)", sql_from, sql_where))
# Debug
logger.debug(cur.mogrify(sql_query, sql_params))
# Querying the DB
try:
total = db.singleValueSql(cur, sql_count_query, sql_params)
except db.SQLDidNotReturnSingleValue:
total = 0
util.reportExceptionAndContinue(logger)
results = []
# No need to call Postgres if we know there will be no results
if total != 0:
try:
results = db.execute(cur, sql_query, sql_params)
except psycopg2.Error:
util.reportExceptionAndContinue(logger)
json_result = {
"total": total,
"hits": []
}
# Transforming the results into what we want
for crash in results:
if params["report_process"] == "plugin":
row = dict(zip(("signature", "count", "is_windows", "is_mac",
"is_linux", "numhang", "numplugin",
"numcontent", "pluginname", "pluginversion",
"pluginfilename"), crash))
else:
row = dict(zip(("signature", "count", "is_windows", "is_mac",
"is_linux", "numhang", "numplugin",
"numcontent"), crash))
json_result["hits"].append(row)
self.connection.close()
return json_result
def get(self, **kwargs):
filters = [
("report_type", None, "str"),
("signature", None, "str"),
("start_date", None, "datetime"),
("end_date", None, "datetime"),
("versions", None, ["list", "str"]),
]
params = external_common.parse_arguments(filters, kwargs)
products = []
versions = []
# Get information about the versions
util_service = Util(config=self.context)
versions_info = util_service.versions_info(**params)
if versions_info:
for i, elem in enumerate(versions_info):
products.append(versions_info[elem]["product_name"])
versions.append(str(versions_info[elem]["product_version_id"]))
params['versions'] = versions
params['product'] = products
version_search = self.determineVersionSearchString(params)
if params['product'] and params['report_type'] is not 'products':
product_list = ' AND product_name IN %s'
else:
product_list = ''
query_params = report_type_sql.get(params['report_type'], {})
if (params['report_type'] not in
('products', 'distinct_install', 'exploitability')
and 'first_col' not in query_params):
raise Exception('Invalid report type')
self.connection = self.database.connection()
cursor = self.connection.cursor()
if params['report_type'] == 'products':
result_cols = ['product_name',
'version_string',
'report_count',
'percentage']
query_string = """WITH counts AS (
SELECT product_version_id, product_name, version_string,
count(*) AS report_count
FROM reports_clean
JOIN product_versions USING (product_version_id)
WHERE
signature_id = (SELECT signature_id FROM signatures
WHERE signature = %s)
AND date_processed >= %s
AND date_processed < %s
GROUP BY product_version_id, product_name, version_string
),
totals as (
SELECT product_version_id, product_name, version_string,
report_count,
sum(report_count) OVER () as total_count
FROM counts
)
SELECT product_name, version_string,
report_count::INT,
round((report_count * 100::numeric)/total_count,3)::TEXT
as percentage
FROM totals
ORDER BY report_count DESC"""
query_parameters = (params['signature'],
params['start_date'],
params['end_date'])
elif params['report_type'] == 'distinct_install':
result_cols = ['product_name',
'version_string',
'crashes',
'installations']
query_string = """
SELECT product_name, version_string,
count(*) AS crashes,
COUNT(DISTINCT client_crash_date - install_age) as
installations
FROM reports_clean
JOIN product_versions USING (product_version_id)
WHERE
signature_id = (SELECT signature_id FROM signatures
WHERE signature = %s)
AND date_processed >= %s
AND date_processed < %s
GROUP BY product_name, version_string
ORDER BY crashes DESC"""
query_parameters = (params['signature'],
params['start_date'],
params['end_date'])
elif params['report_type'] == 'exploitability':
result_cols = [
'report_date',
'null_count',
'none_count',
'low_count',
'medium_count',
'high_count',
]
query_string = """
SELECT
cast(report_date as TEXT),
null_count,
none_count,
low_count,
medium_count,
high_count
FROM exploitability_reports
WHERE
signature_id = (SELECT signature_id FROM signatures WHERE
signature = %s) AND
report_date >= %s AND
report_date < %s
ORDER BY report_date DESC
"""
query_parameters = (
params['signature'],
params['start_date'],
params['end_date'],
)
else:
result_cols = ['category', 'report_count', 'percentage']
results = self.generateGenericQueryString(
params=params,
query_params=query_params,
product_list=product_list,
version_search=version_search)
query_string = results['query_string']
query_parameters = results['query_parameters']
if(product_list):
# This MUST be a tuple otherwise it gets cast to an array.
query_parameters.append(tuple(params['product']))
query_parameters = tuple(query_parameters)
sql_results = db.execute(cursor, query_string, query_parameters)
results = []
for row in sql_results:
newrow = dict(zip(result_cols, row))
results.append(newrow)
return results
def get_list(self, **kwargs):
"""
List all crashes with a given signature and return them.
Both `from_date` and `to_date` (and their aliases `from` and `to`)
are required and can not be greater than 30 days apart.
Optional arguments: see SearchCommon.get_parameters()
"""
# aliases
if "from" in kwargs and "from_date" not in kwargs:
kwargs["from_date"] = kwargs.get("from")
if "to" in kwargs and "to_date" not in kwargs:
kwargs["to_date"] = kwargs.get("to")
if not kwargs.get('from_date'):
raise MissingArgumentError('from_date')
if not kwargs.get('to_date'):
raise MissingArgumentError('to_date')
from_date = datetimeutil.datetimeFromISOdateString(kwargs['from_date'])
to_date = datetimeutil.datetimeFromISOdateString(kwargs['to_date'])
span_days = (to_date - from_date).days
if span_days > 30:
raise BadArgumentError(
'Span between from_date and to_date can not be more than 30'
)
# start with the default
sort_order = {
'key': 'date_processed',
'direction': 'DESC'
}
if 'sort' in kwargs:
sort_order['key'] = kwargs.pop('sort')
_recognized_sort_orders = (
'date_processed',
'uptime',
'user_comments',
'uuid',
'uuid_text',
'product',
'version',
'build',
'signature',
'url',
'os_name',
'os_version',
'cpu_name',
'cpu_info',
'address',
'reason',
'last_crash',
'install_age',
'hangid',
'process_type',
'release_channel',
'install_time',
'duplicate_of',
)
if sort_order['key'] not in _recognized_sort_orders:
raise BadArgumentError(
'%s is not a recognized sort order key' % sort_order['key']
)
sort_order['direction'] = 'ASC'
if 'reverse' in kwargs:
if kwargs.pop('reverse'):
sort_order['direction'] = 'DESC'
include_raw_crash = kwargs.get('include_raw_crash') or False
params = search_common.get_parameters(kwargs)
if not params["signature"]:
raise MissingArgumentError('signature')
params["terms"] = params["signature"]
params["search_mode"] = "is_exactly"
# Default mode falls back to starts_with for postgres
if params["plugin_search_mode"] == "default":
params["plugin_search_mode"] = "starts_with"
# Limiting to a signature
if params["terms"]:
params["terms"] = self.prepare_terms(params["terms"],
params["search_mode"])
# Searching for terms in plugins
if params["report_process"] == "plugin" and params["plugin_terms"]:
params["plugin_terms"] = " ".join(params["plugin_terms"])
params["plugin_terms"] = self.prepare_terms(
params["plugin_terms"],
params["plugin_search_mode"]
)
# Get information about the versions
util_service = Util(config=self.context)
params["versions_info"] = util_service.versions_info(**params)
# Parsing the versions
params["versions_string"] = params["versions"]
(params["versions"], params["products"]) = self.parse_versions(
params["versions"],
params["products"]
)
if hasattr(self.context, 'webapi'):
context = self.context.webapi
else:
# old middleware
context = self.context
# Changing the OS ids to OS names
for i, elem in enumerate(params["os"]):
for platform in context.platforms:
if platform["id"][:3] == elem[:3]:
params["os"][i] = platform["name"]
# Creating the parameters for the sql query
sql_params = {
}
# Preparing the different parts of the sql query
sql_select = """
SELECT
r.date_processed,
r.uptime,
r.user_comments,
r.uuid::uuid,
r.uuid as uuid_text,
r.product,
r.version,
r.build,
r.signature,
r.url,
r.os_name,
r.os_version,
r.cpu_name,
r.cpu_info,
r.address,
r.reason,
r.last_crash,
r.install_age,
r.hangid,
r.process_type,
r.release_channel,
(r.client_crash_date - (r.install_age * INTERVAL '1 second'))
AS install_time
"""
if include_raw_crash:
pass
else:
sql_select += """
, rd.duplicate_of
"""
wrapped_select = """
WITH report_slice AS (
%s
), dupes AS (
SELECT
report_slice.uuid,
rd.duplicate_of
FROM reports_duplicates rd
JOIN report_slice ON report_slice.uuid_text = rd.uuid
WHERE
rd.date_processed BETWEEN %%(from_date)s AND %%(to_date)s
)
SELECT
rs.*,
dupes.duplicate_of,
rc.raw_crash
FROM report_slice rs
LEFT OUTER JOIN dupes USING (uuid)
LEFT OUTER JOIN raw_crashes rc ON
rs.uuid = rc.uuid
AND
rc.date_processed BETWEEN %%(from_date)s AND %%(to_date)s
"""
sql_from = self.build_reports_sql_from(params)
if not include_raw_crash:
sql_from = """%s
LEFT OUTER JOIN reports_duplicates rd ON r.uuid = rd.uuid
""" % sql_from
sql_where, sql_params = self.build_reports_sql_where(
params,
sql_params,
self.context
)
sql_order = """
ORDER BY %(key)s %(direction)s
""" % sort_order
sql_limit, sql_params = self.build_reports_sql_limit(
params,
sql_params
)
# Assembling the query
if include_raw_crash:
sql_query = "\n".join((
"/* socorro.external.postgresql.report.Report.list */",
sql_select, sql_from, sql_where, sql_order, sql_limit)
)
else:
sql_query = "\n".join((
"/* socorro.external.postgresql.report.Report.list */",
sql_select, sql_from, sql_where, sql_order, sql_limit)
)
# Query for counting the results
sql_count_query = "\n".join((
"/* socorro.external.postgresql.report.Report.list */",
"SELECT count(*)", sql_from, sql_where)
)
# Querying the DB
with self.get_connection() as connection:
total = self.count(
sql_count_query,
sql_params,
error_message="Failed to count crashes from reports.",
connection=connection
)
# No need to call Postgres if we know there will be no results
if total:
if include_raw_crash:
sql_query = wrapped_select % sql_query
results = self.query(
sql_query,
sql_params,
error_message="Failed to retrieve crashes from reports",
connection=connection
)
else:
results = []
# Transforming the results into what we want
fields = (
"date_processed",
"uptime",
"user_comments",
"uuid",
"uuid", # the uuid::text one
"product",
"version",
"build",
"signature",
"url",
"os_name",
"os_version",
"cpu_name",
"cpu_info",
"address",
"reason",
"last_crash",
"install_age",
"hangid",
"process_type",
"release_channel",
"install_time",
"duplicate_of",
)
if include_raw_crash:
fields += ("raw_crash",)
crashes = []
for row in results:
crash = dict(zip(fields, row))
for i in crash:
try:
crash[i] = datetimeutil.date_to_string(crash[i])
except TypeError:
pass
crashes.append(crash)
return {
"hits": crashes,
"total": total
}
def get(self, **kwargs):
filters = [
("report_types", None, ["list", "str"]),
("report_type", None, "str"),
("signature", None, "str"),
("start_date", None, "date"),
("end_date", None, "date"),
("versions", None, ["list", "str"]),
]
params = external_common.parse_arguments(filters, kwargs)
if not params.get('report_types') and params.get('report_type'):
# to support the legacy case
individual_report = True
report_types = [params['report_type']]
else:
individual_report = False
report_types = params['report_types']
# check that all the report types are recognized
for report_type in report_types:
query_params = report_type_sql.get(report_type, {})
known_report_types = (
'products',
'distinct_install',
'exploitability',
'devices',
'graphics'
)
if (
report_type not in known_report_types and
'first_col' not in query_params
):
raise BadArgumentError(report_type)
products = []
versions = []
# Get information about the versions
util_service = Util(config=self.context)
versions_info = util_service.versions_info(**params)
if versions_info:
for i, elem in enumerate(versions_info):
products.append(versions_info[elem]["product_name"])
versions.append(str(versions_info[elem]["version_string"]))
# This MUST be a tuple otherwise it gets cast to an array
params['product'] = tuple(products)
params['version'] = tuple(versions)
all_results = {}
assert isinstance(params['start_date'], datetime.date)
assert isinstance(params['end_date'], datetime.date)
with self.get_connection() as connection:
for report_type in report_types:
result_cols, query_string, query_parameters = self._get_query(
report_type,
params
)
sql_results = self.query(
query_string,
params=query_parameters,
connection=connection
)
results = [dict(zip(result_cols, row)) for row in sql_results]
all_results[report_type] = results
if individual_report:
return all_results.values()[0]
else:
return {'reports': all_results}
def get_list(self, **kwargs):
"""
List all crashes with a given signature and return them.
Optional arguments: see SearchCommon.get_parameters()
"""
# Creating the connection to the DB
self.connection = self.database.connection()
cur = self.connection.cursor()
params = search_common.get_parameters(kwargs)
if params["signature"] is None:
return None
params["terms"] = params["signature"]
params["search_mode"] = "is_exactly"
# Default mode falls back to starts_with for postgres
if params["plugin_search_mode"] == "default":
params["plugin_search_mode"] = "starts_with"
# Limiting to a signature
if params["terms"]:
params["terms"] = self.prepare_terms(params["terms"],
params["search_mode"])
# Searching for terms in plugins
if params["report_process"] == "plugin" and params["plugin_terms"]:
params["plugin_terms"] = " ".join(params["plugin_terms"])
params["plugin_terms"] = self.prepare_terms(
params["plugin_terms"], params["plugin_search_mode"])
# Get information about the versions
util_service = Util(config=self.context)
params["versions_info"] = util_service.versions_info(**params)
# Parsing the versions
params["versions_string"] = params["versions"]
(params["versions"],
params["products"]) = self.parse_versions(params["versions"],
params["products"])
# Changing the OS ids to OS names
for i, elem in enumerate(params["os"]):
for platform in self.context.platforms:
if platform["id"] == elem:
params["os"][i] = platform["name"]
# Creating the parameters for the sql query
sql_params = {}
# Preparing the different parts of the sql query
sql_select = """
SELECT
r.date_processed,
r.uptime,
r.user_comments,
r.uuid,
r.product,
r.version,
r.build,
r.signature,
r.url,
r.os_name,
r.os_version,
r.cpu_name,
r.cpu_info,
r.address,
r.reason,
r.last_crash,
r.install_age,
r.hangid,
r.process_type,
(r.client_crash_date - (r.install_age * INTERVAL '1 second'))
AS install_time,
rd.duplicate_of
"""
sql_from = self.build_reports_sql_from(params)
sql_from = """%s
LEFT OUTER JOIN reports_duplicates rd ON r.uuid = rd.uuid
""" % sql_from
(sql_where,
sql_params) = self.build_reports_sql_where(params, sql_params,
self.context)
sql_order = """
ORDER BY r.date_processed DESC
"""
(sql_limit,
sql_params) = self.build_reports_sql_limit(params, sql_params)
# Assembling the query
sql_query = " ".join(
("/* socorro.external.postgresql.report.Report.list */",
sql_select, sql_from, sql_where, sql_order, sql_limit))
# Query for counting the results
sql_count_query = " ".join(
("/* socorro.external.postgresql.report.Report.list */",
"SELECT count(*)", sql_from, sql_where))
# Debug
logger.debug(sql_count_query)
logger.debug(cur.mogrify(sql_count_query, sql_params))
# Querying the DB
try:
total = db.singleValueSql(cur, sql_count_query, sql_params)
except db.SQLDidNotReturnSingleValue:
total = 0
util.reportExceptionAndContinue(logger)
results = []
# No need to call Postgres if we know there will be no results
if total != 0:
try:
results = db.execute(cur, sql_query, sql_params)
except psycopg2.Error:
util.reportExceptionAndContinue(logger)
json_result = {"total": total, "hits": []}
# Transforming the results into what we want
for crash in results:
row = dict(
zip(("date_processed", "uptime", "user_comments", "uuid",
"product", "version", "build", "signature", "url",
"os_name", "os_version", "cpu_name", "cpu_info",
"address", "reason", "last_crash", "install_age",
"hangid", "process_type", "install_time", "duplicate_of"),
crash))
for i in row:
if isinstance(row[i], datetime.datetime):
row[i] = str(row[i])
json_result["hits"].append(row)
self.connection.close()
return json_result
def get(self, **kwargs):
"""
Search for crashes and return them.
See http://socorro.readthedocs.org/en/latest/middleware.html#search
Optional arguments: see SearchCommon.get_parameters()
"""
# change aliases from the web to the implementation's need
if "for" in kwargs and "terms" not in kwargs:
kwargs["terms"] = kwargs.get("for")
if "from" in kwargs and "from_date" not in kwargs:
kwargs["from_date"] = kwargs.get("from")
if "to" in kwargs and "to_date" not in kwargs:
kwargs["to_date"] = kwargs.get("to")
if "in" in kwargs and "fields" not in kwargs:
kwargs["fields"] = kwargs.get("in")
params = search_common.get_parameters(kwargs)
# Default mode falls back to starts_with for postgres
if params["search_mode"] == "default":
params["search_mode"] = "starts_with"
if params["plugin_search_mode"] == "default":
params["plugin_search_mode"] = "starts_with"
# For Postgres, we never search for a list of terms
if params["terms"]:
params["terms"] = " ".join(params["terms"])
params["terms"] = Search.prepare_terms(params["terms"],
params["search_mode"])
# Searching for terms in plugins
if params["report_process"] == "plugin" and params["plugin_terms"]:
params["plugin_terms"] = " ".join(params["plugin_terms"])
params["plugin_terms"] = Search.prepare_terms(
params["plugin_terms"],
params["plugin_search_mode"])
# Get information about the versions
util_service = Util(config=self.context)
params["versions_info"] = util_service.versions_info(**params)
# Parsing the versions
params["versions_string"] = params["versions"]
(params["versions"], params["products"]) = Search.parse_versions(
params["versions"],
params["products"])
if hasattr(self.context, 'webapi'):
context = self.context.webapi
else:
# old middleware
context = self.context
# Changing the OS ids to OS names
for i, elem in enumerate(params["os"]):
for platform in context.platforms:
if platform["id"][:3] == elem[:3]:
params["os"][i] = platform["name"]
# Creating the parameters for the sql query
sql_params = {}
# Preparing the different parts of the sql query
sql_select = self.generate_sql_select(params)
# Adding count for each OS
for i in context.platforms:
sql_params["os_%s" % i["id"]] = i["name"]
sql_from = self.build_reports_sql_from(params)
(sql_where, sql_params) = self.build_reports_sql_where(params,
sql_params,
context)
sql_group = self.generate_sql_group(params)
sql_order = """
ORDER BY total DESC, signature
"""
(sql_limit, sql_params) = self.build_reports_sql_limit(params,
sql_params)
# Assembling the query
sql_query = " ".join(("/* socorro.search.Search search */",
sql_select, sql_from, sql_where, sql_group,
sql_order, sql_limit))
# Query for counting the results
sql_count_query = " ".join((
"/* socorro.external.postgresql.search.Search search.count */",
"SELECT count(DISTINCT r.signature)", sql_from, sql_where))
# Querying the database
error_message = "Failed to retrieve crashes from PostgreSQL"
with self.get_connection() as connection:
try:
total = self.count(
sql_count_query,
sql_params,
error_message="Failed to count crashes from PostgreSQL.",
connection=connection
)
results = []
# No need to call Postgres if we know there will be no results
if total != 0:
results = self.query(
sql_query,
sql_params,
error_message=error_message,
connection=connection
)
except psycopg2.Error:
raise DatabaseError(error_message)
# Transforming the results into what we want
crashes = []
for row in results:
if params["report_process"] == "plugin":
crash = dict(zip((
"signature",
"count",
"is_windows",
"is_mac",
"is_linux",
"numhang",
"numplugin",
"numcontent",
"pluginname",
"pluginversion",
"pluginfilename"
), row))
else:
crash = dict(zip((
"signature",
"count",
"is_windows",
"is_mac",
"is_linux",
"numhang",
"numplugin",
"numcontent"
), row))
crashes.append(crash)
return {
"hits": crashes,
"total": total
}
def search(self, **kwargs):
"""
Search for crashes and return them.
See http://socorro.readthedocs.org/en/latest/middleware.html#search
Optional arguments: see SearchCommon.get_parameters()
"""
# Creating the connection to the DB
self.connection = self.database.connection()
cur = self.connection.cursor()
params = search_common.get_parameters(kwargs)
# Default mode falls back to starts_with for postgres
if params["search_mode"] == "default":
params["search_mode"] = "starts_with"
if params["plugin_search_mode"] == "default":
params["plugin_search_mode"] = "starts_with"
# For Postgres, we never search for a list of terms
if params["terms"]:
params["terms"] = " ".join(params["terms"])
params["terms"] = Search.prepare_terms(params["terms"],
params["search_mode"])
# Searching for terms in plugins
if params["report_process"] == "plugin" and params["plugin_terms"]:
params["plugin_terms"] = " ".join(params["plugin_terms"])
params["plugin_terms"] = Search.prepare_terms(
params["plugin_terms"], params["plugin_search_mode"])
# Get information about the versions
util_service = Util(config=self.context)
params["versions_info"] = util_service.versions_info(**params)
# Parsing the versions
params["versions_string"] = params["versions"]
(params["versions"],
params["products"]) = Search.parse_versions(params["versions"],
params["products"])
# Changing the OS ids to OS names
for i, elem in enumerate(params["os"]):
for platform in self.context.platforms:
if platform["id"] == elem:
params["os"][i] = platform["name"]
# Creating the parameters for the sql query
sql_params = {}
# Preparing the different parts of the sql query
sql_select = self.generate_sql_select(params)
# Adding count for each OS
for i in self.context.platforms:
sql_params["os_%s" % i["id"]] = i["name"]
sql_from = self.build_reports_sql_from(params)
(sql_where,
sql_params) = self.build_reports_sql_where(params, sql_params,
self.context)
sql_group = self.generate_sql_group(params)
sql_order = """
ORDER BY total DESC, signature
"""
(sql_limit,
sql_params) = self.build_reports_sql_limit(params, sql_params)
# Assembling the query
sql_query = " ".join(
("/* socorro.search.Search search */", sql_select, sql_from,
sql_where, sql_group, sql_order, sql_limit))
# Query for counting the results
sql_count_query = " ".join(
("/* socorro.external.postgresql.search.Search search.count */",
"SELECT count(DISTINCT r.signature)", sql_from, sql_where))
# Debug
logger.debug(cur.mogrify(sql_query, sql_params))
# Querying the DB
try:
total = db.singleValueSql(cur, sql_count_query, sql_params)
except db.SQLDidNotReturnSingleValue:
total = 0
util.reportExceptionAndContinue(logger)
results = []
# No need to call Postgres if we know there will be no results
if total != 0:
try:
results = db.execute(cur, sql_query, sql_params)
except psycopg2.Error:
util.reportExceptionAndContinue(logger)
json_result = {"total": total, "hits": []}
# Transforming the results into what we want
for crash in results:
if params["report_process"] == "plugin":
row = dict(
zip(("signature", "count", "is_windows", "is_mac",
"is_linux", "numhang", "numplugin", "numcontent",
"pluginname", "pluginversion", "pluginfilename"),
crash))
else:
row = dict(
zip(("signature", "count", "is_windows", "is_mac",
"is_linux", "numhang", "numplugin", "numcontent"),
crash))
json_result["hits"].append(row)
self.connection.close()
return json_result
def get(self, **kwargs):
filters = [
("report_type", None, "str"),
("signature", None, "str"),
("start_date", None, "datetime"),
("end_date", None, "datetime"),
("versions", None, ["list", "str"]),
]
params = external_common.parse_arguments(filters, kwargs)
# Decode double-encoded slashes in signature
if params["signature"] is not None:
params["signature"] = params["signature"].replace("%2F", "/")
products = []
versions = []
# Get information about the versions
util_service = Util(config=self.context)
versions_info = util_service.versions_info(**params)
if versions_info:
for i, elem in enumerate(versions_info):
products.append(versions_info[elem]["product_name"])
versions.append(str(versions_info[elem]["product_version_id"]))
params['versions'] = versions
params['product'] = products
if params['versions'] and params['report_type'] is not 'products':
glue = ','
version_search = ' AND reports_clean.product_version_id IN (%s)'
version_search = version_search % glue.join(params['versions'])
else:
version_search = ''
if params['product'] and params['report_type'] is not 'products':
glue = ','
product_list = ' AND product_name IN %s'
else:
product_list = ''
query_params = report_type_sql.get(params['report_type'], {})
if (params['report_type'] != 'products' and
'first_col' not in query_params):
raise Exception('Invalid report type')
self.connection = self.database.connection()
cursor = self.connection.cursor()
if params['report_type'] == 'products':
result_cols = ['product_name', 'version_string',
'report_count', 'percentage']
query_string = """WITH counts AS (
SELECT product_version_id, product_name, version_string,
count(*) AS report_count
FROM reports_clean
JOIN product_versions USING (product_version_id)
WHERE
signature_id = (SELECT signature_id FROM signatures
WHERE signature = %s)
AND date_processed >= %s
AND date_processed < %s
GROUP BY product_version_id, product_name, version_string
),
totals as (
SELECT product_version_id, product_name, version_string,
report_count,
sum(report_count) OVER () as total_count
FROM counts
)
SELECT product_name, version_string,
report_count::INT,
round((report_count * 100::numeric)/total_count,3)::TEXT
as percentage
FROM totals
ORDER BY report_count DESC"""
query_parameters = (params['signature'],
params['start_date'],
params['end_date'])
else:
result_cols = ['category', 'report_count', 'percentage']
query_string = ["""WITH counts AS ( SELECT """]
query_string.append(query_params['first_col'])
query_string.append(""" as category, count(*) AS report_count
FROM reports_clean
JOIN product_versions USING (product_version_id)
""")
query_string.append(query_params.get('extra_join', ''))
query_string.append("""
WHERE
signature_id = (SELECT signature_id FROM signatures
WHERE signature = %s)
AND date_processed >= %s
AND date_processed < %s
""")
query_string.append(product_list)
query_string.append(version_search)
query_string.append(""" GROUP BY """)
query_string.append(query_params['first_col'])
query_string.append("""),
totals as (
SELECT category, report_count,
sum(report_count) OVER () as total_count
FROM counts
)
SELECT """)
query_string.append(query_params['first_col_format'])
query_string.append(""",
report_count::INT,
round((report_count::numeric)/total_count,5)::TEXT
as percentage
FROM totals
ORDER BY report_count DESC""")
query_string = " ".join(query_string)
query_parameters = [params['signature'],
params['start_date'],
params['end_date'],
]
if(product_list):
# This MUST be a tuple otherwise it gets cast to an array.
query_parameters.append(tuple(params['product']))
query_parameters = tuple(query_parameters)
sql_results = db.execute(cursor, query_string, query_parameters)
results = []
for row in sql_results:
newrow = dict(zip(result_cols, row))
results.append(newrow)
return results
def get(self, **kwargs):
filters = [
("report_type", None, "str"),
("signature", None, "str"),
("start_date", None, "datetime"),
("end_date", None, "datetime"),
("versions", None, ["list", "str"]),
]
params = external_common.parse_arguments(filters, kwargs)
products = []
versions = []
# Get information about the versions
util_service = Util(config=self.context)
versions_info = util_service.versions_info(**params)
if versions_info:
for i, elem in enumerate(versions_info):
products.append(versions_info[elem]["product_name"])
versions.append(str(versions_info[elem]["version_string"]))
# This MUST be a tuple otherwise it gets cast to an array
params['product'] = tuple(products)
params['version'] = tuple(versions)
if params['product'] and params['report_type'] is not 'products':
product_list = ' AND product_name IN %s '
else:
product_list = ''
if params['version'] and params['report_type'] is not 'products':
version_list = ' AND version_string IN %s '
else:
version_list = ''
query_params = report_type_sql.get(params['report_type'], {})
if (params['report_type'] not in
('products', 'distinct_install', 'exploitability', 'devices',
'graphics')
and 'first_col' not in query_params):
raise BadArgumentError('report type')
self.connection = self.database.connection()
cursor = self.connection.cursor()
if params['report_type'] == 'products':
result_cols = ['product_name',
'version_string',
'report_count',
'percentage']
query_string = """
WITH crashes as (
SELECT
product_name as category
, version_string
, SUM(report_count) as report_count
FROM signature_summary_products
JOIN signatures USING (signature_id)
WHERE signatures.signature = %s
AND report_date >= %s
AND report_date < %s
GROUP BY product_name, version_string
),
totals as (
SELECT
category
, version_string
, report_count
, SUM(report_count) OVER () as total_count
FROM crashes
)
SELECT category
, version_string
, report_count
, round((report_count * 100::numeric)/total_count,3)::TEXT
as percentage
FROM totals
ORDER BY report_count DESC"""
query_parameters = (params['signature'],
params['start_date'],
params['end_date'])
elif params['report_type'] == 'distinct_install':
result_cols = ['product_name',
'version_string',
'crashes',
'installations']
query_string = """
SELECT product_name
, version_string
, SUM(crash_count) AS crashes
, SUM(install_count) AS installations
FROM signature_summary_installations
JOIN signatures USING (signature_id)
WHERE
signatures.signature = %s
AND report_date >= %s
AND report_date < %s
"""
query_string += product_list
query_string += version_list
query_string += """
GROUP BY product_name, version_string
ORDER BY crashes DESC
"""
query_parameters = (
params['signature'],
params['start_date'],
params['end_date']
)
if product_list:
query_parameters += (params['product'],)
if version_list:
query_parameters += (params['version'],)
elif params['report_type'] == 'exploitability':
# Note, even if params['product'] is something we can't use
# that in this query
result_cols = [
'report_date',
'null_count',
'none_count',
'low_count',
'medium_count',
'high_count',
]
query_string = """
SELECT
cast(report_date as TEXT),
SUM(null_count),
SUM(none_count),
SUM(low_count),
SUM(medium_count),
SUM(high_count)
FROM exploitability_reports
JOIN signatures USING (signature_id)
WHERE
signatures.signature = %s
AND report_date >= %s
AND report_date < %s
"""
query_string += product_list
query_string += version_list
query_string += """
GROUP BY report_date
ORDER BY report_date DESC
"""
query_parameters = (
params['signature'],
params['start_date'],
params['end_date'],
)
if product_list:
query_parameters += (params['product'],)
if version_list:
query_parameters += (params['version'],)
elif params['report_type'] == 'devices':
result_cols = [
'cpu_abi',
'manufacturer',
'model',
'version',
'report_count',
'percentage',
]
query_string = """
WITH crashes as (
SELECT
android_devices.android_cpu_abi as cpu_abi,
android_devices.android_manufacturer as manufacturer,
android_devices.android_model as model,
android_devices.android_version as version,
SUM(report_count) as report_count
FROM signature_summary_device
JOIN signatures USING (signature_id)
JOIN android_devices ON
signature_summary_device.android_device_id =
android_devices.android_device_id
WHERE signatures.signature = %s
AND report_date >= %s
AND report_date < %s
"""
query_string += product_list
query_string += version_list
query_string += """
GROUP BY
android_devices.android_cpu_abi,
android_devices.android_manufacturer,
android_devices.android_model,
android_devices.android_version
),
totals as (
SELECT
cpu_abi,
manufacturer,
model,
version,
report_count,
SUM(report_count) OVER () as total_count
FROM crashes
)
SELECT
cpu_abi,
manufacturer,
model,
version,
report_count,
round((report_count * 100::numeric)/total_count,3)::TEXT
as percentage
FROM totals
ORDER BY report_count DESC
"""
query_parameters = (
params['signature'],
params['start_date'],
params['end_date'],
)
if product_list:
query_parameters += (params['product'],)
if version_list:
query_parameters += (params['version'],)
elif params['report_type'] == 'graphics':
result_cols = [
'vendor_hex',
'adapter_hex',
'vendor_name',
'adapter_name',
'report_count',
'percentage',
]
query_string = """
WITH crashes as (
SELECT
graphics_device.vendor_hex as vendor_hex,
graphics_device.adapter_hex as adapter_hex,
graphics_device.vendor_name as vendor_name,
graphics_device.adapter_name as adapter_name,
SUM(report_count) as report_count
FROM signature_summary_graphics
JOIN signatures USING (signature_id)
JOIN graphics_device ON
signature_summary_graphics.graphics_device_id =
graphics_device.graphics_device_id
WHERE signatures.signature = %s
AND report_date >= %s
AND report_date < %s
"""
query_string += product_list
query_string += version_list
query_string += """
GROUP BY
graphics_device.graphics_device_id
),
totals as (
SELECT
vendor_hex,
adapter_hex,
vendor_name,
adapter_name,
report_count,
SUM(report_count) OVER () as total_count
FROM crashes
)
SELECT
vendor_hex,
adapter_hex,
vendor_name,
adapter_name,
report_count,
round((report_count * 100::numeric)/total_count,3)::TEXT
as percentage
FROM totals
ORDER BY report_count DESC
"""
query_parameters = (
params['signature'],
params['start_date'],
params['end_date'],
)
if product_list:
query_parameters += (params['product'],)
if version_list:
query_parameters += (params['version'],)
elif params['report_type'] in report_type_columns:
result_cols = ['category', 'report_count', 'percentage']
query_string = """
WITH crashes AS (
SELECT """
query_string += report_type_columns[params['report_type']]
query_string += """ AS category
, sum(report_count) AS report_count
FROM signature_summary_"""
query_string += params['report_type']
query_string += """
JOIN signatures USING (signature_id)
WHERE
signatures.signature = %s
AND report_date >= %s
AND report_date < %s
"""
query_string += product_list
query_string += version_list
query_string += """
GROUP BY category
),
totals AS (
SELECT
category
, report_count
, sum(report_count) OVER () as total_count
FROM crashes
)
SELECT category
, report_count
, round((report_count * 100::numeric)/total_count,3)::TEXT
as percentage
FROM totals
ORDER BY report_count DESC
"""
query_parameters = (
params['signature'],
params['start_date'],
params['end_date']
)
if product_list:
query_parameters += (params['product'],)
if version_list:
query_parameters += (params['version'],)
sql_results = db.execute(cursor, query_string, query_parameters)
results = []
for row in sql_results:
newrow = dict(zip(result_cols, row))
results.append(newrow)
# Closing the connection here because we're not using
# the parent class' query()
self.connection.close()
return results
def get(self, **kwargs):
"""
Search for crashes and return them.
See http://socorro.readthedocs.org/en/latest/middleware.html#search
Optional arguments: see SearchCommon.get_parameters()
"""
params = search_common.get_parameters(kwargs)
# change aliases from the web to the implementation's need
if "for" in params and "terms" not in params:
params["terms"] = params.get("for")
if "from" in params and "from_date" not in params:
params["from_date"] = params.get("from")
if "to" in params and "to_date" not in params:
params["to_date"] = params.get("to")
if "in" in params and "fields" not in params:
params["fields"] = params.get("in")
# Default mode falls back to starts_with for postgres
if params["search_mode"] == "default":
params["search_mode"] = "starts_with"
if params["plugin_search_mode"] == "default":
params["plugin_search_mode"] = "starts_with"
# For Postgres, we never search for a list of terms
if params["terms"]:
params["terms"] = " ".join(params["terms"])
params["terms"] = Search.prepare_terms(params["terms"],
params["search_mode"])
# Searching for terms in plugins
if params["report_process"] == "plugin" and params["plugin_terms"]:
params["plugin_terms"] = " ".join(params["plugin_terms"])
params["plugin_terms"] = Search.prepare_terms(
params["plugin_terms"], params["plugin_search_mode"])
# Get information about the versions
util_service = Util(config=self.context)
params["versions_info"] = util_service.versions_info(**params)
# Parsing the versions
params["versions_string"] = params["versions"]
(params["versions"],
params["products"]) = Search.parse_versions(params["versions"],
params["products"])
if hasattr(self.context, 'webapi'):
context = self.context.webapi
else:
# old middleware
context = self.context
# Changing the OS ids to OS names
for i, elem in enumerate(params["os"]):
for platform in context.platforms:
if platform["id"][:3] == elem[:3]:
params["os"][i] = platform["name"]
# Creating the parameters for the sql query
sql_params = {}
# Preparing the different parts of the sql query
sql_select = self.generate_sql_select(params)
# Adding count for each OS
for i in context.platforms:
sql_params["os_%s" % i["id"]] = i["name"]
sql_from = self.build_reports_sql_from(params)
(sql_where,
sql_params) = self.build_reports_sql_where(params, sql_params,
context)
sql_group = self.generate_sql_group(params)
sql_order = """
ORDER BY total DESC, signature
"""
(sql_limit,
sql_params) = self.build_reports_sql_limit(params, sql_params)
# Assembling the query
sql_query = " ".join(
("/* socorro.search.Search search */", sql_select, sql_from,
sql_where, sql_group, sql_order, sql_limit))
# Query for counting the results
sql_count_query = " ".join(
("/* socorro.external.postgresql.search.Search search.count */",
"SELECT count(DISTINCT r.signature)", sql_from, sql_where))
# Querying the database
try:
connection = self.database.connection()
total = self.count(
sql_count_query,
sql_params,
error_message="Failed to count crashes from PostgreSQL.",
connection=connection)
results = []
# No need to call Postgres if we know there will be no results
if total != 0:
results = self.query(
sql_query,
sql_params,
error_message="Failed to retrieve crashes from PostgreSQL",
connection=connection)
except psycopg2.Error:
raise DatabaseError("Failed to retrieve crashes from PostgreSQL")
finally:
if connection:
connection.close()
# Transforming the results into what we want
crashes = []
for row in results:
if params["report_process"] == "plugin":
crash = dict(
zip(("signature", "count", "is_windows", "is_mac",
"is_linux", "numhang", "numplugin", "numcontent",
"pluginname", "pluginversion", "pluginfilename"),
row))
else:
crash = dict(
zip(("signature", "count", "is_windows", "is_mac",
"is_linux", "numhang", "numplugin", "numcontent"),
row))
crashes.append(crash)
return {"hits": crashes, "total": total}
def search(self, **kwargs):
"""
Search for crashes and return them.
See http://socorro.readthedocs.org/en/latest/middleware.html#search
Optional arguments: see SearchCommon.get_parameters()
"""
# Creating the connection to the DB
self.connection = self.database.connection()
cur = self.connection.cursor()
params = search_common.get_parameters(kwargs)
# Default mode falls back to starts_with for postgres
if params["search_mode"] == "default":
params["search_mode"] = "starts_with"
if params["plugin_search_mode"] == "default":
params["plugin_search_mode"] = "starts_with"
# For Postgres, we never search for a list of terms
if params["terms"]:
params["terms"] = " ".join(params["terms"])
params["terms"] = Search.prepare_terms(params["terms"],
params["search_mode"])
# Searching for terms in plugins
if params["report_process"] == "plugin" and params["plugin_terms"]:
params["plugin_terms"] = " ".join(params["plugin_terms"])
params["plugin_terms"] = Search.prepare_terms(
params["plugin_terms"],
params["plugin_search_mode"])
# Parsing the versions
params["versions_string"] = params["versions"]
(params["versions"], params["products"]) = Search.parse_versions(
params["versions"],
params["products"])
# Changing the OS ids to OS names
for i, elem in enumerate(params["os"]):
for platform in self.context.platforms:
if platform["id"] == elem:
params["os"][i] = platform["name"]
# Creating the parameters for the sql query
sql_params = {
"from_date": params["from_date"],
"to_date": params["to_date"],
"limit": params["result_number"],
"offset": params["result_offset"]
}
sql_params = Search.dispatch_params(sql_params, "term",
params["terms"])
sql_params = Search.dispatch_params(sql_params, "product",
params["products"])
sql_params = Search.dispatch_params(sql_params, "os",
params["os"])
sql_params = Search.dispatch_params(sql_params, "version",
params["versions"])
sql_params = Search.dispatch_params(sql_params, "build",
params["build_ids"])
sql_params = Search.dispatch_params(sql_params, "reason",
params["reasons"])
sql_params = Search.dispatch_params(sql_params, "plugin_term",
params["plugin_terms"])
sql_params = Search.dispatch_params(sql_params, "branch",
params["branches"])
# Preparing the different parts of the sql query
#---------------------------------------------------------------
# SELECT
#---------------------------------------------------------------
sql_select = self.generate_sql_select(params)
# Adding count for each OS
for i in self.context.platforms:
sql_params["os_%s" % i["id"]] = i["name"]
#---------------------------------------------------------------
# FROM
#---------------------------------------------------------------
sql_from = self.generate_sql_from(params)
#---------------------------------------------------------------
# WHERE
#---------------------------------------------------------------
sql_where = ["""
WHERE r.date_processed BETWEEN %(from_date)s AND %(to_date)s
"""]
## Adding terms to where clause
if params["terms"]:
if params["search_mode"] == "is_exactly":
sql_where.append("r.signature=%(term)s")
else:
sql_where.append("r.signature LIKE %(term)s")
## Adding products to where clause
if params["products"]:
products_list = ["r.product=%(product" + str(x) + ")s"
for x in range(len(params["products"]))]
sql_where.append("(%s)" % (" OR ".join(products_list)))
## Adding OS to where clause
if params["os"]:
os_list = ["r.os_name=%(os" + str(x) + ")s"
for x in range(len(params["os"]))]
sql_where.append("(%s)" % (" OR ".join(os_list)))
## Adding branches to where clause
if params["branches"]:
branches_list = ["branches.branch=%(branch" + str(x) + ")s"
for x in range(len(params["branches"]))]
sql_where.append("(%s)" % (" OR ".join(branches_list)))
## Adding versions to where clause
if params["versions"]:
# Get information about the versions
versions_service = Util(config=self.context)
fakeparams = {
"versions": params["versions_string"]
}
versions_info = versions_service.versions_info(**fakeparams)
if isinstance(params["versions"], list):
versions_where = []
for x in range(0, len(params["versions"]), 2):
version_where = []
version_where.append(str(x).join(("r.product=%(version",
")s")))
key = "%s:%s" % (params["versions"][x],
params["versions"][x + 1])
version_where = self.generate_version_where(
key, params["versions"],
versions_info, x, sql_params,
version_where)
version_where.append(str(x + 1).join((
"r.version=%(version", ")s")))
versions_where.append("(%s)" % " AND ".join(version_where))
sql_where.append("(%s)" % " OR ".join(versions_where))
else:
# Original product:value
key = "%s:%s" % (params["products"], params["versions"])
version_where = []
version_where = self.generate_version_where(
key, params["versions"],
versions_info, None, sql_params,
version_where)
version_where.append("r.version=%(version)s")
sql_where.append("(%s)" % " AND ".join(version_where))
## Adding build id to where clause
if params["build_ids"]:
build_ids_list = ["r.build=%(build" + str(x) + ")s"
for x in range(len(params["build_ids"]))]
sql_where.append("(%s)" % (" OR ".join(build_ids_list)))
## Adding reason to where clause
if params["reasons"]:
reasons_list = ["r.reason=%(reason" + str(x) + ")s"
for x in range(len(params["reasons"]))]
sql_where.append("(%s)" % (" OR ".join(reasons_list)))
if params["report_type"] == "crash":
sql_where.append("r.hangid IS NULL")
elif params["report_type"] == "hang":
sql_where.append("r.hangid IS NOT NULL")
## Searching through plugins
if params["report_process"] == "plugin":
sql_where.append("r.process_type = 'plugin'")
sql_where.append(("plugins_reports.date_processed BETWEEN "
"%(from_date)s AND %(to_date)s"))
if params["plugin_terms"]:
comp = "="
if params["plugin_search_mode"] in ("contains", "starts_with"):
comp = " LIKE "
sql_where_plugin_in = []
for f in params["plugin_in"]:
if f == "name":
field = "plugins.name"
elif f == "filename":
field = "plugins.filename"
sql_where_plugin_in.append(comp.join((field,
"%(plugin_term)s")))
sql_where.append("(%s)" % " OR ".join(sql_where_plugin_in))
elif params["report_process"] == "browser":
sql_where.append("r.process_type IS NULL")
elif params["report_process"] == "content":
sql_where.append("r.process_type = 'content'")
sql_where = " AND ".join(sql_where)
#---------------------------------------------------------------
# GROUP BY
#---------------------------------------------------------------
sql_group = self.generate_sql_group(params)
#---------------------------------------------------------------
# ORDER BY
#---------------------------------------------------------------
sql_order = """
ORDER BY total DESC
"""
#---------------------------------------------------------------
# LIMIT OFFSET
#---------------------------------------------------------------
sql_limit = """
LIMIT %(limit)s
OFFSET %(offset)s
"""
# Assembling the query
sql_from = " JOIN ".join(sql_from)
sql_query = " ".join(("/* socorro.search.Search search */",
sql_select, sql_from, sql_where, sql_group,
sql_order, sql_limit))
# Query for counting the results
sql_count_query = " ".join((
"/* socorro.external.postgresql.search.Search search.count */",
"SELECT count(DISTINCT r.signature)", sql_from, sql_where))
# Debug
logger.debug(cur.mogrify(sql_query, sql_params))
# Querying the DB
try:
total = db.singleValueSql(cur, sql_count_query, sql_params)
except Exception:
total = 0
util.reportExceptionAndContinue(logger)
# No need to call Postgres if we know there will be no results
if total != 0:
try:
results = db.execute(cur, sql_query, sql_params)
except Exception:
results = []
util.reportExceptionAndContinue(logger)
else:
results = []
json_result = {
"total": total,
"hits": []
}
# Transforming the results into what we want
for crash in results:
if params["report_process"] == "plugin":
row = dict(zip(("signature", "count", "is_windows", "is_mac",
"is_linux", "numhang", "numplugin",
"numcontent", "pluginname", "pluginversion",
"pluginfilename"), crash))
else:
row = dict(zip(("signature", "count", "is_windows", "is_mac",
"is_linux", "numhang", "numplugin",
"numcontent"), crash))
json_result["hits"].append(row)
self.connection.close()
return json_result