Replace raw SQL queries with native Django code
authorMitja Nikolaus <mitja@fairphone.com>
Fri, 2 Nov 2018 16:10:02 +0000 (17:10 +0100)
committerMitja Nikolaus <mitja@fairphone.com>
Wed, 21 Nov 2018 14:49:31 +0000 (14:49 +0000)
Issue: HIC-248
Change-Id: I46542f2b6558f6ad3e372cb2900fbcf8614a3144

crashreport_stats/raw_querys.py [deleted file]
crashreport_stats/rest_endpoints.py
crashreport_stats/tests/test_rest_endpoints.py

diff --git a/crashreport_stats/raw_querys.py b/crashreport_stats/raw_querys.py
deleted file mode 100644 (file)
index 66ba252..0000000
+++ /dev/null
@@ -1,56 +0,0 @@
-"""SQL queries for getting device statistics."""
-
-
-def execute_device_update_history_query(cursor, params):
-    """Query the device update history."""
-    query = """
-        SELECT
-            min(crashreports_heartbeat.date) as update_date,
-            build_fingerprint,
-            max(crashreports_device.id),
-            ( select count(crashreports_crashreport.id) from crashreports_crashreport
-              where  boot_reason in ('UNKNOWN', 'keyboard power on')
-              and    max(crashreports_device.id) = crashreports_crashreport.device_id
-              and    crashreports_crashreport.build_fingerprint = crashreports_heartbeat.build_fingerprint ) as prob_crashes,
-            ( select count(crashreports_crashreport.id) from crashreports_crashreport
-            where  boot_reason in ('RTC alarm')
-            and    max(crashreports_device.id) = crashreports_crashreport.device_id
-            and    crashreports_crashreport.build_fingerprint = crashreports_heartbeat.build_fingerprint ) as smpl,
-            ( select count(crashreports_crashreport.id) from crashreports_crashreport
-            where  boot_reason not in ('UNKNOWN', 'keyboard power on', 'RTC alarm')
-            and    max(crashreports_device.id) = crashreports_crashreport.device_id
-            and    crashreports_crashreport.build_fingerprint = crashreports_heartbeat.build_fingerprint ) as other,
-            count(crashreports_heartbeat.id) as heartbeats
-        FROM
-            crashreports_device
-        JOIN
-            crashreports_heartbeat
-        ON
-            crashreports_device.id = crashreports_heartbeat.device_id
-        where
-            crashreports_device.uuid=%s
-            group by build_fingerprint;
-        """  # noqa: E501
-    uuid = params.get("uuid", "18f530d7-e9c3-4dcf-adba-3dddcd7d3155")
-    param_array = [uuid]
-    cursor.execute(query, param_array)
-
-
-def execute_device_report_history(cursor, params):
-    """Query the device report history."""
-    query = """
-        SELECT
-          crashreports_heartbeat.date::date as date,
-          count(crashreports_heartbeat.id) as heartbeats,
-          count(crashreports_crashreport.id) filter (where crashreports_crashreport.boot_reason in ('RTC alarm')) as SMPL,
-          count(crashreports_crashreport.id) filter (where crashreports_crashreport.boot_reason in ('UNKNOWN', 'keyboard power on')) as prob_crashes,
-          count(crashreports_crashreport.id) filter (where crashreports_crashreport.boot_reason not in ('RTC alarm', 'UNKNOWN', 'keyboard power on')) as other
-        from crashreports_device
-        join crashreports_heartbeat on crashreports_device.id = crashreports_heartbeat.device_id
-        left join crashreports_crashreport on crashreports_device.id = crashreports_crashreport.device_id and  crashreports_heartbeat.date::date = crashreports_crashreport.date::date
-        where
-          crashreports_device.uuid = %s group by crashreports_heartbeat.date, crashreports_device.id;
-        """  # noqa: E501
-    uuid = params.get("uuid", "18f530d7-e9c3-4dcf-adba-3dddcd7d3155")
-    param_array = [uuid]
-    cursor.execute(query, param_array)
index f24b625..b5cd108 100644 (file)
@@ -1,4 +1,5 @@
 """REST API for accessing the crashreports statistics."""
+import operator
 import zipfile
 from collections import OrderedDict
 
@@ -11,7 +12,6 @@ from rest_framework.response import Response
 from rest_framework.views import APIView
 
 from django.core.exceptions import ObjectDoesNotExist
-from django.db import connection
 from django.db.models.expressions import F
 from django.utils.decorators import method_decorator
 
@@ -38,19 +38,9 @@ from crashreports.permissions import (
 )
 from crashreports.response_descriptions import default_desc
 
-from . import raw_querys
-
 _RESPONSE_STATUS_200_DESCRIPTION = "OK"
 
 
-def dictfetchall(cursor):
-    """Return all rows from a cursor as a dict."""
-    desc = cursor.description
-    return [
-        dict(zip([col[0] for col in desc], row)) for row in cursor.fetchall()
-    ]
-
-
 _DEVICE_UPDATE_HISTORY_SCHEMA = openapi.Schema(
     type=openapi.TYPE_ARRAY,
     items=openapi.Schema(
@@ -99,13 +89,51 @@ class DeviceUpdateHistory(APIView):
             request: Http request
             uuid: The UUID of the device
 
-        Returns: The update history of the requested device.
+        Returns:
+            The update history of the requested device, sorted by the update
+            date.
 
         """
-        cursor = connection.cursor()
-        raw_querys.execute_device_update_history_query(cursor, {"uuid": uuid})
-        res = dictfetchall(cursor)
-        return Response(res)
+        device = Device.objects.get(uuid=uuid)
+
+        device_heartbeats = list(device.heartbeats.all())
+        device_crashreports = list(device.crashreports.all())
+
+        build_fingerprints = {hb.build_fingerprint for hb in device_heartbeats}
+
+        response = [
+            get_release_stats(
+                build_fingerprint,
+                device,
+                device_crashreports,
+                device_heartbeats,
+            )
+            for build_fingerprint in build_fingerprints
+        ]
+        response = sorted(response, key=operator.itemgetter("update_date"))
+
+        return Response(response)
+
+
+def get_release_stats(build_fingerprint, device, crashreports, heartbeats):
+    """Get the stats for a device for a specific release."""
+    heartbeats = filter_instances(
+        heartbeats, lambda hb: hb.build_fingerprint == build_fingerprint
+    )
+    crashreports = filter_instances(
+        crashreports, lambda c: c.build_fingerprint == build_fingerprint
+    )
+
+    stats = get_stats(heartbeats, crashreports)
+    stats.update(
+        {
+            "build_fingerprint": build_fingerprint,
+            "update_date": min([heartbeat.date for heartbeat in heartbeats]),
+            "max": device.id,
+        }
+    )
+
+    return stats
 
 
 _DEVICE_REPORT_HISTORY_SCHEMA = openapi.Schema(
@@ -154,13 +182,64 @@ class DeviceReportHistory(APIView):
             request: Http request
             uuid: The UUID of the device
 
-        Returns: The report history of the requested device.
+        Returns: The report history of the requested device, sorted by date.
 
         """
-        cursor = connection.cursor()
-        raw_querys.execute_device_report_history(cursor, {"uuid": uuid})
-        res = dictfetchall(cursor)
-        return Response(res)
+        device = Device.objects.get(uuid=uuid)
+
+        device_heartbeats = list(device.heartbeats.all())
+        device_crashreports = list(device.crashreports.all())
+
+        dates = {heartbeat.date.date() for heartbeat in device_heartbeats}
+
+        response = [
+            get_stats_for_date(date, device_crashreports, device_heartbeats)
+            for date in sorted(dates)
+        ]
+
+        return Response(response)
+
+
+def get_stats_for_date(date, crashreports, heartbeats):
+    """Get the stats for a device for a specific date."""
+    heartbeats = filter_instances(heartbeats, lambda hb: hb.date.date() == date)
+    crashreports = filter_instances(
+        crashreports, lambda c: c.date.date() == date
+    )
+
+    stats = get_stats(heartbeats, crashreports)
+    stats.update(date=date)
+    return stats
+
+
+def filter_instances(instances, filter_expr):
+    """Filter instances using a lambda filter function."""
+    return list(filter(filter_expr, instances))
+
+
+def get_stats(heartbeats, crashreports):
+    """Get the numbers of heartbeats and crashes per for each type."""
+    crashes = [
+        crashreport
+        for crashreport in crashreports
+        if crashreport.boot_reason in Crashreport.CRASH_BOOT_REASONS
+    ]
+    smpls = [
+        crashreport
+        for crashreport in crashreports
+        if crashreport.boot_reason in Crashreport.SMPL_BOOT_REASONS
+    ]
+    others = [
+        crashreport
+        for crashreport in crashreports
+        if crashreport not in crashes + smpls
+    ]
+    return {
+        "heartbeats": len(heartbeats),
+        "smpl": len(smpls),
+        "prob_crashes": len(crashes),
+        "other": len(others),
+    }
 
 
 _STATUS_RESPONSE_SCHEMA = openapi.Schema(
index c131915..bf35b6b 100644 (file)
@@ -1,6 +1,6 @@
 """Tests for the rest_endpoints module."""
-from datetime import datetime, timedelta
 import operator
+from datetime import datetime, timedelta
 import unittest
 
 import pytz
@@ -791,7 +791,6 @@ class DeviceStatsTestCase(HiccupStatsAPITestCase):
         # Assert that the report history is empty
         self.assertEqual([], response.data)
 
-    @unittest.skip("Broken raw query. Heartbeats are not counted correctly.")
     def test_get_device_report_history(self):
         """Test getting report history stats for a device."""
         # Create a device with a heartbeat and one report of each type
@@ -823,6 +822,52 @@ class DeviceStatsTestCase(HiccupStatsAPITestCase):
         ]
         self.assertEqual(report_history, response.data)
 
+    def test_get_device_report_history_multiple_days(self):
+        """Test getting report history stats for a device for multiple days."""
+        device = Dummy.create_dummy_device(Dummy.create_dummy_user())
+        expected_report_history = []
+
+        # Create a device with a heartbeat and one report of each type for 10
+        # days
+        report_date = Dummy.DEFAULT_DUMMY_CRASHREPORT_VALUES["date"]
+        for _ in range(10):
+            report_date = report_date + timedelta(days=1)
+
+            Dummy.create_dummy_report(HeartBeat, device, date=report_date)
+            for boot_reason in (
+                Crashreport.SMPL_BOOT_REASONS
+                + Crashreport.CRASH_BOOT_REASONS
+                + ["other boot reason"]
+            ):
+                Dummy.create_dummy_report(
+                    Crashreport,
+                    device,
+                    boot_reason=boot_reason,
+                    date=report_date,
+                )
+
+            # Create the expected report history object
+            expected_report_history.append(
+                {
+                    "date": report_date.date(),
+                    "heartbeats": 1,
+                    "smpl": len(Crashreport.SMPL_BOOT_REASONS),
+                    "prob_crashes": len(Crashreport.CRASH_BOOT_REASONS),
+                    "other": 1,
+                }
+            )
+
+        # Sort the expected values by date
+        expected_report_history.sort(key=operator.itemgetter("date"))
+
+        # Get the device report history statistics
+        response = self._get_with_params(
+            self.device_report_history_url, {"uuid": device.uuid}
+        )
+
+        # Assert that the statistics match
+        self.assertEqual(expected_report_history, response.data)
+
     def test_get_device_update_history_no_reports(self):
         """Test getting update history stats for a device without reports."""
         # Create a device
@@ -901,16 +946,13 @@ class DeviceStatsTestCase(HiccupStatsAPITestCase):
                     "heartbeats": 1,
                 }
             )
-        # Sort the expected values by build fingerprint
-        expected_update_history.sort(
-            key=operator.itemgetter("build_fingerprint")
-        )
+        # Sort the expected values by update date
+        expected_update_history.sort(key=operator.itemgetter("update_date"))
 
-        # Get the device update history statistics and sort it
+        # Get the device update history statistics
         response = self._get_with_params(
             self.device_update_history_url, {"uuid": device.uuid}
         )
-        response.data.sort(key=operator.itemgetter("build_fingerprint"))
 
         # Assert that the statistics match
         self.assertEqual(expected_update_history, response.data)