Geekly articles weekly

Contrôle de version dans SQL Server

Julia : Alors, qui a changé ma procédure hier?
Lesha : pas moi
Maxim : pas moi
- Les gars, pouvons-nous obtenir Git?
Seryozha : il est grand temps!
2 semaines se sont écoulées ...

Julia : les gars?
- Yul, tu ne t'es pas engagé?
Julia : bon sang non (...

Voilà comment tout a commencé. Eh bien, quoi, chaque personnage et chaque ligne s'engagent?

Ou peut-être que tout cela se produira tout seul?) À ce stade, ils commencent à penser
Des déclencheurs DDL , une table temporelle et une image sont ajoutés. Résolu, nous allons stocker des versions à l'intérieur
SQL Server'a !)




Tout d'abord, créez des tables dans lesquelles les versions seront stockées

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

Il est important de garder à l'esprit les limites de la table temporelle.

  1. Après les avoir créés, vous ne pouvez pas appliquer de commandes DDL aux tables principales ou historiques. Et vous ne pouvez pas supprimer la table temporelle
  2. Vous ne pouvez pas modifier les données du tableau historique

La deuxième restriction nous convient, mais que faire de la première?

L'algorithme est le suivant:

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

Bien qu'il n'y ait pas encore d'index sur la table, remplissez-la avec nos procédures, fonctions, etc. marquées INIT , ce qui dans notre cas signifiera le placement initial

 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

Parce que les modifications apportées aux objets se produiront avec l' instruction UPDATE , et nous examinerons le plus souvent les versions par clé: la base de données, le schéma et le nom de l'objet, l'index supplie!

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

Tout est prêt pour commencer à stocker des versions et nous aidera avec ce déclencheur DDL

Important! Parce que les tables pour les versions sont dans la base de données master , après avoir créé un déclencheur, tous ceux qui n'ont pas les droits sur cette base de données ne peuvent pas modifier, créer ou supprimer des objets

 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

Et pour la commodité de l'utilisation de ce système, la procédure ci-dessous est proposée.

C'est simple à utiliser. Le préfixe sp_ nous aidera à accéder à la procédure sans spécifier la base de données et le schéma. Les paramètres sont remplis intuitivement. Vous pouvez spécifier uniquement la base de données et nous verrons les objets qui lui sont associés uniquement pendant tout le temps, mais vous pouvez également utiliser le schéma, l'objet lui-même et bien sûr la plage de temps pour laquelle des modifications ont été apportées.

 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

Voici des exemples d'utilisation de la procédure

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

Vous pouvez installer ce microframework à partir de mon référentiel , et si votre version de SQL Sever est plus récente que 2016, alors vous y êtes . Soit dit en passant, nous utilisons cette version maintenant, mais ce n'est pas si cool.

En conclusion


Je n'ai jamais réussi à vaincre la conclusion & _gt; et & _lt; au lieu des signes > et < de la table master.dbo.VersionControl par le champ Sql . Si vous pouvez nous aider ou si vous avez des idées, j'attends une demande de tirage .

Merci pour votre temps, mettez des étoiles, des cœurs et des flèches.

Source: https://habr.com/ru/post/fr419919/

More articles:


All Articles