blob: 5b1878ba95a4d87f03cceff586fae08033c49033 [file] [log] [blame]
Dirk Vogt0e565a72017-10-09 15:14:21 +02001from django.conf import settings
2
Dirk Vogt62ff7f22017-05-04 16:07:21 +02003class FormatDict(dict):
4 def __missing__(self, key):
5 return "{" + key + "}"
6
7def fill_in_build_fingerprints(query, build_fingerprints):
8 all_fingerprints_query = 'select distinct build_fingerprint from crashreports_crashreport'
9 if len(build_fingerprints) > 0 :
10 return query.format(
11 FormatDict(fingerprint_placeholers=
12 ','.join(["%s"] * len(build_fingerprints))))
13 else:
14 return query.format(FormatDict(fingerprint_placeholers = all_fingerprints_query))
15
Dirk Vogt0e565a72017-10-09 15:14:21 +020016
Dirk Vogt62ff7f22017-05-04 16:07:21 +020017def execute_device_update_history_query(cursor, params):
Dirk Vogt0e565a72017-10-09 15:14:21 +020018 if settings.DATABASES['default']['ENGINE'] == 'django.db.backends.postgresql_psycopg2':
19 return psql_execute_device_update_history_query(cursor, params)
20 else:
21 return sqlite_execute_device_update_history_query(cursor, params)
22
23
24def execute_device_report_history(cursor, params):
25 if settings.DATABASES['default']['ENGINE'] == 'django.db.backends.postgresql_psycopg2':
26 return psql_execute_device_report_history(cursor, params)
27 else:
28 return sqlite_execute_device_report_history(cursor, params)
29
30def sqlite_execute_device_update_history_query(cursor, params):
Dirk Vogt62ff7f22017-05-04 16:07:21 +020031 query = '''
Dirk Vogta10db9d2017-05-05 09:08:45 +020032 SELECT
Dirk Vogt62ff7f22017-05-04 16:07:21 +020033 min(crashreports_heartbeat.date) as update_date,
Dirk Vogta10db9d2017-05-05 09:08:45 +020034 build_fingerprint,
35 ( select count(crashreports_crashreport.id) from crashreports_crashreport
36 where boot_reason in ("UNKNOWN", "keyboard power on")
37 and crashreports_device.id == crashreports_crashreport.device_id
38 and crashreports_crashreport.build_fingerprint == crashreports_heartbeat.build_fingerprint ) as prob_crashes,
39 ( select count(crashreports_crashreport.id) from crashreports_crashreport
40 where boot_reason in ("RTC alarm")
41 and crashreports_device.id == crashreports_crashreport.device_id
Dirk Vogt38f606c2017-12-05 15:30:27 +010042 and crashreports_crashreport.build_fingerprint == crashreports_heartbeat.build_fingerprint ) as smpl,
Dirk Vogta10db9d2017-05-05 09:08:45 +020043 ( select count(crashreports_crashreport.id) from crashreports_crashreport
44 where boot_reason not in ("UNKNOWN", "keyboard power on", "RTC alarm")
45 and crashreports_device.id == crashreports_crashreport.device_id
46 and crashreports_crashreport.build_fingerprint == crashreports_heartbeat.build_fingerprint ) as other,
47 count(crashreports_heartbeat.id) as heartbeats
48 FROM
Dirk Vogt62ff7f22017-05-04 16:07:21 +020049 crashreports_device
Dirk Vogta10db9d2017-05-05 09:08:45 +020050 JOIN
51 crashreports_heartbeat
52 ON
53 crashreports_device.id == crashreports_heartbeat.device_id
54 where
Dirk Vogt62ff7f22017-05-04 16:07:21 +020055 crashreports_device.uuid=%s
56 group by build_fingerprint;
57 '''
58 uuid = params.get('uuid', '18f530d7-e9c3-4dcf-adba-3dddcd7d3155')
59 param_array = [uuid]
60 cursor.execute(query, param_array)
61
62
Dirk Vogt0e565a72017-10-09 15:14:21 +020063def psql_execute_device_update_history_query(cursor, params):
64 query = '''
65 SELECT
66 min(crashreports_heartbeat.date) as update_date,
67 build_fingerprint,
Dirk Vogt38f606c2017-12-05 15:30:27 +010068 max(crashreports_device.id),
Dirk Vogt0e565a72017-10-09 15:14:21 +020069 ( select count(crashreports_crashreport.id) from crashreports_crashreport
70 where boot_reason in ('UNKNOWN', 'keyboard power on')
Dirk Vogt38f606c2017-12-05 15:30:27 +010071 and max(crashreports_device.id) = crashreports_crashreport.device_id
Dirk Vogt0e565a72017-10-09 15:14:21 +020072 and crashreports_crashreport.build_fingerprint = crashreports_heartbeat.build_fingerprint ) as prob_crashes,
73 ( select count(crashreports_crashreport.id) from crashreports_crashreport
74 where boot_reason in ('RTC alarm')
Dirk Vogt38f606c2017-12-05 15:30:27 +010075 and max(crashreports_device.id) = crashreports_crashreport.device_id
76 and crashreports_crashreport.build_fingerprint = crashreports_heartbeat.build_fingerprint ) as smpl,
Dirk Vogt0e565a72017-10-09 15:14:21 +020077 ( select count(crashreports_crashreport.id) from crashreports_crashreport
78 where boot_reason not in ('UNKNOWN', 'keyboard power on', 'RTC alarm')
Dirk Vogt38f606c2017-12-05 15:30:27 +010079 and max(crashreports_device.id) = crashreports_crashreport.device_id
Dirk Vogt0e565a72017-10-09 15:14:21 +020080 and crashreports_crashreport.build_fingerprint = crashreports_heartbeat.build_fingerprint ) as other,
81 count(crashreports_heartbeat.id) as heartbeats
82 FROM
83 crashreports_device
84 JOIN
85 crashreports_heartbeat
86 ON
87 crashreports_device.id = crashreports_heartbeat.device_id
88 where
89 crashreports_device.uuid=%s
90 group by build_fingerprint;
91 '''
92 uuid = params.get('uuid', '18f530d7-e9c3-4dcf-adba-3dddcd7d3155')
93 param_array = [uuid]
94 cursor.execute(query, param_array)
95
96
97def sqlite_execute_device_report_history(cursor, params):
Dirk Vogt62ff7f22017-05-04 16:07:21 +020098 query = '''
99 SELECT
Dirk Vogta10db9d2017-05-05 09:08:45 +0200100 strftime("%%Y-%%m-%%d",crashreports_heartbeat.date) as date,
Dirk Vogt62ff7f22017-05-04 16:07:21 +0200101 count(crashreports_heartbeat.id) as heartbeats,
102 (
Dirk Vogta10db9d2017-05-05 09:08:45 +0200103 select count(id) from crashreports_crashreport
104 where
105 boot_reason in ("RTC alarm")
Dirk Vogt62ff7f22017-05-04 16:07:21 +0200106 and strftime("%%Y-%%m-%%d",crashreports_crashreport.date) == strftime("%%Y-%%m-%%d",crashreports_heartbeat.date)
107 and crashreports_device.id == crashreports_crashreport.device_id
108 ) as smpl,
109 (
Dirk Vogta10db9d2017-05-05 09:08:45 +0200110 select count(id) from crashreports_crashreport
111 where
112 boot_reason in ("UNKNOWN", "keyboard power on")
Dirk Vogt62ff7f22017-05-04 16:07:21 +0200113 and strftime("%%Y-%%m-%%d",crashreports_crashreport.date) == strftime("%%Y-%%m-%%d",crashreports_heartbeat.date)
114 and crashreports_device.id == crashreports_crashreport.device_id
115 ) as prob_crashes,
116 (
Dirk Vogta10db9d2017-05-05 09:08:45 +0200117 select count(id) from crashreports_crashreport
118 where
119 boot_reason not in ("RTC alarm", "UNKNOWN", "keyboard power on")
Dirk Vogt62ff7f22017-05-04 16:07:21 +0200120 and strftime("%%Y-%%m-%%d",crashreports_crashreport.date) == strftime("%%Y-%%m-%%d",crashreports_heartbeat.date)
121 and crashreports_device.id == crashreports_crashreport.device_id
122 ) as other
Dirk Vogta10db9d2017-05-05 09:08:45 +0200123 from crashreports_device
124 join
125 crashreports_heartbeat on crashreports_device.id == crashreports_heartbeat.device_id
126 where
Dirk Vogt62ff7f22017-05-04 16:07:21 +0200127 crashreports_device.uuid = %s
128 group by date;
129 '''
130 uuid = params.get('uuid', '18f530d7-e9c3-4dcf-adba-3dddcd7d3155')
131 param_array = [uuid]
132 cursor.execute(query, param_array)
Dirk Vogt0e565a72017-10-09 15:14:21 +0200133
134
135def psql_execute_device_report_history(cursor, params):
136 query = '''
137 SELECT
138 crashreports_heartbeat.date::date as date,
139 count(crashreports_heartbeat.id) as heartbeats,
140 count(crashreports_crashreport.id) filter (where crashreports_crashreport.boot_reason in ('RTC alarm')) as SMPL,
141 count(crashreports_crashreport.id) filter (where crashreports_crashreport.boot_reason in ('UNKNOWN', 'keyboard power on')) as prob_crashes,
142 count(crashreports_crashreport.id) filter (where crashreports_crashreport.boot_reason not in ('RTC alarm', 'UNKNOWN', 'keyboard power on')) as other
143 from crashreports_device
144 join crashreports_heartbeat on crashreports_device.id = crashreports_heartbeat.device_id
145 left join crashreports_crashreport on crashreports_device.id = crashreports_crashreport.device_id and crashreports_heartbeat.date::date = crashreports_crashreport.date::date
146 where
Dirk Vogt1e8eb122017-12-06 10:56:37 +0100147 crashreports_device.uuid = %s group by crashreports_heartbeat.date, crashreports_device.id;
Dirk Vogt0e565a72017-10-09 15:14:21 +0200148 '''
149 uuid = params.get('uuid', '18f530d7-e9c3-4dcf-adba-3dddcd7d3155')
150 param_array = [uuid]
151 cursor.execute(query, param_array)