<commit_before>--
-- Copyright 2020 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 RUN_METRIC('android/process_metadata.sql');

DROP TABLE IF EXISTS android_sysui_cuj_last_cuj;
CREATE TABLE android_sysui_cuj_last_cuj AS
  SELECT
    process.name AS name,
    process.upid AS upid,
    process_metadata.metadata AS process_metadata,
    SUBSTR(slice.name, 3, LENGTH(slice.name) - 3) AS cuj_name,
    ts AS ts_start,
    ts + dur AS ts_end,
    dur AS dur
  FROM slice
  JOIN process_track ON slice.track_id = process_track.id
  JOIN process USING (upid)
  JOIN process_metadata USING (upid)
  WHERE
    slice.name LIKE 'J<%>'
    AND slice.dur > 0
    AND (
      process.name LIKE 'com.google.android%'
      OR process.name = 'com.android.systemui')
  ORDER BY ts desc
  LIMIT 1;

SELECT RUN_METRIC(
  'android/android_hwui_threads.sql',
  'table_name_prefix', 'android_sysui_cuj',
  'process_allowlist_table', 'android_sysui_cuj_last_cuj');

DROP VIEW IF EXISTS android_sysui_cuj_thread;
CREATE VIEW android_sysui_cuj_thread AS
SELECT
  process.name as process_name,
  thread.utid,
  thread.name
FROM thread
JOIN android_sysui_cuj_last_cuj process USING (upid);

DROP VIEW IF EXISTS android_sysui_cuj_slices_in_cuj;
CREATE VIEW android_sysui_cuj_slices_in_cuj AS
SELECT
  process_name,
  thread.utid,
  thread.name as thread_name,
  slice.*,
  ts + slice.dur AS ts_end
FROM slice
JOIN thread_track ON slice.track_id = thread_track.id
JOIN android_sysui_cuj_thread thread USING (utid)
JOIN android_sysui_cuj_last_cuj last_cuj
ON ts + slice.dur >= last_cuj.ts_start AND ts <= last_cuj.ts_end
WHERE slice.dur > 0;

DROP TABLE IF EXISTS android_sysui_cuj_main_thread_slices_in_cuj;
CREATE TABLE android_sysui_cuj_main_thread_slices_in_cuj AS
SELECT slices.* FROM android_sysui_cuj_main_thread_slices slices
JOIN android_sysui_cuj_last_cuj last_cuj
ON ts + slices.dur >= last_cuj.ts_start AND ts <= last_cuj.ts_end;

DROP TABLE IF EXISTS android_sysui_cuj_do_frame_slices_in_cuj;
CREATE TABLE android_sysui_cuj_do_frame_slices_in_cuj AS
SELECT slices.* FROM android_sysui_cuj_do_frame_slices slices
JOIN android_sysui_cuj_last_cuj last_cuj
ON ts + slices.dur >= last_cuj.ts_start AND ts <= last_cuj.ts_end;

DROP TABLE IF EXISTS android_sysui_cuj_render_thread_slices_in_cuj;
CREATE TABLE android_sysui_cuj_render_thread_slices_in_cuj AS
SELECT slices.* FROM android_sysui_cuj_render_thread_slices slices
JOIN android_sysui_cuj_last_cuj last_cuj
ON ts + slices.dur >= last_cuj.ts_start AND ts <= last_cuj.ts_end;

DROP TABLE IF EXISTS android_sysui_cuj_draw_frame_slices_in_cuj;
CREATE TABLE android_sysui_cuj_draw_frame_slices_in_cuj AS
SELECT slices.* FROM android_sysui_cuj_draw_frame_slices slices
JOIN android_sysui_cuj_last_cuj last_cuj
ON ts + slices.dur >= last_cuj.ts_start AND ts <= last_cuj.ts_end;

DROP TABLE IF EXISTS android_sysui_cuj_hwc_release_slices_in_cuj;
CREATE TABLE android_sysui_cuj_hwc_release_slices_in_cuj AS
SELECT slices.* FROM android_sysui_cuj_hwc_release_slices slices
JOIN android_sysui_cuj_last_cuj last_cuj
ON ts + slices.dur >= last_cuj.ts_start AND ts <= last_cuj.ts_end;

DROP TABLE IF EXISTS android_sysui_cuj_gpu_completion_slices_in_cuj;
CREATE TABLE android_sysui_cuj_gpu_completion_slices_in_cuj AS
SELECT slices.* FROM android_sysui_cuj_gpu_completion_slices slices
JOIN android_sysui_cuj_last_cuj last_cuj
ON ts + slices.dur >= last_cuj.ts_start AND ts <= last_cuj.ts_end;

DROP TABLE IF EXISTS android_sysui_cuj_jit_slices;
CREATE TABLE android_sysui_cuj_jit_slices AS
SELECT *
FROM android_sysui_cuj_slices_in_cuj
WHERE thread_name LIKE 'Jit thread pool%'
AND name LIKE 'JIT compiling%'
AND parent_id IS NULL;

DROP TABLE IF EXISTS android_sysui_cuj_frame_timeline_events;
CREATE TABLE android_sysui_cuj_frame_timeline_events AS
  SELECT
    expected.ts as ts_expected,
    expected.dur as dur_expected,
    expected.layer_name as layer_name,
    actual.name as vsync,
    actual.ts as ts_actual,
    actual.dur as dur_actual,
    actual.jank_type LIKE '%App Deadline Missed%' as app_missed,
    actual.jank_type,
    actual.on_time_finish
  FROM expected_frame_timeline_slice expected
  JOIN android_sysui_cuj_last_cuj cuj
    ON expected.upid = cuj.upid
    AND expected.ts + expected.dur > cuj.ts_start
    AND expected.ts < cuj.ts_end
  JOIN actual_frame_timeline_slice actual
    ON expected.surface_frame_token = actual.surface_frame_token
    AND expected.upid = actual.upid
    AND expected.layer_name = actual.layer_name;

DROP TABLE IF EXISTS android_sysui_cuj_frames;
CREATE TABLE android_sysui_cuj_frames AS
  WITH gcs_to_rt_match AS (
    -- Match GPU Completion with the last RT slice before it
    SELECT
      gcs.ts as gcs_ts,
      gcs.ts_end as gcs_ts_end,
      gcs.dur as gcs_dur,
      gcs.idx as idx,
      MAX(rts.ts) as rts_ts
    FROM android_sysui_cuj_gpu_completion_slices_in_cuj gcs
    JOIN android_sysui_cuj_render_thread_slices_in_cuj rts ON rts.ts < gcs.ts
    -- dispatchFrameCallbacks might be seen in case of
    -- drawing that happens on RT only (e.g. ripple effect)
    WHERE (rts.name LIKE 'DrawFrame%' OR rts.name = 'dispatchFrameCallbacks')
    GROUP BY gcs.ts, gcs.ts_end, gcs.dur, gcs.idx
  ),
  frame_boundaries AS (
    -- Match main thread doFrame with RT DrawFrame and optional GPU Completion
    SELECT
      mts.ts as mts_ts,
      mts.ts_end as mts_ts_end,
      mts.dur as mts_dur,
      mts.vsync as vsync,
      MAX(gcs_rt.gcs_ts) as gcs_ts_start,<commit_msg>Only look at the vsync when using Actual Timeline for sysui_cuj metric<commit_after>--
-- Copyright 2020 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 RUN_METRIC('android/process_metadata.sql');

DROP TABLE IF EXISTS android_sysui_cuj_last_cuj;
CREATE TABLE android_sysui_cuj_last_cuj AS
  SELECT
    process.name AS name,
    process.upid AS upid,
    process_metadata.metadata AS process_metadata,
    SUBSTR(slice.name, 3, LENGTH(slice.name) - 3) AS cuj_name,
    ts AS ts_start,
    ts + dur AS ts_end,
    dur AS dur
  FROM slice
  JOIN process_track ON slice.track_id = process_track.id
  JOIN process USING (upid)
  JOIN process_metadata USING (upid)
  WHERE
    slice.name LIKE 'J<%>'
    AND slice.dur > 0
    AND (
      process.name LIKE 'com.google.android%'
      OR process.name = 'com.android.systemui')
  ORDER BY ts desc
  LIMIT 1;

SELECT RUN_METRIC(
  'android/android_hwui_threads.sql',
  'table_name_prefix', 'android_sysui_cuj',
  'process_allowlist_table', 'android_sysui_cuj_last_cuj');

DROP VIEW IF EXISTS android_sysui_cuj_thread;
CREATE VIEW android_sysui_cuj_thread AS
SELECT
  process.name as process_name,
  thread.utid,
  thread.name
FROM thread
JOIN android_sysui_cuj_last_cuj process USING (upid);

DROP VIEW IF EXISTS android_sysui_cuj_slices_in_cuj;
CREATE VIEW android_sysui_cuj_slices_in_cuj AS
SELECT
  process_name,
  thread.utid,
  thread.name as thread_name,
  slice.*,
  ts + slice.dur AS ts_end
FROM slice
JOIN thread_track ON slice.track_id = thread_track.id
JOIN android_sysui_cuj_thread thread USING (utid)
JOIN android_sysui_cuj_last_cuj last_cuj
ON ts + slice.dur >= last_cuj.ts_start AND ts <= last_cuj.ts_end
WHERE slice.dur > 0;

DROP TABLE IF EXISTS android_sysui_cuj_main_thread_slices_in_cuj;
CREATE TABLE android_sysui_cuj_main_thread_slices_in_cuj AS
SELECT slices.* FROM android_sysui_cuj_main_thread_slices slices
JOIN android_sysui_cuj_last_cuj last_cuj
ON ts + slices.dur >= last_cuj.ts_start AND ts <= last_cuj.ts_end;

DROP TABLE IF EXISTS android_sysui_cuj_do_frame_slices_in_cuj;
CREATE TABLE android_sysui_cuj_do_frame_slices_in_cuj AS
SELECT slices.* FROM android_sysui_cuj_do_frame_slices slices
JOIN android_sysui_cuj_last_cuj last_cuj
ON ts + slices.dur >= last_cuj.ts_start AND ts <= last_cuj.ts_end;

DROP TABLE IF EXISTS android_sysui_cuj_render_thread_slices_in_cuj;
CREATE TABLE android_sysui_cuj_render_thread_slices_in_cuj AS
SELECT slices.* FROM android_sysui_cuj_render_thread_slices slices
JOIN android_sysui_cuj_last_cuj last_cuj
ON ts + slices.dur >= last_cuj.ts_start AND ts <= last_cuj.ts_end;

DROP TABLE IF EXISTS android_sysui_cuj_draw_frame_slices_in_cuj;
CREATE TABLE android_sysui_cuj_draw_frame_slices_in_cuj AS
SELECT slices.* FROM android_sysui_cuj_draw_frame_slices slices
JOIN android_sysui_cuj_last_cuj last_cuj
ON ts + slices.dur >= last_cuj.ts_start AND ts <= last_cuj.ts_end;

DROP TABLE IF EXISTS android_sysui_cuj_hwc_release_slices_in_cuj;
CREATE TABLE android_sysui_cuj_hwc_release_slices_in_cuj AS
SELECT slices.* FROM android_sysui_cuj_hwc_release_slices slices
JOIN android_sysui_cuj_last_cuj last_cuj
ON ts + slices.dur >= last_cuj.ts_start AND ts <= last_cuj.ts_end;

DROP TABLE IF EXISTS android_sysui_cuj_gpu_completion_slices_in_cuj;
CREATE TABLE android_sysui_cuj_gpu_completion_slices_in_cuj AS
SELECT slices.* FROM android_sysui_cuj_gpu_completion_slices slices
JOIN android_sysui_cuj_last_cuj last_cuj
ON ts + slices.dur >= last_cuj.ts_start AND ts <= last_cuj.ts_end;

DROP TABLE IF EXISTS android_sysui_cuj_jit_slices;
CREATE TABLE android_sysui_cuj_jit_slices AS
SELECT *
FROM android_sysui_cuj_slices_in_cuj
WHERE thread_name LIKE 'Jit thread pool%'
AND name LIKE 'JIT compiling%'
AND parent_id IS NULL;

DROP TABLE IF EXISTS android_sysui_cuj_frame_timeline_events;
CREATE TABLE android_sysui_cuj_frame_timeline_events AS
  SELECT
    expected.ts as ts_expected,
    expected.dur as dur_expected,
    expected.layer_name as layer_name,
    actual.name as vsync,
    actual.ts as ts_actual,
    actual.dur as dur_actual,
    actual.jank_type LIKE '%App Deadline Missed%' as app_missed,
    actual.jank_type,
    actual.on_time_finish
  FROM expected_frame_timeline_slice expected
  JOIN android_sysui_cuj_last_cuj cuj
    ON expected.upid = cuj.upid
  JOIN actual_frame_timeline_slice actual
    ON expected.surface_frame_token = actual.surface_frame_token
    AND expected.upid = actual.upid
    AND expected.layer_name = actual.layer_name;

DROP TABLE IF EXISTS android_sysui_cuj_frames;
CREATE TABLE android_sysui_cuj_frames AS
  WITH gcs_to_rt_match AS (
    -- Match GPU Completion with the last RT slice before it
    SELECT
      gcs.ts as gcs_ts,
      gcs.ts_end as gcs_ts_end,
      gcs.dur as gcs_dur,
      gcs.idx as idx,
      MAX(rts.ts) as rts_ts
    FROM android_sysui_cuj_gpu_completion_slices_in_cuj gcs
    JOIN android_sysui_cuj_render_thread_slices_in_cuj rts ON rts.ts < gcs.ts
    -- dispatchFrameCallbacks might be seen in case of
    -- drawing that happens on RT only (e.g. ripple effect)
    WHERE (rts.name LIKE 'DrawFrame%' OR rts.name = 'dispatchFrameCallbacks')
    GROUP BY gcs.ts, gcs.ts_end, gcs.dur, gcs.idx
  ),
  frame_boundaries AS (
    -- Match main thread doFrame with RT DrawFrame and optional GPU Completion
    SELECT
      mts.ts as mts_ts,
      mts.ts_end as mts_ts_end,
      mts.dur as mts_dur,
      mts.vsync as vsync,
      MAX(gcs_rt.gcs_ts) as gcs_ts_start,
{'fraction_non_alphanumeric': 0.05786226446556612, 'fraction_numerical': 0.0021677505419376354, 'mean_word_length': 4.682141165324491, 'pattern_counts': {'":': 0, '<': 22, '<?xml version=': 0, '>': 24, 'https://': 2, 'lorem ipsum': 0, 'www.': 2, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '1369155', 'n_tokens_mistral': 4532, 'n_tokens_neox': 4405, 'n_words': 1149}