PostgreSQL (24) - Views (Pohledy)

Jedním ze způsobů jak zefektivnit práci s databází, při výběrech nebo jak uživatelům omezit to co smějí vidět a co nikoliv, jsou pohledy.

26.9.2006 06:00 | MaReK Olšavský | přečteno 11754×

Teoretický úvod

Pohledy lze chápat jako virtuální tabulku s daty, ale co to znamená?

Pokud vám není jasné, jak mohou pohledy urychlit práci s databází, vysvětlím ihned. Jak se pohledy vytváří a spravují bude vysvětleno v následujícím textu, ale již bylo napsáno, že jsou to v podstatě jednosměrné (pouze pro výběr) tabulky, které jsou definovány příkazem SELECT. Pokud budete používat pohled přes několik tabulek, jsou veškeré joiny již uložené a zanalyzované, čiže následný SELECT z pohledu již neznamená vytvoření složitého dotazu na straně aplikace, jeho předání db serveru a analýza na straně serveru, které sloupce požadujete, přes které spojujete data a jaké indexy musí/může server použít. Jak je obvyklé, toto urychlení se vám moc neprojeví na krátkých tabulkách s minimem dat, ale bude velmi výrazné na rozsáhlých tabulkách. Při provozu jedné aplikace, která měla v databázi na počátku více než 5 mil. záznamů a další přibývaly dávkově po 200 tis. měsíčně, jsem použitím pohledů dosáhl asi 10 násobného urychlení výběrů a přitom jsem si připravil pouze pohledy přes spojované tabulky.

Pohledy prakticky

K vytvoření pohledu slouží velmi jednoduchý příkaz

CREATE [OR REPLACE] [TEMP | TEMPORARY] VIEW jmeno_pohledu AS SELECT ...;

Kde jmeno_pohledu je jen na vás, jen nesmí kolidovat se jménem již existujícího pohledu, pokud tento nehodláte opravdu přepsat, tabulky, indexu nebo sekvence. Do příkazu SELECT nakombinujete výběr z tabulky, nebo tabulek podle potřeb aplikace nebo úvahy. Sekvence OR REPLACE způsobí potlačení chyby, pokud již pohled se stejným jménem existuje, původní pohled je pak tím novým přepsán. Pohled lze vytvořit i ze statických dat.

Od verze PgSQL 8.1 přibyla možnost vytvářet přechodné pohledy, které platí pouze od jejich vytvoření do ukončení sezení, tj. do chvíle, dokud se uživatel (aplikace), který je vytvořil neodhlásí od databázového serveru.

Pochopitelně i pohledy musí patřit do nějakého schématu. Toto zařazení se realizuje zapsáním jména schématu před tečku. Pokud jméno schématu nebude určené, bude použito aktuální schéma, ve kterém pracujete.

Zrušení pohledu je stejně jednoduché a intuitivní, jako zrušení kteréhokoliv jiného objektu, jednoduchým příkazem

DROP VIEW jmeno_pohledu, ... [CASCADE | RESTRICT]

Modifikátory CASCADE a RESTRICT již znáte z předchozích dílů, takže již jen pro zopakování. CASCADE automaticky s rušeným pohledem zruší i všechny objekty, které na tomto pohledu, samozřejmě v databázi, závisí, například další objekty, třeba pohledy1. RESTRICT zakáže mazání závislých objektů, ale tímto si samozřejmě snadno narušíte konzistenci databázového modelu. Samozřejmě můžete zrušit současně několik pohledů jediným příkazem, podobné je to například u tabulek a indexů.

Příklady

První příklad si řekneme pouze obecně. Představte si databázi všech zaměstnanců firmy, každý z nich má určité pracovní zařazení (ředitelé, manageři, thp, vývojáři, …), vhodné pohledy mohou být pohledy podle pracovního zařazení a podle příslušných útvarů, které v organizaci jsou. Určitě by pro statistické údaje byl vhodný agregační pohled, kde by se podle útvarů a pracovních pozic ukládali průměrné hodnoty výplat a odpracovaných hodin.

Pro další příklad použijeme tabulky, které nám existují v databázi již z minulých dílů seriálu, tj. tabulky prodejců a jejich prodejů. Na tomto příkladu uvidíte i to, že pro pohledy je možné používat složených dotazů.

--vytvoreni pohledu prodejcu, kteri alespon neco prodali
CREATE VIEW dealers2 AS SELECT t1.name, t1.dealers_id FROM dealers AS t1
  WHERE EXISTS (SELECT dealers_id FROM orders AS t2 WHERE
  t1.dealers_id = t2.dealers_id);
  
--vytvoreni tehoz pohledu s vlastnim pojmenovanim sloupcu
CREATE VIEW dealers3(jmeno, dealer_mujid) AS SELECT t1.name,
  t1.dealers_id FROM dealers AS t1 WHERE EXISTS (
  SELECT dealers_id FROM orders AS t2 WHERE t1.dealers_id = t2.dealers_id);
  
--vyber dat z pohledu
SELECT * FROM dealers3 WHERE dealer_mujid>=4 ORDER BY jmeno ASC;

--vytvoreni pohledu se statickymi texty
CREATE VIEW dealers4(jmeno, dealer_mujid) AS SELECT t1.name,
  t1.dealers_id, 'dealer'::text AS popis FROM dealers AS t1 WHERE EXISTS (
  SELECT dealers_id FROM orders AS t2 WHERE t1.dealers_id = t2.dealers_id);  

Jak vidíte tvorba a základní manipulace s pohledy je jednoduchá, ale můžete značně urychlit výběry z databáze, definovat možnosti čtení dat podle různých pravidel o tom který uživatel smí vidět která data, podrobněji se budeme správě uživatelů a skupin věnovat v některém z dalších dílů, případně usnadnit vývojářům aplikací přístup k datům, která se čtou a změní se definice tabulek.

Závěr

Pokud používáte pro práci s PgSQL program phpPgAdmin, nebo se podíváte do počeštělé nápovědy v psgl prostředí/konzoli, setkáte se s použitím termínu náhled, místo pohled, já jsem se radši držel terminologie běžné v učebnicích SQL jazyka a příručkách k dalším SQL serverům.

Jsem si vědom, že mám poslední dobou poměrně veliké problémy s, alespoň rozumnou, pravidelností seriálu, nicméně nemusíte mít strach, že jej zanechám neukončený, ale bohužel mi poslední měsíce přibylo více povinností, mimo Linuxsoft, než by mi bylo milé.


1 Ano pohled může čerpat data z dalšího pohledu.

Online verze článku: http://www.linuxsoft.cz/article.php?id_article=1326