Исследование состояния базы данных очень значительно помогает при исследовательском тестировании. А сам тестировщик может найти такой баг, который может удивить самых матерых программистов.
Очень значительной частью приложения, над которым я работаю – является База Данных под управлением SQL Server и Oracle. За 10 лет существования самого приложения, количество таблиц выросло до 210 только в стандартной поставке, каждый пчих пользователя обложен триггерами, написано множество хранимых процедур и функций.
Но для меня важно лишь то, какие изменения данных провоцируют мои манипуляции с пользовательским интерфейсом. И алгоритм моих действий по исследованию системы заключается в следующем:
- Определить какие таблицы были затронуты в ходе манипуляций с пользовательским интерфейсом
- Исследовать изменения данных, по средствам создания отдельных SQL-скриптов
- Записать полученные знания об изменениях и зависимостях в базе данных
Данная статья посвящена очень простой реализации первого пункта, отслеживанию измененных таблиц. Примеры реализации будут заточены для SQL Server, но учитывая что аналогичная функциональность есть в любой известной реляционной СУБД – данный подход можно применить и для Oracle, MySQL и др.
Реализация подсчета контрольных сумм таблиц средствами SQL Server
Здесь нам понадобится два запроса:
Первый, для того чтобы получить список полных имен (имя схемы + имя таблицы) всех таблиц в базе данных:
SELECT '[' + sys.schemas.name + '].[' + sys.Tables.name + ']' AS TABLEFULLNAME FROM sys.Tables JOIN sys.schemas ON sys.Tables.schema_id = sys.schemas.schema_id ORDER BY sys.schemas.name, sys.Tables.name
На примере базы NerdDinner, мы получим следующий результат:
[dbo].[Dinners]
[dbo].[RSVP]
Второй запрос подсчитает контрольную сумму, в данном случае для таблицы [dbo].[Dinners]
SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM [dbo].[Dinners] WITH (NOLOCK)
Результат:
1828475971
Это контрольная сумма текущего состояния таблицы. При удалении, добавлении, изменении новых данных – она будет меняться. И это то, что нам нужно.
Но, неужели теперь, после получение списка таблиц в первом запросе, необходимо подставлять каждую во второй руками? – Да! Но, не руками. Для этого у меня был написан скрипт на языке Perl. Но, в этой статье я хочу обойтись без использования языков программирования.
Для того, чтобы сгенерировать нужные подстановки средствами SQL, необходимо выполнить следующий запрос, который сгенерирует код другого запроса:
SELECT 'SELECT ''[' + sys.schemas.name + '].[' + sys.Tables.name + ']'' AS TABLENAME, CHECKSUM_AGG(BINARY_CHECKSUM(*)) AS CHECKSUM FROM [' + sys.schemas.name + '].[' + sys.Tables.name + '] WITH (NOLOCK) UNION' AS SCRIPT
FROM sys.Tables JOIN sys.schemas ON sys.Tables.schema_id = sys.schemas.schema_id
ORDER BY sys.schemas.name, sys.Tables.name
И в случае NerdDinner, мы получим следующий результат:
SELECT '[dbo].[Dinners]' AS TABLENAME, CHECKSUM_AGG(BINARY_CHECKSUM(*)) AS CHECKSUM FROM [dbo].[Dinners] WITH (NOLOCK) UNION
SELECT '[dbo].[RSVP]' AS TABLENAME, CHECKSUM_AGG(BINARY_CHECKSUM(*)) AS CHECKSUM FROM [dbo].[RSVP] WITH (NOLOCK) <s>UNION</s>
Теперь удалите последний «UNION», и запустите сгенерированный запрос, в результате он покажет следующее:
[dbo].[Dinners] 1828475971
[dbo].[RSVP] 4096
Автоматизация отслеживания изменения таблиц по средствам .bat файла
Теперь давайте создадим простой bat файл, который бы запускал подсчет контрольных сумм и выдавал в консоль лишь имена измененных таблиц.
Что нам понадобится:
- Скачать команду diff.exe, необходимы файлы: diff.exe, libiconv2.dll, libintl3.dll
- Убедитесь в том, что командная утилита управления SQL Server (sqlcmd.exe) установлена и доступна (устанавливается вместе с SQL Server).
- Скрипт вычисления контрольных сумм для каждой таблицы, который мы сгенерировали в предыдущем разделе. Сохраните его в файл под именем «database_checksums.sql»
Теперь давайте создадим файл getchanges.bat, который при каждом нажатии Enter, будет выводить список измененных таблиц:
@echo off
set SQL_SERVER_HOST=dzSQL2008
set SQL_USER=admin
set SQL_USER_PASSWORD=admin
set SQL_DATABASE=NerdDinner
set CHECKSUM_SCRIPT=database_checksums.sql
REM Clenup
echo. > left.txt
echo. > right.txt
:HOME
sqlcmd.exe -U %SQL_USER% -P %SQL_USER_PASSWORD% -S %SQL_SERVER_HOST% -d %SQL_DATABASE% -i %CHECKSUM_SCRIPT% -o left.txt
diff.exe --side-by-side --suppress-common-lines left.txt right.txt > diff-result.txt
type diff-result.txt
copy left.txt right.txt /y > nul
pause
GOTO HOME
Видео демонстрация работы скрипта:
Заключение
В своей работе я очень часто использую данный подход при исследовательском тестировании приложения. Например, без необходимости разбираться в той куче кода на C++, C# и хранимых процедур, который лежит между моим кликом на пользовательском интерфейсе и изменении состоянии базы, я держать ситуацию под контролем и очень быстро находить и отслеживать нужные мне данные.
В некоторых ситуациях, занимаясь мониторингом базы, и опираясь на предыдущие полученные знания, мне удавалось находить баги, которые небыли видны через пользовательский интерфейс приложения, зато сильно искажали данные в производимых отчетах.
Для большего удобства, я написал утилиту Sql Change Scanner, которая позволяет осуществлять процесс мониторинга изменений через более удобный интерфейс.
Более подробно об утилите вы можете прочитать из следующего поста:
Sql Change Scanner – утилита для отслеживания изменений в таблицах SQL Server
Скачать программу можно на Github:
https://github.com/dzhariy/SqlChangeScanner/downloads
Приятного Вам реверс-инжиниринга,
Дмитрий Жарий
Автор: Dmitry_Zhariy
