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.