Example #1
0
    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
Example #2
0
    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}
Example #3
0
    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)
Example #4
0
    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
Example #5
0
    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
Example #6
0
    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
Example #7
0
    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
Example #8
0
    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)
Example #9
0
    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)
Example #10
0
    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)
Example #11
0
    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}
Example #12
0
    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
Example #13
0
    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
Example #14
0
    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}
Example #15
0
    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}
Example #16
0
    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
Example #17
0
    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
Example #18
0
    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
        }
Example #19
0
    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}
Example #20
0
    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
Example #21
0
    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
        }
Example #22
0
    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
Example #23
0
    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
Example #24
0
    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
Example #25
0
    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}
Example #26
0
    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