TDD for Oracle stored procedures

On one of our recent projects, we are faced with a serious problem. The web application that we developed was supposed to use the internal database of the financial organization. For security reasons, access was very limited: any changes needed to be made with the help of stored procedures, and data read only with the help of views. Thus, the application had to perform complex data manipulations, without having any idea about their structure. The main snag for us was that our application fell into dependence on large and complex procedures for which there were no automated tests.

Googling a bit, we found that in the regular Oracle SQL Developer toolkit [1] there is a functional for creating automated tests. We immediately began to study it. And although tests for the most complex procedure had to be created after it was written, this toolkit still helped us eliminate a few errors, and also greatly simplified the process of functional expansion and refactoring. Below I will give an example of using TDD to build stored procedures, and also share my experience in working with the toolkit.

Usage example

Suppose a customer has an existing application that allows his clients to send SMS messages. Another team is developing a new application that will have to work in parallel with the existing one, so it would be nice to have a common place for business logic.

Data structure

The application uses the following data structure:

CREATE TABLE CLIENTS( ID NUMBER GENERATED BY DEFAULT AS IDENTITY NOT NULL, NAME NVARCHAR2(255) NOT NULL, BALANCE NUMBER(*,2) DEFAULT 0 NOT NULL, IS_ACTIVE NUMBER(1) DEFAULT 0 NOT NULL, IS_PREPAY NUMBER(1) DEFAULT 0 NOT NULL ); CREATE TABLE MESSAGE_QUEUE( ID NUMBER GENERATED BY DEFAULT AS IDENTITY NOT NULL, ID_CLIENT NUMBER NOT NULL, SENDER VARCHAR2(20), RECIPIENT VARCHAR(20), MESSAGE NVARCHAR2(255) NOT NULL, QUEUED_ON TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP, SEND_ON TIMESTAMP WITH TIME ZONE NULL, SENT_ON TIMESTAMP WITH TIME ZONE NULL ); CREATE TABLE TRANSACTIONS( ID NUMBER GENERATED BY DEFAULT AS IDENTITY NOT NULL, ID_CLIENT NUMBER NOT NULL, VALUE NUMBER(*,2) NOT NULL, TRANSACTION_TIME TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP ); 

For brevity, the definitions of primary and foreign keys are omitted.

Setting up the environment

Unit testing in SQL Developer uses a database to store tests, their settings, library, and execution results. For this purpose, it is strongly recommended to create a user for testing, then create a repository in its database. This process is described in more detail in the unit testing documentation [2].

Oracle Testing Terminology

The testing terminology that Oracle uses is somewhat different from the common xUnit terminology [3]:

Further in the text I will use the Russian version of the terminology xUnit.

Surprises

Working with the application, we found that it does not always work as we expected:

• Sometimes, all the unit-testing menu items turned off. In such cases, click the menu item View → Unit Test
• All tests inside the script use a common set of settings and reset the context, which is quite logical. But because they are edited through the test tab, it creates the feeling that they can be personalized for each test separately.

Development by testing

Before we can start, you need to create an empty procedure, otherwise you will not be able to create a test. And although the argument list can be left empty, there is no need for that.

Initially, we can assume that in order to send a message, we will need a client identifier, a sender, a recipient, as well as the body of the message itself. Also, we need to signal the result of the execution, say, through the output parameter. With the help of the create procedure dialog you can get a perfectly suitable definition:

 CREATE OR REPLACE PROCEDURE QUEUE_MESSAGE( V_ID_CLIENT IN NUMBER, V_SENDER IN VARCHAR2, V_RECIPIENT IN VARCHAR2, V_MESSAGE IN NVARCHAR2, V_IS_QUEUED OUT NUMBER) AS BEGIN NULL; END QUEUE_MESSAGE; 

In the case of Oracle, it makes sense to set the prefix for variables whose name may coincide with the field name, as in the case of ambiguity, the famous DBMS will resolve the dispute in favor of the field. And in order to avoid confusion, it is easier to prefix all variables without exception.

Note

If the parameters of the procedure have changed, then each test script must be updated manually by clicking the Synchronize Test context menu item ...

The first script

To simplify our example, let's assume that the cost of one message is 0.03 some money. And, oddly enough, Gherkin is quite suitable for describing the scenario:

 :  -  :    :   ,       ,   ,    . 

The fastest way to create a test is to right-click on a procedure in the object tree, then select the menu item Create Unit Test .... In the window that appears, you can immediately click Finish . The QUEUE_MESSAGE script with a single test should appear in the Unit Test panel.

Context setting

First, we will need to fill the database with the necessary data. For us, the most convenient was the use of PL / SQL mode for setting and resetting the context. However, any of the options are easy to reuse using publish to the library. To copy an existing step from the library, just select it from the drop-down list, then click the Copy button. And if you want to use it without changes, but instead of the Copy button, you must click the Subscribe checkbox.

Caution!

The idea of ​​using an existing database for testing may seem attractive. It would seem that it saved the data in the configuration, and restored it when the context was reset ... However, it should be borne in mind that if an unexpected error occurred at any stage during the execution of the tests, the database will appear as it was during the error, and the context will not be reset. Therefore, it is best to use a clean database, which is not terrible and easy to completely re-create in case of damage to the structure or data.

Assuming that we are working with an empty database, for setting the context, we will need only one insert of the post-paid client record. It can be immediately stored in the library, calling the post-paid client .

Context reset

To be able to rerun the tests, you need to clear the added data. Odnadko, in our case, you can simply clear all the tables affected by the tests. This step also needs to be saved to the library for future use.

Call

The test execution itself is determined by setting the parameters of the stored procedure. The values ​​of the output parameters for verification are also set here. Checking the output parameters can be disabled using the Test Result checkbox. It refers to the parameters specified in the table as well as dynamically.

Caution!
')
In appearance, it may seem that setting the parameters with a mouse in the table is very convenient, however, you must bear in mind that this table is not copyable. This is especially important for procedures with a large number of arguments, since to create the next test, all of them will have to be re-set manually, especially when the new test differs from the current one only by one value. Dynamic query (Dynamic Value Query), in contrast to the table, can be saved in the library, and then can be either reused or copied.

As stated above, a dynamic query is more convenient to use. It is also worth noting that the name of the output parameters in the request must be supplemented with a $ sign at the end of the name:

 select 1 as V_ID_CLIENT, '79052222222' as V_SENDER, '79161111111' as V_RECIPIENT, ' !' AS V_MESSAGE, 1 as V_IS_QUEUED$ from DUAL 

Note

To return from the dynamic query mode to the table one, it is necessary to completely clear the dynamic query value.

Since we specified the check of the output parameter, it is already possible to run the script and see the failure. If done correctly, the system should report an error. Any other failure at this stage means incorrect configuration.

The easiest way to calm the test down is to write 1 into the output parameter in the procedure body: SELECT 1 INTO IS_QUEUED FROM DUAL;

Statements

The test is green again, but we have not checked all the necessary conditions. They can be checked in other tests of the same script. Before you create a new test, you should rename the existing one from the default "Test Implementation 1" to "Positive result", and the entire scenario - to "Active post-pay client sends a message."

Important

It is easy to assume that each test is performed inside a transaction. However, it turned out to be wrong. In the event of an unexpected error, the database may be in an undefined state. Expected errors this behavior does not apply.

Our next test will be placed in a separate test to get more subtle feedback, however, it is worth remembering that each new test will take time to set up and reset the context, and each test failure will be provided with a clear message about its cause. We will divide the checks according to different tests in this scenario, and then combine all the checks into one test in the next scenario.

Note

SQL Developer does not allow you to view two tests at the same time. When moving to another test in the tree, the current test is replaced by a new one in the same panel. In addition, it is impossible to split this panel into two independently scrollable areas. However, it is very convenient to open the source code of the procedure in parallel with the test window for a quick transition between two panels.

The following test should verify that the message has been queued. Since the setting and resetting the context are already specified, we need to use a dynamic query from the library, and set the approval check. After we have copied the dynamic query, it may seem that checking the already checked output parameter is useless, and you can reset the Test Result checkbox. However, if you run tests in this state, it will be seen that one of the tests is ignored. For me personally, the ignored test is a symbol of unfinished work, so the checkbox will have to be put in place.

There are several ways to validate assertions. The first item on the list is a boolean function. When creating a Boolean function, the dialog provides a quite suitable template:

 -- Please replace this code with either a boolean -- expression like this: -- RETURN FALSE; -- or else a code block which returns a boolean value -- similar to the following: DECLARE l_count NUMBER; BEGIN SELECT count(*) INTO l_count FROM dual; IF l_count <> 0 THEN RETURN TRUE; ELSE RETURN FALSE; END IF; END; 

For our validation, we can use this template by replacing dual with MESSAGE_QUEUE , then applying the necessary filters. The condition also has to be changed from l_count <> 0 to l_count = 1 for better accuracy. After that, you can safely save the function in the library for future use.

Note

All records in the library are saved according to their type. This means that if you later need to use, for example, an approval check, you will need to remember not only its name, but also the type. This can be very uncomfortable very quickly, especially in large projects.

When running the tests, we should see an error. It is very easy to fix:

  INSERT INTO MESSAGE_QUEUE(ID_CLIENT, SENDER, RECIPIENT, MESSAGE) VALUES(V_ID_CLIENT, V_SENDER, V_RECIPIENT, V_MESSAGE); 

Now you can make sure that all tests pass with success.

Note

When working with tests, the repository is blocked, so after work is finished, you must either close SQL Developer or close the repository (Deselect Repository).

And finally, let's check the transaction record. To do this, select the following type of validation - Compare Query Results. As the name implies, it works very simply: you need to specify two queries, the results of which match. Since the exact date and time cannot be known, you can be content with any value within 10 seconds:

 -- Source query SELECT 1 AS ID_CLIENT, 0.03 AS SUM_VALUE FROM DUAL -- Target query SELECT ID_CLIENT, SUM(VALUE) FROM TRANSACTIONS WHERE TRANSACTION_TIME BETWEEN CURRENT_TIMESTAMP AND (CURRENT_TIMESTAMP - 1/24/6) GROUP BY ID_CLIENT; 

After running the tests, we see a vague error Validation : Compare query results check found differences . Where "one recent transaction" is the name of our last check in the library. And although this option is already a valuable tool, it would be great if it could show what exactly the results differ from.

Add the necessary functionality to our procedure:

  INSERT INTO TRANSACTIONS(ID_CLIENT, VALUE) VALUES(V_ID_CLIENT, 0.03); 

Debugging

After the next test run, it suddenly turns out that the error has not gone away. You, probably, have already noticed an error in the code above, however, in real conditions, the situations are much more complicated. Since the tool does not show the difference, you will have to figure out the cause manually. Unfortunately, SQL Developer's debugging functionality is not able to help here. This means that we will have to run the test without performing a reset. To do this, you can create another script - debug. Or rather two: one - without a reset, but with the same dynamic query as in the non-working test - in order to understand what is going on; and the second - without setting the context, but with a reset - to remove after the first.

After running the first script, you can view the contents of the table, and check with the verification request. Now it is clearly visible that the problem was precisely the verification request. Do not forget to run the second script to clear the data, correct the test conditions, and arrange a rerun. Now everything is all right. Debugging scripts can be left for the future, and the first completed script can be put into a new test suite.

Second script

Now that we have a successful message sending script, we can try a failed send script. For example, when a post-paid client is inactive:

 :  -  :    :   ,    ,     . 

You need to create a new script. We also have to tweak the context setting and dynamic query slightly, but this is much easier than creating new ones from scratch.

To set the context, copy the PL / SQL step "Active post-pay client", in which we replace 1 with 0 and publish it in a library called "Inactive post-pay client". Repeat the same for a dynamic query, calling the new query "Unsent message." To reset the context, use the existing step.

After running the test should show an error. It is very easy to fix. Replace SELECT 1 INTO V_IS_QUEUED FROM DUAL with SELECT IS_ACTIVE INTO V_IS_QUEUED FROM CLIENTS WHERE ID=V_ID_CLIENT - and everything works again.

Then you need to check that the transaction is not saved. To do this, use the following type of verification - comparison tables (Compare tables). At first, it may seem that there is nothing to compare with, however, in the context setting, it is possible to copy an existing table into a temporary one. This is perfect for us - you can copy transactions into a temporary table, and after calling the procedure, compare the results. The main thing - do not forget to delete this table when you reset the context. There are two options - restore, then delete, and just delete. Since we have nothing to restore - choose the second option. Please note that as in the case of query comparison, the only feedback option is whether there is a match or not.

After admiring the error after running the tests, you can think about the solution. For example, you can wrap an insert into a condition using the newly updated V_IS_QUEUED:

 IF V_IS_QUEUED = 1 THEN INSERT INTO TRANSACTIONS (ID_CLIENT, VALUE) VALUES (V_ID_CLIENT, 0.03); END IF; 

We compile the procedure, run the tests - everything works.

Finally, we need to check that the message queue has remained unchanged. And although the hands were itching to immediately place the message insertion inside the condition next to the insertion of the transaction, this would be an encouragement to a breach of discipline. Therefore, we first create an additional check for this statement. The next type of check is a query that returns no rows (Query returning no rows). Since we completely clear all the data after each test, it suffices to specify SELECT * FROM MESSAGE_QUEUE as such a query.

The test run shows an error that we can easily fix by putting the inset inside the condition. And this ends our second scenario.

findings

SQL Developer can be used to develop stored procedures using the TDD method. Despite numerous shortcomings, this package provides a platform for developing stored procedures, allowing developers to easily and confidently change and extend the functionality of existing procedures.

Unfortunately, a test repository can be created only in Oracle DBMS. In addition, attempts to use third-party DBMSs such as PostgreSQL or even MySQL as a database for testing end in the failure of the test subsystem. It also turned out that the use of SQL Developer in continuous integration systems causes a lot of problems, but this is another story.

[1] Oracle SQL Developer (Eng.) - http://www.oracle.com/technetwork/developer-tools/sql-developer/overview/index.html
[2] Oracle SQL Developer Help: Unit Testing Repository (English) - https://docs.oracle.com/cd/E15846_01/doc.21/e15222/unit_testing.htm#RPTUG45067
[3] xUnit - https://ru.wikipedia.org/wiki/XUnit