Add version overview page
diff --git a/crashreport_stats/util.py b/crashreport_stats/util.py
new file mode 100644
index 0000000..316ab53
--- /dev/null
+++ b/crashreport_stats/util.py
@@ -0,0 +1,80 @@
+from django.db import migrations, models
+import django.db.models.deletion
+
+from django.db import connection
+from datetime import date, timedelta
+
+from . import models as myModels
+
+from django.db import transaction
+
+def dictfetchall(cursor):
+ "Returns 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()
+ ]
+
+@transaction.atomic
+def fill_version_data():
+ myModels.Version.objects.all().delete()
+ query = '''
+ SELECT fingerprint as build_fingerprint,
+ ( select count(id) from crashreports_crashreport where boot_reason in ("RTC alarm") and crashreports_crashreport.build_fingerprint = fingerprint) as SMPL,
+ ( select count(id) from crashreports_crashreport where boot_reason in ("UNKNOWN", "keyboard power on") and crashreports_crashreport.build_fingerprint = fingerprint) as prob_crashes,
+ ( select count(id) from crashreports_crashreport where boot_reason not in ("RTC alarm", "UNKNOWN", "keyboard power on") and crashreports_crashreport.build_fingerprint = fingerprint) as other,
+ ( select count(id) from crashreports_heartbeat where crashreports_heartbeat.build_fingerprint = fingerprint) as heartbeats,
+ ( select min(crashreports_heartbeat.created_at) from crashreports_heartbeat where crashreports_heartbeat.build_fingerprint = fingerprint) as first_seen
+ from (select distinct(build_fingerprint) as fingerprint
+ from crashreports_heartbeat) group by fingerprint order by heartbeats;'''
+ cursor = connection.cursor()
+ cursor.execute(query,[])
+ desc = cursor.description
+ for row in cursor.fetchall():
+ i = dict(zip([col[0] for col in desc], row))
+ version = myModels.Version(
+ build_fingerprint = i['build_fingerprint'],
+ first_seen_on = i['first_seen'].split()[0],
+ released_on = i['first_seen'].split()[0],
+ heartbeats= i['heartbeats'],
+ prob_crashes = i['prob_crashes'],
+ smpl = i['SMPL'],
+ other = i['other']
+ )
+ version.save()
+
+@transaction.atomic
+def fill_version_daily_data():
+ myModels.VersionDaily.objects.all().delete()
+ query = '''
+ SELECT build_fingerprint, count(id) as heartbeats,
+ strftime("%%Y-%%m-%%d",crashreports_heartbeat.date) as date,
+ ( select count(id) from crashreports_crashreport where boot_reason in ("RTC alarm") and crashreports_crashreport.build_fingerprint = crashreports_heartbeat.build_fingerprint and crashreports_crashreport.date >= %s and crashreports_crashreport.date < %s) as SMPL,
+ ( select count(id) from crashreports_crashreport where boot_reason in ("UNKNOWN", "keyboard power on") and crashreports_crashreport.build_fingerprint = crashreports_heartbeat.build_fingerprint and crashreports_crashreport.date >= %s and crashreports_crashreport.date < %s) as prob_crashes,
+ ( select count(id) from crashreports_crashreport where boot_reason not in ("RTC alarm", "UNKNOWN", "keyboard power on") and crashreports_crashreport.build_fingerprint = crashreports_heartbeat.build_fingerprint and crashreports_crashreport.date >= %s and crashreports_crashreport.date < %s) as other
+ from crashreports_heartbeat where crashreports_heartbeat.date >= %s and crashreports_heartbeat.date < %s
+ group by build_fingerprint'''
+ start = date(2016, 8, 1)
+ end = date.today() + timedelta(days=5)
+ delta = end - start
+ for d in range(delta.days + 1):
+ day = start + timedelta(days=d)
+ print("Getting Stats for " + str(day))
+ cursor = connection.cursor()
+ cursor.execute(query,[str(day), str(day+timedelta(days=1))]*4)
+ desc = cursor.description
+ for row in cursor.fetchall():
+ i = dict(zip([col[0] for col in desc], row))
+ try:
+ version_daily = myModels.VersionDaily(
+ version = myModels.Version.objects.get(build_fingerprint=i['build_fingerprint']),
+ heartbeats= i['heartbeats'],
+ date=day,
+ prob_crashes = i['prob_crashes'],
+ smpl = i['SMPL'],
+ other = i['other']
+ )
+ except:
+ print("Skipping entry for {} {}".format(i['build_fingerprint'],day))
+ version_daily.save()