MariaDB - Widoki

Widoki MariaDB - Wstęp

Podstawowym celem tego tutorialu jest omówienie podstaw widoków w MariaDB. Zobaczymy czym są widoki i do czego mogą się one nam przydać. Powiem wstępnie, iż widoki są tworzone na podstawie polecenia SELECT. Nazwy utworzonych widoków widnieją w bazie danych analogicznie do nazw istniejących tam tabel, ponieważ tabele i widoki dzielą się tą samą przestrzenią nazw. Po ich utworzeniu widoki mogą być modyfikowane lub usuwane, podobnie jak tabele.

MariaDB

Praca z widokami wymaga gruntownej wiedzy z zakresu formułowania zapytań MySQL, a także z zakresu struktury tabel. Zobaczymy, że widoki MariaDB są potężnym i bardzo przydatnym narzędziem.

Dla lepszego zrozumienia zagadnienia widoków przygotowałem przykładowe tabele i wprowadziłem do nich wiersze danych.

Oto moje przykładowe tabele, zawierające tylko potrzebne dla niniejszego tutorialu kolumny, symulujące fragment bazy danych, powiedzmy jakiegoś rodzaju sklepu:

MariaDB [tests]> show tables; +-----------------+ | Tables_in_tests | +-----------------+ | cennik | | kontrahenci | | zakupy | +-----------------+ 4 rows in set (0.00 sec)

Tabela `cennik` jest cennikiem produktów. Zakładając, dla naszego przykładu, że ceny produktów są aktualizowane codziennie i w danym dniu dla każdego produktu może być wyłącznie jedna cena, utworzyłem odpowiedni klucz UNIQUE:

CREATE TABLE `cennik` ( `produkt` smallint(5) unsigned NOT NULL,> `cena_netto` decimal(6,2) NOT NULL, `data` date NOT NULL, UNIQUE KEY `produkt` (`produkt`,`data`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8

Wprowadzamy przykładowe dane do tabeli `cennik`:

MariaDB [tests]> insert into cennik values(1,10.15,"2016-03-03"),(1,10.16,"2016-03-04"),(2,10.15,"2016-03-03"),(2,11.16,"2016-03-04");

Tabela `kontrahenci`, jak sama nazwa sugeruje, zawiera dane osobowe kontrahentów, czyli klientów dokonujących zakupy w sklepie:

CREATE TABLE `kontrahenci` ( `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT, `imie` varchar(15) DEFAULT NULL, `nazwisko` varchar(20) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8

Wprowadzamy przykładowe dane do tabeli `kontrahenci`:

MariaDB [tests]> insert into kontrahenci (imie, nazwisko) values("Jan","Kowalski"),("Janusz", "Kowal");

Tabela zakupy zawiera informacje o zakupionych produktach i prezentuje się następująco:

CREATE TABLE `zakupy` ( `produkt` smallint(5) unsigned NOT NULL, `kontrahent` smallint(5) unsigned NOT NULL, `data` date NOT NULL, `ilosc` smallint(5) unsigned NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8

A oto przykładowe dane dla powyższej tabeli:

MariaDB [tests]> insert into zakupy values(1,1,"2016-03-03",3), (2,1,"2016-03-03",1),(1,1,"2016-03-04",3),(1,2,"2016-03-04",6);

Nasze tabele zawierają już jakieś informacje o dokonanych w naszym sklepie zakupach ;-) Załóżmy teraz, że chcemy się dowiedzieć, ile w sumie wydali klienci każdego dnia, kupując produkty w naszym sklepie. Aby uzyskać tego typu informacje wykonujemy następujące zapytanie:

MariaDB [tests]> select concat(kontrahenci.imie, ' ',kontrahenci.nazwisko) as klient, sum(cennik.cena_netto * zakupy.ilosc) as razem, zakupy.data from kontrahenci cross join zakupy on (zakupy.kontrahent = kontrahenci.id) left join cennik on (cennik.produkt = zakupy.produkt and cennik.data = zakupy.data) group by kontrahenci.id, zakupy.data; +--------------+-------+------------+ | klient | razem | data | +--------------+-------+------------+ | Jan Kowalski | 40.60 | 2016-03-03 | | Jan Kowalski | 30.48 | 2016-03-04 | | Janusz Kowal | 60.96 | 2016-03-04 | +--------------+-------+------------+ 3 rows in set (0.00 sec)

Zapytanie jest już samo w sobie nieco złożone.

Widoki MariaDB - Podstawowe koszyści

Mają na uwadzę powyżej przedstawione zapytanie i zakładając, iż mamy zamiar generować podobne raporty dość często, a na dodatek chcemy mieć możliwość filtrowania uzyskiwanych w ten sposób informacji, np. na podstawie zakresów dat, wówczas sprawy się nieco bardziej komplikują. Załóżmy również, że powyższe zapytanie ma być częścią kodu php aplikacji web, obsługującej nasz sklep internetowy. W przypadku konieczności modyfikacji struktury naszych tabeli, konieczna może stać się ingerencja w skrypty php zawierające zapytania SQL. Tutaj właśnie przychodzą nam z wielką pomocą widoki, nie tylko dlatego, iż mogą one uprościć nasze zapytania SQL, ale także dlatego, iż mogą one stanowić dodatkową programistyczną warstwę abstrakcyjną pomiędzy aplikacją web a właścimym zapytaniem SQL, co daje programistom dodatkową elastyczność. Jednak widoki mogą nam ułatwić wiele innych zadań. O tym napiszę w dalszej części tego artykułu.

Przed przejściem do dalszej części tego artykułu warto zapoznać się z poleceniem CREATE VIEW Wyjaśniam, że stosowana w naszym przykładzie składnia, zawierająca 'sql security invoker' oznacza iż dostęp do utworzonego widoku ma mieć miejsce z uprawnieniami, jakie posiada użytkownik wywołujący widok. Oznacza to, iż w przykładowym przypadku wywołania widoku przez osobę nie posiadającą uprawnień do tabeli `cennik`, zostanie wygenerowany błąd.

Zobaczmy teraz jak działają widoki w praktyce! Pamiętajmy o tym, iż do tworzenia widoków musimy posiadać uprawnienia CREATE VIEW do tabeli, które mają być objęte widokiem. Aby rozpocząć utwórzmy widok na podstawie naszego poprzedniego zapytania SQL. Robi się to przykładowo w ten oto sposób:

MariaDB [tests]> create sql security invoker view zakupy_wedlug_daty as select concat(kontrahenci.imie, ' ',kontrahenci.nazwisko) as klient, sum(cennik.cena_netto * zakupy.ilosc) as razem, zakupy.data from kontrahenci cross join zakupy on (zakupy.kontrahent = kontrahenci.id) left join cennik on (cennik.produkt = zakupy.produkt and cennik.data = zakupy.data) group by kontrahenci.id, zakupy.data; Query OK, 0 rows affected (0.03 sec)

Nowy widok jest dostępny w tej samej przestrzeni nazw co tabele:

MariaDB [tests]> show tables; +--------------------+ | Tables_in_tests | +--------------------+ | cennik | | kontrahenci | | zakupy | | zakupy_wedlug_daty | +--------------------+ 5 rows in set (0.00 sec)

Możemy już teraz zacząć korzystać z nowo utworzonego widoku:

MariaDB [tests]> select * from zakupy_wedlug_daty; +--------------+-------+------------+ | klient | razem | data | +--------------+-------+------------+ | Jan Kowalski | 40.60 | 2016-03-03 | | Jan Kowalski | 30.48 | 2016-03-04 | | Janusz Kowal | 60.96 | 2016-03-04 | +--------------+-------+------------+ 3 rows in set (0.00 sec)

Jak widać, wyniki powyższego zapytania są analogiczne do wyników naszego poprzedniego, bardziej złożonego zapytania, ponieważ widok został utworzony właśnie na jego podstawie, lecz nasze zapytanie jest teraz o wiele prostsze niż poprzednie, co daje większą swobodę i przejrzystość! W takiej sytuacji, możemy w prosty sposób dodawać kolejne warunki do naszego zapytania. Na przykład:

MariaDB [tests]> select * from zakupy_wedlug_daty where data between "2016-03-02" and "2016-03-03" and klient like "%Kowalski"; +--------------+-------+------------+ | klient | razem | data | +--------------+-------+------------+ | Jan Kowalski | 40.60 | 2016-03-03 | +--------------+-------+------------+ 1 row in set (0.00 sec)

Jak wspomniałem wcześniej, dodatkowym atutem widoków, jest ich programistyczna warstwa abstrakcyjna w przypadku aplikacji zawierających zapytania SQL. Jeśli w naszych aplikacjach stosujemy widoki, modyfikacje struktury tabeli nie niosą za sobą konieczności dopasowania zapytań SQL obecnych w aplikacji! Jedyne co może być w takim przypadku konieczne, będzie dopasowanie widoku, a można to przykładowo zrobić za pomocą polecenia ALTER VIEW.
Aby sprawdzić, w jaki sposób został utworzony dany widok, można wydać polecenie SHOW CREATE VIEW, przykładowo:

MariaDB [tests]> show create view zakupy_wedlug_daty;

Ciekawostką, o której warto tutaj wspomnieć, jest to, iż widoki mogą być tworzone nie tylko na podstawie zapytań obejmujących tabele, lecz zapytania te mogą obejmować także widoki!
Widoki usuwamy za pomocą polecenia DROP VIEW.

Widoki - Dodatkowe korzyści

Widoki w MariaDB to cudowne narzędzie! Oprócz omówionych wcześniej korzyści, stosowanie widoków daje nam możliwość ograniczenia dostępności poufnych informacji. Załóżmy, że tabela kontrahenci zawiera o wiele więcej kolumn niż imię i nazwisko, a są to przykładowo kolumny takie jak numer pesel, czy numery kart kredytowych. Załóżmy również, że użytkownik widoku ma mieć możliwość formułowania zapytań obejmujących wyłącznie wybrane kolumny tej tabeli. Staje się jasne, jak przydatne są tutaj widoki! Możemy w ten sposób tworzyć różne widoki, oparte na tych samych tabelach, lecz umożliwiające dostęp wyłacznie do wybranych kolumn tych tabel, w zależności od rodzaju dostępu do informacji, jaki mamy zamiar przydzielić użytkownikom tych widoków.

Dodatkowym atutem stosowania widoków, w przypadku baz danych złożonych z wielu tabel, w których występują bardzo złożone relacje danych, jest możliwość tworzenia idealnie zoptymalizowanych zapytań zawierających różnego rodzaju skomplikowane łączenia i udostępniania ich mniej doświadczonym użytkownikom, w celu ograniczenia obciążeń serwera wynikających z nieodpowiednio sformułowanych zapytań.

Mam nadzieję, iż niniejszy tutorial okaże się pomocny. W przypadku pytań proszę o komentarze :-)