Исследование изменений в базе данных посредством контрольных сумм

Исследование состояния базы данных очень значительно помогает при исследовательском тестировании. А сам тестировщик может найти такой баг, который может удивить самых матерых программистов.

Очень значительной частью приложения, над которым я работаю – является База Данных под управлением SQL Server и Oracle. За 10 лет существования самого приложения, количество таблиц выросло до 210 только в стандартной поставке, каждый пчих пользователя обложен триггерами, написано множество хранимых процедур и функций.

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

1. Определить какие таблицы были затронуты в ходе манипуляций с пользовательским интерфейсом
2. Исследовать изменения данных, посредством создания отдельных SQL-скриптов
3. Записать полученные знания об изменениях и зависимостях в базе данных

Данная статья посвящена очень простой реализации первого пункта, отслеживанию измененных таблиц. Примеры реализации будут заточены для 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 файл, который бы запускал подсчет контрольных сумм и выдавал в консоль лишь имена измененных таблиц.

Что нам понадобится:

1. Скачать команду diff.exe, необходимы файлы: diff.exe, libiconv2.dll, libintl3.dll
2. Убедитесь в том, что командная утилита управления SQL Server (sqlcmd.exe) установлена и доступна (устанавливается вместе с SQL Server).
3. Скрипт вычисления контрольных сумм для каждой таблицы, который мы сгенерировали в предыдущем разделе. Сохраните его в файл под именем «database_checksums.sql»

Теперь давайте создадим файл getchanges.bat, который при каждом нажатии Enter, будет выводить список измененных таблиц:

@echo off
set SQL_SERVER_HOST=dz\SQL2008
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

Приятного Вам реверс-инжиниринга,
Дмитрий Жарий