Как быстро и эффективно создавать тесты для MySQL

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

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

В этой статье я расскажу, как провести тестирование без установки дополнительных утилит.

Процесс тестирования

Сначала опишем типичный процесс тестирования:

* Есть объект, который необходимо протестировать (процедура, функция и т. д.).

* Существует набор входных параметров, с которыми работает этот объект.

* Также в БД могут находиться исходные данные, необходимые для работы тестируемого объекта (например, данные, хранящиеся в таблице БД).

* Тестируемый объект выполняет некоторые преобразования, т.е. в результате его работы что-то должно измениться.

* Интерпретация результатов тестирования — это сравнение ожидаемых данных с полученными.

Если речь идет о процедуре, то, скорее всего, изменились какие-то данные в базе данных. Говоря о функции, обычно это значение, которое она возвращает. Рассмотрим процесс тестирования процедур на примере тестовой базы данных сотрудников для СУБД MySQL.

Описание структуры и сценарии установки можно найти здесь.

Вкратце команды установки выглядят так:

git clone https://github.com/datacharmer/test_db.git
cd test_db
mysql -t < employees.sql

Мы будем использовать только эти 3 таблицы:

Выполнение вставки

Давайте сначала проверим, как изменяются данные, когда мы выполняем вставку вручную, добавляя нового сотрудника.

Данные в таблице сотрудников следующие:

USE employees;
SELECT * FROM employees e;

Поскольку в первичном ключе таблицы employee нет AUTO_INCREMENT, нам нужно получить максимальное значение и увеличить его, чтобы получить следующий идентификатор. Мы сохраним его в переменной сеанса @emp_no:

SELECT MAX(emp_no) + 1 INTO @emp_no FROM employees e;

Теперь выполните вставку:

INSERT INTO employees (emp_no, birth_date, first_name, last_name, gender, hire_date)
VALUES (@emp_no, CURDATE() - INTERVAL 30 YEAR, 'John', 'Doe', 'M', CURDATE());

Мы сохранили новый идентификатор в переменной сеанса @emp_no, поэтому мы можем повторно использовать его в следующих тестовых примерах. Мы также используем его, чтобы проверить, были ли добавлены необходимые данные:

SELECT * FROM employees e WHERE emp_no = @emp_no;

Если бы первичный ключ имел свойство AUTO_INCREMENT, то нам не нужно было бы генерировать идентификатор, и мы могли бы получить его следующим образом:

SELECT LAST_INSERT_ID() INTO @emp_no;

Очевидно, что в этом случае ID не нужно было бы передавать процедуре и указывать в операторе INSERT.

Создание процедуры

Теперь создадим процедуру, содержащую только один оператор INSERT:

CREATE PROCEDURE employees.add_employee(IN p_emp_no int, IN p_birth_date date, IN p_first_name varchar(14), IN p_last_name varchar(16), IN p_gender enum ('M', 'F'), IN p_hire_date date)
BEGIN
  INSERT INTO employees (emp_no, birth_date, first_name, last_name, gender, hire_date)
  VALUES (p_emp_no, p_birth_date, p_first_name, p_last_name, p_gender, p_hire_date);
END

Мы уже можем вызывать процедуру, но нам нужно запомнить некоторые данные перед вызовом и выполнить какую-то проверку, которая покажет, что после вызова процедура выполнилась корректно.

Тестирование созданной процедуры

SELECT MAX(emp_no) + 1 INTO @emp_no FROM employees e;
CALL add_employee(@emp_no, CURDATE() - INTERVAL 30 YEAR, 'John', 'Doe', 'M', CURDATE())
SELECT IF(COUNT(1) = 1, 'OK', 'FAIL') AS test_result FROM employees e WHERE emp_no = @emp_no;

Второй оператор SELECT возвращает либо "OK", либо "FAIL" в зависимости от его проверки; количество или записи с @emp_id должны быть равны 1:

Еще пара процедур

Давайте добавим еще две процедуры для добавления нового отдела и добавления сотрудника в отдел:

CREATE PROCEDURE employees.add_department(IN p_dept_no char(4), IN p_dept_name varchar(40))
BEGIN
  INSERT into departments (dept_no, dept_name)
  VALUES (p_dept_no, p_dept_name);
END

CREATE PROCEDURE employees.add_emp_to_dept(IN p_emp_no int, IN p_dept_no char(4), IN p_from_date date, IN p_to_date date)
BEGIN
  INSERT INTO dept_emp (emp_no, dept_no, from_date, to_date)
  VALUES (p_emp_no, p_dept_no, p_from_date, p_to_date);
END

Добавление тестов для новых процедур

Теперь нам нужно добавить два теста для этих процедур. Но перед этим мы хотели бы проверить, какие данные нужно сгенерировать:

SELECT * FROM departments d;

Как мы видим, идентификатор отдела имеет формат pNNN, поэтому нам нужно использовать сложную конструкцию для генерации следующего идентификатора:

SELECT CONCAT('d', LPAD(MAX(SUBSTR(d.dept_no, 2, 10)) + 1, 3, 0))
INTO @dept_no
FROM departments d;

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

Мы вызываем и тестируем процедуру следующим образом:

CALL add_department(@dept_no, 'Testing');
SELECT IF(COUNT(1) = 1, 'OK', 'FAIL') AS test_result FROM departments d WHERE d.dept_no = @dept_no;

Аналогичным образом проверим, как добавить сотрудника в отдел:

SELECT * FROM dept_emp ORDER BY emp_no desc;

Поскольку у нас уже есть идентификаторы созданных сотрудников и отделов, сохраненные в переменных сеанса @emp_no и @dept_no, мы можем использовать их для добавления сотрудника в отдел:

CALL add_emp_to_dept(@emp_no, @dept_no, CURDATE(), CURDATE() + INTERVAL 1 YEAR);
SELECT IF(COUNT(1) = 1, 'OK', 'FAIL') AS test_result FROM dept_emp de WHERE de.emp_no = @emp_no AND de.dept_no = @dept_no;

Мы также используем эти переменные сеанса для выполнения тестовых проверок.

Тестирование процедуры, возвращающей набор данных

Есть еще один способ использования процедур, когда нет изменений данных, но возвращается результат некоторых операторов SELECT.

Примером такой процедуры является процедура, которая возвращает информацию обо всех сотрудниках в отделе. Пусть он называется get_employees:

CREATE PROCEDURE employees.get_employees(IN p_dept_no char(4))
BEGIN
  SELECT e.*
  FROM employees e
  INNER JOIN dept_emp de ON e.emp_no = de.emp_no
  WHERE de.dept_no = p_dept_no;
END

Мы называем это:

CALL get_employees(@dept_no);

И результат:

В этом случае у нас нет измененных данных, но мы можем проверить количество возвращенных строк.

И для этого мы используем функцию FOUND_ROWS:

CALL get_employees(@dept_no);
SELECT IF(FOUND_ROWS() = 1, 'OK', 'FAIL') AS test_result FROM dept_emp de WHERE de.emp_no = @emp_no AND de.dept_no = @dept_no;

Давайте добавим немного очистки

Чтобы не засорять базу тестовыми данными, нам нужно удалить их после завершения всех тестов:

DELETE FROM employees WHERE emp_no = @emp_no;
DELETE FROM departments WHERE dept_no = @dept_no;
DELETE FROM dept_emp WHERE dept_no = @dept_no;

Сбор и отображение результатов всех тестов

Давайте добавим немного кода и соберем результаты всех тестов в переменную. Это поможет нам:

1. Визуализируйте результаты тестирования.

2. Запускайте тесты в одном скрипте.

3. Запустите тестовый сценарий из командной оболочки и проанализируйте результаты теста.

4. Настроить интеграцию с git, хуками, CI и т. д.

Наконец, вот наш тестовый скрипт:

set @test_results = '';
set @pad = 80, @s = ' ';

set @test_name = 'Add employee';
SELECT MAX(emp_no) + 1 INTO @emp_no FROM employees e;
CALL add_employee(@emp_no, CURDATE() - INTERVAL 30 YEAR, 'John', 'Doe', 'M', CURDATE());
SELECT IF(COUNT(1) = 1, '[OK]', '[FAIL]') INTO @test_result FROM employees e WHERE emp_no = @emp_no;
set @test_results = CONCAT_WS('', @test_results, CONCAT_WS('', '[TEST]', LPAD(@test_name, @pad, @s), ' ', @test_result), 'n');

set @test_name = 'Add department';
SELECT CONCAT('p', LPAD(MAX(SUBSTR(d.dept_no, 2, 10)) + 1, 3, 0)) INTO @dept_no FROM departments d;
CALL add_department(@dept_no, CONCAT('Testing ', @dept_no));
SELECT IF(COUNT(1) = 1, '[OK]', '[FAIL]') INTO @test_result FROM departments d WHERE d.dept_no = @dept_no;
set @test_results = CONCAT_WS('', @test_results, CONCAT_WS('', '[TEST]', LPAD(@test_name, @pad, @s), ' ', @test_result), 'n');

set @test_name = 'Add employee to department';
CALL add_emp_to_dept(@emp_no, @dept_no, CURDATE(), CURDATE() + INTERVAL 1 YEAR);
SELECT IF(COUNT(1) = 1, '[OK]', '[FAIL]') INTO @test_result FROM dept_emp de WHERE de.emp_no = @emp_no AND de.dept_no = @dept_no;
set @test_results = CONCAT_WS('', @test_results, CONCAT_WS('', '[TEST]', LPAD(@test_name, @pad, @s), ' ', @test_result), 'n');

set @test_name = 'Get employees';
CALL get_employees(@dept_no);
SELECT IF(FOUND_ROWS() = 1, '[OK]', '[FAIL]') INTO @test_result;
set @test_results = CONCAT_WS('', @test_results, CONCAT_WS('', '[TEST]', LPAD(@test_name, @pad, @s), ' ', @test_result), 'n');

set @test_name = 'Cleanup';
DELETE FROM dept_emp WHERE dept_no = @dept_no;
DELETE FROM departments WHERE dept_no = @dept_no;
DELETE FROM employees WHERE emp_no = @emp_no;

SELECT IF(
  (SELECT COUNT(1) FROM employees e WHERE emp_no = @emp_no)
  + (SELECT COUNT(1) FROM departments WHERE dept_no = @dept_no)
  + (SELECT COUNT(1) FROM dept_emp WHERE dept_no = @dept_no)
  = 0, '[OK]', '[FAIL]') INTO @test_result;
;
set @test_results = CONCAT_WS('', @test_results, CONCAT_WS('', '[TEST]', LPAD(@test_name, @pad, @s), ' ', @test_result), 'n');

SELECT @test_results;

И его результат:

Как видно на картинке, теперь у нас есть результаты всех тестов, собранные воедино, и мы можем их отображать, а также обрабатывать и анализировать.

Следует отметить следующие моменты:

При вызове процедуры add_department мы используем CONCAT('Testing', @dept_no) в качестве имени отдела, поскольку это поле должно быть уникальным. Есть более правильный способ генерации уникальных значений, но я думаю, что это тема для следующего обсуждения.

Также необходимо обеспечить корректную работу с транзакциями; здесь я намеренно пропустил его, чтобы сконцентрироваться на сути тестирования.

Некоторые повторяющиеся блоки могут быть помещены в процедуры или функции - если вы хотите и имеете на это разрешение.

Но даже в таком виде несложно скопировать блок и создать из него новые тесты.

Надеюсь, что приведенная выше информация будет полезна всем, кто занимается разработкой и тестированием баз данных.