crashreport_stats/util.py

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()