SQLScript Unit Tests, the End-User Test Framework
Veröffentlicht am 11. Mai 2020 von | News | SQLScript |
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:
In the second table we find the reason for the error. In our case, the number of records is not correct.
The third table now returns the corresponding call stack.
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.
