Dirk Vogt | 0e565a7 | 2017-10-09 15:14:21 +0200 | [diff] [blame] | 1 | from django.conf import settings |
| 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 | class FormatDict(dict): |
| 5 | def __missing__(self, key): |
| 6 | return "{" + key + "}" |
| 7 | |
Mitja Nikolaus | cb50f2c | 2018-08-24 13:54:48 +0200 | [diff] [blame] | 8 | |
Dirk Vogt | 62ff7f2 | 2017-05-04 16:07:21 +0200 | [diff] [blame] | 9 | def fill_in_build_fingerprints(query, build_fingerprints): |
Mitja Nikolaus | cb50f2c | 2018-08-24 13:54:48 +0200 | [diff] [blame] | 10 | all_fingerprints_query = ( |
| 11 | "select distinct build_fingerprint from crashreports_crashreport" |
| 12 | ) |
| 13 | if len(build_fingerprints) > 0: |
Dirk Vogt | 62ff7f2 | 2017-05-04 16:07:21 +0200 | [diff] [blame] | 14 | return query.format( |
Mitja Nikolaus | cb50f2c | 2018-08-24 13:54:48 +0200 | [diff] [blame] | 15 | FormatDict( |
| 16 | fingerprint_placeholers=",".join( |
| 17 | ["%s"] * len(build_fingerprints) |
| 18 | ) |
| 19 | ) |
| 20 | ) |
Dirk Vogt | 62ff7f2 | 2017-05-04 16:07:21 +0200 | [diff] [blame] | 21 | else: |
Mitja Nikolaus | cb50f2c | 2018-08-24 13:54:48 +0200 | [diff] [blame] | 22 | return query.format( |
| 23 | FormatDict(fingerprint_placeholers=all_fingerprints_query) |
| 24 | ) |
Dirk Vogt | 62ff7f2 | 2017-05-04 16:07:21 +0200 | [diff] [blame] | 25 | |
Dirk Vogt | 0e565a7 | 2017-10-09 15:14:21 +0200 | [diff] [blame] | 26 | |
Dirk Vogt | 62ff7f2 | 2017-05-04 16:07:21 +0200 | [diff] [blame] | 27 | def execute_device_update_history_query(cursor, params): |
Mitja Nikolaus | cb50f2c | 2018-08-24 13:54:48 +0200 | [diff] [blame] | 28 | if ( |
| 29 | settings.DATABASES["default"]["ENGINE"] |
| 30 | == "django.db.backends.postgresql_psycopg2" |
| 31 | ): |
Dirk Vogt | 0e565a7 | 2017-10-09 15:14:21 +0200 | [diff] [blame] | 32 | return psql_execute_device_update_history_query(cursor, params) |
| 33 | else: |
| 34 | return sqlite_execute_device_update_history_query(cursor, params) |
| 35 | |
| 36 | |
| 37 | def execute_device_report_history(cursor, params): |
Mitja Nikolaus | cb50f2c | 2018-08-24 13:54:48 +0200 | [diff] [blame] | 38 | if ( |
| 39 | settings.DATABASES["default"]["ENGINE"] |
| 40 | == "django.db.backends.postgresql_psycopg2" |
| 41 | ): |
Dirk Vogt | 0e565a7 | 2017-10-09 15:14:21 +0200 | [diff] [blame] | 42 | return psql_execute_device_report_history(cursor, params) |
| 43 | else: |
| 44 | return sqlite_execute_device_report_history(cursor, params) |
| 45 | |
Mitja Nikolaus | cb50f2c | 2018-08-24 13:54:48 +0200 | [diff] [blame] | 46 | |
Dirk Vogt | 0e565a7 | 2017-10-09 15:14:21 +0200 | [diff] [blame] | 47 | def sqlite_execute_device_update_history_query(cursor, params): |
Mitja Nikolaus | cb50f2c | 2018-08-24 13:54:48 +0200 | [diff] [blame] | 48 | query = """ |
Dirk Vogt | a10db9d | 2017-05-05 09:08:45 +0200 | [diff] [blame] | 49 | SELECT |
Dirk Vogt | 62ff7f2 | 2017-05-04 16:07:21 +0200 | [diff] [blame] | 50 | min(crashreports_heartbeat.date) as update_date, |
Dirk Vogt | a10db9d | 2017-05-05 09:08:45 +0200 | [diff] [blame] | 51 | build_fingerprint, |
| 52 | ( select count(crashreports_crashreport.id) from crashreports_crashreport |
| 53 | where boot_reason in ("UNKNOWN", "keyboard power on") |
| 54 | and crashreports_device.id == crashreports_crashreport.device_id |
| 55 | and crashreports_crashreport.build_fingerprint == crashreports_heartbeat.build_fingerprint ) as prob_crashes, |
| 56 | ( select count(crashreports_crashreport.id) from crashreports_crashreport |
| 57 | where boot_reason in ("RTC alarm") |
| 58 | and crashreports_device.id == crashreports_crashreport.device_id |
Dirk Vogt | 38f606c | 2017-12-05 15:30:27 +0100 | [diff] [blame] | 59 | and crashreports_crashreport.build_fingerprint == crashreports_heartbeat.build_fingerprint ) as smpl, |
Dirk Vogt | a10db9d | 2017-05-05 09:08:45 +0200 | [diff] [blame] | 60 | ( select count(crashreports_crashreport.id) from crashreports_crashreport |
| 61 | where boot_reason not in ("UNKNOWN", "keyboard power on", "RTC alarm") |
| 62 | and crashreports_device.id == crashreports_crashreport.device_id |
| 63 | and crashreports_crashreport.build_fingerprint == crashreports_heartbeat.build_fingerprint ) as other, |
| 64 | count(crashreports_heartbeat.id) as heartbeats |
| 65 | FROM |
Dirk Vogt | 62ff7f2 | 2017-05-04 16:07:21 +0200 | [diff] [blame] | 66 | crashreports_device |
Dirk Vogt | a10db9d | 2017-05-05 09:08:45 +0200 | [diff] [blame] | 67 | JOIN |
| 68 | crashreports_heartbeat |
| 69 | ON |
| 70 | crashreports_device.id == crashreports_heartbeat.device_id |
| 71 | where |
Dirk Vogt | 62ff7f2 | 2017-05-04 16:07:21 +0200 | [diff] [blame] | 72 | crashreports_device.uuid=%s |
| 73 | group by build_fingerprint; |
Mitja Nikolaus | cb50f2c | 2018-08-24 13:54:48 +0200 | [diff] [blame] | 74 | """ |
| 75 | uuid = params.get("uuid", "18f530d7-e9c3-4dcf-adba-3dddcd7d3155") |
Dirk Vogt | 62ff7f2 | 2017-05-04 16:07:21 +0200 | [diff] [blame] | 76 | param_array = [uuid] |
| 77 | cursor.execute(query, param_array) |
| 78 | |
| 79 | |
Dirk Vogt | 0e565a7 | 2017-10-09 15:14:21 +0200 | [diff] [blame] | 80 | def psql_execute_device_update_history_query(cursor, params): |
Mitja Nikolaus | cb50f2c | 2018-08-24 13:54:48 +0200 | [diff] [blame] | 81 | query = """ |
Dirk Vogt | 0e565a7 | 2017-10-09 15:14:21 +0200 | [diff] [blame] | 82 | SELECT |
| 83 | min(crashreports_heartbeat.date) as update_date, |
| 84 | build_fingerprint, |
Dirk Vogt | 38f606c | 2017-12-05 15:30:27 +0100 | [diff] [blame] | 85 | max(crashreports_device.id), |
Dirk Vogt | 0e565a7 | 2017-10-09 15:14:21 +0200 | [diff] [blame] | 86 | ( select count(crashreports_crashreport.id) from crashreports_crashreport |
| 87 | where boot_reason in ('UNKNOWN', 'keyboard power on') |
Dirk Vogt | 38f606c | 2017-12-05 15:30:27 +0100 | [diff] [blame] | 88 | and max(crashreports_device.id) = crashreports_crashreport.device_id |
Dirk Vogt | 0e565a7 | 2017-10-09 15:14:21 +0200 | [diff] [blame] | 89 | and crashreports_crashreport.build_fingerprint = crashreports_heartbeat.build_fingerprint ) as prob_crashes, |
| 90 | ( select count(crashreports_crashreport.id) from crashreports_crashreport |
| 91 | where boot_reason in ('RTC alarm') |
Dirk Vogt | 38f606c | 2017-12-05 15:30:27 +0100 | [diff] [blame] | 92 | and max(crashreports_device.id) = crashreports_crashreport.device_id |
| 93 | and crashreports_crashreport.build_fingerprint = crashreports_heartbeat.build_fingerprint ) as smpl, |
Dirk Vogt | 0e565a7 | 2017-10-09 15:14:21 +0200 | [diff] [blame] | 94 | ( select count(crashreports_crashreport.id) from crashreports_crashreport |
| 95 | where boot_reason not in ('UNKNOWN', 'keyboard power on', 'RTC alarm') |
Dirk Vogt | 38f606c | 2017-12-05 15:30:27 +0100 | [diff] [blame] | 96 | and max(crashreports_device.id) = crashreports_crashreport.device_id |
Dirk Vogt | 0e565a7 | 2017-10-09 15:14:21 +0200 | [diff] [blame] | 97 | and crashreports_crashreport.build_fingerprint = crashreports_heartbeat.build_fingerprint ) as other, |
| 98 | count(crashreports_heartbeat.id) as heartbeats |
| 99 | FROM |
| 100 | crashreports_device |
| 101 | JOIN |
| 102 | crashreports_heartbeat |
| 103 | ON |
| 104 | crashreports_device.id = crashreports_heartbeat.device_id |
| 105 | where |
| 106 | crashreports_device.uuid=%s |
| 107 | group by build_fingerprint; |
Mitja Nikolaus | cb50f2c | 2018-08-24 13:54:48 +0200 | [diff] [blame] | 108 | """ |
| 109 | uuid = params.get("uuid", "18f530d7-e9c3-4dcf-adba-3dddcd7d3155") |
Dirk Vogt | 0e565a7 | 2017-10-09 15:14:21 +0200 | [diff] [blame] | 110 | param_array = [uuid] |
| 111 | cursor.execute(query, param_array) |
| 112 | |
| 113 | |
| 114 | def sqlite_execute_device_report_history(cursor, params): |
Mitja Nikolaus | cb50f2c | 2018-08-24 13:54:48 +0200 | [diff] [blame] | 115 | query = """ |
Dirk Vogt | 62ff7f2 | 2017-05-04 16:07:21 +0200 | [diff] [blame] | 116 | SELECT |
Dirk Vogt | a10db9d | 2017-05-05 09:08:45 +0200 | [diff] [blame] | 117 | strftime("%%Y-%%m-%%d",crashreports_heartbeat.date) as date, |
Dirk Vogt | 62ff7f2 | 2017-05-04 16:07:21 +0200 | [diff] [blame] | 118 | count(crashreports_heartbeat.id) as heartbeats, |
| 119 | ( |
Dirk Vogt | a10db9d | 2017-05-05 09:08:45 +0200 | [diff] [blame] | 120 | select count(id) from crashreports_crashreport |
| 121 | where |
| 122 | boot_reason in ("RTC alarm") |
Dirk Vogt | 62ff7f2 | 2017-05-04 16:07:21 +0200 | [diff] [blame] | 123 | and strftime("%%Y-%%m-%%d",crashreports_crashreport.date) == strftime("%%Y-%%m-%%d",crashreports_heartbeat.date) |
| 124 | and crashreports_device.id == crashreports_crashreport.device_id |
| 125 | ) as smpl, |
| 126 | ( |
Dirk Vogt | a10db9d | 2017-05-05 09:08:45 +0200 | [diff] [blame] | 127 | select count(id) from crashreports_crashreport |
| 128 | where |
| 129 | boot_reason in ("UNKNOWN", "keyboard power on") |
Dirk Vogt | 62ff7f2 | 2017-05-04 16:07:21 +0200 | [diff] [blame] | 130 | and strftime("%%Y-%%m-%%d",crashreports_crashreport.date) == strftime("%%Y-%%m-%%d",crashreports_heartbeat.date) |
| 131 | and crashreports_device.id == crashreports_crashreport.device_id |
| 132 | ) as prob_crashes, |
| 133 | ( |
Dirk Vogt | a10db9d | 2017-05-05 09:08:45 +0200 | [diff] [blame] | 134 | select count(id) from crashreports_crashreport |
| 135 | where |
| 136 | boot_reason not in ("RTC alarm", "UNKNOWN", "keyboard power on") |
Dirk Vogt | 62ff7f2 | 2017-05-04 16:07:21 +0200 | [diff] [blame] | 137 | and strftime("%%Y-%%m-%%d",crashreports_crashreport.date) == strftime("%%Y-%%m-%%d",crashreports_heartbeat.date) |
| 138 | and crashreports_device.id == crashreports_crashreport.device_id |
| 139 | ) as other |
Dirk Vogt | a10db9d | 2017-05-05 09:08:45 +0200 | [diff] [blame] | 140 | from crashreports_device |
| 141 | join |
| 142 | crashreports_heartbeat on crashreports_device.id == crashreports_heartbeat.device_id |
| 143 | where |
Dirk Vogt | 62ff7f2 | 2017-05-04 16:07:21 +0200 | [diff] [blame] | 144 | crashreports_device.uuid = %s |
| 145 | group by date; |
Mitja Nikolaus | cb50f2c | 2018-08-24 13:54:48 +0200 | [diff] [blame] | 146 | """ |
| 147 | uuid = params.get("uuid", "18f530d7-e9c3-4dcf-adba-3dddcd7d3155") |
Dirk Vogt | 62ff7f2 | 2017-05-04 16:07:21 +0200 | [diff] [blame] | 148 | param_array = [uuid] |
| 149 | cursor.execute(query, param_array) |
Dirk Vogt | 0e565a7 | 2017-10-09 15:14:21 +0200 | [diff] [blame] | 150 | |
| 151 | |
| 152 | def psql_execute_device_report_history(cursor, params): |
Mitja Nikolaus | cb50f2c | 2018-08-24 13:54:48 +0200 | [diff] [blame] | 153 | query = """ |
Dirk Vogt | 0e565a7 | 2017-10-09 15:14:21 +0200 | [diff] [blame] | 154 | SELECT |
| 155 | crashreports_heartbeat.date::date as date, |
| 156 | count(crashreports_heartbeat.id) as heartbeats, |
| 157 | count(crashreports_crashreport.id) filter (where crashreports_crashreport.boot_reason in ('RTC alarm')) as SMPL, |
| 158 | count(crashreports_crashreport.id) filter (where crashreports_crashreport.boot_reason in ('UNKNOWN', 'keyboard power on')) as prob_crashes, |
| 159 | count(crashreports_crashreport.id) filter (where crashreports_crashreport.boot_reason not in ('RTC alarm', 'UNKNOWN', 'keyboard power on')) as other |
| 160 | from crashreports_device |
| 161 | join crashreports_heartbeat on crashreports_device.id = crashreports_heartbeat.device_id |
| 162 | left join crashreports_crashreport on crashreports_device.id = crashreports_crashreport.device_id and crashreports_heartbeat.date::date = crashreports_crashreport.date::date |
| 163 | where |
Dirk Vogt | 1e8eb12 | 2017-12-06 10:56:37 +0100 | [diff] [blame] | 164 | crashreports_device.uuid = %s group by crashreports_heartbeat.date, crashreports_device.id; |
Mitja Nikolaus | cb50f2c | 2018-08-24 13:54:48 +0200 | [diff] [blame] | 165 | """ |
| 166 | uuid = params.get("uuid", "18f530d7-e9c3-4dcf-adba-3dddcd7d3155") |
Dirk Vogt | 0e565a7 | 2017-10-09 15:14:21 +0200 | [diff] [blame] | 167 | param_array = [uuid] |
| 168 | cursor.execute(query, param_array) |