src/trace_processor/stdlib/android/battery_stats.sql - platform/external/perfetto - Git at Google

 --
 -- Copyright 2023 The Android Open Source Project
 --
 -- Licensed under the Apache License, Version 2.0 (the "License");
 -- you may not use this file except in compliance with the License.
 -- You may obtain a copy of the License at
 --
 --     https://www.apache.org/licenses/LICENSE-2.0
 --
 -- Unless required by applicable law or agreed to in writing, software
 -- distributed under the License is distributed on an "AS IS" BASIS,
 -- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 -- See the License for the specific language governing permissions and
 -- limitations under the License.

 SELECT IMPORT('common.timestamps');

 -- Converts a battery_stats counter value to human readable string.
 --
 -- @arg track STRING  The counter track name (e.g. 'battery_stats.audio').
 -- @arg value LONG    The counter value.
 -- @ret STRING        The human-readable name for the counter value.
 SELECT CREATE_FUNCTION(
   'BATTERY_STATS_COUNTER_TO_STRING(track STRING, value LONG)',
   'STRING',
   '
   SELECT
     CASE
       WHEN ($track = "battery_stats.wifi_scan" OR
             $track = "battery_stats.wifi_radio" OR
             $track = "battery_stats.mobile_radio" OR
             $track = "battery_stats.audio" OR
             $track = "battery_stats.video" OR
             $track = "battery_stats.camera" OR
             $track = "battery_stats.power_save" OR
             $track = "battery_stats.phone_in_call")
         THEN
           CASE $value
             WHEN 0 THEN "inactive"
             WHEN 1 THEN "active"
             ELSE "unknown"
           END
       WHEN $track = "battery_stats.wifi"
         THEN
           CASE $value
             WHEN 0 THEN "off"
             WHEN 1 THEN "on"
             ELSE "unknown"
           END
       WHEN $track = "battery_stats.phone_state"
         THEN
           CASE $value
             WHEN 0 THEN "in"
             WHEN 1 THEN "out"
             WHEN 2 THEN "emergency"
             WHEN 3 THEN "off"
             ELSE "unknown"
           END
       WHEN ($track = "battery_stats.phone_signal_strength" OR
             $track = "battery_stats.wifi_signal_strength")
         THEN
           CASE $value
             WHEN 0 THEN "none"
             WHEN 1 THEN "poor"
             WHEN 2 THEN "moderate"
             WHEN 3 THEN "good"
             WHEN 4 THEN "great"
             ELSE "unknown"
           END
       WHEN $track = "battery_stats.wifi_suppl"
         THEN
           CASE $value
             WHEN 0 THEN "invalid"
             WHEN 1 THEN "disconn"
             WHEN 2 THEN "disabled"
             WHEN 3 THEN "inactive"
             WHEN 4 THEN "scanning"
             WHEN 5 THEN "authenticating"
             WHEN 6 THEN "associating"
             WHEN 7 THEN "associated"
             WHEN 8 THEN "4-way-handshake"
             WHEN 9 THEN "group-handshake"
             WHEN 10 THEN "completed"
             WHEN 11 THEN "dormant"
             WHEN 12 THEN "uninit"
             ELSE "unknown"
           END
       WHEN $track = "battery_stats.data_conn"
         THEN
           CASE $value
             WHEN 0 THEN "oos"
             WHEN 1 THEN "gprs"
             WHEN 2 THEN "edge"
             WHEN 3 THEN "umts"
             WHEN 4 THEN "cdma"
             WHEN 5 THEN "evdo_0"
             WHEN 6 THEN "evdo_A"
             WHEN 7 THEN "1xrtt"
             WHEN 8 THEN "hsdpa"
             WHEN 9 THEN "hsupa"
             WHEN 10 THEN "hspa"
             WHEN 11 THEN "iden"
             WHEN 12 THEN "evdo_b"
             WHEN 13 THEN "lte"
             WHEN 14 THEN "ehrpd"
             WHEN 15 THEN "hspap"
             WHEN 16 THEN "gsm"
             WHEN 17 THEN "td_scdma"
             WHEN 18 THEN "iwlan"
             WHEN 19 THEN "lte_ca"
             WHEN 20 THEN "nr"
             WHEN 21 THEN "emngcy"
             WHEN 22 THEN "other"
             ELSE "unknown"
           END
       ELSE CAST($value AS text)
     END
   '
 );


 -- View of human readable battery stats counter-based states. These are recorded
 -- by BatteryStats as a bitmap where each 'category' has a unique value at any
 -- given time.
 --
 -- @column ts                  Timestamp in nanoseconds.
 -- @column dur                 The duration the state was active.
 -- @column name                The name of the counter track.
 -- @column value               The counter value as a number.
 -- @column value_name          The counter value as a human-readable string.
 CREATE VIEW android_battery_stats_state AS
 SELECT
   ts,
   name,
   value,
   BATTERY_STATS_VALUE_TO_STRING(name, value) AS value_name,
   LEAD(ts, 1, TRACE_END()) OVER (PARTITION BY track_id ORDER BY ts) - ts AS dur
 FROM counter
 JOIN counter_track
   ON counter.track_id = counter_track.id
 WHERE counter_track.name GLOB 'battery_stats.*';


 -- View of slices derived from battery_stats events. Battery stats records all
 -- events as instants, however some may indicate whether something started or
 -- stopped with a '+' or '-' prefix. Events such as jobs, top apps, foreground
 -- apps or long wakes include these details and allow drawing slices between
 -- instant events found in a trace.
 --
 -- For example, we may see an event like the following on 'battery_stats.top':
 --
 --     -top=10215:"com.google.android.apps.nexuslauncher"
 --
 -- This view will find the associated start ('+top') with the matching suffix
 -- (everything after the '=') to construct a slice. It computes the timestamp
 -- and duration from the events and extract the details as follows:
 --
 --     track_name='battery_stats.top'
 --     str_value='com.google.android.apps.nexuslauncher'
 --     int_value=10215
 --
 -- @column track_name          The battery stats track name.
 -- @column ts                  Timestamp in nanoseconds.
 -- @column dur                 The duration of the event.
 -- @column str_value           The string part of the event identifier.
 -- @column int_value           The integer part of the event identifier.
 CREATE VIEW android_battery_stats_event_slices AS
 WITH
   event_markers AS (
     SELECT
       ts,
       track.name AS track_name,
       str_split(slice.name, '=', 1) AS key,
       substr(slice.name, 1, 1) = '+' AS start
     FROM slice
     JOIN track
       ON slice.track_id = track.id
     WHERE
       track_name GLOB 'battery_stats.*'
       AND substr(slice.name, 1, 1) IN ('+', '-')
   ),
   with_neighbors AS (
     SELECT
       *,
       LAG(ts) OVER (PARTITION BY track_name, key ORDER BY ts) AS last_ts,
       LEAD(ts) OVER (PARTITION BY track_name, key ORDER BY ts) AS next_ts
     FROM event_markers
   ),
   -- Note: query performance depends on the ability to push down filters on
   -- the track_name. It would be more clear below to have two queries and union
   -- them, but doing so prevents push down through the above window functions.
   event_spans AS (
     SELECT
       track_name, key,
       IIF(start, ts, TRACE_START()) AS ts,
       IIF(start, next_ts, ts) AS end_ts
     FROM with_neighbors
     -- For the majority of events, we take the `start` event and compute the dur
     -- based on next_ts. In the off chance we get an end event with no prior
     -- start (matched by the second half of this where), we can create an event
     -- starting from the beginning of the trace ending at the current event.
     WHERE (start OR last_ts IS NULL)
   )
 SELECT
   ts,
   IFNULL(end_ts-ts, -1) AS dur,
   track_name,
   str_split(key, '"', 1) AS str_value,
   CAST(str_split(key, ':', 0) AS INT64) AS int_value
 FROM event_spans;
	--
	-- Copyright 2023 The Android Open Source Project
	--
	-- Licensed under the Apache License, Version 2.0 (the "License");
	-- you may not use this file except in compliance with the License.
	-- You may obtain a copy of the License at
	--
	-- https://www.apache.org/licenses/LICENSE-2.0
	--
	-- Unless required by applicable law or agreed to in writing, software
	-- distributed under the License is distributed on an "AS IS" BASIS,
	-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
	-- See the License for the specific language governing permissions and
	-- limitations under the License.

	SELECT IMPORT('common.timestamps');

	-- Converts a battery_stats counter value to human readable string.
	--
	-- @arg track STRING The counter track name (e.g. 'battery_stats.audio').
	-- @arg value LONG The counter value.
	-- @ret STRING The human-readable name for the counter value.
	SELECT CREATE_FUNCTION(
	'BATTERY_STATS_COUNTER_TO_STRING(track STRING, value LONG)',
	'STRING',
	'
	SELECT
	CASE
	WHEN ($track = "battery_stats.wifi_scan" OR
	$track = "battery_stats.wifi_radio" OR
	$track = "battery_stats.mobile_radio" OR
	$track = "battery_stats.audio" OR
	$track = "battery_stats.video" OR
	$track = "battery_stats.camera" OR
	$track = "battery_stats.power_save" OR
	$track = "battery_stats.phone_in_call")
	THEN
	CASE $value
	WHEN 0 THEN "inactive"
	WHEN 1 THEN "active"
	ELSE "unknown"
	END
	WHEN $track = "battery_stats.wifi"
	THEN
	CASE $value
	WHEN 0 THEN "off"
	WHEN 1 THEN "on"
	ELSE "unknown"
	END
	WHEN $track = "battery_stats.phone_state"
	THEN
	CASE $value
	WHEN 0 THEN "in"
	WHEN 1 THEN "out"
	WHEN 2 THEN "emergency"
	WHEN 3 THEN "off"
	ELSE "unknown"
	END
	WHEN ($track = "battery_stats.phone_signal_strength" OR
	$track = "battery_stats.wifi_signal_strength")
	THEN
	CASE $value
	WHEN 0 THEN "none"
	WHEN 1 THEN "poor"
	WHEN 2 THEN "moderate"
	WHEN 3 THEN "good"
	WHEN 4 THEN "great"
	ELSE "unknown"
	END
	WHEN $track = "battery_stats.wifi_suppl"
	THEN
	CASE $value
	WHEN 0 THEN "invalid"
	WHEN 1 THEN "disconn"
	WHEN 2 THEN "disabled"
	WHEN 3 THEN "inactive"
	WHEN 4 THEN "scanning"
	WHEN 5 THEN "authenticating"
	WHEN 6 THEN "associating"
	WHEN 7 THEN "associated"
	WHEN 8 THEN "4-way-handshake"
	WHEN 9 THEN "group-handshake"
	WHEN 10 THEN "completed"
	WHEN 11 THEN "dormant"
	WHEN 12 THEN "uninit"
	ELSE "unknown"
	END
	WHEN $track = "battery_stats.data_conn"
	THEN
	CASE $value
	WHEN 0 THEN "oos"
	WHEN 1 THEN "gprs"
	WHEN 2 THEN "edge"
	WHEN 3 THEN "umts"
	WHEN 4 THEN "cdma"
	WHEN 5 THEN "evdo_0"
	WHEN 6 THEN "evdo_A"
	WHEN 7 THEN "1xrtt"
	WHEN 8 THEN "hsdpa"
	WHEN 9 THEN "hsupa"
	WHEN 10 THEN "hspa"
	WHEN 11 THEN "iden"
	WHEN 12 THEN "evdo_b"
	WHEN 13 THEN "lte"
	WHEN 14 THEN "ehrpd"
	WHEN 15 THEN "hspap"
	WHEN 16 THEN "gsm"
	WHEN 17 THEN "td_scdma"
	WHEN 18 THEN "iwlan"
	WHEN 19 THEN "lte_ca"
	WHEN 20 THEN "nr"
	WHEN 21 THEN "emngcy"
	WHEN 22 THEN "other"
	ELSE "unknown"
	END
	ELSE CAST($value AS text)
	END
	'
	);


	-- View of human readable battery stats counter-based states. These are recorded
	-- by BatteryStats as a bitmap where each 'category' has a unique value at any
	-- given time.
	--
	-- @column ts Timestamp in nanoseconds.
	-- @column dur The duration the state was active.
	-- @column name The name of the counter track.
	-- @column value The counter value as a number.
	-- @column value_name The counter value as a human-readable string.
	CREATE VIEW android_battery_stats_state AS
	SELECT
	ts,
	name,
	value,
	BATTERY_STATS_VALUE_TO_STRING(name, value) AS value_name,
	LEAD(ts, 1, TRACE_END()) OVER (PARTITION BY track_id ORDER BY ts) - ts AS dur
	FROM counter
	JOIN counter_track
	ON counter.track_id = counter_track.id
	WHERE counter_track.name GLOB 'battery_stats.*';


	-- View of slices derived from battery_stats events. Battery stats records all
	-- events as instants, however some may indicate whether something started or
	-- stopped with a '+' or '-' prefix. Events such as jobs, top apps, foreground
	-- apps or long wakes include these details and allow drawing slices between
	-- instant events found in a trace.
	--
	-- For example, we may see an event like the following on 'battery_stats.top':
	--
	-- -top=10215:"com.google.android.apps.nexuslauncher"
	--
	-- This view will find the associated start ('+top') with the matching suffix
	-- (everything after the '=') to construct a slice. It computes the timestamp
	-- and duration from the events and extract the details as follows:
	--
	-- track_name='battery_stats.top'
	-- str_value='com.google.android.apps.nexuslauncher'
	-- int_value=10215
	--
	-- @column track_name The battery stats track name.
	-- @column ts Timestamp in nanoseconds.
	-- @column dur The duration of the event.
	-- @column str_value The string part of the event identifier.
	-- @column int_value The integer part of the event identifier.
	CREATE VIEW android_battery_stats_event_slices AS
	WITH
	event_markers AS (
	SELECT
	ts,
	track.name AS track_name,
	str_split(slice.name, '=', 1) AS key,
	substr(slice.name, 1, 1) = '+' AS start
	FROM slice
	JOIN track
	ON slice.track_id = track.id
	WHERE
	track_name GLOB 'battery_stats.*'
	AND substr(slice.name, 1, 1) IN ('+', '-')
	),
	with_neighbors AS (
	SELECT
	*,
	LAG(ts) OVER (PARTITION BY track_name, key ORDER BY ts) AS last_ts,
	LEAD(ts) OVER (PARTITION BY track_name, key ORDER BY ts) AS next_ts
	FROM event_markers
	),
	-- Note: query performance depends on the ability to push down filters on
	-- the track_name. It would be more clear below to have two queries and union
	-- them, but doing so prevents push down through the above window functions.
	event_spans AS (
	SELECT
	track_name, key,
	IIF(start, ts, TRACE_START()) AS ts,
	IIF(start, next_ts, ts) AS end_ts
	FROM with_neighbors
	-- For the majority of events, we take the `start` event and compute the dur
	-- based on next_ts. In the off chance we get an end event with no prior
	-- start (matched by the second half of this where), we can create an event
	-- starting from the beginning of the trace ending at the current event.
	WHERE (start OR last_ts IS NULL)
	)
	SELECT
	ts,
	IFNULL(end_ts-ts, -1) AS dur,
	track_name,
	str_split(key, '"', 1) AS str_value,
	CAST(str_split(key, ':', 0) AS INT64) AS int_value
	FROM event_spans;