🥇Unit tests in DB — how we do it at Sportmaster, part two

The first part — here.

Imagine the following situation. You are tasked with developing new functionality. You have some groundwork laid by your predecessors. If we assume you have no moral obligations, how would you proceed?

Most often, all previous work is forgotten, and everything starts from scratch. No one likes to dig through someone else's code, and if there’s time, why not create your own system? This is a typical approach, and it's largely correct. But in our project, we did not do so. We based our future automated testing system on the groundwork of unit tests on utPLSQL left by predecessors, and then we worked in several parallel directions.

Recovery of old unit tests. Recovery involves adapting the tests to the current state of the loyalty system and fitting the tests to utPLSQL standards.
Solving the problem of understanding what exactly, which methods and processes, we have covered with automated tests. We either need to keep this information in mind or draw conclusions directly from the code of the automated tests. Therefore, we decided to create a catalog. We assigned a unique mnemonic code to each automated test, formulated a description, and documented the settings (for example, under what conditions it should run, or what should happen if the test run fails). Essentially, we filled in the metadata about the automated tests and stored this metadata in the standard tables of the utPLSQL schema.
Defining the expansion strategy, i.e., choosing the functionality that will be covered by automated tests. We decided to focus on three things: new enhancements to the system, incidents from production, and key system processes. Thus, we evolve in parallel with the release, ensuring its higher quality while expanding the regression scope and providing system reliability in critical areas. The first such bottleneck was the process of distributing discounts and bonuses on invoices.
Naturally, we began developing new automated tests. One of the first release tasks was to evaluate the performance of predefined selections in the loyalty system. Our project includes a block of fixed SQL queries that select clients based on certain conditions. For example, to get a list of all clients whose last purchase was in a specific city, or a list of clients whose average purchase amount exceeds a certain value. By writing automated tests, we verified the predefined selections, established baseline performance parameters, and additionally conducted load testing.
Working with automated tests should be convenient.The two most common actions are running automated tests and creating test data. This led to the creation of two auxiliary modules in our system: a launch module and a data generation module.
The launch module is presented as a single universal procedure with one input text parameter. As a parameter, you can pass the mnemonic code of the automated test, the name of the package, the test name, the automated test configuration, or a reserved keyword. The procedure selects and launches all automated tests that meet the specified conditions.
The data generation module is presented as a package, in which a special procedure is created for each object of the test system (table in the database), which inserts data into it. This procedure maximally fills default values, enabling the creation of objects literally at the snap of a finger. To facilitate usage, templates for the created data were developed. For example, to create a client of a certain age with a test phone and a completed purchase.
Automated tests should be launched and operate within an acceptable time frame for your system. Therefore, a daily nighttime launch was organized, the results of which produce a report that is sent to the entire development team via corporate email. After restoring old automated tests and creating new ones, the total runtime was 30 minutes. This level of performance was satisfactory for everyone, as the launch occurred during off-hours.
However, we had to work on optimizing the speed of operation. The loyalty system update in production is done at night. During one of the releases, urgent changes had to be made overnight. A half-hour wait for the results of the automated tests at three in the morning did not make the release manager happy (a fiery hello to Alexey Vasyukov!), and many kind words were said about our system the next morning. In the end, a 5-minute standard for operation was established.
To enhance performance, we utilized two methods: automated tests began running in three parallel streams, which is quite convenient due to the architecture of our loyalty system. We also abandoned the approach where an automated test does not create its own test data but tries to find something suitable in the system. After the changes were made, the total execution time was reduced to 3-4 minutes.
The project with automated tests should be deployable on various environments. In the beginning, there were attempts to write our own batch scripts, but it became clear that a custom automated installation was a complete nightmare, and we turned to industrial solutions. Given that the project has a lot of actual code (primarily we store the automated test code) and very little data (the main data consists of metadata about the automated tests), the implementation of Liquibase in the project was quite simple.
It is a database-independent open-source library for tracking, managing, and applying database schema changes. It is managed via the command line or frameworks like Apache Maven. The operation principle of Liquibase is quite straightforward. We have a project organized in a certain way, consisting of changes or scripts that need to be applied to the target server, along with control files that specify the sequence and parameters for applying those changes.
At the database level, a special table is created where Liquibase stores the change log. Each modification has a calculated hash that is compared between the project and the database state each time. Thanks to Liquibase, we can easily apply changes to our system across any environment. Automated tests are now run on testing and release environments, as well as on containers (personal environments of developers).

So let's talk about the results of our unit testing system implementation.

First and foremost, we are convinced that we have begun to develop higher quality software. Automated tests are run daily and at each release, discovering dozens of bugs. Moreover, some of these bugs are only indirectly related to the functionality we actually wanted to change. There is considerable doubt that these bugs would have been found through manual testing.
The team has gained confidence that specific functionalities work correctly... This is primarily true for our critical processes. For instance, in the past six months, we have not encountered any issues with the distribution of discounts and bonuses on receipts, despite changes made in every release, whereas errors arose periodically in previous periods.
We have managed to reduce the number of testing iterations. Because automated tests are written for new functionality, analysts, who also act as testers, receive higher quality code, as it has already been verified.
Some of the automated testing developments are utilized by developers. For example, test data is generated using an object generation module in containers.
It's important to note that we have established a ‘acceptance’ of the automated testing system among developers. There is an understanding that it is important and beneficial. From my experience, I can say that this is far from the case. Automated tests need to be written, maintained, and developed, and their results analyzed, and often these time investments simply do not pay off. It is much easier to go to production and deal with the issues there. However, developers line up and request coverage of their functionality with automated tests.

What's Next

Let’s discuss the plans for the project's development in automated testing.

Certainly, as long as the Sportmaster loyalty system is alive and continues to evolve, we can endlessly develop automated tests. Hence, the main focus of development is to expand the area of coverage.

As the number of automated tests increases, the total execution time will inevitably grow, and we will have to revisit the question of performance. Most likely, the solution will lie in increasing the number of parallel threads.

However, these are obvious paths of development. If we speak about something less trivial, let's highlight the following:

Currently, the management of automated tests is performed at the DBMS level, meaning that knowledge of PL/SQL is required for effective work. If necessary, some administration interface can be implemented for managing the system (for instance, executing runs or creating metadata), utilizing Jenkins or something similar.
Everyone loves quantitative and qualitative metrics. For automated testing, a universal metric is Code Coverage or code coverage metric. With this indicator, we can determine what percentage of the code of our tested system is covered by automated tests. Since version 12.2, Oracle has provided capabilities to calculate this metric and recommends using the standard package DBMS_PLSQL_CODE_COVERAGE.
Our automated testing system is just over a year old, and perhaps now is the right time to assess the coverage. In my previous project (not a Sportmaster project), this is exactly what happened. A year after starting the automated tests, management tasked us with evaluating the percentage of code we cover. With coverage exceeding 1%, management would be happy. We, the developers, expected a result of around 10%. We integrated code coverage, measured it, and got 20%. Elated, we went for a bonus, but how we went about that and where we headed afterwards is quite another story.
Automated tests can validate the deployed web services. Oracle allows for this, and we will no longer encounter a series of issues.
And, of course, our automated testing system can be applied to another project. The solution we've developed is universal and simply requires the use of Oracle. I've heard that other projects at Sportmaster are interested in automated testing, and we might go to them.

Conclusions

Let's summarize. In the Sportmaster loyalty program project, we managed to implement an automated testing system. Its foundation is the utPLSQL solution by Steven Feuershtain. Surrounding utPLSQL is the code for the autotests and custom auxiliary modules: a launch module, data generation module, and others. The autotests run daily and, most importantly, they work and provide benefits. We are convinced that we have started to release software of higher quality. Moreover, the solution is universal and can be freely applied to any project that requires organizing automated testing on Oracle DB.

P.S. This article turned out to be rather vague: there's a lot of text and almost no technical examples. If the topic is of interest on a global scale, we are ready to continue and come back with a follow-up, where we'll discuss what has changed in the past six months and provide code examples.

Please leave comments if there are points that should be emphasized in the future or questions that need clarification.

Only registered users can participate in the survey. Please log in, please.