Baza danych jest jak cebula...
27.08.2012 | aktual.: 27.08.2012 14:48
... 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.