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}