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.
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.
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.
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.
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|
While working with the application, we have discovered that sometimes the application did not behave as we would expect:
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.
If the signature of the procedure is changed, the Synchronize Test… menu option should be run for every relevant test.
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.
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.
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.
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.
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.
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
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;.
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”.
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.
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.
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:
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.
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:
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);
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.
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
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.
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.