Контроль версий внутри SQL Server’a

Юля: Так, кто вчера менял мою процедуру?
Лёша: не я
Максим: не я
— Ребят, может Git заведём ?
Серёжа: давно пора!
прошло 2 недели…

Юля: ребяяят?
— Юль, а ты не коммитила?
Юля: damn нет(…

Вот так всё и началось. Ну а что, каждый символ и каждую строчку коммитить?

А может всё это будет происходить само?) На этом моменте в голову начинают приходить
DDL-триггеры, Temporal table и картина складывается. Решено, будем хранить версии внутри
SQL Server'a !)

Для начала создаём таблицы в которых будут храниться версии

USE master
GO


-- Сначала создаем историческую таблицу
IF NOT EXISTS (
	SELECT 1 
	FROM sys.objects
	WHERE name = 'VersionControlHistory'
		AND type = 'U'
)
	CREATE TABLE dbo.VersionControlHistory(
		Id INT NOT NULL,
		Event sysname NOT NULL,
		Db sysname NOT NULL,
		Sch sysname NOT NULL,
		Object sysname NOT NULL,
		Sql XML NOT NULL,
		Login sysname NOT NULL,
		StartDate DATETIME2(0) NOT NULL,
		EndDate DATETIME2(0) NOT NULL
	)  ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO


-- А затем таблицу журнала изменений
IF NOT EXISTS (
	SELECT 1 
	FROM sys.objects
	WHERE name = 'VersionControl'
		AND type = 'U'
)
	CREATE TABLE dbo.VersionControl(
		Id INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_VersionControl 
			PRIMARY KEY NONCLUSTERED,
		Event sysname NOT NULL,
		Db sysname NOT NULL,
		Sch sysname NOT NULL,
		Object sysname NOT NULL,
		Sql XML NOT NULL,
		Login sysname NOT NULL,
		StartDate DATETIME2(0) GENERATED ALWAYS AS ROW START NOT NULL,
		EndDate DATETIME2(0) GENERATED ALWAYS AS ROW END NOT NULL,
		PERIOD FOR SYSTEM_TIME (StartDate, EndDate)
	) WITH ( 
		SYSTEM_VERSIONING = ON (
			HISTORY_TABLE = dbo.VersionControlHistory
		)
	)
GO

Важно помнить об ограничениях для Temporal table

1. После их создания вы не можете применять DDL команды ни к основной, ни к исторической таблицам. И нельзя удалять Temporal table
2. Нельзя изменять данные в исторической таблице

Второе ограничение нам подходит, а что делать с первым?

Алгоритм такой:

-- Для начала отключаем версионирование на основной таблице
ALTER TABLE dbo.VersionControl SET ( SYSTEM_VERSIONING = OFF );

/*
    что-то делаем
*/

--И снова включаем поддержку изменений:
ALTER TABLE dbo.VersionControl SET ( 
	SYSTEM_VERSIONING = ON ( 
		HISTORY_TABLE = dbo.VersionControlHistory, 
		DATA_CONSISTENCY_CHECK = OFF
	);

Пока на таблице еще нет индексов, заполним её нашими процедурами, функциями и пр. с пометкой INIT, что в нашем случае будет означать первичное размещение

DECLARE @query NVARCHAR(MAX),
	@template NVARCHAR(MAX) = N'
USE [db]

INSERT INTO MASTER.dbo.VersionControl WITH (TABLOCKX) (
	Event, Db, Sch, Object, Sql, Login
) 
	SELECT ''INIT'' AS Event,
		DB_NAME(),
		ss.name AS Sch,
		so.name AS Object,
		CONCAT(''<query><![CDATA['', sasm.definition, '']]></query>'' ),
		SUSER_SNAME() AS Login
	FROM sys.objects AS so
		JOIN sys.schemas AS ss ON ss.schema_id = so.schema_id
		JOIN sys.all_sql_modules AS sasm ON sasm.object_id = so.object_id
	WHERE so.is_ms_shipped = 0
		AND NOT EXISTS (
			SELECT 1
			FROM MASTER.dbo.VersionControl AS vc
			WHERE vc.Db = ''[db]''
				AND vc.Sch = ss.name
				AND vc.Object = so.name
			);
';
DECLARE @databases TABLE (rn INT, Name sysname);
	INSERT @databases (rn, Name)
		SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) rn, name
		FROM sys.databases
		WHERE owner_sid != 0x01;

DECLARE @i INT = 1, 
	@max INT = (SELECT MAX(rn) FROM @databases),
	@error NVARCHAR(128),
	@db sysname;

WHILE @i < @max BEGIN

	SELECT @query = REPLACE(@template, '[db]', Name),
		@db = Name
	FROM @databases
	WHERE rn = @i;

	BEGIN TRY
		EXECUTE sp_executesql @query;
		SET @i += 1;
		CONTINUE;
	END TRY
	BEGIN CATCH
		SET @error = CONCAT(
			'XML Parsing error. In this case that''s mean one of [', 
			@db, '] object is invalid for convert to XML'
		);
		PRINT @error;
		SET @i += 1;
		CONTINUE;
	END CATCH;

END;
GO

Т.к. изменения объектов будут происходить инструкцией UPDATE, а смотреть версии мы будем чаще всего по ключу: база данных, схема и имя объекта, напрашивается индекс!

IF NOT EXISTS (
	SELECT 1
	FROM sys.indexes
	WHERE name = 'IX_VersionControl_upd_key'
)
	CREATE UNIQUE NONCLUSTERED INDEX IX_VersionControl_upd_key 
		ON MASTER.dbo.VersionControl (Db, Sch, Object)
		INCLUDE (Sql, Event, Login);

Всё готово для того чтобы начать хранить версии и поможет нам в этом DDL-Триггер

Важно! Т.к. таблицы для версий находятся в базе данных master, после создания триггера, все у кого нет прав на эту базу, изменять, создавать и удалять объекты не смогут

IF EXISTS (
	SELECT 1
	FROM sys.server_triggers
	WHERE name = 'tr_VersionControl'
)
	DROP TRIGGER tr_VersionControl ON ALL SERVER
GO
CREATE TRIGGER tr_VersionControl ON ALL SERVER
--WITH ENCRYPTION -- по желанию
/*	Указываем отлавливаемые события
	полный список событий: 
		https://docs.microsoft.com/ru-ru/sql/relational-databases/triggers/ddl-events?view=sql-server-2017
*/
FOR
	CREATE_TABLE, ALTER_TABLE, DROP_TABLE,
	CREATE_VIEW, ALTER_VIEW, DROP_VIEW,
	CREATE_FUNCTION, ALTER_FUNCTION, DROP_FUNCTION,
	CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE,
	CREATE_ASSEMBLY, ALTER_ASSEMBLY, DROP_ASSEMBLY,
	CREATE_INDEX, ALTER_INDEX, DROP_INDEX,
	CREATE_TRIGGER, ALTER_TRIGGER, DROP_TRIGGER,
	RENAME
AS
BEGIN
	SET NOCOUNT ON;

	UPDATE vs
	SET vs.Event =  ev.EventType,
		vs.Sql = CONCAT('<query><!CDATA', ev.Sql, '></query>' ),
		vs.Login = ev.Login
	FROM MASTER.dbo.VersionControl AS vs
		JOIN (
			SELECT * 
			FROM ( VALUES (
				EVENTDATA().value(
		'(/EVENT_INSTANCE/EventType)[1]', 'NVARCHAR(128)'
				),
				EVENTDATA().value(
		'(/EVENT_INSTANCE/SchemaName)[1]', 'NVARCHAR(128)'
				),
				EVENTDATA().value(
		'(/EVENT_INSTANCE/ObjectName)[1]', 'NVARCHAR(128)'
				),
				EVENTDATA().value(
		'(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'NVARCHAR(MAX)'
				),
				EVENTDATA().value(
		'(/EVENT_INSTANCE/LoginName)[1]', 'NVARCHAR(128)'
				)
			)) AS Event (EventType, Sch, Object, Sql, Login ) 
		) ev ON vs.Db = DB_NAME()
			AND vs.Sch = ev.Sch
			AND vs.Object = ev.Object
	;

END
GO

А для удобства использования этой системы предлагается процедура ниже.

Пользоваться ей просто. Префикс sp_ поможет нам обращаться к процедуре без указания базы данных и схемы. Параметры заполняются интуитивно. Можно указать только базу данных и мы увидим объекты связанные только с ней за всё время, а можно и схему, и сам объект и конечно временной диапазон, за который были внесены изменения.

CREATE PROCEDURE dbo.sp_Vc 
	@db sysname = '%', 
	@sch sysname = '%',
	@obj sysname = '%',
	@from DATETIME2(0) = NULL,
	@to DATETIME2(0) = NULL

AS
BEGIN
	SET NOCOUNT ON;
	
	IF @from IS NULL AND @to IS NULL BEGIN
		SELECT *
		FROM master.dbo.VersionControl
		WHERE Db LIKE @db
			AND Sch LIKE @sch
			AND Object LIKE @obj
		ORDER BY StartDate DESC
	END
	ELSE BEGIN	
		SELECT *
		FROM master.dbo.VersionControl 
			FOR SYSTEM_TIME BETWEEN @from AND @to
		WHERE Db LIKE @db
			AND Sch LIKE @sch
			AND Object LIKE @obj
		ORDER BY StartDate DESC
	END

END
GO

Ниже примеры использования процедуры

-- вывод всей таблицы изменений за всё время
sp_Vc;

/* вывод изменений по определенной базе данных 
	за всё время */
sp_Vc 'dwh';

/* вывод изменений по определенной базе данных и схеме
	за всё время */
sp_Vc 'dwh', 'dbo';

/* вывод изменений по определенной базе данных, схеме
	и конкретному объекту за всё время */
sp_Vc 'dwh', 'dbo', 'MyObject';

/* вывод изменений по определенной базе данных, схеме,
	и конкретному объекту за период с 1-го по 9-е мая */
sp_Vc 'dwh', 'dbo', 'MyObject', '20180501 00:00:00', '20180509 00:00:00';

Установить этот микрофреймворк вы можете с моего репозитория, а если ваша версия SQL Sever'a младше 2016-й, тогда вам сюда. Кстати, этой версией мы сейчас и пользуемся, но она не такая классная.

В заключение

Мне так и не удалось победить вывод &_gt; и &_lt; вместо знаков > и < из таблицы master.dbo.VersionControl по полю Sql. Если ты можешь помочь с этим или у тебя есть идеи, жду Pull Request'a .

Спасибо что уделили время, ставьте звёздочки, сердечки и стрелочки вверх.

Автор: Лестат Ким

Источник