18 kwietnia 2011

Implementacja DML Triggerów w CLR .NET

1) Integracja CLR i SQL Server


Od wersji SQL Server 2005 Microsoft umożliwił wykonywanie kodu zarządzanego (managed code) w kontekście procesu SQL Servera. Tym samym SQL Server stał się hostem CLR (Common Language Runtime). Ten krok miał ułatwić programistom tworzenie skomplikowanej logiki wykonywanej po stronie serwerowej. W poprzednich wersjach SQL Servera należało zaimplementować rozszerzoną procedurę składowaną (extended stored procedure, XP),  jednak model programowania w porównaniu z .NET Framework był "trochę" siermiężny (COM, wycieki pamięci, destabilizacja procesu SQL Server'a etc).

CLR umożliwia implementację procedur składowanych, funkcji, triggerów, typów użytkownika oraz operatorów agregacyjnych. W tym poście skupimy się na CLR DML (Data Manipulation Language) triggerach.


Kod źrodłowy jest do ściągnięcia tutaj.

2) Implementacja triggera


Opis DML Triggerów można znaleźć w moim poprzednim poście Triggery DML w MS SQL Server. Do implementacji CLR DML Triggera wystarczy
  1. Implementacja statycznej metody w .NET Framework. 
  2. Kompilacja kodu do .NET assembly
  3. Zarejestrowanie .NET assembly w docelowej bazie danych poprzez CREATE ASSEMBLY
  4. Zarejestrowanie triggera poprzez CREATE TRIGGER w danej bazie danych  z podaniem pełnej nazwy w klauzuli EXTERNAL NAME.

Visual Studio vs obiekty CLR


Ten proces może być uproszczony na maszynie programisty poprzez użycie wbudowanych szablonów Visual Studio.

Visual Studio 2010 - SQL CLR Database Project
Visual Studio 2010 dostarcza 'SQL CLR Database Project' szablonu, który ułatwia implementację poprzez dostępne wzorce plików do tworzenia obiektów CLR (procedury składowane, triggery etc). Deployment na maszynie programisty ogranicza się do wskazania docelowej bazy danych. Wybranie opcji 'Deploy' dokona rejestracji .NET assembly i wszystkich obiektów CLR zarejestrowanych w assembly oraz wrzuci kod źródłowy i pliki pdb w celu umożliwienia debugowania (czytelnik może sprawdzić sys.assembly_files).
Visual Studio 2008 dostarcza 'SQL Server Project' szablonu.

Visual Studio 2008 - SQL Server Project

W tym poście będzie opisany scenariusz, w którym Trigger będzię zaimplementowany przy użyciu szablonu Visual Studio 2008 - SQL Server Project. Jednak proces deploymentu i obsługi pewnych zależności będzie opisany tak by czytelnik był w stanie wykonać ręcznie, to co automatyzuje przycisk 'Deployment'. Polecam jednak użycie wbudowanych szablonów do  tworzenia obiektów CLR w SQL Server, aby uprościć proces debugowania kodu zarządzanego.

Trigger na tabeli Employee


Implementowany CLR Trigger będzie wywoływany po wykonaniu polecenia INSERT na tabeli Employee.


Wartości w kolumnach Country i City zostaną wykorzystane do wysłania wiadomości email do pracownika  z pogodą na następne 2 dni w podanej lokalizacji. Informacje o pogodzie będą uzyskane z serwisu weather.com.Korzystanie z serwisu API Weather.com wymaga dostarczenia kodu Partner Id i klucza licencji (License Key). W przykładzie informacje te będą znajdowały sie w tabelce Configuration.
Szczegóły implementacji użytej biblioteki i opis jak zarejestrować się na Weather.com dostępne są w poście 'Prognoza pogody na dziś - Weather.com .NET klient ' .



Po utworzeniu projektu z szablonu 'SQL Server Project' w Visual Studio 2008, należy wskazać bazę danych, w której będziemy rejestrować CLR Triggera.Wybierz  'Properties' dla projektu i zakładkę 'Database', kliknij przycisk 'Browse' i skonfiguruj połączenie do bazy.

Dodać zewnętrzną bibliotekę przez Add Reference w Visual Studio


Po wskazaniu bazy, czas na dodanie biblioteki Weather.com.Client.dll. Po wybraniu 'Add Reference...' zobaczymy listę wspieranych bibliotek oraz wszystkie biblioteki zarejestrowane w docelowej bazie. Aby zobaczyć na liście Assembly bibliotekę Weather.com.Client.dll należy wykonać następujące polecenie.


Ponieważ CLR jest hostowany przez SQL Server i kod jest wykonywany domyślnie w kontekście konta procesu SQL Server, na biblioteki .NET Framework, które mogą być używane w implementacji obiektów CLR nałożono restrykcje. Tylko biblioteki przetestowane przez SQL Team nie wymagają wcześniejszej rejestracji, są to tak zwane Supported .NET Framework Libraries  in CLR. Niewspierane biblioteki .NET Framework muszą  być zarejestrowane przy użyciu CREATE ASSEMBLY. Nawet jeżeli biblioteka jest w GAC to też musi być zarejestrowana i wersja w bazie danych i wersja w GAC muszą się zgadzać co do wersji i binarnej zawartości pliku KB949080. Konsekwencją tego jest ograniczona liczba bibliotek w dialogu 'Add References'.

Szczególy implementacyjne triggera AFTER INSERT na tabeli Employee


Metoda statyczna implementująca funkcjonalność triggera CLR może być oznaczona atrybutem SqlTriggerAttribute. Wartości przekazane do właściwości atrybutu mogą być wykorzystane przy auto-rejestracji triggera. Sam atrybut jest opcjonalny.


Trigger EmployeeTrigger pobiera dane z tabeli Configuration, później na podstawie danych z tabeli Inserted rozsyła emaile do pracowników z prognozą pogody. Trigger wykorzystuje bibliotekę zewnętrzną, której kod
wykonuje zapytanie do web serwisu. Metoda SqlContext.Pipe.Send została użyta do wyświetlenia rezultatów, jednak dokumentacja Microsoftu zaleca, by nie stosować tego typu konstrukcji w rozwiązaniach produkcyjnych, jeżeli trigger zwraca wyniki to powinny zostać zapisane w tabeli bazy danych.

3) Ręczna instalacja CLR triggera w bazie danych


Obiekty CLR po rejestracji są dostępne w obrębie jednej bazy danych. Zakres uprawnień wymagany do rejestracji jest mniejszy, natomiast XP były rejestrowane przez system administratora w bazie master i dostępne dla całego serwera. Aby kod CLR mógł w ogóle się uruchomić należy:

Włączyć CLR dla całej instancji SQL Server




Inaczej podczas wykonywania kodu CLR można otrzymać następujący komunikat.
Msg 6263, Level 16, State 1, Line 1
Execution of user code in the .NET Framework is disabled. Enable "clr enabled" configuration option.
The statement has been terminated.

Zweryfikuj Error Log w poszukiwaniu inicjalizacji Common Language Runtime, zachowanie będzie inne w SQL Server 2008 gdzie CLR jest ładowany domyślnie ponieważ niektóre nowe typy użytkownika (np. Geometry, HierarchyId) są zaimplementowane przez CLR, a w 2005 wpis w Error Logu pojawi się dopiero po właczeniu 'clr enabled'.

Ustawić dla bazy danych TRUSTWORTHY ON




Inaczej zostanie wyrzucony następujący błąd przy wywołaniu CREATE ASSEMBLY z PERMISSION_SET = EXTERNAL_ACCESS. Biblioteka WeatherClient wywołuje web service Weather.com zatem potrzebuje rozszerzonych uprawnien.


Msg 10327, Level 14, State 1, Line 1
CREATE ASSEMBLY for assembly 'MulawaSqlServerCLRTriggers' failed because assembly 'MulawaSqlServerCLRTriggers' is not authorized for PERMISSION_SET = EXTERNAL_ACCESS. The assembly is authorized when either of the following is true: the database owner (DBO) has EXTERNAL ACCESS ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with EXTERNAL ACCESS ASSEMBLY permission. If you have restored or attached this database, make sure the database owner is mapped to the correct login on this server. If not, use sp_changedbowner to fix the problem.


Rejestracja assembly




Rejestracja Triggera i namespace'y w assembly


Z namespacem należy użyć kwadratowych nawiasów:


inaczej CREATE TRIGGER zwróci następujący błąd

Msg 102, Level 15, State 1, Procedure AfterEmployeeUpdate, Line 4
Incorrect syntax near '.'.


lub:

Msg 6505, Level 16, State 1, Procedure AfterEmployeeUpdate2, Line 1
Could not find Type 'Mulawa.SqlServer.CLRTriggers.Triggers' in assembly 'mulawa.sqlserver.clrtriggers'.


Wywołanie Triggera




Wyniki:

day: Apr 18, highest daily temperature 17C
day: Apr 19, highest daily temperature 16C
day: Apr 20, highest daily temperature 17C

Brak komentarzy:

Prześlij komentarz

Uwaga: tylko uczestnik tego bloga może przesyłać komentarze.