Databases 2021/22 spring

Vaated

Tuletame meelde, et nagu loengus rääkisime, siis vaade on virtuaalne tabel ehk päring, millele paneme nime, et seda ei peaks korduvalt kirjutama, vaid saaks nimega välja kutsuda. Vaate ehk nimelise päringu loomise lause:

CREATE VIEW <vaate_nimi> [({veerud})]
AS {select lause ilma järjestamiseta};

Mõned näited vaadete loomise kohta. Meil on sageli vaja saada inimeste nimekirja koos klubi nimega. Loome esmalt vaate, milles on nii isikute kui ka klubide andmed. Selles vaates moodustame ühe välja, kus on koos nii ees- kui perenimi. Vaates võime seda teha, sest see ei sega päringuid.

CREATE VIEW v_isikudklubid(isik_nimi, isik_id, klubi_nimi, klubi_id) AS
SELECT isikud.perenimi || ', ' || isikud.eesnimi, isikud.id, klubid.nimi, klubid.id 
FROM isikud JOIN klubid ON isikud.klubis = klubid.id;

Paneme tähele, et nime ette kirjutasime v_. See on kokkuleppeline, aga meie oma kursusel hakkame eristama vaatenimesid selle eesliitega. Kasutatakse ka järelliidet vaadete nimes _View või ei kasutata üldse liiteid. See on konkreetse organisatsiooni kujundada. Kutsume nüüd loodud vaate välja päringus:

SELECT * FROM v_isikudklubid

Oletame, et soovime ainult mängijate nimesid koos klubinimega ja järjestatuna. Järjestus selline, et esiteks tähestikuliselt klubi nimede alusel ja siis perenime alusel. Teostame sellise päringu vaate pealt v_isikudklubid:

SELECT isik_nimi, klubi_nimi 
FROM v_isikudklubid
ORDER BY klubi_nimi, isik_nimi

Vaadet saab kasutada teise vaate loomisel nagu me ka loengus rääkisime. Näiteks tahame luua vaate v_punktemustadega, milles on isiku nimi koos klubi nimega (saame kasutada eelnevalt loodud vaadet v_isikudklubid) ja partiide tabelist, kui palju nad keskmiselt mustadega mängides punkte said. Kirjutame:

CREATE VIEW v_punktemustadega AS 
SELECT isik_nimi AS Mängija, klubi_nimi AS Klubi, AVG(musta_tulemus) AS 'Musta tulemus'
FROM v_isikudklubid LEFT JOIN partiid ON v_isikudklubid.isik_id=partiid.must
GROUP BY isik_nimi, klubi_nimi;

Nagu näete, siis siin ühendamine on samade välisvõtmete abil nagu on ühendatud ka baastabelid. Baastabelis Partiid välisvõtmeks on Must, mis viitab Isikud tabeli ID'le. Kui nüüd loome uue vaate (antud juhul v_isikudklubid), kuhu lisame ka Isikud tabelist ID, siis ka vaate puhul tabeli Partiid välisvõti Must viitab sellele.

Päringulause võib sisaldada ka tingimust, mis pannakse SQLis kirja

IF… THEN…. 
ELSE… 
ENDIF

Loome vaate nimega v_voitjad

CREATE VIEW v_voitjad AS
SELECT id, valge, must,
IF valge_tulemus=2 THEN 'valge võit' 
    ELSE IF musta_tulemus=2 THEN 'musta võit'
    ELSE 'viik'
    ENDIF 
ENDIF AS 'kes_voitis'
FROM Partiid

Vaadet saab kustutada DROP VIEW <vaate_nimi>.

Kustutame vaate v_mangijad, mis meil on andmebaasis eelnevalt loodud.

DROP VIEW v_mangijad

Vaate kaudu baastabelisse andmete sisestamine, uuendamine ja kustutamine

Kuigi põhiliselt kasutatakse vaadet ikkagi päringutes, siis teatud tingimustel saab läbi vaate baastabelisse andmeid lisada, muuta ja kustutada. Vaade, mille kaudu saab baastabelisse andmeid sisestada, muuta ja kustutada peab olema loodud nii, et:

• vaates kasutatavas SELECT lause FROM osas on ainult üks tabel (või üks vaade);
• SELECT lauses ei ole kasutatud meile teadaolevatest GROUP BY, HAVING, LIMIT, DISTINCT, UNION, INTERSECT ega EXCEPT;
• SELECT lauses ei ole kasutatud agregeerivaid funktsioone (näiteks SUM, COUNT jms);
• ei ole kasutatud alampäringuid ja erinevaid OUTER JOIN’i

Erinevatel andmebaasi juhtimissüsteemidel on need tingumused ka veidi erinevad.

Soovitav on kasutada vaadete puhul, mille kaudu baastabelite andmeid saab muuta või sisestada WITH CHECK OPTION kitsendust. Näiteks loome vaate v_knimed, millesse võtame isikud, kelle puhul nii ees- kui ka perenimi algab K-tähega:

CREATE VIEW v_knimed AS
SELECT eesnimi, perenimi 
FROM isikud
WHERE eesnimi LIKE 'K%' AND perenimi LIKE 'K%'
WITH CHECK OPTION

Lisame selle vaate kaudu tabelisse Isikud isiku nimega Kalle Kusta.

INSERT INTO v_knimed (eesnimi, perenimi) VALUES ('Kalle', 'Kusta')

Võite kontrollida tabelist isikud, kas vastav kirje on lisatud. Tabeli lõpus on isik Kalle Kusta, kes ei kuulu ühtegi klubisse.

Tahame aga lisada ka Mari Maasika.

INSERT INTO v_knimed (eesnimi, perenimi) VALUES ('Mari', 'Maasikas')

Siis sellisel juhul saame veateate:

Ehk siis kuna meie poolt soovitav kirje Mari Maasikas ei vasta vaate tingimustele (ees- ja perenimi ei alga K-tähega), siis lisamist ei teostada. Kui me poleks vaate loomisel lisanud lõppu kitsenduse WITH CHECK OPTION, oleks saanud vabalt ka Mari Maasika vaate kaudu lisada.

Miks seda vaja on? Näiteks on meil korterite andmebaas. Igal korteri omanikul peab olema luba lisada oma korterisse elanikke (saab vaate kaudu). Samas ei tohi keegi ju lisada elanikke naaberkorterisse.

Läbi vaate saab ka kirjeid uuendada ning kustutada. Uuendame kirjet vaate kaudu:

UPDATE v_knimed SET eesnimi = 'Kalle Kalvi' 
WHERE eesnimi='Kalle' AND perenimi='Kusta'

Samamoodi kuna meil on vaatel v_knimed kitsendus peal, siis ei saaks me muuta Kalle Kusta nime aga näiteks Malle Kustaks või Kalle Maasikaks. Kirje kustamisel vaate kaudu:

DELETE FROM v_knimed 
WHERE eesnimi='Kalle Kalvi' and perenimi='Kusta'

Vaate muutmine

Vaadet saab ka muuta. Vaate muutmisel kirjutatakse uus SELECT lause, milles võib olla esialgse vaatega võrreldes muudetud veergude järjekord, lisatud veerge, veerge kokkuvõetud või ka midagi ära jäetud. Viimasel juhul peab eelnevalt veenduma, et seda vaadet ei kasuta mõni teine vaade või rakendus, kus need veerud on vajalikud! SQL lause:

CREATE OR REPLACE VIEW <vaate_nimi> [({veerud})]
AS {select lause ilma järjestamiseta}
[ WITH CHECK OPTION ]

Meil on andmebaasis vaade v_klubi54:

Muudame vaadet, et selles poleks isikukoodi ja et oleks 54 asemel vastava klubi nimi.

CREATE OR REPLACE VIEW v_klubi54 AS
SELECT isikud.id, eesnimi, perenimi, nimi 
FROM isikud KEY JOIN klubid
WHERE klubi=54;

Vaade muutus:

Vaadet saab ka muuta tuttava ALTER käsuga.

ALTER VIEW <vaate_nimi> AS SELECT …

Materialiseeritud vaade

Nagu loengus rääkisime, siis vaatamata sellele, et materialiseeritud vaade võtab mäluruumi, siis sellest tehtud päring töötab kiiremini kui tavaline vaade. Seega kui tegemist on suure andmebaasiga, siis nii mõnigi kord kui tavalise vaate kaudu päring võtab liiga kaua aega, on materialiseeritud vaate loomine vajalik.

SQL lause materialiseeritud vaate loomiseks:

CREATE MATERIALIZED VIEW <vaate_nimi> 
AS {select lause}

SQL Anywhere abil ei saa luua materialiseeritud vaadet kasutades tavalisi vaateid, vaid ainult baastabelite põhjal. Teatud andmebaasi juhtimissüsteemides (näiteks Oracle omas) on see võimalik.

Kuigi meie male andmebaasi korral ükski päring liiga kaua aega ei võta, kuid reaalses elus on see oskus vajalik, siis loome materialiseeritud vaate mv_punktevalgetega, milles on isiku nimi koos klubi nimega (siin kahjuks ei saa kasutada SQL Anywheres eelnevalt loodud vaadet v_isikudklubid) ja partiide tabelist, kui palju nad keskmiselt valgetega mängides punkte said.

CREATE MATERIALIZED VIEW mv_punktevalgetega AS
SELECT  isikud.perenimi || ', ' || isikud.eesnimi AS Mängija, klubid.nimi AS Klubi, AVG(valge_tulemus) AS 'Valge tulemus'
FROM isikud LEFT JOIN klubid ON isikud.klubis = klubid.id,
isikud LEFT JOIN partiid ON isikud.id=partiid.valge
GROUP BY Mängija, Klubi;

Materialiseeritud vaadet kutsutakse välja nagu tavalist vaadet. Mõnedes andmebaasisüsteemides nagu ka SQL Anywhere on vaja aga eelnevalt see materialiseeritud vaade värskendada (süntaks: REFRESH MATERIALIZED VIEW <vaate_nimi>) ja alles siis teha päring. Seega kirjutame

REFRESH MATERIALIZED VIEW mv_punktevalgetega;
SELECT  *  FROM mv_punktevalgetega
ORDER BY 3 DESC;

Lisame vaate v_knimed kaudu tabelisse Isikud isiku nimega Kaja Kallas

INSERT INTO v_knimed (eesnimi, perenimi) VALUES ('Kaja', 'Kallas');

Kui vaatame materialiseeritud vaadet, siis teda endiselt tulemustes pole. Et ka uus lisatud isik oleks päringutulemuses, mis kasutab materialiseeritud vaadet, peame selle taas värskendama.

REFRESH MATERIALIZED VIEW mv_punktevalgetega;

Nüüd on see lõpus tõesti olemas:

Materialiseeritud vaate saab kustutada:

DROP MATERIALIZED VIEW <vaate_nimi>