Nauka SQL – aplikacja do nauki SQL

Mam przyjemność zaprezentować wersję BETA skryptu/aplikacji do nauki SQL.

aplikacja dostępna tutaj

Aplikacja pozwala na pisanie zapytań SQL i ich testowanie. W chwili obecnej obsłużony został tylko dialekt MySQL.
Trwają prace nad obsługą innych baz (Oracle, SQL Server).

Równolegle szykowane są kolejne rozdziały z zadaniami.

Zapraszam do komentowania aplikacji, feedback mile widziany.

ADO.NET zapytania parametryzowane – C#

W poprzednim wpisie pokazałem, jak napisać prostą klasę w C#, dzięki której można pobierać wyniki prostych zapytań typu select. W tym wpisie pokażę, jak parametryzować zapytania i jak tego nie robić. Zakładam, że czytając ten post, masz drogi czytelniku jakąś klasę na wzór z poprzedniego posta.

W poprzednim poście aplikacja potrafiła wykonać zapytanie ‘select * from klienci’, a gdybyśmy chcieli pokazać konkretnego klienta? Normalnie w SSMS wystarczy napisać zapytanie

select * from klienci where klient_id = 1

Jeśli powyższe zapytanie jest niejasne, zapraszam do lektury o filtrowaniu danych (kurs sql). Oczywiście można w zapytaniu podstawić jakąkolwiek inną tabelę i kolumnę, przykłady opieram na poprzednich postach (zestawy szkoleniowe).

Powyższe zapytanie zwróci nam jeden wiersz.


Zanim przejdziemy do tak zwanego “kodzenia”, jedna uwaga, zmieniłem komputer, na którym prezentuję kod, więc nazwy aplikacji i niektórych kontrolek i obiektów mogły się zmienić.

Gotową klasę można pobrać tutaj
 

By wykonać przykład, który chcę zaprezentować, musimy mieć projekt, posiadający jedną formę, na której znajduje się guzik oraz datagridview.

Gdy nasze zapytanie wkleimy do kodu, gdzie wykonuje się kod po kliknięciu guzika
private void button1_Click(object sender, EventArgs e)
{
jodb db = new jodb("localhost\\sqlexpress", "zajecia");
dataGridView1.DataSource = db.pobierz_dane("select * from klienci where klient_id=1");
}

Po skompilowaniu, uruchomieniu i kliknięciu guzika pojawi się jeden wiersz w DataGridView:

Oczywiście bez sensu jest zmieniać kod, by zobaczyć inny rekord. Dołóżmy na formę textboxa.

Teraz załóżmy, że użytkownik aplikacji zna numery identyfikacyjne klientów i chce oglądać pojedynczych klientów. Do tego celu będzie w textboxa (pole tekstowe) wpisywał numer klienta.

Najprostszą drogą do osiągnięcia tego celu jest konkatenacja w zapytaniu:
if (textBox1.Text.Length > 0)
{

dataGridView1.DataSource = db.pobierz_dane(“select * from klienci where klient_id=” + textBox1.Text);
}

Powyższy oczywiście zadziała, jeśli użytkownik wpisze liczbę do pola tekstowego. Więc, generalnie nie jest to najlepsze rozwiązanie. Dodatkowo, przy takim rozwiązaniu narażamy się na SQL Injection, użytkownik może dopisać część zapytania, która w skrajnych przypadkach może doprowadzić do katastrofalnych zmian w bazie danych.

Przykład na to, że działa:

Przykład na to, że można popsuć coś w ten sposób:

W między czasie, treść zapytania przeniosłem do zmiennej q, tak by móc zdebuggować program i zobaczyć, jak wygląda zapytanie dostarczane do funkcji. Od razu widać, że taki sposób parametryzacji jest bardzo niebezpieczny.

Aby zrobić to poprowanie, powinniśmy skorzystać z obiektu SqlParameter. Do tego celu najlepiej zmodyfikować naszą klasę do obsługi bazy danych.

Skopiujmy naszą funkcję służącą do pobierania danych i dodajmy argumenty wejściowe:

public DataTable pobierz_dane(string q,string nazwa_p,object wartosc_p)
{
..

}

Teraz musimy dodać Parametr do obiektu SqlCommand, tak aby nasza funkcja wyglądała, np. tak:
public DataTable pobierz_dane(string q,string nazwa_p,object wartosc_p)
{
DataTable dt = new DataTable(); // deklaracja i utworzenie instancji obiektu DataTable o nazwie dt
SqlDataReader dr; // deklaracja obiektu SqlDataReader o nazwie dr
SqlCommand sqlc; // Deklaracja obiektu SqlCOmmand

sqlc = new SqlCommand(q);
// utworzenie instancji SQLCommand ktora ma wykonac zapytanie podane jako parametr
// w zmiennej q

sqlc.Connection = this.polaczenie; // wskazanie polaczenia do bazy danych
SqlParameter par = new SqlParameter(nazwa_p, wartosc_p); // stworzenie nowej instancji parametru
sqlc.Parameters.Add(par); // dodanie utworzonego parametru do sqlCommand

dr = sqlc.ExecuteReader(); //wykonanie zapytanie i utworzenie wskaznika dr
dt.Load(dr); //zaladowanie danych do obiektu DataTAble
return dt; // zwrocenie danych

}

Dodaliśmy te dwie linie:
SqlParameter par = new SqlParameter(nazwa_p, wartosc_p); // stworzenie nowej instancji parametru
sqlc.Parameters.Add(par); // dodanie utworzonego parametru do sqlCommand

Musimy je dodać w miejscu, gdzie już mamy utworzoną instancję obiektu SqlCommand. Kolejność dodawania nie ma znaczenia. Parametry należy dodać do obiektu przed wywołaniem zapytania (metoda ExecuteReader).

Gdy już mamy gotową metodę, możemy zmienić nieco kod w naszej metodzie wywoływanej po kliknięciu guzika:

private void button1_Click(object sender, EventArgs e)
{
jodb db = new jodb("localhost\\sqlexpress", "zajecia");

string q = “select * from klienci where klient_id=@id”;
dataGridView1.DataSource = db.pobierz_dane(q,”@id”,textBox1.Text);
}
}

Jak widać, w zapytaniu pojawił się @id. Jest to wskaźnik parametru i informacja o jego nazwie, w przypadku SQLServer parametry powinny się zaczynać się od @. Teraz pod @id, przy wykonywaniu zapytania zostanie podstawiona wartość z pola tekstowego, jednak środowisko .NET zadba za nas o niezbędne walidacje tak, by wszystko zadziałało jak należy:

Teraz próba sql injection skończy się wyjątkiem:

Nasz kod nie posiada bloków try i catch, więc aplikacja najzwyczajniej w świecie się wywali. Kwestii Obsługi błędów poświecę osobny wpis.

Oczywiście może zdarzyć się sytuacja, że będziemy potrzebowali większej ilości parametrów, do tego celu możemy skorzystać ze słowników zaimplementowanych w języku C#. Potrzebujemy znowu przeciążyć metodę od pobierania danych.

Słownik w C# jest listą par obiektów, Para jest kluczem i wartością (tak samo jak SQLParameter). Oczywiście możemy przekazać od razu listę SQLParameter do naszej funkcji lub gotowy obiekt SQLCommand. Ale pokazana ścieżka wymusza użycie obiektów Sql* w jednym miejscu. Tak więc nasza funkcja może wyglądać tak:

public DataTable pobierz_dane(string q, Dictionary < string,object > lista_parametrow )
{
DataTable dt = new DataTable(); // deklaracja i utworzenie instancji obiektu DataTable o nazwie dt
SqlDataReader dr; // deklaracja obiektu SqlDataReader o nazwie dr
SqlCommand sqlc; // Deklaracja obiektu SqlCOmmand

sqlc = new SqlCommand(q);
// utworzenie instancji SQLCommand ktora ma wykonac zapytanie podane jako parametr
// w zmiennej q

sqlc.Connection = this.polaczenie; // wskazanie polaczenia do bazy danych
SqlParameter par; // deklaracja zmiennej
foreach (KeyValuePair<string, object> parametr in lista_parametrow)
//iteracja po wszystkich elementach slownika
{
par = new SqlParameter(parametr.Key, parametr.Value); // stworzenie nowej instancji parametru
sqlc.Parameters.Add(par); // dodanie utworzonego parametru do sqlCommand
}

dr = sqlc.ExecuteReader(); //wykonanie zapytanie i utworzenie wskaznika dr
dt.Load(dr); //zaladowanie danych do obiektu DataTAble
return dt; // zwrocenie danych

}

Jak widać w pierwszej linii:
public DataTable pobierz_dane(string q, Dictionary< string,object > lista_parametrow )
mamy deklarację zmiennej typu słownikowego: Dictionary< string,object > lista_parametrow

Później dodaliśmy pętlę foreach (co oznacza dla każdego obiektu w liście/słowniku/tablicy etc. wykonaj…)

foreach (KeyValuePair< string, object > parametr in lista_parametrow)
//iteracja po wszystkich elementach slownika
{
par = new SqlParameter(parametr.Key, parametr.Value); // stworzenie nowej instancji parametru
sqlc.Parameters.Add(par); // dodanie utworzonego parametru do sqlCommand
}

Dla każdej pary słownika tworzymy obiekt KeyValuePair, jest to obiekt, potrafiący przechowywać dwie wartości jednocześnie. Jest to po prostu struktura/obiekt, posiadający dwie właściwości (key, value). W pętli powstają nowe instancje obiektu i dodawane są do listy parametrów. Mimo, iż obiekt par za każdym razem – mogłoby się wydawać – będzie nadpisany, o tyle referencje do pamięci będą poprawne i lista parametrów w obiekcie SQLCommand tak naprawdę będzie zawierać pełną listę dostarczonych parametrów. Przeróbmy nasz przykład, by działał ze słownikiem (na razie dodamy tylko jeden parametr).

private void button1_Click(object sender, EventArgs e)
{
jodb db = new jodb("localhost\\sqlexpress", "zajecia");

Dictionary<string, object> lista_par = new Dictionary< string, object >();
lista_par.Add(“@id”, textBox1.Text);
string q = “select * from klienci where klient_id=@id”;
dataGridView1.DataSource = db.pobierz_dane(q,lista_par);
}

Tworzymy obiekt (instancję) słownika i dodajemy do niej jedną parę: klucz i wartość (key,value).
Value jest typem object, co oznacza, że możemy podstawiać tam dowolny typ obiektu, nieważne, czy to będzie data, napis, czy liczba, ten kod zadziała i dane zostaną odpowiednio zwalidowane.

Żeby sprawdzić, czy zadziała to dla większej ilości parametrów, dodajmy jeszcze jedno pole tekstowe. Wyobraźmy sobie, że będziemy porównywać dwóch klientów:

private void button1_Click(object sender, EventArgs e)
{
jodb db = new jodb(“localhost\\sqlexpress”, “zajecia”);

Dictionary<string, object> lista_par = new Dictionary< string, object >();
lista_par.Add(“@id”, textBox1.Text);
lista_par.Add(“@id2”, textBox2.Text);
string q = “select * from klienci where klient_id=@id or klient_id=@id2”;
dataGridView1.DataSource = db.pobierz_dane(q,lista_par);
}
}

Dodajemy jeszcze jeden parametr do zapytania, oraz dodajemy go do słownika, efekt:

ADO.NET – podłączenie się do bazy SQL Server w C#

Tworzenie aplikacji w środowisku .NET, które wykorzystują bazy danych (a zwłaszcza SQL Server) jest stosunkowo proste. Na początek potrzebujemy Visual Studio (najlepiej darmowy express C#) oraz SQL Server (też najlepiej express) lub SQL Server Client (w przypadku gdy SQL Server nie jest zainstalowany na komputerze na którym prowadzimy prace developerskie).

Ten wpis zawiera dość długi wstęp dla osób o niskim obeznaniu ze środowiskiem VS, dlatego jeśli jesteś bardziej zaawansowanym użytkownikiem przewiń proszę tekst.

Gotową klasę można pobrać tutaj

Na początek musimy utworzyć projekt w VS, najlepiej wybrać Windows Form Application (jeśli korzystamy z VS innego niż Express proszę zaznaczyć język C#).

Poniższe Screenshoty pochodzą z wersji VS 2010 Professional.

Utworzenie nowego projektu:

Po utworzeniu projektu naszym oczom powinna ukazać się formatka (form1) nazywana skrótowo formą:

Z lewej strony znajduje się toolbox (tam znajdziemy różne kontrolki). Metodą przeciągnij upuść można na formę bezpośrednio z Toolboxa przeciągać wszystkie dostępne kontrolki. Proponuję zacząć od Button’a (guzika).

Button znajduje się nad zaznaczonym na screenshocie checkbox’em. Kontrolki są posortowane alfabetycznie.


Finalnie na formie powinien pojawić się button1 nasz guzik. Z prawej strony ekranu powinien być object(solution) explorer i properties.

Po jednokrotnym kliknięciu guzika na formie w properties zobaczymy jego właściwości. Wszystkie te właściwości są dostępne z poziomu kodu (do którego przejdziemy za chwilę). Proponuję zwrócić uwagę na właściwość (Name) jest to nazwa naszego obiektu. Możemy ją zmienić – co zalecam uczynić. W momencie gdy guzików pojawi się więcej będzie trudno nad tym zapanować. Ja mój guzik nazwę pobieracz. Jak widać po zmianie nazwy opis guzika w ogóle się nie zmienia, za napis na guziku odpowiada właściwość Text, można ją dowolnie zedytować. Zalecam także przejrzenie innych dostępnych właściwości.
Gdy już skonfigurujemy guzik tak by nam odpowiadał możemy w niego 2 razy kliknąć, otworzony nam się okno z kodem odpowiedzialnym za stworzenie formy, oraz pojawi się tam metoda obsługująca zdarzenie (event) kliknięcia myszą w guzik.
cały kod powinien wyglądać +/- tak:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace WindowsFormsApplication2
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}

private void pobieracz_Click(object sender, EventArgs e)
{

}
}
}

Omawiając kod od samej góry:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

Najprościej rzecz ujmując jest poinformowanie jakich przestrzeni nazw chcemy używać, tworząc obiekt możemy napisać w kodzie System.Data.SqlClient.SqlCommand sqlc = new System.Data.SqlClient.SqlCommand(); albo możemy dodać przestrzeń nazw using System.Data.SqlClient i wtedy w kodzie napiszemy SqlCommand sqlc = new SqlCommand();. Tak więc using wykorzystujemy tylko dla skrócenia zapisów i sprawienia by były bardziej czytelne.
Kolejno mamy:

namespace WindowsFormsApplication2
{

jest to deklaracja naszej własnej przestrzeni nazw. Standardowo VS tworzy przestrzeń nazw tożsamą z nazwą aplikacji/projektu/solucji.

public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}

Teraz mamy deklarację publicznej klasy (public) o nazwie Form1 dziedziczącej (:) z ogólnej klasy Form. Pojawia się jeszcze słowo partial, które informuje kompilator że ta klasa może być deklarowana w wielu plikach. Ten zabieg pozwala zaprezentować nam kod który rzeczywiście jest dla nas istotny, podczas przeciągania kolejnych obiektów na formę VS tworzy wiele linii kodów które są przed nami ukrywane, dla zachowania czystości i pewnej czytelności.

public Form1() to konsturktor klasy Form1, konstruktor poznajemy zawsze po tym że nazywa się tak samo jak klasa oraz konstruktor nie ma definicji tego co zwraca (brak nazwy typu obiektu i słowa void przed nazwą metody). W konstruktorze mamy jedną funkcję która inicjuje nam kontrolki na formie.

Poniżej zostaje nam obsługa kliknięcia w guzik:

private void pobieracz_Click(object sender, EventArgs e)
{

}

moj guzik nazywa się pobierz więc VS automatycznie utworzyło funkcję według konwencji nazewnictwa ze środowiska .NET, pomiędzy klamrami nalezy dopisać kod który zostanie wykonany po kliknięciu guzika. Jako że poniższy post jest de facto o bazach danych a nie podstawach programowania w .NET przejdziemy do pisania kodu który umożliwi nam podłączenie się do bazy danych.

Dla zachowania czystości kodu proponuję utworzyć nową klasę, aby to zrobić należy z górnego menu wyrać project/add new item, albo add class.

VS utworzy nam klasę nazwaną tak samo jak plik w przestrzeni nazw naszej aplikacji:
namespace WindowsFormsApplication2
{
class jodb
{
}
}

Na samej górze w sekcji using warto (należy) dodać:

using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Data.SqlTypes;

Teraz musimy utworzyć konstruktor, warto się zastanowić w tym miejscu w jaki sposób chcemy łączyć się z bazą danych. Mam na myśli tutaj autentykację windows(domenową) lub autentykację SQL Server. Standardowo SQL Server instalowany jest z włączoną autoryzacją windows. Oznacza to że otwierając połączenie z SQL Serverem zostanie przekazany nasz login do komputera i na tej podstawie zostaną nam przyznane uprawnienia. Autoryzacja SQL Server standardowo jest wyłączona, można ją włączyć w dość prosty sposób (opiszę to w osobnym poście).
Kod który stworzę pozwala na połączenie się jedną i drugą metodą.

W klasie jodb należy stworzyć konstruktory, które w ostatecznym wyglądzie powinny obsłużyć podłączenie się do bazy. Na początek proponuję by kod klasy wyglądał tak:

class jodb
{

public jodb(string user, string pass, string instance, string dbdir)
{

}

public jodb(string instance, string dbdir)
{

}
}

Mimo iż on nic nie robi sensownego, możemy powiedzieć że mamy 2 konstruktory i zależnie od wyboru możemy podłączyć się za pomocą jednej z dwóch metod autentykacji. Warto pamiętać także o komentarzach które pozwalają później w łatwy sposób operować na wytworzonym kodzie. Dodająć /// linię nad deklaracją metody VS wygeneruje nam komentarze które uzupełnione o nasze dopiski będą wyświetlały się w intelisense (podpowiedzi podczas pisania kodu).
Gdy nasz kod wygląda +\- tak możemy przejść do sedna sprawy, musimy utworzyć połączenie do SQL Server. W obu przypadkach posłużymy się obiektem SQLConnection.

Proponuję stworzyć właściwość połączenie, która będzie widoczna dla całej klasy odpowiedzialnej za łączenie się SQL Serverem.

class jodb
{
private SqlConnection polaczenie; //obiekt polaczenia widoczny w calej klasie

W tym celu po klamrze otwierającej definicję klasy dodajemy private (czyli obiekt/właściwość widoczna tylko wewnątrz klasy – SQLConnection -> typ opbiektu/właściwości klasy, oraz polaczenie czyli nazwa naszego obiektu.
W takim wypadku mamy tylko deklarację zmiennej/obiektu bez utworzenia instancji. Teraz w konstruktorach należy dodać utworzenie instancji obiektu SQLConnection oraz przypisać właściwość ConnectionString i otworzyć połączenie. Teraz nasz kod powinien zacząć wyglądać tak:

class jodb
{
private SqlConnection polaczenie; //obiekt polaczenia widoczny w calej klasie

///
/// Konstruktor do tworzenia polaczenia za pomoca autoryzacji SQL Server
///

///uzytkownik ///haslo ///nazwa instancji ///nazwa bazy danych public jodb(string user, string pass, string instance, string dbdir)
{
polaczenie = new SqlConnection();
polaczenie.ConnectionString = “”;
polaczenie.Open();
}
///
/// Konstruktor do tworzenia polaczenia za pomoca autoryzacji windows
///

///nazwa instancji ///nazwa bazy danych public jodb(string instance, string dbdir)
{
polaczenie = new SqlConnection();
polaczenie.ConnectionString = “”;
polaczenie.Open();
}
}

Do pełni szczęścia brakują nam jeszcze connectionStringi, czyli ciągi wskazujące na konkretną bazę danych, można je znaleść na connectionstrings.com – ja poniżej przedstawiam te z których korzystam (dla mnie są wystarczające, jednakże istnieje możliwość zdefiniowania większej ilości parametrów).

Po uzupełnieniu ConnectionString nasz kontruktor powinien wyglądać mniej więcej tak:

public jodb(string user, string pass, string instance, string dbdir)
{
polaczenie = new SqlConnection();
polaczenie.ConnectionString = "user id=" + user + ";" +
"password=" + pass + ";Data Source=" + instance + ";" +
"Trusted_Connection=no;" +
"database=" + dbdir + "; " +
"connection timeout=3";
polaczenie.Open();
}

łańcuch do łączenia z bazą wygląda tak:
“user id=” + user + ;password=” + pass + “;Data Source=” + instance+ “;Trusted_Connection=no;database=” + dbdir + “;connection timeout=3”;

znaki + są konkatenacją (łączeniem łańcuchów) i parametryzują nasz łańcuch tak by podstawić zmienne które podajemy jako argumenty. Powyższy ciąg służy do łączenia za pomocą autentykacji SQL Server.

W drugim kostruktorze stosujemy trochę krótszy ConnectionString:
polaczenie.ConnectionString = "Data Source=" + instance + ";" +
"Trusted_Connection=yes;" +
"database=" + dbdir + "; " +
"connection timeout=3";

Jak widać główna różnica polega na braku użytkownika i hasła oraz zmianie na trusted_connection=yes.
Powyższy kod warto uzupełnić o obsługę błędów Try i catch, ale zrobimy to później, na tym etapie nie jest nam to potrzebne. Na tym etapie warto skompilować projekt (wciskając F5) lub zieloną strzałkę. Jeśli wszystko dobrze pójdzie ujrzymy naszą formatkę jako osobną aplikację. W przeciwnym wypadku musimy usunąć wszystkie błędy. Proszę pamiętać że konstruktory muszą się nazywać tak samo jak klasa więc jeśli klasa nazywa się inaczej niż jodb to konstruktory także. Warto też przeczytać ewentualne komunikaty błędów i sprawdzić w google co one mogą znaczyć. Jeśli na tym etapie coś nie działa proszę o komentarz pod postem, postaram się pomóc.

Jeśli projekt się nam kompiluje trzeba przetestować połączenie.
Przejdźmy do kodu w pliku form1.cs

Do góry na belce mamy listę otwartych plików:

Możemy też w solution explorer kliknąc dwa razy na form1.cs i jak pokaże się forma kliknąc 2 razy guzik lub wcisnąć klawisz F7.

Do funkcji/zdarzenia/metody odpowiedzialnej za kliknięcie guzikiem dopiszmy kod który utworzy nam obiekt do obsługi bazy danych, dodatkowo także wraz z tym obiektem zostanie ustanowione połączenie z bazą.


Jak widać przy dobrym opisaniu własnego kodu, łatwe jest jego późniejsze wykorzystanie.
Teraz nasz kod form1.cs powinien wyglądać podobnie do:
namespace WindowsFormsApplication2
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}

private void pobieracz_Click(object sender, EventArgs e)
{
jodb baza = new jodb(“test”, “test”, “localhost\\s2012n”, “zajecia”);
}
}
}

Kompilujemy projekt, jeśli wszystko poszło ok po kliknięciu guzika nic się nie stanie. Jeśli coś jest nie tak albo projekt się nie skompiluje albo po kliknięciu guzika pojawi się wyjątek (exception) który poinformuje nas o błędzie. Warto zwrócić uwagę na podwójny \\ w nazwie instancji, \ jest znakiem wyjścia i znakiem wyjścia dla niego jest on sam stąd podwojenie. Proszę także pamiętać że SQL Server ma standardowo wyłączoną obsługę TCIP co oznacza że nie podłączymy się do niego za pomocą adresu IP ( tylko po nazwie hosta Windows) ew localhost.

To jest moment w którym zaczynają się schody i większośc ludzi ma problem z podłączeniem do bazy, najczęstszymi powodami są literówki w ConnectionString dlatego proszę go dokładnie sprawdzić w razie problemów, lub napisać komentarz postaram się pomóc.

Aby cały projekt miał sens brakuje nam metody która pozwoli pobrać nam dane z bazy. W poprzednich moich postach stworzona została baza zajęcia do której były dołączone dwa zestawy szkoleniowe, w dalszym kodzie będziemy się opierać na tej bazie.

Wracamy do pliku klasy do łączenia się z bazą danych, klikamy jego nazwę do góry na belce lub też klikamy go podwójnie w solution explorer.

Dopisujemy kolejną metodę publiczną, ja ją nazwę pobierz_dane.

///
/// Metoda do pobierana danych z SQL Server
///

///zapytanie sqL /// zwraca dane w obiekcie DataTable
public DataTable pobierz_dane(string q)
{
DataTable dt= new DataTable();

return dt;

}


Jak widać powyższy kod opatrzyłem komentarzami. Po słowie public mamy nazwę obiektu (DataTable) który oznacza co metoda zwróci, przygotowałem też wnętrze funkcji (wydmuszka) by było można skompilować projekt. W tym momencie ta metoda nie robi niczego sensownego po prostu za każdym razem zwróci pusty obiekt DataTable.
Za chwilę dopiszemy kod który pozwoli nam pobierać dane.

W tym celupotrzebujemy obiekt SQLCommand, któremu musimy wskazać z jakiego połączenia ma korzystać oraz jakie zapytanie ma wykonać, potrzebujemy także obiekt SQLDataReader, który to odczyta dane z bazy i na koniec trzeba te dane umieścić w obiekcie DataTable.

public DataTable pobierz_dane(string q)
{
DataTable dt= new DataTable(); // deklaracja i utworzenie instancji obiektu DataTable o nazwie dt
SqlDataReader dr; // deklaracja obiektu SqlDataReader o nazwie dr
SqlCommand sqlc; // Deklaracja obiektu SqlCOmmand

sqlc = new SqlCommand(q);
// utworzenie instancji SQLCommand ktora ma wykonac zapytanie podane jako parametr
// w zmiennej q

sqlc.Connection = this.polaczenie; // wskazanie polaczenia do bazy danych
dr =sqlc.ExecuteReader(); //wykonanie zapytanie i utworzenie wskaznika dr
dt.Load(dr); //zaladowanie danych do obiektu DataTAble
return dt; // zwrocenie danych

}

cała funkcja do pobierania danych powinna wyglądać jak powyżej.

Teraz należy utworzyć obiekt w którym możemy zaprezentować pobrane dane, do tego celu możemy skorzystać z obiektu DataGridView. Można go utworzyć z poziomu kodu, lub też przeciągnąć z toolbox’a. W tym celu klikamy w solution explorer form1.cs dwa razy i jak pojawi się forma klikamy toolbox i przeciągamy DataGridView.
Proszę zwrócić uwagę iż kontrolka DataGridView może nie znajdować się w zakładce common controls.

Gdy już mamy datagridview, (domyślnie VS utworzy nam obiekt o nazwie datagridview1), wchodzimy do kodu form1.cs (przycisk F7 lub podwójny klik na guzik). Przy tworzeniu datagridview VS chce wymusić na nas ustawienia DataSource, ale my z tego świadomie rezygnujemy(nie klikamy w nic na formie co się pojawia wokół Datagridview).

W kodzie form1.cs w funkcji która wykonuje się po kliknięciu guzikiem dopisujemy linijkę kodu:

dataGridView1.DataSource = baza_win.pobierz_dane("select * from klienci");

Z lewej strony mamy obiekt datagridview1 i jego właściwość Datasource, przypisujemy do niej wynik zwrócony przez metodę pobierz_dane obiektu baza_win (wcześniej był pokazywany obiekt baza, jednak dodałem sobie także obiekt baza_win który łączy się z SQLServer poprzez logowanie windows).
Metoda pobierz_dane przyjmuje jeden argument typu string i jest nim zapytanie SQL.
Cała metoda wygląda tak:

poniżej jeszcze kod:
private void pobieracz_Click(object sender, EventArgs e)
{
// jodb baza = new jodb("test", "test", "localhost\\s2012n", "zajecia");
jodb baza_win = new jodb("localhost\\s2012n", "zajecia");
dataGridView1.DataSource = baza_win.pobierz_dane("select * from klienci");
}

Teraz zostaje nam tylko skompilować i uruchomić projekt (F5).
Po uruchomieniu programu powinno się pokazać coś podobnego do:

Po kliknięciu guzika powinny w DatagridView pojawić się wyniki:

Zestaw szkoleniowy 2 – Select i złączenia (sql joins)

Wykonując zadania z zestawu szkoleniowego 1, można było się nauczyć podstaw zapytań SQL,
w zestawie drugim – ćwiczenia związane są z łączeniem danych z różnych tabel. By zachować maksymalną przejrzystość w przykładach zostaną użyte tylko 2 tabele.
Wykonanie poniższego skryptu utworzy odpowiednie tabele i wprowadzi odpowiednie dane, przed wykonaniem skryptu proszę się upewnić że takie struktury nie istnieją w bazie (jeśli wcześniej na tej bazie były wykonywane ćwiczenia z postu o kluczu obcym takie obiekty mogą już tam istnieć).

use zajecia
create table klienci
(klient_id int not null primary key identity(1,1),
klient_imie varchar(255),
klient_data datetime default getdate()
)

create table adresy (
adres_id int not null primary key identity(1,1),
adres_ulica varchar(255),
adres_miejscowosc varchar(255),
adres_klient_id int,
adres_typ int
)

insert into klienci (klient_imie) values ('Krzys');
insert into klienci (klient_imie) values ('Adam');
insert into klienci (klient_imie) values ('Andrzej');
insert into klienci (klient_imie) values ('Marek');
insert into klienci (klient_imie) values ('Ania');
insert into klienci (klient_imie) values ('Marysia');
insert into adresy (adres_miejscowosc,adres_ulica,adres_typ,adres_klient_id) values ('Poznan','Poznanska',1,1);
insert into adresy (adres_miejscowosc,adres_ulica,adres_typ,adres_klient_id) values ('Warszawa','Warszawska',2,1);
insert into adresy (adres_miejscowosc,adres_ulica,adres_typ,adres_klient_id) values ('Gniezno','Dworcowa',1,2);
insert into adresy (adres_miejscowosc,adres_ulica,adres_typ,adres_klient_id) values ('Krakow','Glowna',1,3);
insert into adresy (adres_miejscowosc,adres_ulica,adres_typ,adres_klient_id) values ('Gdansk','Bursztynowa',1,4)
insert into adresy (adres_miejscowosc,adres_ulica,adres_typ,adres_klient_id) values ('Olsztyn','Polska',1,5)
delete from klienci where klient_id = 5

Po wykonaniu tego skryptu proszę sprawdzić czy wyniki poniższych zapytań są zgodne:
select * from adresy;
select * from klienci

Jeśli wszystko się zgadza proszę wykonać poniższe ćwiczenia. W przypadku wątpliwości zapraszam do lektury:

Zadania

1) Pokaż w jakich miejscowościach mieszkają klienci (kolumny: klient_imie, adres_miejscowosc)
2) Pokaż w jakich miejscowościach klienci posiadają adresy typu 1 (kolumny: klient_imie, adres_miejscowosc)
3) Pokaż dla jakich adresów nie ma przypisanych klientów ( przygotuj min. 2 rozwiązania)
4) Pokaż dla jakich klientów nie ma przypisanych adresów ( przygotuj min. 2 rozwiązania)
5) Pokaż w jednej kolumnie imiona i nazwy miejscowości dla wszystkich klientów
6) Dlaczego klient z id=6 w poprzednim zadaniu wyświetlił się jako null? Jak to obejść?
7) Pokaż klientów którzy posiadają więcej niż 1 adres
8) pokaż klientów którzy posiadają tylko i wyłącznie 1 adres
9) Podziel klientów na grupy ze względu na pierwszą literę imienia i policz ile przypada adresów na każdą z grup.

Odpowiedzi:
1) Pokaż w jakich miejscowościach mieszkają klienci (kolumny: klient_imie, adres_miejscowosc)
select klient_imie,adres_miejscowosc from klienci
inner join adresy on
adres_klient_id=klient_id


Pozostaje jeszcze kwestia wyjaśnienia dlaczego Krzyś pokazał się dwa razy:
Dodając kolumny do zapytania:
select klient_imie,adres_miejscowosc,klient_id,adres_klient_id,adres_id from klienci
inner join adresy on
adres_klient_id=klient_id


Widać wyraźnie iż Krzys ma przypisane 2 różne adresy.

2) Pokaż w jakich miejscowościach klienci posiadają adresy typu 1 (kolumny: klient_imie, adres_miejscowosc)
select klient_imie,adres_miejscowosc from klienci
inner join adresy on
adres_klient_id=klient_id
where adres_typ = 1


Lub można też użyć innego zapisu:
select klient_imie,adres_miejscowosc from klienci
inner join adresy on
adres_klient_id=klient_id and adres_typ = 1

wynik zapytania będzie dokładnie taki sam.

3) Pokaż dla jakich adresów nie ma przypisanych klientów ( przygotuj min. 2 rozwiązania)
Poniżej rozwiązania korzystające ze złączeń:
select adres_miejscowosc,klient_id from adresy
left outer join klienci on
adres_klient_id = klient_id
where klient_id is null

Lub można na odwrót, korzystając ze złączenia prawostronnego:
select adres_miejscowosc,klient_id from klienci
right outer join adresy on
adres_klient_id = klient_id
where klient_id is null

Podane rozwiązania bazują na złączeniach, jednak proszę być świadomym że takie zadanie da się rozwiązać jeszcze inaczej.

4) Pokaż dla jakich klientów nie ma przypisanych adresów ( przygotuj min. 2 rozwiązania)
To zadanie jest analogiczne do poprzedniego.
select klient_imie,adres_miejscowosc from klienci
left outer join adresy on
adres_klient_id = klient_id
where adres_id is null

I tak samo jak poprzednie zadanie można je wykonać z pomocą złączenia prawostronnego:
select klient_imie,adres_miejscowosc from adresy
right outer join klienci on
adres_klient_id = klient_id
where adres_id is null

5) Pokaż w jednej kolumnie imiona i nazwy miejscowości dla wszystkich klientów
select adres_miejscowosc + ' ' + klient_imie from klienci
left outer join adresy on
adres_klient_id = klient_id

6) Dlaczego klient z id=6 w poprzednim zadaniu wyświetlił się jako null? Jak to obejść?
W poprzednich wynikach widać że ostatni wiersz wyświetlił się jako null, jest to spowodowane operacją konkatenacji łańcucha z wartością null (Imię + null) = null.

7) Pokaż klientów którzy posiadają więcej niż 1 adres
select klient_imie from klienci
inner join adresy on
adres_klient_id=klient_id
group by klient_imie
having count(*) > 1

8) pokaż klientów którzy posiadają tylko i wyłącznie 1 adres
select klient_imie from klienci
inner join adresy on
adres_klient_id=klient_id
group by klient_imie
having count(*) =1

9) Podziel klientów na grupy ze względu na pierwszą literę imienia i policz ile przypada adresów na każdą z grup.
select LEFT(klient_imie,1),COUNT(*) from klienci
inner join adresy on
adres_klient_id=klient_id
group by LEFT(klient_imie,1)

Zestaw szkoleniowy 1 – SQL – Podstawy SELECT

Postanowiłem udostępnić dość prosty zestaw szkoleniowy, który powinien pomóc w pierwszych chwilach z bazami danych. Zestaw jest przygotowany dla bazy SQL SERVER, a zadania są weryfikowane w środowisku SQL Server 2008R2.


create database zajecia;
use zajecia;
create table przyjaciele (
id int not null primary key identity(1,1), -- unikalny id - pole numeryczne
imie varchar(255), -- pole tekstowe
plec int, -- 0 dziewczyna 1 facet -- wartosc numeryczna
wiek int, -- wiek w latach -- wartosc liczbowa
zarobki int, -- zarobki w tysiacach -- wartosc liczbowa
kolor int) -- kolor skory 0 jasny 10 ciemny - skala -- wartosc liczbowa

--wstawienie danych do tabeli:
insert into przyjaciele (imie,plec,wiek,zarobki,kolor)
values ('Janek',1,19,2000,0);
insert into przyjaciele (imie,plec,wiek,zarobki,kolor)
values ('Ania',0,29,5000,0);
insert into przyjaciele (imie,plec,wiek,zarobki,kolor)
values ('Krysia',0,25,3000,0);
insert into przyjaciele (imie,plec,wiek,zarobki,kolor)
values ('Marek',1,25,3500,0);
insert into przyjaciele (imie,plec,wiek,zarobki,kolor)
values ('Krzysztof',1,32,1400,0);
insert into przyjaciele (imie,plec,wiek,zarobki,kolor)
values ('Marysia',0,21,1200,0);

Powyższy kod tworzy bazę zajecia, oraz dodaje do niej jedną tabelę o nazwie przyjaciele. Do utworzonej tabeli ładowane są przykładowe dane. Polecenia te można wkleić do okna zapytań w SSMS i wykonać (przycisk F5 lub execute).

po wykonaniu tych zapytań możemy otworzyć nowe query window (należy się upewnić czy korzystamy z bazy zajęcia) i można wykonać zapytanie:

select * from przyjaciele

Powyżej wynik zapytania.
Teraz lista zadań:
1) Pokaż tylko imiona.
2) Pokaż imiona zapisane Wielkimi literami
3) Pokaż 1 literę imienia i nadaj alias kolumnie pt PierwszaLitera
4) Pokaż pierwszą i ostatnią literę imienia
5) Pokaż imiona osób które zarabiają mniej niż 2000
6) Pokaż wszystkie kolumny w tabeli posortowane po zarobkach
7) Pokaż wiersze w których imiona kończą się na literę ‘a’
8) Pokaż wiersze w których imiona nie kończą się na literę ‘a’ oraz zarobki są mniejsze niż 2000
9) Pokaż minimalne zarobki, maksymalne, średnią.
10) pokaż średnie zarobki w rozbiciu na płeć.

Poniżej odpowiedzi:

1) Pokaż tylko imiona.
select imie from przyjaciele

2) Pokaż imiona zapisane Wielkimi literami

select UPPER(imie) from przyjaciele


3) Pokaż 1 literę imienia i nadaj alias kolumnie pt PierwszaLitera
select LEFT(imie,1) PierwszaLitera from przyjaciele
lub
select LEFT(imie,1) as PierwszaLitera from przyjaciele
lub
select SUBSTRING(imie,1,1) as PierwszaLitera from przyjaciele

4) Pokaż pierwszą i ostatnią literę imienia

select LEFT(imie,1) + RIGHT(imie,1) from przyjaciele

5) Pokaż imiona osób które zarabiają mniej niż 2000
select imie from przyjaciele where zarobki < 2000

6) Pokaż wszystkie kolumny w tabeli posortowane po zarobkach
select * from przyjaciele order by zarobki

7) Pokaż wiersze w których imiona kończą się na literę ‘a’
select * from przyjaciele where imie like '%a'
lub
select * from przyjaciele where right(imie,1) = 'a'

8) Pokaż wiersze w których imiona nie kończą się na literę ‘a’ oraz zarobki są mniejsze niż 2000
select * from przyjaciele where imie not like '%a' and zarobki < 2000
lub
select * from przyjaciele where RIGHT(imie,1)!='a' and zarobki < 2000

9) Pokaż minimalne zarobki, maksymalne, średnią.
select MIN(zarobki),MAX(zarobki), AVG(zarobki) from przyjaciele

10) pokaż średnie zarobki w rozbiciu na płeć.
select AVG(zarobki),plec from przyjaciele
group by plec

weryfikacja numeru identyfikacji podatkowej – NIP

W wielu bazach danych stajemy przed problemem weryfikacji różnych numerów używanych w administracji, opisałem już jak weryfikować PESEL.
Poniżej opis algorytmu weryfikacji NIP (numer identyfikacji podatkowej). Podstawowe informacje o weryfikacji numeru można znaleźć w wikipedii.
Dodam jednak kilka słów od siebie.
Swego czasu NIP’y podawało się z kreskami(myślnikami), nie wnoszą one kompletnie nic do procesu walidacji, więc funkcja je usuwa ze sprawdzanego ciągu znaków. Oczywiście NIP musi być ciągiem numerycznym i zawierać odpowiednią ilość znaków. W dodatku warto uczulić się na wartości skompromitowane które mogą mimo wszystko przechodzić przez walidację, ale mamy 100% pewność że nie istnieją. Są to numery które zaczynają się na 0 bądź posiadają 2 cyfrę w numerze = 0 i pierwszą różną od 1. Czyli NIP zaczynający się 20 jest na pewno niepoprawny mimo iż suma kontrolna może być poprawna.
Suma kontrolna w numerze NIP to suma mnożników dzielona przez 11.

Funkcja zwraca:
1 gdy NIP jest poprawny,
0 w przypadku złej sumy kontrolnej
-2 gdy wartość jest skompromitowana
-3 gdy wartość nie jest numeryczna
-4 gdy długość ciągu jest różna od 10
-5 gdy ciąg jest nieokreślony (null)

CREATE FUNCTION [dbo].[is_nip]
(
— Add the parameters for the function here
@nip varchar(255)
)
RETURNS int
AS
BEGIN

if @nip is null begin return -5 end

set @nip = replace(@nip,’-‘,”)
— zla dlugosc numeru nip
if len(@nip) != 10 begin return -4 end

if ISNUMERIC(@nip) = 0 begin return -3 end

— zwroc wartosci skompromitowane
if @nip=’0000000000′ begin return -2 end
if @nip=’1234567891′ begin return -2 end
if @nip=’1111111111′ begin return -2 end
if @nip=’1111111112′ begin return -2 end
if @nip=’9999999999’ begin return -2 end
if @nip like ‘0%’ begin return -2 end
if @nip like ‘_0%’ and @nip not like ‘1%’ begin return -2 end

if (
( ( cast(substring(@nip,1,1) as bigint)*6)
+(cast(substring(@nip,2,1) as bigint)*5)
+(cast(substring(@nip,3,1) as bigint)*7)
+(cast(substring(@nip,4,1) as bigint)*2)
+(cast(substring(@nip,5,1) as bigint)*3)
+(cast(substring(@nip,6,1) as bigint)*4)
+(cast(substring(@nip,7,1) as bigint)*5)
+(cast(substring(@nip,8,1) as bigint)*6)
+(cast(substring(@nip,9,1) as bigint)*7)
) % 11
= right(@nip,1) )
begin
return 1;
end

return 0;

END

Klucz obcy – informacje praktyczne | foreign key – practical

By dokładniej omówić jak działa klucz obcy posłużę się przykładem. Weźmy po uwagę 2 tabele: Klienci i ich adresy.
Załóżmy że zbieramy informację o adresach do korespondencji i adresach zameldowania. Możemy to zrealizować na 2 sposoby. Albo utworzymy tabelę klienci która będzie zawierała odpowiednie kolumny (odpowiedzialne za adres do korespondencji i adres zameldowania) lub też stworzymy 2 tabele, gdzie w jednej będziemy trzymać dane klienta a w drugiej dane adresowe. Drugi przypadek jest o tyle ciekawszy że w momencie jak będziemy chcieli zbierać więcej niż 2 adresy nie musimy przebudowywać aplikacji i bazy danych by dodać taką możliwość. Jednak taka struktura wymaga( a właściwie stwarza możliwość) wykorzystania klucza podstawowego i obcego. Dla zainteresowanych teorią tego zagadnienia jak prawidłowo rozdzielać kolumny pomiędzy tabelami i jak tworzyć relację polecam hasło: 3 postać normalna (3NF), zapraszam także do lektury moich postów o modelu relacyjnym.

Wracając do praktyki, poniższe skrypty są tworzone pod SQL Server, jednak po niewielkich przeróbkach mogą aplikowane w innych bazach danych.


create table klienci
(klient_id int not null primary key identity(1,1),
klient_imie varchar(255),
klient_data datetime default getdate()
)

create table adresy (
adres_id int not null primary key identity(1,1),
adres_ulica varchar(255),
adres_miejscowosc varchar(255),
adres_klient_id int,
adres_typ int
)

insert into klienci (klient_imie) values ('Krzys');
insert into klienci (klient_imie) values ('Adam');
insert into klienci (klient_imie) values ('Andrzej');
insert into klienci (klient_imie) values ('Marek');

insert into adresy (adres_miejscowosc,adres_ulica,adres_typ,adres_klient_id) values ('Poznan','Poznanska',1,1);
insert into adresy (adres_miejscowosc,adres_ulica,adres_typ,adres_klient_id) values ('Warszawa','Warszawska',2,1);
insert into adresy (adres_miejscowosc,adres_ulica,adres_typ,adres_klient_id) values ('Gniezno','Dworcowa',1,2);
insert into adresy (adres_miejscowosc,adres_ulica,adres_typ,adres_klient_id) values ('Krakow','Glowna',1,3);
insert into adresy (adres_miejscowosc,adres_ulica,adres_typ,adres_klient_id) values ('Gdansk','Bursztynowa',1,4)
insert into adresy (adres_miejscowosc,adres_ulica,adres_typ,adres_klient_id) values ('Olsztyn','Polska',1,5)

Uruchomienie powyższych zapytań tworzy nam dwie tabele. Tabela z danymi klientów (celowo uproszczona) oraz prosta tabela z adresami. Jak widać w tabeli adresy jest kolumna wskazująca na klienta (adres_klient_id). Jest to kolumna na której powinniśmy założyć klucz obcy.

Najpierw jednak przyjrzyjmy się danym w tabelach.

Jak widać w tabeli adresów znajduje się jedno wskazanie na id klienta który nie istnieje w tabeli klientów. Do takiej sytuacji łatwo doprowadzić w sytuacji gdy baza nie posiada więzów integralności (czyli właśnie kluczy obcych i podstawowych). Taki efekt możemy uzyskać np w sytuacji gdy ktoś po
prostu usunął klient z klient_id=5.

Możemy spróbować założyć klucz obcy na tabelę adresy by uniknąć takich sytuacji:

alter table adresy
add constraint adresy_klient_fk foreign key (adres_klient_id) references klienci(klient_id)

Składnia polecenia jest dość prosta:
Alter table nazwa_tabeli – wskazanie tabeli na której chcemy założyć klucz.
add constraint – rodzaj operacji.
adresy_klient_fk – nazwa naszego klucza, wybieramy ją sami, proszę pamiętać że nazwa klucza nie może powielać się z innymi obiektami w bazie.
foreign key (adres_klient_id) – typ klucza, constraint – więzu integralności, w nawiasie nazwa kolumny na którą zakładamy klucz.
references klienci(klient_id) – wskazanie na tabelę i kolumnę do której klucz obcy ma się odnosić.
Typ klucza podstawowego i obcego w sql server zawsze muszą być identyczne (uwzględniając długość łańcuchów varchar).

Nie mniej ze względu na nasz błąd w danych dostaniemy komunikat błędu:
Msg 547, Level 16, State 0, Line 1
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint “adresy_klient_fk”. The conflict occurred in database “zajecia”, table “dbo.klienci”, column ‘klient_id’.

Aby poradzić sobie z taką sytuacją mamy dwa wyjścia. Możemy stwierdzić że nie jesteśmy wstanie naprawić bałaganu, ale nie chcemy go też powiększać. Dlatego chcielibyśmy utworzyć klucz który będzie działał na nowo wprowadzone dane:

alter table adresy with nocheck
add constraint adresy_klient_fk foreign key (adres_klient_id) references klienci(klient_id)

w tej wersji zapytania pojawiły się słowa with nocheck, jest to informacja dla bazy danych że ma nie weryfikować już wprowadzonych danych.

Gdy utworzyliśmy taki klucz, polecenie:
insert into adresy (adres_miejscowosc,adres_ulica,adres_typ,adres_klient_id) values ('Olsztyn','Polska',1,6)

zwróci nam:
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint “adresy_klient_fk”. The conflict occurred in database “zajecia”, table “dbo.klienci”, column ‘klient_id’.
The statement has been terminated.

klient_id = 6 nie istnieje w tabeli klienci więc baza nie pozwoli nam na zapis takiej informacji.

Drugi scenariusz jaki możemy przeprowadzić to próba posprzątania bałaganu, tutaj mamy dwie opcje. Możemy usunąć kłopotliwy wiersz, lub też możemy ustawić wartość adres_klient_id na null. Czyli na wartość nieokreśloną. W ten sposób zachowamy adres i zweryfikujemy poprawność pozostałych danych.

By przeprowadzić scenariusz numer 2, usuniemy na chwilę klucz obcy:

alter table adresy
drop constraint adresy_klient_fk

teraz ustawmy wartosc null dla danych które nie spełniają zależności klucza obcego:

update adresy set adres_klient_id = null where adres_klient_id not in (select klient_id from klienci)

Teraz możemy założyć klucz obcy według standardowych ustawień:

alter table adresy
add constraint adresy_klient_fk foreign key (adres_klient_id) references klienci(klient_id)
.

Możemy zauważyć że relacja która właśnie stworzyliśmy jest typu jeden do wiele. W tabeli klienci mamy zawsze wartości unikalne (wymóg klucza podstawowego). W tabeli adresy możemy mieć wiele adresów przypisanych do 1 klienta. Jest to najpopularniejszy typ relacji.

Spróbujmy teraz usunąć klienta z naszej bazy:

delete from klienci where klient_id = 2

Baza zwróci nam komunikat:
Msg 547, Level 16, State 0, Line 1
The DELETE statement conflicted with the REFERENCE constraint “adresy_klient_fk”. The conflict occurred in database “zajecia”, table “dbo.adresy”, column ‘adres_klient_id’.
The statement has been terminated.

W ten sposób już nie osierocimy adresów, jednak czasem mimo wszystko ktoś może chcieć usunąć dane z bazy. Wtedy musimy usuwać dane w kierunku relacji. Czyli zaczynamy od tabel które nie mają na siebie wskazań (ich klucz podstawowy nie jest nigdzie kluczem obcym). W naszym przykładzie taką tabelą jest tabela adresy.

delete from adresy where adres_klient_id = 2
delete from klienci where klient_id = 2

Taki kod wykonany w takiej kolejności zadziała prawidłowo. Warto jednak dodać iż warto go objąć tak zwaną transakcją by nie osierocić klienta z adresu. Możemy bowiem wyobrazić sobie sytuację w które adres udaje nam się usunąć a dane klienta już nie.

begin transaction
delete from adresy where adres_klient_id = 2
delete from klienci where klient_id = 2
commit

Powyższy kod jest najbardziej poprawną wersją.

Istnieje jeszcze możliwość kaskadowego usuwania danych.
Usuńmy na chwilę nasz klucz obcy:

alter table adresy
drop constraint adresy_klient_fk

dodajmy go z powrotem, tym razem z opcją kaskadowego usuwania danych.

alter table adresy
add constraint adresy_klient_fk foreign key (adres_klient_id) references klienci(klient_id) on delete cascade

teraz wykonanie polecenia:
delete from klienci where klient_id=1

Niestanowi żadnego problemu, możemy też zauważyć że automatycznie został usunięty wiersz z tabeli adresy ( w naszym wypadku nawet dwa wiersze).

Możemy też tak skonfigurować nasz klucz by podczas usuwania danych z tabeli klienci, ustawiał wartość null w kolumnie adres_klient_id. Aby tego dokonać w naszym przykładzie usuńmy po raz kolejny nasz klucz:

alter table adresy
drop constraint adresy_klient_fk

Teraz dodajmy klucz z opcją on delete set null:

alter table adresy
add constraint adresy_klient_fk foreign key (adres_klient_id) references klienci(klient_id) on delete set null

Po ustawieniu klucza, wykonujemy delete z tabeli klientów:

delete from klienci where klient_id = 3

Po wykonaniu zapytania, widać że baza ustawiła nam wartość null dla adresu z adres_klient_id=3.

Metodyka porównywania wydajności operacji SQL Insert w wybranych RDBMS

Poniżej polska wersja artykułu opublikowanego w PIMR,
oryginalna wersja dostępna tutaj

Wprowadzenie
Kluczową kwestią nabierającą coraz większego znaczenia, braną pod uwagę na etapie projektowania, wytwarzania i eksploatowania bazodanowych systemów informatycznych jest problem wydajności i skalowalności aplikacji[1]. W procesie wieloetapowego projektowania danych generalnie nasza uwaga skupiona jest na ich poprawnym odwzorowaniu zgodnie z rozpoznanymi wymaganiami i z przyjętym architektonicznym modelem danych. Zagadnienie wydajności samo w sobie jest zagadnieniem złożonym albowiem możemy go rozpatrywać w kontekście różnych operacji dokonywanych na bazie produkcyjnej lub też w kontekście procesów tworzenia na jej podstawie baz analitycznych[1][7][8]. Z tego obszaru, dość szerokiego, nakreślonego powyżej autorów zainteresował wpływ istnienia klucza podstawowego oaz sposób jego generowania na wydajność zapisu danych w RDBMS. W podejściu badawczym przyjęto perspektywę, interesującą użytkowników gotowego produktu, jaki jest RDBMS, w związku z tym świadomie pominięto zagadnienia związane z mechanizmami fizycznego rozmieszczenia danych na dysku.
Metodyka

Próba odpowiedzi na pytanie na ile obecność klucza podstawowego oraz sposób jego tworzenia wpływa na zapis danych w relacyjnych wymagało opracowania metodyki i wytworzenia niezbędnych narzędzi programistycznych.
Na etapie początkowym przyjęto szereg założeń, których zachowanie powinno eliminować wpływ czynników zakłócających pomiar. Między innymi zdecydowano się, iż aplikacja powinna działać całkowicie po stronie serwera bazodanowego. Tym samym wyeliminowano by wpływ interfejsów programistycznych dostępu do danych typu ADO, ADO.NET itp. na czas zapisu rekordów. Konsekwencją tej decyzji było zwrócenie naszej uwagi tylko na te RDBMS, których wewnętrzny język jest językiem proceduralnym.
Wytworzona procedura, względnie procedury stanowiące podstawowe narzędzie badawcze, powinny tworzyć strukturę tabeli, generować rekordy, a następnie je zapisywać. Równocześnie należałoby rejestrować czas rozpoczęcia i zakończenia zapisu danych z jednoczesnym odnotowywaniem liczby rekordów osadzonych w bazie. Powyższa druga porcja informacji, potrzebna do analiz, gromadzona byłaby w oddzielnej tabeli.
Skoro podstawowym celem autorów było uzyskanie odpowiedzi na pytanie na ile obecność klucza głównego i sposób jego generowania ma wpływ na szybkość zapisu zdecydowano się na utworzenie szeregu niezależnych procedur, osobno dla każdego przewidywanego wariantu. Wspólnym elementem byłby zapis wyników eksperymentu do tej samej tabeli. Przyjęto następujące przypadki, które wiązały się rodzajem informacjami zapisywanych w bazie:
• dane bez klucza podstawowego,
• dane z kluczem głównym, którego wartości generowane są przez RDBMS,
• dane z kluczem głównym, gdzie wartości są generowane na podstawie algorytmu użytkownika.
Procedury to realizujące powinny być zaimplementowane w co najmniej w dwóch RDBMS. Podjęto decyzję, iż będzie to SQL Server 2008, Oracle XE i MySQL. Podstawą wspomnianej decyzji była popularność wykorzystania tych narzędzi informatycznych. I tak dla przykładu na polskim rynku darmowa wersja MSSQL jest wykorzystywana między innymi w znanych programach: Płatnik, MicroSubiekt. Z kolei jego większy brat (płatny) stosowany jest między innymi w jednym z najpopularniejszych programów typu ERP na naszym rynku tj. CDN XL. Baza Mysql skolei dominuje na rynku aplikacji interneotwych, większość skryptów obsługujących fora to tandem Apache, PHP wraz z MySQL. Oprogramowanie bazodanowe dostarczane przez Oracle ma jedną z najlepszych opinii w środowisku IT, dlatego też darmowa wersja (XE) została poddana analogicznym testom jak MSSQL i MySQL.
W prezentowanej metodyce przyjęto jeszcze jedno założenie, z którym w praktyce bazodanowej bardzo często mamy do czynienia, polegające na tym, iż klucz podstawowy jest jedno elementowy, przyjmujący wartości typu int.
Mimo, że zaproponowana metodyka całościowo nie pokrywa się z operacjami przebiegającymi w rzeczywistości, to zdaniem autorów jest dobrym narzędziem do realizacji badań porównawczych. Świadomie zrezygnowano w rozważaniach i badaniach z specjalistycznych narzędzi służących do ładowania danych (typu ETL) wykorzystywanych w tworzeniu hurtowni danych, skupiając się głównie na rozwiązaniach bazodanowych OLTP.

Narzędzie badawcze – procedury

Sygnalizowane powyżej procedury, stanowiące narzędzie badawcze, wytworzono wpierw w języku T-SQL, który jest językiem wbudowanym w SQL Server 2008R2. Pozwala on nie tylko na manipulowanie danymi (składowe języka DML – Data modyfication Language) ale również na definiowanie struktury (składowe języka DDL – Data definition Language). W zaprezentowanej poniżej procedurze, dotyczące przypadku zapisu danych w tabeli bez zdefiniowanego klucza podstawowego, wykorzystano obie wspomniane możliwości języka T-SQL. Podjęto również decyzję o zapisie wyników cząstkowych eksperymentu w dodatkowej tabeli. Do utworzenia agregatów, które tworzą wyniki cząstkowe, odnotowane w tabeli wykorzystano pytanie grupujące wraz z funkcją DateDiff. Grupę tworzą rekordy, które zostały zapisane do bazy w tym samym przedziale czasowym wynoszącym jedna sekunda. Ten sposób grupowania jest możliwy, albowiem jedno z pól tabeli podstawowej (simple_insert_table) zawiera datę i czas systemowy zwróconą przez funkcję GetDate.

CREATE PROCEDURE [dbo].[simple_insert]
@numerow int=1000;
@numtries int= 100;
AS
BEGIN
SET NOCOUNT ON;
declare @testb datetime;
declare @tests datetime;

create table simple_insert_table (
a int, b varchar(10), c datetime );

declare @numtries1 int=@numtries;
declare @numerow1 int=@numerow;

begin transaction;
set @testb = GETDATE();
while @numtries >0
begin
set @numerow = @numerow1;
while @numerow >0
begin
insert into simple_insert_table values (1,’1234567890’,GETDATE());
set @numerow = @numerow -1;
end;
set @numtries = @numtries -1;
end;
set @tests=GETDATE();

insert into test_results (test_name,start_d,stop_d,param1,param2)
values (‘simple insert test’,@testb,@tests,@numerow1,@numtries1);

set @numerow = @@IDENTITY;
insert into test_subresult (test_id, ins_num, time_agr)
select @numerow,count (DATEDIFF(S, ‘19700101’, c)) e, DATEDIFF(S, ‘19700101’, c) from
simple_insert_table
group by DATEDIFF(S, ‘19700101’, c);
insert into test_results (test_name,param1,param2,param3,param4,param5)
select ‘statistic for simple insert: ‘+ cast(@numerow as varchar),exec_per,max_ins,avg_ins,min_ins,stddev_ins from dbo.simple_insert_stat;
drop table simple_insert_table;
commit transaction;
END

Niewielkie zmiany w prezentowanym kodzie pozwoliły na zbudowanie nowych procedur, które stanowiły narzędzie testujące dla dwóch kolejnych przypadków. W pierwszym wariancie klucz był generowany przez algorytm zawarty w procedurze. Zmienna wykorzystywana do przekazywania wartości generowanego klucza do zapytania, stanowiła równocześnie składową warunku, decydującego o liczbie wykonanych pętli. W drugim przypadku do generowania klucza wykorzystano odpowiednie narzędzia serwera bazodanowego. W przypadku SQL Server 2008R2 wykorzystano mechanizm identity, natomiast jego odpowiednikiem na poziomie MySQL był autoincrement. Z kolei w RDBMS Oracle wykorzystano mechanizm sekwencji. Ingerencja w wspomniane narzędzia dotyczyła tylko ustawień początkowych, obejmujących wartość startową oraz skok, które we wszystkich przypadkach były identyczne. Celem zapewnienia możliwości dokonania uogólnień omawiany algorytm został zaimplementowany w języku PL/SQL oraz w proceduralnym rozszerzeniu języka SQL dla bazy MySQL 5.5.8, co pozwoliło na przeprowadzenie testów z wykorzystaniem innych RDBMS.
W trakcie podjętych przez autorów badań pojawiła się, co prawda nowa wersja SQL Server 2011 wyposażona tym razem w dwa mechanizmy, pozwalające na automatyczne tworzenie klucza podstawowego, lecz nie jest to wersja ostateczna, w związku z powyższym nie stała się ona przedmiotem eksperymentów. Tym dodatkowym narzędziem umożliwiającym automatyczne generowanie klucza na poziomie wspomnianego RDBMS jest mechanizm sekwencji [8].
Badania i wyniki

Badania przeprowadzono w wirtualnym środowisku, opartym o system operacyjny Windows XP Home Edition wraz z najnowszymi aktualizacjami. Instalacja miała charakter standardowy podobnie, jak późniejsza instalacja RDBMS (zaaprobowano wszystkie ustawienia kreatora). Po zakończeniu testów danego RDBMS następowała reinstalacja środowiska badawczego. Każdy z testów został także wykonany na maszynie serwerowej podpiętej do macierzy w środowisku Windows Server 2008 ( z wyłączeniem testów bazy Oracle). Procedury dla bazy Oracle zostały przetestowane na serwerze z systemem operacyjnym Ubuntu Server 11 także podpiętym do wydajnej macierzy. Wyniki z środowisk serwerowych były analogiczne do maszyny badawczej (relacje wyników testów dla danego RDBMS).
Powszechnie uważa się iż wyłączenie ograniczeń (np. Klucza podstawowego) podczas wstawiania dużej ilości danych pozwala zwiększyć wydajność zapisu. Opisywany eksperyment pozwala ocenić czy wskazana operacja jest wstanie przynieść realne zyski.
W celu przeprowadzenia testów potrzebne było utworzenie w każdym badanym RDBMS pustej bazy danych, również zgodnie z ustawieniami sugerowanymi przez kreatora. Dało to podstawy do osadzenia w niej procedur testujących z jednoczesnym utworzeniem struktury relacyjnej do gromadzenia wyników badań.
Test przeprowadzono dla każdego z RDBMS dla wyszczególnianych poniżej wariantów, podczas których wprowadzono do bazy 106 rekordów:
• wstawiano dane bez klucza głównego,
• wstawiano dane tworzące klucz główny z poziomu procedury
• wstawiono dane zawierające klucz główny utworzony przy użyciu mechanizmów RDBMS (autoincrement, identity, sequence).

Efekty przeprowadzonych badań zaprezentowano w formie tabelarycznej tab. 1, 2, 3 i 4. Zawierają one wielkości względne, a punktem ich odniesienia są wyniki uzyskane dla pierwszego wariantu badań. Powyższy sposób postępowania zastosowano do rezultatów uzyskiwanych dla każdego z badanych RDBMS. W przypadku MySQL 5.5.8 badania przeprowadzono dla dwóch różnych silników INNODB oraz MyISAM, dostępnych dla tego RDBMS. Natomiast niecelowym, zdaniem autorów było umieszczenie względnych wyników badań uzyskanych dla przypadku zapisu rekordów pozbawionych klucza podstawowego.

Omówienie wyników

Zaprezentowane wyniki badań zapisu danych zawierających i niezawierających klucz podstawowy niewiele się różnią się między sobą w przypadku SQL Server 2008 R2. Dotyczy to zarówno sytuacji, gdy klucz jest tworzony przez RDBMS, jak i przez algorytm zaszyty w procedurze testującej. Opóźnienie zapisu danych wyposażonych w klucz podstawowy, przy przyjęciu prędkości zapisu 5000 wierszy na sekundę oraz liczbie rekordów 106, wynosi 10 sekund. Ważnym odnotowania jest również fakt, iż w trakcie badań nie zaobserwowano spadku prędkości zapisu wierszy wraz ze wzrostem liczby rekordów zawartych w tabeli. Zbliżoną prawidłowość zauważono w przypadku MySQL 5.5.8 wyposażonego w silnik InnoDB z tym, że okazał się on bardziej wrażliwy na sposób generowania klucza podstawowego. Drugą dość zaskakującą zależnością, wynikającą z otrzymanych wyników dla tego RDBMS jest wzrost względnej prędkości zapisu danych, zawierających klucz podstawowy. Dotyczy to zarówno sytuacji gdy klucz główny jest generowany przez system bazodanowy, jak i procedurę. Równoległym faktem, wymagającym wyjaśnienia są zerowe wartości prędkości minimalnej uzyskiwane w trakcie badań MySQL 5.5.8[5]. Skutkowało to znacznym wzrostem odchylenia standardowego oraz tym, że minimalna prędkość względna przyjmowała wartości nieokreślone.
Tego rodzaju prawidłowości nie stwierdzono przy użyciu wcześniejszego silnika MyISAM MySQL 5.5.8 [4]. Odnotowane w tym przypadku tendencje, będę konsekwencjami pomiarów są zgodne z rezultatami uzyskanymi dla Oracle XE, które potwierdzają dotychczasowe przekonanie, że wprowadzenie klucza spowalnia zapis nowych wierszy do tabeli. Należy jednak przypomnieć o istotnych wadach silnika MyISAM, jakim jest brak możliwość tworzenia transakcji i definiowania więzów integralności referencyjnej [6].
Podsumowanie

Współczesne RDBMS bardzo się różnią pod względem implementacji modelu relacyjnego, co utrudniania formułowanie uogólnień i reguł dotyczących wydajności tych systemów informatycznych. W pewnych wypadkach rozwiązania intuicyjne, czy będące efektem dotychczasowych doświadczeń z RDBMS mogą się okazać nieefektywne przy pracy z nowymi systemami bazodanowymi, dlatego autorzy zalecają testowanie proponowanych rozwiązań zwłaszcza przed rozpoczęciem prac rozwojowych tworzonego oprogramowania (zwłaszcza w przypadku tworzenia struktur OLAP). Przeprowadzone badania wraz z dokonaną analizą wyników, z użyciem trzech różnych RDBMS skłoniły autorów do sformułowania następujących uwag i wniosków:
• Wyposażenie danych w klucz podstawowy ogranicza z reguły wydajność zapisu rekordów w każdym badanym RDBMS z wyłączaniem MySQL 5.5 zawierającego silnik InnoDB. Spadek tej wydajności jest zróżnicowany lecz z perspektywy SQL Server 2008R2 jest on mało znaczący.
• Mechanizm generowania klucza podstawowego wbudowany w RDBMS z perspektywy wydajności zapisu nowych wierszy jest generalnie rozwiązaniem lepszym od własnych rozwiązań programistycznych zaszytych w procedurach.
• Wskazanym wydaje się podjęcie dalszych wysiłków poznawczych, zmierzających do wyjaśnienia nietypowego zachowania się MySQL 5.5.8 z silnikiem InnoDB z perspektywy prędkości zapisu danych pozbawianych i wyposażonych w klucz podstawowy.
• Uzyskane całkowite czasy zapisy danych w analizowanych wariantach i w różnych RDBMS i przy zaproponowanej metodyce badań wskazują, że z perspektywy aplikacji OLTP korzyści wydajnościowe, wynikające z niestosowania jednoelementowego klucza podstawowego są mało znaczące.
Bibliografia

[1] Beynon-Davies – Database Systems 2003
[2] Joe Celko – SQL for Smarties, Advanced SQL Programming, 3 Edition 2005
[3] MySQL Reference Manual – http://dev.mysql.com/doc/refman/5.5/en/mysql-nutshell.html 2011
[4] MySQL MyISAM Storage Engine Manual – http://dev.mysql.com/doc/refman/5.0/en/myisam-storage-engine.html
[5] MySQL InnoDB – http://dev.mysql.com/doc/refman/5.5/en/innodb-storage-engine.html
[6] MySQL refman – http://dev.mysql.com/doc/refman/5.0/en/ansi-diff-foreign-keys.html
[7] Paulraj Ponniah – Data Warehousing Fundamentals for IT Professionals 2010
[8] SQL Server perfomance – http://www.sql-server-performance.com/articles/dev/sequence_sql_server_2011_p1.aspx 2011
[9] Chris Todman – Designing a data warehouse: supporting customer relationship management 2003

Struktura gwiazdy – Struktura płatka śniegu

Gdy mówi się o hurtowniach danych, najczęściej całość sprowadza się do odpowiedniej struktury. Są to stosunkowo proste układy tabel zwane – gwiazdą lub płatkiem śniegu. Ułożenie danych w ten sposób pozwala na znaczące ułatwienie tworzenia wszelkiej maści raportów. To wszystko bez wdrażania drogich systemów BI, które de facto działają na tego typu strukturach. W prostych przypadkach wystarczy nam struktura gwiazdy, co oznacza jedną tabelę faktów połączoną z wieloma wymiarami. We faktach odnotujemy zaistnienie jakiegoś zdarzenia np. sprzedaż (słoika ogórków). Aby to odnotowanie było sensowne musimy to powiązać z jakimiś wymiarami oraz przypisać do tego wydarzenia jakieś wielkości(ilość,cena sprzedaży,cena zakupu) itp. Powiązania w tabeli faktów to klucze obce do innych tabel nazywanych wymiarami. We wymiarach trzymamy pogrupowane tematycznie dane. Oczywiście tworzenie hurtowni danych to znacznie więcej teorii, jednakże warto rozpocząć od prostych modeli. Poniżej przedstawiam procedurę w T-SQL szykującą gotowy do przekształcenia w kostkę analityczną model gwiazdy.

Studenci przedmiotu hurtownie danych który prowadzę dostali ode mnie dane źródłowe, które pozwalały na wygenerowanie tabel opisanych poniżej. Jeśli drogi czytelniku nie jesteś moim studentem, nie zrażaj się całość jest opisana w taki sposób abyś łatwo mógł to przystosować do swoich potrzeb.

Najpierw zamieszczę kompletny kod t-sql później go konkretnie opiszę.

ALTER PROCEDURE [dbo].[data_loader]
AS
BEGIN
SET NOCOUNT ON;
begin try
drop table f_wypozyczenia
end try
begin catch
print 'cos'
end catch

begin try
drop table w_adresy
end try

begin catch
print 'cos'
end catch

--- zaladowanie adresow do tabeli wymiarow
select *
into w_adresy
from (
select kod_p,
(select top 1 miasto from etl_badresy b1 where b.kod_p = b1.kod_p ) miasto,
(select top 1 dzielnica from etl_badresy b1 where b.kod_p = b1.kod_p ) dzielnica,
(select top 1ulica from etl_badresy b1 where b.kod_p = b1.kod_p ) ulica

from etl_badresy b
) e
group by kod_p,miasto,dzielnica,ulica

begin try
drop table w_klienci
end try

begin catch
print 'cos'
end catch
--- zaladowanie klientow

select * ,case when len(pesel)=11 then dbo.get_wiek_from_pesel(pesel) else 0 end wiek,
dbo.pesel_plec(pesel) plec
into w_klienci
from etl_bosob

begin try
drop table w_ksiazki
end try

begin catch
print 'cos'
end catch

select * into w_ksiazki from etl_ksiazki

-- utworzenie indeksow
begin try
create index wypo_ean on etl_wypozyczenia(ean)
create index wypo_osob on etl_wypozyczenia(osob_id)
create index osob_id on etl_bosob (id)
end try
begin catch
print 'juz mam indeksy na etl'
end catch

-- stworzenie tabeli faktow
select osob_id,b.ean,o.kodp,cena,liczba_stron,data_od,data_do,datediff(day,data_od,data_do) ile_dni,
case when year(data_od)<2011 then 0.01 else 0.02 end zysk_per_strona, case when year(data_od)<2011 then 0.01 else 0.02 end * liczba_stron as zysk_wypozyczenie into f_wypozyczenia from etl_wypozyczenia w inner join etl_ksiazki b on b.ean = w.ean inner join etl_bosob o on o.id = osob_id --utworzenie wiezow integralnosci pk ALTER TABLE w_adresy ALTER COLUMN kod_p varchar(255) not null ALTER TABLE w_adresy ADD CONSTRAINT pk_kodp PRIMARY KEY (kod_p) ALTER TABLE w_klienci ALTER COLUMN id int not null ALTER TABLE w_klienci ADD CONSTRAINT pk_klientID PRIMARY KEY (id) -- zapewnienie unikalnosci ean w bazie ksiazek: delete from w_ksiazki where ean in ( select ean from w_ksiazki group by ean having count(*) >1
) and id not in (
select max(id ) from w_ksiazki where ean in (
select ean from w_ksiazki
group by ean
having count(*) >1
)
)

ALTER TABLE w_ksiazki
ALTER COLUMN ean varchar(255) not null;

ALTER TABLE w_ksiazki
ADD CONSTRAINT pk_eanasid PRIMARY KEY (ean)

-- utworzenie fk

alter table f_wypozyczenia
alter column osob_id int

alter table f_wypozyczenia
alter column kodp varchar(255)

alter table f_wypozyczenia
add constraint fk_ean foreign key (ean) references w_ksiazki(ean)

alter table f_wypozyczenia
add constraint fk_osobid foreign key (osob_id) references w_klienci(id)

-- sprawdzenie wiezow integralnosci pomiedzy wypozyczeniami a adresami

--select count(distinct kodp) from f_wypozyczenia where kodp not in (select kod_p from w_adresy)
update f_wypozyczenia set kodp='66-666' where kodp not in (select kod_p from w_adresy)

insert into w_adresy (kod_p,miasto,dzielnica,ulica) values ('66-666','NIEZNANE','NIEZNANA','NIEZNANA');

alter table f_wypozyczenia
add constraint fk_kodpi foreign key (kodp) references w_adresy(kod_p);
end;

Całość to procedura która usuwa/tworzy tabele w odpowiedniej kolejności i kopiuje dane z jakiś innych struktur. Jest to model pokazowy, który nie spełnia wielu założeń komercyjnych modeli. Założeniem było maksymalne uproszczenie.

Każda hurtownia ma swoje tło, najczęściej biznesowe. Wytworzony model ma wspomagać pracę biblioteki. Biblioteka posiada listę książek(tytuł, autor, ean, cena, ilość stron), oraz listę klientów (nazwisko,pesel, kod_pocztowy). Biblioteka zakupiła słownik kodów pocztowych(kod pocztowy, ulica, dzielnica, miasto).
Dodatkowo biblioteka posiada system w którym są odnotowane: czas wypożyczenia, czas oddania, ean książki, id osoby wypożyczającej. Umówiliśmy się że za każdą przeczytaną stronę biblioteka dostaje dotację od Państwa w wysokości 1grosza(w 2010) lub 2 grosze(od 2011).

W modelach komercyjnych stosuje się różne podejścia w stosunku do zmienności danych (mniej lub bardziej restrykcyjnie – prowadzące do niezmienności, bądź niskiej zmienności danych w czasie), w przedstawionym modelu o to nie dbam. dlatego też usuwam wszystkie tabele do których ładuje dane.

begin try
drop table f_wypozyczenia
end try

begin catch
print 'cos'
end catch

Zaczynam od tabeli faktów, jak pisałem posiada ona same klucze obce i miary. Nie mogę usunąć tabel z kluczami podstawowymi do momentu usunięcia więzów integralności (drop table te więzy usuwa).

Powyższa składnia próbuje usunąć tabele, w przypadku gdy coś pójdzie nie tak procedura wydrukuje komunikat ‘cos’.

begin try
drop table w_adresy
end try

begin catch
print 'cos'
end catch

--- zaladowanie adresow do tabeli wymiarow
select *
into w_adresy
from (
select kod_p,
(select top 1 miasto from etl_badresy b1 where b.kod_p = b1.kod_p ) miasto,
(select top 1 dzielnica from etl_badresy b1 where b.kod_p = b1.kod_p ) dzielnica,
(select top 1 ulica from etl_badresy b1 where b.kod_p = b1.kod_p ) ulica

from etl_badresy b
) e
group by kod_p,miasto,dzielnica,ulica

Usunięcie wymiaru z słownikiem adresu, powyżej przykład (może niezbyt optymalny) ale pewny zapewnienia unikalności kodu pocztowego. Podczas tworzenia hurtowni danych nie raz spotkamy się z potrzebą stworzenia klucza unikalnego. Wtedy trzeba pójść na kompromis – zaprezentowałem jedno z podejść pt. świadoma utrata danych mało znaczących biznesowo.

W poprzednich moich wpisach na blogu opisałem metody ekstrakcji danych z numeru pesel, poniżej z nich skorzystałem:
begin try
drop table w_klienci
end try

begin catch
print 'cos'
end catch
--- zaladowanie klientow

select * ,case when len(pesel)=11 then dbo.get_wiek_from_pesel(pesel) else 0 end wiek,
dbo.pesel_plec(pesel) plec
into w_klienci
from etl_bosob

funkcja dbo.get_wiek_from_pesel(pesel) oblicza wiek na podstawie numeru pesel, a dbo.pesel_plec(pesel)
wyznacza płeć.

begin try
drop table w_ksiazki
end try

begin catch
print 'cos'
end catch

select * into w_ksiazki from etl_ksiazki

Niektóre wymiary uda się przekopiować żywcem…

Tabele faktów często są największe, dlatego też przed rozpoczęciem tworzenia warto przemyśleć kwestię założenia indeksów:

-- utworzenie indeksow
begin try
create index wypo_ean on etl_wypozyczenia(ean)
create index wypo_osob on etl_wypozyczenia(osob_id)
create index osob_id on etl_bosob (id)
end try
begin catch
print 'juz mam indeksy na etl'
end catch

W moim przypadku przed stworzeniem tabeli faktów musiałem ją połączyć z innymi tabelami by wyznaczyć sensowne klucze obce:


select osob_id,b.ean,o.kodp,cena,liczba_stron,data_od,data_do,datediff(day,data_od,data_do) ile_dni,
case when year(data_od)<2011 then 0.01 else 0.02 end zysk_per_strona, case when year(data_od)<2011 then 0.01 else 0.02 end * liczba_stron as zysk_wypozyczenie into f_wypozyczenia from etl_wypozyczenia w inner join etl_ksiazki b on b.ean = w.ean inner join etl_bosob o on o.id = osob_id

gdy dane są już w bazie zakładamy więzy integralności:


--utworzenie wiezow integralnosci pk

ALTER TABLE w_adresy
ALTER COLUMN kod_p varchar(255) not null

ALTER TABLE w_adresy
ADD CONSTRAINT pk_kodp PRIMARY KEY (kod_p)

ALTER TABLE w_klienci
ALTER COLUMN id int not null

ALTER TABLE w_klienci
ADD CONSTRAINT pk_klientID PRIMARY KEY (id)

Może się okazać że jakość danych nie pozwoli nam na takie zabiegi (nulle, powtarzające się rekordy wynikające z jakiś błędów). Poniżej obsługa błędu powtarzającego się ean książki:

-- zapewnienie unikalnosci ean w bazie ksiazek:
delete from w_ksiazki where ean in (
select ean from w_ksiazki
group by ean
having count(*) >1
) and id not in (
select max(id ) from w_ksiazki where ean in (
select ean from w_ksiazki
group by ean
having count(*) >1
)
)

Powyższe w przypadku wykrycia powielonego ean zostawi najnowszą wersję rekordu.

Jak mamy zapewnione utworzenie klucza podstawowego, możemy dokończyć kwestię integralności:

ALTER TABLE w_ksiazki
ALTER COLUMN ean varchar(255) not null;

ALTER TABLE w_ksiazki
ADD CONSTRAINT pk_eanasid PRIMARY KEY (ean)

-- utworzenie fk

alter table f_wypozyczenia
alter column osob_id int

alter table f_wypozyczenia
alter column kodp varchar(255)

alter table f_wypozyczenia
add constraint fk_ean foreign key (ean) references w_ksiazki(ean)

alter table f_wypozyczenia
add constraint fk_osobid foreign key (osob_id) references w_klienci(id)

może się okazać że udało się utworzyć PK, ale nie można stworzyć FK ponieważ, występują wartośći których nie ma w tabeli wymiarów. Wtedy takie wartości należy oznaczyć w jakiś sposób:

update f_wypozyczenia set kodp='66-666' where kodp not in (select kod_p from w_adresy)

insert into w_adresy (kod_p,miasto,dzielnica,ulica) values ('66-666','NIEZNANE','NIEZNANA','NIEZNANA');

Na koniec dodanie ostatniego klucza:
alter table f_wypozyczenia
add constraint fk_kodpi foreign key (kodp) references w_adresy(kod_p);

I mamy całość struktury gwiazdy, na tej podstawie z łatwością utworzy się kostkę analityczną.