Skrypty Pre-deployment i Post-deployment w projekcie bazodanowym Visual Studio

Kontynuujemy naszą przygodę związaną z projektem bazodanowym w Visual Studio. W pierwszej części powiedzieliśmy sobie jak stworzyć projekt bazodanowy, dodawać do niego zmiany oraz publikować je na serwer docelowy. Dziś rozszerzymy nieco tę koncepcję i powiemy jak wykorzystać skrypty przedwdrożeniowe (Pre-deployment) oraz powdrożeniowe (Post-deployment) – zapraszam do lektury.

Wspomniane we wstępie skrypty to nic innego jak kod wykonywany przed głównym skryptem bazy danych oraz bezpośrednio po nim. W dużym skrócie wygląda to tak, że:

1. Generowany jest skrypt różnicowy pomiędzy projektem a bazą docelową.
2. Wykonywane są instrukcje zawarte w skrypcie pre-deployment.
3. Wykonywany jest skrypt wygenerowany w punkcie 1.
4. Wykonywane są instrukcje zawarte w skrypcie post-deployment.

Warto zwrócić uwagę na fakt, że punkty nr.1 oraz  3 są wykonywane zawsze w całości, a punkt nr 2 może wyglądać różnie w zależności od tego czy wykonujemy deployment całościowy (drop – create) czy też przyrostowy. Jest więc to bardzo ważne aby zrozumieć, że skrypt różnicowy nie jest “świadomy” tego co się dzieje w pre i post deployment.

Jeśli chodzi o wspomniane skrypty to możemy w ramach pojedynczego projektu posiadać maksymalnie po jednym skrypcie pre oraz post-deployment. Nic jednak nie stoi na przeszkodzie aby z jego poziomu wywoływać wiele instrukcji lub wywoływać inne skrypty, o tym jak to zrobić powiemy sobie w dalszej części artykułu.

W tym miejscu ktoś może zadać zasadne pytanie, a mianowicie po co nam tego typu skrypty. Zastosowań jest wiele m.in:

• przygotowanie danych i struktur do wykonania głównego skryptu bazy danych,
• kopiowanie danych do zapasowej tabeli i czyszczenie głównej tabeli aby uniknąć błędu “data loss may occur”,
• wypełnienie sztucznych słowników i wymiarów (np. wymiaru czasu),
• logowanie informacji o deploymencie,
• inne.

Zastosowań jest naprawdę wiele, a powyższe to tylko kilka z nich. Nie tracąc czasu przejdźmy do przykładów użycia omawianych funkcjonalności – na starcie stworzyłem projekt bazodanowy (o tym jak to zrobić pisałem tydzień temu – artykuł znajduje się tutaj). Projekt jest bardzo prosty i zawiera jedynie tabelę oraz widok w ramach schematu dbo:

O tym jak dodać foldery i obiekty nie będę się rozpisywał gdyż zrobiłem to w podanym wyżej artykule – zamieszczam jedynie skrypty na wypadek jakby ktoś chciał powtórzyć ćwiczenie:

CREATE TABLE [dbo].[CustomerType]
(
	[CustomerTypeId] INT NOT NULL PRIMARY KEY, 
    [CustomerType] NVARCHAR(50) NULL, 
    [ModificationDate] DATETIME NULL DEFAULT GETDATE(), 
    [IsDeleted] BIT NULL DEFAULT 0
)

CREATE VIEW [dbo].[vGetCustomerType]
AS 
SELECT 
	 [CustomerTypeId] I
	,[CustomerType]	
FROM [dbo].[CustomerType]
WHERE [IsDeleted] = 0;
GO

Przechodząc dalej nie pozostaje nam nic innego jak przejść do meritum, dla porządku dodałem następujące foldery aby móc w nich umieścić odpowiednie skrypty:

Zacznijmy od tworzenia skryptów dodamy je oczywiście z poziomu menu kontekstowego wybierając Add -> Script:

Po wybraniu powyższej opcji pojawi się okno wyboru odpowiedniego rodzaju skryptu, nas będą interesować pierwsze dwa:

Pozostałe dwa to zwykłe skrypty, które mogą być buildowane lub też nie (z praktycznego punktu widzenia Script (Build) oznacza, że kod w skrypcie jest definicją obiektów, które trafią do głównego skryptu do opublikowania).  Stworzyłem zatem po jednym Pre i Post Deployment skrypcie:

Skrypt PreDeployment jak sama nazwa wskazuje będzie wykonywał się przed właściwym skryptem deploymentu. Po jego otwarciu zobaczymy krótki komentarz informujący nas co tak naprawdę możemy w tym miejscu zrobić. W moim scenariuszu testowym będziemy czyścić dane jeśli tabela nie jest pusta. Do tego celu posłuży poniższy kod TSQL:

IF OBJECT_ID('dbo.CustomerType') IS NOT NULL
BEGIN
	IF EXISTS (SELECT TOP 1 * FROM dbo.CustomerType)
	BEGIN
	
		TRUNCATE TABLE dbo.CustomerType;
	
	END
END

Po co sprawdzam czy obiekt istnieje? To bardzo ważne żeby sobie uzmysłowić, że są sytuacje, że tej tabeli nie ma na serwerze docelowym więc nie będziemy mogli jej wyczyścić i dostaniemy błąd.  Może pojawić się pytanie jak to nie ma tabeli docelowej? Odpowiedź jest prosta np. podczas pierwszego deploymentu struktur w ogóle nie ma! Innym przykładem może być sytuacja, że jakiś deweloper chce sobie na maszynie deweloperskiej postawić struktury w całości. Zawsze powinniśmy myśleć w taki sposób aby nasze skrypty były uniwersalne i mogły być puszczane za każdym razem bez względu na stan środowiska docelowego.

Przechodząc do skryptu Post Deployment wygląda on następująco:

INSERT INTO dbo.CustomerType
(
     [CustomerTypeId]
	,[CustomerType]
)
VALUES
	 (0,'Unknown')
	,(1,'Company')
	,(2,'Person');
GO

Prosty INSERT wstawiający wartości do wyczyszczonej wcześniej tabeli. W taki sposób możemy wstawiać różnego rodzaju kategorie do tabel, których nie możemy załadować z systemu źródłowego. Jest to częsty wymóg szczególnie w przypadku projektów hurtowni danych. Oczywiście prezentowany przykład jest jedynie poglądowy i zamiast czyścić i na nowo wstawiać dane lepszym pomysłem byłoby użycie instrukcji MERGE i nieczyszczenie całej tabeli za każdymr azem jednakże na ten moment zostawimy to właśnie w takiej formie.

Przechodząc dalej nie pozostaje nam nic innego tylko wygenerować plik deploymentu czyli klikamy prawym przyciskiem myszy na projekt i wybieramy Publish, w moim przypadku okno publikacji wygląda następująco (nieco bardziej szczegółowo opisałem konfigurację profilu w poprzednim artykule):

Po kliknięciu Generate Script otrzymamy nasz kod, który zostanie wykonany na docelowej instancji. Jego główna część wygląda następująco:

IF OBJECT_ID('dbo.CustomerType') IS NOT NULL
BEGIN
	IF EXISTS (SELECT TOP 1 * FROM dbo.CustomerType)
	BEGIN
	
		TRUNCATE TABLE dbo.CustomerType;
	
	END
END
GO

GO
PRINT N'Creating [dbo].[CustomerType]...';


GO
CREATE TABLE [dbo].[CustomerType] (
    [CustomerTypeId]   INT           NOT NULL,
    [CustomerType]     NVARCHAR (50) NULL,
    [ModificationDate] DATETIME      NULL,
    [IsDeleted]        BIT           NULL,
    PRIMARY KEY CLUSTERED ([CustomerTypeId] ASC)
);


GO
PRINT N'Creating unnamed constraint on [dbo].[CustomerType]...';


GO
ALTER TABLE [dbo].[CustomerType]
    ADD DEFAULT GETDATE() FOR [ModificationDate];


GO
PRINT N'Creating unnamed constraint on [dbo].[CustomerType]...';


GO
ALTER TABLE [dbo].[CustomerType]
    ADD DEFAULT 0 FOR [IsDeleted];


GO
PRINT N'Creating [dbo].[vGetCustomerType]...';


GO
CREATE VIEW [dbo].[vGetCustomerType]
AS 
SELECT 
	 [CustomerTypeId] I
	,[CustomerType]	
FROM [dbo].[CustomerType]
WHERE [IsDeleted] = 0;
GO

INSERT INTO dbo.CustomerType
(
     [CustomerTypeId]
	,[CustomerType]
)
VALUES
	 (0,'Unknown')
	,(1,'Company')
	,(2,'Person');
GO

Pierwszy mój deployment nie tylko stworzy obiekty ale również całą bazę danych. Widzimy również jasno w którym momencie wykonuje się PreDeployment – wtedy tabeli CustomerType jeszcze nie ma dlatego też dodałem sprawdzenie czy coś takiego w ogóle istnieje. Następnie wywoływane są operacje DDL, które stworzą obiekty, a następnie PostDeployment uzupełniający tabelę. Po uruchomieniu skryptu mogę spróbować jeszcze raz wygenerować taki skrypt i wtedy zobaczę, że tabela CustomerType oraz towarzyszący jej widok nie zostaną utworzone ze względu na fakt, iż istnieją one na instancji docelowej, jednakże Pre/Post skrypty wykonywane są zawsze bez względu na strukturę docelowej bazy.

Po wykonaniu powyższego testu postanowiłem dodać nową tabelę o nazwie Customer z referencją do stworzonej wcześniej CustomerType. Jej definicja wygląda następująco:

CREATE TABLE [dbo].[Customer]
(
	[Id] INT NOT NULL PRIMARY KEY,
	[FirstName] NVARCHAR(50),
	[LastName] NVARCHAR(50),
	[FK_CustomerType_ID] INT,
	CONSTRAINT FK_CustomerType FOREIGN KEY (FK_CustomerType_ID) REFERENCES dbo.CustomerType(CustomerTypeID)
);
GO

Po wygenerowaniu skrypt różnicowy wygląda bardzo ciekawie:

IF OBJECT_ID('dbo.CustomerType') IS NOT NULL
BEGIN
	IF EXISTS (SELECT TOP 1 * FROM dbo.CustomerType)
	BEGIN
	
		TRUNCATE TABLE dbo.CustomerType;
	
	END
END
GO

GO
PRINT N'Creating [dbo].[Customer]...';


GO
CREATE TABLE [dbo].[Customer] (
    [Id]                 INT           NOT NULL,
    [FirstName]          NVARCHAR (50) NULL,
    [LastName]           NVARCHAR (50) NULL,
    [FK_CustomerType_ID] INT           NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);


GO
PRINT N'Creating [dbo].[FK_CustomerType]...';


GO
ALTER TABLE [dbo].[Customer] WITH NOCHECK
    ADD CONSTRAINT [FK_CustomerType] FOREIGN KEY ([FK_CustomerType_ID]) REFERENCES [dbo].[CustomerType] ([CustomerTypeId]);


GO
INSERT INTO dbo.CustomerType
(
     [CustomerTypeId]
	,[CustomerType]
)
VALUES
	 (0,'Unknown')
	,(1,'Company')
	,(2,'Person');
GO

GO
PRINT N'Checking existing data against newly created constraints';


GO
USE [$(DatabaseName)];


GO
ALTER TABLE [dbo].[Customer] WITH CHECK CHECK CONSTRAINT [FK_CustomerType];


GO
PRINT N'Update complete.';

Skrypty Pre oraz Post pozostały bez zmian i widzimy, że dodana została  nowa tabela oraz referencja klucza obcego z parametrem NOCHECK. Następnie na samym końcu już po wykonaniu PostDeployment skryptu constraint ten został włączony.

W tym miejscu powinna nam się zapalić czerwona lampka. Chodzi mianowicie o to, co się stanie jeśli ktoś doda dane do tabeli Customer, a my będziemy chcieli wrzucić kolejne zmiany?  Odpowiedzi na to pytanie udzielimy po raz kolejny na przykładzie. Powyższy skrypt możemy uruchomić i wszystko powinno zakończyć się bez problemów ponieważ tabela Customer na ten moment jest pusta. Dodajmy do niej dane używając wbudowanego w Visual Studio okna SQL Server Object Explorer o którym wstępnie chciałbym powiedzieć parę słów:

Okno to posiada dwie sekcje, pierwsza widoczna w górnej części posiada połączenia do instancji SQL Server (w tym do wbudowanej w Visual Studio lokalnej instancji localdb). Druga z kolei nazwana Projects umożliwia nam podgląd obiektów w ramach naszych projektów bazodanowych w sposób zbliżony do tego co widzimy w tradycyjnym eksploratorze obiektów Management Studio.

W ramach eksploratora serwera możemy standardowo pracować z bazą danych mniej więcej tak jak w przypadku wspomnianego Management Studio. Oczywiście brakuje niektórych funkcjonalności jednakże w moim odczuciu do typowej pracy deweloperskiej praktycznie mamy wszystko co potrzebne:

Eksplorator projektów z kolei pozwala podejrzeć obiekty znajdujące się w definicji naszych projektów w uporządkowany sposób grupujący obiekty według typu:

Jak możecie zauważyć powyżej eksplorator projektu umożliwia również dodawanie obiektów tak więc jeśli ktoś woli inicjować te operacje z tego poziomu to nic nie stoi na przeszkodzie. Przejdźmy zatem do eksploratora serwera i tam klikając prawym przyciskiem myszy na wybraną bazę danych wybrałem New Query i wpisałem poniższe polecenie INSERT:

INSERT INTO dbo.Customer(Id, FirstName, LastName, FK_CustomerType_ID )
VALUES(1, N'Adrian', N'Chodkowski', 2 );

W tym momencie tabela na serwerze docelowym posiada dane. Spróbujmy dokonać jeszcze jednej zmiany w projekcie, a mianowicie zmieńmy definicję kolumny FirstName w tabeli dbo.Customer z NVARCHAR(50) na NVARCHAR(40):

Po  wygenerowaniu skryptu różnicowego warto podejrzeć sobie zmiany, które Visual Studio będzie próbował wykonać – dokonamy tego w oknie Data Tools Operations klikając Preview:

Mamy tutaj  skondensowaną informację  o tym czy jakieś tabelę będę przebudowane, jakie operacje zostaną wykonane i czy któreś z nich mogą skutkować utratą danych:

** Highlights
     Tables that will be rebuilt
       None
     Clustered indexes that will be dropped
       None
     Clustered indexes that will be created
       None
     Possible data issues
       The type for column FirstName in table [dbo].[Customer] is currently  NVARCHAR (50) NULL but is being changed to 
         NVARCHAR (40) NULL. Data loss could occur.

** User actions
     Alter
       [dbo].[Customer] (Table)

** Supporting actions

The type for column FirstName in table [dbo].[Customer] is currently  NVARCHAR (50) NULL but is being changed to  NVARCHAR (40) NULL. Data loss could occur.

W powyższym opisie widzimy, że zmiana typu danych, której dokonaliśmy może skutkować właśnie taką utratą danych. Gdy przyjrzymy się samemu skryptowi to możemy dostrzec następujący zapis:

IF EXISTS (select top 1 1 from [dbo].[Customer])
    RAISERROR (N'Rows were detected. The schema update is terminating because data loss might occur.', 16, 127) WITH NOWAIT
GO
PRINT N'Altering [dbo].[Customer]...';
GO
ALTER TABLE [dbo].[Customer] ALTER COLUMN [FirstName] NVARCHAR (40) NULL;
GO

Czyli jeśli tabela zawiera jakiekolwiek dane to zwrócony zostanie błąd i skrypt zostanie przerwany. Rozwiązań tego problemu jest wiele jednym z nich jest użycie np. PreDeployment skryptu, skopiowanie danych do tabeli pomocniczej:

IF OBJECT_ID('dbo.CustomerCopy') IS NULL AND OBJECT_ID('dbo.Customer') IS NOT NULL
BEGIN

PRINT('Creating Customer copy table');

	SELECT 
		*
	INTO [dbo].[CustomerCopy]
	FROM
		[dbo].[Customer]

	IF EXISTS(SELECT TOP 1 1 FROM dbo.CustomerCopy)
	BEGIN
	
	PRINT('Truncate Customer table');
	
		TRUNCATE TABLE dbo.Customer;
	END
END

Następnie przywrócenie danych z kopii do głównej tabeli w ramach PostDeploymentu:

IF OBJECT_ID('dbo.CustomerCopy') IS NOT NULL
BEGIN


	INSERT INTO dbo.Customer (Id,FirstName,LastName,FK_CustomerType_ID)
	SELECT Id,FirstName,LastName,FK_CustomerType_ID FROM dbo.CustomerCopy


	DROP TABLE dbo.CustomerCopy;
END

Kolejna próba deploymentu również kończy się niepowodzeniem ponieważ mamy konflikt klucza obcego:

Cannot truncate table 'dbo.CustomerType' because it is being referenced by a FOREIGN KEY constraint.
** An error was encountered during execution of batch. Exiting.

Nie możemy wyczyścić tabeli CustomerType – dlatego też dodamy w PreDeployment kawałek kodu, który sprawdzi istnienie tego klucza, a następnie go usunie.

IF EXISTS (SELECT * 
  FROM sys.foreign_keys 
   WHERE object_id = OBJECT_ID(N'dbo.FK_CustomerType')
   AND parent_object_id = OBJECT_ID(N'dbo.Customer')
)
BEGIN 
	ALTER TABLE dbo.Customer  
	DROP CONSTRAINT FK_CustomerType;  
END 

By następnie przywrócić go w PostDeployment:

IF NOT EXISTS (SELECT * 
  FROM sys.foreign_keys 
   WHERE object_id = OBJECT_ID(N'dbo.FK_CustomerType')
   AND parent_object_id = OBJECT_ID(N'dbo.Customer')
)
BEGIN 
	ALTER TABLE [dbo].[Customer]
    ADD CONSTRAINT [FK_CustomerType] FOREIGN KEY ([FK_CustomerType_ID]) REFERENCES [dbo].[CustomerType] ([CustomerTypeId]);
END 

Po tych zmianach już udało nam się wrzucić zmiany na serwer i utrzymać strukturę oraz dane w konsystentnym stanie. Skrypty możemy oczywiście wzbogacać o transakcje, TRY.. CATCH i tym podobne struktury aby w pełni odpowiadały naszym wymogom. Problemy można rozwiązywać na szereg różnych sposobów więc wszystko zależy od podejścia i konkretnej sytuacji, jedyne na co warto zwracać uwagę to fakt, że Pre oraz Post skrypty wykonują się zawsze i powinny być budowane z uwzględnieniem tej cechy.

W ramach niniejszego artykułu chciałbym pokazać również, że omawiane skrypty mogą odwoływać się do kodu zawartego w innych plikach. Zachęcam do logicznego dzielenia operacji na osobne pliki dzięki czemu rozwiązanie będzie bardziej przejrzyste. Aby tego dokonać stworzyłem foldery, które nazwałem ReleaseA czyli będą tam znajdowały się Pre i Post skrypty związane z konkretnym releasem:

Następnie dodałem tam skrypty (znane już menu kontekstowe Add -> Script i jako typ skryptu wybieramy Script (Not in Build)) i każdą logiczną operację umieściłem w osobnym pliku:

Teraz nie pozostaje nic innego jak dodać odwołania do tych plików w głównych plikach Pre oraz Post.  Używamy tutaj względnego odwołania do pliku czyli :r .\ i następnie podajemy ścieżkę do pliku:

--RELEASE A:
:r .\ReleaseA\TurnOffCustomerCustomerTypeReferences.sql
:r .\ReleaseA\CreateCustomerCopyTable.sql
:r .\ReleaseA\TruncateCustomerTypeTable.sql

Pamiętamy oczywiście o kolejności poszczególnych wywołań. Po wygenerowaniu skyptu deploymentu widzimy, że wszystko działa tak jak należy:

/*
Deployment script for MyDBProject

This code was generated by a tool.
Changes to this file may cause incorrect behavior and will be lost if
the code is regenerated.
*/

GO
SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON;

SET NUMERIC_ROUNDABORT OFF;


GO
:setvar DatabaseName "MyDBProject"
:setvar DefaultFilePrefix "MyDBProject"
:setvar DefaultDataPath "C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\"
:setvar DefaultLogPath "C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\"

GO
:on error exit
GO
/*
Detect SQLCMD mode and disable script execution if SQLCMD mode is not supported.
To re-enable the script after enabling SQLCMD mode, execute the following:
SET NOEXEC OFF; 
*/
:setvar __IsSqlCmdEnabled "True"
GO
IF N'$(__IsSqlCmdEnabled)' NOT LIKE N'True'
    BEGIN
        PRINT N'SQLCMD mode must be enabled to successfully execute this script.';
        SET NOEXEC ON;
    END


GO
USE [$(DatabaseName)];


GO

--RELEASE A:
IF EXISTS (SELECT * 
  FROM sys.foreign_keys 
   WHERE object_id = OBJECT_ID(N'dbo.FK_CustomerType')
   AND parent_object_id = OBJECT_ID(N'dbo.Customer')
)
BEGIN 
	ALTER TABLE dbo.Customer  
	DROP CONSTRAINT FK_CustomerType;  
END 

IF OBJECT_ID('dbo.CustomerCopy') IS NULL AND OBJECT_ID('dbo.Customer') IS NOT NULL
BEGIN

PRINT('Creating Customer copy table');

	SELECT 
		*
	INTO [dbo].[CustomerCopy]
	FROM
		[dbo].[Customer]

	IF EXISTS(SELECT TOP 1 1 FROM dbo.CustomerCopy)
	BEGIN
	
	PRINT('Truncate Customer table');
	
		TRUNCATE TABLE dbo.Customer;
	END
END
IF OBJECT_ID('dbo.CustomerType') IS NOT NULL
BEGIN
	IF EXISTS (SELECT TOP 1 * FROM dbo.CustomerType)
	BEGIN
	
		PRINT('Truncate CustomerType table');
	
		TRUNCATE TABLE dbo.CustomerType;
	
	END
END
GO

GO


--RELEASE A

INSERT INTO dbo.CustomerType
(
     [CustomerTypeId]
	,[CustomerType]
)
VALUES
	 (0,'Unknown')
	,(1,'Company')
	,(2,'Person');
IF NOT EXISTS (SELECT * 
  FROM sys.foreign_keys 
   WHERE object_id = OBJECT_ID(N'dbo.FK_CustomerType')
   AND parent_object_id = OBJECT_ID(N'dbo.Customer')
)
BEGIN 
	ALTER TABLE [dbo].[Customer]
    ADD CONSTRAINT [FK_CustomerType] FOREIGN KEY ([FK_CustomerType_ID]) REFERENCES [dbo].[CustomerType] ([CustomerTypeId]);
END 

IF OBJECT_ID('dbo.CustomerCopy') IS NOT NULL
BEGIN


	INSERT INTO dbo.Customer (Id,FirstName,LastName,FK_CustomerType_ID)
	SELECT Id,FirstName,LastName,FK_CustomerType_ID FROM dbo.CustomerCopy


	DROP TABLE dbo.CustomerCopy;
END

GO

GO
PRINT N'Update complete.';


GO

Polecam również dodawać komentarze w postaci instrukcji Print dzięki czemu łątwo śledzić co się dzieje podczas wykonywania tego skryptu. Cykl życia bazy danych może być bardzo skomplikowany i zależy od wielu czynników jednakże w powyższym podejściu zmiany oznaczone jako ReleaseA jak już będą na środowisku produkcyjnym możemy wyrzucić/zakomentować w głównym pliku Pre/Post i dodać nowe zmiany. Jeśli te skrypty nie są nam potrzebne możemy je albo wyrzucić albo z archiwizować dodając nowy folder z aktualnym Releasem:

Wszystko podpięte pod system kontroli wersji może nam znacznie przyspieszyć i ułatwić pracę deweloperską. Tak jak już wielokrotnie podkreślałem podejść jest bardzo wiele z wykorzystaniem przeróżnych technik, powyżej chciałem przedstawić użycie Pre i Post deployment skryptów w oparciu o jedną z nich. Mam nadzieję, że udało mi się osiągnąć cel jaki postawiłem przed sobą pisząc ten artykuł. Pozdrawiam serdecznie i zapraszam do subskrypcji bloga – już w najbliższym czasie będziemy rozszerzać naszą serie o dodatkowe artykuły – pozdrawiam!