Replace raw SQL queries with native Django code
Issue: HIC-248
Change-Id: I46542f2b6558f6ad3e372cb2900fbcf8614a3144
diff --git a/crashreport_stats/raw_querys.py b/crashreport_stats/raw_querys.py
deleted file mode 100644
index 66ba252..0000000
--- a/crashreport_stats/raw_querys.py
+++ /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)
diff --git a/crashreport_stats/rest_endpoints.py b/crashreport_stats/rest_endpoints.py
index f24b625..b5cd108 100644
--- a/crashreport_stats/rest_endpoints.py
+++ b/crashreport_stats/rest_endpoints.py
@@ -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.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.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 @@
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 @@
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(
diff --git a/crashreport_stats/tests/test_rest_endpoints.py b/crashreport_stats/tests/test_rest_endpoints.py
index c131915..bf35b6b 100644
--- a/crashreport_stats/tests/test_rest_endpoints.py
+++ b/crashreport_stats/tests/test_rest_endpoints.py
@@ -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 @@
# 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 @@
]
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 @@
"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)