Kodulehed
Valitud koduleht: [368] - Andmebaasid I (IDU0220) (kevad 2018)
Üldist
Materjalid
Vaated materjalidele
Tudeng
Abi
Mitmesugust
HELPDESK - teemad:

                         
Erki Eessaar:
Kuidas Mauruse otsing tĂ€psemalt töötab? Millist tuge pakkus selle realiseerimiseks andmebaasisĂŒsteem, mida Maurus kasutab?
Vastus: Mauruse kasutajad soovivad sageli, et seal oleks otsingu funktsionaalsus. Lisasin Maurusesse selle funktsionaalsuse. Otsing on vĂ”imalik kodulehe piires. Leiate vĂ”imaluse seda teha iga aine kodulehelt alajaotusest Üldist=>Otsing. Otsing toimub ĂŒle materjalide metaandmete, teadete, helpdeski kĂŒsimuste/vastuste ning viidete. Otsingu tulemused on ĂŒhel lehel nende kategooriate kaupa grupeerituna. Materjale, teateid ja helpdeski 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 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.

Avalik_teade(avalik_teade_id, pealkiri, sisu)
PrimaarvÔti(avalik_teade_id)

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.

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. Kahjuks ei toeta kasutatav PostgreSQL versioon vĂ”imalust mÀÀrata, et triger peaks kĂ€ivituma ainult siis, kui andmeid muudetakse (UPDATE) mingis kindlas veerus. Kahjuks ei ole tĂ€istektiotsingutel eesti keele tuge, tĂ€nu millele oskaks andmebaasisĂŒsteem otsingu tegemisel arvestada keele eripĂ€radega. Seega kasutan inglise keele (pg_catalog.english) reegleid.

CREATE TRIGGER teade_tsv_iu BEFORE INSERT OR UPDATE
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;

Veerus tsv on nĂŒĂŒd andmed nagu: 26,231,261 'segamini':253 'sell':24 'sellel':100 'sellest':91 'sellis':132 '

Loon veerule tsv otsingu kiirendamiseks indeksi.

CREATE INDEX teade_tsv_idx ON Teade USING gin(tsv);

Muudan veeru tsv kohustuslikuks.

ALTER TABLE Teade ALTER COLUMN tsv SET NOT NULL;

JÀrgnev pÀring leiab tabelist Avalik_teade sellised teated, mis sisaldavad sÔna "SQL". 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..

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'

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('SQL') AS q
WHERE tsv @@ q

ORDER BY rank DESC
LIMIT 10) AS foo;

Kommentaarid
Sellele küsimusele/vastusele pole kommentaare



1.Erki Eessaar:
2.Erki Eessaar:
3.Erki Eessaar:
4.Erki Eessaar:
5.Erki Eessaar:
6.Erki Eessaar:
7.Erki Eessaar:
8.AnonĂŒĂŒmne:
9.Erki Eessaar:
10.Erki Eessaar:
11.Erki Eessaar:
12.Erki Eessaar:
13.Erki Eessaar:
14.Erki Eessaar:
15.AnonĂŒĂŒmne:
16.Erki Eessaar:
17.Erki Eessaar:
18.Erki Eessaar:
19.Erki Eessaar:
20.Erki Eessaar:
21.AnonĂŒĂŒmne:
22.Erki Eessaar:
23.Erki Eessaar: