def post(self, **kwargs):
params = external_common.parse_arguments(self.filters, kwargs)
if not params.category:
raise MissingArgumentError('category')
if not params.rule:
raise MissingArgumentError('rule')
sql = """
/* socorro.external.postgresql.skiplist.SkipList.post */
INSERT INTO skiplist (category, rule)
VALUES (%s, %s);
"""
sql_params = [params.category, params.rule]
connection = self.database.connection()
try:
with connection.cursor() as cur:
cur.execute(sql, sql_params)
connection.commit()
except psycopg2.Error:
connection.rollback()
error_message = "Failed updating skip list in PostgreSQL"
logger.error(error_message)
raise DatabaseError(error_message)
finally:
connection.close()
return True
def count(self, sql, params=None, error_message=None, connection=None):
"""Return the result of a count SQL query executed against PostgreSQL.
Create a connection, open a cursor, execute the query and return the
result. If an error occures, log it and raise a DatabaseError.
Keyword arguments:
sql -- SQL query to execute.
params -- Parameters to merge into the SQL query when executed.
error_message -- Eventual error message to log.
connection -- Optional connection to the database. If none, a new one
will be opened.
"""
fresh_connection = False
try:
if not connection:
connection = self.database.connection()
fresh_connection = True
#self.context.logger.debug(connection.cursor().mogrify(sql, params))
result = single_value_sql(connection, sql, params)
except psycopg2.Error, e:
if error_message is None:
error_message = "Failed to execute count against PostgreSQL"
error_message = "%s - %s" % (error_message, str(e))
logger.error(error_message, exc_info=True)
raise DatabaseError(error_message)
def query(self, sql, params=None, error_message=None, connection=None):
"""Return the result of a query executed against PostgreSQL.
Create a connection, open a cursor, execute the query and return the
results. If an error occures, log it and raise a DatabaseError.
Keyword arguments:
sql -- SQL query to execute.
params -- Parameters to merge into the SQL query when executed.
error_message -- Eventual error message to log.
connection -- Optional connection to the database. If none, a new one
will be opened.
"""
fresh_connection = False
try:
if not connection:
connection = self.database.connection()
fresh_connection = True
results = execute_query_fetchall(connection, sql, params)
except psycopg2.Error:
if error_message is None:
error_message = "Failed to execute query against PostgreSQL"
logger.error(error_message, exc_info=True)
raise DatabaseError(error_message)
finally:
if connection and fresh_connection:
connection.close()
return results
class Query(ElasticSearchBase):
'''Implement the /query service with ElasticSearch. '''
filters = [
('query', None, 'str'),
('indices', None, ['list', 'str']),
]
def get(self, **kwargs):
'''Return the result of a custom query. '''
params = external_common.parse_arguments(self.filters, kwargs)
if not params.query:
raise MissingArgumentError('query')
try:
query = json.loads(params.query)
except ValueError:
raise BadArgumentError(
'query',
msg="Invalid JSON value for parameter 'query'"
)
es = pyelasticsearch.ElasticSearch(
urls=self.config.elasticsearch_urls,
timeout=self.config.elasticsearch_timeout_extended,
)
# Set indices.
indices = []
if not params.indices:
# By default, use the last two indices.
today = utc_now()
last_week = today - datetime.timedelta(days=7)
indices = self.generate_list_of_indexes(last_week, today)
elif len(params.indices) == 1 and params.indices[0] == 'ALL':
# If we want all indices, just do nothing.
pass
else:
indices = params.indices
search_args = {}
if indices:
search_args['index'] = indices
search_args['doc_type'] = self.config.elasticsearch_doctype
try:
results = es.search(
query,
**search_args
)
except ElasticHttpNotFoundError, e:
missing_index = re.findall(BAD_INDEX_REGEX, e.error)[0]
raise ResourceNotFound(
"elasticsearch index '%s' does not exist" % missing_index
)
except (InvalidJsonResponseError, ElasticHttpError), e:
raise DatabaseError(e)
def _execute(
self, actor_function, sql, error_message, params=None, connection=None
):
fresh_connection = False
try:
if not connection:
connection = self.database.connection()
fresh_connection = True
# logger.debug(connection.cursor().mogrify(sql, params))
result = actor_function(connection, sql, params)
connection.commit()
except psycopg2.Error, e:
error_message = "%s - %s" % (error_message, str(e))
logger.error(error_message, exc_info=True)
if connection:
connection.rollback()
raise DatabaseError(error_message)
def update_featured(self, **kwargs):
"""Update lists of featured versions. """
products_list = Products(config=self.context).get()['products']
releases = {}
for p in kwargs:
if p in products_list:
if isinstance(kwargs[p], basestring):
# Assuming `,` for now, see
# https://bugzilla.mozilla.org/show_bug.cgi?id=787233
releases[p] = kwargs[p].split(',')
else:
releases[p] = kwargs[p]
if len(releases) == 0:
return False
sql = """/* socorro.external.postgresql.releases.update_featured */
SELECT edit_featured_versions(%%s, %s)
"""
error_message = "Failed updating featured versions in PostgreSQL"
with self.get_connection() as connection:
try:
cursor = connection.cursor()
for p in releases:
query = sql % ", ".join(
"%s" for i in xrange(len(releases[p]))
)
sql_params = [p] + releases[p]
# logger.debug(cursor.mogrify(query, sql_params))
cursor.execute(query, sql_params)
connection.commit()
except psycopg2.Error:
connection.rollback()
logger.error(error_message)
raise DatabaseError(error_message)
return True
def delete(self, **kwargs):
params = external_common.parse_arguments(self.filters, kwargs)
if not params.category:
raise MissingArgumentError('category')
if not params.rule:
raise MissingArgumentError('rule')
sql_params = [params.category, params.rule]
count_sql = """
/* socorro.external.postgresql.skiplist.SkipList.delete */
SELECT COUNT(*) FROM skiplist
WHERE category=%s AND rule=%s
"""
sql = """
/* socorro.external.postgresql.skiplist.SkipList.delete */
DELETE FROM skiplist
WHERE category=%s AND rule=%s
"""
connection = self.database.connection()
try:
cur = connection.cursor()
count = self.count(count_sql, sql_params, connection=connection)
if not count:
return False
cur.execute(sql, sql_params)
connection.commit()
except psycopg2.Error:
connection.rollback()
error_message = "Failed delete skip list in PostgreSQL"
logger.error(error_message)
raise DatabaseError(error_message)
finally:
connection.close()
return True
def query(self,
sql,
params=None,
error_message=None,
action=execute_query_fetchall):
"""Return the result of a query executed against PostgreSQL.
Create a connection, open a cursor, execute the query and return the
results. If an error occures, log it and raise a DatabaseError.
Keyword arguments:
sql -- SQL query to execute.
params -- Parameters to merge into the SQL query when executed.
error_message -- Eventual error message to log.
"""
try:
result = self.transaction(action, sql, params)
return result
except psycopg2.Error, x:
raise
self.config.logger.error(
error_message if error_message else str(x), exc_info=True)
raise DatabaseError(error_message)
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 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}
