Andmebaaside ajaveeb

Mauruse kasutajad soovisid sageli, et seal oleks otsingu funktsionaalsus. Lisasin Maurusesse selle funktsionaalsuse. Otsing on võimalik kodulehe piires. Leiate võimaluse seda teha iga aine kodulehe paremast ülaservast.

Otsing toimub üle aine sisu kirjelduse, materjalide metaandmete, tegevuskava, teadete (v.a isiklikud teated), kasutajatoe küsimuste/vastuste, viidete, tähaegade, ülesannete (kuid mitte nende vastused) ja vastuvõtuaegade. Otsingu tulemused on ühel lehel nende kategooriate kaupa grupeerituna. Materjale, teateid ja kasutajatoe küsimusi/vastuseid on otsingu tulemuses igaühte kuni 10. Viiteid on tulemuses kuni 20. Materjalide puhul ei toimu otsing mitte failide sisu, vaid materjale kirjeldavate andmete (metaandmete) põhjal.

Kuna Maurus kasutab PostgreSQL andmebaasisüsteemi, siis tegi PostgreSQLi pakutav täisteksti otsingu funktsionaalsus, mille kohta saab näiteks lugeda SIIT ja SIIT, selle funktsionaalsuse realiseerimise lihtsamaks.

Järgnevalt esitan koodinäite.

Näiteks oletame, et tahan lisada teadete otsimise võimaluse ning et andmebaasis on tabel Avalik_teade, üle mille veergude pealkiri ja sisu peaks otsing toimuma.

CREATE TABLE Avalik_teade(avalik_teade_id SERIAL,
pealkiri TEXT NOT NULL,
sisu TEXT NOT NULL,
CONSTRAINT pk_avalik_teade PRIMARY KEY (avalik_teade_id));

INSERT INTO Avalik_teade (pealkiri, sisu) VALUES ('Otsinguvõimalus', 'Kodulehel saab nüüd teha otsingut');

Lisan tabelisse veeru tsv, mis hakkab iga teate korral sisaldama spetsiaalset pealkirja ning sisu kokkuvõtet ja on ühtlasi aluseks nende alusel otsingute tegemisele. See veerg on tüüpi tsvector. See on spetsiifiline PostgreSQLi andmetüüp. Kahjuks ei ole täistektiotsingutel eesti keele tuge, tänu millele oskaks andmebaasisüsteem otsingu tegemisel arvestada keele eripäradega. Seega kasutan inglise keele (english) reegleid. Tabelisse lisatud rea korral oleks veerus tsv väärtus: 'kodulehel':2 'nüüd':4 'otsingut':6 'otsinguvõimalus':1 'saab':3 'teha':5

Andmete automaatseks uuendamiseks tekstiotsingu veerus on kaks võimalust.

Võimalus 1. Lisan tabelisse kohustusliku arvutatava veeru, mille väärtus on salvestatud kettale (stored).

ALTER TABLE Avalik_teade
ADD COLUMN tsv tsvector
GENERATED ALWAYS AS (to_tsvector('english', coalesce(pealkiri, '') || ' ' || coalesce(sisu, ''))) STORED NOT NULL;

coalesce funktsioon tagastab vasakult lugedes esimese argumendi, mis ei ole NULL. coalesce(pealkiri,'') avaldis tähendab, et kui pealkiri on olemas (pole NULL) tagastab funktsioon pealkirja, vastasel juhul teiseks argumendiks oleva tühja stringi. Tühi string ja NULL on kaks ise asja - tühi string on väärtus, kuid NULL on marker, mis tähistab väärtuse puudumist. coalesce läheb vaja, sest PostgreSQLi stringide ühendamise (konkatenatsiooni) operaator käitub juhul kui üks argument on NULL järgnevalt: 'tekst' || NULL => NULL. Juhuks kui teates pealkiri või sisu puudub tuleb enne nende ühendamist kontrollida kas üks nendest on NULL ja vajadusel asendada see tühja stringiga: 'tekst' || '' => 'tekst'

Võimalus 2. Lisan tabelisse uue veeru ning lisan tabelile trigeri, mis tabelisse rea lisamisel või olemasolevas reas pealkirja või sisu uuendamisel, arvutab tsvector väärtuse ja salvestab sellesse veergu.

ALTER TABLE Avalik_teade ADD COLUMN tsv tsvector;

Loon tabeliga seotud trigeri, mis andmete tabelisse lisamisel või muutmisel värskendab veergudes pealkiri ja sisu olevate andmete põhjal veerus tsv olevaid andmeid. Määran, et ridade uuendamise (UPDATE) korral peaks triger käivituma ainult siis, kui andmeid uuendatakse (UPDATE) mingis kindlas veerus.

CREATE TRIGGER teade_tsv_iu BEFORE INSERT OR UPDATE OF pealkiri, sisu
ON Avalik_teade FOR EACH ROW EXECUTE PROCEDURE
tsvector_update_trigger(tsv, 'pg_catalog.english', pealkiri, sisu);

Teen andmemuudatuse, mis tänu eelnevale trigerile tingib andmete lisamise veergu tsv.

UPDATE Avalik_teade SET pealkiri=pealkiri;

Muudan veeru tsv kohustuslikuks.

ALTER TABLE Avalik_teade ALTER COLUMN tsv SET NOT NULL;

Loon veerule tsv otsingu kiirendamiseks indeksi, ükskõik kas kasutan võimalust 1 või 2.

CREATE INDEX teade_tsv_idx ON Avalik_teade USING gin(tsv);

Järgnev päring leiab tabelist Avalik_teade sellised teated, mis sisaldavad sõna "otsingut". Päring väljastab teate identifikaatori, pealkirja, sisu ning pealkirja ja sisu põhjal moodustatud kokkuvõtte, kus on otsisõna esile tõstetud. Päringu tulemus on sorteeritud tulemuse relevantsuse hinnangu alusel kahanevalt. Päringu tulemuses on maksimaalselt 10 rida..

SELECT avalik_teade_id, pealkiri, sisu, ts_headline(coalesce(pealkiri,'') || ' ' || coalesce(sisu,''), q) AS fragment
FROM (SELECT avalik_teade_id, pealkiri, sisu, q, ts_rank_cd(tsv,q) AS rank
FROM Avalik_teade, to_tsquery('otsingut') AS q WHERE tsv @@ q
ORDER BY rank DESC
FETCH FIRST 10 ROWS ONLY) AS foo;

Hinda postitust:

Keskmine hinne : 5.0