Jedeme dál! Hlavním tématem dalτího pokraƒování naτeho seriálu jsou virtuální tabulky.
Zpátky k DDL
DDL - Data Definition Language jako souƒást SQL umoºσuje vytvá²ení a ruτení virtuálních tabulek. Ty jsou anglicky oznaƒovány jako VIEW, coº ƒesky neznamená nic jiného neº pohled na n╪co ƒi do n╪ƒeho. Virtuální tabulka je totiº jenom pohledem do jiné tabulky nebo tabulek. Zatímco klasická tabulka CLOVEK je vytvá²ena p²íkazem CREATE TABLE CLOVEK s uveden∞m seznamem sloupcà, je virtuální tabulka PRACHAC vytvá²ena z uº existující tabulky CLOVEK pomocí p²íkazu CREATE VIEW PRACHAC, uvnit² kterého je uveden vhodn∞ p²íkaz SELECT. V naτem konkrétním p²ípad╪ bude vhodné pojmenovat i sloupce nov╪ vzniklé virtuální tabulky. Tak dostaneme p²íkaz pro její vytvo²ení:
CREATE VIEW PRACHAC(RODNE,
PRIJMENI, JMENO, JMENI)
AS
SELECT RC, PRIJMENI, JMENO,
SUM(STAV) INTO PRACHAC
FROM CLOVEK, UCET
WHERE CLOVEK.RC=UCET.RC
GROUP BY CLOVEK.RC
HAVING SUM(STAV)>1000000;
Pokud v okamºiku formulace dotazu existovaly tabulky CLOVEK a UCET a neexistovala tabulka PRACHAC, vznikne virtuální tabulka PRACHAC bez problémà. V ostatních p²ípadech virtuální tabulka nevznikne. Pokud dojde ke zm╪nám v obsahu tabulek CLOVEK nebo UCET, okamºit╪ se promítnou do virtuální tabulky PRACHAC, do které lze nahlédnout jako do kaºdé jiné jedním z následujících p²íkazà:
SELECT * FROM PRACHAC
ORDER BY PRIJMENI,JMENO,RODNE;
SELECT * FROM PRACHAC
ORDER BY JMENI DESC, PRIJMENI, JMENO, RODNE;
SELECT * FROM PRACHAC
WHERE JMENI>100000000;
SELECT PRIJMENI, MAX(JMENI)
FROM PRACHAC
GROUP BY PRIJMENI;
Nyní je z²ejmé, ºe vytvo²ení VIEW se vyplatilo a ºe pouºívání ORDER BY uvnit² VIEW není k niƒemu. Pro úplnost je uveden obecn∞ tvar p²íkazu pro vytvo²ení virtuální tabulky:
CREATE VIEW název virtuální tabulky (seznam názvà sloupcà)
AS
SELECT seznam v∞razà
FROM seznam tabulek
WHERE logick∞ v∞raz
GROUP BY seznam sloupcà
HAVING logick∞ v∞raz;
Je nutné respektovat následující omezení p²íkazu CREATE VIEW:
- Nesmí obsahovat ORDER BY.
- Nesmí obsahovat
HAVING bez GROUP BY.
- Seznam v∞razà projekce musí mít stej-
n∞ poƒet ƒlenà jako seznam názvà
sloupcà.
- Seznam názvà sloupcà je nepovinn∞.
Virtuální tabulka vznikne, je-li vnit²ní p²íkaz SELECT platn∞ a je p²ipravena pro ƒtení jin∞m p²íkazem SELECT. Virtuální tabulka se màºe odkazovat na jiné tabulky vƒetn╪ virtuálních tabulek. Odkazování virtuální tabulky na sebe samu není moºné. Pokud budeme chtít virtualitu nové tabulky brát doslova a budeme ji chtít aktualizovat pomocí INSERT, UPDATE nebo DELETE, musíme se pon╪kud uskrovnit a p²ijmout dalτí omezení. P²i popisu projekce nesmí obsahovat v∞razy GROUP BY, DISTINCT, více neº jednu tabulku za FROM, JOIN ani zahnízd╪n∞ dotaz. Pozornému ƒtená²i jist╪ neuτlo, ºe v zájmu absolutní virtuality p²iτel tém╪² o vτechny vymoºenosti p²íkazu SELECT. Zdrav∞ rozum velí naopak zapomenout na absolutní virtualitu a v╪novat se naplno vymoºenostem virtuálních tabulek urƒen∞ch jenom pro ƒtení. Koneƒn╪ tak máme k dispozici nástroj k virtuálnímu, ale trvalému spojování n╪kolika tabulek v jeden celek. Te╘ bude velmi snadné dívat se na reálná data v n╪kolika tabulkách a zároveσ nemít ºádnou zbyteƒnou práci. Nad klasick∞mi tabulkami tak màºeme vytvo²it i n╪kolik hierarchick∞ch vrstev virtuálních tabulek, které pomohou p²ísn╪ strukturovan∞ sv╪t fyzicky uloºen∞ch dat odd╪lit od sv╪ta lidí a jejich klientsk∞ch aplikací. To slouºí nejen k usnadn╪ní ƒtení dat, ale mnohdy téº k utajení základních datov∞ch struktur p²ed konkurencí. Virtuální tabulka je velmi k²ehk∞m objektem, kter∞ relativn╪ snadno zaniká. Klasickou cestou ke zruτení naτí virtuální tabulky PRACHAC je pouºití p²íkazu
DROP VIEW PRACHAC;
Virtuální tabulka PRACHAC zanikne, zruτíme-li n╪kterou z tabulek CLOVEK, UCET nebo n╪kter∞ z pouºívan∞ch sloupcà t╪chto tabulek nap²íklad pomocí p²íkazà
DROP TABLE CLOVEK;
DROP TABLE UCET;
ALTER TABLE CLOVEK
DROP COLUMN RC;
ALTER TABLE UCET
DROP COLUMN STAV;
To je první a poslední p²ípad, kdy si v SQL vznikl∞ objekt nechrání své zdroje p²ed zniƒením a rad╪ji zruτí sám sebe. Porovnejte to nap²íklad s vytvá²ením, uºitím a ruτením indexà, které si svoji tabulku dàsledn╪ hlídají. Pouºijeme-li p²íkazy
DELETE FROM CLOVEK;
DELETE FROM UCET;
bude to patrn╪ velká ztráta informací o lidech a úƒtech, ale virtuální tabulka PRACHAC to p²eºije ve zdraví a kdykoli bude p²ipravena zobrazit kaºdého milioná²e, kter∞ bude znovu zadán do prázdn∞ch tabulek CLOVEK a UCET.
Virtualita se vyplatí
M╪jme tabulku CLOVEK se sloupci RC, JMENO, PRIJMENI a tabulku KONTAKT se sloupci RC, TYP a SPOJENI. Mezi tabulkami existuje relace zprost²edkovaná rodn∞m ƒíslem RC, která je typu N : 1 a má název KONTAKT NA CLOVEKA. Sloupec TYP p²edstavuje typ kontaktu jako TELEFON, FAX, EMAIL a podobn╪, zatímco sloupec SPOJENI obsahuje konkrétní hodnotu telefonního ƒísla, ƒísla faxu ƒi poƒítaƒovou adresu. Pokud jsou ob╪ tabulky zapln╪ny bezrozporn∞mi daty, máme motivaci k vytvo²ení celé ²ady virtuálních tabulek. P²edevτím nás bude zajímat seznam lidí a p²ísluτn∞ch spojení:
Od tohoto okamºiku je zbyteƒné ƒíst data p²ímo z pàvodních tabulek. Nevadí, ºe tabulka CLOVICEK je virtuální. Màºeme z ní vytvo²it telefonní seznam p²íkazem:
CREATE VIEW TELEFON(RC, JMENO, PRIJMENI, TEL)
AS
SELECT RC, JMENO, PRIJMENI,
SPOJENI FROM CLOVICEK
WHERE TYP="TELEFON";
Virtuální tabulka TELEFON umoºní zobrazení telefonního seznamu p²íkazem
SELECT * FROM TELEFON ORDER BY PRIJMENI, JMENO, RC, TEL;
Uº jist╪ víte, jak vytvo²it VIEW vτech faxov∞ch kontaktà, vτech e-mailov∞ch kontaktà nebo seznam vτech lidí, na které není ºádn∞ kontakt. P²edstavte si, ºe chcete v rámci marketingové akce zavolat vτem lidem, na které existuje alespoσ jeden telefonní kontakt a kte²í jeτt╪ nemají internetovou adresu. Na to staƒí vytvo²it virtuální tabulku AKCE:
CREATE VIEW AKCE(PRIJMENI, JMENO, RC, TEL)
AS
SELECT PRIJMENI, JMENO, RC,
SPOJENI FROM CLOVICEK
WHERE TYP="TELEFON"
AND RC NOT IN
(SELECT DISTINCT RC FROM
CLOVICEK WHERE TYP="EMAIL");
Kdykoli se pak lze zeptat, kter∞m klientàm máme nabízet sluºbu:
SELECT * FROM AKCE ORDER BY
PRIJMENI, JMENO, RC, TEL;
Pokud tvàrce databázového systému vhodn╪ navrhne tabulky, relace mezi nimi, a nad tím vτím vybuduje racionáln╪ systém virtuálních tabulek, je vτe p²ipraveno k formulaci jednoduch∞ch klientsk∞ch dotazà SELECT do jednotliv∞ch virtuálních tabulek s jednoduchou podmínkou za WHERE a definovan∞m t²íd╪ním pomocí ORDER BY. Tvàrce aplikace pak ani nemusí tuτit, jak dokonalého systému se vlastn╪ ptá. P²iberme si do naτeho minisystému jeτt╪ dalτí dv╪ tabulky. T²etí tabulka UCET bude mít jako klíƒ CISUCTU a navíc jeτt╪ dva sloupce RC a STAV, kde RC je rodné ƒíslo majitele úƒtu a STAV je okamºit∞ stav úƒtu v korunách. Obƒas majitel úƒtu dovolí jin∞m lidem, aby z n╪j také sm╪li vybírat peníze. Tento jev je popsán tabulkou PRAVO se sloºen∞m klíƒem, tvo²en∞m sloupci CISUCTU a RC. Zároveσ p²ibylo n╪kolik relací N : 1 mezi tabulkami. Jde o relace UCET VLASTNI OSOBA, PRAVO PRO OSOBU a PRAVO NA UCET. Te╘ je t²eba nové tabulky pokr∞t uºiteƒn∞m systémem virtuálních tabulek. P²edevτím bude uºiteƒné vytvo²it virtuální tabulku lidí a jejich úƒtà:
CREATE VIEW CLOVEK_S_UCTY
(PRIJMENI, JMENO, RC, UCET, STAV )
AS
SELECT PRIJMENI, JMENO,
CLOVEK.RC, CISUCTU, STAV
FROM CLOVEK LEFT JOIN UCET
ON CLOVEK.RC=UCET.RC;
Pro vytipování nebo odstran╪ní lidí, kte²í nemají vlastní úƒet, budou pot²ebné dalτí dv╪ virtuální tabulky. Navíc uτet²íme na p²ejmenovávání sloupcà:
CREATE VIEW IGNORANT_BANK
AS
SELECT PRIJMENI, JMENO, RC
FROM CLOVEK_S_UCTY
WHERE UCET IS NULL;
CREATE VIEW MAJITEL_UCTU
AS
SELECT PRIJMENI, JMENO, RC,
UCET, STAV FROM CLOVEK_S_UCTY
WHERE UCET IS NOT NULL;
Souhrnné údaje mají mnohdy v╪tτí cenu neº podrobné v∞pisy, a tak máme o dàvod víc k tomu, abychom pokraƒovali v návrhu dalτí virtuální tabulky:
CREATE VIEW MAJITEL(PRIJMENI, JMENO, RC, CELKEM)
AS
SELECT PRIJMENI, JMENO, RC,
SUM(STAV) FROM MAJITEL_UCTU
GROUP BY RC;
Nenechaví lidé poƒítají do svého jm╪ní i stavy na úƒtech, na které mají právo v∞b╪ru, coº vyºaduje rafinovan╪jτí virtuální tabulku nad tabulkami CLOVEK, UCET a PRAVO:
CREATE VIEW NAROK(PRIJMENI,
JMENO, RC, MAJITEL, UCET, STAV)
AS
SELECT PRIJMENI, JMENO,
CLOVEK.RC, CLOVEK.RC=UCET.RC,
CISUCTU, STAV
FROM CLOVEK, UCET, PRAVO
WHERE CLOVEK.RC=UCET.RC OR
CLOVEK.RC=PRAVO.RC AND
PRAVO.CISUCTU=UCET.CISUCTU;
V∞raz CLOVEK.RC=UCET.RC je p²ejmenován na sloupec MAJITEL, kter∞ má pak logickou hodnotu YES, nebo NO, podle toho, zda je dan∞ ƒlov╪k majitelem úƒtu, nebo má na n╪j jenom právo v∞b╪ru. Celkov∞ sumarizovan∞ pohled do dat:
CREATE VIEW SUMARIZACE
(PRIJMENI, JMENO, RC, CELKEM)
AS
SELECT PRIJMENI, JMENO, RC,
SUM(STAV) FROM NAROK
GROUP BY RC;
Chceme-li zjistit, kolikrát v╪tτími ƒástkami disponují lidé v porovnání se skuteƒn∞m vlastnictvím, vytvo²íme si virtuální tabulku:
