Widoki (InterBase i Firebird)
Alexey Kovyazin, ostatnia aktualizacja 13 kwietnia 2012
Ci, którzy znają język SQL, nie wymagają szczegółowych wyjaśnień tego tematu, ale dla zachowania kolejności prezentacji wprowadzimy krótką definicję widoków.
WIDOK to wirtualna tabela utworzona na podstawie zapytania do zwykłych tabel. Widok jest implementowany jako zapytanie przechowywane na serwerze i wykonywane za każdym razem, gdy odwołujemy się do widoku.
Rozważmy różne warianty użycia widoków. Widoki umożliwiają tworzenie poziomów struktury danych, pozwalając oddzielić implementację przechowywania danych od ich typu. Na przykład możemy utworzyć widok, który wybiera dane z kilku tabel. Jeśli klienci używają tego widoku zamiast bezpośredniego odwoływania się do tabel źródłowych, programista bazy danych będzie mógł zmienić zapytanie leżące u podstaw widoku, aby je zmodyfikować (na przykład zoptymalizować), a klient niczego nie zauważy - dla niego będzie to ten sam widok. Oprócz tego, że izolują implementację przechowywania danych od użytkownika, widoki pozwalają zorganizować dane w wygodniejszy i prostszy sposób. Problem „uproszczenia” struktury danych pojawia się, gdy liczba tabel w bazie danych staje się wystarczająco duża, a zależności między nimi - skomplikowane. Widok pozwala wyeliminować (lub przeciwnie, dodać) część danych niepotrzebnych konkretnemu klientowi bazy danych (lub - potrzebnych).
Ponadto widoki pozwalają prościej zorganizować bezpieczeństwo w bazie danych InterBase. Niektórzy użytkownicy mogą mieć prawa tylko do odczytu / aktualizacji danych w widoku, ale nie mieć praw (a nawet nie mieć pojęcia) o tabelach leżących u podstaw widoku! Więcej szczegółów na temat bezpieczeństwa w InterBase można znaleźć w rozdziale „Bezpieczeństwo w InterBase: użytkownicy, ich funkcje i prawa” (część 4).
Składnia DDL do pracy z widokami
Teraz rozważymy polecenia tworzenia i usuwania widoków zdefiniowane przez DDL (Data Definition Language - podzbiór SQL, patrz słowniczek). Aby utworzyć widok w InterBase, należy użyć zdania o następującej składni:
CREATE VIEW nazwa_widoku [(kolumna_widoku[, kolumna_widoku…])] AS [WITH CHECK OPTION]; Tutaj nazwa_widoku to nazwa widoku, która musi być unikalna w obrębie bazy danych, a następnie grupa nie zawsze obowiązkowych nazw pól zawartych w widoku: [(kolumna_widoku [, kolumna_widoku …])]. Niezbędne jest zdefiniowanie zdania , które wybiera dane zawarte w widoku. Opcjonalny parametr WITH CHECK OPTION omówimy nieco później w części „Widoki modyfikowalne”.
Aby zmienić widok, będziemy musieli go odtworzyć, tj. usunąć i utworzyć ponownie. Podczas usuwania widoku konieczne jest również usunięcie wszystkich obiektów zależnych - triggerów, procedur składowanych i innych widoków. Jest to jedna z głównych niedogodności pracy z widokami: konieczność odtworzenia drzewa obiektów korzystających z widoku (istnieją narzędzia, które ułatwiają to zadanie, na przykład IBAlterView, patrz aplikacja „Narzędzia administratora i projektanta InterBase”). Aby usunąć widok, należy użyć następującego polecenia DDL:
DROP VIEW nazwa_widoku;
Przykłady widoków
Oto przykład prostego widoku:
CREATE VIEW MyView AS SELECT NAME, PRICE_1 FROM Table_example;
W tym przykładzie tworzymy widok na podstawie zapytania do tabeli Table_example, którą rozważaliśmy w rozdziale „Tabele. Klucze podstawowe i generatory”. W tym przypadku widok będzie składał się z dwóch pól - NAME i PRICE_1, które zostaną wybrane z tabeli Table_example bez żadnych warunków, tj. liczba rekordów w widoku MyView będzie równa liczbie rekordów w Table_example. Jednak widoki nie zawsze są tak proste. Mogą być oparte na danych z kilku tabel, a nawet na podstawie innych widoków. Ponadto widoki mogą zawierać dane otrzymane na podstawie różnych wyrażeń - w tym na podstawie funkcji agregujących. Aby dokładniej rozważyć użycie tego widoku, utwórzmy dwie tabele połączone relacją jeden-do-wielu (często taką relację nazywa się master-detail). Oto skrypt DDL do tworzenia tych tabel:
/\* Tabela: WISEMEN */
CREATE TABLE WISEMEN ( ID_WISEMAN INTEGER NOT NULL, WISEMAN_NAME VARCHAR(80));
/\* Definicja klucza podstawowego */
ALTER TABLE WISEMEN ADD CONSTRAINT PK_WISEMEN PRIMARY KEY (ID_WISEMAN);
/\* Tabela: WISEBOOK */
CREATE TABLE WISEBOOK ( ID_BOOK INTEGER NOT NULL, ID_WISEMAN INTEGER, BOOK VARCHAR(80));
/\* Definicja klucza podstawowego */
ALTER TABLE WISEBOOK ADD CONSTRAINT PK_WISEBOOK PRIMARY KEY (ID_BOOK);
/\* Definicja klucza obcego */
ALTER TABLE WISEBOOK ADD CONSTRAINT FK_WISEBOOK FOREIGN KEY (ID_WISEMAN) REFERENCES WISEMEN (ID_WISEMAN);
Utworzyliśmy więc dwie tabele - WISEMEN i WISEBOOK połączone relacją master-detail za pomocą ograniczenia klucza obcego - FOREIGN KEY. Załóżmy, że te tabele będą przechowywać informacje o wielkich chińskich mędrcach i ich dziełach. Teraz możemy utworzyć kilka widoków na podstawie tych tabel. Na przykład utwórzmy widok pokazujący, ile dzieł ma każdy mędrzec:
CREATE VIEW WiseBookCount (WISEMAN, HOW_WISEBOOKS) AS SELECT M.WISEMAN_NAME, COUNT(B.BOOK) FROM WISEMEN M, WISEBOOK B WHERE (M.ID_WISEMAN = B.ID_WISEMAN) GROUP BY M.WISEMAN_NAME
Zwróć uwagę, że przy użyciu jakichkolwiek wyrażeń obliczeniowych, takich jak funkcje agregujące COUNT (), SUM (), MAX () itp., konieczne jest użycie określonych nazw pól widoku, tj. nadanie nazw wszystkim polom zwracanym przez zapytanie. Jak widać z tego przykładu, nazwy te nie muszą koniecznie pokrywać się z nazwami pól zapytania, ale ich liczba musi odpowiadać liczbie pól zwracanych przez zapytanie. Określenie, które pole zwracane przez zapytanie odpowiada któremu polu widoku, odbywa się według numeru seryjnego - pierwsze pole zapytania zostanie odzwierciedlone w pierwszym polu widoku, drugie - w drugim itd.
A co, jeśli chcielibyśmy wiedzieć, który z mędrców napisał najwięcej książek? Spróbujemy dodać wyrażenie sortowania - ORDER BY do zapytania leżącego u podstaw widoku. Jednak ta próba zakończy się niepowodzeniem: użycie sortowania ORDER BY w widokach jest niedozwolone i przy próbie utworzenia widoku z zapytaniem zawierającym ORDER BY pojawi się błąd. Jeśli chcemy posortować wyniki zwracane przez widok, będziemy musieli to zrobić po stronie klienta:
SELECT * FROM WiseBookCount ORDER BY HOW_WISEBOOKS
Wykonanie tego zapytania SQL doprowadzi do pożądanego rezultatu. Oprócz ograniczenia użycia wyrażenia ORDER BY w widokach, nie możemy również używać zestawu danych otrzymanego w wyniku wykonania procedur składowanych jako źródła danych (patrz rozdział „Procedury składowane” poniżej).
Być może warto podać jeszcze jeden przykład ilustrujący zastosowanie widoków. Załóżmy, że musimy wypisać listę mędrców, których imię zaczyna się na literę „K”. W tym przypadku użyjemy widoku z warunkami:
CREATE VIEW WiseMen2 (WISEMAN) AS SELECT M.WISEMAN_NAME FROM WISEMEN M WHERE M.WISEMAN_NAME LIKE ‘K%’
W ten sposób łatwo jest tworzyć widoki, które odgrywają rolę stale aktualizowanych dostawców danych, wybierając je z bazy danych zgodnie z określonymi warunkami.
Widoki modyfikowalne
Wspomnieliśmy powyżej, że istnieje możliwość tworzenia modyfikowalnych widoków danych. Rzeczywiście tak jest - istnieje możliwość nie tylko odczytu danych z widoku, ale także ich modyfikacji!
Istnieją dwa sposoby uczynienia widoku modyfikowalnym. Pierwszy sposób stosuje się, gdy widok jest utworzony na podstawie pojedynczej tabeli (lub innego modyfikowalnego widoku), a wszystkie kolumny danej tabeli muszą dopuszczać obecność NULL. Zatem zapytanie, na którym oparty jest widok, nie może zawierać podzapytań, funkcji agregujących, UDF, procedur składowanych, wyrażeń DISTINCT i HAVING. Jeśli wszystkie te warunki są spełnione, widok automatycznie staje się modyfikowalny, tj. możemy wykonywać na nim zapytania DELETE, INSERT i UPDATE, które będą zmieniać dane w tabeli źródłowej.
Lista warunków jest dość imponująca i znacznie ogranicza zastosowanie takich modyfikowalnych widoków, dlatego są one używane dość rzadko.
Aby utworzyć modyfikowalny widok, który narusza którykolwiek z powyższych warunków, stosuje się mechanizm triggerów. Więcej szczegółów na temat triggerów można znaleźć w rozdziale „Triggery” (część 1). Teraz rozważymy tylko ogólne zasady organizacji zmiany danych w WIDOKU.
Aby zaimplementować aktualizowany widok za pomocą triggerów, należy wykonać następujące czynności. Utworzyć 3 triggery dla danego widoku dla zdarzeń: BEFORE DELETE, BEFORE UPDATE i BEFORE INSERT. Opisać w tych triggerach, co należy zrobić z danymi podczas usuwania, aktualizacji i wstawiania.
Następnie należy użyć danego widoku w zapytaniach modyfikujących - DELETE, INSERT lub UPDATE. Gdy InterBase otrzyma to zapytanie, sprawdzi, czy istnieją odpowiednie triggery dla danego widoku, tj. BEFORE DELETE/INSERT/UPDATE. Jeśli trigger dla wykonywanej akcji istnieje, InterBase wywoła go w celu modyfikacji rzeczywistych danych w tabelach leżących u podstaw widoku (chociaż mogą to być inne dane - nie ma tekstowych ograniczeń dla tych triggerów), a następnie ponownie odczyta wiersz (lub wiersze), na których wykonano modyfikację.
W ten sposób istnieje możliwość realizacji złożonych łańcuchów aktualizacji danych w widokach.
Opcja WITH CHECK OPTION została wspomniana w opisie składni tworzenia widoku. Jeśli ta opcja jest ustawiona podczas tworzenia modyfikowalnego widoku, każdy wiersz danych wstawiony lub zmieniony w tym widoku będzie sprawdzany pod kątem warunku dostania się do widoku. Można to wyjaśnić tak: jeśli nowy rekord wstawiony przez użytkownika lub otrzymany w wyniku aktualizacji istniejącego rekordu nie spełnia warunków zapytania, które jest dostawcą danych dla WIDOKU, wstawienie tego rekordu zostanie anulowane i pojawi się błąd.
Podsumowanie
Pomimo pozornej prostoty tworzenia i używania widoków, zapewniają one ogromne możliwości poprawy organizacji danych w bazie danych i pozwalają tworzyć hierarchię organizacji danych.
Niektórzy projektanci aplikacji bazodanowych bardzo często używają widoków w swojej pracy, inni unikają ich stosowania, motywując to złożonością modyfikacji widoków i tendencją do zachowania schematu bazy danych jako prostego i efektywnego. To od Ciebie zależy, jak będziesz używać widoków w swojej pracy. Najważniejsze jest, aby pamiętać o istnieniu tak potężnego narzędzia, jakim jest widok, i wiedzieć, jak go używać.