Mitja Nikolaus | 6a67913 | 2018-08-30 14:35:29 +0200 | [diff] [blame] | 1 | """SQL queries for getting device statistics.""" |
| 2 | |
Mitja Nikolaus | cb50f2c | 2018-08-24 13:54:48 +0200 | [diff] [blame] | 3 | |
Dirk Vogt | 62ff7f2 | 2017-05-04 16:07:21 +0200 | [diff] [blame] | 4 | def execute_device_update_history_query(cursor, params): |
Mitja Nikolaus | 6a67913 | 2018-08-30 14:35:29 +0200 | [diff] [blame] | 5 | """Query the device update history.""" |
Mitja Nikolaus | 18a96ed | 2018-09-05 16:05:39 +0200 | [diff] [blame] | 6 | query = """ |
| 7 | SELECT |
| 8 | min(crashreports_heartbeat.date) as update_date, |
| 9 | build_fingerprint, |
| 10 | max(crashreports_device.id), |
| 11 | ( select count(crashreports_crashreport.id) from crashreports_crashreport |
| 12 | where boot_reason in ('UNKNOWN', 'keyboard power on') |
| 13 | and max(crashreports_device.id) = crashreports_crashreport.device_id |
| 14 | and crashreports_crashreport.build_fingerprint = crashreports_heartbeat.build_fingerprint ) as prob_crashes, |
| 15 | ( select count(crashreports_crashreport.id) from crashreports_crashreport |
| 16 | where boot_reason in ('RTC alarm') |
| 17 | and max(crashreports_device.id) = crashreports_crashreport.device_id |
| 18 | and crashreports_crashreport.build_fingerprint = crashreports_heartbeat.build_fingerprint ) as smpl, |
| 19 | ( select count(crashreports_crashreport.id) from crashreports_crashreport |
| 20 | where boot_reason not in ('UNKNOWN', 'keyboard power on', 'RTC alarm') |
| 21 | and max(crashreports_device.id) = crashreports_crashreport.device_id |
| 22 | and crashreports_crashreport.build_fingerprint = crashreports_heartbeat.build_fingerprint ) as other, |
| 23 | count(crashreports_heartbeat.id) as heartbeats |
| 24 | FROM |
| 25 | crashreports_device |
| 26 | JOIN |
| 27 | crashreports_heartbeat |
| 28 | ON |
| 29 | crashreports_device.id = crashreports_heartbeat.device_id |
| 30 | where |
| 31 | crashreports_device.uuid=%s |
| 32 | group by build_fingerprint; |
| 33 | """ # noqa: E501 |
| 34 | uuid = params.get("uuid", "18f530d7-e9c3-4dcf-adba-3dddcd7d3155") |
| 35 | param_array = [uuid] |
| 36 | cursor.execute(query, param_array) |
Dirk Vogt | 0e565a7 | 2017-10-09 15:14:21 +0200 | [diff] [blame] | 37 | |
| 38 | |
| 39 | def execute_device_report_history(cursor, params): |
Mitja Nikolaus | 6a67913 | 2018-08-30 14:35:29 +0200 | [diff] [blame] | 40 | """Query the device report history.""" |
Mitja Nikolaus | cb50f2c | 2018-08-24 13:54:48 +0200 | [diff] [blame] | 41 | query = """ |
Mitja Nikolaus | 18a96ed | 2018-09-05 16:05:39 +0200 | [diff] [blame] | 42 | SELECT |
| 43 | crashreports_heartbeat.date::date as date, |
| 44 | count(crashreports_heartbeat.id) as heartbeats, |
| 45 | count(crashreports_crashreport.id) filter (where crashreports_crashreport.boot_reason in ('RTC alarm')) as SMPL, |
| 46 | count(crashreports_crashreport.id) filter (where crashreports_crashreport.boot_reason in ('UNKNOWN', 'keyboard power on')) as prob_crashes, |
| 47 | count(crashreports_crashreport.id) filter (where crashreports_crashreport.boot_reason not in ('RTC alarm', 'UNKNOWN', 'keyboard power on')) as other |
| 48 | from crashreports_device |
| 49 | join crashreports_heartbeat on crashreports_device.id = crashreports_heartbeat.device_id |
| 50 | left join crashreports_crashreport on crashreports_device.id = crashreports_crashreport.device_id and crashreports_heartbeat.date::date = crashreports_crashreport.date::date |
Dirk Vogt | a10db9d | 2017-05-05 09:08:45 +0200 | [diff] [blame] | 51 | where |
Mitja Nikolaus | 18a96ed | 2018-09-05 16:05:39 +0200 | [diff] [blame] | 52 | crashreports_device.uuid = %s group by crashreports_heartbeat.date, crashreports_device.id; |
| 53 | """ # noqa: E501 |
Mitja Nikolaus | cb50f2c | 2018-08-24 13:54:48 +0200 | [diff] [blame] | 54 | uuid = params.get("uuid", "18f530d7-e9c3-4dcf-adba-3dddcd7d3155") |
Dirk Vogt | 0e565a7 | 2017-10-09 15:14:21 +0200 | [diff] [blame] | 55 | param_array = [uuid] |
| 56 | cursor.execute(query, param_array) |