Хранимые процедуры и версионность

[sparkboy]

 
Решил поделится одним из вариантов по обеспечению версионного хранения исходного кода хранимых процедур для SQL Server 2005/2008

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

В этом посте описан один из подходов по обеспечению версионного хранения исходного кода хранимых процедур для SQL Server 2005/2008

Плюсы:

-Если нет Source Control Server - это единственный вариант

-Не нужно никаких дополнительных действий a la "Check in"

-Сведения можно получить прямо в ядре БД

Минусы:

-Накапливается большое количество изменений

-Для сравнения нужны сторонние утилиты

Этапы:

1. Создание таблицы, в которую будут заносится все изменения.

2. Занесение исходного кода всех хранимых процедур, функций и триггеров.

3. Создание DDL-триггера

Итак код:

--Создание таблицы для ведения всех изменений

if exists   
(    
        select *    
        from sys.tables t        
        where t.name like 'XStore_Procedure'    
)    
        drop table XStore_Procedure    
go

create table XStore_Procedure    
(    
        Id int identity (1,1 ) not null primary key,    
        Who varchar(64) not null default system_user,--пользователь из текущего сеанса    
        What int not null,--1 - insert, 2 - update, 3 - delete    
        DateWhen datetime default getdate(),    
        ProcName varchar(50) not null,--название программного блока    
        ProcSchema varchar(30) not null,--схема    
        ProcType varchar(50) not null,--тип    
        ProcCode varchar(max) null--код    
)

go

-- заполнение данными о текущем состоянии    
insert into XStore_Procedure(What,ProcName, ProcSchema,ProcType,ProcCode)    
select    
        1 as What,    
        o.name as ProcName,    
        s.name,    
        case        
                when o.type='FN' then 'FUNCTION'    
                when o.type='IF' then 'FUNCTION'    
                when o.type='TF' then 'FUNCTION'    
                when o.type='P' then 'PROCEDURE'    
                when o.type='TR' then 'TRIGGER'    
                else    
                        'NOT DEFINED'        
        end ProcType,    
        m.definition as ProcCode--объявление программного объекта    
from        
--идем по системным вьюшкам   
        sys.objects o --корневая    
                inner join        
                        sys.sql_modules m    
                                on o.object_id=m.object_id    
                                        inner join sys.schemas s    
                                                on        o.schema_id=s.schema_id    
go

--Создание триггера    
if        exists(    
select *        
from sys.triggers        
where parent_class_desc = 'DATABASE' and name = 'ChangeStoredProc')

drop trigger ChangeStoredProc on database

go

--DDL позволяет перехватывать большую часть всех событий, связанных с изменениями в схеме данных    
create trigger ChangeStoredProc on database            
for        
create_procedure, alter_procedure, drop_procedure,        
create_function, alter_function, drop_function,    
create_trigger, alter_trigger, drop_trigger    
as    
begin    
set nocount on    
--аргумент события, приходит в виде xml, из котого можно вытащить все значиния через XPath    
declare @Event xml    
set @Event=(select EventData())    
declare @EventType varchar(30)

if @Event.exist('/EVENT_INSTANCE/EventType')=1    
                set @EventType=@Event.value('data(/EVENT_INSTANCE/EventType)[1]', 'varchar(30)')

declare @op int    
set @op=0    
if @EventType like '%create%' set @op=1    
if @EventType like '%alter%' set @op=2    
if @EventType like '%drop%' set @op=3    
declare @LoginName varchar(50)    
if @Event.exist('/EVENT_INSTANCE/LoginName')=1    
                set @LoginName=@Event.value('data(/EVENT_INSTANCE/LoginName)[1]','varchar(50)')

declare @ProcName varchar(50)                    
if @Event.exist('/EVENT_INSTANCE/ObjectName')=1    
                set @ProcName=@Event.value('data(/EVENT_INSTANCE/ObjectName)[1]','varchar(50)')

declare @ProcSchema varchar(50)                    
if @Event.exist('/EVENT_INSTANCE/SchemaName')=1    
                set @ProcSchema=@Event.value('data(/EVENT_INSTANCE/SchemaName)[1]','varchar(50)')    
declare @ProcType varchar(50)                    
if @Event.exist('/EVENT_INSTANCE/ObjectType')=1    
                set @ProcType=@Event.value('data(/EVENT_INSTANCE/ObjectType)[1]','varchar(50)')    
declare @ProcCode varchar(max)    
if @Event.exist('/EVENT_INSTANCE/TSQLCommand')=1    
                set @ProcCode=@Event.value('data(/EVENT_INSTANCE/TSQLCommand)[1]','varchar(max)')    
insert into XStore_Procedure (Who, What, DateWhen, ProcName, ProcSchema, ProcType, ProcCode)    
                values(@LoginName, @op,getdate(),@ProcName,@ProcSchema, @ProcType,@ProcCode)    
end    
go    
--Ну и после можно протестировать полученный механизм    
--create procedure Test        
--as        
--begin    
--        print 'Test'        
--end    
--go    
--drop procedure Test    
--go    
--create table Test (Id int, value varchar(max))    
--go    
--create trigger TestTrig on Test    
--after insert, update, delete    
--as    
--begin    
--        print 'Test trigger!'    
--end    
--go

--drop table Test    
--go    
--create function Test (@a int)    
--returns int    
--as    
--begin    
--        return @a    
--end   
--go   
--drop function Test   
--go   
--select * from        XStore_Procedure   
--go