21 Lisalugemine: Vaated
Siiani oleme päringuid kasutanud vaid selleks, et andmebaasist andmeid kätte saada. Peatükis Lihtsad päringud mainiti ka seda, et päringuid võib alati julgelt jooksutada ning et nende jooksutamine ei saa midagi katki teha. Täpsemalt mõeldi seda, et päringute jooksutamisega ei saa muuta andmebaasi seisu.
Kuigi see on tõsi seni õpitu suhtes, saab päringuid kasutada ka andmebaasi uue sisu loomiseks, milleks on vaated. Tasub siiski meeles pidada, et vaated põhinevad alati andmebaasi tabelites olemasolevatel andmetel.
Vaated on nimega päringud, mis võimaldavad andmebaasis olemasolevaid andmeid uutmoodi esitada. Vaadetele viidatakse ka kui virtuaalsetele tabelitele.
Vaateid luuakse SQL lausete abil, nii nagu tabeleidki. Loomise lauses tuleb esmalt täpsustada vaate nimi ning seejärel päring, mille põhjal vaadet luuakse. Lause jooksutamisel luuakse vaade ehk virtuaalne tabel, milles hoiustatavad veerud ja andmeread põhinevad sisemise päringu tulemusel. Seejärel saab uute päringute koostamisel vaateid tabelitega samaväärselt kasutada.
Vaatame ühte vaate loomise lause näidet:
CREATE VIEW v_riigid_valjalasked AS
SELECT riigid.id AS id, nimi, ROUND(AVG(valjalaske_aasta)) AS keskmine_teose_valjalaske_aasta
FROM riigid, teosed
WHERE teosed.riigi_id = riigid.id
GROUP BY riigid.id, nimi ORDER BY 1 ASC;
Lausesisese päringu tulemuses on näha kõigi riikide ID-sid, nimesid ja nendes riikides loodud teoste keskmist väljalaskeaastat täisarvuni ümardatult. Ühtlasi on päringu tulemused järjestatud riikide ID-de alusel kasvavalt. Saadud päringu tulemus ongi vaate v_riigid_valjalasked sisu.
Kuigi me muudame vaadete loomisel andmebaasi seisu, ei tasu ka vigaseid vaateid luues muretseda. Kui lause on struktuurselt vigane, esineb veateade ning midagi ei loodagi. Kui aga vaade loodi ning see pole selline nagu soovisid, saab seda lihtsasti kustutada. Kui tabeleid kustutati käsuga DROP TABLE <tabeli_nimi>;, siis vaateid kustutatakse käsuga DROP VIEW <vaate_nimi>;. Näiteks saab näites loodud vaate kustutada järgneva käsuga:
DROP VIEW v_riigid_valjalasked;
Vaate loomise lause päringu osas näeme ka uut mitteagregeerivat funktsiooni ROUND. Nagu nimigi viitab, ümardab see arve. Siinkohal on ROUND järel sulgudes vaid üks element, ümardatav arv. See tähendab, et argumendina saadud arv tuleb täisarvuni ümardada. Komaga eraldatult võib sulgudesse lisada ka teise arvu, mis näitab mitme komakohani ümardada.
Katseta esmalt funktsiooni ROUND mingis oma loodud päringus. Seejärel uuri, mis juhtub, kui kasutada seda täisarvude peal nii, et ümardamist nõutakse näiteks 3 komakohani.
Seekord näeme vaate loomise lause päringu osas, et mooduli alguses õpitud märksõna DISTINCT on võimalik kasutada ka funktsiooni COUNT sees erinevate väärtuste kokku lugemiseks. Seega pole DISTINCT tingimata seotud vaid märksõnaga SELECT.
Vaatame, kuidas saab loodud vaateid täpsemalt uurida. Selleks tuleb navigeeruda vaikeskeema alamkausta Views, mis asub samal tasemel alamkaustaga Tables. Peale väikeste erinevuste sarnaneb vaatele topeltkliki tegemisel avanev vaade üldjoontes tabeli vastava vaatega, mida uurisime peatükis Tarkvara installimine ja näidisandmebaasi loomine.
Vaadete kasutamist peetakse osaks heast andmebaasi disainist ning põhjuseid nende loomiseks on mitmeid. Vaatame siinkohal paari asjakohasemat neist.
Esiteks saab vaateid kasutada selleks, et koguda kokku mitmest tabelist tihedalt vaja minevaid andmeid. See tähendab seda, et iga kord, kui on tarvis kasutada andmeid mitmest tabelist, ei ole enam vaja keerukat päringut koostada, vaid andmed saab kätte lihtsa vaate põhjal loodud päringu abil. Vaatame ühte sellist potentsiaalselt kasulikku vaadet.
Vaate saame luua jooksutades järgnevat lauset:
CREATE VIEW v_teosed_naitlemised_riigid AS
SELECT pealkiri,COUNT(naitlemine.*) AS naitlejate_arv, on_film, valjalaske_aasta, vanusepiirang, zanr, nimi, rahvastikuarv, pindala
FROM riigid, teosed, naitlemine
WHERE teosed.riigi_id = riigid.id AND naitlemine.teose_id = teosed.id
GROUP BY 1, 3, 4, 5, 6, 7, 8, 9 ORDER BY 2 DESC, 1 ASC;
Loodud vaates on andmeid kolmest erinevast tabelist – peamiselt kirjeldavad tunnused teoseid ning neid loonud riike, kuid juures on ka teoses näidelnud näitlejate arv.
Nüüd saame loodud vaadet kasutada edasiste päringute loomiseks. Oletame, et me tahame teoste ja neid loonud riikide kohta näha seda, mitu elanikku on iga teoses näidelnud näitleja kohta. Selleks saame kasutada järgnevat päringut:
SELECT pealkiri, nimi, rahvastikuarv / naitlejate_arv AS rahvastikuarvu_naitlejate_suhe
FROM v_teosed_naitlemised_riigid
ORDER BY 3ASC, 1 ASC LIMIT 100;
Näeme, kui lihtsaks tegi eelnevalt loodud vaade meile huvitava päringu loomise.
Teiseks saab vaateid kasutada andmete kasutajatele spetsiifilisel ja/või ilusal kujul kuvamiseks. See tähendab ka seda, et kui andmebaasis tehakse struktuurseid muudatusi – luuakse uusi tabeleid, kustutakse vanasid, luuakse uusi seoseid jne – on vaade kasutaja jaoks ikka endisel kujul. Vaatame ühte sobivat näidet.
Oletame, et me soovime täiendada Soome Wikipedia lehte. Kuna Culture (ee Kultuur), on üks lehe alapeatükkidest ning kuna Soomes loodud filmid ja sarjad on kindlasti osa Soome kultuurist, võime me tahta neid sinna lisada. Selleks ei saa me aga lisada meie algseid 5 andmebaasi tabelit, mis sisaldavad tuhandeid kirjeid.
Seega on sobiv kasutada mingit meie enda loodud ning kujundatud vaadet. Ühe sobiva vaate saame luua järgneva lausega:
CREATE VIEW v_soome_teosed AS
SELECT pealkiri, ARRAY_AGG(isikud.nimi), valjalaske_aasta, kirjeldus
FROM teosed, riigid, naitlemine, isikud
WHERE teosed.riigi_id = riigid.id AND naitlemine.teose_id = teosed.id AND naitlemine.naitleja_id = isikud.id AND riigid.nimi = 'Finland'
GROUP BY 1, 3, 4
ORDER BY 3 DESC, 1 ASC;
Loodud vaates on näha kõigi Soomes loodud teoste pealkirju, neis näidelnud näitlejaid, väljalaske aastaid ning kirjeldusi. Selline vaade annab Soome Wikipedia lehe külastajatele kiire ülevaate seal loodud Netflixi teostest. Andmetest saab muuhulgas välja lugeda seda, kes on Soomes populaarsed näitlejad, millised on ajaperioodid, mil loodi enim teoseid ning ka seda, milliseid teemasid enim käsitletakse.
Vaate loomise käigus õppisime ära ka uue agregeeriva funktsiooni ARRAY_AGG. Nagu näha ühendab see kõik väärtused, siinkohal näitlejate nimed, ühte massiivi. Kuigi loodud vaade pole veel Wikipedia jaoks ehk ideaalsel kujul, on see näidatud kujul juba inimeste jaoks päris hästi loetav.
Nüüd oleme õppinud seda, mis on vaated, kus neid kasutatakse ning kuidas neid ka ise luua.
