Support utPLSQL template creation

[PhilippSalvisberg]

Based on an existing PL/SQL unit (package, function, procedure, type) an utPLSQL test package (spec and body) may be created based on a template. Add a dedicated context menu item in the Connections navigator tree.

This is independent of #11. It works without oddgen for a single unit. The output is generated in a new worksheet (spec and body). More generation options are provided with #11 via oddgen.

[jgebal]

There is a package generator created by @lwasylow
https://github.com/lwasylow/utPlssqlTestGen
It is just not tested and not included in utPLSQL yet.

[PhilippSalvisberg]

I've installed utPlssqlTestGen and run

SELECT * FROM TABLE(ut_test_generator.RUN(USER, 'LINEAGE_UTIL', NULL));

which produced

CREATE OR REPLACE PACKAGE PLSCOPE.UT_LINEAGE_UTIL IS

   --%suitepath(alltests)
   --%suite(ut_lineage_util)
   --%displayname(Sample Display Name) 

   --%test(ut_get_dep_cols_from_query)
   --%disabled
   --%displayname(ut_get_dep_cols_from_query)
   PROCEDURE ut_get_dep_cols_from_query;

   --%test(ut_get_dep_cols_from_view)
   --%disabled
   --%displayname(ut_get_dep_cols_from_view)
   PROCEDURE ut_get_dep_cols_from_view;

   --%test(ut_set_recursive)
   --%disabled
   --%displayname(ut_set_recursive)
   PROCEDURE ut_set_recursive;

   --%test(ut_get_target_cols_from_insert)
   --%disabled
   --%displayname(ut_get_target_cols_from_insert)
   PROCEDURE ut_get_target_cols_from_insert;

   --%test(ut_get_dep_cols_from_insert)
   --%disabled
   --%displayname(ut_get_dep_cols_from_insert)
   PROCEDURE ut_get_dep_cols_from_insert;

   --%test(ut_get_recursive)
   --%disabled
   --%displayname(ut_get_recursive)
   PROCEDURE ut_get_recursive;

END;
/

CREATE OR REPLACE PACKAGE BODY PLSCOPE.UT_LINEAGE_UTIL IS

   /*************************************************************
   ******************     README     ****************************
   * This is a generic pacakge skeleton creaed from db metadata *
   * by default is in disabled stated (--%disabled)             *
   * to enable it just remove that annotation.                  *
   * This is not a full test package and dont parse a code      *
   *************************************************************/

   /*******************************************************
      TEST: get_dep_cols_from_query
      SCENARIOS                                            
      1) Sample Desciption
   *******************************************************/

   PROCEDURE ut_get_dep_cols_from_query IS
   BEGIN
      NULL;
   END ut_get_dep_cols_from_query;

   /*******************************************************
      TEST: get_dep_cols_from_view
      SCENARIOS                                            
      1) Sample Desciption
   *******************************************************/

   PROCEDURE ut_get_dep_cols_from_view IS
   BEGIN
      NULL;
   END ut_get_dep_cols_from_view;

   /*******************************************************
      TEST: set_recursive
      SCENARIOS                                            
      1) Sample Desciption
   *******************************************************/

   PROCEDURE ut_set_recursive IS
   BEGIN
      NULL;
   END ut_set_recursive;

   /*******************************************************
      TEST: get_target_cols_from_insert
      SCENARIOS                                            
      1) Sample Desciption
   *******************************************************/

   PROCEDURE ut_get_target_cols_from_insert IS
   BEGIN
      NULL;
   END ut_get_target_cols_from_insert;

   /*******************************************************
      TEST: get_dep_cols_from_insert
      SCENARIOS                                            
      1) Sample Desciption
   *******************************************************/

   PROCEDURE ut_get_dep_cols_from_insert IS
   BEGIN
      NULL;
   END ut_get_dep_cols_from_insert;

   /*******************************************************
      TEST: get_recursive
      SCENARIOS                                            
      1) Sample Desciption
   *******************************************************/

   PROCEDURE ut_get_recursive IS
   BEGIN
      NULL;
   END ut_get_recursive;

END;
/

I'd like the template to be a bit more configurable, e.g.

• prefix/suffix of the generated test package
• suitepath
• displayname
• enable/disable tests
• comment blocks

[lwasylow]

Some of these options can be easily added however I would be cautious regarding too much config.
While purpose of such package is to cut time, adding too much config will lead to situation when configuring a generator will take almost as much as just writing package.

I can see some improvements to be added there like auto recognize of data type , and some generic extra configuration on package level like suite path, enabled disabled tests or prefixes and suffixes.

I will do some more work on it in near time just had a busy month , but you welcome to hijack it and use it or extend if you find it useful, or move it ti utPlsql project and just maintain there.

[PhilippSalvisberg]

Creating the skeleton in the extension should be right click, choosing "Create utPLSQL test" and the result is presented in a new worksheet. Configuration is part of the preferences. The extension will come with "reasonable" defaults, so changing them is optional and does not complicate the usage.

I currently favour implementing the test generator as part of the extension.

[PhilippSalvisberg]

Generate tests for public procedures not protected by an accessible_by_clause. See also https://www.salvis.com/blog/2018/04/30/white-listed-pl-sql-programs-in-oracle-database-18c/

[jgebal]

It would be great if the generator would produce the following for 3.1.0:

CREATE OR REPLACE PACKAGE PLSCOPE.UT_LINEAGE_UTIL IS

   --%suitepath(alltests)
   --%suite(lineage_util)

   --%context(get_dep_cols_from_query)
   
   --%test(does something successful)
   PROCEDURE get_dep_cols_from_query_1;

  --%test(fails on domething)
   PROCEDURE get_dep_cols_from_query_2;
  --%endcontext

....
 
END;
/


CREATE OR REPLACE PACKAGE BODY PLSCOPE.UT_LINEAGE_UTIL IS

   /*************************************************************
   ******************     README     ****************************
   * This is a generic pacakge skeleton creaed from db metadata *
   * by default is in disabled stated (--%disabled)             *
   * to enable it just remove that annotation.                  *
   * This is not a full test package and dont parse a code      *
   *************************************************************/

   /*******************************************************
      TEST: get_dep_cols_from_query
      SCENARIOS                                            
      1) Sample Desciption
   *******************************************************/

   PROCEDURE get_dep_cols_from_query_1 IS
      l_expected INTEGER;
      l_actual INTEGER;
   BEGIN
      --Arrange

      --Act
       -- lineade_util.get_dep_cols_from_query;

      --Assert
      l_expected := 1;
      ut.expect(l_actual).to_equal(l_expected);
   END ut_get_dep_cols_from_query;
...
END;
/

This is more of a concept/template I'd like to see.

[lwasylow]

Whichever way this got implemented, using modified mine package or Philipp would be great if that could be added to utplsql code base somehow. I think it's useful feature but not everyone uses sql developer.

[PhilippSalvisberg]

I'm going to consider using the core functionality when it is available, but it is not a necessity for the SQL Developer extension.

@lwasylow please open a dedicated issue in the core project, if you want to make it part of the core. Thank you.