Проверка различий между двумя версиями сборки базы данных
Одной из распространенных проблем при разработке баз данных является знание того, что изменилось и когда. Вы хотите обнаружить изменения и выяснить, что изменилось в конкретной сборке. Вы могли подумать, что это будет легко, если учесть, что у каждого объекта базы данных в системных представлениях SQL Server есть дата последнего изменения объекта. Если вы хотите узнать, что было изменено с определенной даты, вам нужно будет просто перечислить все те объекты, у которых дата «последнего изменения» больше этой даты.
Это и в самом деле работает нормально, если вы просто хотите знать, изменились ли объекты, но вы, вероятно, захотите большего. Хорошо было бы знать также, что было удалено, было ли что-то переименовано или добавлено.
Все это может быть достигнуто достаточно просто путем запроса метаданных, но в отличие от инструмента сравнения баз данных, он может сделать немного больше, чем просто сказать вам, что изменилось, а не то, как он изменился. У него другая цель. Это дает вам только обзор, чтобы отслеживать изменения в конкретной сборке. Если вы хотите сравнить разные сборки, вы не можете их использовать, потому что object_ids будет другим. Вам нужно будет использовать инструмент сравнения баз данных, потому что информация, которую вы хотите использовать, потеряна.
Проблемы с этим методом возникают из-за того, что некоторые компоненты базы данных, такие как индексы, столбцы, параметры и статистика распределения, не считаются объектами, поскольку в этом случае они являются частями других объектов, таблиц или подпрограмм. Например, когда изменяется столбец, изменяется вся таблица, а ее дата последнего изменения изменяется на время, когда столбец был изменен. Однако некоторые объекты, которые являются частями других объектов, таких как первичные ключи и ограничения, считаются объектами сами по себе. Иногда они возвращают обновленную дату модификации к родительскому объекту. Иногда, редко они этого не делают. При изменении ограничения внешнего ключа обе участвующие таблицы помечаются как измененные, что не совсем интуитивно понятно.
В настоящее время проще использовать расширенные события для отслеживания изменений в базе данных, но для этого требуется сеанс, который должен быть запущен во время захвата события. Если это невозможно, то трассировка по умолчанию может содержать информацию. С другой стороны, это могло быть отключено, или данные могли быть потеряны. Это происходит, если фоновый шум задач обслуживания заглушает модификации базы данных, которые вы хотите отслеживать, выталкивая их из текущего журнала трассировки по умолчанию. Бесполезно, если вы хотите изучить историю изменений в сборке.
Чтобы отслеживать изменения в базе данных, вам необходимо понять, каким образом различные компоненты базы данных записываются в системных представлениях. Объектами, у которых нет родителей, обычно являются представления, табличные функции, хранимые процедуры, очереди обслуживания, встроенные табличные функции, таблицы и скалярные функции. С другой стороны, проверочные ограничения, ограничения по умолчанию, ограничения внешнего ключа, ограничения первичного ключа, триггеры sql и ограничения уникальности имеют родителей и не имеют независимого существования вне своего родительского объекта. Когда родитель удаляется, удаляются и дочерние объекты. Однако столбцы, параметры и индексы не считаются объектами, а являются только атрибутами объектов, поэтому могут отслеживаться только по их идентификаторам вместе с идентификаторами объектов связанных с ними объектов.
Зачем мы говорим про все это? Если вы поддерживаете единственную сборку, просто внося изменения в нее для каждого внутреннего выпуска, тогда вы можете сохранить только значения имен объектов, object_id, modify_date и parent_object_id где-нибудь в базе данных. Если вы делаете это для каждой интеграции, то у вас есть возможность перечислить все изменения между любыми двумя выпусками. Хм. Интересно, что изменилось с начала мая? (нажмите, нажмите, нажмите.) Хм. Хорошо, это не говорит вам, как это изменилось, но это сужает это! Другое использование: в начале рабочего дня вы сохраняете эти четыре столбца из таблицы sys.objects. В конце дня вы запускаете запрос, который идентифицирует изменения. Это позволяет вам записать все измененные объекты в систему контроля версий. С SMO (sqlserver) вы можете делать все это автоматически, как только вы разработали сценарий для этого.
Мы начали с этой процедуры, чтобы отслеживать изменения, вносимые в клоны, используя SQL Clone. В этом случае при создании образа легко сохранить файл json с необходимыми столбцами на диск и использовать его для проверки того, что изменилось с каждым клоном. Можно даже сообщить, когда были внесены изменения.
Табличное значение, необходимое для проведения сравнений, легко иллюстрируется с помощью Adventureworks, но, очевидно, подойдет любая база данных.
Вы можете создать таблицу для хранения этих файлов или сохранить их на диске. Мы покажем первый метод. Вот пример такой таблицы
|
CREATE TABLE DatabaseObjectReadings( Reading_id int IDENTITY, DatabaseName sysname NOT NULL, TheDateAndTime datetime NULL default GETDATE(), TheJSON NVARCHAR(MAX)) |
… Тогда вы можете взять «insta-запись» о состоянии базы данных (в данном случае Adventureworks2016)
|
INSERT INTO DatabaseObjectReadings (DatabaseName, TheJSON) SELECT 'Adventureworks2016' AS DatabaseName, (SELECT --the data you need from the test database's system views Coalesce(--if it is a parent, then add the schema name CASE WHEN parent_object_id=0 THEN Object_Schema_Name(object_id,Db_Id('AdventureWorks2016'))+'.' ELSE Object_Schema_Name(parent_object_id,Db_Id('AdventureWorks2016'))+'.'+ Object_Name(parent_Object_id,Db_Id('AdventureWorks2016'))+'.' END + name,'!'+name+'!' --otherwise add the parent object name ) AS [name], object_id, modify_date, parent_object_id FROM AdventureWorks2016.sys.objects WHERE is_ms_shipped = 0 FOR JSON AUTO) AS TheJSON |
... и ссылаться на них позже. Вы можете, если хотите, получить счет одних лишь модификаций
|
SELECT Count(*) FROM AdventureWorkstest.sys.objects new LEFT OUTER JOIN OPENJSON(( SELECT TOP 1 theJSON FROM DatabaseObjectReadings WHERE DatabaseName='AdventureWorks2016' ORDER BY TheDateAndTime desc )) WITH([object_id] int, modify_date datetime) AS original ON original.Object_ID = new.object_id AND original.Modify_Date = new.modify_date WHERE new.is_ms_shipped = 0 AND original.Object_ID IS NULL; |
Вы получите количество объектов базы данных, которые изменились. Тем не менее, вы наверняка хотели бы большего. Это включает в себя несколько сравнений между двумя табличными значениями объектов, поэтому вам нужно либо CTE, либо несколько табличных переменных.
Наилучшая производительность и универсальность достигается благодаря встроенной табличной функции. Здесь мы сначала создаем табличный тип и табличную функцию перед тем, как подготовить данные и передать их в функцию. Это, конечно может выглядеть немного громоздко, но зато быстро.
Мы взяли оригинал из версии, хранящейся в таблице DatabaseObjectReadings. Мы сделали копию AdventureWorks 2016 и провел несколько удалений и модификаций, чтобы протестировать ее. Естественно, вы можете сравнить две версии одной и той же сборки базы данных, когда у вас их нет, только запись соответствующих столбцов в sys.objects.
|
IF Object_Id('dbo.DatabaseChanges') IS NOT NULL DROP FUNCTION [dbo].[DatabaseChanges]
IF EXISTS (SELECT * FROM sys.types WHERE name LIKE 'DatabaseUserObjects') DROP TYPE [dbo].[DatabaseUserObjects] CREATE TYPE [dbo].[DatabaseUserObjects] AS TABLE ( [name] sysname, object_id int, modify_date Datetime, parent_object_id int )
go CREATE FUNCTION [dbo].[DatabaseChanges] ( @Original DatabaseUserObjects READONLY , @Comparison DatabaseUserObjects READONLY ) RETURNS TABLE AS RETURN ( SELECT Cloned.name, 'Added' AS action --all added base objects FROM @Comparison AS Cloned --get the modified LEFT OUTER JOIN @Original AS Original-- check if they are in the original ON Cloned.object_id = Original.object_id WHERE Original.object_id IS NULL AND cloned.parent_Object_id =0 --if they are base objects and they aren't in the original UNION ALL --OK but what if just child objects were added ... SELECT Clonedchildren.name, 'Added' -- to existing objects? FROM @Original AS Original-- check if they are in both the original INNER join @Comparison AS Cloned -- and also they are in the clone ON Cloned.name = Original.name --not renamed AND Cloned.object_id = Original.object_id --for ALL surviving objects inner JOIN @Comparison AS Clonedchildren--get all the chil objects ON Clonedchildren.parent_object_id =cloned.object_id LEFT OUTER JOIN -- and compare what child objects there were @Original OriginalChildren ON Originalchildren.object_id=ClonedChildren.object_id WHERE OriginalChildren.object_id IS NULL UNION ALL --all deleted objects but not their children SELECT Original.name, 'deleted' FROM @Original AS Original --all the objects in the original LEFT OUTER JOIN @Comparison AS Cloned --all the objects in the clone ON Cloned.name = Original.name AND Cloned.object_id = Original.object_id WHERE Cloned.object_id IS NULL AND original.parent_Object_id =0 --the original base objects that aren't in the clone UNION ALL --all child objects that were deleted where parents survive SELECT children.name, 'deleted' FROM @Original AS Original INNER join @Comparison AS Cloned ON Cloned.name = Original.name AND Cloned.object_id = Original.object_id --for ALL surviving objects inner JOIN @Original AS children ON children.parent_object_id =original.object_id LEFT OUTER JOIN @Comparison AS ClonedChildren ON children.object_id=ClonedChildren.object_id WHERE ClonedChildren.object_id IS NULL UNION ALL SELECT Original.name, CASE WHEN Cloned.name <> Original.name THEN 'renamed' WHEN Cloned.modify_date <> Original.modify_date THEN 'modified' ELSE '' END FROM @Original AS Original INNER JOIN @Comparison AS Cloned ON Cloned.object_id = Original.object_id WHERE Cloned.modify_date <> Original.modify_date OR Cloned.name <> Original.name ) GO |
Теперь мы можем использовать эту функцию, чтобы проверить разницу между любыми двумя сохраненными версиями базы данных или текущим состоянием базы данных.
|
DECLARE @original AS DatabaseUserObjects DECLARE @Changed AS DatabaseUserObjects
INSERT INTO @Changed SELECT --the data you need from the test database's system views Coalesce(--if it is a parent, then add the schema name CASE WHEN parent_object_id=0 THEN Object_Schema_Name(object_id,Db_Id('AdventureWorksTest'))+'.' ELSE Object_Schema_Name(parent_object_id,Db_Id('AdventureWorksTest'))+'.'+ Object_Name(parent_Object_id,Db_Id('AdventureWorksTest'))+'.' END + name,'!'+name+'!' --otherwise add the parent object name ) AS [name], object_id, modify_date, parent_object_id FROM AdventureWorksTest.sys.objects WHERE is_ms_shipped = 0
INSERT INTO @Original SELECT [name], object_id, modify_date, parent_object_id --the data you need from the original database's system views FROM OpenJson(( SELECT TOP 1 theJSON FROM DatabaseObjectReadings WHERE DatabaseName='AdventureWorks2016' ORDER BY TheDateAndTime desc )) WITH(name NVARCHAR(4000),[object_id] int, modify_date DATETIME, [parent_object_id] int) AS original
SELECT * FROM DatabaseChanges(@Original,@Changed) ORDER BY name |
Мы можем отсортировать это по-другому, но это наиболее очевидный способ, потому что он группирует элементы каждой схемы вместе и перечисляет каждый объект вместе с дочерним объектом, который изменился: за родителем следуют дочерние объекты.
Возможно, вы могли бы добиться большего, добавив подробности столбцов, индексов и других элементов, которые связаны с ними. Однако это не является целью, потому что мы просто хотим знать, на какой объект влияет модификация. Если вы хотите все это знать, вы можете купить SQL Comparison tool!