GeezerVison is showing github.com — Use coverage without ut.run() · Issue #1025 · utPLSQL/utPLSQL
Use coverage without ut.run() #1025DescriptionDescribe 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 Activityjgebal commented on Nov 28, 2019 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. ksawerykarwacki commented on Nov 29, 2019 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. jgebal commented on Nov 29, 2019 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;
begin
ut_coverage.coverage_start();
ut_coverage.coverage_resume();
end;
/
- execute your tested code:
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. Reacted by Philipp Salvisberg, Ksawery Karwacki and alesana-san ksawerykarwacki commented on Nov 29, 2019 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. jgebal commented on Nov 29, 2019 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:
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 :) Reacted by Ksawery Karwacki and Philipp Salvisberg ksawerykarwacki commented on Nov 29, 2019 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. jgebal commented on Mar 14, 2020 @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. PhilippSalvisberg commented on Mar 15, 2020 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. pesse commented on Mar 20, 2020 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. ksawerykarwacki commented on Apr 10, 2020 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 ;) jgebal commented on Apr 10, 2020 HI @ksawerykarwacki
Long weekend is coming and with lockdown, there is a chance I might make good progress on it :) jgebal commented on Apr 13, 2020 @ksawerykarwacki - will need another weekend or two to get into this.
This long Easter weekend I have decided to actually get some rest. Reacted by Ksawery Karwacki ksawerykarwacki commented on May 12, 2020 Is there some progress by any chance? jgebal commented on May 12, 2020 Sorry @ksawerykarwacki
I had some really serious personal matters to attend.
I've started looking into it just now. ksawerykarwacki commented on May 18, 2020 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 :) jgebal commented on Jun 21, 2020 ksawerykarwacki commented on Jun 21, 2020 ksawerykarwacki commented on Jun 30, 2020 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 ;) ksawerykarwacki commented on Jun 30, 2020 @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 jgebal commented on Jul 1, 2020 @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. jgebal commented on Jul 1, 2020 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
MetadataMetadataDevelopmentNo branches or pull requestsIssue actions
|