trace/database/TempSql.ts

/*
 * Copyright (C) 2022 Huawei Device Co., Ltd.
 * 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
 *
 *     http://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.
 */

let temp_query_process = `create table temp_query_process as
select distinct process.pid  as pid,
                process.name as processName
from (select ipid, itid
      from sched_slice
      group by itid) the_tracks
         left join
     (select sched_slice.ipid, sum(dur) as total_dur from sched_slice group by ipid) using (ipid)
         left join
     process using (ipid)
where pid is not null
order by total_dur desc,
         the_tracks.ipid,
         processName,
         the_tracks.itid;
`;
let temp_query_cpu_data = `create table temp_query_cpu_data as
with list as (SELECT IP.name            as processName,
                     IP.name               processCmdLine,
                     IP.pid             as processId,
                     B.cpu,
                     A.name,
                     C.id               as schedId,
                     A.tid,
                     A.id,
                     A.type,
                     B.dur,
                     B.ts - TR.start_ts AS startTime,
                     C.priority,
                     C.end_state
              from thread_state AS B
                       left join thread as A on B.itid = A.id
                       left join sched_slice AS C on B.itid = C.itid and B.ts = C.ts
                       left join trace_range AS TR
                       left join process AS IP on A.ipid = IP.id
              where C.itid is not null
              order by B.id)
select *
from list;
create index temp_query_cpu_data_idx on temp_query_cpu_data (cpu, startTime);
`;

let temp_query_freq_data = `create table temp_query_freq_data as
select cpu, value, ts - tb.start_ts as startNS
from measure c,
     trace_range tb
         inner join cpu_measure_filter t on c.filter_id = t.id
where (name = 'cpufreq' or name = 'cpu_frequency')
order by ts;
create index temp_query_freq_data_idx on temp_query_freq_data (cpu);
`;

let temp_query_process_data = `create table temp_query_process_data as
select ta.id,
       type,
       ts,
       dur,
       ta.cpu,
       itid             as utid,
       state,
       ts - tb.start_ts as startTime,
       tc.tid,
       tc.pid,
       tc.process,
       tc.thread
from thread_state ta,
     trace_range tb
         left join (select it.id,
                           tid,
                           pid,
                           ip.name as process,
                           it.name as thread
                    from thread as it
                             left join process ip on it.ipid = ip.id) tc on ta.itid = tc.id
where ta.cpu is not null
order by startTime;
create index temp_query_process_data_idx on temp_query_process_data (pid, startTime);
`;
let temp_query_thread_function = `create table temp_query_thread_function as
select tid,
       A.name            as threadName,
       is_main_thread,
       c.callid          as track_id,
       c.ts - D.start_ts as startTs,
       c.dur,
       c.name            as funName,
       c.parent_id,
       c.id,
       c.depth,
       c.argsetid
from thread A,
     trace_range D
         left join callstack C on A.id = C.callid
where startTs not null and c.cookie is null;
create index temp_query_thread_function_idx on temp_query_thread_function (tid);
`;

let temp_query_thread_data = `create table temp_query_thread_data as
select A.id
     , A.type
     , A.tid
     , A.name
     , A.start_ts
     , A.end_ts
     , A.ipid             as upid
     , A.is_main_thread
     , B.cpu
     , B.ts - TR.start_ts AS startTime
     , B.dur
     , B.state
     , IP.pid
     , IP.name            as processName
from thread_state AS B
         left join thread as A on A.id = B.itid
         left join trace_range AS TR
         left join process AS IP on IP.id = A.ipid;
create index temp_query_thread_data_idx on temp_query_thread_data (tid);`;

let temp_view = `CREATE VIEW IF NOT EXISTS thread_view AS SELECT id as itid, * FROM thread;
CREATE VIEW IF NOT EXISTS process_view AS SELECT id as ipid, * FROM process;
CREATE VIEW IF NOT EXISTS sched_view AS SELECT *, ts + dur as ts_end FROM sched_slice;
CREATE VIEW IF NOT EXISTS instants_view AS SELECT *, 0.0 as value FROM instant;
CREATE VIEW IF NOT EXISTS trace_section AS select start_ts, end_ts from trace_range;`;

let temp_query_cpu_freq = `create table temp_query_cpu_freq as
select cpu
from cpu_measure_filter
where (name = 'cpufreq' or name = 'cpu_frequency')
order by cpu;`;
let temp_query_cpu_max_freq = `create table temp_query_cpu_max_freq as
select max(value) as maxFreq
from measure c
         inner join cpu_measure_filter t on c.filter_id = t.id
where (name = 'cpufreq' or name = 'cpu_frequency');`;

let temp_get_tab_states_group_by_process = `create table temp_get_tab_states_group_by_process as
select IP.name                 as process,
       IP.pid                  as processId,
       dur,
       Ip.id                   as id,
       (ts - B.start_ts + dur) as end_ts,
       (ts - B.start_ts)       as start_ts
from thread_state as A,
     trace_range as B
         left join thread as C on A.itid = C.id
         left join process AS IP on C.ipid = IP.id
where A.dur > 0
  and processId not null and (ts - B.start_ts)>0;
create index temp_get_tab_states_group_by_process_idx on temp_get_tab_states_group_by_process (end_ts, start_ts);
`;

let temp_get_process_thread_state_data = ` create table temp_get_process_thread_state_data as
select IP.name                      as process,
       IP.pid                       as processId,
       A.name                       as thread,
       B.state                      as state,
       A.tid                        as threadId,
       B.dur,
       (B.ts - TR.start_ts + B.dur) as end_ts,
       (B.ts - TR.start_ts)         as start_ts,
       B.cpu,
       C.priority,
       '-'                          as note
from thread_state as B
         left join thread as A on B.itid = A.id
         left join process as IP on A.ipid = IP.id
         left join trace_range as TR
         left join sched_slice as C on B.itid = C.itid and C.ts = B.ts
where B.dur > 0
  and IP.pid not null and (B.ts - TR.start_ts) >= 0;
create index temp_get_process_thread_state_data_idx on temp_get_process_thread_state_data (end_ts, start_ts);
`;

let temp_get_tab_states_group_by_state_pid_tid = ` create table temp_get_tab_states_group_by_state_pid_tid as
select IP.name                      as process,
       IP.pid                       as processId,
       A.name                       as thread,
       B.state                      as state,
       A.tid                        as threadId,
       B.dur                        as dur,
       A.tid                        as tid,
       (B.ts - TR.start_ts + B.dur) as end_ts,
       (B.ts - TR.start_ts)         as start_ts
from thread_state AS B
         left join thread as A on B.itid = A.id
         left join process AS IP on A.ipid = IP.id
         left join trace_range AS TR
where B.dur > 0
  and IP.pid not null and (B.ts - TR.start_ts > 0);
create index temp_get_tab_states_group_by_state_pid_tid_idx0 on temp_get_tab_states_group_by_state_pid_tid (process, processId, thread, threadId, state);
create index temp_get_tab_states_group_by_state_pid_tid_idx1 on temp_get_tab_states_group_by_state_pid_tid (end_ts, start_ts);
create index temp_get_tab_states_group_by_state_pid_tid_idx3 on temp_get_tab_states_group_by_state_pid_tid (end_ts, start_ts, process, processId, thread, threadId, state);
`;
let temp_get_tab_states_group_by_state_pid = `create table temp_get_tab_states_group_by_state_pid as
select IP.name                  as process,
       IP.pid                   as processId,
       B.state                  as state,
       B.dur                    as dur,
       A.tid                    as tid,
       (ts - TR.start_ts + dur) as end_ts,
       (ts - TR.start_ts)       as start_ts
from thread_state AS B
         left join thread as A on B.itid = A.id
         left join process AS IP on A.ipid = IP.id
         left join trace_range AS TR
where pid not null and
    B.dur > 0 and (ts - TR.start_ts > 0);
create index temp_get_tab_states_group_by_state_pid_idx0 on temp_get_tab_states_group_by_state_pid (process, processId, state);
create index temp_get_tab_states_group_by_state_pid_idx1 on temp_get_tab_states_group_by_state_pid (start_ts, end_ts);
`;
let temp_get_tab_states_group_by_state = `create table temp_get_tab_states_group_by_state as
select state,
       dur,
       (ts - B.start_ts + dur) as end_ts,
       (ts - B.start_ts)       as start_ts
from thread_state as A,
     trace_range as B
         left join thread as C on A.itid = C.id
         left join process AS IP on C.ipid = IP.id
where A.dur > 0
  and IP.pid not null and (ts - B.start_ts > 0);
create index temp_get_tab_states_group_by_state_idx0 on temp_get_tab_states_group_by_state (state);
create index temp_get_tab_states_group_by_state_idx1 on temp_get_tab_states_group_by_state (start_ts, end_ts);
`;
let temp_get_tab_states_group_by_process_thread = `create table temp_get_tab_states_group_by_process_thread as
select IP.name                  as process,
       IP.pid                   as processId,
       A.name                   as thread,
       a.tid                    as threadId,
       B.dur                    as dur,
       A.tid                    as tid,
       (ts - TR.start_ts + dur) as end_ts,
       (ts - TR.start_ts)       as start_ts
from thread_state AS B
         left join
     thread as A on B.itid = A.id
         left join
     process AS IP on A.ipid = IP.id
         left join
     trace_range AS TR
where pid not null
    and
      B.dur > 0
    and
      (ts - TR.start_ts)>0;
create index temp_get_tab_states_group_by_process_thread_idx0 on temp_get_tab_states_group_by_process_thread (process, processId, thread, threadId);
create index temp_get_tab_states_group_by_process_thread_idx1 on temp_get_tab_states_group_by_process_thread (start_ts, end_ts);
`;

let temp_get_cpu_rate = `create table temp_get_cpu_rate as
with cpu as (select cpu,
                    ts,
                    dur,
                    (case when ro < 99 then ro else 99 end)                               as ro,
                    (case when ro < 99 then stime + ro * cell else stime + 99 * cell end) as st,
                    (case when ro < 99 then stime + (ro + 1) * cell else etime end)       as et
             from (select cpu,
                          ts,
                          A.dur,
                          ((ts + A.dur) - D.start_ts) / ((D.end_ts - D.start_ts) / 100) as ro,
                          D.start_ts                                                    as stime,
                          D.end_ts                                                         etime,
                          (D.end_ts - D.start_ts) / 100                                 as cell
                   from sched_slice A
                            left join
                        trace_range D
                            left join
                        thread B on A.itid = B.id
                            left join
                        process C on B.ipid = C.id
                   where tid != 0
        and (A.ts)
          between D.start_ts and D.end_ts))
select cpu,
       ro,
       sum(case
               when ts <= st and ts + dur <= et then (ts + dur - st)
               when ts <= st and ts + dur > et then et - st
               when ts > st and ts + dur <= et then dur
               when ts > st and ts + dur > et then et - ts end) / cast(et - st as float) as rate
from cpu
group by cpu, ro;
`;

let temp_get_tab_thread_states = `create table temp_get_tab_thread_states as
select IP.name                                 as process,
       IP.pid                                  as pid,
       A.name                                  as thread,
       A.tid                                   as tid,
       B.state                                 as state,
       B.dur                                   as dur,
       (B.ts - TR.start_ts + ifnull(B.dur, 0)) as end_ts,
       (B.ts - TR.start_ts)                    as start_ts
from thread_state AS B
         left join
     thread as A
     on
         A.id = B.itid
         left join
     trace_range AS TR
         left join
     process AS IP
     on
         IP.id = A.ipid
where (B.ts - TR.start_ts > 0);
create index temp_get_tab_thread_states_idx0 on temp_get_tab_thread_states (process, pid, thread, tid, state);
create index temp_get_tab_thread_states_idx1 on temp_get_tab_thread_states (start_ts, end_ts);
`;

let temp_get_tab_slices = `create table temp_get_tab_slices as
select c.name                      as name,
       c.dur                       as dur,
       A.tid                       as tid,
       (C.ts - D.start_ts + C.dur) as end_ts,
       (C.ts - D.start_ts)         as start_ts
from thread A,
     trace_range D
         left join
     callstack C on A.id = C.callid
where C.ts not null
      and c.dur >= 0
      and (C.ts - D.start_ts > 0);
create index temp_get_tab_slices_idx0 on temp_get_tab_slices (name);
`;

let createProcessNoId = `
    insert into process(id, ipid, type, pid, name, start_ts)
    SELECT null, null, 'process' as type, tid as pid, t.name, t.start_ts
    from thread t
    where ipid is null
      and tid != 0;
    update process
    set id   = ROWID - 1,
        ipid = ROWID - 1
    where id is null;
    update thread
    set ipid = (select id from process where thread.tid = process.pid)
    where thread.ipid is null;
`;
let temp_create_cpu_freq_view = `CREATE VIEW cpu_freq_view AS SELECT B.cpu, A.ts, LEAD(A.ts, 1, (SELECT end_ts FROM trace_range)) OVER (PARTITION BY A.filter_id ORDER BY ts) AS end_ts,LEAD(A.ts, 1, (SELECT end_ts FROM trace_range)) OVER (PARTITION BY A.filter_id ORDER BY ts) - ts AS dur,value AS freq FROM measure AS A, cpu_measure_filter AS B WHERE B.name = 'cpu_frequency' AND A.filter_id = B.id`;
let temp_create_virtual_table = `CREATE VIRTUAL table result USING SPAN_JOIN(cpu_freq_view partitioned cpu, sched_slice partitioned cpu)`;

let queryThreadWakeUpFrom = `
    select TB.tid, TB.name as thread, TA.cpu, (TA.ts - TR.start_ts) as ts, TC.pid, TC.name as process
    from (select ts as wakeTs, wakeup_from as wakeupFromTid
          from instant,
               trace_range
          where name = 'sched_wakeup'
            and ref = $itid
            and ts > start_ts + $startTime
            and ts < start_ts + $startTime + $dur
          order by ts) TW
             left join thread_state TA
                       on TW.wakeupFromTid = TA.itid and TA.ts < TW.wakeTs and TA.ts + TA.dur >= TW.wakeTs
             left join thread TB on TA.itid = TB.id
             left join process TC on TB.ipid = TC.id
             left join trace_range TR
    where TB.ipid not null
    limit 1;
`;

let queryThreadWakeUp = `
    select TB.tid, TB.name as thread, min(TA.ts - TR.start_ts) as ts, TC.pid, TC.name as process
    from (select min(ts) as wakeTs, ref as itid
          from instant,
               trace_range
          where name = 'sched_wakeup'
            and wakeup_from = $itid
            and ts > start_ts + $startTime
            and ts < start_ts + $startTime + $dur
          group by ref) TW
             left join thread_state TA on TW.itid = TA.itid and TA.ts > TW.wakeTs
             left join thread TB on TA.itid = TB.id
             left join process TC on TB.ipid = TC.id
             left join trace_range TR
    where TB.ipid not null
    group by TB.tid, TB.name, TC.pid, TC.name;
`;

let delete_callstack_binder_data = `DELETE
                                    FROM callstack
                                    WHERE dur < -1
                                       or name = 'binder transaction async'
                                       or name = 'binder async rcv';`;

let temp_init_sql_list = [temp_query_process];