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

[qw1]

если база стогиговая (что нередко бывает), это долго.

альтернативный способ отслеживания изменений в базе — использовать профайлер, встроенный в MS SQL Management Studio. его можно настроить на логгирование всех SQL-запросов. для удобства можно отсеять select-ы

[lair]

Oh my god, а профилирование и change tracking (в MS SQL) зачем придумали?

Мне страшно подумать, сколько будут считаться контрольные суммы по нашей маленькой БД в 2Tb.

[Dmitry_Zhariy]

Да, про ограничения такого подхода я не указал. Так и знал, что найдется человек с базой 2 ТБ :)

Но, я думаю, что в закромах у вас завалялась «лабораторная» база данных более меньшего размера.

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

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

Профайлером я тоже пользуюсь. Просто с применением данного подхода для меня нет необходимости его запускать каждый раз.

[qw1]

с профайлером можно узнать, сколько строк изменилось и какие.

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

[Dmitry_Zhariy]

Я с вами согласен. Действительно, если происходит несколько модификаций в одной таблице, то для более детального исследования нужно использовать профайлер.
Но, для того, чтобы предварительно узнать, какая таблица была модифицирована – достаточно и этого средства контрольных сумм. Это быстро и просто.
Если брать аналогию со (школьной) химией, то это средство действует как лакмусовая бумажка – чтобы узнать есть ли в стакане кислота. Ну а дальше нужно использовать несколько реакций, чтобы определить какая именно.

[Dmitry_Zhariy]

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

SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) AS CHKSUM, * 
  FROM [dbo].[Dinners]
  GROUP BY  [DinnerID]
      ,[Title]
      ,[EventDate]
      ,[Description]
      ,[HostedById]
      ,[HostedBy]
      ,[ContactPhone]
      ,[Address]
      ,[Country]
      ,[Latitude]
      ,[Longitude]

Осталось только придумать, как сделать этот код более универсальным.

[qw1]

предполагаю, что [DinnerID] — primary key. поэтому включение других полей в группировку не требуется.

если бы MS SQL поддерживал универсальный альяс на уникальный номер записи в таблице (как SQLite, Oracle или Firebird), легко было бы сделать универсальный скрипт.

а так — для каждой анализируемой таблицы искать primary key в системных таблицах и строить sql-запрос, подставляя его в group by

[Dmitry_Zhariy]

Нет, пробовал без group by, SQL Server выдает ошибку
Msg 8120, Level 16, State 1, Line 1
Column 'dbo.Dinners.DinnerID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

когда я дописываю GROUP BY DinnerID — уже ругается на Title

Вот скрипт создания Диннерз:

CREATE TABLE [dbo].[Dinners](
	[DinnerID] [int] IDENTITY(1,1) NOT NULL,
	[Title] [nvarchar](50) NOT NULL,
	[EventDate] [datetime] NOT NULL,
	[Description] [nvarchar](256) NOT NULL,
	[HostedById] [nvarchar](256) NULL,
	[HostedBy] [nvarchar](256) NOT NULL,
	[ContactPhone] [nvarchar](20) NOT NULL,
	[Address] [nvarchar](50) NOT NULL,
	[Country] [nvarchar](30) NOT NULL,
	[Latitude] [float] NOT NULL,
	[Longitude] [float] NOT NULL,
 CONSTRAINT [PK_Dinners] PRIMARY KEY CLUSTERED 
(
	[DinnerID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

[qw1]

блин, это ж основы SQL. при наличии аггрегирующей функции и не аггрегированных выражений в выборке нужно эти выражения перечислить в group by

SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) AS CHKSUM, DinnerID
FROM Dinners
GROUP BY DinnerId

[Dmitry_Zhariy]

Ну, типа того, значит возвращаемся к первоначальному запросу :-D

[Dmitry_Zhariy]

Сделаю важное уточнение, в запросе я указал * не просто так, а именно потому что хочу видеть и все данные + чексумма

[qw1]

тогда в чексумме нет смысла.
diff так или иначе покажет изменения

[Dmitry_Zhariy]

точно. Это тупик :)

В таком случае, ваш вариант с DinnerID имеет больше смысла

[lair]

Но, я думаю, что в закромах у вас завалялась «лабораторная» база данных более меньшего размера.

Вот чтобы сделать эту «лабораторную» БД, и надо сделать реверс-инжиниринг существующей.

[Dmitry_Zhariy]

В таком случае, предложенный мной механизм вряд ли поможет. Знаю по своему опыту о «скрытой» бизнес-логике, с которой не раз приходилось встречаться, например «элементы с ID 1 и 2 нельзя удалять, потому что в некоторых ситуациях код приложения может рассчитывать на их существование». Тут без анализа и хранимых процедур и исходного кода бывает на обойтись.

[lair]

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

[Dmitry_Zhariy]

Наибольший объем одной из моих продакшн баз данных – 50 гиг.
В таких условиях работает вполне сносно.
Есть лабораторные базы данных, размер которых до 20 Мб. Я удаляю элемент одним способом: смотрю какие таблицы модифицируются.
Таким образом я узнал как правильно удалять элементы чтобы не сломать зависимости, где находится таблицы с конфигурационными данными, что происходит в результате тех или инных операций.
В моей ситуации таких таблиц около 210 и этот подход позволят очень быстро найти именно те таблицы, на которых в дальнейшем необходимо сосредоточить исследование.
Конкретно в моей ситуации – этот подход очень полезен, но я не могу гарантировать пользу в контексте вашего проекта.

[dr013]

В копилку — отслеживание изменений в Oracle. Если интересует только дата/время последнего изменения данных —

select max(ora_rowscn), scn_to_timestamp(max(ora_rowscn)) from scott.emp;

Подробнее у Кайта = Finding latest DML time on a table

[Dmitry_Zhariy]

Спасибо