Skip to content

Commit 0858701

Browse files
PhilippSalvisbergPhilippSalvisberg
authored
Merge pull request #981 from utPLSQL/feature/improve_coverage_source_query
Feature/improve coverage source query
2 parents 04a2b9b + a95ca55 commit 0858701

File tree

3 files changed

+107
-91
lines changed

3 files changed

+107
-91
lines changed

‎source/core/coverage/ut_coverage.pkb‎

Lines changed: 92 additions & 72 deletions
Original file line numberDiff line numberDiff line change
@@ -35,110 +35,130 @@ create or replace package body ut_coverage is
3535
return g_develop_mode;
3636
end;
3737

38-
function get_cov_sources_sql(a_coverage_options ut_coverage_options) return varchar2 is
39-
l_result varchar2(32767);
40-
l_full_name varchar2(100);
41-
l_view_name varchar2(200) := ut_metadata.get_source_view_name();
38+
function get_cov_sources_sql(a_coverage_options ut_coverage_options, a_skip_objects ut_object_names) return varchar2 is
39+
l_result varchar2(32767);
40+
l_full_name varchar2(32767);
41+
l_join_mappings varchar2(32767);
42+
l_filters varchar2(32767);
43+
l_mappings_cardinality integer := 0;
4244
begin
43-
if a_coverage_options.file_mappings is not null and a_coverage_options.file_mappings.count > 0 then
44-
l_full_name := 'f.file_name';
45-
else
46-
l_full_name := 'lower(s.owner||''.''||s.name)';
47-
end if;
48-
l_result := '
49-
select full_name, owner, name, line, to_be_skipped, text
50-
from (
51-
select '||l_full_name||q'[ as full_name,
52-
s.owner,
53-
s.name,
54-
s.line -
55-
coalesce(
56-
case when type!='TRIGGER' then 0 end,
57-
(select min(t.line) - 1
58-
from ]'||l_view_name||q'[ t
59-
where t.owner = s.owner and t.type = s.type and t.name = s.name
60-
and regexp_like( t.text, '[A-Za-z0-9$#_]*(begin|declare|compound).*','i'))
61-
) as line,
62-
s.text, ]';
63-
l_result := l_result ||
64-
q'[case
65-
when
66-
-- to avoid execution of regexp_like on every line
67-
-- first do a rough check for existence of search pattern keyword
68-
(lower(s.text) like '%procedure%'
69-
or lower(s.text) like '%function%'
70-
or lower(s.text) like '%begin%'
71-
or lower(s.text) like '%end%'
72-
or lower(s.text) like '%package%'
73-
) and
74-
regexp_like(
75-
s.text,
76-
'^([\t ]*(((not)?\s*(overriding|final|instantiable)[\t ]*)*(static|constructor|member)?[\t ]*(procedure|function)|package([\t ]+body)|begin|end([\t ]+\S+)*[ \t]*;))', 'i'
77-
)
78-
then 'Y'
79-
end as to_be_skipped ]';
80-
81-
l_result := l_result ||' from '||l_view_name||q'[ s]';
82-
45+
l_result := q'[
46+
with
47+
trigger_source_offsets as (
48+
select min(s.line) - 1 offset, s.owner, s.name, s.type
49+
from {sources_view} s
50+
where s.type = 'TRIGGER'
51+
{filters}
52+
and (lower(s.text) like '%begin%' or lower(s.text) like '%declare%' or lower(s.text) like '%compound%')
53+
group by s.owner, s.name, s.type
54+
),
55+
sources as (
56+
select /*+ cardinality(f {mappings_cardinality}) */
57+
{l_full_name} as full_name, s.owner, s.name,
58+
s.line - case when s.type = 'TRIGGER' then o.offset else 0 end as line,
59+
s.text
60+
from {sources_view} s {join_file_mappings}
61+
left join trigger_source_offsets o
62+
on (s.owner = o.owner and s.name = o.name and s.type = o.type)
63+
where s.type in ('PACKAGE BODY', 'TYPE BODY', 'PROCEDURE', 'FUNCTION', 'TRIGGER')
64+
{filters}
65+
),
66+
coverage_sources as (
67+
select full_name, owner, name, line, text,
68+
case
69+
when
70+
-- to avoid execution of regexp_like on every line
71+
-- first do a rough check for existence of search pattern keyword
72+
(lower(s.text) like '%procedure%'
73+
or lower(s.text) like '%function%'
74+
or lower(s.text) like '%begin%'
75+
or lower(s.text) like '%end%'
76+
or lower(s.text) like '%package%'
77+
) and
78+
regexp_like(
79+
s.text,
80+
'^([\t ]*(((not)?\s*(overriding|final|instantiable)[\t ]*)*(static|constructor|member)?[\t ]*(procedure|function)|package([\t ]+body)|begin|end([\t ]+\S+)*[ \t]*;))', 'i'
81+
)
82+
then 'Y'
83+
end as to_be_skipped
84+
from sources s
85+
)
86+
select full_name, owner, name, line, to_be_skipped, text
87+
from coverage_sources s
88+
-- Exclude calls to utPLSQL framework, Unit Test packages and objects from a_exclude_list parameter of coverage reporter
89+
where (s.owner, s.name) not in ( select /*+ cardinality(el {skipped_objects_cardinality})*/el.owner, el.name from table(:l_skipped_objects) el )
90+
and line > 0
91+
]';
92+
8393
if a_coverage_options.file_mappings is not empty then
84-
l_result := l_result || '
94+
l_mappings_cardinality := ut_utils.scale_cardinality(cardinality(a_coverage_options.file_mappings));
95+
l_full_name := 'f.file_name';
96+
l_join_mappings := '
8597
join table(:file_mappings) f
8698
on s.name = f.object_name
8799
and s.type = f.object_type
88-
and s.owner = f.object_owner
89-
where 1 = 1';
90-
elsif a_coverage_options.include_objects is not empty then
91-
l_result := l_result || '
92-
where (s.owner, s.name) in (select il.owner, il.name from table(:include_objects) il)';
100+
and s.owner = f.object_owner';
93101
else
94-
l_result := l_result || '
95-
where s.owner in (select upper(t.column_value) from table(:l_schema_names) t)';
102+
l_full_name := q'[lower(s.owner||'.'||s.name)]';
103+
l_filters := case
104+
when a_coverage_options.include_objects is not empty then '
105+
and (s.owner, s.name) in (
106+
select /*+ cardinality(il '||ut_utils.scale_cardinality(cardinality(a_coverage_options.include_objects))||') */
107+
il.owner, il.name
108+
from table(:include_objects) il
109+
)'
110+
else '
111+
and s.owner in (
112+
select /*+ cardinality(t '||ut_utils.scale_cardinality(cardinality(a_coverage_options.schema_names))||') */
113+
upper(t.column_value)
114+
from table(:l_schema_names) t)'
115+
end;
96116
end if;
97-
l_result := l_result || q'[
98-
and s.type not in ('PACKAGE', 'TYPE', 'JAVA SOURCE')
99-
--Exclude calls to utPLSQL framework, Unit Test packages and objects from a_exclude_list parameter of coverage reporter
100-
and (s.owner, s.name) not in (select el.owner, el.name from table(:l_skipped_objects) el)
101-
)
102-
where line > 0]';
117+
118+
l_result := replace(l_result, '{sources_view}', ut_metadata.get_source_view_name());
119+
l_result := replace(l_result, '{l_full_name}', l_full_name);
120+
l_result := replace(l_result, '{join_file_mappings}', l_join_mappings);
121+
l_result := replace(l_result, '{filters}', l_filters);
122+
l_result := replace(l_result, '{mappings_cardinality}', l_mappings_cardinality);
123+
l_result := replace(l_result, '{skipped_objects_cardinality}', ut_utils.scale_cardinality(cardinality(a_skip_objects)));
124+
103125
return l_result;
126+
104127
end;
105128

106-
function get_cov_sources_cursor(a_coverage_options in ut_coverage_options,a_sql in varchar2) return sys_refcursor is
129+
function get_cov_sources_cursor(a_coverage_options in ut_coverage_options) return sys_refcursor is
107130
l_cursor sys_refcursor;
108131
l_skip_objects ut_object_names;
109132
l_sql varchar2(32767);
110-
l_valid_pattern varchar2(250) := '^\s*select.+$';
111133
begin
112134
if not is_develop_mode() then
113135
--skip all the utplsql framework objects and all the unit test packages that could potentially be reported by coverage.
114136
l_skip_objects := ut_utils.get_utplsql_objects_list() multiset union all coalesce(a_coverage_options.exclude_objects, ut_object_names());
115137
end if;
116-
if regexp_like(a_sql, l_valid_pattern, 'mi') then
117-
-- pseudo assert for PL/SQL Cop
118-
l_sql := sys.dbms_assert.noop(a_sql);
119-
else
120-
raise_application_error(-20542, 'Possible SQL injection detected. a_sql parameter does not match valid pattern "' || l_valid_pattern || '".');
121-
end if;
138+
139+
l_sql := get_cov_sources_sql(a_coverage_options, l_skip_objects);
140+
141+
ut_event_manager.trigger_event(ut_event_manager.gc_debug, ut_key_anyvalues().put('l_sql',l_sql) );
142+
122143
if a_coverage_options.file_mappings is not empty then
123144
open l_cursor for l_sql using a_coverage_options.file_mappings, l_skip_objects;
124145
elsif a_coverage_options.include_objects is not empty then
125-
open l_cursor for l_sql using a_coverage_options.include_objects, l_skip_objects;
146+
open l_cursor for l_sql using a_coverage_options.include_objects, a_coverage_options.include_objects, l_skip_objects;
126147
else
127-
open l_cursor for l_sql using a_coverage_options.schema_names, l_skip_objects;
148+
open l_cursor for l_sql using a_coverage_options.schema_names, a_coverage_options.schema_names, l_skip_objects;
128149
end if;
129150
return l_cursor;
130151
end;
131152

132-
procedure populate_tmp_table(a_coverage_options ut_coverage_options, a_sql in varchar2) is
153+
procedure populate_tmp_table(a_coverage_options ut_coverage_options) is
133154
pragma autonomous_transaction;
134155
l_cov_sources_crsr sys_refcursor;
135156
l_cov_sources_data ut_coverage_helper.t_coverage_sources_tmp_rows;
136157
begin
137158

138159
if not ut_coverage_helper.is_tmp_table_populated() or is_develop_mode() then
139160
ut_coverage_helper.cleanup_tmp_table();
140-
ut_event_manager.trigger_event(ut_event_manager.gc_debug, ut_key_anyvalues().put('a_sql',a_sql) );
141-
l_cov_sources_crsr := get_cov_sources_cursor(a_coverage_options, a_sql);
161+
l_cov_sources_crsr := get_cov_sources_cursor(a_coverage_options);
142162

143163
loop
144164
fetch l_cov_sources_crsr bulk collect into l_cov_sources_data limit 10000;
@@ -212,7 +232,7 @@ create or replace package body ut_coverage is
212232
begin
213233
--prepare global temp table with sources
214234
ut_event_manager.trigger_event('about to populate coverage temp table');
215-
populate_tmp_table(a_coverage_options, get_cov_sources_sql(a_coverage_options));
235+
populate_tmp_table(a_coverage_options);
216236
ut_event_manager.trigger_event('coverage temp table populated');
217237

218238
-- Get raw data for both reporters, order is important as tmp table will skip headers and dont populate

‎source/core/coverage/ut_coverage.pks‎

Lines changed: 1 addition & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -18,7 +18,6 @@ create or replace package ut_coverage authid current_user is
1818

1919
gc_proftab_coverage constant varchar2(32) := 'proftab';
2020
gc_block_coverage constant varchar2(32) := 'block';
21-
gc_extended_coverage constant varchar2(32) := 'extended';
2221

2322
type tt_coverage_id_arr is table of integer index by varchar2(30);
2423

@@ -75,7 +74,7 @@ create or replace package ut_coverage authid current_user is
7574
* Allows overwriting of private global variable g_coverage_id
7675
* Used internally, only for unit testing of the framework only
7776
*/
78-
procedure mock_coverage_id(a_coverage_id integer,a_coverage_type in varchar2);
77+
procedure mock_coverage_id(a_coverage_id integer, a_coverage_type in varchar2);
7978

8079
procedure mock_coverage_id(a_coverage_id tt_coverage_id_arr);
8180

‎source/core/coverage/ut_coverage_helper_profiler.pkb‎

Lines changed: 14 additions & 17 deletions
Original file line numberDiff line numberDiff line change
@@ -54,26 +54,23 @@ create or replace package body ut_coverage_helper_profiler is
5454
end;
5555

5656
function proftab_results(a_object_owner varchar2, a_object_name varchar2, a_coverage_id integer) return t_proftab_rows is
57-
l_raw_coverage sys_refcursor;
58-
l_coverage_rows t_proftab_rows;
57+
l_coverage_rows t_proftab_rows;
5958
begin
60-
open l_raw_coverage for q'[select d.line#,
59+
select
60+
d.line#,
6161
case when sum(d.total_occur) = 0 and sum(d.total_time) > 0 then 1 else sum(d.total_occur) end total_occur
62-
from plsql_profiler_units u
63-
join plsql_profiler_data d
64-
on u.runid = d.runid
65-
and u.unit_number = d.unit_number
66-
where u.runid = :a_coverage_id
67-
and u.unit_owner = :a_object_owner
68-
and u.unit_name = :a_object_name
69-
and u.unit_type not in ('PACKAGE SPEC', 'TYPE SPEC', 'ANONYMOUS BLOCK')
70-
group by d.line#]' using a_coverage_id,a_object_owner,a_object_name;
62+
bulk collect into l_coverage_rows
63+
from plsql_profiler_units u
64+
join plsql_profiler_data d
65+
on u.runid = d.runid
66+
and u.unit_number = d.unit_number
67+
where u.runid = a_coverage_id
68+
and u.unit_owner = a_object_owner
69+
and u.unit_name = a_object_name
70+
and u.unit_type in ('PACKAGE BODY', 'TYPE BODY', 'PROCEDURE', 'FUNCTION', 'TRIGGER')
71+
group by d.line#;
7172

72-
FETCH l_raw_coverage BULK COLLECT
73-
INTO l_coverage_rows;
74-
CLOSE l_raw_coverage;
75-
76-
RETURN l_coverage_rows;
73+
return l_coverage_rows;
7774
end;
7875

7976
function get_raw_coverage_data(a_object_owner varchar2, a_object_name varchar2, a_coverage_id integer) return ut_coverage_helper.t_unit_line_calls is

0 commit comments

Comments
 (0)