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
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
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
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.
- SAP Managed Tags:
2 Comments
