Mapping Source and Test folders for SonarCloud

[rafael-trevisan]

Hi,

I've been struggling to properly map the source and test folders to avoid SonarCloud complaining:

INFO: Test execution data ignored for 1 unknown files, including:

This is what I am using:

utPLSQL-cli/bin/utplsql 
  run
  username/password@url
  -source_path=src
  -test_path=test
  -f=ut_coverage_sonar_reporter -o=coverage.xml
  -f=ut_sonar_test_reporter     -o=test-results.xml

The project folder looks like:

src
+-- packages
    +-- r__package_a_spec.pks
    +-- r__package_a_body.pkb
    +-- r__package_b_spec.pks
    +-- r__package_b_body.pkb
test
    +-- r__test_package_a_spec.pks
    +-- r__test_package_a_body.pkb
    +-- r__test_package_b_spec.pks
    +-- r__test_package_b_body.pkb

I think the problem is the "spec" and "body" that I have to append to the filenames due to Flyway, but I am don't know how to get the -regex_expression thing right.

Another strange thing is that the coverage report is blank. This is the sample content:

<?xml version="1.0" encoding="UTF-8"?>
<coverage version="1">
</coverage>

<?xml version="1.0" encoding="UTF-8"?>
<testExecutions version="1">
<file path="test_package_a">
<testCase name="some_proc" duration="3" >
</testCase>
</file>
</testExecutions>

Any thoughts will be very appreciated.

[jgebal]

Hi @rafael-trevisan
I assume you have a naming pattern for your packages/files
Can you:

• give the actual package names for the sample files you've listed
• give schema name where they get deployed
• give username that you connect as to run the tests

The key things to know are:

• is test owner = src owner
• is test owner = test run(connect) user.
  Woth that I should be able to give some hints to make it work.

[rafael-trevisan]

Hi @jgebal,

For a file like r__package_a_spec.pks the package name is package_a.
For a test file like r__test_package_a_spec.pks the package name is test_package_a.

The schema name where it's been deployed is a "run time" schema like BUILD_1, BUILD_2, BUILD_3 and etc. Each time a commit happens, the CI is creating a brand new schema, then Flyway re-creates the database including the UT and utPLSQL takes action running the test in that brand new schema.

I am connecting utPLSQL with the same "run time" user (BUILD_<n>).

Test owner is the same as src owner, however, I do have other schemas in the database with the same packages (both for logic and test).

[rafael-trevisan]

If it helps, this is the sonar-project.properties:

sonar.sources=./src
sonar.coverageReportPaths=coverage.xml
sonar.coverage.exclusions=**/*.sql,**/*.pks
sonar.tests=./test
sonar.testExecutionReportPaths=./test-results.xml
sonar.plsql.file.suffixes=pkb,plsql
sonar.language=plsql
sonar.exclusions=**/*.sql,**/*.xml

[jgebal]

So I took the default mapping regex parameters we have:

  gc_file_mapping_regex        constant varchar2(100) := '/(((\w|[$#])+)\.)?((\w|[$#])+)\.(\w{3})$';
  gc_regex_owner_subexpression constant positive := 2;
  gc_regex_name_subexpression  constant positive := 4;
  gc_regex_type_subexpression  constant positive := 6;

Modified it and tested on regex101 - you can see example here:
https://regex101.com/r/LoWaHo/2

Then I ran the below test-cases in SQL console to verify mappings:

create or replace package package_a as
  function f return integer;
end;
/

create or replace package body package_a as
  function f(a integer) return integer is
    x integer := 0;
  begin
    if a = 2 then
      x := 1;
    else
      x := x + 3;
    end if;
    return x;
  end;
end;

/

create or replace package test_package_a as
  --%suite

  --%test
  procedure proc;
end;
/

create or replace package body test_package_a as
  procedure proc is
  begin
    ut.expect(package_a.f(0) ).to_equal( 1 );
  end;
end;
/

Running with sonar coverage reporter - mapping source packages:

begin
  ut.run(
    'test_package_a',
    ut_coverage_sonar_reporter(),
    a_source_file_mappings => ut_file_mapper.build_file_mappings(
      a_file_paths => ut_varchar2_list(
        'src/packages/r__package_a_spec.pks',
        'src/packages/r__package_a_spec.pkb',
        'src/packages/r__package_b_spec.pks',
        'src/packages/r__package_b_spec.pkb'
      ),
      a_regex_pattern => '/r__((\w|[$#])+)_(spec|body)\.(\w{3})$',
      a_object_owner_subexpression  => 9,
      a_object_name_subexpression   => 1,
      a_object_type_subexpression   => 4
    )
  );
end;
/

Outcomes:

<?xml version="1.0"?>
<coverage version="1">
<file path="src/packages/r__package_a_spec.pkb">
<lineToCover lineNumber="3" covered="true"/>
<lineToCover lineNumber="5" covered="true"/>
<lineToCover lineNumber="6" covered="false"/>
<lineToCover lineNumber="8" covered="true" branchesToCover="1" coveredBranches="1"/>
<lineToCover lineNumber="10" covered="true"/>
</file>
</coverage>

Running with sonar test reporter - mapping test packages:

begin
  ut.run(
    'test_package_a',
    ut_sonar_test_reporter(),
    a_test_file_mappings => ut_file_mapper.build_file_mappings(
      a_file_paths => ut_varchar2_list(
        'test/packages/r__test_package_a_spec.pks',
        'test/packages/r__test_package_a_body.pkb',
        'test/packages/r__test_package_b_spec.pks',
        'test/packages/r__test_package_b_body.pkb'
        ),
      a_regex_pattern => '/r__((\w|[$#])+)_(spec|body)\.(\w{3})$',
      a_object_owner_subexpression  => 9,
      a_object_name_subexpression   => 1,
      a_object_type_subexpression   => 4
      )
    );
end;

Outcomes:

<?xml version="1.0"?>
<testExecutions version="1">
<file path="test/packages/r__test_package_a_body.pkb">
<testCase name="proc" duration="4" >
<failure message="some expectations have failed">
<![CDATA[
Actual: 3 (number) was expected to equal: 1 (number)
at "UT3_TESTER_HELPER.TEST_PACKAGE_A.PROC", line 4 ut.expect(package_a.f(0) ).to_equal( 1 );
]]>
</failure>
</testCase>
</file>
</testExecutions>

So conclusion is as follows:
You can use following command to achieve the same via utplsql-cli:

utplsql run username/password@url \
-source_path=src                                              \
-regex_expression='/r__((\w|[$#])+)_(spec|body)\.(\w{3})$'    \
-owner_subexpression=9                                        \
-name_subexpression=1                                         \
-type_subexpression=4                                         \
-test_path=test                                                \
-regex_expression='/r__((\w|[$#])+)_(spec|body)\.(\w{3})$'    \
-owner_subexpression=9                                        \
-name_subexpression=1                                         \
-type_subexpression=4                                         \
-f=ut_coverage_sonar_reporter     -o=coverage.xml             \
-f=ut_sonar_test_reporter         -o=test-results.xml         \
-f=ut_documentation_reporter      -o=test_results.log -s

[rafael-trevisan]

That’s amazing! I’ll test it here in a hour or so and will share the results.

[jgebal]

This is assuming that:
the filename prefix is static = r__
the filename suffix is static = _spec or _body

You can play around with the regex if needed.

One thing to note is that I've specified a non existing matching group value 9 for the owner_subexpression.
This is needed, so that it evaluates to NULL, as you don't have owner in filename or file patch.
When it is NULL, the owner is taken based suitepath evaluation - in your case it is current user.

[rafael-trevisan]

Makes total sense. Thanks for detailing.

Wondering here if I should use the -p parameter to indicate which schema to run the tests as I have multiple schemas with the same packages.

[jgebal]

No need, as you connect as schema owner so this is default.

[rafael-trevisan]

Worked like a charm. Great thanks.

INFO: Sensor Generic Coverage Report
INFO: Parsing /github/workspace/coverage.xml
INFO: Imported coverage data for 1 files
INFO: Sensor Generic Coverage Report (done) | time=57ms
INFO: Sensor Generic Test Executions Report
INFO: Parsing /github/workspace/test-results.xml
INFO: Imported test execution data for 1 files
INFO: Sensor Generic Test Executions Report (done) | time=7ms

However I don't see (or don't know yet where to see) this information on the SonarCloud as it still shows 0% of coverage and the line I am sure it's being tested shows in red "Not covered by tests".

I will reach out to SonarCloud folks to see what missing, anyways great thanks again.