Andmebaasid 2021/22 kevad

Indeksite ja trigerite loomine

Indeksid

Nagu loengus rääkisime, loome indekseid eelkõige neile atribuutidele, mida sageli kasutame päringu WHERE osas. Tänapäevased ABJSid võimaldavad kasutada ka süsteemide enda vahendeid nagu Index Consultant SQL Anywhere'is (Tools → Index Consultant) Indeksi loomine:

CREATE [UNIQUE] [CLUSTERED] INDEX <indeksi_nimi> 
ON <tabeli_nimi> ({veerud ja suunad});

Siin suunad on päringutest tuttavad ASC ja DESC (mittekahanevalt ja mittekasvavalt). Kui suund jätta määramata, siis vaikimisi võetakse ASC. Loome indeksi isikute tabelile võttes indeksiväljaks isikukoodi:

CREATE INDEX ix_isikukood 
ON Isikud (isikukood DESC);

Tõsi, meie tabelis sellest indeksist mitte mingit kasu poleks, sest meil isikute tabelis pole isikukoode sisestatud. Meeldetuletus loengust, indekseid ei looda veergudele, millel on palju NULL väärtusi.

Taas teeme kokkuleppe nimede osas. Oma aines indekseid luues kirjutame indeksi nime ette ix_. Ja veelkord ka meeldetuletus, et see pole üldlevinud, igas töörühmas on oma reeglid ja kokkulepped.

Nagu loengus rääkisime, siis kui WHERE osas kasutatakse sageli mitme tunnuse kombinatsiooni, on otstarbekas luua indeks, mis kasutaks mitut atribuuti ehk siis mitme välja indeks. Näiteks meie male andmebaasis on sageli vaja isikuid koos ees- ja perenimega. Loome selleks indeksi:

CREATE INDEX ix_nimi 
ON Isikud (perenimi ASC, eesnimi ASC);

Kui veergude suund pole määratud, siis vaikimisi võetakse mittekahanev suund, siis oleks võinud kirjutada ka

CREATE INDEX ix_nimi 
ON Isikud (perenimi, eesnimi);

SQL Anywhere’s saab täpsustada, kas indeks on unikaalsele väljale loodud (indeksi loomise lauses täpsustus UNIQUE) ja kas on tegemist kobarindeksiga (indeksi loomise lauses CLUSTERED). Need kaks täpsustust aga võib ka jätta lisamata nagu eelmistes näidetes oli.

Kui valida unikaalne indeks UNIQUE, siis peab olema tabeli definitsioonis vastav atribuut unikaalsena defineeritud. Kui aga see unikaalne atribuut sisaldab ka tühje väätusi (NULL väätusi), siis peaks lisama veel indeksi loomise lause lõppu klausli: WITH NULLS NOT DISTINCT.

Indekseid saab luua ka materialiseeritud vaadetele, kuid mitte tavalisetele vaadetele. Loome indeksi neljandas kodutöös loodud materialiseeritud vaatele mv_partiide_arv_valgetega, kus indeksi väljaks on partiisid_valgetega ja järjestus oleks mittekasvav.

CREATE INDEX ix_partiisid 
ON mv_partiide_arv_valgetega (partiisid_valgetega DESC);

NB! Indekseid ei saa luua kui keegi teine kasutab tabelit!

Indeksit kustutatakse

DROP INDEX <indeksi_nimi>;

Trigerid

Nagu loengus rääkisime, siis trigerid pakuvad INSERT, UPDATE ja DELETE käskudega tehtavaid tegevuste korral lisategevusi, tagamaks, et andmebaasi nõuded on kõik endiselt täidetud, andmed on terviklikud ja ka võivad väljastada teateid, kui need tegevused sellel kasutajagrupil lubatud pole.

Trigeri loomine SQL Anywhere'is:

CREATE TRIGGER <trigeri_nimi> <aeg> <sündmus> [ORDER <jrk>] ON <tabeli_nimi>
[REFERENCING [OLD as <vana>] [NEW as <uus>]]
FOR EACH <ühik>
[WHEN (<tingimus>)]
    BEGIN
    ...
    END;

Vaatame neid lause osi lähemalt:

• aeg võib olla BEFORE, AFTER või INSTEAD OF vastavalt sellele, kas triger käivitatakse enne sündmust, pärast sündmust või siis sündmuse asemel. Oma kursusel vaatame neist kahte esimest.
• sündmuseks on eelpoolmainitud INSERT, UPDATE või DELETE ja võib kasutada ka nende kombinatsioone, näiteks INSERT, UPDATE. Nii nimi, aeg kui ka sündmus peavad trigeri loomise lauses kindlasti olema nagu ka tabeli nimi, millele trigerit luuakse.
• ORDER ei pea lauses olema ja see sätestatakse vaid siis kui ühele tabelile on loodud erinevaid trigereid. Sellisel juhul määratakse, mitmendada antud triger käivitatakse.
• Kohustuslik ei ole ka osa [REFERENCING [OLD as <vana>] [NEW as <uus>]]. Neid selgitame lähemalt näidetes.
• FOR EACH <ühik> osa on samuti kohustuslik ning ühikuks võib olla ROW või STATEMENT. FOR EACH ROW tähendab, et WHEN… osas defineeritud kontroll toimub iga rea korral, mida siis kas lisatakse, muudetakse või kustutatakse. FOR EACH STATEMENT puhul kontrollitakse lauset tervikuna.

Vaatame trigeri loomise näiteid. Loome esmalt trigeri, mis klubi kustutamisel kustutab ära ka klubi isikud.

CREATE TRIGGER tg_kustuta_klubi_isikutega BEFORE 
DELETE ON Klubid
REFERENCING OLD AS vana
FOR EACH ROW
WHEN (EXISTS (SELECT * FROM Isikud WHERE Isikud.klubis = vana.id))
    BEGIN
        DELETE FROM Isikud WHERE Isikud.klubis = vana.id;  
    END;

Lepime kokku, et trigeri nime ees on tg_.

Vaatame seda näidet lähemalt.

• Esimene rida ütleb, mis nimega trigeri loome ja et tegemist on BEFORE trigeriga ehk siis enne sündmust käivitub.
• Teisel real on kirjas, mis sündmuse korral mis tabelis triger käivitatakse ehk siis, et kui Klubid tabelis kustutatakse, siis käivitub triger.
• REFERENCING OLD AS vana sätestab, et paneme reatüüpi muutujasse kustutatavad klubi väljade väärtused (tõstame need tabelist muutujasse, kuid ei kustuta püsivalt).
• FOR EACH ROW määrab, et kontrollitakse iga kustutatavat rida.
• Tingimuses sätestame, et vaadatakse neid isikuid, kelle atribuudi klubis väärtuseks on vastava klubi id, mille oleme pannud viitesse.
• Tegevus, mis käivitub on, et kõik isikud, kelle klubis väljas on kustutatava klubi id väärtus, kustutatakse.

Kustutame klubi Laudnikud, et kontrollida, kas selle mängijad ka kustutatakse:

DELETE Klubid WHERE nimi='Laudnikud';

Saame veateate

Miks? Nagu loengus rääkisime, siis ka välisvõtmed on trigerid ja need käivitatakse esimesena. Meil on partiide ja isikute vahel loodud välisvõti, mille tingimuseks panime, et isikut ei saa kustutada, kui tal on partiisid.

Ühe ja sama trigeri võib kirja panna nii BEFORE kui ka AFTER trigerina. Vaatame nende käitumise erisusi luues trigeri tabelile Turniirid, mis käivituks uue turniiri lisamisel või olemasoleva turniiri muutmisel. Triger kontrolliks, kas lõppkuupäev on ikka hilisem kui alguskuupäev. Kui pole, siis võrdsustab need kuupäevad.

Loome selleks esmalt BEFORE trigeri:

CREATE TRIGGER tg_turniiriaeg1 BEFORE
INSERT, UPDATE ON turniirid
REFERENCING NEW as uus
FOR EACH ROW
WHEN (uus.loppkuupaev < uus.alguskuupaev)
   BEGIN
       SET uus.loppkuupaev = uus.alguskuupaev;
   END;

Siin me kontrollisime, kas lõpukuupäev on väiksem kui alguskuupäev ning kui nii on, siis triger võrdsustas need kuupäevad. BEFORE trigerina enne lisamist toimub võrdsustamine, seega kui lisatakse võttes kirje muutujast uus, siis seal on juba need kaks kuupäeva võrdsustatud ja tabelisse turniirid lisatakse juba võrdsustatud kuupäevad.

Kuigi me panime oma turniiride tabelile peale kitsenduse ajakontroll, mis kontrollis, et alguskuupäev poleks hilisem kui lõppkuupäev, siis BEFORE trigeri puhul see ei sega ja triger töötab, sest BEFORE triger teeb muudatuse enne sündmust ehk siis enne andmete tabelisse sisestamist.

Kontrollime trigeri toimimist. Lisame turniiri Prooviturniir, mille alguskuupäevaks paneme 01.03.2022 ja lõppkuupäevaks 01.01.2022.

INSERT INTO Turniirid (Nimi, alguskuupaev, loppkuupaev) 
VALUES ('Prooviturniir', '2022-03-01', '2022-01-01')

Vaatame turniiride nimekirja, seal on

ehk siis triger on võrdsustanud need kuupäevad.

Sarnaselt käituv triger AFTER trigerina oleks:

CREATE TRIGGER tg_turniiriaeg2 AFTER
INSERT, UPDATE ON turniirid
REFERENCING NEW AS uus
FOR EACH ROW
WHEN (uus.loppkuupaev < uus.alguskuupaev)
   BEGIN
       UPDATE turniirid SET loppkuupaev = alguskuupaev
       WHERE id = uus.id;
   END;

Siin toimub sama kontroll, kuid kuna kasutame AFTER trigerit, siis see tähendab, et turniiride tabelisse on juba vastav kirje lisatud. Triger pani selle aga ka muutujasse uus. Nüüd võrdsustatakse turniiride tabelis sellise turniiri kuupäevad, mille id on võrdne selle id’ga, mis on muutujas uus. Kuigi lõime turniiride tabelile 2 trigerit, siis me ei määranud nende täitmise järjekorda. Siin polnud see vajalik, sest alati BEFORE triger on enne kui AFTER triger.

NB! Trigeri puhul alati mõelda, kas saab ise otsustada, mis on õige väärtus mida sisestada tuleks. Näiteks kui Prooviturniir oleks kestnud 1. jaanuarist 3. jaanuarini (sisestamisel tehti viga vahetades kuu ja kuupäeva), siis muutis meie triger kuupäevad täiesti valeks. Et seda korrigeerida kustutame kõigepealt loodud BEFORE trigeri ja muudame AFTER trigerit pannes selle järjekorras teiseks.

Trigeri kustutamine

DROP TRIGGER <trigeri_nimi> 

Seega kirjutame

DROP TRIGGER tg_turniiriaeg1;

Nüüd aga hakkab meid turniiride tabeli kitsendus ajakontroll segama. AFTER trigeri puhul toimuks muutmine pärast sündmust ja kuna meil on turniiride tabelil kitsendus, mis ei luba sellist lisamist, peame esmalt sellise kitsenduse kustutama. Vastasel juhul nagunii ei lubataks meil sellist turniiri sisestada (AFTER trigeri aga tahaks enne sisestada ja siis alles muuta) ning saaksime sellise veateate (võite katsetada lisades näiteks samade kuupäevadega Prooviturniir1):

Selleks kustutame kitsenduse ajakontroll:

ALTER TABLE turniirid DROP CONSTRAINT ajakontroll; 

Nüüd muudame trigerit tg_turniiriaeg2. Trigeri muutmiseks tuleb trigeri loomise lause sisse kirjutada OR REPLACE.

CREATE OR REPLACE TRIGGER tg_turniiriaeg2 AFTER
INSERT, UPDATE ORDER 2 ON turniirid
REFERENCING NEW AS uus
FOR EACH ROW
WHEN (uus.loppkuupaev < uus.alguskuupaev)
   BEGIN
       UPDATE turniirid SET loppkuupaev = alguskuupaev
       WHERE id = uus.id;
   END;

Nüüd loome uue trigeri, mis on ka AFTER triger, mis ei lase lisada sellist kuupäevade kombinatsiooni ja väljastab kliendile teate ning mis käivitub esimese trigerina.

CREATE TRIGGER tg_turniiriaeg1 AFTER
INSERT, UPDATE ORDER 1 ON turniirid
REFERENCING NEW AS uus
FOR EACH ROW
WHEN (uus.loppkuupaev < uus.alguskuupaev)
    BEGIN
        MESSAGE 'Viga kuupäevades! Turniiri lõppkuupäev ei saa olla varasem kui alguskuupäev.' TO CLIENT;
        DELETE turniirid 
        WHERE id = uus.id;
    END;

Kontrollimiseks püüame lisada turniiri Prooviturniir2.

INSERT INTO Turniirid (Nimi, alguskuupaev, loppkuupaev) 
VALUES ('Prooviturniir2', '2022-03-01', '2022-01-01');

Turniiride tabelisse ei lisandu kirjet ja History aknast leiate teate kliendile:

Kui sisestasite katsetamiseks Prooviturniire, siis kustutage need turniiride tabelist:

DELETE turniirid 
WHERE nimi LIKE 'Proovi%';