Юнит тестирование скриптов баз данных
Принимая удобство в использовании юнит тестов на моем любимом С++, я попытался перенести свой опыт на TSQL, тем более что новый работодатель любит полезную инициативу на местах и раздает плюшки за оное.
Просмотрел несколько известных фреймворкoв я пришел к выводу, что, как правило они громоздки и приносят дополнительных синтаксис, который надо изучать дополнительно.
Некоторые фреймворки работают красиво и радуют глаз менеджера, которому их показывают, но имеют ряд ограниченией, которые мне не понравились.
Мне же хотелось все реализовать на чистом кошерно-халяльно-православном TSQL.
Периодически отвлекаясь от основной разработки в течении нескольких лет над оттачиванием структуры скрипта я таки решился с вами поделится (но все равно успел наплодить 3,5 Mb скриптов).
Основные требования у меня были простые — я должен выполнить любой юнит тест в файле без необходимости каких-либо телодвижений и специальных программных средств — только хардкор: sqlcmd или MSSMS.
В базу, в которой тест выполняется, не вносится никаких изменений — все откатывается на начало выполнения скрипта.
Только одно поставил ограничение — тест должен работать в пустой базе (начальные данные могут быть), иначе устанешь разбирать все варианты.
Основная задача — тестирование логики и поддержка целосности логики.
Для это в начало теста я ставлю такой заголовок:
SET QUOTED_IDENTIFIER ON
GO
PRINT '-------------------------------- CLR Unit tests for Habr Logic ---------------------------------'
IF 0 < ( SELECT count(*) FROM device)
begin
RAISERROR ('FAILED: database must be empty for this unit test', 16, -1 )
end
GO
Я стараюсь не создавать юнит-тестов длинее чем пару экранов, хотя это не просто, в случае сложной логики.
Типичный юнит-тест выглядит так и имеет 3 ключевые части:
BEGIN TRAN TestClr2
declare @test_name sysname = (select TOP 1 name from sys.dm_tran_active_transactions WHERE transaction_type = 1 ORDER BY transaction_begin_time DESC)
+ ' [fn_calculate_dev_status] record for device has wrong range'
BEGIN TRY SET NOCOUNT ON;
-- 1. prepare data for unit test
insert into device (mli, oxygen, stamp ) values ('111', 5.55, getdate() )
-- 2. execute unit test -- SELECT dbo.fn_calculate_dev_status( 111, 0.1, 1.2)
declare @result int = ( SELECT dbo.fn_calculate_dev_status( '111', 0.1, 1.2) )
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_SEVERITY() AS ErrorSeverity, ERROR_STATE() AS ErrorState
, @test_name AS ErrorProcedure, ERROR_LINE() AS ErrorLine, ERROR_MESSAGE() AS ErrorMessage
END CATCH
-- 3. result verification
IF @result <> 0
RAISERROR ('FAILED: %s no data for device should be presented %d ', 16, -1, @test_name, @result )
ELSE
print 'PASSED ' + @test_name
ROLLBACK TRAN TestClr2
GO
— 1. prepare data for unit test
Здесь мы можем заполнить нужные таблицы данными и подготовить какие-то временные переменные или таблицы, что бы не загромождать код в разделе тестирования.
— 2. execute unit test
Здеcь как правило идет, или вызов функции, или процедуры, или изменение таблицы, если тестируем логику триггера.
— 3. result verification
В этой части теста мы проверяем, как изменилось состояние обьектов базы данных, или результат тестируемой функции-процедуры.
Если функция-процедура возвращает рекордсет, то вставляем во временныю таблицу и уже ее анализируем.
Агрегатированные и подготовленные результаты сравниваем с эталоном и выдаем исключение, если ничего не получилось.
С Oracle все немного сложнее — написание и запуск теста в том виде и в той же идеалогии мне не удалось сделать, скорее от небольшого опыта — поддержку Oracle мы прекратили для своего продукта.
Каждый юнит-тест оформляется как процедура:
CREATE OR REPLACE PROCEDURE UnitTest9_TRG_JOBLOGDETAIL
AS
v_message VARCHAR2(255) := 'UnitTest9_TRG_JOBLOGDETAIL: INSERT joblogdetail]- joblogdetail_result not Failed and joblogdetail_endtime is null ';
v_maxdate date := '2014/01/01';
v_cnt NUMBER := 0;
BEGIN
savepoint my_savepoint;
-- 1. prepare data for unit test
insert into device ( dev_datecreated, dev_create_user, dev_ipaddress, dev_serialnumber
, dev_productid, dev_manufacturer, dev_model, dev_id, dev_status, dev_functions)
values (sysdate, 'Joe', '1.127.0.1', 'GSN-6238-N34', 'PRTF-452', 'Pinter Company', 'CM6003', 1, 1, 1 );
insert into joblog (JOBLOG_ID, joblog_starttime, joblog_progress) values (11, sysdate, 1);
insert into joblog_template (JOBLOG_TEMPLATE_ID, joblog_id, joblog_templatename, joblog_templatetype) values (111, 11, N'joblog_template_test', 1);
-- 2. execute unit test
insert into joblogdetail ( JOBLOGDETAIL_ID, joblog_template_id, joblogdetail_function, joblogdetail_functiondetail, joblogdetail_result, joblogdetail_dev_id, joblogdetail_starttime, joblogdetail_endtime)
values ( 1111, 111, 1, 1, 40, 1, v_maxdate, v_maxdate);
-- 3. result verification
SELECT count(dev_id) INTO v_cnt FROM device where dev_last_comm_time = v_maxdate;
IF 1 <> v_cnt THEN
DBMS_OUTPUT.PUT_LINE( 'FAILED: ' || v_message || ': Should not be update dev_last_comm_time: ' || TO_CHAR(v_maxdate));
ELSE
DBMS_OUTPUT.PUT_LINE( 'PASSED: ' || v_message );
END IF;
rollback to my_savepoint;
END;
/
В том же файле теста в конце придется сделать очистку базы от созданных и выполненых тестов.
commit;
/
set serveroutput on;
SET FEEDBACK OFF;
spool C:\dist\test.spl;
exec UnitTest_empty_database;
exec UnitTest3297_TRGBFR_UDEVICE(1);
exec UnitTest5_TRG_BF_UDEVICE;
exec UnitTest_3062a;
...
spool off;
/
DROP PROCEDURE UnitTest_3062;
DROP PROCEDURE UnitTest_BIRDIESEC_3344;
DROP PROCEDURE UnitTest_empty_database;
...
SET FEEDBACK ON;
commit;
Вот собственно и все.
Дальше вы только плодите файлы, разбитые по категориям типа: триггеры, функции, процедуры, репорты, крупные и специальные обьекты бизнес логики, и конечно, для каждого обьекта базы данных.
Практически все девелоперы баз данных данных морщатся и говорят — зачем мне надо, пусть тестеры этим занимаются. Если в базе нету логики совсем от слова совсем — то я с ними соглашусь, но если ее там много — то они натурально спасают нервы, репутацию и деньги.
Пример.
Есть у нас в web интерфейсе деревья логических связей между обьектами дерева типа America → Canada → Ontario → Waterloo, Asia → Japan → Tokyo → Ebina, то есть целый шар географических офисов.
Каждому такому ноду имеющего очень сложные правила пользователь или правило или генератор назначает устройства.
В результате, даже детально описанные по шагам инструкции сносят крышу всем, даже тем кто принимал участие в обсуждении и разработке этой логики.
Более полусотни шагов инструкции с разными наборами данных — все детально задокументированно.
Любое изменение или дополнение в логику — часы ручной проверки, что ничего не поломалось.
Рефакторинг смерти подобен.
После того как я покрыл логику юнит-тестами — все проверяется по шелчку и я точно уверен все работает как надо.
Любой прибегающий ко мне java разработчик, мечущий гром и молнии (думая про себя о моих кривый руках) легко ставится на место запуском соотвествующего теста.
Пару минут и все удовлетворены. Любое фатальное изменение кода в мое отсуствие будет быстро доложено мне пo почте.
Естественно, как человек ленивый, я решил все автоматизировать для Continuous Automation и написал кашу из батчей и питона.
Прошу меня казнить несильно, в ежедневной разработке почти десятoк языков и сред между которыми приходится прыгать, кастрофически не хватает времени все вылизать и привести в профессиональный вид.
Мне не хотелось все делать на windows powershell — наши скипты еще запускaют кое-где на embedded windows95.
Хотел все обращения сделать на Python, но обнаружилось, что некоторые sql (XML-parsing внутри cte) конструкции не поддерживаются не то что в библиотеке питона, но и в .NET, поэтому запуск скритов сделал через sqlcmd.
Код выложил здесь.
Чтобы запустить рабочий пример достаточно отредактировать 2 файла: smtppart.py и config.ini — SMTP серверное имя, порт и email куда будут валится сообщения о ошибках.
Скрипты сначала пытаются достать свежие обновления из svn (замените на свое — git, perforce,…).
Затем создается чистая база из скриптов со случайным именем, в ней запускаются юнит-тесты, затем база удаляется.
Создание базы в 80 Mb скриптов и 3.5 Mb тестов (основная часть схемы уже была сделана до моего прихода в компанию, поэтому оттестировал только свою часть) выполняются на моей машине примерно за 15 минут. Как раз успеваю выпить чашку кофе перед финальным коммитом.
Если были ошибки, то результаты ошибки придут на email.
Установка зависимостей описана в файле: readme.txt
После каждого изменения кода приходится ручками устанавливать хэш кода (будет видет в коммандной строке) в файле config.ini — письмо придет даже если изменили код и ничего не поломали — так я могу контролировать изменения в коде для того что бы я мог проверить сделанные изменения без моего предварительного участия.
Запуск всех юнит-тестов в файле autorun.bat может быть помещен в Windows Task Scheduler на запуск 1–2 в сутки перед корпоративным билдом или после ухода домой — если что вечером поломалось — можно дома перед телевизором глянуть что случилось и оперативно починить.
Знаю что в юнит тестах — самое тяжелое все настроить, а потом тесты писать легко и приятно, хотя бывает что тяжело и трудно, но надо. Удачи в тестировании, надеюсь мои советы кому-нибудь помогут.
С удовольствием приму советы если где-то что-то можно улучшить и причесать код, не судите строго.
