Pivotal Engineering Journal

Technical articles from Pivotal engineers.

SQL Test Driven Development with Oracle RDBMS

Test-driving SQL stored procedures using Oracle SQL Developer IDE.

Posted on by
Categories:   SQL    TDD    Oracle   
Edit this post on GitHub.

On one of our recent projects we were facing a serious dilemma. The web application we have been building had to utilise an internal database of a financial institution. For security purposes, access was highly restricted, and the application was only allowed to insert or update data via stored procedures, and to read data using views. The application was completely ignorant of the data structures used by the financial applications, but it had to trigger a number of complicated data manipulations. The main concern on the team was that we were relying on big and complicated stored procedures that haven’t been tested.

After some googling, we have discovered that there is some unit testing functionality in the Oracle SQL Developer tool, which we have started to investigate immediately. And although we had to create the tests after the most complicated procedure had already been built, we still succeeded in eliminating a number of bugs, and felt confident while extending the functionality.

Example

For the purpose of this exercise, let’s assume that we have a legacy application that sends mobile messages on behalf of a client. Another team is writing a new application that will run in parallel with the legacy one, and it would be nice to have a common space for the application logic.

Initial data structure

Below is the definition for the relevant data schema:

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
);

The definitions for the primary and foreign keys have been omitted here for brevity.

Set up the environment

Unit testing is a feature in Oracle SQL Developer that uses the database to store the test harness infrastructure, test definitions and results. In order to use it, creating a test user is highly recommended, then a test repository has to be created in that user’s database. This process is explained in more detail in the Unit testing documentation.

Oracle testing terminology

The terminology used in Oracle SQL Developer is slightly different from the commonly used xUnit terminology:

xUnit SQL Developer Comments for SQL Developer
Test Suite Test Suite Can include other tests as well as test suites
Testcase Test Can only test a single function or procedure
Test method Test Implementation
Fixture setup Startup Process Available on the Test or Test Suite Level
Fixture teardown Teardown process same as above

Unexpected behaviour

While working with the application, we have discovered that sometimes the application did not behave as we would expect:

  • Sometimes, all unit testing options in the menu are unavailable until View→Unit Test menu item is clicked.
  • All test implementations share the same setup and teardown steps, which is not unexpected. However, since they can only be edited through the test implementation UI, it might seem that they are specific to the implementation, which they are not.

Test driving

Before we can start, we will have to define the skeleton of the procedure. It would be impossible otherwise to create a test case. And although it’s possible to leave the arguments out in the beginning, it does not really make sense to do so.

Initially, we can assume, that in order to send a message, we would need to supply the client id, sender, receiver, and the body of the message to send. Additionally, we would need to communicate the result of the operation using an out parameter. The following definition is a result of using the Create Procedure dialog, which suites perfectly:

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;

It’s a good idea to prefix the parameter names or variables that may match the column names to avoid ambiguity which Oracle seems to resolve silently in favor of the column. Having only some parameters prefixed doesn’t look nice, so it’s better to prefix all of them.

Note

If the signature of the procedure is changed, the Synchronize Test… menu option should be run for every relevant test.

Creating the first test case

To simplify our example, let’s assume that the cost of a message is fixed and is equal to 0.03. Although it might feel awkward, we can still use Gherkin to define the use case:

Given:
    There is an active bill pay customer

When:
    He submits the message to the queue

Then:
    The success is indicated,
    and the cost of the message is recorded in transactions,
    and the message is added to the queue.

The quickest way to create the test would be to right-click the procedure in the object tree and select Create Unit Test…, then to click Finish in the following dialog. The QUEUE_MESSAGE test with a single implementation should appear in the list of tests in the Unit Test panel.

Setup

First we would need to shape our pre-conditions. We have discovered that in most cases arbitrary Pl/Sql code steps worked better than other options to define the startup and teardown steps.

However, any type of step they can be easily duplicated by publishing it to the library. In order to make a copy, the user has to create a step of the same type, pick the appropriate item from the dropdown, then click the copy button. To reuse an item, one should click the subscribe checkbox instead.

Caution

It might be tempting to run the tests against a database that may be used by other users, and to copy the contents of the messages into temp, and purge the data, planning to restore the data during teardown. Unfortunately, the teardown process is not guaranteed to run if an exception occurs at any stage during test execution. Naturally, is better to run the tests against a fresh database whenever possible.

As we assume to be working with a clean database, we should create only one insert for the active bill pay customer. It would be handy to publish that insert to the library under the name Active bill pay customer.

Teardown

In order to be able to re-run the tests, we should also take care of removing the created entries on teardown as well. However, for our example, it would be easier to truncate all the tables that our tests are affecting, and put this action into a single reusable teardown step.

Action

The action is defined by supplying input values for the procedure. Incidentally, the output values are also specified here, and equality against the specified values will be asserted. The assertion can be skipped un-checking the Test Result checkbox. This checkbox applies both to the table entries and the Dynamic Value Query.

Caution

Argument table

Although it might seem convenient to supply the values directly in the table, beware that there is no way to copy that table into another implementation. This is especially important when the procedure under test has more than a few arguments, as there would be use cases that differ only by a single value. In contrast, the Dynamic Value Query can be saved to the library and re-used.

As outlined above, it’s better to use the Dynamic Value Query. It’s worth noting that the value name specified to validate the out parameters is the same as the parameter name, but with a $ at the end:

select 1 as V_ID_CLIENT, '353832223344' as V_SENDER, '353831112233' as V_RECIPIENT,
   'Let''s hangout in a cafè!' AS V_MESSAGE, 1 as V_IS_QUEUED$ from DUAL

Note

To revert from Dynamic Value Query back to regular table input, you have to set the Dynamic Value Query to a blank value.

Because we have had to set the expectation for the value of the out parameter, we can run the test case and observe a failure. If everything is correct, the system should report the error. Any other failure at this point should indicate a misconfiguration.

The easiest way to make this test to pass is to set the body of the stored procedure to SELECT 1 INTO IS_QUEUED FROM DUAL;.

Assertions

The test should now be green, but we haven’t specified all the assertions yet. These can be specified in different implementations of the same test. Moving on to our next condition, it looks like a good point for renaming both the test case and the default implementation. The test case name should reflect our use case, while the implementation should reflect the validation. For example, the test case can be called, “Active bill pay customer queues message”, while the implementation can be called “Success indication”.

Important

It’s easy to assume that the test cases or test instances would be running within a transaction, however, that turned out not to be the case. If an unexpected exception will occur somewhere, the database might be left in an unknown state. This isn’t true, however, for expected exceptions.

Our next verification can be placed into another implementation for the sake of feedback granularity, however, it is important to keep in mind that each implementation will spend extra time for setup and teardown, and every validation failure is usually accompanied with a clear error message. We’ll add separate implementations for this test case, and group them in a single implementation for the next use case.

Note

It is impossible to view two test implementations at the same time. When navigating to a new implementation, the pane with the previous one is replaced by the new one, and it’s impossible to split the pane. It is, however, possible and very convenient to have the stored procedure open in a separate tab group side by side with the test.

Next implementation should check that a message has been placed in the queue. The new implementation already shares the setup and teardown, so we’ll have to specify the value query and an expectation. After specifying the Value query, it makes sense to un-check the Test Result checkbox next to the out parameter, as this check is already performed in another implementation. There are several options available for validating the results. The boolean function is the first on the list. When creating a boolean function, the dialog displays a clear template snippet with an explanation:

-- 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 the snippet by modifying the select statement to take record from the MESSAGE_QUEUE applying the relevant filters, and changing the comparison from l_count <> 0 to l_count = 1. And make sure to save the function to the library for later use.

Note

All the snippets in the library are stored by type. Which means that if you need to recall a validation, you should remember it’s type. This might quickly grow out of hand especially for big test suites.

Run the tests, and sure enough, the validation is failing. Note yellow triangle next to the out parameter - it indicates a skipped test:

Skipped result check

I personally don’t like to have ignored tests in the output – to me this is an indication of incomplete work. So let’s switch the checkbox back on and re-run the tests.

Note

Working on a test case locks it up preventing other users from accessing it, so make sure to close the UI or de-select the test repository when not working with it.

Now the only problem in the output is the failure related to the absence of the queued message. Let’s implement that functionality:

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

And watch our tests pass:

Successful run

Finally, we must check that the transaction has been recorded. To do that we can try the next type of validation which is Compare query results. It’s very straightforward - all we have to do is to define two queries that should have matching results. As it’s impossible to know the timestamp exactly, we can accept any timestamp within 10 seconds from now:

-- 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 can see an obscure error message - Validation One recent transaction failed: Compare query results check found differences. where One recent transaction is the name of the validation in the library. Although this in itself is already a valuable tool, it would benefit significantly from being able to display a difference between the query results, rather than just stating the failure.

Let’s implement the functionality:

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

Troubleshooting

When we run the tests, they didn’t seem to be passing. You might have spotted an error in our condition above, however, in real production conditions might be much more complicated than that. As the application doesn’t have a diff feature, we’ll have to run the test without teardown to figure out what’s going on - it’s impossible to disable other implementations. This means we need to create a separate test case for debugging purposes, as the test debugging functionality of SQL Developer doesn’t help us here either.

Because the test runs are not transactional, we can create a new test case with only the setup section filled in and repeat the value query. Then we should be able to check how our validation query work. Sure enough, we can now see that the timestamps in the between clause should be swapped around, and also, the result of the sum should be named to match the source query.

We can leave the debugging test for future use, and can also add another test with our cleanup teardown and a blank implementation for cleanup purposes. Now we can see that our tests are passing. At this point we can add our first test case to a brand new test suite.

Second use case

Now that we have the message queuing working, we can try a use case when the message is not queued. For example, when the bill pay user is marked inactive:

Given:
    There is an inactive bill pay user

When:
    He sends a message
    
Then:
    Failure is indicated,
    and no transactions are recorded,
    and nothing is added to the message queue.

We’ll have to add a new test. We will also have to slightly modify our startup and the value query for calling the procedure. It’s simpler than creating one from scratch.

For the startup, we create a new Pl/Sql Code step, choose ‘Active bill client’ from the list, then click ‘Copy’. The edit box is now populated with the code from the step, but it’s available for independent editing. We replace 1 with 0 and publish to library as ‘Inactive bill pay client’, and repeat the same steps for the value query, naming the new query ‘Rejected message’.

For the teardown, we create a Pl/Sql Code step, select ‘Cleanup’ from the list, and tick the ‘Subscribe’ checkbox.

We can run the test and watch it fail. This one is really easy to fix: we simply replace our SELECT 1 INTO V_IS_SUCCESS FROM DUAL with SELECT IS_ACTIVE INTO V_IS_SUCCESS FROM CLIENTS WHERE ID=V_ID_CLIENT and re-run the tests to confirm that we’re back to green.

Then we have to make sure that no transactions are recorded. The next verification option is to compare tables, but at first it seems that we don’t have any tables to compare with. Fortunately, we can add a startup step to copy the contents of our transactions table into a temp table. Then we could verify that the tables are still identical after the test run. This, of course, also means that we’ll need a corresponding action to destroy the table once the test is done. We have two choices there - either use the ‘Table or row restore’ which has the option to drop the temp table after restore is complete, or just drop the table. Since we don’t need to restore anything, we can drop the table. Please note, that like with the query result comparison, the only feedback available is match or no match.

After running the tests and observing the failure, we can start thinking about the solution. One way of solving the problem could be to create an IF statement using our newly updated output variable, and to wrap the insert statement in question with it:

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

Compile the updated procedure, and watch the test pass.

Finally, we’ll need to verify that the message queue remains empty. Although it was tempting to drag the other insert inside the if statement and call it a day, such action would encourage discipline violation. So we’ll create a separate assertion for this condition. We’ll turn to the next verification option, which is surprisingly handy for this case. It is the ‘Query returning no row(s)’ verification. Since we know that all the data is cleared every time, it’s sufficient to specify the generic SELECT * FROM MESSAGE_QUEUE as our query in this case.

Running the test at this stage should demonstrate a failure. This can be fixed by dragging the insert statement inside the if block we have already created previously. And the second use case is now complete.

Conclusion

The use of Oracle SQL Developer has demonstrated the ability to use TDD for developing SQL procedures and functions. Despite the drawbacks, it provided a solid ground for developing SQL procedures and functions, enabling to refactor the code and adding or changing functionality with confidence.

Unfortunately, the test suite repository can only be installed in an Oracle database, and also, when a 3rd party database, such as PostgreSQL or MySQL is selected as the tested database, the testing tools crash. And, as it turned out, integrating SQL Developer with CI proved to be far from trivial, but that is a topic for a whole different story.