Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Реализация само-поддерживающегося кода  [new]
Sergey Zenzinov
Member

Откуда:
Сообщений: 2
Блог
Хотел тут блог завести, но заявку отправил - и молчок. Поэтому в другом месте, и на англ.. http://zenzinov.blogspot.com/2011/12/self-maintaining-code-approach.html
20 дек 11, 01:19    [11793192]     Ответить | Цитировать Сообщить модератору
 Re: Реализация само-поддерживающегося кода  [new]
Sergey Zenzinov
Member

Откуда:
Сообщений: 2
Блог
Перевод:

Очень часто при логировании используется общая процедура, которая принимает параметры процедуры, собранные в XML, например так
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;

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

Был разработан подход, позволяющий делать эти изменения автоматически. Достаточно просто внести в код небольшой шаблон
-- <SetParams>
declare @params xml; -- Well, it has to be declared
-- </SetParams>


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

create user imp without login with default_schema=dbo;
exec sp_addrolemember 'ddl_admin', '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)')


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

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

+ Процедура dev.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;


Проверяем. Создаем тестовую процедуру
-- <Header>
-- </Header>
create procedure 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>
CREATE procedure [dbo].[test_templates]
 @param1 int
 , @param2 varchar(100)
as
begin
 -- <SetParams>
 
 set nocount on;
 exec _Context.push @@procid;
 
 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;


+ Функция генерации шаблона для набора параметров
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;
        exec _Context.push @@procid;


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

PS Прошу прощения, при копировании из студии полетела разметка.
21 дек 11, 21:58    [11806035]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить