SQLScript Unit Tests, the End-User Test Framework

With version 2.0 SPS04 of the SAP HANA database, the so-called “End-User Test Framework in SQLScript” was introduced. This makes it possible to create SQLScript Unit tests in the form of  User Defined Libraries (UDL). These can then be SQLSCRIPT_RUN_TESTS_ON_ORIGINAL_DATA executed automatically when the procedure is called.

What is being tested?

Most of the time, test cases are used to check for a subprogram whether the expected output is also generated when a given input is entered. In the case of databases, therefore, these are procedures and functions that are primarily tested. However, you can also test the state of the database by querying, for example, the number of records in a table. For example, in the following example, we can verify that the scripts for the demo data model were successful and generated the expected number of records.

Example

To explain the concept of the Test Framework, let’s start with a simple example. In Listing 1.2 you will find a test library that contains only the bare essentials. It consists of only T_AUFGABEN_CNT one test procedure . It checks whether there are exactly 1000 entries in the table AUFGABEN.

CREATE LIBRARY test_demo_dm 
LANGUAGE SQLSCRIPT TEST 
AS BEGIN 

 @test()
 PUBLIC PROCEDURE t_aufgaben_cnt 
 AS BEGIN
 USING sqlscript_test AS test;
 test:expect_eq((SELECT COUNT(*) FROM tasks),
 1000 );
 END; 
END;

To run this SQLScript Unit test in our test library, we call the following procedure in the SQL console. You may need to customize the name of the schema:

CALL SQLSCRIPT_RUN_TESTS_ON_ORIGINAL_DATA('{"schema":"SYSTEM",
 "library":"TEST_DEMO_DM"}', ?, ?, ?);

The result of this procedure call is 3 tables. In our case, only in the first one there is actually an entry, because the test was successful. As soon as a test fails, the 2. Table a message is issued and the 3. Table contains details about the call stack of errors. Below is an example of the output tables

The language of the tests

The first thing that is striking about the small example is that the library is created with the language SQLSCRIPT TEST. This distinguishes test libraries from productive libraries. In fact, the implementation of the test is also SQLScript.

Pragmas for procedures

Pragmas are used to classify the individual procedures of the test libraries. The most important role is played by the parameterless test procedures. These are marked by a leading pragma @test() .

In order to establish a defined system state  before the test, a corresponding procedure can be marked with the pragma @TestSetUpLibrary().

After the tests are performed, the procedure with the pragma @TestTearDownLibrary() may be called to clean up after the test procedures.

Test procedures

Within the test procedures, the system’s test procedures can be used to verify SQLSCRIPT_TEST the states of the program. The different inspection procedures can be found in Table 1.1. Any scalar expression can be used as a parameter value of 1 or 2.

Testing procedure Description
EXPECT_EQ(<Wert1>, <Wert2>) Checks whether both values are equal
EXPECT_NE(<Wert1>, <Wert2>) Checks whether the two values are different
EXPECT_GE(<Wert1>, <Wert2>) Checks whether value 1 is greater than or equal to the value 2
EXPECT_GT(<Wert1>, <Wert2>) Checks whether value 1 is greater than value 2
EXPECT_LE(<Wert1>, <Wert2>) Checks whether value 1 is less than or equal to the value 2
EXPECT_LT(<Wert1>, <Wert2>) Checks whether value 1 is less than value 2
EXPECT_NULL(<Wert>) Checks whether the value is null
EXPECT_TABLE_EQ(<Tabelle1>, <Tabelle2>[,<Reihenfolge ignorieren>]) Checks whether the two tables are identical. The order does not matter, as long as the corresponding flag is not set to FALSE.
FAIL(<Nachricht>) The test is set to failed and the Message is issued

Modularization in the test library

These check procedures of our SQLScript Unit tests do not need to be called directly in the test procedure. It can also be further modularized within the test library. For example, if multiple tables should be checked for the number of records it contains, it is a good way to offload this part of the code to a separate procedure to avoid unnecessary repetition.

In Listing 1.3, the logic to check the number of records within a table was outsourced to a private procedure. This makes it easy to check a lot of tables without having to repeat the same code every time.

CREATE LIBRARY TEST_DEMO_DM 
LANGUAGE SQLSCRIPT TEST 
AS BEGIN 
  
 PRIVATE PROCEDURE check_table_count
 (IN iv_tabname NVARCHAR(30),
 IN iv_expected_count INTEGER)
 AS BEGIN
 USING sqlscript_test AS test;
 DECLARE lv_query NVARCHAR(5000);
 DECLARE lv_count INTEGER;
    
 lv_query = 'SELECT COUNT(*) FROM' || :iv_tabname;
 EXEC lv_query INTO lv_count;
 test:expect_eq( :lv_count, :iv_expected_count );
      
 END;
  
 @test()
 PUBLIC PROCEDURE t_table_count 
 AS BEGIN
 check_table_count( 'TASKS', 1000);
 check_table_count( 'USER', 30);
 check_table_count( 'STATUS', 6); 
 check_table_count ( 'STATUS_TEXT', 12);
 --check_table_count( 'STATUS_TEXT', 13);
 END; 
END;

Test

If we run the above test library and have done everything correctly, we will not receive an error message. To demonstrate, I changed STATUS_TEXT the parameters for the table. See the following three illustrations for the three return tables in the test. The first table shows us that the test procedure was executed in the test library with the result FAILED:

Result of the test execution

In the second table we find the reason for the error. In our case, the number of records is not correct.

Error messages of test execution

The third table now returns the corresponding call stack.

Call stack of reported bugs

Further concepts

In addition to these basic concepts, there is also the possibility to use so-called configurations to create different preconditions for create the test procedures.

The test procedures, in turn, can divided into groups by a classification.

When calling SQLSCRIPT_RUN_TESTS_ON_ORIGINAL_DATA  the procedure, you can enter a JSON document that specifies in detail which procedures with which classifications should be executed in which configurations.

These details can be found in the reference documentation SAP HANA SQLScript Reference for SAP HANA Platform.

Review

With the Test Framework, we now have a very useful tool in our hands that allows us to automatically test our programs at the database level. Unit testing is now a widely used standard, which is also applicable to SQLScript developments. Test-driven development (TDD) is also possible with the framework.

This post is also available in: Deutsch

Fordern Sie weitere Informationen an

Wollen Sie mehr Informationen?

Unverbindliche Anfrage

Do you want more information?

Inquiry

%d bloggers like this: