Database Testing Developer Version

For several years I have been doing database programming and I see the same amount of difficulty in testing the functionality implemented in the database itself. Such a task requires from the tester not only knowledge of SQL, but, at times, and an understanding of the subtleties of the test functionality. And developers do not always strive to describe their decision in detail.

It became interesting how the database logic is being tested. I think it is not surprising that at that time searches on the Internet were unsuccessful. Either because of insufficient perseverance, or because of the desire to do "the same, but its own, other." And the options are not satisfied because of the complexity of use. For example, there were also clr-assemblies with unit tests. About HP QTP found out later.

And this activity led to the creation of a test constructor, embodied in the plug-in to eclipse for testing the base. The article will describe the basics of working with the designer.
')
I do not want a theory, I want the result immediately!

Theory

The algorithm is based on the principles:

• the test consists of a sequence of steps (simple sql scripts)
• a step is a single operation that accepts some input data and returns others
• steps can be combined into a sequence to create a more complex module
• less code when creating a test, more mouse work
• tests will be stored in the database

And how I, being a tester, would like to create tests:

1. I want to write a simple script once, give it a name, set parameters, and save it for reuse. And let the program itself find the parameters from the text of the script. And I’ll give them the data type and direction - input is a parameter or output.
2. I want to combine several scripts into one component . Suppose you need to put a record in the database, wait for its processing and get the result of this processing. A component is the basic logical unit for creating a test.
3. And let the components can be combined into the test itself. The one who will check this or that business transaction.

Well, that’s understandable. And where is the test? After all, the test result is a comparison of expected and actual results. Therefore, both the component and the test store a context for their parameters (in more detail and with an example below), which both indicate the expected or reference value. In this case, the parameters must be with the same name. And, when a change in the value of such a parameter occurs in the work of a component or test, a check for equality or inequality of these values ​​should be performed. If the test fails, the entire test fails.

What came out of it

1. Installation and Setup
2. Project creation
3. Script creation
4. Combining scripts into a component
5. Creating a dough from components

Installation and Setup

The plugin can work with MS SQL Server.

1. link to the plugin for eclipse and the sources at the end of the article. We put the plugin
2. create an empty database and run a script from the repository on it (database \ script.sql)
3. We set 2 bases in settings - for storing tests and for executing tests (Window \ Preferences \ SQL test settings)

Project creation

Script creation

For the order, through the context menu on the "Scripts" section, create an "Example" folder, in which our examples will be located. And create a script “Get server time” through the context menu on the “Example” folder, which will return the date and time of the server. Open the folder "Example" and, double click, open the script. Enter this script text and click on the "Find Parameters" button.

SELECT GETDATE() AS [:NOW] 

Parameters are by signature : param . And to get the value from the script, return the DataSet with the column name - the parameter name [: param] .

Select parameter type DATE and direction "Output". Save the script and wait near the button "Run". The result will be displayed in the “Test Value” column:


This is a simple example. Now let's complicate the task. We have to wait for 5 seconds in the test, and then make sure that we performed for exactly 5 seconds. This is quite an artificial task, but to understand the possibilities just right.

To solve the problem we have to make several scripts, then to combine them into a component. These scripts are:

1. Set the reference interval to 5 seconds
2. Get server time # 1
3. Wait for 5 seconds
4. Get server time # 2
5. Compare times and make sure the difference is 5 seconds.

For (2) and (4) steps, we already have a script. (3) Lastly, as the most difficult. We will not use simple WAITFOR DELAY, since it can be any lengthy operation that can be completed at any time, and not after a fixed interval.

(1) may look quite simple. The only output parameter, type INTEGER:

 SELECT 5 as [:interval] 

And (5) is also not difficult. The first two parameters will be input, of type DATE. The third - day off, type INTEGER:

 SELECT DATEDIFF(SECOND, :from, :to) as [:interval] 

It was the turn (3) of the script, the task of which is to work out quickly and report whether the condition was fulfilled and you can go further, or repeat its execution a little later.

 SELECT 1 as [:ret] WHERE DATEDIFF(SECOND, :from, GETDATE()) >= :interval 

This script in " ret " will return one if the number of seconds since: from the current time is not less than the “interval” parameter, and returns nothing otherwise. This is what we will check.

Select the “Event” script type, select “ret” in the “Parameter” drop-down list, and enter 1 in the “Parameter Value” field. The program will execute a script of this type until “ret” becomes equal to 1. The restart will be 100ms after completion.


Here we come to the union of the created scripts into a component, in which the check for equality of the expected and actual intervals will be performed.

Combining scripts into a component

By analogy with the creation of scripts, we will create and open the “Check interval” component. Drag and drop the component window with the mouse from the list of scripts (in the Project Explorer) just created scripts into the "Scripts" area. If you have transferred something extra, click the mouse and click the "Delete" button. I got this sequence of scripts:

1. Set the reference interval
2. Get server time
3. Wait until the timeout is complete
4. Get server time
5. Get the time difference

In the "Parameters" section of the open component, add 4 new parameters:

Parameter	Context	Comment
interval	Reference	Expected value. Will be obtained from (1) script
interval	Test	Actual value Will be obtained from (5) script
start	Local	The start time of the test. Will be obtained from (2) script
finish	Local	Test completion time. Will be obtained from (4) script

It remains to set the correspondence between the parameters of the component and the transferred scripts. To do this, click on the first script "Set reference interval" in the list of scripts. In the “Script Parameters” table, select the “interval” parameter and, through the context menu of the mouse, set the connection with the component parameter: Standard \ interval.


Save the component. I got this result.

Before launching, the component should be saved, since the program loads it from the database before launching it. Click the "Run" button in the component window. A new window will open with the test result if it has not been opened before.


This result displays all the data on the component: what scripts were executed, what data came in the input and turned out at the output. If there was a comparison of the parameters, then, as in our case, it is also shown.

Let's try to “break” the test and see how it appears. Change the "Get the time difference" script to

 SELECT DATEDIFF(SECOND, :from, :to) + 1 as [:interval] 

Return 1 more than it should be. Save the script and run the component again.


Do not forget to return the correct value of the query "Get the difference in time."

Creating a dough from components

The test is built from components in the same way as a component from scripts. Therefore, I will not describe it in detail. Let's make the test “Check intervals” and drag the “Check interval” component into it 2 times. Save and run the test.

Conclusion

This article describes the simplest, artificial script for the test base. But there is nothing difficult in real-world scenarios, where it is necessary to save data to the database, wait for their processing, and get the result by comparing with the standard. I wondered if it would be possible to remove the manual labor routine when creating and executing tests in such a very specific area. I think this problem was partially solved. And for me it was the experience of getting to know java and eclipse plugin development. Let this and one more bike in the universal bike park.

Links

GoogleDrive: Plugin for eclipse (tested on eclipse 4.5)
Plugin project on github