How to reduce the time of manual testing by 3 times using an SQL script and make life easier for colleagues

If your system uses a database and needs test records from time to time, or if you’re doing inserts with multiple values, then the following might come in handy.

Search or create test records?

If you have a database and you are developing algorithms that select records from one or more tables according to certain criteria, then at the development stage you need test data that meets the given conditions.
How to get them?
The first thought – to find among already available.
But, the more conditions, the more problems when searching.
Let’s say we found it. The developer and tester need many options for a dataset. You can take a few records and update them, but wouldn’t that hurt someone else? Will some of the data crash for some reason? And what will happen to these records in a few months, when you need to double-check something?
In practice, more than once faced with the worst answers to such questions. How can this be avoided?

If we know in which table which records are needed, then the most logical thing is to do inserts with the necessary values, taking into account all the links.
Yes, it looks complicated and resource-intensive, but this is only at first glance.
Since we are starting to understand the records of a particular table, we still have to find out: which fields are required in it, which are associated with other tables, which can take values, etc.
All this will help to write a script that is unlikely to fail due to changes in the database.
In addition, one person will understand and write the script once, and it will be possible to use it many times and to anyone.
Variables, loops, conditional expressions and subqueries will allow you to create a flexible, easy to read and short script that generates a ready-made data set.

The main idea is to get a complete set of test data for one or more cases by running the script.

Let’s take an example

I will show you on Transact-SQL for MS SQL Server (it will be similar on PostgreSQL).
Let’s say there is a Trades table.
We are interested in the data for yesterday.
Let’s create a few:

Declare
    @i int,
    @n int = 5, -- сколько будет записей
    @TradeDate datetime = dateadd(day, -1, getdate()),
    @Market_id int = (select id from MarketDic where Code="Нужный"),
    @Currency_id int = (select top 1 id from CurrencyDic);
 
set @i = 1;
while (@i <= @n)
Begin
    insert into Trades (ID, TradeDate, AccountName, DealType, Market_id, Currency_id) values (
        (select max(id) + 1 from Trades), -- id
        @TradeDate, -- TradeDate
        concat('Имя_', @i), -- AccountName
        Case -- DealType
            when @i % 2 = 0 then 'Buy'
            else 'Sell'
        end,
        @Market_id, -- Market_id
        @Currency_id -- Currency_id
    );
    Set @i = @i + 1;
End;

What’s happening:

1. We declare variables:

   • i – for loop

   • n – desired number of records

   • TradeDate – yesterday, obtained by subtracting one day from the current date-time

   • Market_id is a field associated with some other MarketDic table. We take the id value of the Code we need

   • Currency_id is also a related field. But, here we will be satisfied with any value that exists in a certain CurrencyDic table.

2. Set the i value

3. While the condition is true, we do inserts into our table
   Fields:

   • id – calculate the current maximum value of id and increase it by 1

   • TradeDate – use the value of the variable of the same name, i.e. yesterday

   • AccountName – combine the static “Name_” into one line with the value i. As a result, for the corresponding lines we get Name_1, Name_2, … Name_5

   • DealType – for all even lines (whose number is divisible by 2 without a remainder), the value will be “Buy”, and for the rest – “Sell”

   • Market_id and Currency_id are the values ​​of the variables of the same name described above.

So, after executing the script, we will get a ready data set in a second.
What is good about the script:

• The date value is always the right one, no need to update

• Always up-to-date values ​​from related tables

• The number of generated records is easily changed

• compact look
  Inserting at least 5 records through the value set values ​​(…), (…), … (…) would be much longer, most of the values ​​would be repeated and it would all be hard to read.

• Fairly simple implementation
  If you have already got here, most likely, you have an idea about variables and cycles.
  About getdate(), dateadd, concat, case… it is easy to find information in the official documentation and not only.

Of course, in practice, you need several records in several tables, and there are much more fields. But, if you understand my example, then neither the number of tables nor the ratio of records should scare you.

What to pay attention to when writing such scripts

Preliminary removal

In order not to clog the database, it would be good to delete the records received from its previous run by each script.
To do this, between the declaration of variables and inserts, we insert a set of deletes.
Accordingly, it should be possible to separate your records from any others.
I used 2 methods:

• Text Labels
  Some of the tables should have a text field where you can write something saying, like “Test_for_Task-12345_1”.
  Such records are located like 'Test_for_Task-12345_%' or just equality.
  The records associated with it are nested selects and they are deleted first, and then the main one.
  May be slow if there is a lot of data. Still like.

• Specified range of id-shnikov
  As a variable, we set a value that is far from the current one, and start from it: in the loop, add i to the variable.
  Knowing how many records we add, then we search by id through between.

If test cases are automated, then the deletion must be written so that it is performed after the check is completed (positive or with a fall).

Nested Loops

It will almost certainly be necessary to generate several records for one record in one table in another.
This is where a nested loop can help.
If you use your own id, then the algorithm for calculating id for it will be as follows:

@id + @k + @n2 * (@i - 1)

Where:

• id – computed/desired initial value

• k – variable inside nested loop

• n2 – the number of entries of the nested loop (i.e., while the nested loop we have with the condition: k <= n2)

• i – main loop variable

Checking for the existence of a record

The design may be very useful: if not exists (select ... )
Those. if nothing is found for some request, then we do something.

Variable types must match field types

When using variables to populate fields, as in the TradeDate, Market_id, and Currency_id examples, their types must be exactly the same as the fields themselves. So that it does not happen that the field is of type bigint, and the variable is int.

Calculations inside loops

Keep calculations/subqueries to a minimum inside loops so that they don’t create a load on each iteration.
In the example, id is calculated every time because otherwise, it cannot be recognized, and Market_id and Currency_id – only when declared.

Ready selects

In the process of writing the script, you will create selects that show all the generated records.
Save them at the end, commented out.
The next time you, or one of your colleagues, needs to understand the script, these queries will save time.

Implementation results

What has changed in the work of our team after I tried it in practice and told colleagues about this approach to data preparation:

• The time of manual testing of one of the algorithms on which this was tested was reduced by 3-4 times (before that, we found several suitable records and update-or them).

• It became easier for developers and analysts.
  The developers used my scripts to have something to debug.
  Analysts – to prepare for the demo.

• The problem of different directories on different stands is gone.
  The script worked equally well for everyone, because. id-shniks are not hardcoded, but are taken by requests.

• Automating test cases with such scripts is easy and fast.