Mateusz Mazurek – programista z pasją

Python, architektura, ciekawostki ze świata IT

    • Programowanie

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

    Mateusz M. Opublikowane w
    🕑 7 minuty czytania
    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?

    Podobne artykuły:

    Otagowano:
    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

    1
    Odpowiedz

    Cześć

    Muszę przyznać że niesamowicie fajnie Cię tu widzieć :)

    Informatyką pasjonuję się już od kilkunastu lat a ten blog jest odzwierciedleniem moich zainteresowań jak i chęcią podzielenia się nimi. Jeśli uważasz że znalazłeś tu coś interesującego – podziel się tym z innymi :)

    Dzięki ogromne!

    Serie artykułów

    serie
    Zerknij na pełne serie artykułów!

    Czytam aktualnie

    Przeczytane książki