Strona używa cookies (ciasteczek). Dowiedz się więcej o celu ich używania i zmianach ustawień. Korzystając ze strony wyrażasz zgodę na używanie cookies, zgodnie z aktualnymi ustawieniami przeglądarki.    X

Baza danych jest jak cebula...

... ma warstwy!

Relacje, czyli jak skomplikować, ale zoptymalizować

Dla wszystkich, którzy w jednym palcu mają bazy danych - to nie jest wpis dla Was. Pozostałych czytelników zachęcam do wytrwania do końca.

Relacyjne bazy danych, tak modne wymaganie nakładane przez headhunterów pracowników IT to po prostu baza danych, w której dane przechowywane są w relacjach. Idem per idem. Może na przykładzie. Mamy bazę danych książek. W zasadzie, możemy w jednej tabelce umieścić informację o numerze katalogowym, ilości sztuk, autorze, roku wydania, itd, itp. Wydajniej (w tym przypadku - szybciej i mniej zasobożernie) będzie utworzyć kilka tabel oraz umieścić w nich relację. I tak tworzymy sobie tabelkę z autorami (w końcu każdy autor może napisać więcej niż jedną książkę), tytułami, ilością w magazynie itd. Wszystko spinają dla nas klamry relacji, które dbają za nas o wszystko. Rzućmy odrobiną przykładu...

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0; SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0; SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES'; CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ; USE `mydb` ; -- ----------------------------------------------------- -- Table `mydb`.`autorzy` -- ----------------------------------------------------- DROP TABLE IF EXISTS `mydb`.`autorzy` ; CREATE TABLE IF NOT EXISTS `mydb`.`autorzy` ( `id` INT NOT NULL AUTO_INCREMENT , `autor` VARCHAR(45) NULL , PRIMARY KEY (`id`) , UNIQUE INDEX `autor_UNIQUE` (`autor` ASC) ) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `mydb`.`ksiazki` -- ----------------------------------------------------- DROP TABLE IF EXISTS `mydb`.`ksiazki` ; CREATE TABLE IF NOT EXISTS `mydb`.`ksiazki` ( `id` INT NOT NULL AUTO_INCREMENT , `tytuł` VARCHAR(45) NULL , `autorzy_id` INT NOT NULL , PRIMARY KEY (`id`, `autorzy_id`) , INDEX `fk_ksiazki_autorzy_idx` (`autorzy_id` ASC) , CONSTRAINT `fk_ksiazki_autorzy` FOREIGN KEY (`autorzy_id` ) REFERENCES `mydb`.`autorzy` (`id` ) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; SET SQL_MODE=@OLD_SQL_MODE; SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

Podany kod utworzy nam bazę mydb (jeśli taka istnieje przejdzie do kolejnego kroku), następnie utworzy tabelę `autorzy`. Tabela jest prosta, zawiera tylko dwie kolumny: id, która jest autoinkrementowana (tzn. przy dodawaniu kolejnej encji, zostanie z automatu dodany nowy id) oraz `autor`, która jest unikatowa i indeksowana. Ciekawiej robi się w przypadku drugiej tabelki. Tabela `ksiazki` posiada dwie oczywiste kolumny (id oraz tytuł) oraz tzw. klucz obcy. Nazywa się on `autorzy_id` i jest referencją do tabelki `autorzy` i jej kolumny `id`. Za deklaracją referencji znajdują się dwa parametry ON DELETE oraz ON UPDATE. Oba parametry przyjmują wartości:

  • NO ACTION - nie podejmuje żadnej akcji
  • RESTRICT - wiersz nie może zostać usunięty lub zaktualizowany
  • SET NULL - usunięcie elementu nadrzędnego powoduje ustawienie wartości NULL na elemencie wskazywanym
  • CASCADE - zmiana elementu nadrzędnego powoduje ustawienie takiego samego stanu na elemencie wskazywanym (czyli na delete zostanie on usunięty, przy update ustawi się jego wartości na taką samą).
  • SET DEFAULT - przy zmianie elementu nadrzędnego element wskazywany ustawi się na wartość domyślną, jeśli taką posiada.

Mamy więc gotowe relacje. Zróbmy dwa szybkie insterty:

insert into `mydb`.`autorzy` (`autor`) values ('Stanislaw Wyspianski'); insert into `mydb`.`ksiazki` (`tytuł`, `autorzy_id`) values ('Wesele', 1);

Jak teraz zrobić do niej select, by dostać dane u książce i jej autorze? Można spróbować tak:

select `autor`, `tytuł` from `autorzy` inner join `ksiazki` on `autorzy`.`id` = `ksiazki`.`autorzy_id`;

Masakra, prawda? Przejdźmy więc do warstwy...

Widoku

Widoki powstały między innymi po to, by ułatwić zapytania do relacyjnych baz danych. Utwórzmy sobie taki widok, by dane z poprzedniego zapytania były wyświetlane:

CREATE VIEW `mydb`.`ksiazki_autorzy` AS select `autor`, `tytuł` from `autorzy` inner join `ksiazki` on `autorzy`.`id` = `ksiazki`.`autorzy_id`

Proste, prawda? Rozpoczynamy deklarację CREATE VIEW, potem tylko powtarzamy wcześniejsze zapytanie. I gotowe. Teraz zapytanie

select * from `ksiazki_autorzy`

Daje nam to co chcieliśmy. Co więcej - możemy insertować do tego widoku. Co prawda nie do każdej z tabel znajdujących się "pod spodem", ale (w tym przypadku) do tabeli `autorzy`. Posiada ona jedną kolumnę, która jest "podlinkowana" z naszym widokiem. Druga (id) jest autoinkrementowana i podanie jej wartości nie jest wymagane.

Procedury

Długo się zastanawiałem, czy w ogóle poruszać ten element możliwości baz danych. Mimo wszystko zdarza mi się używać procedur raz na jakiś czas, więc napiszę krótko i o tej warstwie baz danych.

Jednak uprzedzam - procedury są reliktem, nikt już nie programuje na poważnie proceduralnie i należy się dwa razy zastanowić czy na pewno warto jest wrzucać procedurę.

Na potrzeby naszego przykładu dodamy kolejną tabelę

CREATE TABLE `ile_ksiazek` ( `id` int(11) NOT NULL AUTO_INCREMENT, `count` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `fk_autorzy_id_idx` (`id`), CONSTRAINT `fk_autorzy_id` FOREIGN KEY (`id`) REFERENCES `autorzy` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION )

Po co w ogóle tworzyć taką tabelkę? W zasadzie sam nie wiem... No ewentualnie, gdy chcemy podać klientowi ile książek jednego autora mamy w zasobach, nie musimy nic countować bo wszystko znajduje się statycznie w bazie... Co być może zwiększy wydajność zapytań... itd. W każdym razie mamy takie coś. Chcemy teraz, by w tabeli pojawiała się informacja o ilości książek jednego autora. Wobec tego przy insertowaniu tabeli `ksiazki` powinien nam się zwiększać ilość wpisów... spróbujemy zrobić więc procedurę:

CREATE PROCEDURE `mydb`.`inc_count` (IN g_autor_id int) BEGIN INSERT INTO ile_ksiazek (id, count) VALUES (g_autor_id,1) ON DUPLICATE KEY UPDATE count=count+1; END

Jak widać konstrukcja jest bardzo prosta. Rozpoczynamy od deklaracji CREATE PROCEDURE, następnie podajemy nazwę procedury i deklarujemy argumenty, które przyjmuje. Ostatecznie między BEGIN a END zapisujemy kod naszej procedury. W naszym przypadku - banał. Insert or update na bazie ile_ksiazek. Teraz należy tylko zmusić bazę, by za każdym insertem odpalała tę procedurę. Tworzymy więc triggera (zwanego też wyzwalaczem):

DELIMITER // CREATE TRIGGER increment_count AFTER INSERT ON ksiazki FOR EACH ROW BEGIN call inc_count(NEW.autorzy_id); END;//

Budowa także nie wymaga by zbyt głowić się nad jej konstrukcją. Po deklaracji CREATE TRIGGER następuje jej nazwa, następnie parametr AFTER INSERT, czyli po zainsertowaniu (z sukcesem) do tabeli `ksiazki` zostanie wykonane co poniżej. A poniżej informacja, że dla każdego wiersza należy wykonać call funkcji. I wszystko.

Podsumowanie

No i to już wszystko w tym temacie. Na podstawie tego wpisu, osoby, które dotrwały do końca powinny wiedzieć mniej więcej co to są relacje, dlaczego i kiedy je stosować, czym jest widok i dlaczego taki fajny oraz czym jest procedura, jak ją wywołać i dlaczego nie jest takie to fajne.

Zastanawiam się w którą stronę pójść ze swoim pisaniem na dp.pl. Chcecie więcej takich wpisów, czy lepiej pisać czasem coś trochę oderwanego, jak poprzedni wpis o snach dewelopera?

PS. Stosowanie nazw kolumn oznaczające funkcje natywne języka (jak count) jest błędem. Popełniam go, by na to zwrócić uwagę. Same zapytania powinny wykonać się bezbłędnie, ale czytelność i jasność kodu na tym traci.

PS. Wpis w całości dedykuje osobie, która przebrnęła przez większość moich wpisów, kierując je do publikacji na tzw. głównej. 

programowanie

Komentarze

0 nowych
Cebula REDAKCJA  17 #1 27.08.2012 14:01

Dziękuję za dedykację... Swoją drogą co ja mam wspólnego z bazami danych ;)

djDziadek   16 #2 27.08.2012 14:27

@Cebula - z bazami nie wiem, ale z warstwami ... ? :)

Autor edytował komentarz.
kamil_w   10 #3 27.08.2012 14:36

"Baza danych jest jak cebula..."

czasem się przez nie płacze ;/

gowain   18 #4 27.08.2012 14:41

Czyżby tytuł rodem ze Shreka? :P Co do baz, to ja bym na wstępie to latin1 zmienił na utf-8 :)

tfl   8 #5 27.08.2012 14:48

@gowain

na dodatek jakaś szwedzka odmiana... samo kodowanie w bazach danych to jednak temat na osobny artykuł...

parasite85   7 #6 27.08.2012 15:09

@tfl:
Stworzenie wielu tabel i relacji poprawia wydajność jedynie przy większych bazach (chociaż ostatnio czytałem info o tym, że ogromne bazy odchodzą od relacji właśnie ze względu na relacje) - przy mały wydajniej jest skorzystać z jednej. Inną sprawą jest (i to głównie dlatego się korzysta z relacji), że dane są wpisywane raz i tylko za pomocą relacji połączenia. Zapobiega do duplikacji danych, pomyłkom, ułatwia aktualizację. Następnie unikałbym nazywania kolumny tak jak słowa kluczowe w sql - count używane jest w zapytaniach sql. Kolejnym minusem - nie powinno się trzymać imienia i nazwiska w jednej kolumnie - konkretne pola powinny być "atomowe" - wyobrażam sobie sytuację, że poszukuje jakiejś książki, ale nie pamiętam nazwiska - tylko imię mi gdzieś świta pod kopułą. Przypuszczam, że korzystałeś z MySQL Workbench?? Jakbyś chciał poćwiczyć bazy, to moim zdaniem najwygodniejszym rozwiązaniem jest MS SQL - wersja Express jest bezpłatna, ale ograniczone, wersja Developer jest bardzo tania (mniej niż 300 zł) i możesz tworzyć komercyjne projekty, prezentować je klientowi na tym systemie, ale bazy nie mogą na takiej licencji działać na serwerach produkcyjnych.

Frankfurterium   9 #7 27.08.2012 15:29

Imo wypadałoby zaznaczyć, którego dialektu SQL autor wpisu się trzymał. Także definicja widoku wydaje się być dosyć... enigmatyczna.

tfl   8 #8 27.08.2012 15:54

@parasite85

No przeciez napisalem na koncu, ze count jako nazwa kolumny to zlo...Wszystko co napisales to oczywiscie prawda (choc co do samej atomowosci i przyklad z imionami... pamietam imie, nie pamietam nazwiska, wprowadzam imie z bledem, ale na szczescie mam slownik z imionami i nazwiskami, ktory wskazuje wlasciwe id. Wowczas trzymanie imienia i nazwiska w osobnych kolumnach jest tylko zabiegiem...)
Co do mysql workbench to strzal w 10. Zakladam, ze poznales po create statmencie. Na codzien kontakt mam glownie z postgre i wlasnie mssql, mysql trzymam sobie na lokalnej maszynie glownie do zabawy.

@Frankfurterium

Mysle, ze na tym poziomie roznice w dialekcie nie wystepuja. Choc moge sie mylic, tak naprawde to dawno juz nie pisalem "czystego sql"

StawikPiast   10 #9 27.08.2012 17:34

a kto ci nagadal ze od procedor sie odchodzi?
Toz to czysta nieprawda.
Oczywiscie nie wszedzie sie je stosuje ale w biznesie maja gigantyczne zastosowanie.

To co podales w przykladzie dla procedurty to lepiej zrobic na widoku i jesli sa to jakies straszne obliczenia zabijajace serwer to mozna taki widok zmaterializowac (czyli zalozyc mu indeks).

Do tego watpie zeby osoba ktora nie wie co to sa relacje pojela cos z twojego wpisu. To co tu przedstawiles na szybko ludzie ucza sie pewnie pare dni.

tfl   8 #10 27.08.2012 18:20

@StawikPiast

Nikt mi nie nagadal. Zycie nauczylo. Procedury sa wystarczajace do wspomagania modeli danych przeznaczonych do projektow typu crud. Gdy pojdzie sie "za daleko" w "programowaniu modelu danych" uzyska sie tylko zaciemnienie sposobu dzialania aplikacji. Nie musze chyba Ci tlumaczyc jak ogromnym problemem jest to, gdy rozwijasz aplikacje. Chyba ze mowimy o clr w mssql, ale to juz nie sa procedury.

parasite85   7 #11 27.08.2012 23:41

Oj, teraz przeczytałem jeszcze raz swój komentarz i zobaczyłem ile tam baboli narobiłem....ech...zgroza
"(chociaż ostatnio czytałem info o tym, że ogromne bazy odchodzą od relacji właśnie ze względu na relacje"
oczywiście powinno być:
chociaż ostatnio czytałem info o tym, że ogromne bazy odchodzą od relacji właśnie ze względu na wydajność
to do wykreślenia: "... i tylko za pomocą relacji połączenia"

@tfl
Z Postgre mam mały kontakt, staram się dokształcać z MS SQL, a do pracy licencjackiej którą tworzę korzystam z MySQL. Staram się trzymać III postaci normalnej - może się czepiam, może się tak przyzwyczaiłem, ale co do połączenia imienia i nazwiska nie jestem przekonany.

StawikPiast   10 #12 28.08.2012 10:34

@tfl

To co podałeś jako procedurę raczej nie powinno nią być.
Ale procedury są np bardzo przydatne do generowania raportów, gdzie trzeba wcześniej masę danych przygotować (np do tabel tymczasowych) a potem wrzucić to do Excel-a.

Do tego super odpala się je w jobach, niby można natłuc to wszystko w jobie, ale ladniej jest to zamknąć w procedurze, a job ją tylko wywołuje. Wiec to naprawde tylko kwestia co robisz. Ja osobiscie glownie procedury pisze bo sa mi potrzebne. Oczywiscie wszystko zalezy od tego co musisz wkonywac.

tfl   8 #13 28.08.2012 11:37

@StawikPiast

Tego typu procedury masz na mysli... ja sie jakos uparlem na procedury realizujace "stale" logike biznesowa aplikacji. Jasne, takie procedury sie pisze.