Indeksite ja trigerite loomine
Indeksi loomine
Nagu loengus rääkisime, loome indekseid eelkõige neile atribuutidele, mida sageli kasutame päringu WHERE osas. Indeksi loomine:
CREATE INDEX indeksi_nimi ON tabeli_nimi (atribuudid);
Atribuudid on vastava tabeli väli või väljad, millele indeks luuakse. Neid võib olla üks (ühe atribuudiga indeks) või mitu (mitme atribuudiga indeks).
Loome indeksi isikute tabelile võttes indeksiväljaks isikukoodi:
CREATE INDEX ix_isikukood ON Isikud (isikukood);
Meeldetuletus loengust, indekseid luukse veergudele, millel on palju unikaalseid väärtusi (meil isikukood on selline) ja 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.
Olemasolevad indeksid ja indeksi detailide vaatamine DBeaver rakenduses
Olemasolevaid indekseid näeb DBeaver rakenduses Indexes menüüst.
Esmalt uuendame indeksite nimekirja valides parema hiireklahviga Indexes menüüst Refresh (Joonis).
Olemasoleva indeksi loomise SQL käsku näeb, kui indeksi peal parempoolse hiireklahviga valida ”Generate SQL” ja uues avanenud menüüs “DDL”, mille tulemusena avaneb uues aknas valitud indeksi loomise SQL lause (Joonis).
Indeksid on seotud tabeliga. Olemasoleva tabeli juurde kuuluvaid indekseid näeb DBeaver rakenduses vastava tabeli alammenüüst Indexes (Joonis)
Uuendades DBeaver vaates indeksite nimekirja näeme, et loodud indeks on ilmunud teiste indeksite hulka. Primaarvõtmed lähevad automaatselt indeksiteks.
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 kahe atribuudiga indeksi:
CREATE INDEX ix_nimi ON Isikud (perenimi, eesnimi);
PostgreSQL'is saab täpsustada, kas indeks on unikaalsele väljale loodud (indeksi loomise lauses täpsustus UNIQUE). 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: NULLS [NOT] DISTINCT vastavalt sellele, kas tühje väärtusi eristada või mitte.
CREATE INDEX indeksi_nimi ON tabeli_nimi USING indeksi_tüüp (atribuudid);
Indeksi tüüp määrab ära algoritmi, mida index kasutab. PostgreSql versioon 14 kasutab vaikimisi (kui indeksi tüüp ei ole valitud) tüübks B-Tree. Rohkem informatsiooni erinevate indeksi tüüpide kohta saab lugeda PostgreSql dokumentatsioonist aadressil https://www.postgresql.org/docs/14/indexes-types.html.
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.
CREATE INDEX ix_partiisid ON mv_partiide_arv_valgetega (partiisid_valgetega);
NB! Indekseid ei saa luua kui keegi teine kasutab tabelit!
Indeksit kustutatakse
DROP INDEX <indeksi_nimi>;
Link PostgreSQL dokumentatsioonile
Triger
Funktsioon, mida triger käivitab
Enne kui trigeri loomise juurde saame minna tuleb luua funktsioon, mida triger saaks käivitada.
Triger eeldab teistsuguseid funktsioone võrreldes nendega, milledega me funktsioonide loomise peatükis kokku puutusime.
Trigeri poolt käivitatav funktsioon peab olema loodud PLPGSQL keeles ja tulemus peab olema ilma argumentideta triger tüüpi.
PLPGSQL keel omakorda seab teatud formaadi funktsiooni sisus osas.
Funktsiooni sisu, peab tagastama kas NULL või rea struktuuri, millega triger käivitatakse või käivitati.
Struktureeritud tagastamise puhul on valida, kas tagastame uued väärtused (peale trigeri käivitamist) - RETURN NEW; või vanad väärtused (mis olid antud real enne trigeri käivitamist) - RETURN OLD;.
CREATE OR REPLACE FUNCTION name () RETURNS trigger AS $$ BEGIN function_body; RETURN (NULL | NEW| OLD); END; $$ LANGUAGE PLPGSQL;
Loome funktsiooni, mida triger hakkab välja kutsuma.
CREATE OR REPLACE FUNCTION exec_proc() RETURNS trigger AS $$ BEGIN UPDATE isikud SET eesnimi = initcap(eesnimi), perenimi = initcap(perenimi); return NULL; END; $$ LANGUAGE PLPGSQL;
Trigeri loomine
CREATE [ OR REPLACE ] [ CONSTRAINT ] TRIGGER name { BEFORE | AFTER | INSTEAD OF } { event }
ON table_name]
[ FOR [ EACH ] { ROW | STATEMENT } ]
[ WHEN ( condition ) ]
EXECUTE { FUNCTION } function_name ( arguments )
name - Trigerile antav nimi.
BEFORE Triger käivitatakse enne INSERT, UPDATE, DELETE, TRUNCATE sündmust (event).
AFTER Triger käivitatakse pärast INSERT, UPDATE, DELETE, TRUNCATE sündmust (event).
INSTEAD OF Triger käivitatakse INSERT, UPDATE, DELETE, TRUNCATE sündmuste (event) asemel. Näiteks INSTEAD OF INSERT ON isikud … käivitatakse triger, kui tabelisse isikud lisatakse kirjeid, aga lisamise asemel käivitatakse hoopis antud trigeri poolt käivitatav funktsioon.
event - tegevus (INSERT, UPDTE, DELETE, TRUNCATE), mis trigeri käivitab. SELECT sündmuse jaoks ei saa trigerit luua.
table_name - Tabel, kus mingi tegevuse (INSERT, UPDATE, DELETE, TRUNCATE) korral triger käivitatakse.
FOR EACH ROW - Triger käivitatakse iga tabeli reale, millega triger seotud on. Näiteks, kui tabelis isikud on 100 rida, siis võib juhtuda (olenevalt tingimustest, et trigerit käivitatatakse 100 korda). Kasutatakse kui on vaja teha mingeid tegevusi teatud ridadel. Näiteks iga kord, kui kustutatakse isikute tabelist kirje, siis luuakse audit kirjed kustutatavate ridade kohta audit tabelisse.
FOR EACH STATEMENT - Triger käivitatakse ainult ainult üks kord. Kui isikute tabelis on 100 kirjet, siis triger käivitatakse ainult korra. Olenevalt trigeri poolt käivitatava funktsiooniga võib tabeli iga rida olla mõjutatud. Kasutatakse üldistema tegevuste jaoks. Näiteks iga öösel mingist tabelist vanemate kui X päeva vanuste logide kustutamiseks.
WHEN condition - Lisatingimus trigeri käivitamiseks või mittekäivitamiseks.
Loome trigeri, mis käivitub iga kord peale (AFTER) isikud tabelisse rea lisamist (INSERT).
CREATE TRIGGER check_isikud_upper AFTER INSERT ON isikud EXECUTE function exec_proc();
Lisame tabelisse isikud uue kirje. Paneme tähele, et ees- ja perenime esitähed on väiketähed.
INSERT INTO isikud (eesnimi, perenimi, isikukood) VALUES ('sammal', 'habe', '37303140311');
Kontrollime isikute tabelist ja märkame, et loodud kirjel on suured algustähed.
Olemasolevate trigerite nimekiri DBeaver rakenduses
Loodud trigerid asuvad DBeaver rakenduses “Function” menüü all (Joonis).
Trigeri kustutamine
Trigeri kustutamisel peab lisaks trigeri nimele viitama ka tabelile, millega triger on seotud.
DROP TRIGGER check_isikud_upper ON isikud ;
Enesekontroll
On tarvis peale isikud tabelisse kirje lisamisel automaatselt käivitada funktsioon check_idcode_func(). Loo vajalik trigger. Triggeri nimeks paneme check_idcode_afrer_insert