ARCHIV

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ý | Články autora | přečteno 15263×

Teoretický úvod

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

• Nejsou to skutečné tabulky s daty, takže nezabírají žádné další místo v souborovém prostoru databáze.
• Jsou definovány pomocí příkazu SELECT, mohou obsahovat výběr sloupců, či řádek z jediné tabulky, ale mohou být i z několika joinovaných tabulek. Omezení na sloupce, či řádky umožní patřičným uživatelům vidět data, která smějí vidět.
• Mohou obsahovat nejen sloupce a řádky z jiných tabulek, ale mohou to být i takzvané agregační pohledy.
• Jejich použití může velmi usnadnit vývoj aplikace, pokud se například rozhodnete jednu tabulku rozdělit na několik dalších, které budou obsahovat další, nové údaje, ale v aplikaci budete ke stávajícím sloupcům a tabulkám přistupovat přes pohledy, které jen předefinujete, čiže nemusíte v aplikaci nikterak měnit názvy položek k nimž přistupujete.
• Pohledy v PostgreSQL, ale i v naprosté většině dalších db serverů, jsou ryze jednosměrnou záležitostí. Lze z nich data číst, ale nelze přes ně data vkládat, mazat, či updatovat. Vkládání dat je tedy nutné řešit pomocí přímého přístupu k tabulkám, třeba pomocí stored procedur.
• Pohledy jsou definovány jako perzistentní objekty na straně PgSQL serveru, tj. nezanikají po odpojení uživatele, který je vytvořil, ale jsou přístupné (samozřejmě, že přístupy lze delegovat pouze vybraným uživatelům/skupinám) do jejich změny, nebo zrušení. Toto chování se dá od verze 8.1 upravit.

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 pohledy¹. 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é.

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

Verze pro tisk

DISKUZE akcelerace pohledu 26.9.2006 07:22 Pavel Stěhule   Re: akcelerace pohledu 26.9.2006 08:16 MaReK Olšavský Zobrazit označené Zobrazit vše Příspívat do diskuze mohou pouze registrovaní uživatelé.