Andmebaasid (sessioonõpe) 2023/24 kevad

Vaadete loomine

Vaade

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. Tavaline vaade ei loo uut tabelit või tabeleid. Vaate ehk nimelise päringu loomise lause:

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

SELECT päring võib koosneda kõikidest päringusse koosnevatest osadest, mida oleme varem õppinud.

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, synniaeg, klubi_nimi, klubi_id) AS
SELECT isikud.perenimi || ', ' || isikud.eesnimi, isikud.id, synniaeg, klubid.nimi, klubid.id 
FROM isikud JOIN klubid ON isikud.klubi = 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. Võime kutsuda välja kõik veerud ja read, mis vaates, aga võime ka kasutada piiranguid. 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;

Kontrollime ka DBeaver menüüst, kas vaade tekkis. Selleks, lähme vaadete (View) menüüsse (vajadusel parema hiirevajutusega avame menüü, kus valime “Refresh” (Uuenda), et viimati loodud vaated ka ilmuks).

DBeaveri kaudu saame loodud vaate (või varem loodud vaadete) kohta ka detailsemat informatsiooni. Näiteks, millise SQL lausega loodi vaade. Valides DDL saame SQL lause, millega valitud vaade loodi.

Kontrollimaks, kas said vaate loomisest aru ava soovi korral test.
AVA TEST

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 Mangija, klubi_nimi AS Klubi, AVG(must_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.

Teine aspekt, mida pange tähele, et me ei kasuta ka vaate veergude pealkirjades täpitähti. Täpitähti realiseeritakse rakendustes.

Päringulause võib sisaldada ka tingimuslauset: PostgeSQL'is pannakse see kirja CASE väljendi abil

CASE
    WHEN tingimus(ed)1 THEN tulemus1
    WHEN tingimus(ed)2 THEN tulemus2
    [WHEN......]  
    [ELSE tulemus] 
END

Loome vaate nimega v_voitjad, mis kirjutaks tulemustabelisse, kas võitis valge, must või oli tegemist viigiga.

CREATE VIEW v_voitjad AS
SELECT id, valge, must,
CASE
   WHEN valge_tulemus=2 THEN 'valge võit' 
   WHEN must_tulemus=2 THEN 'musta võit'
   ELSE 'viik'
END voitja
FROM Partiid

Lisalugemiseks link PostgreSQL dokumentatsioonile

Vaata videost, kuidas luua vaateid::

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. Materialiseeritud vaade salvestab päringu tulemuse ka kõvakettale ja järgmine kord kui materialiseeritud vaadet pärida, siis ei minda enam vaate sees olevat päringut päriselt sooritama vaid kasutatakse salvestatud andmeid.

SQL lause materialiseeritud vaate loomiseks:

CREATE MATERIALIZED VIEW <vaate_nimi> 
    AS {SELECT lause}

PostgreSQL'is 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 eelnevalt loodud vaadet v_isikudklubid) ja partiide tabelist, kui palju nad keskmiselt valgetega mängides punkte said. Paneme tähele, et materialiseeritud vaate nime ette kirjutasime mv_. See on kokkuleppeline, aga meie oma kursusel hakkame eristama materialiseeritud vaadete nimesid selle eesliitega.

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

DBeaver menüüs materialiseeritud vaadete (Materialized Views) nimekirjas (vajadusel teha Refresh) on ka meie loodud materialiseeritud vaade ning saame analoogselt vaatele ka selle kohta detailsemat infot avanevast menüüst.

Pärime andmeid loodud vaate kaudu.

SELECT * FROM mv_punktevalgetega;

Lisame isikute tabelisse uue isiku.

INSERT INTO isikud (eesnimi, perenimi, isikukood, klubis, sugu) VALUES ('Kaja', 'Kajakas', '48804016028', 61, 'n');

Kontrollime, et kirje on kindlasti tabelis isikud.

SELECT * FROM isikud WHERE eesnimi = 'Kaja' AND perenimi = 'Kajakas';

Loeme taas andmeid kasutades vaadet.

SELECT * FROM mv_punktevalgetega;

Panime tähele, et kuigi kasutasime LEFT JOINi, siis isikud tabelisse lisatud viimane kirje ei kajastu materialiseeritud vaate pealt tehtud päringu tulemuses. Seda seetõttu, et materialiseeritud vaade salvestas esimesel korral tulemuse kõvakettale ja kasutab päringu tulemuste tagastamiseks salvestatud andmeid, mitte ei tee iga kord uut päringut põhitabelisse.

REFRESH MATERIALIZED VIEW mv_punktevalgetega;

Pärime taas vaadet ja näeme, et isikud tabelisse lisatud kirje kajastub nüüd ka vaate tulemuses.

Kuna meil andmebaasis on klubide ja isikute vahel seos 1:n, sest meid huvitas ainult see klubi, kus ta hetkel on, siis materialiseeritud vaate abil saaksime kajastada ka klubidesse kuulumise ajalugu. Näiteks loome materialiseeritud vaate mv_isikudklubid_jaan2023 ja seda me ei muudaks, vaid säilitamegi andmebaasis 2023. aasta algseisu kajastamiseks.

Lisalugemiseks link PostgreSQL dokumentatsioonile

Vaadete kohta üldine enesekontroll on järgmise teema lõpus.

Tabelite loomine ja välisvõti

Vaadete muutmine ja vaate kaudu põhitabeli andmete muutmine