1-- 2-- Copyright 2019 The Android Open Source Project 3-- 4-- Licensed under the Apache License, Version 2.0 (the "License"); 5-- you may not use this file except in compliance with the License. 6-- You may obtain a copy of the License at 7-- 8-- https://www.apache.org/licenses/LICENSE-2.0 9-- 10-- Unless required by applicable law or agreed to in writing, software 11-- distributed under the License is distributed on an "AS IS" BASIS, 12-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. 13-- See the License for the specific language governing permissions and 14-- limitations under the License. 15-- 16CREATE VIEW battery_view AS 17SELECT 18 all_ts.ts as ts, 19 current_avg_ua, 20 capacity_percent, 21 charge_uah, 22 current_ua 23FROM ( 24 SELECT distinct(ts) AS ts 25 FROM counter c 26 JOIN counter_track t on c.track_id = t.id 27 WHERE name LIKE 'batt.%' 28) AS all_ts 29LEFT JOIN ( 30 SELECT ts, value AS current_avg_ua 31 FROM counter c 32 JOIN counter_track t on c.track_id = t.id 33 WHERE name='batt.current.avg_ua' 34) USING(ts) 35LEFT JOIN ( 36 SELECT ts, value AS capacity_percent 37 FROM counter c 38 JOIN counter_track t on c.track_id = t.id 39 WHERE name='batt.capacity_pct' 40) USING(ts) 41LEFT JOIN ( 42 SELECT ts, value AS charge_uah 43 FROM counter c 44 JOIN counter_track t on c.track_id = t.id 45 WHERE name='batt.charge_uah' 46) USING(ts) 47LEFT JOIN ( 48 SELECT ts, value AS current_ua 49 FROM counter c 50 JOIN counter_track t on c.track_id = t.id 51 WHERE name='batt.current_ua' 52) USING(ts) 53ORDER BY ts; 54 55DROP TABLE IF EXISTS android_batt_wakelocks_raw_; 56CREATE TABLE android_batt_wakelocks_raw_ AS 57SELECT 58 ts, 59 dur, 60 ts+dur AS ts_end 61FROM slice 62WHERE slice.name LIKE 'WakeLock %' AND dur != -1; 63 64DROP TABLE IF EXISTS android_batt_wakelocks_labelled_; 65CREATE TABLE android_batt_wakelocks_labelled_ AS 66SELECT 67 *, 68 NOT EXISTS ( 69 SELECT * 70 FROM android_batt_wakelocks_raw_ AS t2 71 WHERE t2.ts < t1.ts 72 AND t2.ts_end >= t1.ts 73 ) AS no_overlap_at_start, 74 NOT EXISTS ( 75 SELECT * 76 FROM android_batt_wakelocks_raw_ AS t2 77 WHERE t2.ts_end > t1.ts_end 78 AND t2.ts <= t1.ts_end 79 ) AS no_overlap_at_end 80FROM android_batt_wakelocks_raw_ AS t1; 81 82CREATE VIEW android_batt_wakelocks_merged AS 83SELECT 84 ts, 85 ( 86 SELECT min(ts_end) 87 FROM android_batt_wakelocks_labelled_ AS ends 88 WHERE no_overlap_at_end 89 AND ends.ts_end >= starts.ts 90 ) AS ts_end 91FROM android_batt_wakelocks_labelled_ AS starts 92WHERE no_overlap_at_start; 93 94SELECT RUN_METRIC('android/counter_span_view.sql', 95 'table_name', 'screen_state', 96 'counter_name', 'ScreenState'); 97 98CREATE VIEW android_batt_output AS 99SELECT AndroidBatteryMetric( 100 'battery_counters', ( 101 SELECT RepeatedField( 102 AndroidBatteryMetric_BatteryCounters( 103 'timestamp_ns', ts, 104 'charge_counter_uah', charge_uah, 105 'capacity_percent', capacity_percent, 106 'current_ua', current_ua, 107 'current_avg_ua', current_avg_ua 108 ) 109 ) 110 FROM battery_view 111 ), 112 'battery_aggregates', ( 113 SELECT AndroidBatteryMetric_BatteryAggregates( 114 'total_screen_off_ns', 115 SUM(CASE WHEN screen_state_val = 1.0 THEN dur ELSE 0 END), 116 'total_screen_on_ns', 117 SUM(CASE WHEN screen_state_val = 2.0 THEN dur ELSE 0 END), 118 'total_screen_doze_ns', 119 SUM(CASE WHEN screen_state_val = 3.0 THEN dur ELSE 0 END) 120 ) 121 FROM screen_state_span 122 ), 123 'battery_aggregates', ( 124 SELECT AndroidBatteryMetric_BatteryAggregates( 125 'total_wakelock_ns', 126 SUM(ts_end - ts) 127 ) 128 FROM android_batt_wakelocks_merged 129 ) 130); 131