search

utPLSQL / utPLSQL

Testing Framework for PL/SQL
https://www.utplsql.org/
Apache License 2.0
562 stars 186 forks source link

Struggling with custom file path in ut.run() #1115

Closed sometowngeek closed 2 years ago

sometowngeek commented 3 years ago

Describe the bug The documentation does not provide instructions on how to use ut.run() within sqlplus and make references to source/test files. I am trying to produce a valid sonar report that Sonar will scan. It is currently being rejected because the tests are not referring to appropriate source files.

This is the current file I have for sonar_report.xml and the file path is not pointing to the appropriate source/test directory.

<?xml version="1.0"?>
<testExecutions version="1">
    <file path="test_betwnstr">
        <testCase name="basic_usage" duration="1" >
        </testCase>
        <testCase name="zero_start_position" duration="1" >
        </testCase>
    </file>
    <file path="test_get_something">
        <testCase name="test_something" duration="1" >
        </testCase>
        <testCase name="test_something_invalid" duration="1" >
        </testCase>
    </file>
    <file path="test_lang">
        <testCase name="valid_primary_lang" duration="1" >
        </testcase>
        <testCase name="invalid_primary_lang" duration="1">
        </testCase>
    </file>
</testExecutions>

I tried perusing the documentation and the only guides I see are for using the utPLSQL-cli which is not an option at my organization, so I am wondering if there is a way to do the same with just sqlplus?

Provide version info Information about utPLSQL and Database version,

Information about client software What client was used to run utPLSQL tests? Was it from TOAD, SQLDeveloper, SQLPlus, PLSQL Developer etc...

To Reproduce Steps to reproduce the behavior, I am simulating one test:

  1. Create testing package for TEST_GET_SOMETHING function.
  2. Executed: 
    
exec ut.run('unit_test_user');

spool sonar_report.xml;

exec ut.run(ut_sonar_test_reporter(), a_source_file_mapping => ut_file_mappings(ut_file_mapping(file_name => 'this_dir/get_something.fnc', object_owner=> 'GET_SOMETHING_OWNER', object_name=> 'GET_SOMETHING', object_type=>'FUNCTION'));`

spool off;

3. See error

Error starting at line : 4 in command - BEGIN ut.run(ut_sonar_test_reporter(), a_source_file_mapping => ut_file_mappings(ut_file_mapping(file_name => 'this_dir/get_something.fnc', object_owner=> 'GET_SOMETHING_OWNER', object_name=> 'GET_SOMETHING', object_type=>'FUNCTION'));

Error report - ORA-06550: line 1, column 219: PLS-00306: wrong number or types of arguments to call to 'RUN' ORA-06550: line 1, column 219 PL/SQL: Statement ignored


**Expected behavior**
I am unable to have utPLSQL-cli installed on Jenkins and my organization stated they will not install it and I am required to use utplsql to produce reports.

The documentation does not show examples on how to use it with just sqlplus.

**Example code**
If applicable, add sample code to help explain your problem.
Please avoid putting your company private/protected code in an issue, as it might violate your company's privacy and security policies.

```sql
create or replace package test_get_something
AS
    --%suite(Tests the get_something function.)

    --%test(get_something() returns 1)
    procedure test_valid_get_something();

END;
/

create or replace package body test_get_something
AS

    procedure test_valid_get_something is
        ut.expect(get_something()).to_equal(1);
    end test_valid_get_something;
end;
/

Additional context Add any other context about the problem here.

sometowngeek commented 3 years ago

I was perusing in the issues and found a closed issue within utPLSQL-cli with the post I think would be relevant.

https://github.com/utPLSQL/utPLSQL-cli/issues/184#issuecomment-649139965

I will look into using @jgebal 's approach in this.

Maybe the documentation could be updated to include those examples?

sometowngeek commented 3 years ago

It has been a while and I'm still stuck on this.

Here is my setup:

Database schemas:

Source paths:

I am working with a software that we purchased from a vendor and we are trying to retain the original content, including names, syntax, etc. in case we need to roll back to them.

I am trying to test get_gameid() function:

-- get_gameid.fnc
create or replace function get_gameid() return integer is
    g_id integer := 0;
begin
    select  game_id 
    into    g_id
    from    game_property;

    return g_id;
end;
/

I tried to create a test package to test the get_gameid function:

-- test_getgameid.pks
create or replace package test_getgameid
AUTHID CURRENT_USER as

    --%suite(Test getgameid function.)

    --%test(Valid stuff)
    procedure valid;
end test_getgameid;
/
-- test_getgameid.pkb
create or replace package body test_getgameid as

    procedure valid is
        ut.expect(foo.get_gameid()).to_equal(523352);
    end;
end test_getgameid;
/

I made some progress in assessing @jgebal's answer in #184 and trying to mimic it.

I replicated his example and was able to get the same result.

However... When I tried to modify the ut.run(...) to run the test I created by providing the test package name, source file name, it does not work.

This is my modified ut.run(...)

With the assumption that the source tree looks like this: test/this_owner/function_or_procedure_name.ext

Regex pattern tested: https://regex101.com/r/EJ8hIZ/2

begin
  ut.run(
    'test_get_gameid',
    ut_coverage_sonar_reporter(),
    a_source_file_mappings => ut_file_mapper.build_file_mappings(
      a_file_paths => ut_varchar2_list(
        'test/foo/function/test_get_gameid.fnc'
      ),
      a_regex_pattern => '/test\/(foo|lorem|dolor)\/(function)\/((\w)+(_(\w)+){0,10})\.(\w{3})$',
      a_object_owner_subexpression  => 1,
      a_object_name_subexpression   => 3,
      a_object_type_subexpression   => 7
    )
  );
end;
/

The resulting output:

<?xml version="1.0"?>
<coverage version="1">
</coverage>

PL/SQL procedure successfully completed.

But if I run this: exec ut.run('test_get_gameid') by itself, it works.

Output:

Test test_get_gameid
  Valid stuff [.001 sec]

Finished in .002612 seconds
1 tests, 0 failed, 0 errored, 0 disabled, 0 warning(s)

PL/SQL procedure successfully completed.

I am not sure where I am stumped... I have a feeling that it has to do with how I created the regex and/or created the mapping with ut_file_mapper.build_file_mappings(...).

Any thoughts on why I am getting empty results?

jgebal commented 3 years ago

Does the user executing the tests has rights to execute any and create any procedure? See coverage documentation for privileges needed. If user doesn't have privileges, tests will run but coverage will be blank.

sometowngeek commented 3 years ago

Yeah, the user running the test (UT3_USER) has that privilege.

Here are their privileges:

System:

Table: (With EXECUTE privilege)

Update:

I misread your comment. No, it does not have the EXECUTE ANY PROCEDURE. I will have my DBA get that added and then try it out.

sometowngeek commented 3 years ago

Sorry... I realized my earlier post was left unfinished.

I filled in the rest of the test package.

sometowngeek commented 3 years ago

Now UT3_USER has EXECUTE ANY PRIVILEGE.

Sys privs:

I ran the test again and had no luck...

Could it be because I don't have the source path / regex setup properly?

sometowngeek commented 3 years ago

Using your answer from #184, I placed PACKAGE_A under separate schema (Amet), recompiled the test packages in UT3_USER, and then re-ran this:

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;

There was no coverage to report... I think you may be right that there is a privilege issue.

I will check the coverage page to see what I am missing.

For reference:

Version:       12.1.0.2.0
Compatibility: 12.1.0

UT_VERSION     
---------------
v3.1.10.3349

BANNER                                                                             CON_ID
------------------------------------------------------------------------------ ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production            0
PL/SQL Release 12.1.0.2.0 - Production                                                  0
CORE    12.1.0.2.0  Production                                                      0
TNS for Linux: Version 12.1.0.2.0 - Production                                          0
NLSRTL Version 12.1.0.2.0 - Production                                                  0

PARAMETER                      VALUE                              
------------------------------ -----------------------------------
NLS_LANGUAGE                   AMERICAN                           
NLS_TERRITORY                  AMERICA                            
NLS_CURRENCY                   $                                  
NLS_ISO_CURRENCY               AMERICA                            
NLS_NUMERIC_CHARACTERS         .,                                 
NLS_CALENDAR                   GREGORIAN                          
NLS_DATE_FORMAT                DD-MON-RR                          
NLS_DATE_LANGUAGE              AMERICAN                           
NLS_SORT                       BINARY                             
NLS_TIME_FORMAT                HH.MI.SSXFF AM                     
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM           
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR                 
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR       
NLS_DUAL_CURRENCY              $                                  
NLS_COMP                       BINARY                             
NLS_LENGTH_SEMANTICS           BYTE                               
NLS_NCHAR_CONV_EXCP            FALSE                              

PORT_STRING              
-------------------------
x86_64/Linux 2.4.xx
sometowngeek commented 3 years ago

I did some further testing and do not think it is related to privilege issues.

I ran this:

begin
  ut.run(
    'ut3_user',
    ut_coverage_sonar_reporter(), 
    a_coverage_schemes => ut_varchar2_list('amet'), 
    a_include_objects => ut_varchar2_list('package_a', 'get_gameid'));
end;
/

And was able to get coverage results.

<?xml version="1.0"?>
<coverage version="1">
<file path="amet.get_gameid">
<lineToCover lineNumber="2" covered="true"/>
<lineToCover lineNumber="4" covered="true"/>
<lineToCover lineNumber="6" covered="true"/>
</file>
<file path="amet.package_a">
<lineToCover lineNumber="3" covered="true"/>
<lineToCover lineNumber="5" covered="true"/>
<lineToCover lineNumber="6" covered="false"/>
<lineToCover lineNumber="8" covered="true"/>
<lineToCover lineNumber="10" covered="true"/>
</file>
</coverage>

PL/SQL procedure successfully completed.
pesse commented 3 years ago

Hey @sometowngeek is this still an issue?

What I noticed when reading your example:

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;

The subexpression indizes don't look valid to me. AFAIK they indicate which sub-expression of the used regex should be extracted, so this would be 1 for ((\w|[$#])+, 2 for (spec|body) and 3 for (\w{3})

Cheers, Sam