Лёша: не я
Максим: не я
— Ребят, может Git заведём ?
Серёжа: давно пора!
прошло 2 недели…
Юля: ребяяят?
— Юль, а ты не коммитила?
Юля:
Вот так всё и началось. Ну а что, каждый символ и каждую строчку коммитить?
А может всё это будет происходить само?) На этом моменте в голову начинают приходить
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
- После их создания вы не можете применять DDL команды ни к основной, ни к исторической таблицам. И нельзя удалять Temporal table
- Нельзя изменять данные в исторической таблице
Второе ограничение нам подходит, а что делать с первым?
Алгоритм такой:
-- Для начала отключаем версионирование на основной таблице
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-Триггер
Важно! Т.к. таблицы для версий находятся в базе данных 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 .
Спасибо что уделили время, ставьте звёздочки, сердечки и стрелочки вверх.
Комментарии (24)
lair
11.08.2018 21:57+3Простите, а какую задачу вы решаете, и почему вам не подходят традиционные системы управления версиями?
lestvt Автор
11.08.2018 23:08-4К сожалению не все коммитят абсолютно все изменения в гит. Данная реализация решает эту задачу
lair
11.08.2018 23:35+5А еще эту задачу решает реализация "изменения попадают в БД только из гита". Потому что в гите-то они все равно должны быть.
lestvt Автор
11.08.2018 23:43-1«Контроль версий внутри Sql server'a» != «Бест практис версионирования объектов БД»
epee
13.08.2018 18:43для DEV (а иногда и TEST) среды это далеко не всегда вариант
решение автора не отменяет использование GIT (или его альтернативы)
из моего опыта — вариант автора использовался на DEV\TEST среде что найти виновного в поломке, ибо DEV и TEST базы были в единственных экземплярах и если что-то существенное ломалось могло затронуть работу многих
ну все что было на проде соответствовало тому что лежит в master гитаlair
13.08.2018 21:19ибо DEV и TEST базы были в единственных экземплярах и если что-то существенное ломалось могло затронуть работу многих
Это очень, очень, очень печальная история (это говорит человек, который в таком режиме работал и бросил).
VolCh
14.08.2018 06:53По факту это очень часто встречается. При том, на продакшене может быть настроено всё по лучшим практикам, но вот ресурсы на дев и прочие стейджинги выделяются по остаточному принципу. Как вычислительные ресурсы, так и человеческие. :( И если по проблемам на продакшене потом часто проводится анализ причин и принятие комплекса технических и административных мер, минимизирующих повторение инцидентов такого типа, то на дев средах в лучшем случае принимаются минимальные организационные меры типа «кто ещё раз свои миграции не откатит при уходе со своей ветки — премии не получит».
lair
14.08.2018 10:45То, что это часто встречается, не делает это менее печальной историей (а может быть и наоборот).
epee
14.08.2018 10:26честно говоря чего я уже не насмотрелся за 13 лет, удивить и испугать меня сложно :)))
конкретно в том случае были большие сложности с поддержанием более чем одной DEV\TEST базы — приложение было сложным монолитом с огромным числом логики в базе, позднее его распилили на микросервисную архитектуру, но почему-то проще оно от этого не стало :)))
вот и жили вот так, понятное дело что такое себе удовольствие, но со временем привыкаешь и учишься по быстрому разгребать случаи когда чье-то неудачное изменение в базе становится проблемой для многих
ikovrigin
11.08.2018 22:17+6По-моему правильное решение выглядит так.
Лёша: ребята кто то видел Юлю?
Максим: она не коммитила…
Серёжа: давно пора!
Imbecile
12.08.2018 00:50Проблемы со структурой базы и хранимками легко решаются. А вот что делать с версионностью миграций данных? Да ещё и в определённой последовательностью с dml запросами.
xRay
12.08.2018 12:19
Stepanho
12.08.2018 16:07На оракле столкнулся с такой проблемой, решаю так (+git):
https://github.com/stepanho/ora-sourcecontrol
BalinTomsk
13.08.2018 06:17У вас абстрактный конь в ваккуме или есть продукт?
Обычно есть до десятка ветвей продукта — типа sandbox, main, version 2, subversion 2.1, subversion 1.0, service pack 1.1, feature pack 1.1a, custom user pack 1.1.aSiemens, и так далее.
У вас тикеты есть? Типа там feаture request, баги? С номерами сроками, -pre-review, post-review?
Unit tests, functional tests, load tests,…
Или у вас хуяк-хуяк и в продакшн?
na9ort
Статья интересная, но ещё более хороший пример, как делать не надо. По факту Вы придумали свою костыльно-велосипедную систему контроля версий. Может в качестве запасного (дублирующего) средства и пойдёт, но в таком случае в будущем Вы столкнётесь с рассинхронизацией пакетов в Ваших таблицах и в основной системе контроля версий. В общем статья хорошая, идея так себе. Заведите лучше аккаунт на гитхабе и там храните Ваши процедуры. Целее будут.
Dm1tr1ch
Как контроль версий на стороне ПО поможет понять если кто-то зашёл в базу и поменял что-то в объектах? Мы живёл не в идеальном мире и такое происходит сплошь и рядом и далеко не всегда это вредительство, часто это исправления на лету, так как не всё оттестировали, а править надо. Так же можно поправить много объектов и забыть что правил если чинил аварию, а этот механизм поможет понять что и кто изменил и отразить это уже в контроле версий приложения.
slonpts
После того, как один мой коллега (но не я) разок во время аврала выполнил «DELETE FROM my_table» вместо «DELETE FROM my_table WHERE ...» (IDE позволяет выделить часть кода и выполнить только ее), стали все скрипты гонять как миграции БД, даже если все горит.
А кто руками что-то правил — просили не делать и показывали результаты нарушения техники безопасности работы с production.
Сделали 3 инстанса БД — production, qa, dev.
qa — копия production (на несколько дней/недель отстает).
dev — схема точно такая же, но данных меньше (скажем, не 100М строк, а 10К). Перед каждым прогоном автоматически восстанавливается из бэкапа.
Пишешь скрипт, тестишь руками на dev. Когда готово — гоняешь на dev уже полностью, причем начальное состояние dev каждый раз одно и то же.
Если есть время — гоняешь еще на qa, если все горит — запускаешь на production и держишь кулачки.
DSolodukhin
Контроль версий тут конечно не поможет, здесь помогут массовые расстрелы, казни и прочие репрессии.
Уже правильно советовали, все изменения в БД через миграции, миграции в VCS. У разработчиков и тестеров не должно быть доступа к боевым базам, плюс, по-хорошему, у каждого разработчика своя копия БД. Всё это дисциплинирует, и разработчики забывают, что можно руками что-то там править в БД.
VolCh
Кроме разработчиков бывают администраторы.