Pohledy (InterBase a Firebird)
Alexey Kovyazin, poslední aktualizace 13. dubna 2012
Ti, kteří znají jazyk SQL, nepotřebují podrobná vysvětlení tohoto tématu, ale pro zachování posloupnosti výkladu si uvedeme stručnou definici pohledů.
POHLED (VIEW) je virtuální tabulka vytvořená na základě dotazu na běžné tabulky. Pohled je implementován jako dotaz, uložený na serveru a spouštěný při každém odkazu na pohled.
Podívejme se na různé varianty použití pohledů. Pohledy umožňují vytvářet úrovně datové struktury, které oddělují implementaci ukládání dat od jejich typu. Můžeme například vytvořit pohled, který vybírá data z několika tabulek. Pokud klienti používají tento pohled místo přímého odkazu na podkladové tabulky, bude moci vývojář databáze změnit dotaz, na kterém je pohled založen, upravit jej (například optimalizovat), a klient si ničeho nevšimne - bude to pro něj stejný pohled. Kromě toho, že izolují implementaci ukládání dat od uživatele, umožňují pohledy uspořádat data pohodlněji a jednodušeji. Problém „zjednodušení" datové struktury nastává, když počet tabulek v databázi vzroste natolik, že vztahy mezi nimi jsou složité. Pohled umožňuje odstranit (nebo naopak přidat) část dat, která nejsou pro konkrétního klienta databáze nezbytná (nebo jsou nezbytná).
Kromě toho pohledy umožňují jednodušeji organizovat zabezpečení v databázi InterBase. Někteří uživatelé mohou mít práva pouze ke čtení / aktualizaci dat v pohledu, ale nemají práva (a dokonce ani tušení) o tabulkách, na kterých je pohled založen! Podrobněji o zabezpečení v InterBase viz kapitola „Zabezpečení v InterBase: uživatelé, jejich funkce a práva" (část 4).
Syntaxe DDL pro práci s pohledy
Nyní se podíváme na příkazy pro vytváření a mazání pohledů definované jazykem DDL (Data Definition Language - podmnožina SQL, viz slovník). Pro vytvoření pohledu v InterBase použijeme větu s následující syntaxí:
CREATE VIEW název_pohledu [(sloupec_pohledu[, sloupec_pohledu…])] AS [WITH CHECK OPTION]; Zde je název_pohledu název pohledu, který musí být v rámci databáze jedinečný, a poté následuje skupina ne vždy povinných názvů polí zahrnutých do pohledu: [(sloupec_pohledu [, sloupec_pohledu …])]. Je nezbytné definovat příkaz , který vybírá data zahrnutá do pohledu. Volitelný parametr WITH CHECK OPTION probereme o něco později v části „Modifikované pohledy".
Pro změnu pohledu jej budeme muset znovu vytvořit, tj. smazat a znovu vytvořit. Při mazání pohledu je nutné smazat také všechny závislé objekty - triggery, uložené procedury a další pohledy. To je jedna z hlavních nevýhod práce s pohledy: nutnost znovu vytvářet strom objektů používajících pohled (existují nástroje, které to usnadňují, například IBAlterView, viz aplikace „Nástroje pro administrátory a návrháře InterBase"). Pokud chceme pohled smazat, použijeme následující příkaz DDL:
DROP VIEW název_pohledu;
Příklady pohledů
Zde je příklad jednoduchého pohledu:
CREATE VIEW MyView AS SELECT NAME, PRICE_1 FROM Table_example;
V tomto příkladu vytváříme pohled založený na dotazu na tabulku Table_example, kterou jsme probrali v kapitole „Tabulky. Primární klíče a generátory". V tomto případě bude pohled obsahovat dvě pole - NAME a PRICE_1, která budou vybrána z tabulky Table_example bez jakýchkoli podmínek, tj. počet záznamů v pohledu MyView bude roven počtu záznamů v Table_example. Pohledy však nejsou vždy tak jednoduché. Mohou být založeny na datech z několika tabulek a dokonce na základě jiných pohledů. Kromě toho mohou pohledy obsahovat data získaná na základě různých výrazů - včetně agregačních funkcí. Pro podrobnější zvážení použití tohoto pohledu vytvoříme dvě tabulky spojené vztahem jedna k mnoha (takový vztah se často nazývá master-detail). Zde je DDL skript pro vytvoření těchto tabulek:
/\* Tabulka: WISEMEN */
CREATE TABLE WISEMEN ( ID_WISEMAN INTEGER NOT NULL, WISEMAN_NAME VARCHAR(80));
/\* Definice primárního klíče */
ALTER TABLE WISEMEN ADD CONSTRAINT PK_WISEMEN PRIMARY KEY (ID_WISEMAN);
/\* Tabulka: WISEBOOK */
CREATE TABLE WISEBOOK ( ID_BOOK INTEGER NOT NULL, ID_WISEMAN INTEGER, BOOK VARCHAR(80));
/\* Definice primárního klíče */
ALTER TABLE WISEBOOK ADD CONSTRAINT PK_WISEBOOK PRIMARY KEY (ID_BOOK);
/\* Definice cizího klíče */
ALTER TABLE WISEBOOK ADD CONSTRAINT FK_WISEBOOK FOREIGN KEY (ID_WISEMAN) REFERENCES WISEMEN (ID_WISEMAN);
Vytvořili jsme tedy dvě tabulky - WISEMEN a WISEBOOK spojené vztahem master-detail pomocí omezení cizího klíče - FOREIGN KEY. Předpokládejme, že tyto tabulky budou ukládat informace o velkých čínských mudrcích a jejich dílech. Nyní můžeme na základě těchto tabulek vytvořit několik pohledů. Vytvořme například pohled zobrazující, kolik děl má každý mudrc:
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
Všimněte si, že při použití jakýchkoli vypočítaných výrazů, jako jsou agregační funkce COUNT (), SUM (), MAX () atd., je nezbytné použít konkrétní názvy polí pohledu, tj. pojmenovat všechna pole vrácená dotazem. Jak je vidět z tohoto příkladu, tato jména se nemusí nutně shodovat s názvy polí dotazu, ale jejich počet se musí shodovat s počtem polí vrácených dotazem. Přiřazení toho, které pole vrácené dotazem odpovídá kterému poli pohledu, se provádí pořadovým číslem - první pole dotazu se promítne do prvního pole pohledu, druhé do druhého atd.
A co když bychom chtěli vědět, který z mudrců napsal nejvíce knih? Zkusíme do dotazu, na kterém je pohled založen, přidat výraz pro řazení - ORDER BY. Tento pokus však bude neúspěšný: použití řazení ORDER BY v pohledech není povoleno a při pokusu o vytvoření pohledu s dotazem obsahujícím ORDER BY dojde k chybě. Pokud chceme seřadit výsledky vrácené pohledem, musíme to udělat na straně klienta:
SELECT * FROM WiseBookCount ORDER BY HOW_WISEBOOKS
Provedení tohoto SQL dotazu povede k požadovanému výsledku. Kromě omezení použití výrazu ORDER BY v pohledech nemůžeme jako zdroj dat použít ani datovou sadu získanou provedením uložených procedur (viz kapitola „Uložené procedury" níže).
Možná stojí za to uvést ještě jeden příklad ilustrující použití pohledů. Předpokládejme, že musíme vypsat seznam mudrců, jejichž jméno začíná písmenem „K". V tomto případě použijeme pohled s podmínkami:
CREATE VIEW WiseMen2 (WISEMAN) AS SELECT M.WISEMAN_NAME FROM WISEMEN M WHERE M.WISEMAN_NAME LIKE ‘K%’
Je tedy snadné vytvářet pohledy, které hrají roli neustále aktualizovatelných dodavatelů dat, vybírajících je z databáze podle určitých podmínek.
Modifikované pohledy
Výše jsme zmínili, že existuje možnost vytvářet modifikované datové pohledy. Je to skutečně tak - existuje možnost nejen číst data z pohledu, ale také je modifikovat!
Existují dva způsoby, jak učinit pohled modifikovatelným. První způsob se používá, když je pohled vytvořen na základě jediné tabulky (nebo jiného modifikovatelného pohledu) a všechny sloupce dané tabulky musí umožňovat přítomnost NULL. Dotaz, na kterém je pohled založen, tedy nemůže obsahovat poddotazy, agregační funkce, UDF, uložené procedury, příkazy DISTINCT a HAVING. Pokud jsou všechny tyto podmínky splněny, pohled se automaticky stane modifikovatelným, tj. můžeme pro něj provádět dotazy DELETE, INSERT a UPDATE, které změní data ve zdrojové tabulce.
Seznam podmínek je poměrně obsáhlý a výrazně omezuje použití takových modifikovatelných pohledů, proto se používají poměrně zřídka.
Abychom vytvořili modifikovatelný pohled, který porušuje některou z výše uvedených podmínek, použijeme mechanismus triggerů. Podrobněji o triggerech viz kapitola „Triggery" (část 1). Nyní se budeme zabývat pouze obecnými principy organizace změny dat v VIEW.
Pro implementaci aktualizovaného pohledu pomocí triggerů je třeba provést následující. Vytvořit 3 triggery pro daný pohled pro události: BEFORE DELETE, BEFORE UPDATE a BEFORE INSERT. V těchto triggerech popsat, co se má s daty dělat při mazání, aktualizaci a vkládání.
Poté použijeme daný pohled v modifikačních dotazech - DELETE, INSERT nebo UPDATE. Když InterBase obdrží tento dotaz, zkontroluje, zda existují příslušné triggery pro daný pohled, tj. BEFORE DELETE/INSERT/UPDATE. Pokud trigger pro prováděnou akci existuje, InterBase jej zavolá pro modifikaci skutečných dat v tabulkách, na kterých je pohled založen (i když to mohou být jiná data - neexistují žádná textová omezení těchto triggerů), a poté znovu přečte řetězec (nebo řetězce), nad kterými byla modifikace provedena.
Existuje tedy možnost realizovat složité řetězce aktualizace dat v pohledech.
Volba WITH CHECK OPTION byla zmíněna v popisu syntaxe vytváření pohledu. Pokud je tato volba nastavena při vytváření modifikovatelného pohledu, každý datový řetězec vložený nebo změněný v tomto pohledu bude zkontrolován na podmínku zařazení do pohledu. Lze to vysvětlit takto: pokud nový záznam vložený uživatelem nebo získaný aktualizací existujícího záznamu nesplňuje podmínky dotazu, který je dodavatelem dat pro VIEW, vložení tohoto záznamu bude zrušeno a dojde k chybě.
Závěr
Navzdory zdánlivé jednoduchosti vytváření a používání pohledů poskytují velké možnosti pro zlepšení organizace dat v databázi a umožňují vytvářet hierarchii organizace dat.
Někteří návrháři databázových aplikací používají pohledy ve své práci velmi často, jiní se jejich použití vyhýbají, přičemž to odůvodňují složitostí modifikace pohledů a snahou zachovat databázové schéma co nejjednodušší a nejefektivnější. Je na vás, jak budete pohledy ve své práci používat. Nejdůležitější je pamatovat na existenci tak výkonného nástroje, jakým pohled je, a vědět, jak jej používat.