O wykorzystaniu CLR w SQL
Server 2005 można by napisać wiele książek (i nie wątpię, że w najbliższym
czasie tak się stanie). My zajmiemy się dość zasadniczymi zagadnieniami, które
będą mogły przedstawić rewolucyjny charakter usprawnień. Pierwszym krokiem
będzie napisanie funkcji do formatowania daty. Drugim nieco bardziej
skomplikowanym zagadnieniem będzie napisanie własnej funkcji agregującej.
Funkcje bazodanowe
Pierwszym krokiem, jakiego musimy
dokonać jest uruchomienie MS Visual Studio .NET 2005. Gdy już to
będziemy mieli za sobą, musimy utworzyć odpowiedni projekt. W tym celu musimy
wykonać następujące czynności. W głównym menu wybieramy File -> New ->
Project. Następnie z lewego okna rozwijamy Visual C# zaznaczamy Database
i naciskamy OK.
Po utworzeniu projektu pojawi
się okienko dialogowe z możliwością wyboru serwera, z jakim chcemy się połączyć
i bazy danych na nim istniejącym. My uzupełnimy to tak jak na zdjęciu poniżej.
Gdy projekt się uruchomi,
odnajdujemy zakładkę Solution Explorer i klikamy prawym guzikiem myszy
na projekt w celu dodania naszej funkcji. Wybieramy Add -> User Defined
Funcition po otworzeniu okna dialogowego wpisujemy nazwę funkcji DateFormater
i naciskamy OK.
Pojawiło nam się okno z wygenerowaną przykładową funkcją.
Przechodzimy teraz do napisania funkcji, która jako argument będzie pobierać
datę oraz format daty w jakim chcemy ją przedstawić. Jak pamiętamy z
wcześniejszych artykułów, standardowo zwracana data wygląda następująco: 2005-08-20
17:19:22.977. Co jednak, jeśli chcemy, aby data zwracana była w formacie 20.08.2005
lub 20//08//2005? Aby rozwiązać ten problem wystarczy tylko jedna linia kodu w
C#.
Zmodyfikujmy wygenerowaną funkcję do takiej postaci:
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString
DateFormater(SqlDateTime myDate, SqlString dateFormat)
{
return
(SqlString)string.Format((string)("{0:" + dateFormat +
"}"), myDate.Value);
}
Metoda pobiera standardowo dwa
argumenty: datę oraz format, w jakim chcemy datę przedstawić. Format daty
przedstawiany jest w następujący sposób: „y” reprezentuje rok, „M” miesiąc, a
„d” dzień. Pomiędzy podane znaczniki można podstawiać dowolne znaki. Teraz
wystarczy tylko utworzyć plik DDL i przerzucić go na serwer bazodanowy.
Z menu wybieramy Build -> Build Solution, a następnie Bulid- >
Deploy Solution. Jeśli wszystko poszło dobrze możemy teraz przejść do
serwera SQL i sprawdzić czy nasza funkcja działa poprawnie. Musimy tylko
jeszcze włączyć na MS SQL Server 2005 opcje korzystania z CLR.
Funkcja ta ze względów bezpieczeństwa jest domyślnie wyłączona. Aby ją
uruchomić, wystarczy uruchomić następującą linię kodu.
sp_configure 'clr enabled', 1
go
reconfigure
go
Teraz możemy przetestować naszą
funkcję. Poniższy skrypt pokazuje w jaki sposób można ją uruchomić i wyniki
jakie otrzymujemy.
USE AdventureWorks
GO
SELECT dbo.DateFormater(GetDate(),
'yy.MM.dd') 05.08.20
SELECT dbo.DateFormater(GetDate(),
'dd///MM///yyyy') 20///08///2005
SELECT dbo.DateFormater(GetDate(),
'yy**MM**dd') 05**08**20
SELECT
dbo.DateFormater(GetDate(), 'yy:-)MM:-)dd') 05:-)08:-)20
Funkcje agregujące
Funkcjom agregującym poświęciliśmy jeden z artykułów. I od
tego czasu mam nadzieję, że wszyscy zdają sobie sprawę, jak ważnym zagadnieniem
są agregaty. A co jeśli moglibyśmy napisać własne funkcje agregujące, z
własnymi zasadami agregacji? Taką właśnie możliwość daje nam połączenie SQL
Server 2005 z .NET Framework.
Aby pracować z własnymi User-Defined
Aggregates (UDA), należy zapoznać się z czterema podstawowymi zasadami
rządzącymi ich działaniem. Entering, accumulating,
mering
i existing. Kiedy uruchamiamy funkcję agregującą na serwerze,
musimy zainicjalizować wartość początkowe. Jeśli robilibyśmy operacje dodawania,
odejmowania - wartością startową byłoby zero. Jeśli jednak chcielibyśmy
przeprowadzić operacje mnożenia wartością pierwotną byłaby wartość jeden.
Ponieważ nasz przykład nie
będzie nazbyt wyrachowany i ograniczy się do powielenia agregatu SUM,
wartość domyślną inicjalizujemy zerem.
public void Init()
{
result = 0;
}
Następnym krokiem jest
zdefiniowanie tego, co się dzieje z kolejną wartością, jaka jest pobierana.
Czyli co się dzieje, jeśli podczas procesu przechodzenia po kolejnych wierszach
w tabeli dostajemy nowe wartości. Jak mają się zachować w stosunku do wartości,
które już mamy.
public void Accumulate(SqlInt32 Value)
{
result += (int)Value;
}
W naszym przypadku odpowiedź jest dość prosta. Należy dodać
wartość, która została podana do wartości, które już zsumowaliśmy.
Metoda Terminale
zwraca wynik po wykonaniu metody Accumulate. Jest również
odpowiedzialna za zwrócenie wyniku do kodu wywołującego. W naszym przypadku
zwróci sumę wyniku z całej tabeli.
public SqlInt32 Terminate()
{
return new SqlInt32(result);
}
Merge jest
specjalną metodą, która pomaga w utrzymaniu spójności na poziomie obiektowym.
public void Merge(MyIntegerCalculate
Group)
{
result += Group.result;
}
W ten sposób napisaliśmy funkcję
agregującą SUM, która w naszym przypadku nazywa się MyIntegerCalculate
i używamy jej dokładnie w ten sam sposób, jak SUM z tą różnicą,
że wstawiamy przed nazwę agregata dbo.
Przykład ten miał za zadanie
zademonstrować, jak działają funkcje agregujące i przedstawić cztery podstawowe
zasady tworzenia własnych agregatów. Następnym i wydaje się ostatnim krokiem na
drodze tworzeniem własnych efektywnych i poprawnie działających funkcji są
atrybuty.
Atrybuty agregatów
Atrybuty są specjalnymi
znacznikami na klasach w .NET, za pomocą których możemy
definiować dodatkową funkcjonalność dla danej klasy. Przykładem mogą być Web
serwisy, przy których należy do każdej z metod dodać atrybut [WebMethod],
aby umożliwić korzystanie z niej za pośrednictwem stron WWW.
W przypadku funkcji agregujących, które z definicji pracują na bazach danych,
gdzie najważniejsza jest wydajność, atrybuty są wyjątkowo ważne. Pomagają one
SQL
Server Query Optimizer efektywniej budować zapytania.
Format – mówi SQL
czy powinien serializować dane
IsInvariantToNull
– jeśli jest ustawione na true, serwer SQL ignoruje wartości NULL.
W naszym przypadku nie ma potrzeby pobierać wartości nieokreślonych, więc
możemy bez problemu ustawić na true. Jeśli jednak tworzylibyśmy funkcję
wyliczającą wartość średnią musielibyśmy również uwzględnić wartości NULL.
IsInvariantToOrder
– jeśli ustawimy na true, oznacza to, że nie zważamy na kolejność w jakiej dane
są pobierane. Ponieważ suma jest operacją przemienną, nie ma potrzeby
zachowywać kolejności.
IsNullIfEmpty – jeśli jest ustawione na true,
zwraca wartość nieokreśloną NULL, gdy nie ma żadnych wartości. W naszym
przypadku ogranicza się to do tego, że jeśli tabela będzie pusta, lub wiersze,
które podaliśmy do sumowania posiadają wartość NULL to wynik również
będzie NULL.
Podsumowanie
Wydaje mi się, że wszystkich zachęciłem do zagłębienie się w
CLR i nowe możliwości jakie stoją przed SQL Server 2005 i
jego integracją z .NET Framework. Mimo wielkich możliwości, jakie
zostały dane zarówno administratorom baz danych, jak i developerom powinniśmy
pamiętać, iż operacje wykonywane za pomocą Frameworka są znacznie
bardziej kosztowne i czasochłonne, niż alternatywna operacja w Transact-SQL.
Przy użyciu CLR powinniśmy najpierw sprawdzić, czy korzyści
wynikające z łatwiejszego zakodowania są na tyle duże, aby zrezygnować z SQL.