Everyone knows that testing is important, and anyone who has been involved in software development is likely to have come across a range of testing tools. When you’re building an application you’ve got plenty of unit testing frameworks to choose from (ABAP Unit, JUnit, QUnit, etc.), but what if you’re just building a piece of an application? What if you are building a set of Hana procedures which stand alone, waiting to be integrated into a number of potential applications? How do you test just your piece?

In previous projects, I have used Fitnesse to test applications, and also to run procedures and SQL commands against databases, so I investigated using this framework again. On searching, I came across references to DBFit, and discovered that this was the newest recommended way to test a database using Fitnesse. DBFit offered the functionality and power of Fitnesse, with additional out-of-the-box database operation support without the need to code fixtures.



DBFit meets HANA

Working with HANA means a lot of things, but importantly, for the purposes of DBFit, it meant that just a little bit of work was necessary to get our out-of-the-box solution to really work. DBFit comes with a number of connector classes to support different databases, and instructions on how to go about creating your own connector if your database is not supported.

To customise DBFit to work with Hana, a HanaEnvironment java class is necessary, which provides a mapping between the HANA database types (e.g. NVARCHAR) and java.sql types, and also implements two key queries – one which yields all the column metadata of a given table or view, and one which yields all the parameter information of a given procedure.



Under the hood

Creating a new HanaEnvironment class was as simple as implementing a new class which extended the AbstractDbEnvironment class provided by DBFit. This class does the bulk of the work in preparing queries and managing results, with the HanaEnvironment class just taking care of specific syntax and requirements for our environment.

The three most basic requirements to get the connector up and running were:


Adding HANA types to the java.sql type matching

HANATypes.png

Other connectors bundled with DBFit had examples of these type lists, so it was easy to create a list mapping HANA data types (e.g. NVARCHAR, NCLOB, etc.) to their java SQL types within the HanaEnvironment class.


A query to return table metadata

TableMetadata.png

In order to allow proper mapping of table columns for querying the database, a getAllColumns method must be implemented in the HanaEnvironment class. At its most basic, this method searches the TABLE_COLUMNS system table in HANA to get information about the columns belonging to the given table.

Extra checking had to be implemented here to account for database objects that are defined using HANA repository objects (e.g. objects which do not take the form “MY_SCHEMA”.”MY_TABLE” but are defined as “my.package.object::MY_TABLE”). The code checks for the presence of a double colon, denoting this package structure.


A query to return procedure metadata

ProcMetadata.png

 

A similar method, getAllProcedureParameters, also has to be implemented in the HanaEnvironment class. This method searches the PROCEDURE_PARAMETERS system table for information about the given procedure. Special checking for the package structure (with the double colon notation) was also implemented here. Inverted commas are stripped from copies of the names of the procedure and schema to allow for searching the system table, but maintained in the actual schema and procedure name objects in order to ensure that they are called properly when reaching the DB.


Limitations


Unfortunately, there is one limitation that was identified during the development and use of this connector that remains outstanding – if the output of a procedure is a table variable, the DBFit framework cannot currently process this output.



An automated regression suite, at the click of a button

Developing the DBFit connector enabled our QA colleagues to write a comprehensive test suite for our procedures, which can be run at the click of a button. The flexibility of Fitnesse allows us to group these tests with common setup and teardown pages, set global variables that can be inherited through multiple tests, and view simply and quickly whether changes have broken any of our existing functionality. The wiki syntax is easily understood by everyone, and the built in fixtures from DBFit have allowed many database operations be performed without explicit knowledge of SQL (e.g. calling procedures, verifying the output of the procedures, etc).



I want to use DBFit

DBFit is open source, so you can get it from their github repository. There is information about how to build the project, as well as how to run DBFit, on the DBFit public site. You’ll need to make sure that you have a copy of the SAP ngdbc.jar (to be placed in the custom-libs folder) too.

To report this post you need to login first.

2 Comments

You must be Logged on to comment or reply to a post.

  1. Bodo Hüsemann

    Would you please share the Hana Connector to the github dbFit community? I would like to use dbFit on Hana and if you have it already, it would help everyone.

    Many thanks,

    Bodo

    (0) 
    1. Jennifer Keane Post author

      Hi Bodo,

      I’m afraid that I can’t share the completed connector directly (for contractual reasons) but I can hopefully guide you in the right direction to creating your own.

      If you can pull down the DBFit source code from github, and find the folders which list the existing connectors, that’s almost half the battle! 🙂

      I recommend duplicating an existing connector (such as the MySQL connector) and using this as a base to start off. If you do this, you’ll have the various methods and type definitions already there, and they’ll just need a little modification to operate for HANA instead.

      After this, hopefully the code samples which I’ve provided in the blog above should give enough information for you to modify the two key methods (returning procedure and table metadata) and the type matching list. These methods are easily found in the existing connectors and can serve as a good basis for the modifications above.

      If you can get those two methods up and running, and searching on the correct HANA system tables (SYS.TABLE_COLUMNS and SYS.PROCEDURE_PARAMETERS) the rest of the framework should just work. It’s designed to be extensible for new connectors added.

      I hope this helps to point you in the right direction, please feel free to reach out again if something isn’t clear.

      Jen

      (0) 

Leave a Reply