Mitja Nikolaus | 6a67913 | 2018-08-30 14:35:29 +0200 | [diff] [blame] | 1 | """SQL queries for getting device statistics.""" |
| 2 | |
Dirk Vogt | 0e565a7 | 2017-10-09 15:14:21 +0200 | [diff] [blame] | 3 | from django.conf import settings |
| 4 | |
Mitja Nikolaus | cb50f2c | 2018-08-24 13:54:48 +0200 | [diff] [blame] | 5 | |
Dirk Vogt | 62ff7f2 | 2017-05-04 16:07:21 +0200 | [diff] [blame] | 6 | def execute_device_update_history_query(cursor, params): |
Mitja Nikolaus | 6a67913 | 2018-08-30 14:35:29 +0200 | [diff] [blame] | 7 | """Query the device update history.""" |
Mitja Nikolaus | cb50f2c | 2018-08-24 13:54:48 +0200 | [diff] [blame] | 8 | if ( |
| 9 | settings.DATABASES["default"]["ENGINE"] |
| 10 | == "django.db.backends.postgresql_psycopg2" |
| 11 | ): |
Dirk Vogt | 0e565a7 | 2017-10-09 15:14:21 +0200 | [diff] [blame] | 12 | return psql_execute_device_update_history_query(cursor, params) |
Mitja Nikolaus | b4e3bec | 2018-08-30 17:16:21 +0200 | [diff] [blame] | 13 | return sqlite_execute_device_update_history_query(cursor, params) |
Dirk Vogt | 0e565a7 | 2017-10-09 15:14:21 +0200 | [diff] [blame] | 14 | |
| 15 | |
| 16 | def execute_device_report_history(cursor, params): |
Mitja Nikolaus | 6a67913 | 2018-08-30 14:35:29 +0200 | [diff] [blame] | 17 | """Query the device report history.""" |
Mitja Nikolaus | cb50f2c | 2018-08-24 13:54:48 +0200 | [diff] [blame] | 18 | if ( |
| 19 | settings.DATABASES["default"]["ENGINE"] |
| 20 | == "django.db.backends.postgresql_psycopg2" |
| 21 | ): |
Dirk Vogt | 0e565a7 | 2017-10-09 15:14:21 +0200 | [diff] [blame] | 22 | return psql_execute_device_report_history(cursor, params) |
Mitja Nikolaus | b4e3bec | 2018-08-30 17:16:21 +0200 | [diff] [blame] | 23 | return sqlite_execute_device_report_history(cursor, params) |
Dirk Vogt | 0e565a7 | 2017-10-09 15:14:21 +0200 | [diff] [blame] | 24 | |
Mitja Nikolaus | cb50f2c | 2018-08-24 13:54:48 +0200 | [diff] [blame] | 25 | |
Dirk Vogt | 0e565a7 | 2017-10-09 15:14:21 +0200 | [diff] [blame] | 26 | def sqlite_execute_device_update_history_query(cursor, params): |
Mitja Nikolaus | 6a67913 | 2018-08-30 14:35:29 +0200 | [diff] [blame] | 27 | """Execute SQLite query for getting the device update history.""" |
Mitja Nikolaus | cb50f2c | 2018-08-24 13:54:48 +0200 | [diff] [blame] | 28 | query = """ |
Dirk Vogt | a10db9d | 2017-05-05 09:08:45 +0200 | [diff] [blame] | 29 | SELECT |
Dirk Vogt | 62ff7f2 | 2017-05-04 16:07:21 +0200 | [diff] [blame] | 30 | min(crashreports_heartbeat.date) as update_date, |
Dirk Vogt | a10db9d | 2017-05-05 09:08:45 +0200 | [diff] [blame] | 31 | build_fingerprint, |
| 32 | ( select count(crashreports_crashreport.id) from crashreports_crashreport |
| 33 | where boot_reason in ("UNKNOWN", "keyboard power on") |
| 34 | and crashreports_device.id == crashreports_crashreport.device_id |
| 35 | and crashreports_crashreport.build_fingerprint == crashreports_heartbeat.build_fingerprint ) as prob_crashes, |
| 36 | ( select count(crashreports_crashreport.id) from crashreports_crashreport |
| 37 | where boot_reason in ("RTC alarm") |
| 38 | and crashreports_device.id == crashreports_crashreport.device_id |
Dirk Vogt | 38f606c | 2017-12-05 15:30:27 +0100 | [diff] [blame] | 39 | and crashreports_crashreport.build_fingerprint == crashreports_heartbeat.build_fingerprint ) as smpl, |
Dirk Vogt | a10db9d | 2017-05-05 09:08:45 +0200 | [diff] [blame] | 40 | ( select count(crashreports_crashreport.id) from crashreports_crashreport |
| 41 | where boot_reason not in ("UNKNOWN", "keyboard power on", "RTC alarm") |
| 42 | and crashreports_device.id == crashreports_crashreport.device_id |
| 43 | and crashreports_crashreport.build_fingerprint == crashreports_heartbeat.build_fingerprint ) as other, |
| 44 | count(crashreports_heartbeat.id) as heartbeats |
| 45 | FROM |
Dirk Vogt | 62ff7f2 | 2017-05-04 16:07:21 +0200 | [diff] [blame] | 46 | crashreports_device |
Dirk Vogt | a10db9d | 2017-05-05 09:08:45 +0200 | [diff] [blame] | 47 | JOIN |
| 48 | crashreports_heartbeat |
| 49 | ON |
| 50 | crashreports_device.id == crashreports_heartbeat.device_id |
| 51 | where |
Dirk Vogt | 62ff7f2 | 2017-05-04 16:07:21 +0200 | [diff] [blame] | 52 | crashreports_device.uuid=%s |
| 53 | group by build_fingerprint; |
Mitja Nikolaus | 21075cf | 2018-08-30 16:53:21 +0200 | [diff] [blame] | 54 | """ # noqa: E501 |
Mitja Nikolaus | cb50f2c | 2018-08-24 13:54:48 +0200 | [diff] [blame] | 55 | uuid = params.get("uuid", "18f530d7-e9c3-4dcf-adba-3dddcd7d3155") |
Dirk Vogt | 62ff7f2 | 2017-05-04 16:07:21 +0200 | [diff] [blame] | 56 | param_array = [uuid] |
| 57 | cursor.execute(query, param_array) |
| 58 | |
| 59 | |
Dirk Vogt | 0e565a7 | 2017-10-09 15:14:21 +0200 | [diff] [blame] | 60 | def psql_execute_device_update_history_query(cursor, params): |
Mitja Nikolaus | 6a67913 | 2018-08-30 14:35:29 +0200 | [diff] [blame] | 61 | """Execute PostgreSQL query for getting the device update history.""" |
Mitja Nikolaus | cb50f2c | 2018-08-24 13:54:48 +0200 | [diff] [blame] | 62 | query = """ |
Dirk Vogt | 0e565a7 | 2017-10-09 15:14:21 +0200 | [diff] [blame] | 63 | SELECT |
| 64 | min(crashreports_heartbeat.date) as update_date, |
| 65 | build_fingerprint, |
Dirk Vogt | 38f606c | 2017-12-05 15:30:27 +0100 | [diff] [blame] | 66 | max(crashreports_device.id), |
Dirk Vogt | 0e565a7 | 2017-10-09 15:14:21 +0200 | [diff] [blame] | 67 | ( select count(crashreports_crashreport.id) from crashreports_crashreport |
| 68 | where boot_reason in ('UNKNOWN', 'keyboard power on') |
Dirk Vogt | 38f606c | 2017-12-05 15:30:27 +0100 | [diff] [blame] | 69 | and max(crashreports_device.id) = crashreports_crashreport.device_id |
Dirk Vogt | 0e565a7 | 2017-10-09 15:14:21 +0200 | [diff] [blame] | 70 | and crashreports_crashreport.build_fingerprint = crashreports_heartbeat.build_fingerprint ) as prob_crashes, |
| 71 | ( select count(crashreports_crashreport.id) from crashreports_crashreport |
| 72 | where boot_reason in ('RTC alarm') |
Dirk Vogt | 38f606c | 2017-12-05 15:30:27 +0100 | [diff] [blame] | 73 | and max(crashreports_device.id) = crashreports_crashreport.device_id |
| 74 | and crashreports_crashreport.build_fingerprint = crashreports_heartbeat.build_fingerprint ) as smpl, |
Dirk Vogt | 0e565a7 | 2017-10-09 15:14:21 +0200 | [diff] [blame] | 75 | ( select count(crashreports_crashreport.id) from crashreports_crashreport |
| 76 | where boot_reason not in ('UNKNOWN', 'keyboard power on', 'RTC alarm') |
Dirk Vogt | 38f606c | 2017-12-05 15:30:27 +0100 | [diff] [blame] | 77 | and max(crashreports_device.id) = crashreports_crashreport.device_id |
Dirk Vogt | 0e565a7 | 2017-10-09 15:14:21 +0200 | [diff] [blame] | 78 | and crashreports_crashreport.build_fingerprint = crashreports_heartbeat.build_fingerprint ) as other, |
| 79 | count(crashreports_heartbeat.id) as heartbeats |
| 80 | FROM |
| 81 | crashreports_device |
| 82 | JOIN |
| 83 | crashreports_heartbeat |
| 84 | ON |
| 85 | crashreports_device.id = crashreports_heartbeat.device_id |
| 86 | where |
| 87 | crashreports_device.uuid=%s |
| 88 | group by build_fingerprint; |
Mitja Nikolaus | 21075cf | 2018-08-30 16:53:21 +0200 | [diff] [blame] | 89 | """ # noqa: E501 |
Mitja Nikolaus | cb50f2c | 2018-08-24 13:54:48 +0200 | [diff] [blame] | 90 | uuid = params.get("uuid", "18f530d7-e9c3-4dcf-adba-3dddcd7d3155") |
Dirk Vogt | 0e565a7 | 2017-10-09 15:14:21 +0200 | [diff] [blame] | 91 | param_array = [uuid] |
| 92 | cursor.execute(query, param_array) |
| 93 | |
| 94 | |
| 95 | def sqlite_execute_device_report_history(cursor, params): |
Mitja Nikolaus | 6a67913 | 2018-08-30 14:35:29 +0200 | [diff] [blame] | 96 | """Execute SQLite query for getting the device report history.""" |
Mitja Nikolaus | cb50f2c | 2018-08-24 13:54:48 +0200 | [diff] [blame] | 97 | query = """ |
Dirk Vogt | 62ff7f2 | 2017-05-04 16:07:21 +0200 | [diff] [blame] | 98 | SELECT |
Dirk Vogt | a10db9d | 2017-05-05 09:08:45 +0200 | [diff] [blame] | 99 | strftime("%%Y-%%m-%%d",crashreports_heartbeat.date) as date, |
Dirk Vogt | 62ff7f2 | 2017-05-04 16:07:21 +0200 | [diff] [blame] | 100 | count(crashreports_heartbeat.id) as heartbeats, |
| 101 | ( |
Dirk Vogt | a10db9d | 2017-05-05 09:08:45 +0200 | [diff] [blame] | 102 | select count(id) from crashreports_crashreport |
| 103 | where |
| 104 | boot_reason in ("RTC alarm") |
Dirk Vogt | 62ff7f2 | 2017-05-04 16:07:21 +0200 | [diff] [blame] | 105 | and strftime("%%Y-%%m-%%d",crashreports_crashreport.date) == strftime("%%Y-%%m-%%d",crashreports_heartbeat.date) |
| 106 | and crashreports_device.id == crashreports_crashreport.device_id |
| 107 | ) as smpl, |
| 108 | ( |
Dirk Vogt | a10db9d | 2017-05-05 09:08:45 +0200 | [diff] [blame] | 109 | select count(id) from crashreports_crashreport |
| 110 | where |
| 111 | boot_reason in ("UNKNOWN", "keyboard power on") |
Dirk Vogt | 62ff7f2 | 2017-05-04 16:07:21 +0200 | [diff] [blame] | 112 | and strftime("%%Y-%%m-%%d",crashreports_crashreport.date) == strftime("%%Y-%%m-%%d",crashreports_heartbeat.date) |
| 113 | and crashreports_device.id == crashreports_crashreport.device_id |
| 114 | ) as prob_crashes, |
| 115 | ( |
Dirk Vogt | a10db9d | 2017-05-05 09:08:45 +0200 | [diff] [blame] | 116 | select count(id) from crashreports_crashreport |
| 117 | where |
| 118 | boot_reason not in ("RTC alarm", "UNKNOWN", "keyboard power on") |
Dirk Vogt | 62ff7f2 | 2017-05-04 16:07:21 +0200 | [diff] [blame] | 119 | and strftime("%%Y-%%m-%%d",crashreports_crashreport.date) == strftime("%%Y-%%m-%%d",crashreports_heartbeat.date) |
| 120 | and crashreports_device.id == crashreports_crashreport.device_id |
| 121 | ) as other |
Dirk Vogt | a10db9d | 2017-05-05 09:08:45 +0200 | [diff] [blame] | 122 | from crashreports_device |
| 123 | join |
| 124 | crashreports_heartbeat on crashreports_device.id == crashreports_heartbeat.device_id |
| 125 | where |
Dirk Vogt | 62ff7f2 | 2017-05-04 16:07:21 +0200 | [diff] [blame] | 126 | crashreports_device.uuid = %s |
| 127 | group by date; |
Mitja Nikolaus | 21075cf | 2018-08-30 16:53:21 +0200 | [diff] [blame] | 128 | """ # noqa: E501 |
Mitja Nikolaus | cb50f2c | 2018-08-24 13:54:48 +0200 | [diff] [blame] | 129 | uuid = params.get("uuid", "18f530d7-e9c3-4dcf-adba-3dddcd7d3155") |
Dirk Vogt | 62ff7f2 | 2017-05-04 16:07:21 +0200 | [diff] [blame] | 130 | param_array = [uuid] |
| 131 | cursor.execute(query, param_array) |
Dirk Vogt | 0e565a7 | 2017-10-09 15:14:21 +0200 | [diff] [blame] | 132 | |
| 133 | |
| 134 | def psql_execute_device_report_history(cursor, params): |
Mitja Nikolaus | 6a67913 | 2018-08-30 14:35:29 +0200 | [diff] [blame] | 135 | """Execute Postgresql query for getting the device report history.""" |
Mitja Nikolaus | cb50f2c | 2018-08-24 13:54:48 +0200 | [diff] [blame] | 136 | query = """ |
Dirk Vogt | 0e565a7 | 2017-10-09 15:14:21 +0200 | [diff] [blame] | 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 Vogt | 1e8eb12 | 2017-12-06 10:56:37 +0100 | [diff] [blame] | 147 | crashreports_device.uuid = %s group by crashreports_heartbeat.date, crashreports_device.id; |
Mitja Nikolaus | 21075cf | 2018-08-30 16:53:21 +0200 | [diff] [blame] | 148 | """ # noqa: E501 |
Mitja Nikolaus | cb50f2c | 2018-08-24 13:54:48 +0200 | [diff] [blame] | 149 | uuid = params.get("uuid", "18f530d7-e9c3-4dcf-adba-3dddcd7d3155") |
Dirk Vogt | 0e565a7 | 2017-10-09 15:14:21 +0200 | [diff] [blame] | 150 | param_array = [uuid] |
| 151 | cursor.execute(query, param_array) |