Niniejsza część cyklu o migracjach zmian należy do mojej ulubionej, którą wykorzystuję w swojej pracy najczęściej. Jest to związany przede wszystkim z tym, że piszę po prostu sporo procedur i innego kodu zamiast prostych zapytań 😉 Moim zdaniem jest to również najłatwiejszy w realizacji proces wyszukiwania różnic, który często piszę nawet na szybko zamiast wywoływać przygotowaną procedurę 🙂
O czym ogólnie będzie ta część? Głównie będziemy zajmować się tym co można ładnie nazwać modułami SQL, czyli tym wszystkim w naszych bazach co możemy wyposażyć w jakiś kod robiący cokolwiek z naszymi danymi, który sobie nazwiemy a potem gdzieś wykorzystamy. Wspierając się dokumentacją na stronie Microsoftu możemy wymienić wszystkie typy obiektów jakimi będziemy się dzisiaj zajmować:
- procedury składowane,
- procedury składowane filtra replikacji,
- widoki,
- triggery,
- funkcje skalarne,
- proste funkcje tabelaryczne,
- złożone funkcje tabelaryczne,
- reguły,
- obiekty domyślne jak np. ograniczenia również mogą być tutaj uwzględniane.
Oczywiście nie będziemy analizować dosłownie wszystkich z powyższych przypadków. Po prostu kod, który przygotujemy będzie w stanie wykrywać różnice pomiędzy obiektami tych właśnie typów.
Do zabawy tradycyjnie wykorzystamy bazy poligon oraz poligon_dev. Aby nie było wątpliwości z czego będziemy korzystać to utworzymy sobie wszystkie potrzebne obiekty od podstaw. Wszelkie różnice w kodzie, nawet literówki w nazwach, będą tutaj w pełni zamierzone 🙂 Aby niepotrzebnie nie komplikować przykładowych danych nie będę tworzył wszystkich obiektów, które normalnie warto by dodać (np. indeksy, klucze i inne powiązania).
use [master]
go
create login [TestUser] with password =N'Test123', default_database = [master], check_expiration = off, check_policy = off
go
use [poligon];
go
create user [TestUser] for login [TestUser]
go
create table [dbo].[pomieszczenia]
(
[idPomieszczenia] int identity (1, 1) primary key clustered,
[nazwa] nvarchar(100) not null,
[pietro] int not null
);
go
create table [dbo].[pomieszczeniaZlecenia]
(
[idZlecenia] int identity (1, 1) primary key clustered,
[idPomieszczenia] int not null,
[opis] nvarchar(256) not null
);
go
create procedure [dbo].[getPomieszczenia] as
begin
set nocount on;
select * from [dbo].[pomieszczenia];
end
go
create procedure [dbo].[getPomieszczeniaNaPietrze] @pietro int as
begin
set nocount on;
select * from [dbo].[pomieszczenia] where [pietro] = @pietro;
end
go
create trigger [dodawaniePomieszczenie]
on [dbo].[pomieszczenia]
after insert as
begin
insert into [dbo].[pomieszczenia_zlecenia] ([idPomieszczenia], [opis])
select [idPomieszczenia], N'Zamówić wyposażenie' as [opis] from [inserted];
end;
go
create trigger [likwidacjaPomieszczenia]
on [dbo].[pomieszczenia]
after delete as
begin
insert into [dbo].[pomieszczenia_zlecenia] ([idPomieszczenia], [opis])
select [idPomieszczenia], N'Usunąć wyposażenie' as [opis] from [deleted];
end;
go
create view [dbo].[widokZlecen] as
select [p].[pietro], [p].[nazwa] as [pomieszczenie], [pz].[opis] as [opisZlecenia]
from [dbo].[pomieszczeniaZlecenia] as [pz]
inner join [dbo].[pomieszczenia] as [p] on [p].[idPomieszczenia] = [pz].[idPomieszczenia];
go
create function [liczbaZlecenDlaPomieszczenia] (@idPomieszczenia int) returns int
with execute as 'TestUser'
as
begin
declare @ileZlecen int;
set @ileZlecen = ( select count(distinct [idZlecenia])
from [dbo].[pomieszczeniaZlecenia]
where [idPomieszczenia] = @idPomieszczenia );
return (@ileZlecen);
end
go
create function [zleceniaDlaPietra] (@pietro int)
returns table as return
(
select [pz].*
from [dbo].[pomieszczeniaZlecenia] as [pz]
inner join [dbo].[pomieszczenia] as [p]
on [p].[idPomieszczenia] = [pz].[idPomieszczenia]
where [p].[pietro] = @pietro
);
go
use [poligon_dev];
go
create user [TestUser] for login [TestUser]
go
create table [dbo].[pomieszczenia]
(
[idPomieszczenia] int identity (1, 1) primary key clustered,
[nazwa] nvarchar(100) not null,
[pietro] int not null
);
go
create table [dbo].[pomieszczeniaZlecenia]
(
[idZlecenia] int identity (1, 1) primary key clustered,
[idPomieszczenia] int not null,
[opis] nvarchar(256) not null
);
go
create procedure [dbo].[getPomieszczenia] as
begin
set nocount on;
select * from [dbo].[pomieszczenia];
end
go
create procedure [dbo].[getPomieszczeniaNaPietrze] @pietro int as
begin
set nocount on;
select * from [dbo].[pomieszczenia] where [pietro] = @pietro;
end
go
create trigger [dodawaniePomieszczenie]
on [dbo].[pomieszczenia]
after insert as
begin
set nocount on;
insert into [dbo].[pomieszczenia_zlecenia] ([idPomieszczenia], [opis])
select [idPomieszczenia], N'Zamówić wyposażenie do nowego pomieszczenia' as [opis]
from [inserted];
end;
go
create trigger [aktualizacjaPomieszczenia]
on [dbo].[pomieszczenia]
after update as
begin
set nocount on;
insert into [dbo].[pomieszczenia_zlecenia] ([idPomieszczenia], [opis])
select [idPomieszczenia], N'Zaktualizować opis wyposażenia' as [opis]
from [inserted];
end;
go
create trigger [likwidacjaPomieszczenia]
on [dbo].[pomieszczenia]
after delete as
begin
set nocount on;
insert into [dbo].[pomieszczenia_zlecenia] ([idPomieszczenia], [opis])
select [idPomieszczenia], N'Usunąć wyposażenie z likwidowanego pomieszczenia' as [opis]
from [deleted];
end;
go
create view [dbo].[widokZlecen] as
select [p].[pietro], [p].[nazwa] as [pomieszczenie], [pz].[opis] as [opisZlecenia]
from [dbo].[pomieszczeniaZlecenia] as [pz]
inner join [dbo].[pomieszczenia] as [p] on [pz].[idPomieszczenia] = [p].[idPomieszczenia];
go
create function [liczbaZlecenDlaPomieszczenia] (@idPomieszczenia int) returns int
with execute as owner as
begin
declare @ileZlecen int;
set @ileZlecen = (
select count(distinct [idZlecenia])
from [dbo].[pomieszczeniaZlecenia]
where [idPomieszczenia] = @idPomieszczenia
);
return (@ileZlecen);
end
go
create function [zleceniaDlaPietra] (@pietro int)
returns table as return
(
select [pz].*
from [dbo].[pomieszczeniaZlecenia] as [pz]
inner join [dbo].[pomieszczenia] as [p]
on [p].[idPomieszczenia] = [pz].[idPomieszczenia]
where [p].[pietro] = @pietro
);
go
Cała magia wyszukiwania różnic opierać się będzie o systemowy widok sys.sql_modules, którego opis znajduje się pod linkiem podanym wcześniej w niniejszym wpisie. Dlaczego jest to zadanie proste? Ponieważ widok zawiera kompletną definicję obiektu zawierającego kod. Pisząc kompletną mam na myśli kod T‑SQL, który stworzył obiekt. Przyjrzyjmy się zawartości tego widoku dla bazy poligon_dev.
select * from sys.sql_modules
Efektem zapytania będzie następujący widok. Jak widać w polu definition, nawet bez oglądania pełnych wartości, mamy tam faktycznie dostępny cały kod modułu.
Na nasze potrzeby wartościowe będą tylko trzy kolumny (chociaż dla pełnego porównania jak najbardziej możemy uwzględnić wszystkie), które w praktyce przydają się najbardziej w typowych rozwiązaniach. Chodzi o kolumny object_id, definition oraz execute_as_principal_id.
W przypadku tego ostatniego pola sprawa jest nieco bardziej złożona. Pole to zawiera albo identyfikator z widoku sys.database_principals, albo ma wartość NULL oznaczającą with execute as caller, albo zawiera wartość ‑2 oznaczającą with execute as owner. Ważne jest również to, iż nie każdy z elementów opisanych w sys.database_principals może być użyty w tym miejscu. Ograniczeniem są dozwolone wskazania dla klauzuli execute as opisanej w dokumentacji pod tym adresem. W przygotowanych danych przykładowych użyłem dwóch wartości, z których jedna to celowo utworzony użytkownik (będzie to widać w dalszej części wpisu).
Na pierwszy rzut oka może tego nie być widać, ale definicja zawiera dosłownie każdy znak jakiego użyliśmy do jego utworzenia. Każda spacja, znak nowej linii – wszystko tam jest. Dlaczego to takie ważne? Ponieważ porównując definicje nie analizujemy kodu tylko porównujemy dwa łańcuchy znaków. Jeśli różnią się choćby jednym znakiem (nawet spacją) to porównanie zawsze wykaże różnicę.
Spróbujmy przygotować teraz zapytanie, które pozyska nam wszystkie niezbędne dane z jednej bazy.
select [s].[name] as [schemat],
[o].[name] as [modul_nazwa],
[o].[type] as [modul_typ],
[o].[type_desc] as [modul_typ_nazwa],
[m].[definition] as [modul_definicja],
case when [m].[execute_as_principal_id] = -2 then 'owner'
when [p].[principal_id] is not null then [p].[name]
else 'caller'
end as [execute_as],
[p].[type] as [execute_as_type],
[p].[type_desc] as [execute_as_type_name]
from [sys].[sql_modules] as [m]
inner join [sys].[objects] as [o] on [o].[object_id] = [m].[object_id]
inner join [sys].[schemas] as [s] on [s].[schema_id] = [o].[schema_id]
left join [sys].[database_principals] as [p]
on [p].[principal_id] = [m].[execute_as_principal_id];
Efekt powyższego zapytania chciałbym pokazać na obu bazach oddzielnie ponieważ różnią się między sobą w polach związanych z klauzulą execute as.
Spróbujmy to teraz zestawić w formie porównania. Nasze zapytanie zapiszemy najpierw w podobnym stylu jak w poprzednich wpisach z tej serii.
with [moduly_prod] as ( select [s].[name] as [schemat],
[o].[name] as [modul_nazwa],
[o].[type] as [modul_typ],
[o].[type_desc] as [modul_typ_nazwa],
[m].[definition] as [modul_definicja],
case when [m].[execute_as_principal_id] = -2 then 'owner'
when [p].[principal_id] is not null then [p].[name]
else 'caller'
end as [execute_as],
[p].[type] as [execute_as_type],
[p].[type_desc] as [execute_as_type_name]
from [poligon].[sys].[sql_modules] as [m]
inner join [poligon].[sys].[objects] as [o] on [o].[object_id] = [m].[object_id]
inner join [poligon].[sys].[schemas] as [s] on [s].[schema_id] = [o].[schema_id]
left join [poligon].[sys].[database_principals] as [p]
on [p].[principal_id] = [m].[execute_as_principal_id]
),
[moduly_dev] as ( select [s].[name] as [schemat],
[o].[name] as [modul_nazwa],
[o].[type] as [modul_typ],
[o].[type_desc] as [modul_typ_nazwa],
[m].[definition] as [modul_definicja],
case when [m].[execute_as_principal_id] = -2 then 'owner'
when [p].[principal_id] is not null then [p].[name]
else 'caller'
end as [execute_as],
[p].[type] as [execute_as_type],
[p].[type_desc] as [execute_as_type_name]
from [poligon_dev].[sys].[sql_modules] as [m]
inner join [poligon_dev].[sys].[objects] as [o] on [o].[object_id] = [m].[object_id]
inner join [poligon_dev].[sys].[schemas] as [s] on [s].[schema_id] = [o].[schema_id]
left join [poligon_dev].[sys].[database_principals] as [p]
on [p].[principal_id] = [m].[execute_as_principal_id]
),
[cmp_prod] as ( select * from [moduly_prod] except select * from [moduly_dev] ),
[cmp_dev] as ( select * from [moduly_dev] except select * from [moduly_prod] )
select coalesce([p].[schemat], [d].[schemat]) as [schemat],
coalesce([p].[modul_nazwa], [d].[modul_nazwa]) as [modul_nazwa],
coalesce([p].[modul_typ], [d].[modul_typ]) as [modul_typ],
coalesce([p].[modul_typ_nazwa], [d].[modul_typ_nazwa]) as [modul_typ_nazwa],
[p].[modul_definicja] as [b1_definicja],
[p].[execute_as] as [b1_execute_as],
[p].[execute_as_type] as [b1_execute_as_type],
[p].[execute_as_type_name] as [b1_execute_as_type_name],
[d].[modul_definicja] as [b2_definicja],
[d].[execute_as] as [b2_execute_as],
[d].[execute_as_type] as [b2_execute_as_type],
[d].[execute_as_type_name] as [b2_execute_as_type_name]
from [cmp_prod] as [p]
full outer join [cmp_dev] as [d]
on [p].[schemat] = [d].[schemat] and [p].[modul_nazwa] = [d].[modul_nazwa] and [p].[modul_typ] = [d].[modul_typ]
Efektem będzie dosyć „szeroka” tabela, ale spróbujmy ją pokazać.
W poprzednich wpisach mniej więcej na tym etapie zmierzałem już do zakończenia wpisu. Tym razem chciałbym jednak zaproponować coś więcej. Chodzi o to, że przy porównywaniu wcześniejszych elementów dosyć łatwo można było się zorientować się w tym czym dane obiekty się różniły. Tutaj mamy jednak do czynienia z kolumnami zawierający dosyć złożony kod T‑SQL. Nie będziemy przecież porównywać sobie sami tych treści. Dlatego też chciałbym dodać jeszcze dodatkowe kolumny, które wskazywały by konkretne elementy, w których występują różnice. Zmodyfikujmy więc nasze zapytanie.
with [moduly_prod] as ( select [s].[name] as [schemat],
[o].[name] as [modul_nazwa],
[o].[type] as [modul_typ],
[o].[type_desc] as [modul_typ_nazwa],
[m].[definition] as [modul_definicja],
case when [m].[execute_as_principal_id] = -2 then 'owner'
when [p].[principal_id] is not null then [p].[name]
else 'caller'
end as [execute_as],
[p].[type] as [execute_as_type],
[p].[type_desc] as [execute_as_type_name]
from [poligon].[sys].[sql_modules] as [m]
inner join [poligon].[sys].[objects] as [o] on [o].[object_id] = [m].[object_id]
inner join [poligon].[sys].[schemas] as [s] on [s].[schema_id] = [o].[schema_id]
left join [poligon].[sys].[database_principals] as [p]
on [p].[principal_id] = [m].[execute_as_principal_id]
),
[moduly_dev] as ( select [s].[name] as [schemat],
[o].[name] as [modul_nazwa],
[o].[type] as [modul_typ],
[o].[type_desc] as [modul_typ_nazwa],
[m].[definition] as [modul_definicja],
case when [m].[execute_as_principal_id] = -2 then 'owner'
when [p].[principal_id] is not null then [p].[name]
else 'caller'
end as [execute_as],
[p].[type] as [execute_as_type],
[p].[type_desc] as [execute_as_type_name]
from [poligon_dev].[sys].[sql_modules] as [m]
inner join [poligon_dev].[sys].[objects] as [o] on [o].[object_id] = [m].[object_id]
inner join [poligon_dev].[sys].[schemas] as [s] on [s].[schema_id] = [o].[schema_id]
left join [poligon_dev].[sys].[database_principals] as [p]
on [p].[principal_id] = [m].[execute_as_principal_id]
),
[cmp_prod] as ( select * from [moduly_prod] except select * from [moduly_dev] ),
[cmp_dev] as ( select * from [moduly_dev] except select * from [moduly_prod] )
select coalesce([p].[schemat], [d].[schemat]) as [schemat],
coalesce([p].[modul_nazwa], [d].[modul_nazwa]) as [modul_nazwa],
coalesce([p].[modul_typ], [d].[modul_typ]) as [modul_typ],
coalesce([p].[modul_typ_nazwa], [d].[modul_typ_nazwa]) as [modul_typ_nazwa],
iif(coalesce([p].[schemat], '') != coalesce([d].[schemat], ''), 1, 0) as [diffSchemat],
iif(coalesce([p].[modul_nazwa], '') != coalesce([d].[modul_nazwa], ''), 1, 0) as [difModulNazwa],
iif(coalesce([p].[modul_typ], '') != coalesce([d].[modul_typ], ''), 1, 0) as [difModulTyp],
iif(coalesce([p].[modul_definicja], '') != coalesce([d].[modul_definicja], ''), 1, 0) as [diffDefinition],
iif(coalesce([p].[execute_as], '') != coalesce([d].[execute_as], ''), 1, 0) as [diffExecuteAs],
iif(coalesce([p].[execute_as_type], '') != coalesce([d].[execute_as_type], ''), 1, 0) as [diffExecuteAsType],
[p].[modul_definicja] as [b1_definicja],
[p].[execute_as] as [b1_execute_as],
[p].[execute_as_type] as [b1_execute_as_type],
[p].[execute_as_type_name] as [b1_execute_as_type_name],
[d].[modul_definicja] as [b2_definicja],
[d].[execute_as] as [b2_execute_as],
[d].[execute_as_type] as [b2_execute_as_type],
[d].[execute_as_type_name] as [b2_execute_as_type_name]
from [cmp_prod] as [p]
full outer join [cmp_dev] as [d]
on [p].[schemat] = [d].[schemat] and [p].[modul_nazwa] = [d].[modul_nazwa] and [p].[modul_typ] = [d].[modul_typ]
Dla wygody oznaczyłem nowe linie (których nazwy zaczynają się od diff). Ich rolą jest zidentyfikowanie kolumn, w których wystąpiły różnice. Jak widać na poniższym fragmencie efektu działania nowego kodu (tabela zrobiła się zbyt „szeroka” aby wygodnie pokazać ją w całości 😉 ) jeśli obiekt istnieje tylko w jednej tabeli to oznaczają się niemal wszystkie różnice (w przypadku triggera w ostatnim wierszu nie występuje różnica w typie obiektu klauzuli execute as – w obu przypadkach jest to bowiem NULL).
Podobne podejście można oczywiście zastosować we wszystkich innych porównaniach jakimi zajmowaliśmy się wcześniej 🙂
Gotowe rozwiązanie możemy tradycyjnie opakować sobie w wygodną procedurę i zapisać ją w bazie master. Trzymając się wcześniejszej konwencji nazewniczej nazwałem ją dbo.migracje_sprawdzModuly. Kod procedury można pobrać z mojego repozytorium na GitHubie. Wywołanie procedury przeprowadza się przy pomocy poniższego kodu (oczywiście trzeba wstawić odpowiednie nazwy baz danych).
-- Należy uważać na kolejność podawania nazw baz danych
exec master.dbo.migracje_sprawdzModuly 'poligon', 'poligon_dev';
W kolejnym wpisie zajmiemy się uprawnieniami użytkowników, o których wspominałem już przy okazji wpisu o rolach.
