1-- 2-- Copyright 2020 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-- 16 17-- Must be invoked after populating launches table in android_startup. 18DROP VIEW IF EXISTS functions; 19CREATE VIEW functions AS 20SELECT 21 slices.ts as ts, 22 slices.dur as dur, 23 process.name as process_name, 24 thread.name as thread_name, 25 slices.name as function_name 26FROM slices 27INNER JOIN thread_track on slices.track_id = thread_track.id 28INNER JOIN thread USING(utid) 29INNER JOIN process USING(upid); 30 31-- Animators don't occur on threads, so add them here. 32DROP VIEW IF EXISTS animators; 33CREATE VIEW animators AS 34SELECT 35 slices.ts AS ts, 36 slices.dur AS dur, 37 thread.name AS process_name, 38 slices.name AS animator_name 39FROM slices 40INNER JOIN process_track on slices.track_id = process_track.id 41INNER JOIN thread USING(upid) 42WHERE slices.name LIKE "animator%"; 43 44DROP VIEW IF EXISTS frame_times; 45CREATE VIEW frame_times AS 46SELECT 47 functions.ts AS ts, 48 functions.ts + functions.dur AS ts_end, 49 launches.package AS name, 50 launches.id AS launch_id, 51 ROW_NUMBER() OVER(PARTITION BY launches.id ORDER BY functions.ts ASC) as number 52FROM functions 53INNER JOIN launches on launches.package LIKE '%' || functions.process_name || '%' 54WHERE functions.function_name LIKE "Choreographer#doFrame%" AND functions.ts > launches.ts; 55 56DROP TABLE IF EXISTS hsc_based_startup_times; 57CREATE TABLE hsc_based_startup_times(package STRING, id INT, ts_total INT); 58 59-- Calculator 60INSERT INTO hsc_based_startup_times 61SELECT 62 launches.package as package, 63 launches.id as id, 64 frame_times.ts_end - launches.ts as ts_total 65FROM frame_times 66INNER JOIN launches on launches.package LIKE '%' || frame_times.name || '%' 67WHERE frame_times.number=2 AND frame_times.name LIKE "%roid.calcul%" AND frame_times.launch_id = launches.id; 68 69-- Calendar 70INSERT INTO hsc_based_startup_times 71SELECT 72 launches.package as package, 73 launches.id as id, 74 frame_times.ts_end - launches.ts as ts_total 75FROM frame_times 76INNER JOIN launches on launches.package LIKE '%' || frame_times.name || '%' 77WHERE frame_times.name LIKE "%id.calendar%" AND frame_times.launch_id = launches.id 78ORDER BY ABS(frame_times.ts_end - (SELECT ts + dur FROM functions WHERE function_name LIKE "DrawFrame%" AND process_name LIKE "%id.calendar" ORDER BY ts LIMIT 1)) LIMIT 1; 79 80-- Camera 81INSERT INTO hsc_based_startup_times 82SELECT 83 launches.package as package, 84 launches.id as id, 85 frame_times.ts_end - launches.ts as ts_total 86FROM frame_times 87INNER JOIN launches on launches.package LIKE '%' || frame_times.name || '%' 88WHERE frame_times.number=2 AND frame_times.name LIKE "%GoogleCamera%" AND frame_times.launch_id = launches.id; 89 90-- Chrome 91INSERT INTO hsc_based_startup_times 92SELECT 93 launches.package as package, 94 launches.id as id, 95 frame_times.ts_end - launches.ts as ts_total 96FROM frame_times 97INNER JOIN launches on launches.package LIKE '%' || frame_times.name || '%' 98WHERE frame_times.number=1 AND frame_times.name LIKE "%chrome%" AND frame_times.launch_id = launches.id; 99 100-- Clock 101INSERT INTO hsc_based_startup_times 102SELECT 103 launches.package as package, 104 launches.id as id, 105 frame_times.ts_end - launches.ts as ts_total 106FROM frame_times 107INNER JOIN launches on launches.package LIKE '%' || frame_times.name || '%' 108WHERE frame_times.ts > (SELECT ts + dur FROM animators WHERE animator_name="animator:translationZ" AND process_name LIKE "%id.deskclock" ORDER BY (ts+dur) DESC LIMIT 1) AND frame_times.name LIKE "%id.deskclock" AND frame_times.launch_id = launches.id 109ORDER BY ts_total LIMIT 1; 110 111-- Contacts 112INSERT INTO hsc_based_startup_times 113SELECT 114 launches.package as package, 115 launches.id as id, 116 frame_times.ts_end - launches.ts as ts_total 117FROM frame_times 118INNER JOIN launches on launches.package LIKE '%' || frame_times.name || '%' 119WHERE frame_times.number=3 AND frame_times.name LIKE "%id.contacts" AND frame_times.launch_id=launches.id; 120 121-- Dialer 122INSERT INTO hsc_based_startup_times 123SELECT 124 launches.package as package, 125 launches.id as id, 126 frame_times.ts_end - launches.ts as ts_total 127FROM frame_times 128INNER JOIN launches on launches.package LIKE '%' || frame_times.name || '%' 129WHERE frame_times.number=1 AND frame_times.name LIKE "%id.dialer" AND frame_times.launch_id=launches.id; 130 131-- Facebook 132INSERT INTO hsc_based_startup_times 133SELECT 134 launches.package as package, 135 launches.id as id, 136 frame_times.ts_end - launches.ts as ts_total 137FROM frame_times 138INNER JOIN launches on launches.package LIKE '%' || frame_times.name || '%' 139WHERE frame_times.ts > (SELECT ts+dur FROM slices WHERE slices.name LIKE "fb_startup_complete" ORDER BY ts LIMIT 1) AND frame_times.name LIKE "%ok.katana" AND frame_times.launch_id = launches.id 140ORDER BY ts_total LIMIT 1; 141 142-- Facebook Messenger 143INSERT INTO hsc_based_startup_times 144SELECT 145 launches.package as package, 146 launches.id as id, 147 frame_times.ts_end - launches.ts as ts_total 148FROM frame_times 149INNER JOIN launches on launches.package LIKE '%' || frame_times.name || '%' 150WHERE frame_times.ts > (SELECT ts+dur FROM slices WHERE slices.name LIKE "msgr_cold_start_to_cached_content" ORDER BY ts LIMIT 1) AND frame_times.name LIKE "%book.orca" AND frame_times.launch_id = launches.id 151ORDER BY ts_total LIMIT 1; 152 153-- Gmail 154INSERT INTO hsc_based_startup_times 155SELECT 156 launches.package as package, 157 launches.id as id, 158 frame_times.ts_end - launches.ts as ts_total 159FROM frame_times 160INNER JOIN launches on launches.package LIKE '%' || frame_times.name || '%' 161WHERE frame_times.ts > (SELECT ts + dur FROM animators WHERE animator_name="animator:elevation" AND process_name LIKE "%android.gm" ORDER BY (ts+dur) DESC LIMIT 1) AND frame_times.name LIKE "%android.gm" AND frame_times.launch_id = launches.id 162ORDER BY ts_total LIMIT 1; 163 164-- Instagram 165INSERT INTO hsc_based_startup_times 166SELECT 167 launches.package as package, 168 launches.id as id, 169 frame_times.ts_end - launches.ts as ts_total 170FROM frame_times 171INNER JOIN launches on launches.package LIKE '%' || frame_times.name || '%' 172WHERE frame_times.ts > (SELECT ts+dur FROM slices WHERE slices.name LIKE "ig_cold_start_to_cached_content" ORDER BY ts LIMIT 1) AND frame_times.name LIKE "%gram.android" AND frame_times.launch_id = launches.id 173ORDER BY ts_total LIMIT 1; 174 175-- Maps 176INSERT INTO hsc_based_startup_times 177SELECT 178 launches.package as package, 179 launches.id as id, 180 frame_times.ts_end - launches.ts as ts_total 181FROM frame_times 182INNER JOIN launches on launches.package LIKE '%' || frame_times.name || '%' 183WHERE frame_times.number=1 AND frame_times.name LIKE "%maps%" AND frame_times.launch_id = launches.id; 184 185-- Messages 186INSERT INTO hsc_based_startup_times 187SELECT 188 launches.package as package, 189 launches.id as id, 190 frame_times.ts_end - launches.ts as ts_total 191FROM frame_times 192INNER JOIN launches on launches.package LIKE '%' || frame_times.name || '%' 193WHERE frame_times.ts_end > (SELECT ts + dur FROM animators WHERE animator_name="animator:translationZ" AND process_name LIKE "%apps.messaging%" ORDER BY ts LIMIT 1) AND frame_times.name LIKE "%apps.messaging%" AND frame_times.launch_id = launches.id 194ORDER BY ts_total LIMIT 1; 195 196-- Netflix 197INSERT INTO hsc_based_startup_times 198SELECT 199 launches.package as package, 200 launches.id as id, 201 frame_times.ts_end - launches.ts as ts_total 202FROM frame_times 203INNER JOIN launches on launches.package LIKE '%' || frame_times.name || '%' 204WHERE frame_times.ts < (SELECT ts FROM animators WHERE animator_name LIKE "animator%" AND process_name LIKE "%lix.mediaclient" ORDER BY ts LIMIT 1) AND frame_times.name LIKE "%lix.mediaclient%" AND frame_times.launch_id = launches.id 205ORDER BY ts_total DESC LIMIT 1; 206 207-- Photos 208INSERT INTO hsc_based_startup_times 209SELECT 210 launches.package as package, 211 launches.id as id, 212 frame_times.ts_end - launches.ts as ts_total 213FROM frame_times 214INNER JOIN launches on launches.package LIKE '%' || frame_times.name || '%' 215WHERE frame_times.number=1 AND frame_times.name LIKE "%apps.photos%" AND frame_times.launch_id = launches.id; 216 217-- Settings was deprecated in favor of reportFullyDrawn b/169694037. 218 219-- Snapchat 220INSERT INTO hsc_based_startup_times 221SELECT 222 launches.package as package, 223 launches.id as id, 224 frame_times.ts_end - launches.ts as ts_total 225FROM frame_times 226INNER JOIN launches on launches.package LIKE '%' || frame_times.name || '%' 227WHERE frame_times.number=1 AND frame_times.name LIKE "%napchat.android" AND frame_times.launch_id = launches.id; 228 229-- Twitter 230INSERT INTO hsc_based_startup_times 231SELECT 232 launches.package as package, 233 launches.id as id, 234 frame_times.ts_end - launches.ts as ts_total 235FROM frame_times 236INNER JOIN launches on launches.package LIKE '%' || frame_times.name || '%' 237WHERE frame_times.ts_end > (SELECT ts FROM animators WHERE animator_name="animator" AND process_name LIKE "%tter.android" ORDER BY ts LIMIT 1) AND frame_times.name LIKE "%tter.android" AND frame_times.launch_id = launches.id 238ORDER BY ts_total LIMIT 1; 239 240-- WhatsApp 241INSERT INTO hsc_based_startup_times 242SELECT 243 launches.package as package, 244 launches.id as id, 245 frame_times.ts_end - launches.ts as ts_total 246FROM frame_times 247INNER JOIN launches on launches.package LIKE '%' || frame_times.name || '%' 248WHERE frame_times.ts > (SELECT ts+dur FROM slices WHERE slices.name LIKE "wa_startup_complete" ORDER BY ts LIMIT 1) AND frame_times.name LIKE "%om.whatsapp" AND frame_times.launch_id = launches.id 249ORDER BY ts_total LIMIT 1; 250 251-- Youtube 252INSERT INTO hsc_based_startup_times 253SELECT 254 launches.package as package, 255 launches.id as id, 256 frame_times.ts_end - launches.ts as ts_total 257FROM frame_times 258INNER JOIN launches on launches.package LIKE '%' || frame_times.name || '%' 259WHERE frame_times.number=2 AND frame_times.name LIKE "%id.youtube" AND frame_times.launch_id = launches.id; 260