SQL Server中的版本控制

朱莉娅 ： 那么，昨天谁改变了我的程序？
勒莎 ： 不是我
马克西姆 ： 不是我
- 伙计们，我们可以拿到Git吗？
Seryozha ： 是时候了！
2个星期过去了...

朱莉娅 ： 伙计们？
- 尤尔，你没犯吗？
朱莉娅 ： 该死 （...

这就是一切的开始。 那么，每个字符和每一行都会提交什么？

还是所有这些都将单独发生？）在这一点上，他们开始浮现在脑海
添加了DDL触发器 ， 时间表和图片。 解决后，我们将版本存储在内部
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 

重要的是要记住时间表的局限性。

1. 创建它们后，您将无法将DDL命令应用于主表或历史表。 而且您不能删除时间表
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)) AS 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 Trigger帮助我们

重要！ 因为 版本表位于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版本低于2016，则您在这里 。 顺便说一句，我们现在正在使用此版本，但是它并不那么酷。

总结

我从来没有想过得出结论_gt; 和＆_lt; 而不是Sql字段的master.dbo.VersionControl表中的符号>和< 。 如果您可以提供帮助或有任何想法，我正在等待请求请求 。

谢谢您的时间，放星星，心和向上箭头。