Kontrol versi di dalam SQL Server

Julia : Jadi, siapa yang mengubah prosedur saya kemarin?
Lesha : bukan aku
Maxim : bukan aku
- Bisakah kita mendapatkan Git?
Seryozha : sudah saatnya!
2 minggu telah berlalu ...

Julia : teman-teman?
- Yul, bukankah kamu berkomitmen?
Julia : sial tidak (...

Begitulah semuanya dimulai. Nah, apa, setiap karakter dan setiap baris komit?

Atau mungkin semua ini akan terjadi dengan sendirinya?) Pada titik ini, mereka mulai datang ke pikiran
Pemicu DDL , tabel temporal dan gambar ditambahkan. Diselesaikan, kami akan menyimpan versi di dalamnya
SQL Server'a !)




Pertama, buat tabel di mana versi akan disimpan

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 

Penting untuk mengingat keterbatasan dari tabel temporal.

1. Setelah membuatnya, Anda tidak bisa menerapkan perintah DDL ke tabel utama atau historis. Dan Anda tidak dapat menghapus tabel Temporal
2. Anda tidak dapat mengubah data dalam tabel historis

Pembatasan kedua cocok untuk kita, tetapi apa yang harus dilakukan dengan yang pertama?

Algoritma adalah sebagai berikut:

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

Meskipun belum ada indeks di atas meja, isilah dengan prosedur, fungsi, dll, bertanda INIT , yang dalam kasus kami berarti penempatan awal

 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 

Karena perubahan pada objek akan terjadi dengan pernyataan UPDATE , dan kita akan paling sering melihat versi dengan kunci: database, skema dan nama objek, indeks memohon!

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

Semuanya siap untuk mulai menyimpan versi dan akan membantu kami dengan Pemicu DDL ini

Penting! Karena tabel untuk versi ada di database master , setelah membuat pemicu, semua orang yang tidak memiliki hak untuk database ini tidak dapat memodifikasi, membuat, atau menghapus objek

 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 

Dan untuk kenyamanan menggunakan sistem ini, prosedur di bawah ini diusulkan.

Ini mudah digunakan. Awalan sp_ akan membantu kami mengakses prosedur tanpa menentukan basis data dan skema. Parameter diisi secara intuitif. Anda hanya dapat menentukan basis data dan kami akan melihat objek yang hanya dikaitkan dengannya sepanjang waktu, tetapi Anda juga dapat menggunakan skema, objek itu sendiri, dan tentu saja rentang waktu perubahan yang dibuat.

 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 

Di bawah ini adalah contoh penggunaan prosedur

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

Anda dapat menginstal struktur mikro ini dari repositori saya, dan jika versi SQL Sever Anda lebih muda dari 2016, maka di sinilah Anda. Omong-omong, kami menggunakan versi ini sekarang, tetapi tidak terlalu keren.

Kesimpulannya

Saya tidak pernah berhasil mengalahkan kesimpulan & _gt; dan & _lt; alih-alih tanda > dan < dari tabel master.dbo.VersionControl oleh bidang Sql . Jika Anda dapat membantu dengan ini atau memiliki ide, saya sedang menunggu Permintaan Tarik .

Terima kasih atas waktu Anda, letakkan bintang, hati, dan panah atas.