В мире баз данных и бизнес-аналитики разработчикам критически важно тестировать свои запросы перед их развертыванием. Не выполнение этого требования может привести к проблемам в производственной среде, которые могут быть связаны с реальными данными, а не с неправильным модульным тестированием. Чтобы избежать таких проблем, важно генерировать тестовые данные для проверки логики запросов без использования реальных данных. В этой статье мы рассмотрим процесс генерации тестовых данных и предоставим некоторые полезные рекомендации.

Понимание модульного тестирования в SQL

Прежде чем приступить к тестированию SQL-запросов, важно понять, что такое модульное тестирование и как оно применяется к SQL-коду. Модульное тестирование – это тип тестирования программного обеспечения, который проверяет отдельные программные модули или компоненты, чтобы убедиться, что каждый из них работает ожидаемым образом. В SQL модульное тестирование обычно относится к тестированию данных, которое включает проверку данных по тестовым критериям. С другой стороны, модульное тестирование в SQL также включает проверку логики запросов с использованием фиксированных входных данных без использования реальных данных.

Если вы новичок в SQL-запросах, рекомендуется ознакомиться с основами. Вы можете начать с изучения того, что такое SQL-запрос и пройти несколько уроков по SQL-запросам.

Рекомендации по генерации тестовых данных

При разработке запросов разработчики часто сталкиваются с двумя типами разрешений базы данных: полный доступ для добавления, удаления и изменения данных в существующей базе данных или только возможность чтения данных. Кроме того, могут возникать ситуации, когда разработчики сталкиваются с пустыми базами данных и им нужно добавить данные для разработки интерфейса, что может затруднить правильное модульное тестирование запросов. Чтобы справиться с этими сценариями, следуйте некоторым рекомендациям:

Рекомендация 1 – Генерация тестовых данных с использованием API

Если у вас есть доступ к приложениям интерфейса или API транзакционной базы данных, вы можете генерировать тестовые данные с помощью этих инструментов. Многие компании имеют тестовые среды, которые позволяют разработчикам создавать запросы и генерировать тестовые данные. Используя эти приложения или API, разработчики могут получить практические знания о бизнес-аспекте данных и даже обнаружить связанные ошибки или проблемы. Если вы новичок в этом процессе или нуждаетесь в помощи в генерации тестовых данных, не стесняйтесь обратиться к командам операций или контроля качества. Существуют также программные инструменты, такие как Postman, которые могут помочь вам использовать и тестировать API.

Рекомендация 2 – Генерация тестовых данных с использованием T-SQL

Если у вас есть разрешения на добавление, удаление и обновление данных, вы можете генерировать тестовые данные, создав их самостоятельно. Однако важно понимать бизнес-правила, связанные с данными. Например, если вам нужно сгенерировать данные для таблицы “Person” с полями, такими как ID, NationalID, FirstName, LastName, Gender и BirthDate, вы можете использовать предопределенные функции и простые техники запросов для создания данных. Вот пример:

-- Сначала мы создадим таблицу.
CREATE TABLE Person
(
  ID         NUMERIC(38,0) PRIMARY KEY IDENTITY (1,1),
  NationalID NVARCHAR(10),
  FirstName  NVARCHAR(30),
  LastName   NVARCHAR(30),
  Gender     NVARCHAR(10),
  BirthDate  DATE
)

-- Затем мы создадим запрос, который заполнит таблицу Person 39600 строками.
declare @max int = 200;

with FirstName(FirstName,Gender) as
(
    SELECT N'John'       , N'Male'   UNION ALL
    SELECT N'Tim'        , N'Male'   UNION ALL
    SELECT N'Laura'      , N'Female' UNION ALL
    SELECT N'Jeff'       , N'Male'   UNION ALL
    SELECT N'Alexander'  , N'Male'   UNION ALL
    SELECT N'Burt'       , N'Male'   UNION ALL
    SELECT N'Christopher', N'Male'   UNION ALL
    SELECT N'Daniel'     , N'Male'   UNION ALL
    SELECT N'Daniela'    , N'Female' UNION ALL
    SELECT N'Eric'       , N'Male'   UNION ALL
    SELECT N'Alexandra'  , N'Female' UNION ALL
    SELECT N'Bertha'     , N'Female' UNION ALL
    SELECT N'Christine'  , N'Female' UNION ALL
    SELECT N'Noor'       , N'Female' UNION ALL
    SELECT N'Leen'       , N'Female' UNION ALL
    SELECT N'Aseel'      , N'Female' UNION ALL
    SELECT N'Erica'      , N'Female' UNION ALL
    SELECT N'Jackson'    , N'Male'   UNION ALL
    SELECT N'Walid'      , N'Male'   UNION ALL
    SELECT NULL          , NULL      UNION ALL --  Эта строка была добавлена, потому что столбцы FirstName,Gender в таблице Person могут быть пустыми
    SELECT NULL          , N'Male'   UNION ALL --  Эта строка была добавлена, потому что столбец FirstName в таблице Person может быть пустым
    SELECT NULL          , N'Female'           --  Эта строка была добавлена, потому что столбец FirstName в таблице Person может быть пустым
)
, LastNames(LastName) as
(
    SELECT N'Johnson'   UNION ALL
    SELECT N'Hudson'    UNION ALL
    SELECT N'Jackson'   UNION ALL
    SELECT N'Ranallo'   UNION ALL
    SELECT N'Curry'     UNION ALL
    SELECT N'Allaham'   UNION ALL
    SELECT N'Alnatur'   UNION ALL
    SELECT N'Alkhattab' UNION ALL
    SELECT Null --  Эта строка была добавлена, потому что столбец LastName в таблице Person может быть пустым
)
, SortedNames(FirstName, LastName,Gender, RowNum) as  -- Этот код сгенерирует отсортированные имена
(
   /* Количество записей из этого SELECT будет: Количество мужских записей в WITH-клаузе FirstName, умноженное на количество записей в WITH-клаузе LastNames*/
    select
           FirstName
         , LastName
         , Gender
         , ROW_NUMBER() over (Order by newid())
    from FirstName
    cross join LastNames
    where Gender = 'Male'
   UNION
   /* Количество записей из этого SELECT будет: Количество женских записей в WITH-клаузе FirstName, умноженное на количество записей в WITH-клаузе LastNames*/
    select
           FirstName
         , LastName
         , Gender
         , ROW_NUMBER() over (Order by newid())
    from FirstName
    cross join LastNames
    where Gender = 'Female'
   UNION
   /* Количество записей из этого SELECT будет: Количество записей с неопределенным полом в WITH-клаузе FirstName, умноженное на количество записей в WITH-клаузе LastNames*/
    select 
           FirstName
         , LastName
         , Gender
         , ROW_NUMBER() over (Order by newid())
    from FirstName
    cross join LastNames
    where Gender is null
),
Names AS
(
    select     
         FirstName
       , LastName
       , Gender
    from SortedNames
)
, Multiplier as
 (
   SELECT 1 N
   UNION ALL
   SELECT N+1
   FROM Multiplier
   WHERE N < @max
 )
INSERT INTO [dbo].[Person]([NationalID],[FirstName],[LastName],[Gender],[BirthDate])
select 
  RIGHT(CAST(CAST(CRYPT_GEN_RANDOM(4) AS bigint) AS varchar(10)), 10) NationalID /* Сгенерировать случайные 10 чисел*/             
, FirstName
, LastName
, Gender
, DATEADD(DAY, ABS(CHECKSUM(NEWID()) % 36500), '1940-1-1') BirthDate /* Сгенерировать случайные даты между 1940-1-1 и 2040-01-01; 36500 - это примерно 100 лет*/
from Names
cross join Multiplier /*Для кросс-умножения имен с WITH-клаузой, которая возвращает 198 случайных комбинаций {FirstName LastName с полом} с числом, которое мы указываем в параметре @max*/
order by NEWID()
option (maxrecursion 0);

В приведенном выше примере мы создаем таблицу “Person” и заполняем ее 39 600 строками тестовых данных. Мы используем предопределенные функции и простые техники запросов для генерации случайных значений для полей NationalID, FirstName, LastName, Gender и BirthDate. Это позволяет нам иметь реалистичный набор данных для тестирования наших запросов.

Рекомендация 3 – Генерация данных с помощью запросов

Если у вас есть только разрешение на чтение базы данных, которая не имеет необходимого шаблона данных для тестирования вашего запроса, вы все равно можете генерировать тестовые данные, логически изменяя существующие данные. Обычным подходом является добавление ваших данных в запрос при тестировании. Например, если вам нужно подсчитать количество лиц в таблице “Person” по поколениям и полу, вы можете добавить фиктивные записи с определенными значениями возраста и пола для проверки логики вашего запроса. Вот пример:

WITH Person_SQ AS
(
 SELECT  ID, NationalID, FirstName ,LastName,Gender,BirthDate
 FROM Person
 UNION ALL
 SELECT (SELECT MAX(ID) FROM Person) + 1  ID,
        N'0000000001' NationalID,
        N'Dummy' FirstName ,
        N'Record' LastName,
        N'' Gender,
        NULL BirthDate
)
, AgeGeneration AS
 (
  SELECT 1 GenID , 78 FromAge  , 999 ToAge  , N'The Silent Generation' Generation , N'1900-1945' AgeRange
  UNION ALL
  SELECT 2 GenID , 59 FromAge , 77 ToAge     , N'Baby Boomers' Generation , N'59-77' AgeRange
  UNION ALL
  SELECT 3 GenID , 43 FromAge , 58 ToAge , N'Gen X: Born' Generation , N'43-58' AgeRange
  UNION ALL              
  SELECT 4 GenID , 27 FromAge , 42 ToAge , N'Millennials' Generation , N'27-42' AgeRange
  UNION ALL              
  SELECT 5 GenID , 11 FromAge , 26 ToAge , N'Gen Z' Generation , N'11-26' AgeRange
  UNION ALL              
  SELECT 6 GenID , -999 FromAge , 10 ToAge , N'Gen Alpha' Generation , N'<=10' AgeRange
  UNION ALL              
  SELECT 7 GenID , NULL FromAge , NULL ToAge , N'Unknown' Generation , N'Unknown' AgeRange
 )
 
SELECT *
FROM 
   (
     SELECT Person_SQ.ID, NationalID, FirstName ,LastName,Gender,BirthDate , 
          DateDiff(Year,BirthDate, GETDATE()) Age , 
          AG.GenID, AG.Generation , AG.AgeRange,
          CASE WHEN Gender IS NULL OR Gender = N'' THEN N'Unknown' ELSE Gender END MappedGender
     FROM Person_SQ
     LEFT JOIN AgeGeneration AG ON (DateDiff(Year,BirthDate, GETDATE()) BETWEEN AG.FromAge AND ToAge) OR (BirthDate IS NULL AND AG.GenID = 7)
   ) MainQuery
ORDER BY ID DESC;

В этом примере мы добавляем фиктивную запись с определенными значениями в таблицу “Person” для проверки логики запроса. Мы также используем WITH-клаузу и команду UNION ALL для логического создания таблицы справочника, которая не создается физически в базе данных. Это позволяет нам тестировать наш код, даже когда требуемые данные недоступны.

Рекомендация 4 – Генерация данных с использованием инструментов генерации данных

В некоторых ситуациях использование инструментов генерации данных может значительно упростить процесс вставки данных в базу данных для тестирования. Эти инструменты предоставляют эффективный и экономически выгодный способ генерации тестовых данных, особенно для больших баз данных. Некоторые популярные инструменты генерации данных включают Red Gate SQL Data Generator, Visual Studio Data Generators, Toad for SQL Server и devart Data Generator for SQL Server.

Заключение

Генерация тестовых данных для запросов SQL Server – это важный этап процесса разработки. Следуя рекомендациям, указанным в этой статье, разработчики могут убедиться, что их запросы правильно протестированы и проверены. Будь то использование API, техник T-SQL, логической манипуляции данными или инструментов генерации данных, существуют различные методы для генерации тестовых данных. Вложив время и усилия в тестирование запросов перед развертыванием, разработчики могут избежать проблем в производственной среде и предоставить надежный и эффективный SQL-код.

Статья последний раз обновлялась: 2023-08-21