Unit Tests in DBMS — How We Do It at Sportmaster, Part One

Hello, Habr!

My name is Maxim Ponomarenko, and I am a developer at Sportmaster. I have 10 years of experience in the IT field. I started my career in manual testing and then switched to database development. For the last 4 years, by accumulating knowledge gained from testing and development, I have been engaged in test automation at the DBMS level.

I have been part of the Sportmaster team for just over a year, and on one of the major projects, I am developing automated testing. In April, my colleagues from Sportmaster Lab and I spoke at a conference in Krasnodar; my talk was titled "Unit Tests in DBMS," and I would like to share it with you now. There will be a lot of text, so I decided to break the presentation into two posts. In the first, we will discuss automated tests and testing in general, while in the second, I will elaborate on our unit testing system and the results of its application.

First, a bit of dull theory. What is automated testing? It is testing conducted using software tools, and in modern IT, it is increasingly used in software development. This is related to the fact that companies grow, their information systems expand, and correspondingly, the amount of functionality that needs testing also increases. Conducting manual testing becomes more and more unmanageable.

I worked at a large company where releases occurred every two months. A whole month was spent on a dozen testers manually verifying the functionality. Thanks to the implementation of automation by a small team of developers, we managed to cut testing time down to 2 weeks over one and a half years. We not only increased the speed of testing but also improved its quality. Automated tests are run regularly, and they always perform the entire suite of checks embedded in them, effectively eliminating the human factor.

In modern IT, it is typical for a developer to be required not only to write the product code but also to write unit tests that verify that code.

But what should you do if your system is primarily based on server logic? There is no universal solution or best practices available in the market. Typically, companies address this challenge by creating their own custom testing systems. A custom automated testing system was developed for our project, and I will discuss it in my presentation.

Testing Loyalty

First, let's talk about the project where we implemented the automated testing system. Our project is the loyalty system of Sportmaster (by the way, we have already written about it in this post).

If your company is large enough, your loyalty system will have three standard characteristics:

• Your system will be highly loaded.
• Your system will include complex computational processes.
• Your system will be actively developed.

Let's go step by step... In total, considering all Sportmaster brands, we have over 1,000 stores in Russia, Ukraine, China, Kazakhstan, and Belarus. These stores make approximately 300,000 purchases daily. This means that every second, 3-4 receipts enter our system. Naturally, our loyalty system is highly loaded. And since it is being actively used, we must provide the highest quality standards, because any software error can lead to significant financial, reputational, and other losses.

At the same time, over a hundred different promotions are running at Sportmaster. The promotions vary widely: some are product-related, some are tied to specific days of the week, some are linked to particular stores, and there are promotions based on purchase amount or quantity of items. It's quite substantial. Customers have bonuses and promo codes that are used during purchases. All of this makes calculating any order a rather non-trivial task.

The algorithm that handles order processing is truly terrible and complex. Any changes made to this algorithm are quite risky. It turns out that even the smallest seemingly insignificant changes can lead to quite unpredictable effects. Such complex computational processes, especially those implementing critical functionality, are indeed the best candidates for automation. Manually checking dozens of identical cases is very time-consuming. Given that the entry point into the process remains unchanged, once described, automated tests can be quickly generated, ensuring the functionality works correctly.

Since our system is actively used, businesses will expect something new from you, to keep up with the times and be client-oriented. In our loyalty system, releases come out every two months. Therefore, every two months, we need to conduct a full regression of the entire system. Naturally, like in any modern IT environment, development does not go directly from the developer to production. It starts on the developer’s outline, then sequentially moves through the testing stage, release stage, acceptance, and only then ends up in production. At the very least, we need a full regression of the entire system on both the testing and release outlines.

The properties described are standard for almost any loyalty system. Let's talk about the specifics of our project.

Technologically, 90% of the logic of our loyalty system is server-based and implemented on Oracle. There is a client implemented in Delphi that performs the function of the ARM-administrator. Web services are also established for external applications (for example, a website). Therefore, it is quite logical that if we are going to implement an automated testing system, we will do so on Oracle.

The loyalty system at Sportmaster has been in place for over 7 years and was created by a few individual developers… The average number of developers on our project over these 7 years was 3-4 people. However, in the past year, our team has significantly grown, and now 10 people are working on the project. This means that new people are joining the project who are not familiar with standard tasks, processes, and architecture. There is an increased risk that we will overlook errors.

The project is characterized by the absence of dedicated testers as full-time units. Testing certainly exists, but it is performed by analysts alongside their other primary responsibilities: communicating with business clients, users, and working through system requirements, etc. Despite the fact that testing is conducted very thoroughly (especially worth mentioning since this report may be seen by some analysts), the effectiveness of specialization and concentration on a single task is paramount.

Considering all of the above, to enhance the quality of the delivered product and reduce development time, the idea of automating testing in the project seems quite logical. At various stages of the loyalty system's existence, individual developers have made efforts to cover their code with unit tests. This was overall a rather fragmented process where each person used their architecture and methods. What was common among the unit tests was the final result: tests were developed, used for some time, stored in a versioned file repository, but at some point, they ceased to run and were forgotten. This primarily happened because the tests were more tied to specific implementers rather than to the project.

utPLSQL comes to the rescue

Do you know anything about Steven Feirershtein?

This is a smart guy who devoted a significant part of his career to working with Oracle and PL/SQL, writing a substantial number of works on the subject. One of his well-known books is titled: 'Oracle PL/SQL. For Professionals'. It is Steven who developed the solution utPLSQL, which stands for Unit Testing framework for Oracle PL/SQL. The utPLSQL solution was created in 2016, but it continues to be actively worked on, with new versions being released. At the time of the report, the latest version is dated March 24, 2019.
So, what is this? It is a separate open-source project. It weighs a couple of megabytes including examples and documentation. Physically, it represents a separate schema in the ORACLE database with a set of packages and tables for organizing unit testing. Installation takes a few seconds. A distinctive feature of utPLSQL is its ease of use.
Globally, utPLSQL acts as a mechanism for running unit tests, where a unit test is understood as ordinary Oracle package procedures, the organization of which follows certain rules. In addition to execution, utPLSQL keeps a log of all your test runs and has an internal reporting system.

Let's look at an example of how the code for a unit test, implemented according to this methodology, looks.

So, the screen displays the code of a typical package specification with unit tests. What are the mandatory requirements? The package must have the prefix 'utp_'. The same prefix must be applied to all test procedures. The package must include two standard procedures: 'utp_setup' and 'utp_teardown'. The first procedure is called before each unit test, while the second one is called after execution.

'utp_setup' typically prepares our system for running the unit test, for example, by creating test data. 'utp_teardown' does the opposite, reverting everything to the original settings and resetting the execution results.

Here is an example of the simplest unit test that verifies the normalization of a client's phone number to the standard format for our loyalty system. There are no mandatory standards on how to write procedures with unit tests. Typically, a method of the system under test is called, and the result returned by this method is compared to a benchmark. It is important that the comparison between the benchmark result and the obtained result is carried out using standard utPLSQL methods.

A unit test can contain any number of checks. As seen in the example, we make four consecutive calls to the method under test for normalizing the phone number, and after each call, we evaluate the result. When developing a unit test, it is important to consider that there are checks that do not affect the system at all, while some require reverting to the system's initial state.
For example, in the presented unit test, we simply format the input phone number, which does not affect the loyalty system in any way.

However, if we are writing unit tests for creating a new client, a new client will be created in the system after each check, which can impact subsequent test runs.

This is how unit tests are executed. There are two acceptable ways to run them: running all unit tests within a specific package or running a specific unit test in a particular package.

Here is an example of an internal reporting system. As a result of the unit test, utPLSQL generates a small report. In it, we see the result of each specific check and the overall result of the unit test execution.

6 Rules of Automated Tests

Before starting to create a new automated testing system for the loyalty system, we, together with management, established the principles that our future automated tests should adhere to.

1. Automated tests must be effective and beneficial. We have amazing developers who deserve mention, as some of them will surely see this report, and they write excellent code. However, even their great code is not perfect and has, contains, and will continue to have bugs. Automated tests are required to find these bugs. If they do not, then either we are writing poor automated tests, or we have entered a dead area that is simply not being developed further. In both cases, we are doing something wrong, and our approach is simply meaningless.
2. Automated tests must be utilized. It's pointless to spend a lot of time and effort writing a software product, set up its repository, and then forget about it. Tests should be run, and as regularly as possible.
3. Automated tests must work reliably. Regardless of the time of day, the testing environment, and other system settings, test runs should yield the same result each time. Typically, this is ensured by using special test data with fixed system configurations.
4. Automated tests must operate at a speed acceptable for your project. This time is determined individually for each system. Some projects can afford to run tests all day, while for others, meeting a deadline of seconds is critical. I will discuss the speed standards we achieved in our project a little later.
5. The development of automated tests must be flexible. It is undesirable to abandon testing any functionality simply because we have not done it that way before or for some other conviction. utPLSQL imposes no restrictions on development, and Oracle generally allows the implementation of a wide range of things. Most tasks have solutions; the only question is time and effort expended.
6. Deployability. We have several environments where test execution is needed. At any moment, a data dump can be updated on any of the environments. The project with automated tests should be managed in such a way that a full or partial installation can be carried out without issues.

In the second post in a couple of days, I will share what we have done and what results we achieved.

Source: habr.com