Versionskontrolle in SQL Server

Julia : Also, wer hat gestern mein Verfahren geändert?
Lesha : nicht ich
Maxim : nicht ich
- Leute, können wir Git bekommen?
Seryozha : Es ist höchste Zeit!
2 Wochen sind vergangen ...

Julia : Leute?
- Yul, hast du dich nicht verpflichtet?
Julia : verdammt nein (...

So fing alles an. Nun, was, jedes Zeichen und jede Zeile begehen?

Oder passiert das alles vielleicht von alleine?) An diesem Punkt kommen sie in den Sinn
DDL-Trigger , Zeittabelle und Bild werden hinzugefügt. Gelöst werden wir Versionen darin speichern
SQL Server'a !)




Erstellen Sie zunächst Tabellen, in denen Versionen gespeichert werden

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 

Es ist wichtig, die Einschränkungen des Zeitplans zu berücksichtigen .

1. Nach dem Erstellen können Sie weder auf die Haupt- noch auf die Verlaufstabelle DDL- Befehle anwenden. Und Sie können die temporäre Tabelle nicht löschen
2. Sie können keine Daten in der Verlaufstabelle ändern

Die zweite Einschränkung passt zu uns, aber was tun mit der ersten?

Der Algorithmus ist wie folgt:

 --        ALTER TABLE dbo.VersionControl SET ( SYSTEM_VERSIONING = OFF ); /* -  */ --    : ALTER TABLE dbo.VersionControl SET ( SYSTEM_VERSIONING = ON ( HISTORY_TABLE = dbo.VersionControlHistory, DATA_CONSISTENCY_CHECK = OFF ); 

Obwohl die Tabelle noch keine Indizes enthält, füllen Sie sie mit unseren Prozeduren, Funktionen usw., die mit INIT gekennzeichnet sind. Dies bedeutet in unserem Fall die Erstplatzierung

 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 

Weil Änderungen an den Objekten werden mit der UPDATE-Anweisung vorgenommen , und wir werden die Versionen am häufigsten nach Schlüssel betrachten: die Datenbank, das Schema und den Namen des Objekts, der Index bittet!

 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); 

Alles ist bereit, Versionen zu speichern, und hilft uns bei diesem DDL-Trigger

Wichtig! Weil Tabellen für Versionen befinden sich in der Master- Datenbank. Nach dem Erstellen eines Triggers kann jeder, der keine Rechte an dieser Datenbank hat, keine Objekte ändern, erstellen oder löschen

 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 

Zur Vereinfachung der Verwendung dieses Systems wird das folgende Verfahren vorgeschlagen.

Es ist einfach zu bedienen. Das Präfix sp_ hilft uns beim Zugriff auf die Prozedur, ohne die Datenbank und das Schema anzugeben. Parameter werden intuitiv gefüllt. Sie können nur die Datenbank angeben und es werden nur Objekte angezeigt, die nur für die gesamte Zeit zugeordnet sind. Sie können jedoch auch das Schema, das Objekt selbst und natürlich den Zeitraum verwenden, für den Änderungen vorgenommen wurden.

 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 

Nachfolgend finden Sie Beispiele für die Verwendung des Verfahrens

 --        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'; 

Sie können dieses Mikroframework aus meinem Repository installieren. Wenn Ihre Version von SQL Server jünger als 2016 ist, sind Sie hier . Übrigens verwenden wir diese Version jetzt, aber sie ist nicht so cool.

Abschließend

Ich habe es nie geschafft, die Schlussfolgerung zu besiegen & _gt; und & _lt; anstelle der Zeichen > und < aus der Tabelle master.dbo.VersionControl im Feld SQL . Wenn Sie dabei helfen können oder Ideen haben, warte ich auf eine Pull-Anfrage .

Vielen Dank für Ihre Zeit, setzen Sie Sterne, Herzen und Pfeile.