Mateusz Mazurek – programista z pasją

Python, architektura, ciekawostki ze świata IT

Programowanie

UDT – Czyli własne typy danych w MsSql’u

Cześć! Cieszę się, że mnie odwiedziłeś/aś. Zanim przejdziesz do artykułu chciałbym zwrocić Ci uwagę na to, że ten artykuł był pisany kilka lat temu (2014-02-15) miej więc proszę na uwadzę że rozwiązania i przemyślenia które tu znajdziesz nie muszą być aktualne. Niemniej jednak zachęcam do przeczytania.

Wiele firm i osób prywatnych wykorzystuje MsSqla w swoich projektach. Jest to fajna baza danych, ma tez kilka ciekawych feature’ów. Dziś omówię jeden z nich.

Mało osób wie że MsSql jest nie tylko relacyjną bazą danych, ale i obiektową, co oznacza że dane mogą być obiektami.

Pokażę jak stworzyć własny typ danych w MsSqlu – UDT (User Defined Types).
Jest to bardzo przydatna umiejętność, gdyż może zaoszczędzić sporo problemów przy projektowaniu baz danych, a jest słabo opisana w internecie.

Ok, zacznijmy od procesu przez jaki będziemy musieli przejść:

  1. Instalacja Visual Studio
  2. Napisanie struktury w C#
  3. Kompilacja jej do pliku *.dll
  4. Import pliku *.dll w SQL’u
  5. Używanie swojego typu danych

Ok, punkt 1 pominę, gdyż instalacje jego nie jest skomplikowana, dodam tylko że przykład piszę na Visual C# Express 2010 oraz SQL’u 2008 – te wersję mam gdyż korzystam nadal z Win XP ;)

Dobra, spróbujmy rozwiązać taki problem:

Pewna firma która produkuje koła zębate i chce mieć w bazie danych, w przejrzysty sposób pokazane jakiego typu, o jakich zębach oraz o jakiej średnicy koła są aktualnie w magazynie.

TYPY:

TYP1 – w skrócie T1

TYP2 – w skrócie T2

TYP3 – w skrócie T3

ŚREDNICA – liczba podwójnej precyzji

ZĘBY:

PROSTE – w skrócie P

ŚRUBOWE – w skrócie S

DASZKOWE – w skrócie D

ŁUKOWE – w skrócie L

Zęby dodatkowo mają swoją ilość, która jest liczbą całkowitą, a więc np P10, albo L20 jest poprawnym oznaczeniem. Zakładamy że wysokość zębów jest dla każdego koła taka sama (raczej nie spotykane w rzeczywistości  ;) ).

Ok, opracujmy teraz jak będzie wyglądało oznaczenie koła:

Typ/Średnica/Ząb

a więc poprawnym oznaczeniem będzie np. T2/2.38/P20

Zebraliśmy wymagania, a więc przystępujemy do pisania.

Tworzymy nowy projekt w Visualu i dodajemy nową klasę (plik *.cs) i dodajemy referencję do Microsoft.SqlServer.Types.dll.

Następnie przystępujemy do właściwego pisania struktury ;)

[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedType(Format.UserDefined,MaxByteSize = 8000,
IsByteOrdered = true, ValidationMethodName = "Validate")]


Te linijki to swoista konfiguracja. Najważniejsze rzeczy z niej to rzecz jasna ValidationMethodName – czyli nazwa metody która sprawdza czy typ tworzymy z odpowiedniego Stringa i drugi ważny parametr to Format – mamy dwa sensowne typy Formatu – Native i UserDefined. Oba maja swoje plusy i minusy.

Native – w nim możemy używać w naszej strukturze tylko typów prostych, a dokładniej: bool, byte, sbyte, short, ushort, int, uint, long, ulong, float, double, SqlByte, SqlInt16, SqlInt32, SqlInt64, SqlDateTime, SqlSingle, SqlDouble, SqlMoney no i inne typy użytkownika które zawierają te typy jako pola. I nic więcej nie musimy robić.

UserDefined – możemy każdy typ użyć, ale musimy je ręcznie serializować.

My mamy w naszym oznaczeniu znaki, są to pojedyncze litery i można by się pokusić o to żeby interpretować je po stronie programu jako liczby (np. wg kodu ASCII), ale ja wybrałem drugą drogę – daje większe możliwości.

No ale wróćmy do kodu

1
2
3
4
5
6
7
8
9
10
11
12
13
14
[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedType(Format.UserDefined, MaxByteSize = 8000, IsByteOrdered = true, ValidationMethodName = "Validate")]

public struct Gear: INullable {
 private bool is_Null;
 private string cog;
 private double diameter;
 private string type;
 public bool IsNull {
  get {
   return (is_Null);
  }
 }
}

Zmienne:
is_Null – określa czy obiekt jest nullem.
cog – określa informację o zębach.
diameter – średnica.
type – typ.

Dodajmy pierwszą wymaganą metodę – Parse(SqlString):

1
2
3
4
5
6
7
8
9
10
[SqlMethod(OnNullCall = false)] public static Gear Parse(SqlString s) {
  if (s.IsNull) return Null;
  Gear fn = new Gear();
  string[] values = s.Value.Split(@ "/".ToCharArray());
  fn.type = values[0];
  fn.diameter = Double.Parse(values[1]);
  fn.cog = values[2];
  if (!fn.Validate()) throw new ArgumentException("Invalid values!");
  return fn;
 }

Zostajemy przy formacie ze znakiem „/”, a więc jako parametr dostajemy string sql’owy i rozbijamy go względem znaku „/” a odpowiednie części zapisujemy do zmiennych.

Pojawiła się tutaj funkcja Validate() – sprawdza ona czy string który przekazaliśmy nadaje się na zamianę na obiekt. Napiszmy tę funkcję:

1
2
3
4
5
 private bool Validate() {
  if ((this.cog.Contains("P") || this.cog.Contains("S") || this.cog.Contains("D") || this.cog.Contains("L")) && this.type.Contains("T")) {
   return true;
  } else return false;
 }

Używamy w niej też pola Null, którego nigdzie nie ma, a więc napiszmy go, wraz z właściwością GET:

1
2
3
4
5
6
7
 public static Gear Null {
 get {
  Gear pt = new Gear();
  pt.is_Null = true;
  return pt;
 }
}

No i fajnie, a teraz czas na kolejną wymaganą metode – ToString() – dzięki niej możemy w SQLu wyświetlić obiekt

1
2
3
4
5
6
7
8
9
10
 public override string ToString() {
  if (this.IsNull) return "NULL";
  else {
   StringBuilder builder = new StringBuilder();
   builder.Append(type + "/");
   builder.Append(diameter + "/");
   builder.Append(cog);
   return builder.ToString();
  }
 }

No tutaj widać co robimy :)
I teraz chyba najtrudniejsza część – musimy zaimplementować interfejs IBinarySerialize, po czym jego metody:
public void Read(System.IO.BinaryReader r);
public void Write(System.IO.BinaryWriter w);

Żeby przeczytać coś, pierw musimy wiedzieć jak jest zapisane, więc zaczniemy od metody Write(). Ogólnie chodzi o to zeby zamienić pola naszej struktury na ciąg binarny.
A więc zaplanujmy to. Niech string będzie miał 20 bajtów. I w sumie to jedyne założenie :P no może kolejność, u nas wychodzi (taka sama kolejność jak przy oznaczeniu – Typ – Średnica – Zęby) String – double – String. A więc do dzieła ;)

1
2
3
4
5
6
7
8
9
10
11
12
13
 public void Write(System.IO.BinaryWriter w) {
  int maxStringSize = 20;
  string paddedString;
  paddedString = type.PadRight(maxStringSize, '\0'); //konczymy string binarnym zerem
  for (int i = 0; i < paddedString.Length; i++) {
   w.Write(paddedString[i]); //zapisujemy po literce
  }
  w.Write(diameter); //zapisujemy srednice
  paddedString = cog.PadRight(maxStringSize, '\0'); // to samo z zebami
  for (int i = 0; i < paddedString.Length; i++) {
   w.Write(paddedString[i]);
  }
 }

I metoda odwrotna – korzystamy z informacji w jakiej kolejności zapisaliśmy dane.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
 public void Read(System.IO.BinaryReader r) {
  int maxStringSize = 20;
  char[] chars;
  int stringEnd;
  string stringValue;
  chars = r.ReadChars(maxStringSize);
  stringEnd = Array.IndexOf(chars, '\0');
  if (stringEnd == 0) {
   stringValue = null;
   return;
  }
  stringValue = new String(chars, 0, stringEnd);
  this.type = stringValue;
  this.diameter = r.ReadDouble();
  chars = r.ReadChars(maxStringSize);
  stringEnd = Array.IndexOf(chars, '\0');
  if (stringEnd == 0) {
   stringValue = null;
   return;
  }
  stringValue = new String(chars, 0, stringEnd);
  this.cog = stringValue;
 }

I całość kodu:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
 using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using Microsoft.SqlServer.Types;
using System.Data.SqlClient;
[Serializable][Microsoft.SqlServer.Server.SqlUserDefinedType(Format.UserDefined, MaxByteSize = 8000, IsByteOrdered = true, ValidationMethodName = "Validate")] public struct Gear: INullable, IBinarySerialize {
  private bool is_Null;
  private string cog;
  private double diameter;
  private string type;
  public bool IsNull {
   get {
    return (is_Null);
   }
  } [SqlMethod(OnNullCall = false)] public static Gear Parse(SqlString s) {
   if (s.IsNull) return Null;
   Gear fn = new Gear();
   string[] values = s.Value.Split(@ "/".ToCharArray());
   fn.type = values[0];
   fn.diameter = Double.Parse(values[1]);
   fn.cog = values[2];
   if (!fn.Validate()) throw new ArgumentException("Invalid values!");
   return fn;
  }
  private bool Validate() {
   if ((this.cog.Contains("P") || this.cog.Contains("S") || this.cog.Contains("D") || this.cog.Contains("L")) && this.type.Contains("T")) {
    return true;
   } else return false;
  }
  public static Gear Null {
   get {
    Gear pt = new Gear();
    pt.is_Null = true;
    return pt;
   }
  }
  public override string ToString() {
   if (this.IsNull) return "NULL";
   else {
    StringBuilder builder = new StringBuilder();
    builder.Append(type + "/");
    builder.Append(diameter + "/");
    builder.Append(cog);
    return builder.ToString();
   }
  }
  public void Write(System.IO.BinaryWriter w) {
   int maxStringSize = 20;
   string paddedString;
   paddedString = type.PadRight(maxStringSize, '\0');
   for (int i = 0; i < paddedString.Length; i++) {
    w.Write(paddedString[i]);
   }
   w.Write(diameter);
   paddedString = cog.PadRight(maxStringSize, '\0');
   for (int i = 0; i < paddedString.Length; i++) {
    w.Write(paddedString[i]);
   }
  }
  public void Read(System.IO.BinaryReader r) {
   int maxStringSize = 20;
   char[] chars;
   int stringEnd;
   string stringValue;
   chars = r.ReadChars(maxStringSize);
   stringEnd = Array.IndexOf(chars, '\0');
   if (stringEnd == 0) {
    stringValue = null;
    return;
   }
   stringValue = new String(chars, 0, stringEnd);
   this.type = stringValue;
   this.diameter = r.ReadDouble();
   chars = r.ReadChars(maxStringSize);
   stringEnd = Array.IndexOf(chars, '\0');
   if (stringEnd == 0) {
    stringValue = null;
    return;
   }
   stringValue = new String(chars, 0, stringEnd);
   this.cog = stringValue;
  }
 }

Kompilujemy to do postaci pliku *.dll i otwieramy Management Studiu SQL’a… Pierwsze co musimy zrobić to stworzyć ASSEMBLY – to jest trochę taki pomost między SQLem a innymi językami:

1
 CREATE ASSEMBLY Gear FROM 'E:\Documents and Settings\Matt\Moje dokumenty\Visual Studio 2010\Projects\sql\SQL\SQL\bin\Release\SQL.dll' WITH PERMISSION_SET = SAFE;

Z naszego Assembly tworzymy teraz typ:

1
 CREATE TYPE dbo.Gear EXTERNAL NAME Gear.[Gear];

Żeby udowodnić że to na prawdę działa, stwórzmy tabelę:

1
 CREATE TABLE dbo.Gears (ID INT IDENTITY(1,1) PRIMARY KEY, gear Gear);

prosta tabela, dwie kolumny, z czego jedna z nich jest naszego typu. No i dodajmy do niej coś:

1
2
INSERT INTO dbo.Gears VALUES( CONVERT(Gear, 'T1/24,3/P20'));
INSERT INTO dbo.Gears VALUES( CONVERT(Gear, 'T3/120,5/L56'));

Funkcji CONVERT używać musimy. Teraz czas na ostateczne sprawdzenie, napisanie SELECTa:

1
 SELECT ID, gear FROM dbo.Gears;

Ale po wykonaniu dostaliśmy, hmm.. Coś dziwnego!

ID gear
1 0x5431000000000000000000000000000000000000CDCCCCCCCC4C384050323000000000000000000000000000000000006E861BF0F9210940
2 0x54330000000000000000000000000000000000000000000000205E404C353600000000000000000000000000000000006E861BF0F9210940

Dostaliśmy reprezentację bajtową naszego obiektu. Poprawmy to:

1
SELECT ID, gear.ToString() FROM dbo.Gears;

I teraz jest poprawnie:

ID (No column name)
1 T1/24,3/P202
2 T3/120,5/L56

Uff i to by było na tyle. Zobacz sobie że nie można dodać nieprawidlowego wpisu, np:

1
INSERT INTO dbo.Gears VALUES( CONVERT(Gear, 'P3/120,5/L56'));
Wrzuci wyjątek, gdyż nie ma typu P.

Typy użytkownika są dużo bardziej przydatne niż tutaj opisałem. Przede wszystkim mogą mieć metody i komunikować się z innymi obiektami – co daje nam ogromne możliwości bazodanowe.

Dzięki za wizytę,
Mateusz Mazurek

A może wolisz nowości na mail?

Subskrybuj
Powiadom o
guest

Witryna wykorzystuje Akismet, aby ograniczyć spam. Dowiedz się więcej jak przetwarzane są dane komentarzy.

1 Komentarz
Inline Feedbacks
View all comments
Piotr Łuszcz

Wpis wysokich lotów, pozdrawiam serdecznie