Use coverage without ut.run()

Describe the bug
I'm super happy with coverage provided by utPLSQL however my company uses testing framework written in java with additional code run with sqlplus for integration tests. Is there any way to manually start, stop and generate sonar report without wrapping every piece of code with utplsql?

It would be great as my PL/SQL code is fragmented and called from different places but I would love to be able to get the coverage. I found that this project uses dbms_profiler so it shouldn't be a problem. Is there any instruction how to do this?

Provide version info
utPLSQL 3.1.9
Oracle 12.2

Information about client software
Other, using jdbc

@ksawerykarwacki

Hi @ksawerykarwacki

Currently, utPLSQL utilizes coverage internally.

We start the ut.run,
start profiler (and coverage on db >=12.2)
keep run_id for those
finish test run
stop profiler (and coverage on db >=12.2)
gather information from both profiler and coverage
compose data
produce reports (one or more)

All of the above is done in single-session mode.

To enable coverage gathering from outside, we would need do to some rework of internal code so that you could have one coverage for multiple sessions.

Most probably, when running your tests from outside of DB, you might be utilizing more than one session.
You might have:

connection pool
multiple connects/disconnects

So in essence you we would need to expose API that would allow an external program to:

connect
start coverage - passing some unique id (say ABCDE or some UUID)
do stuff
stop coverage
(optionally disconnect)
(optionally connect)
start coverage - passing the same common unique id ABCDE
do stuff
stop coverage ...
produce coverage report(s) for unique id ABCDE

We've already had similar request in the past.
I think it's doable.

We would need:

a new public API for start/stop of coverage
a new table to hold association of the external unique id ABCD with list of coverage & profiler run_id's
change the way we reference coverage internally - use the unique id
change the way we compose data - consider multiple coverage runs

I guess it could be few days - few weeks of work to get it done.

So there is a way to do this on single session? So I could wrap my main jdbc connection (it uses one session) and cover at least this. That is still huge for me as I just need coverage without wrapping everything into ut.run (as I would have to wrap every call into fake test procedure because it is run from java) which is also not perfect as I will end up with as many reports as calls.

Yes. In single-session mode, there is a way.
This way is however undocumented and you need to be aware that it might no longer work in next releases as you're hooking into internal parts of uPLSQL instead of using public API.

The below was tested to work on v3.1.9,

Add extra grants and synonyms to internal utPLSQL elements.

grant execute on ut_coverage to ...;
grant execute on ut_run to ...;

create or replace synonym ut_coverage for ut3.ut_coverage;
create or replace synonym ut_run for ut3.ut_run;
create or replace synonym ut_output_table_buffer for ut3.ut_output_table_buffer;

connect to DB.
call:

begin
  ut_coverage.coverage_start();
  ut_coverage.coverage_resume();
end;
/

execute your tested code:

begin
  my_code;
end;
/

call:

declare
  /* PROVIDE UNIQUE ID for this run - the ID needs to be used later*/
  v_reporter_id raw(32) := 'd8a79e85915640a6a4e1698fdf90ba75'; --sys_guid(); 
  /* PROVIDE TYPE OF REPORTER*/
  v_reporter      ut_coverage_html_reporter := ut_coverage_html_reporter(); 
begin
  v_reporter.set_reporter_id( v_reporter_id );
  v_reporter.after_calling_run(
    ut_run( 
      a_items => null--, /* leave it as NULL - irrelevant*/
      /*optional - LIST OF SCHEMAS TO GET COVERAGE FOR - if not provided - current-schema is used */
      --a_schema_names => ut_varchar2_rows(user) --,
      /* optional - you can provide list of objects that should be included in coverage reports*/
--      a_include_objects => null,
      /* optional -  you can provide list of objects that should be excluded from coverage reports*/
--      a_exclude_objects => null,
      /* optional - you can provide DB-object - file map here using ut_file_mappings 
          that way your coverage will be limited to specified objects
      */
--      a_project_file_mappings => null 
    ) 
  );
  v_reporter.on_finalize(null);
end;
/

Run the below query to get report in reporter format specified above.

/* PROVIDE UNIQUE ID for the run - same as in above block*/
select *
  from table(  
    ut_output_table_buffer('d8a79e85915640a6a4e1698fdf90ba75').get_lines(1,1)
  );

The query works only once per run, as the data is consumed from buffer by query.
So select into something (save data into file).

Having all that said

I would rather have a public API for this in utPLSQL then have users hook into internal stuff.

Public API could be easier to use too.

@jgebal

I agree that having official API is a way to go so this is still a enhancement request rather than question.

@jgebal Nevertheless thank you for giving me something to start with for now.

I think it's a good feature to add to API.
I mean adding ability to run utPLSQL coverage without invoking ut.run();

The API could be something like this:

use UUID on the APP side to generate a unique identifier for your application tests.
wrap each call to database code with coverage registration like:

exec ut.coverage_start(:UUID);
exec your_tested_stuff;
exec ut.coverage_stop(:UUID);

Each test run can be executed in separate session.
Tests can be executed in parallel.

get coverage back after all sessions/runs were complete

select *
  from table(
    ut_coverage_html_reporter()
    .get_report(
      :UUID,
      a_coverage_options => 
        ut_coverage_options(
          a_schema_names,
          a_exclude_objects,
          a_include_objects,
          a_project_file_mappings
        )
    )
  );

The a_coverage_options could be optional.

That would be pretty nice usable API I think.
With that API, we would open-up utPLSQL for coverage-usage outside of utPLSQL unit-test runs.

That change would also allow for multi-session runs therefore enabling utPLSQL itself for parallel test execution!

There are two options though to consider:

the coverage data gets fully consumed after reporting (data is cleaned-up)
the coverage data remains in DB after reporting

I would prefer the first option as it also takes care of cleaning-up profiler and coverage tables. This is something utPLSQL is not handling at the moment.

I must say - this feature work looks pretty exciting :)

I agree on that approach as there is almost no reason why we should keep the data after report is generated. One possible scenario is when you want to generate multiple types of report from single run.

@pesse

@pesse @PhilippSalvisberg @lwasylow
What do you think of the suggested change from usability perspective?

I see it's definitely doable from implementation side and probably not a huge change.
We would maintain backward-compatibility of API, so that whatever works today, would still work as it was.

I like the approach.

The CLI should be extended to produce reports (e.g. using ut_coverage_sonar_reporter or ut_coverage_html_reporter or any other reporter) based on a given report ID without running utPLSQL tests.

The start/stop mechanism is flexible enough. It can be used explicitly during test runs or included in a logon/logoff trigger to further simplify the usage. That way everything executed under a defined user is covered and the profiling data is cleared after the completion of the report.

I like the feature, too. Will have to think about what that means for CLI and java-api. It sounds like it'll lead to more modularization, which is a great thing.

Is there any ETA for that as I am moving closer and closer to the point when I would love to introduce code coverage. I could of course use junky hack discussed above but if there is any chance for pre-release, bleeding edge alpha implementation then I'm all in ;)

@ksawerykarwacki

HI @ksawerykarwacki
Long weekend is coming and with lockdown, there is a chance I might make good progress on it :)

@ksawerykarwacki

@ksawerykarwacki - will need another weekend or two to get into this.

This long Easter weekend I have decided to actually get some rest.

Is there some progress by any chance?

@ksawerykarwacki

Sorry @ksawerykarwacki
I had some really serious personal matters to attend.
I've started looking into it just now.

No problem. I hope everything is fine for you now.

I finally tried the old janky workaround and it didn't work for me as reporting returns 0 lines covered even thou I run a procedure form my schema. But I tested that on latest version so it might no longer work.

Anyway I still can't wait to lay my hands on full api :)

@ksawerykarwacki

@ksawerykarwacki - did you get a chance to try the new coverage approach in #1079?

I'm on vacation for past couple of weeks and I try to avoid coding :) I'll be back to my regular office routine in a week after that I'm going to prepare some tests and run it.

To give you some way how I will test it:
I will be using Java to call it as all my calls are run over NamedParameterJdbcTemplate using update method(https://docs.spring.io/spring-framework/docs/current/javadoc-api/org/springframework/jdbc/core/namedparam/NamedParameterJdbcTemplate.html#update-java.lang.String-java.util.Map-), inside I use call statement.

I tried to run the workaround approach from #1025 (comment) using java as described above but generated report was always empty. Due to lack of time to investigate this I just dropped it and was waiting for full implementation.

I'll get back to you as soon as I will get some results after my holiday break.

I've run some initial test and seems to work like a charm.

I had few doubts about configuration of reporter as documentation lists all parameters except coverage_run_id as optional so my assumption was that something like this:

select * from table( ut_coverage_html_reporter().get_report( a_coverage_options => ut_coverage_options( coverage_run_id => 'A6AA5B7361251CE6E053020011ACA055' ) ) );

should generate me report for all schemas where ut_runner.coverage_start/ut_runner.coverage_stop occurred. That didn't happened and I had to add schema to parameters which makes it not so optional ;)

Generally I'm pretty pumped about it, start/stop takes almost no time so overhead is minimal, the fact that we can generate multiple reports for different purposes is awesome.

One thing for consideration is to add option to remove data for specific run_id. Without auto cleanup after generation of report for multiple runs it may be just a lot of unused data clogging datafiles. Something like ut_runner.coverage_clean(a_coverage_run_id) might be useful especially if code coverage is generated over multiple run_ids and over many big schemas.

Overall 10/10, would recommend this to my family and friends ;)

@jgebal

@jgebal I also struggle with some file mappings as my structure is a bit different. My folder structure looks like this:

- functions
- packages
--- definitions
--- bodies
- procedures

I don't know how to do mapping if I have additional folder level for packages

@ksawerykarwacki

@ksawerykarwacki
I'll look into the schema name issue.
Note however, that coverage reporting has a bit of inverse logic to what you described:

should generate me report for all schemas where ut_runner.coverage_start/ut_runner.coverage_stop occurred

Coverage is gathered on all schemas (regardless of connected user.
So if you connect as user X, enable coverage and run code in schema Y, the coverage on objects located in schema Y is gathered, not in schema X

When reporting, you need to specify which schemas or objects you're interested in for coverage reports.
If no schema is provided, the current schema of session where report is generated will be used.

For the mapping, you will need a clever regex, depends on your file names too.

Can you provide a more detailed example of what would be file names and extensions for given objects?
Also do your file path (filename or directory) include owner(schema) or are all sources deployed to one schema?

All of this decides on config for report.

If you will provide more details I can give you some help with that.
Have a look at this issue: utPLSQL/utPLSQL-cli#184

Navigation Menu

Repository navigation

Use coverage without ut.run() #1025

Having all that said

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Use coverage without ut.run() #1025

Description

Activity

jgebal commented on Nov 28, 2019

ksawerykarwacki commented on Nov 29, 2019

jgebal commented on Nov 29, 2019

Having all that said

ksawerykarwacki commented on Nov 29, 2019

jgebal commented on Nov 29, 2019

ksawerykarwacki commented on Nov 29, 2019

jgebal commented on Mar 14, 2020

PhilippSalvisberg commented on Mar 15, 2020

pesse commented on Mar 20, 2020

ksawerykarwacki commented on Apr 10, 2020

jgebal commented on Apr 10, 2020

jgebal commented on Apr 13, 2020

ksawerykarwacki commented on May 12, 2020

jgebal commented on May 12, 2020

ksawerykarwacki commented on May 18, 2020

jgebal commented on Jun 21, 2020

ksawerykarwacki commented on Jun 21, 2020

ksawerykarwacki commented on Jun 30, 2020

ksawerykarwacki commented on Jun 30, 2020

jgebal commented on Jul 1, 2020

jgebal commented on Jul 1, 2020

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions