Artykuł ma na celu zapoznać czytelnika z zagadnieniem Integracji CLR, a także na prostym przykładzie przedstawić krok po kroku sposób wdrożenia i wykorzystania UDT (User Defined Type) zaimplementowanego w .NET Framework 2.0. z wykorzystaniem języka C#.
W tym artykule chciałbym przedstawić dość interesujące rozszerzenie, jakie posiada SQL Server 2005, a mianowicie możliwość integracji .NET Framework 2.0 z silnikiem bazy danych. Otwiera ona naprawdę wiele ciekawych możliwości. Korzystając z Visual Basic, C# czy zarządzanego C++ będziemy mogli tworzyć własne: procedury składowane, wyzwalacze, funkcje, funkcje agregujące, typy. W tym momencie nie jesteśmy już tylko ograniczeni do T-SQL, ale na naszych usługach jest potężny Framework razem ze wszystkimi swoimi bibliotekami.
Całość rozpocznę do nieuniknionego w tym przypadku wstępu teoretycznego, potem przejdę do części praktycznej, w której przedstawię implementację własnego typu w języku C#.
1. Teoria
Mechanizm :
Każdy program napisany w .NET Framework nie jest bezpośrednio kompilowany do kodu natywnego, ale do formy pośredniej zwanej MSIL (Microsoft Intermediate Language). Dopiero ona w momencie wykonywania programu jest przekształcana na kod „zrozumiały” dla systemu operacyjnego (kod natywny). Odbywa się to bezpośrednio za pośrednictwem JIT (just-in-time) compiler, jednej z usług oferowanych przez CLR (Common Language Runtime, czyli wspólne srodowisko uruchomieniowe dla platformy .NET).
W środowisku, jakim jest SQL Server (cały artykuł dotyczy SQL Server 2005, zatem gdzie nie jest to wyszczególnione, odnosimy się właśnie do tej wersji), gdy kod SQL jest kompilowany i pojawia się odwołanie do zarządzanych komponentów, generowany jest obiekt zastępczy, zwany namiastką (ang. stub). Zawiera ona kod pośredniczący pozwalający przekazać parametry ze „zwykłego” przebiegu kodu w SQL Server do CLR, wywołać funkcje i zwrócić wynik operacji Ten kod jest odpowiednio optymalizowany i gwarantuje poprawność wymuszoną przez SQL Server. Natomiast pozostała część namiastki jest kompilowana do kodu natywnego i zoptymalizowana do architektury sprzętowej, gdzie działa SQL Server. W wyniku tego otrzymujemy wskaźnik do funkcji, która może być wywoływana z kodu natywnego bazy danych. Innymi słowy za pośrednictwem namiastki baza danych przekazuje parametry CLR, ten wykonuje całą pracę, a następnie po prostu zwraca wynik.
Samo wykonanie zadań powierzonych CLR odbywa się tak, że SQL Server, a w zasadzie jego procesy, stanowią środowisko uruchomieniowe dla komponentów skompilowanych do MSIL. Współpraca przebiega w taki sposób, że SQL Server zachowuje się jak system operacyjny dla CLR, który jest wykonywany w jego „wnętrzu”. CLR wywołuje niskopoziomowe funkcje udostępnione przez SQL Server, które obsługują zarządzanie pamięcią, synchronizację itd. Daje to bazie danych kontrolę nad sposobem wykonania kodu.
Przykładowo, gdy CLR wykonuje swoją wewnętrzną operację, nadzorca szeregowalności bazy danych (ang. scheduler) jest o tym informowany i może swobodnie wykonywać zadania niepowiązane z kodem zarządzanym. Oprócz tego może także wykrywać zakleszczenia jakie nastąpiły z udziałem CLR i usuwać je tradycyjnymi metodami.
Kolejną zaletą tego rozwiązania jest to, że CLR nigdy nie będzie konkurował o pamięć z SQL Server. Baza może w razie potrzeby odrzucać prośby kodu zarządzanego o przydział zasobów lub ograniczyć dostępną dla niego pamięć. Dzięki temu nie ma ryzyka przekroczenia zasobów przeznaczonych dla SQL Server’a czy problemów związanych z synchronizacją. W mechaniźmie tym uniknięto także niebezpieczeństwa związanego z utratą integralności danych przechowywanych w bazie.
Warto także wspomnieć, że każdy program napisany w .NET korzysta z CAS (Code Access Security), które definiuje, jakie operacje zarządzany kod może wykonać. Baza danych ma w tym momencie możliwość nałożenia ograniczeń za sprawą host-level security.
Zatem, ładując daną kompilacje do bazy danych mamy możliwość określenia poziomu bezpieczeństwa, jaki będzie jej przysługiwał. Więcej konkretnych informacji na temat znajdzie się w części praktycznej.
Tak, z pewnymi uproszczeniami, przebiega współpraca między SQL Server, a zintegrowanym z nim CLR. Wcześniej wspomniałem, że SQL narzuca pewne ograniczenia na zarządzany kod, który może być wykonany w jego „wnętrzu”. Przyjrzyjmy się bliżej temu zagadnieniu w odniesieniu do typów definiowanych przez użytkownika.
2. Implementacja UDT
Specyfikacja :
Własny typ możemy implementować korzystając z klasy lub struktury, chociaż dokumentacja SQL Server zaleca stosowanie struktur. Taka struktura, aby móc swobodnie działać w SQL Server musi spełniać pewne bardzo precyzyjnie określone warunki. Oto fragment przykładu, jaki wykorzystuję w tym artykule :
using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlTypes;
using System.Text;
using System.Security.Cryptography;
using Microsoft.SqlServer.Server;
[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedType(Format.UserDefined, MaxByteSize=64,IsByteOrdered=true)]
public struct UserType : INullable,IBinarySerialize
{
private bool m_Null;
private string userName;
private byte[] userPass;
[...]
}
Po pierwsze nasz typ musi wykorzystywać 3 przestrzenie nazw :
using System;
using System.Data.Sql;
using System.Data.SqlTypes;
Oprócz tego konieczne jest nadanie typowi atrybutu [Serializable] informującego, że może być on poddany serializacji. Jednak największe znaczenie w tym momencie ma atrybut SqlUserDefinedType i jego właściwości. Piewsza z nich i mająca największy wpływ na pozostałe to Format. Mamy do wyboru mamy dwie :
Format.Native: dość wygodna opcja (ze względu na nakład pracy, jakiego wymaga), ale niestety możemy z niej korzystać tylko, gdy nasza struktura/klasa na składowe następującego typu : bool, byte, sbyte, short, ushort, int, uint, long, ulong, float, double, SqlByte, SqlInt16, SqlInt32, SqlInt64, SqlDateTime, SqlSingle, SqlDouble, SqlMoney. Także wykorzystanie tablic składających się z wyżej wymienionych całkowicie przekreśla wykorzystanie tej opcji. Nie mogą występować nieserializowane pola. Ale jej niewątpliwą zaletą jest to ręcznie nie musimy implementować serializacji czy podawać pewnych właściwości.
Format.UserDefined: zdecydowanie bardziej „pracochłonna”, ale dopiero ona pozwala nam na wykorzystanie pełni możliwości drzemiących w UDT. Wykorzystanie jej pociąga ze sobą konieczność samodzielnej serializacji. Zmusza to nas do zaimplementowania interfejsu IBinarySerialize, który jest częścią przestrzeni Microsoft.SqlServer.Server. W naszym przypadku implementacja tego interfejsu wygląda następująco :
#region IBinarySerialize Members
public void Read(System.IO.BinaryReader r)
{
userName = r.ReadString();
userPass = r.ReadBytes(20);
m_Null = r.ReadBoolean();
}
public void Write(System.IO.BinaryWriter w)
{
w.Write(UserName);
w.Write(userPass);
w.Write(IsNull);
}
#endregion
Oprócz tego, korzystając z tej opcji musimy podać maksymalny rozmiar instancji tego typu korzystając z właściwości MaxByteSize.
Kolejna właściwość, którą warto dodać i ustawić na true (zarówno dla Format.Native i Format.UserDefined) jest IsByteOrdered (domyślnie jest false). Gwarantuje ona, że będzie możliwe porównywanie reprezentacji binarnej zdefiniowanego przez nas typu korzystając z mechanizmów bazy danych. Dzięki temu będziemy mogli w stosunku do pól tego typu : wykorzystywać operatory porównania (<; > , = ,...) ,
nadawać ograniczenia CHECK i UNIQUE,
stosować polecenia T-SQL, takie jak ORDER BY, PARTITION BY, ORDER BY czy DISTINCT .
A także umożliwi to na polach tego typu tworzyć indeksy oraz wykorzystywać pola tego typu jako klucze główne i klucze obce.
To wszystkie atrybuty, które zdecydowałem się przedstawić mogące odgrywać istotną rolę przy budowaniu własnego UDT. Niestety, to nie koniec wymagań, którym nasz typ musi podołać. Niezbędne w tej sytuacji jest zaimplementowanie interfejsu Inullable. To akurat nie jest zbyt skomplikowane :
public bool IsNull
{
get
{
return m_Null;
}
}
Jednak to nie koniec naszej przygody z null. Będziemy musieli utworzyć odpowiednią właściwość :
public static UserType Null
{
get
{
UserType h = new UserType();
h.m_Null = true;
return h;
}
}
Okazuje się, że jest ona niezbędna przy pisaniu metody Parse(SqlString s). To kolejna wymagana metoda (a jakżeby inaczej ... ). Służy do tworzenia instancji naszego typu z poziomu SQL. Przykładowa implementacja znajduje się poniżej.
public static UserType Parse(SqlString s)
{
if (s.IsNull)
return Null;
UserType u = new UserType();
string[] tab = s.ToString().Split('|');
UnicodeEncoding encode = new UnicodeEncoding();
SHA1CryptoServiceProvider sha1provider = new
SHA1CryptoServiceProvider();
u.userName = tab[0];
u.userPass = sha1provider.ComputeHash(encode.GetBytes(tab[1]));
u.m_Null = false;
return u;
}
Najpierw sprawdzamy czy przekazany SqlString ma wartość NULL. Jeśli coś takiego miało miejsce, zwracamy wspomnianą wcześniej właściwość, a jeśli otrzymaliśmy poprawny string możemy go zamienić na typ wypełniając odpowiednie pola. Jak widać konieczna może się okazać pewna „konwencja” rozdzielania i kolejności danych. Akurat ja w tym przypadku zakładam, że na pierwszym miejscu pojawi się nazwa użytkownika, a następnie jego hasło, z którego przed przypisaniem obliczam skrót.
Drugą wymaganą metodą jest metoda ToString(). Jak łatwo się domyśleć, stanowi ona przeciwieństwo poprzedniej i pozwala instancje naszego typu zamieniać na ciągi znaków.
public override string ToString()
{
if (IsNull)
return "NULL";
else
{
UnicodeEncoding encode = new UnicodeEncoding();
StringBuilder sb = new StringBuilder();
sb.Append(userName);
sb.Append("|");
sb.Append(encode.GetString(userPass));
return sb.ToString();
}
}
Ponownie na samym początku zapewniamy obsługę dla null, a potem dopiero przystępujemy do przekształcenia naszych pól do typu string.
Oprócz tego jeśli nasz typ będzie posiadał publiczne właściwości, dla których nie będzie wsparcia serializacji do XML trzeba będzie implementować interfejs IxmlSerializable. Posiada on 3 metody :
public System.Xml.Schema.XmlSchema GetSchema()
public void ReadXml(XmlReader reader)
public void WriteXml(XmlWriter writer)
Pierwsza z nich zwraca schemat dla danego typu, pozostałe obsługują zapis i odczyt danych. W moim przypadku występuje jedna publiczna właściwość,
public string UserName
{
get
{
return userName;
}
set
{
userName = value;
}
}
ale nie wymusza ona samodzielnej serializacji do XML.
W ten sposób dobiega końca omawianie specyfikacji i wymagań, którym to musi nasz UDT podołać. Ale samo zapewnienie specyfikacji to nie wszystko, nasz typ to nie tylko zestaw atrybutów i właściwości, dla których trzeba zapewnić wsparcie dla serializacji, parsowania i konwersji do typu string. Warto wzbogacić nasz typ o dodatkowe metody pozwalające wykonywać określone operacje na tych polach.
Metody :
Funkcje, które do damy do naszego UDT należ podzielić na 2 grupy. Pierwsza odczytuje zawarte w naszym typie zmienne (w żaden sposób ich nie modyfikuje) i na tej podstawie zwraca określony wynik. Dla przykładu :
public bool CheckUserNoHashed (string user_name, string passwd)
{
SHA1CryptoServiceProvider sha1provider = new SHA1CryptoServiceProvider();
UnicodeEncoding encode = new UnicodeEncoding();
byte [] psd = sha1provider.ComputeHash(encode.GetBytes(passwd));
if ((UserName == user_name) && (ComparePasswd(userPass,psd)))
return true;
else
return false;
}
public bool CheckUserHashed(string user_name, byte[] passwd)
{
if ((UserName == user_name) && (ComparePasswd(userPass, passwd)))
return true;
else
return false;
}
Każda z tych metod zwraca true, jeśli podana nazwa użytkownika i hasło odpowiadają atrybutom danej instancji (jedna najzwyczajniej w świecie przyjmuje string i liczy skrót, a druga wymaga wcześniejszego obliczenia skrótu).
Natomiast druga grupa to metody, za pośrednictwem których możemy modyfikować pola. Czyli :
[SqlMethod(IsMutator = true, OnNullCall = false)]
public void SetNewPasswd(string newpass)
{
UnicodeEncoding encode = new UnicodeEncoding();
SHA1CryptoServiceProvider sha1provider = new SHA1CryptoServiceProvider();
userPass = sha1provider.ComputeHash(encode.GetBytes(newpass));
}
Znowu występują kolejne atrybuty metody:
IsMutator = true (informacja, że ta metoda może modyfikować atrybuty naszego typu).
OnNullCall Jeśli nie życzymy sobie, aby metodę wywoływano na rzecz pól w bazie zawierających NULL ustawiamy false.
Każdy z tych atrybutów ma domyślną wartość false, zatem nie jest konieczne definiowanie ich w przypadku pierwszej grupy.
Ostania uwaga, na jaką sobie pozwolę jest taka, iż w przypadku metod oraz właściwości możemy zapomnieć o przeciążaniu nazw, jeśli chcielibyśmy je wykorzystać z poziomu bazy danych. Wszystko się skompiluje, nawet uda nam się ten typ utworzyć w bazie, ale gdy tylko wywołamy którąkolwiek z przeładowanych metod ... SQL Server z właściwym sobie wdziękiem poinformuje nas o tym, że nie wspiera przeładowania.
Msg 6572, Level 16, State 1, Line 1
More than one method, property or field was found with name 'CheckUserHashed' in class 'UserType' in assembly 'UDTSample'. Overloaded methods, properties or fields are not supported.
W tym miejscu już dobiega końca dość pracochłonny proces budowy własnego UDT. Najwyższa pora, aby rozwinął on skrzydła w SQL Server.
Osadzenie :
Razem z wprowadzeniem integracji z CLR wprowadzono pewne rozszerzenia do T-SQL pozwalające obsługiwać komponenty CLR z poziomu bazy danych. Jednak pierwszym krokiem, jaki należy poczynić jest włączenie integracji z CLR (oczywiste jest, że musimy dysponować prawami administratora, aby dokonać tej zmiany). Poniższe polecenia pozwalają osiągnąć cel :
EXEC sp_configure 'clr enabled' , 1
GO
RECONFIGURE
GO
Pierwsze polecenie włącza integracje z CLR, natomiast drugie pozwala zaktualizować ustawienia SQL Server, jeśli nie wymaga to restartu usługi.
Teraz można przystąpić to dodania naszego typu do bazy danych. Najpierw dodajemy komponent poleceniem :
CREATE ASSEMBLY UDTSample
FROM '<PATH>\UDTSample.dll' -- To nalezy zmodyfikowac i podac wlasna sciezke
WITH PERMISSION_SET = SAFE
GO
Parę słów poświęcę opcji PERMISSION_SET, ona bowiem definiuje, jakie uprawnienia wykonania będzie posiadał kod z danej kompilacji. Opcja SAFE jest domyślna i najbardziej restrykcyjna. Zezwala tylko i wyłącznie na działanie w ramach zintegrowanego CLR, nie można bezpośrednio odwoływać się do kodu natywnego lub zasobów zewnętrznego systemu. EXTERNAL_ACCESS pozwala uzyskać dostęp to zewnętrznych zasobów, dla których ma dostęp konto usługi SQL Server. Ostatnia i najbardziej niebezpieczna opcja to UNSAFE może ona swobodnie wywoływać kod natywny, nie jest ograniczana przez CAS. Korzystać z niej może tylko sysadmin.
Teraz już tylko jedno polecenie dzieli nas od utworzenia typu :
CREATE TYPE [dbo].UserType
EXTERNAL NAME UDTSample.UserType
GO
W tym momencie mamy własny UDT dołączony do wybranej bazy danych. Aby zobaczyć, jakie informacje baza przechowuje o nim i o komponencie można skorzystać z widoków systemowych sys.assembly_files, sys.assemblies i sys.types.
SELECT *
FROM sys.assembly_files
GO
SELECT *
FROM sys.assemblies
GO
SELECT *
FROM sys.types
GO
Czytając moje opracowanie można odnieść wrażenie, że budowa UDT jest niezwykle ciężka i żmudna. Szczęśliwie Visual Studio 2005 zdecydowanie upraszcza ten proces. Jeżeli wykorzystamy projekt SQL Server Project otrzymamy bardzo wygodne środowisko do budowy własnych UDT, procedur itd. Wystarczy dodać do wcześniej utworzonego projektu SQL Server Project UserDefinedType, a otrzymamy typ spełniający podstawowe warunki.
Jednak ważniejsza wydaje się możliwość debugowania. Jeśli w pliku Test.sql umieścimy polecenia wykorzystujące nasz typ, procedurę, funkcję, będziemy mogli je debugować z poziomu VS, tak jak miałoby to miejsce dla każdego innego projektu.
W ostatnim punkcie tego artykułu przedstawię wykorzystanie UDT z poziomu SQL Servera jak i bardzo prostego programu opartego o ADO.NET 2.0.
3. Wykorzystanie
SQL Server :
W ogólnym ujęciu UDT tym możemy się posługiwać jak każdym innym typem.
Na samym początku warto utworzyć własną tabele :
CREATE TABLE [dbo].Users
(
id_user int IDENTITY(1,1),
person [dbo].UserType NOT NULL
)
GO
Potem już tylko pozostaje wstanie wartości :
INSERT INTO [dbo].Users ([person]) VALUES ('Jan|abc')
INSERT INTO [dbo].Users ([person]) VALUES ('Anna|xyz')
INSERT INTO [dbo].Users ([person]) VALUES ('Kazimierz|ibm')
GO
Aby zobaczyć zawartość pola person musimy skorzystać z metody ToString().
SELECT id_user, person.ToString()
FROM [dbo].Users
GO
albo publicznych właściwości
SELECT id_user, person.UserName
FROM [dbo].Users
ORDER BY person
GO
Możemy tworzyć zmienne tego typu.
DECLARE @usr UserType
SET @usr = CONVERT (UserType, 'Alicja|bbb')
SELECT @usr.UserName as [Name]
GO
Ich wartość można ustawiać za pomocą konwersji (powyżej) lub przypisania (poniżej).
DECLARE @usr UserType
SELECT @usr = person FROM Users U
WHERE person IN (SELECT U.person
WHERE person.UserName = 'Jan')
SELECT @usr.UserName as [Name]
GO
Tutaj przyznaję, że niepotrzebnie skomplikowałem to zapytanie, ale chciałem pokazać, że tym polem można operować podobnie, jak każdym innym, a ponieważ ustawiliśmy IsByteOrdered na true można stosować ORDER BY oraz porównania.
Kolejny przykład przedstawia stosowanie „mutatorów” (metod, które zmieniają zawartość instancji).
UPDATE Users
SET person.SetNewPasswd('nowe')
WHERE id_user = 2
GO
Jeżeli nasze właściwości nie są tylko-do-odczytu. Możemy modyfikować instancję za ich pośrednictwem.
UPDATE Users
SET person.UserName = 'Roman'
WHERE person.UserName = 'Kazimierz'
GO
Zewnętrzny Program :
Ostatni przykład jaki chciałem przedstawić to prosty program korzystający z ADO.NET 2.0 i zdefiniowanego wcześniej typu. Wystarczy dodać do projektu referencję do naszego UDT.
Standardowo łączymy się z bazą i wykonujemy zapytanie :
string commandText = "SELECT id_user,person FROM USERS ORDER BY id_user";
wypełniamy DataSet :
adapter = new SqlDataAdapter(commandText, connection);
ds = new DataSet();
adapter.Fill(ds);
Tutaj pojawia się najciekawszy fragment kodu :
userTab = new UserType[ds.Tables[0].Rows.Count];
foreach (DataRow r in ds.Tables[0].Rows)
{
userTab[i] = (UserType)r["person"];
i++;
}
Po zastosowaniu rzutowania możemy potem z poziomu aplikacji posługiwać się naszym typem, dokładnie tak, jak gdyby nie był on przeznaczony do działania w bazie.
foreach (UserType ut in userTab)
{
if (ut.CheckUserHashed(name, pass))
Console.WriteLine("Sprawdzenie hasła dla użytkownika " + ut.UserName + " powiodlo sie.");
else
Console.WriteLine(ut.UserName + " ma inne haslo");
}
Oczywiście, tutaj nie występuje już problem związany z brakiem przeciążania nazw.
Można zarzucić, że ten przykład na dobrą sprawę nic nowego nie wnosi, ja jednak chciałem pokazać, że typy w tym momencie potrafią stać dodatkowym powiązaniem miedzy warstwą danych a logika. A ich możliwości nie ograniczają się tylko i wyłącznie do „agregatu danych”.
4. Podsumowanie
Mam nadzieję, że mój artykuł w pewnym stopniu przybliżył czytelnikowi zagadnienie integracji SQL Server z CLR. Z całą pewnością otwiera ono bardzo wiele ciekawych możliwości. Niepodważalną zaletą tego rozwiązania jest to, iż programista może teraz korzystać z Framework 2.0 operując na danych zawartych w bazie
Z drugiej strony największe obawy, jakie są związane z wykorzystaniem integracji wiążą się właśnie z UDT. Zarzut stanowi to, iż w pewien sposób narusza on obowiązująca w bazach normalizacje, stanowiąc pewien „zamach” na pierwszą postać normalną. Może to mieć miejsce, jeśli UDT ograniczymy tylko i wyłącznie jako schowek na dane, nie dodając do niego dodatkowej funkcjonalności.
Prawdziwy potencjał drzemie właśnie w przekazywaniu tych typów do aplikacji bez potrzeby rozbudowy struktury tabel w bazie, ciekawe może być to, że atrybuty zawarte w typie mogą być niedostępne z poziomu bazy danych. Pomysłów na wykorzystanie UDT może być naprawdę wiele.
Kończąc chciałbym poprosić, aby z pewnym przymrużeniem oka potraktować to jak posługuję się kryptografią w zastosowanych metodach. Raczej miały one „znaczenie edukacyjne”. Przede wszystkim chciałem pokazać, że własny UDT może być czym więcej niż strukturą Punkt z dwoma współrzędnymi.
5. Bibliografia
W załączniku znajdują się : skrypt i 2 projekty odpowiadające przykładom z tego artykułu.Załączniki: