Когда мы развертываем код на SQL Server, мы делаем это через один или несколько сценариев T-SQL. Независимо от того, создаются ли эти сценарии вручную или автоматически сгенерированы с помощью такого инструмента, как SSDT или SQL Compare, мы должны убедиться, что там, где это возможно, сценарии будут повторно запущены или легко сгенерированы. Что мы получаем при перезапускаемых скриптах, - так это способность удалять много неопределенностей из релизов и возможность быстрого восстановления после сбоя или ошибки, и это облегчает работу по эффективному выполнению релиза для человека, который это делает.

Идемпотент

Первое, что нам нужно сделать, - начать думать о написании сценариев, чтобы они были идемпотентными, что означает, что независимо от того, сколько раз вы что-либо делаете, вы всегда получаете тот же результат.

Это пример идемпотентного скрипта в T-SQL

if object_id('abc.def') is not null
 begin
   drop procedure abc.def
 end
GO
 create procedure abc.def
 as
   select 1
GO

Происходит то, что мы проверяем, существует ли хранимая процедура, и если да, то мы ее отбрасываем. Поэтому, когда мы переходим к инструкции create, мы знаем, что она будет успешной (при условии, что код компилируется).

Типы объектов

В SQL Server существуют разные типы объектов и различные типы изменений, которые мы можем с ними провести. Объекты верхнего уровня, такие как таблицы или хранимые процедуры, имеют запись в sys.objects и возвращают правильный object_id, когда вы вызываете object_id в базе данных, содержащей их. Другие изменения, такие как добавление столбца в таблицу, не могут быть проверены с помощью object_id, поскольку это не объект верхнего уровня.

Чтобы обрабатывать объекты верхнего уровня, нам нужно запросить базу данных, чтобы увидеть, существует ли объект, и мы действительно должны использовать аспекты INFORMATION_SCHEMA.

Например, если мы хотим увидеть, существует ли столбец с именем last_name в таблице person и, в случае его отсутствия, добавить его, мы можем сделать следующее:

 if not exists(
   select * from INFORMATION_SCHEMA.COLUMNS
     where TABLE_NAME = 'person' and COLUMN_NAME = 'last_name')
 begin
             
   alter table person
     add last_name varchar(200) null
 end

Далее, если мы хотим изменить определение столбца, тогда мы можем проверить, есть ли у нас необходимость делать это:

if not exists(
   select * from INFORMATION_SCHEMA.COLUMNS
     where TABLE_NAME = 'person' and COLUMN_NAME = 'last_name' and CHARACTER_MAXIMUM_LENGTH = 255)
   begin
               
     alter table person
       alter column last_name varchar(255) null
 end

Если мы используем эту проверку перед внесением каких-либо изменений, мы гарантируем, что мы сможем продолжать повторять сценарий, и мы получим те же результаты, и когда вы находитесь под давлением, зная, что вы можете повторно запустить любой скрипт, это станет для вас преимуществом, которое вы действительно оцените.

Ручные и автоматические скрипты

Когда мы используем инструменты для генерации скриптов, все, что генерирует инструмент, например, шаги create / alter, можно быстро регенерировать с помощью этих инструментов. Поэтому, если вы развернули половину скрипта, сгенерированного с помощью SQL Compare, лучше всего сделать следующее сравнение, а затем запустить обновленный скрипт. Даже если вы используете инструмент для создания сценариев развертывания, вы можете использовать сценарий T-SQL для развертывания ваших ссылочных данных.

Что такое ссылочные данные?

Это данные, которые находятся в вашей базе данных и не являются частью бизнес-данных, ценными данными. Ссылочные данные обычно используются для обеспечения ссылочной целостности, поэтому, если у вас есть таблица employee_type с employee_id из 1 с employee_type из «Full Time». Имея ссылочные данные, мы можем обеспечить ссылочную целостность и часто должны совпадать с теми же значениями, которые существуют в перечислениях кода.

Как мы можем убедиться в том, что ссылочные данные являются идемпотентными?

Самый простой способ убедиться в этом - использовать, пожалуй, самый сложный оператор DML - MERGE. Если мы посмотрим на нашу таблицу employee_type:

create table hr.employee_type(
   employee_type_id int identity(1,1) not null,
   employee_type varchar(12) not null,
   constraint [pk_employee_type] primary key clustered (employee_type_id)
 )

Чтобы сделать развертывания этой таблицы идепондентными,  можно удалить идентификатор в employee_type_id, особенно если вам нужно сопоставить тот же идентификатор, который существует в коде приложения. Если вы не удалите его, вам нужно снова включить и выключить идентификационную информацию, когда вы закончите/Это означает, что если вы не сделаете какую-либо блокировку таблицы или другого объекта, вы не сможете запустить два сценария развертывания одновременно, как если бы вы попали в классическое состояние гонки, например:

Сценарий 1 - включение идентификационной вставки

Сценарий  1 - запуск модификации данных

Сценарий 2 - включение идентификационной вставки

Сценарий 1 - выключение идентификационной вставки

Сценарий  2 - запуск модификации данных <- это не удастся

Если мы удалим идентификатор для этого столбца и так как мы вставляем известные значения, нам действительно не нужен столбец идентификации, который становится определением таблицы:

create table hr.employee_type(
   employee_type_id int not null,
   employee_type varchar(12) not null,
   constraint [pk_employee_type] primary key clustered (employee_type_id)
 )

Конечно, чтобы удалить идентификатор из существующей системы, мы можем сделать это идемпотентным образом:

begin tran
 --does the column exist with an identity column
if exists (select * from sys.columns where object_id = object_id('hr.employee_type') and name ='employee_type_id' and is_identity = 1)
begin
 --add temp column - in a separate batch so the 2nd batch compiles
 alter table hr.employee_type
   add type_id_temp int null;

end
go

 --has the temp column been created?
if exists(select * from sys.columns where object_id = object_id('hr.employee_type') and name ='type_id_temp')
begin
 
 exec sp_executesql N'update hr.employee_type set type_id_temp = employee_type_id;';

 --if the primary key is still there then remove it
 if exists(select * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS where TABLE_NAME = 'employee_type' and CONSTRAINT_NAME = 'pk_employee_type')
 begin
   alter table hr.employee_type
     drop constraint [pk_employee_type];
 end

 --re-add constraint on out new column, make it not null first
 if exists(select * from [INFORMATION_SCHEMA].[COLUMNS] where TABLE_NAME = 'employee_type' and COLUMN_NAME = 'type_id_temp' and IS_NULLABLE = 'YES')
 begin
 alter table hr.employee_type
   alter column type_id_temp int not null;
 end

 --drop the original identity column
 if 2 = (select count(*) from [INFORMATION_SCHEMA].[COLUMNS] where TABLE_NAME = 'employee_type' and COLUMN_NAME in ('type_id_temp', 'employee_type_id'))
 begin
 alter table hr.employee_type
     drop column employee_type_id;
 end

 --rename the temp columnt to the original name
 if exists (select * from [INFORMATION_SCHEMA].[COLUMNS] where TABLE_NAME = 'employee_type' and COLUMN_NAME = 'type_id_temp')
 begin
   exec sp_rename 'hr.employee_type.type_id_temp', 'employee_type_id', 'COLUMN';
 end

 --put the primary key constraint back on
 if not exists(select * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS where TABLE_NAME = 'employee_type' and CONSTRAINT_NAME = 'pk_employee_type')
 begin
   alter table hr.employee_type
     add constraint [pk_employee_type] primary key clustered (employee_type_id);
 end

end

commit tran

Теперь у нас есть таблица без ограничения идентификатора, и мы можем использовать оператор MERGE без необходимости включать вставки идентификаторов:

SET NOCOUNT ON

MERGE INTO [hr].[employee_type] AS Target
USING (VALUES
 ('Full Time',1)
,('Contractor',2)
,('Part Time',3)
) AS Source ([employee_type],[employee_type_id]) ON (Target.[employee_type_id] = Source.[employee_type_id])
WHEN MATCHED AND (
 NULLIF(Source.[employee_type], Target.[employee_type]) IS NOT NULL OR NULLIF(Target.[employee_type], Source.[employee_type]) IS NOT NULL) THEN

UPDATE SET
 [employee_type] = Source.[employee_type]
WHEN NOT MATCHED BY TARGET THEN

INSERT([employee_type],[employee_type_id])
VALUES(Source.[employee_type],Source.[employee_type_id])

WHEN NOT MATCHED BY SOURCE THEN
DELETE;
GO

Альтернативой использованию оператора MERGE могло бы быть наличие 3 отдельных операторов: одного для вставки любых отсутствующих строк, другого - для обновления всех строк, чтобы убедиться, что имена одинаковые - возможно, вы хотите переименовать «Full Time» в « Permanent ", а третий оператор-  для удаления любых строк, которые были удалены из вашего кода. Вам также нужно будет сделать что-то, чтобы убедиться, что утверждения являются идемпотентными, поэтому либо используйте if exists, либо присоедините к данным в скрипте против живой таблицы, но это довольно трудоемко, поэтому лучше все же использовать оператор MERGE.

Написание сценариев развертывания или внесение изменений в базы данных SQL Server часто значительно проще для человека или инструмента, выполняющего релиз, в случае возможности повторного их запуска, или, по крайней мере, повторного генерирования нового скрипта, который может быть повторно запущен.

Выработайте привычку делать ваши сценарии идемпотентными, и вы проложите себе путь более надежного развертывания.

No Comments