SQL – od tabulek k pohledům

Dosud jsme v našem seriálu vždy pracovali pouze s tabulkou. Dnes si ukážeme a vysvětlíme si v mnohém podobný objekt – pohled. Možná ani nevíte, že ve velkém počtu případů v praxi pracujete právě s pohledem a nikoli s tabulkou. Tedy, abych byl přesnější – navenek.

Pod tabulkou jsme dosud chápali relaci, pro kterou platí, že jak její definice (tedy atributy s typy a integritními omezeními), tak data jsou fyzicky uložena v databázi. Řekli jsme si, že tabulku můžeme založit jedním z DDL příkazů – CREATE TABLE. V prvním díle našeho seriálu jsme začali používat tabulku osoby, která měla následující podobu:

Jméno	Příjmení	rodné_číslo	datum
Karel	Smolný	580815/8777	3.12.1992
Marek	Jurda	650404/8956	2.11.1992
Martin	Pavlásek	720303/5511	28.3.1990
Pavel	Jirásek	551212/7877	26.3.1989
Pavla	Nováková	785308/4569	28.6.1997
Pavla	Nováková	555111/3042	28.5.1990
Romana	Bourková	745226/8820	3.5.1995

Nyní si představme situaci, že k uvedené tabulce přistupují dva uživatelé – ředitel a jeho zástupce. Ředitele vždy zajímají pouze osoby, které nastoupily do zaměstnání (určeno atributem datum) před 1.1.1993, jeho zástupce ti s nástupním datem pozdějším. Jedná se o jediný rozdíl v programu, který používají. Zatím bychom měli dokázat napsat dva rozdílné příkazy SELECT, řešící právě tento požadavek.

SELECT * 
  FROM osoby
  WHERE datum<’1.1.1993’

a

SELECT *
  FROM osoby
  WHERE datum>’31.12.1992’

Na první pohled nic složitého – ale pokud bychom měli daný příkaz upravovat na dvaceti místech a nebyl zdaleka tak triviální, může se programování stát noční můrou. A právě řešení takového problému je úkolem (nikoli však jediným) pohledu, kterému v angličtině říkáme view.

Pohled je velmi podobný tabulce. Také obsahuje atributy (sloupce), řádky (záznamy) či se může používat v SQL příkazech (existují však určitá omezení, jak si ukážeme později). Jeden rozdíl je však podstatný – v databázi je uložena pouze definice pohledu, nikoli data. Ta jsou čerpána přímo z tabulky (či tabulek), nad kterou je pohled definován. Pohled je tedy jakousi virtuální tabulkou, která pracuje s daty uloženými v tabulce jiné. Pohled zdánlivě „materializuje“ příkaz SELECT do tabulky.

Pohled vytvoříme obdobně jako tabulku – pomocí příkazu CREATE VIEW, který má (ve zjednodušené podobě) následující syntaxi:

  CREATE VIEW [(atributy)]
      <jméno_pohledu>
    AS <dotaz>

kde na místě dotazu může být příkaz SELECT ve všech nám známých variantách (normy SQL nekladou žádná omezení na použití konstruktu SELECT při vytváření pohledu – bohužel totéž nelze říci o jednotlivých SŘBD). Pokud specifikujeme atributy, musí být jejich počet shodný s počtem atributů (včetně vypočítávaných, například pomocí agregačních funkcí) ve vlastním příkazu SELECT. Ve druhém díle jsme si uvedli, že tento příkaz může obsahovat tzv. aliasy (zde ve smyslu přejmenování atributů). Tuto metodu je nutno použít při vytváření pohledu (opomeneme-li vyjmenovat atributy) právě v případě vypočítávaných hodnot či v případě nejednoznačnosti (pohled může být vytvořen pomocí příkazu SELECT spojujícího více tabulek).

Ale zpět k našemu příkladu – variantu pro pana ředitele můžeme vytvořit takto:

CREATE VIEW osoby_red
  AS SELECT * 
    FROM osoby
    WHERE datum<’1.1.1993’

Příkaz

SELECT *
  FROM osoby_red

nám vybere pouze záznamy vyhovující podmínce uplatněné při vytváření pohledu:

jméno  příjmení  rodné_číslo  datum 
---------------------------------------
Karel  Smolný    580815/8777  3.12.1992
Marek  Jurda     650404/8956  2.11.1992
Martin Pavlásek  720303/5511  28.3.1990
Pavel  Jirásek   551212/7877  26.3.1989
Pavla  Nováková  555111/3042  28.5.1990

Při tvorbě programu nám pak stačí změnit pouze jméno použitého pohledu (v některém z následujících dílů si ukážeme, že by nemuselo být nutné ani to – stačilo by vhodným způsobem použít tzv. synonyma).

Zopakujme si, že pohled je pouze virtuální tabulkou, která žádná fyzická data neobsahuje. O aktualizaci se nemusíme starat – každá změna v původní tabulce se automaticky projeví i v pohledu. Musíme si ale dát pozor především na komplikované pohledy pro rozsáhlé tabulky – záleží sice na konkrétním systému, ale lze říci, že při každém dotazu nad pohledem se tento znovu vyhodnocuje, není-li v dočasné paměti od posledního použití a současně nedošlo v původní tabulce ke změně.

Pohled zrušíme pomocí příkazu DROP VIEW <jméno_pohledu>. V našem případě tedy

DROP VIEW osoby_red

Odstranění původní tabulky by mělo automaticky vyvolat i zrušení pohledů, můžeme se však v praxi setkat s určitými odlišnostmi.

V některých systémech můžeme vytvořit pohled i nad tabulkou, která v okamžiku vytváření neexistuje. Uplatnění můžeme nalézt zejména v případech, kdy jsou pohledy vytvářeny nad dočasnými tabulkami. K problémům také dochází v případě, kdy chceme data v pohledu měnit či vkládat data nová. Obecně můžeme říci, že pohledy jsou určeny pouze pro výběr dat. Naštěstí v mnoha případech nemusí být toto omezení nutné. Stoprocentně platí pouze tehdy, je-li pohled vytvořen za pomoci agregačních funkcí (změna dat by neměla smysl) či za pomoci spojení z více tabulek. Pokud je pohled definován pouze s omezením na záznamy (viz. pohled osoby_red), jediným problémem může být zaktualizování pohledu (při změně hodnot omezujících atributů či vkládání nových záznamů nevyhovujících podmínce z definice pohledu). Máme-li dotaz definující pohled omezen na jednotlivé atributy (hovoříme o projekci), změna hodnot je povolena pouze tehdy, zůstanou-li platná omezení (null hodnoty, referenční integrita, primární klíče) v původní tabulce.

Pohledy jsou v praxi používány velmi často, proto je pro odborníky nezbytné, aby se je naučili správně používat. Nezapomeneme-li na výše uvedená omezení, mohou nám ušetřit mnoho problémů. Odpověď na zatím nepoloženou otázku, jak poznat, zda se jedná o tabulku či pohled, není vůbec jednoduchá – záleží na konkrétním systému. A co bude příště? Podíváme se na integritní omezení, mezi které patří již zmíněné klíče (primární, cizí atd.).