Реализация самоподдерживающегося кода

добавлено: 06 май 12
понравилось:0
просмотров: 1968
комментов: 5

теги:

Автор: Sergey Zenzinov

Статья написана довольно давно, но с заведением блога вышла задержка, поэтому была опубликована в форуме. А потом руки не доходили перенести. Дошли :)

При разработке обычно используются стандартные шаблоны процедур. Например, все процедуры должны логировать значения параметров в случае ошибки.
declare @trancount int = @@trancount;
declare @options int = @@options;
declare @params xml = (
    select
        @param1 as param1
        , @param2 as param2
        , @trancount as trancount
        , @options as options
    for xml raw, type
);
begin try
    -- Main code
end try
begin catch
    exec log_error @params = @params;
end catch;


Этот подход обеспечивает гибкость, однако при изменении набора параметров, это необходимо поддерживать, что, к сожалению, не всегда происходит.

Был разработан подход, позволяющий делать эти изменения автоматически. В текст процедуры вносятся комментарии, содержащие теги начала и конца шаблона. Далее, в базе данных создается триггер, отслеживающий изменения процедур, затем для измененных процедур запускается обработчик, который заменяет шаблоны на заготовки. Я не буду приводить ни текст триггера, ни собственно обработку, т.к. это можно сделать очень многими способами (с использованием Service Broker, таблица со статусами процедур и тд). Единственное, что следует отметить, что обработчик должен запускаться под специально созданным пользователем, чтобы избежать повторные обработки. Мы назвали его imp (чертенок)

create user imp without login with default_schema=dbo;
exec sp_addrolemember 'db_ddladmin', 'imp';


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

Далее нам необходимо создать шаблоны для обработки - обозначить тэги и функции, возвращающие текст-замену

if schema_id('dev') is null
    exec ('create schema dev authorization dbo');
go
 
if object_id(N'dev.templates') is null
    create table dev.templates(
        templateID int identity(1,1) not null
        , start_tag nvarchar(100) null
        , end_tag nvarchar(100) null
        , template_function sysname not null
        , constraint PK_templates primary key (templateID)
    ) 
go
 
insert into dev.templates (
    start_tag
    , end_tag
    , template_function
)
values
    ('<SetParams>', '</SetParams>', 'dev.get_parameters_template(@schema_name, @procedure_name)')
    , ('<Header>', '</Header>', 'dev.get_header_template(@schema_name, @procedure_name)')
    , ('<CheckTempTables>', '</CheckTempTables>', 'dev.get_missing_tables_template(@schema_name, @procedure_name)')


Исходный текст функций приведен ниже.

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

+ Процедура apply_templates
create procedure [dev].[apply_templates]
	@schema_name sysname
	, @procedure_name sysname
	, @debug bit = 0
with execute as 'imp'
as
begin
	set nocount on;

	create table #lines (
		id int identity(1,1)
		, line nvarchar(1000)
	);

	declare @object_full_name sysname = @schema_name + '.' + @procedure_name;

	begin try
		insert into #lines (line)
		exec sp_helptext @object_full_name;

		if @debug = 1
			select * from #lines;

		declare @templateID int = 0;

		declare @lines_cur cursor;

		set @lines_cur = cursor 
		for	
		select	
			l.line
		from
			#lines l
		order by
			l.id;

		open @lines_cur;

		declare @line nvarchar(1000);

		while 1 = 1
		begin
			fetch next from @lines_cur into @line;

			if @@fetch_status <> 0
				break;

			if @templateID = 0
			begin
				select 
					@templateID = t.templateID
				from
					dev.templates t
 				where 1 = 1
					and dbo.Regex_Like(@line, '^\s*--\s*' + t.start_tag + '\s*$') = 1;

				continue;
			end;

			if @templateID > 0 and exists (
				select 
					t.templateID
				from
					dev.templates t
 				where 1 = 1
					and (
						dbo.Regex_Like(@line, '^\s*--\s*' + t.start_tag + '\s*$') = 1
						or
						dbo.Regex_Like(@line, '^\s*--\s*' + t.end_tag + '\s*$') = 1
						and	t.templateID <> @templateID
					)
			)
			begin
				raiserror('Nested tags!', 16, 1);
			end;

			if @templateID > 0 and exists (
				select 
					t.templateID
				from
					dev.templates t
 				where 1 = 1
					and t.templateID = @templateID
					and dbo.Regex_Like(@line, '^\s*--\s*' + t.end_tag + '\s*$') = 1
			)
			begin
				set @templateID = 0;
			end;


		end;

		close @lines_cur;
		deallocate @lines_cur;

		if @templateID > 0
			raiserror ('Unclosed tags!', 16, 1);

		declare @procedure_text nvarchar(max);
		select
			@procedure_text = txt.definition
		from
			sys.sql_modules txt
			inner join sys.objects obj
				on obj.object_id = txt.object_id
				and obj.name = @procedure_name
				and obj.schema_id = schema_id(@schema_name);

		set @procedure_text = dbo.Regex_Replace(
			@procedure_text
			, 'create\s+proc(edure)?\s+(\[?' + @schema_name + '\]?\.)?\[?' + @procedure_name + '\]?'
			, 'alter procedure ' + @object_full_name
		);
		if @debug = 1
			select @procedure_text; 

		declare @templates_cur cursor;

		declare 
			@start_tag	nvarchar(200)
			, @end_tag	nvarchar(200)
			, @template_function	sysname;


		set @templates_cur = cursor
		for
		select	
			start_tag
			, end_tag
			, 'select @replacement = ' + template_function
		from
			dev.templates;

		declare @replacement nvarchar(max);

		open @templates_cur;
		while 1 = 1
		begin
			fetch next from @templates_cur
			into
				@start_tag
				, @end_tag
				, @template_function;
			
			if @@fetch_status <> 0
				break;

			exec sp_executesql	
				@template_function
				, N'@schema_name sysname
				, @procedure_name sysname
				, @replacement nvarchar(max) output'
				, @schema_name
				, @procedure_name
				, @replacement output;
				
			if @debug = 1
				select @replacement;

			set @procedure_text = dbo.Regex_Replace(
				@procedure_text
				, '^(?<start>\s*--\s*' + @start_tag + ')(.*?)^(?<end>\s*--\s*' + @end_tag + ')'
				, '${start}
' + @replacement + '
${end}'
			)
			if @debug = 1
				print @procedure_text;	
		end;

		close @templates_cur;
		deallocate @templates_cur;

		if @debug = 1
			select @procedure_text p for xml raw, type;

		exec sp_executesql @procedure_text;
	end try
	begin catch
		-- do error logging
		select
			error_line() error_line
			, error_message() error_message;
	end catch
		
	drop table #lines;	 
end;


+ Функция сбора параметров
 
create function [dev].[get_parameters_template] (
	@schema_name		sysname
	, @procedure_name	sysname
)
returns nvarchar(max)
as
begin
	declare @sql nvarchar(max) = '';

	set @sql += '
	set nocount on;

	declare @trancount int = @@trancount;
	declare @options int = @@options;
	declare @params xml = (
		select
			@trancount trancount
			, @options options
			';

	select 
		@sql += dbo.Concatenate(', ' + 
			case 
				when type_name(sp.system_type_id) <> 'table type' 
					then sp.name + ' ' + quotename(replace(sp.name, '@', ''))
				else 
					'(select * from ' + sp.name + ' for xml raw, type) ' + quotename(replace(sp.name, '@', ''))
			end
			,  '
			'
		) 
	from 
		sys.parameters sp
		inner join sys.objects obj
			on obj.object_id = sp.object_id
	where 1 = 1
		and obj.name = @procedure_name
		and obj.schema_id = schema_id(@schema_name);

	set @sql += '
		for xml raw, type
	);'

	return @sql;
end;


+ Функция генерации тестового скрипта
 
create function [dev].[get_header_template] (
	@schema_name		sysname
	, @procedure_name	sysname
)
returns nvarchar(max)
as
begin
	declare @sql nvarchar(max) = '';


	select 
		@sql += dbo.Concatenate(
			'--	declare ' + sp.name + ' ' + type_name(sp.user_type_id) 
				+ case	
					when type_name(sp.user_type_id) in (
						'char'
						, 'nchar'
						, 'varchar'
						, 'nvarchar'
					)  
						then '(' + case when sp.max_length > 0 then convert(varchar(10), sp.max_length) else 'max' end + ')'
					when type_name(sp.user_type_id) in (
						'decimal'
					)  
						then '(' + convert(varchar(10), sp.precision) + ',' + convert(varchar(10), sp.scale) + ')'
					else ''
				end + 
				+ case 
					when type_name(sp.system_type_id) <> 'table type' then ' = null' else '' end + ';'
			,  '
'
		) 
	from 
		sys.parameters sp
		inner join sys.objects obj
			on obj.object_id = sp.object_id
	where 1 = 1
		and obj.name = @procedure_name
		and obj.schema_id = schema_id(@schema_name);

	set @sql += '

--	exec ' + @schema_name + '.' + @procedure_name + '
--		';
	select 
		@sql += dbo.Concatenate(
			sp.name + ' = ' + sp.name
			,  '
--		, '
		) 
	from 
		sys.parameters sp
		inner join sys.objects obj
			on obj.object_id = sp.object_id
	where 1 = 1
		and obj.name = @procedure_name
		and obj.schema_id = schema_id(@schema_name);

	return @sql;
end;


+ Функция для проверки на существование таблиц
 
create function [dev].[get_missing_tables_template] (
	@schema_name sysname
	, @procedure_name sysname
) returns nvarchar(max)
as
begin
	declare @code nvarchar(max);
	declare @sql nvarchar(max);

	select 
		@code = dbo.Regex_Replace(dbo.Regex_Replace(txt.definition, '--.*?$', ''), '/\*.*\*/', '')
	from
		sys.sql_modules txt
		inner join sys.objects obj
			on obj.object_id = txt.object_id
			and obj.name = @procedure_name
			and obj.schema_id = schema_id(@schema_name);

	with missing_tables(table_name) as (
		select distinct 
			dbo.Regex_Substring(match, '#\w+') 
		from 
			dbo.Regex_Matches(@code, '\s#\w+')
		except
		select 
			dbo.Regex_Substring(match, '#\w+')
		from
			dbo.Regex_Matches(@code, 'create\s+table\s+#\w+')
		except
		select 
			dbo.Regex_Substring(match, '#\w+')
		from
			dbo.Regex_Matches(@code, 'select(?:(?!select|insert|update|delete|merge|from|on).)*\s+into\s+#\w+')
	)
	select 
		@sql = dbo.Concatenate('if object_id(''tempdb..' + table_name + ''') is null
			raiserror(''The table ' + table_name + ' was not created!!!'', 16, 1);'
			, '
	')
	from
		missing_tables;

	return @sql;
end;


+ Функции для работы с регулярными выражениями
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text.RegularExpressions;
using System.Linq;
using System.Collections;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static bool Regex_Like([SqlFacet(MaxSize = -1)]string str, string pattern)
    {
        // Put your code here
        return Regex.IsMatch(str, pattern, RegexOptions.IgnoreCase);
    }

    [Microsoft.SqlServer.Server.SqlFunction]
    [return:SqlFacet(MaxSize = -1)]
    public static string Regex_Substring([SqlFacet(MaxSize = -1)]string str, string pattern)
    {
        // Put your code here
        return Regex.Match(str, pattern).Value;
    }
    [Microsoft.SqlServer.Server.SqlFunction]
    public static int Regex_Index([SqlFacet(MaxSize = -1)]string str, string pattern)
    {
        // Put your code here
        return Regex.Match(str, pattern).Index;
    }

    [Microsoft.SqlServer.Server.SqlFunction]
    [return:SqlFacet(MaxSize=-1)]
    public static string Regex_Replace([SqlFacet(MaxSize = -1)]string str, string pattern, [SqlFacet(MaxSize = -1)]string replacement)
    {
        // Put your code here
        return Regex.Replace(str, pattern, replacement, RegexOptions.IgnoreCase | RegexOptions.Multiline | RegexOptions.Singleline);
    }

    [SqlFunction(
        DataAccess=DataAccessKind.None
        , FillRowMethodName = "Regex_Matches_FillRow"
        , TableDefinition="match nvarchar(max)"
    )]
    public static IEnumerable Regex_Matches([SqlFacet(MaxSize = -1)]string str, string pattern)
    {
        return Regex.Matches(str, pattern).Cast<Match>();
    }

    public static void Regex_Matches_FillRow(object r, out SqlString match)
    {
        Match tmp = (Match)r;
        match = tmp.Value;
    }

};


+ Агрегатная функция конкатенации
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text;

[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(

    Format.UserDefined, /// Binary Serialization because of StringBuilder
    IsInvariantToOrder = false, /// order changes the result
    IsInvariantToNulls = true,  /// nulls don't change the result
    IsInvariantToDuplicates = false, /// duplicates change the result
    MaxByteSize = -1
    )]

public class Concatenate : IBinarySerialize
{
    private StringBuilder _accumulator;
    private string _delimiter;

    /// <summary>
    /// IsNull property
    /// </summary>
    public Boolean IsNull { get; private set; }

    public void Init()
    {
        _accumulator = new StringBuilder();
        _delimiter = string.Empty;
        this.IsNull = true;
    }

    public void Accumulate(SqlString Value, SqlString Delimiter)
    {
        if (!Delimiter.IsNull
            & Delimiter.Value.Length > 0)
        {
            _delimiter = Delimiter.Value; /// save for Merge
            if (_accumulator.Length > 0) _accumulator.Append(Delimiter.Value);

        }
        _accumulator.Append(Value.Value);
        if (Value.IsNull == false) this.IsNull = false;
    }
    /// <summary>
    /// Merge onto the end 
    /// </summary>
    /// <param name="Group"></param>
    public void Merge(Concatenate Group)
    {
        /// add the delimiter between strings
        if (_accumulator.Length > 0
            & Group._accumulator.Length > 0) _accumulator.Append(_delimiter);

        ///_accumulator += Group._accumulator;
        _accumulator.Append(Group._accumulator.ToString());

    }

    public SqlString Terminate()
    {
        // Put your code here
        return new SqlString(_accumulator.ToString());
    }

    /// <summary>
    /// deserialize from the reader to recreate the struct
    /// </summary>
    /// <param name="r">BinaryReader</param>
    void IBinarySerialize.Read(System.IO.BinaryReader r)
    {
        _delimiter = r.ReadString();
        _accumulator = new StringBuilder(r.ReadString());

        if (_accumulator.Length != 0) this.IsNull = false;
    }

    /// <summary>
    /// searialize the struct.
    /// </summary>
    /// <param name="w">BinaryWriter</param>
    void IBinarySerialize.Write(System.IO.BinaryWriter w)
    {
        w.Write(_delimiter);
        w.Write(_accumulator.ToString());
    }
}


Проверяем. Создаем тестовую процедуру
-- <Header>
-- </Header>
create procedure [dbo].[test_templates]
	@param1 int
	, @param2 varchar(100)
as
begin
	-- <SetParams>
           declare @params xml;
	-- </SetParams>

	begin try
		-- <CheckTempTables>
		-- </CheckTempTables>

	
		select @param1 param1 into #tmp;

		select * from #missing_table;
	end try
	begin catch
		exec log_error @params = @params;
	end catch;
end;


Применяем шаблоны
exec dev.apply_templates
    @schema_name = 'dbo'
    , @procedure_name = 'test_templates';


Проверяем результат!
-- <Header>
--	declare @param1 int = null;
--	declare @param2 varchar(100) = null;

--	exec dbo.test_templates
--		@param1 = @param1
--		, @param2 = @param2
-- </Header>
alter procedure [dbo].[test_templates]
	@param1 int
	, @param2 varchar(100)
as
begin
	-- <SetParams>

	set nocount on;

	declare @trancount int = @@trancount;
	declare @options int = @@options;
	declare @params xml = (
		select
			@trancount trancount
			, @options options
			, @param1 [param1]
			, @param2 [param2]
		for xml raw, type
	);
	-- </SetParams>

	begin try
		-- <CheckTempTables>
if object_id('tempdb..#missing_table') is null
			raiserror('The table #missing_table was not created!!!', 16, 1);
		-- </CheckTempTables>

	
		select @param1 param1 into #tmp;

		select * from #missing_table;
	end try
	begin catch
		exec log_error @params = @params;
	end catch;
end;

Комментарии


  • 07 мая 2012, 16:29 Andrey Sribnyak

    а Regex_Like где брать? ;-)

  • 07 мая 2012, 16:39 Sergey Zenzinov

    Хм, я прикладывал файлы, почему-то нет сейчас. Пока можно взять по ссылке на форум, потом попробую еще раз приаттачить

  • 07 мая 2012, 17:52 Andrey Sribnyak

    Ну и группа по видимому db_ddladmin ?

  • 07 мая 2012, 17:56 Andrey Sribnyak

    ну да... и что такое log_error ?

  • 07 мая 2012, 18:03 Sergey Zenzinov

    спасибо, поправил роль и текст процедуры (declare @params xml). Думаю, можно и без DDL admin обойтись - главное, чтоб были права на изменения процедур. log_error - процедура в моей базе. К статье имеет мало отношения, это же приведено просто в качестве примера. Можно сделать select error_line(), error_message(), @params для теста



Необходимо войти на сайт, чтобы оставлять комментарии