Data Store Example Queries

Example 1

How long did my Scenarios with a given tag take to complete on various versions of Windows?

MySQL Syntax

select t.execution_id, t.os, r.name, r.status, r.end_time, r.start_time, ceiling(((r.end_time - r.start_time) * 1000)) AS duration from cycle_test_execution as t join cycle_execution_results as r on r.execution_id = t.execution_id where r.tags like ('%MY-TAG%') and r.node_type = 'Scenario'

MOCA Syntax

[select t.execution_id, t.os, r.name, r.status, r.end_time, r.start_time, datediff(ms, r.start_time, r.end_time) AS duration_ms from uc_cycle_test_execution as t join uc_cycle_execution_results as r on r.execution_id = t.execution_id where r.tags like ('%MY-TAG%') and r.node_type = 'Scenario']

Example 2

My Feature runs several python scripts via local terminal. Looking at the start time vs end time of the scenario, I see the duration of the feature. I want to know how much of that time that feature ran was spent starting my local terminal, including the step delay, and how much time was spent purely doing the work (disregarding the step delay).

MySQL Syntax

select t.execution_id, r.name, r.status, r.end_time, r.start_time, r.delay_start_time, ceiling(((r.end_time - r.start_time) * 1000)) AS working_time, ceiling(((r.end_time - r.delay_start_time) * 1000)) AS total_duration from cycle_test_execution as t join cycle_execution_results as r on r.execution_id = t.execution_id where r.node_type = 'Step' and r.name like '%I open local terminal%' and t.execution_id = '1551191374036'

MOCA Syntax

[select t.execution_id, r.name, r.status, r.end_time, r.start_time, r.delay_start_time, datediff(ms, r.start_time, r.end_time) AS working_time_ms, datediff(ms, r.delay_start_time, r.end_time) AS total_duration_ms from uc_cycle_test_execution as t join uc_cycle_execution_results as r on r.execution_id = t.execution_id where r.node_type = 'Step' and r.name like '%I open local terminal%' and t.execution_id = '1551191374036']

Example 3

Formatting data from Group Tests - grouping things by worker or group. In this example, count passing/failing steps by worker and group.

MySQL Syntax

select g.execution_id, g.node_id, g.name, g.data_file_path, w.worker_name, d.status, count(d.node_id) from cycle_execution_results as g join (select execution_id, node_id, parent_node_id, worker_name from cycle_execution_results where node_type = 'Feature') as w on w.execution_id = g.execution_id and w.parent_node_id = g.node_id join (select execution_id, node_id, sending_node_id, name, status from cycle_execution_results where node_type = 'Step') as d on d.execution_id = w.execution_id and d.sending_node_id = w.node_id where g.node_type = 'Group' group by g.execution_id, g.node_id, g.name, g.data_file_path, w.worker_name, d.status order by g.name, w.worker_name, d.status

MOCA Syntax

[select g.execution_id, g.node_id, cast(g.name as nvarchar(1000)) as name, cast(g.data_file_path as nvarchar(1000)) as data_file_path, cast(w.worker_name as nvarchar(1000)) as worker_name, d.status, count(d.node_id) from uc_cycle_execution_results as g join (select execution_id, node_id, parent_node_id, worker_name from uc_cycle_execution_results where node_type = 'Feature') as w on w.execution_id = g.execution_id and w.parent_node_id = g.node_id join (select execution_id, node_id, sending_node_id, name, status from uc_cycle_execution_results where node_type = 'Step') as d on d.execution_id = w.execution_id and d.sending_node_id = w.node_id where g.node_type = 'Group' group by g.execution_id, g.node_id, cast(g.name as nvarchar(1000)), cast(g.data_file_path as nvarchar(1000)), cast(w.worker_name as nvarchar(1000)), d.status order by cast(g.name as nvarchar(1000)), cast(w.worker_name as nvarchar(1000)), d.status]