28.04.2017 10:01
lestvt 14 3200 Источник
image

Все началось с того, что я начал писать стандарт оформления T-SQL для своей компании. В этой теме я остановлюсь на конструкции удаления объекта перед его созданием.

В нашей команде порядка двадцати SQL Ninja разработчиков и все описывают данную конструкцию по разному, например вот так:

IF OBJECT_ID('dbo.Function', 'TF') IS NOT NULL
	DROP FUNCTION dbo.Function;
GO
CREATE FUNCTION dbo.Function ..

Или так:

IF EXISTS (
    SELECT * 
    FROM sys.objects 
    WHERE name = 'Procedure'
        AND type = 'P' 
)
    DROP PROCEDURE dbo.Procedure;
GO
CREATE PROCEDURE dbo.Procedure ..

И даже так:

IF EXISTS (
    SELECT 1
    FROM sys.objects 
    WHERE object_id = OBJECT_ID(N'dbo.Function')
        AND type IN (N'FN', N'IF', N'TF', N'FS', N'FT')
)
    DROP FUNCTION dbo.Function;
GO
CREATE FUNCTION dbo.Function ..

А на StackOverflow больше всего лайков собрал вот такой вариант:

IF EXISTS (
    SELECT * FROM sysobjects WHERE id = object_id(N'function_name') 
    AND xtype IN (N'FN', N'IF', N'TF')
)
    DROP FUNCTION function_name
GO

Звезды пошли ко мне на встречу и наткнулся я на одном из SQL-сайтов на реализацию, которая по началу возмутила меня, но потом мне подсказали что с ней «так»:

IF OBJECT_ID('dbo.Function', 'TF') IS NULL
    EXEC('CREATE FUNCTION dbo.Function() RETURNS @t TABLE(i INT) BEGIN RETURN END');
GO
ALTER FUNCTION dbo.Function ..

Дело в том что если каждый раз делать DROP и CREATE, то удаляются права на объект, а еще объект может быть в репликации и при пересоздании, из неё он удалится тоже.

В общем мне понравился этот лямбда декоратор способ и я решил его инкапсулировать
в процедуру под названием dbo.antidrop.

У процедуры всего два аргумента, это имя объекта и его тип. Посмотреть тип своего объекта можно вот так:

SELECT type 
FROM sys.objects 
WHERE name = 'Name'

Вот как это будет выглядеть по итогу:

EXEC dbo.antidrop('dbo.Name', 'FN');
GO
ALTER FUNCTION dbo.Name ..

Ну и конечно же код самой процедуры:

IF OBJECT_ID('dbo.antidrop', 'P') IS NULL
    EXEC('CREATE PROC dbo.antidrop AS');
GO
CREATE PROC dbo.antidrop @name SYSNAME, @type SYSNAME
AS
BEGIN

    DECLARE @if_tf NVARCHAR(512) = '
        IF OBJECT_ID(''' + @name + ''', ''' + @type + ''') IS NULL
            EXEC(''CREATE FUNCTION ' + @name + '() RETURNS @t TABLE(i INT) BEGIN RETURN END'');
        GO
    ';
    DECLARE @fn NVARCHAR(512) = '
        IF OBJECT_ID(''' + @name + ''', ''' + @type + ''') IS NULL
            EXEC(''CREATE FUNCTION ' + @name + '(@i INT) RETURNS INT AS BEGIN RETURN @i + 1 END'');
        GO
    ';
    DECLARE @p NVARCHAR(512) = '
        IF OBJECT_ID(''' + @name + ''', ''' + @type + ''') IS NULL
            EXEC(''CREATE PROC ' + @name + 'AS'');
        GO
    ';
    DECLARE @v NVARCHAR(512) = '
        IF OBJECT_ID(''' + @name + ''', ''' + @type + ''') IS NULL
            EXEC(''CREATE VIEW ' + @name + ' AS SELECT 1 AS i'');
        GO
    ';

    IF @type in (N'IF', N'TF')
    BEGIN
        EXEC(@if_tf);
    END

    ELSE IF @type = N'FN'
    BEGIN
        EXEC(@fn);
    END
    
    ELSE IF @type = N'P'
    BEGIN
        EXEC(@p);
    END

    ELSE IF @type = N'V'
    BEGIN
        EXEC(@v);
    END

END
GO

Спасибо за внимание!
Поделиться с друзьями
-->

Комментарии (14)


  1. minamoto
    28.04.2017 13:43
    #10195880
    +1

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

    ну и по вашей процедуре замечания:

    1) Примечательно, что ее вы дропаете и создаете вместо использования аналогичного метода :)
    2) Для процедуры достаточно такого определения:

    EXEC('CREATE PROC [procName] AS');

    3) с IF, TF нужна получше обработка. Пример: у вас уже есть инлайновая табличная функция, вы переделываете ее в мультистейтмент. Своей процедуре вы передаете TF, но при этом внутри вы должны проверить и существование IF, и существование TF, иначе вы получите ошибку либо при первом, либо при повторном запуске. альтерить IF в TF и обратно можно, но проверять нужно оба варианта
    4) Вы вроде для Type и для Name забыли кавычки внутри сгенерированного кода.
    5) Внутри сгенерированного кода не надо писать GO — это разделитель между батчами в студии, а не инструкция SQL.
    6) Ну это мелочь — перед юникодной строкой надо ставить N.
    7) Вообще непонятно, зачем вам 4 переменных, и вы выбираете, какую из них выполнить. Можно же сильно все упростить.

    По итогу я бы превратил вашу процедуру в что-то такое:

    IF OBJECT_ID('dbo.antidrop', 'P') IS NOT NULL
    DROP PROC dbo.antidrop;
GO
CREATE PROC dbo.antidrop @name SYSNAME, @type SYSNAME
AS
BEGIN

	declare @sql nvarchar(max);

	select @sql =	N'if ' + 
					case when @type in ('IF', 'TF') then N'OBJECT_ID(''' + @name + N''', ''IF'') is null and OBJECT_ID(''' + @name + N''', ''TF'') is null' 
													else N'OBJECT_ID(''' + @name + N''', ''' + @type + N''') IS NULL'
							end + N'
	EXEC(''CREATE ' + 
					case @type	when 'IF' then N'FUNCTION'
								when 'TF' then N'FUNCTION'
								when 'FN' then N'FUNCTION'
								when 'P' then N'PROC'
								when 'V' then N'VIEW'
							end + 
					N' ' + @name + 
					case @type	when 'IF' then N'() RETURNS table as return (select 1 as i)'
								when 'TF' then N'() RETURNS table as return (select 1 as i)'
								when 'FN' then N'(@i INT) RETURNS INT AS begin return 1; end'
								when 'P' then N' as'
								when 'V' then N' AS SELECT 1 AS i'
							end + 
					';'');';

	exec(@sql);

END
GO


    1. lestvt
      02.05.2017 23:38
      #10200688

      Алексей, спасибо за развернутый комментарий.
      1. Исправил
      2. Исправил
      3. dbo.antidrop не планировалась как готовое решение, лишь пример на коленке
      4. Заметил еще до модерации, но редактировать было нельзя…
      5. Исправил

      По поводу 4х непонятнных переменных, хотелось чтобы суть была ясна начинающим разработчикам в том числе. Безусловно были динамические мысли. В общем было принято решение оставить более простенький вариант.


    1. Bronx
      03.05.2017 08:15
      #10200874

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

      А это вообще нормальная практика — менять базу на продакшене прямо под нагрузкой? Я не DBA, но почему-то считал, что у DBA есть процедуры деплоя изменений, гарантированно исключающие шаловливые ручонки на работающем продакшене.


      1. minamoto
        03.05.2017 09:59
        #10200988

        Ну как бы есть базы без технологических перерывов, работающие 24/7, и на них тоже надо устанавливать обновления, не останавливая работу. Для этого используются различные интересные техники. Особенно интересно обновлять высоконагруженные таблицы — например, добавлять туда колонки и заполнять их значениями. Изменение процедуры — это так — разумная предосторожность.


        1. Bronx
          03.05.2017 11:04
          #10201084

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


          1. minamoto
            03.05.2017 17:15
            #10201813

            Так это же база данных. Она одна на весь продакшен, поэтому и изменения надо накатывать сразу все.


  1. Ivan22
    28.04.2017 14:38
    #10196006
    +2

    Люблю синтаксис «CREATE OR REPLACE», чего и MS рекомендую. Дешево и сердито.


    1. kast218
      28.04.2017 19:45
      #10196654
      +1

      Ваши "молитвы" услышаны https://blogs.msdn.microsoft.com/sqlserverstorageengine/2016/11/17/create-or-alter-another-great-language-enhancement-in-sql-server-2016-sp1/, начиная с 2016 SP1 работает для функций, процедур, триггеров и представлений


      1. Danik-ik
        29.04.2017 12:19
        #10197298

        Ms давно (то есть недавно) в курсе, а вот потребители — увы… Я до сих пор вынужден поддержиавать sql 2000 (две тысячи ровно). И я впервые увидел эмуляцию create or alter, без дропа и без обработки ошибок на клиенте. Это так просто! Где были мои глаза?!!


        1. kast218
          30.04.2017 20:05
          #10198380

          Мне приходится иногда разбираться с Oracle 8 и тут уже тяжело сказать кто из них "круче".
          По поводу самой техники создания болванки и ее изменения — довольно известная вещь в среде SQL Server.
          Крайне рекомендую ознакомиться с отличной статьей https://habrahabr.ru/post/315142/ и просто шедевральной серией статьей Bad Habits Revival


    1. lestvt
      02.05.2017 23:41
      #10200694

      В общем Константин (kast218) уже ответил. Действительно данный функционал уже существует, но только начиная с версии SQL Server 2016
      CREATE OR ALTER


  1. alexhott
    30.04.2017 11:42
    #10198040

    Для таблиц есть интересный приём. Когда нужно целиком перетащить таблицу с одного сервера на другой. И при этом на целевом сервере таблица уже есть, и структура может отличаться.
    сначала тащим таблицу insert into table_new, если стащили успешно преименовываем и дропаем старую.


  1. OlegAxenow
    01.05.2017 10:11
    #10198634

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

    Если кто-то запустит скрипт на alter до создающего скрипта — желательно чтобы он упал, потому что, как следствие, стоит исправить что-то в процессе разработки или мыслительных процессах конкретного разработчика :)

    P.S. SQL Ninja, в контексте статьи — это кто? Они под покровом ночи делают незаметные для других изменения в БД? :)


  1. Ex_Soft
    01.05.2017 16:25
    #10198816

    if object_id(N'SmthProc', N'p') is not null
	set noexec on;
go

create procedure SmthProc
as
	set nocount on;
go

set noexec off;
go

alter procedure SmthProc
as
begin
	set nocount on

	print N'SmthProc'
end
go


    а так?