How to build a mail search engine using Nitro

By Kashia.

Tags: postgresql regex 0.31 og sql nitro

Part 6 - The search engine

Yeah, now there's a problem, we got data in the database and don't know how to access it. You have the database set up, and played with TSearch2 a while, right? If not, now's your chance.

SQL BANZAI!

BEGIN TRANSACTION;

-- add index columns
ALTER TABLE ogindexedmail ADD COLUMN idxFTI tsvector;

-- populate indexes intially
UPDATE ogindexedmail SET idxfti=to_tsvector(coalesce(email_from,'') || ' ' || 
coalesce(subject,'') || ' ' || coalesce(body,''));

-- add indexes
CREATE INDEX idxFTI_mail ON ogindexedmail USING gist(idxFTI);

-- create update/insert triggers
CREATE TRIGGER tsvectorupdate_ogindexedmail BEFORE UPDATE OR INSERT ON ogindexedmail
FOR EACH ROW EXECUTE PROCEDURE tsearch2(idxFTI, email_from, subject, body);


-------------------------------------------------

CREATE TYPE findmail_t AS (
  email_from text, 
  id text,
  date timestamp, 
  subject text,
  header text, 
  body text,
  oid integer, 
  rank real
);


-------------------------------------------------


CREATE OR REPLACE FUNCTION findogindexedmail(text) 
    RETURNS SETOF findmail_t LANGUAGE sql AS '
SELECT email_from, id, date, subject, header, body, oid, 
        rank(idxfti,q) AS rank
FROM ogindexedmail, to_tsquery($1) AS q
WHERE idxfti @@ q
';

COMMIT;

-- to update everything
VACUUM FULL ANALYZE;

If you have read the previously mentioned resources on the TSearch2 page, that stuff should be all too familiar to you.

First we add a new column ("idxFTI", which we earlier said Og should ignore), after that we populate that column by indexing the sender, subject and body of the email. Right after this we create an index, to speed up lookup on that column, and create a trigger, to update the idxFTI column on all UPDATEs or INSERTs to that table.

The newly created type findmail_t is used by the findogindexedmail('text') function, which returns an unordered set of that new type. The type matches the SELECT .... FROM in the function exactly and returns a nice row when SELECT'ing from it. Try SELECT * FROM findogindexedmail('nitro&og&george') LIMIT 5 once, to see what I mean.

Wait! Did you note the rank field? This wasn't in our Model!

Rest assured, Og can handle this, you just have to trick it.

The last thing it does, is a VACUUM FULL ANALYZE, to let PostgreSQL know, that you did a huge change, and want it to look if it can optimize its search queries somehow.

That done, you have your search engine! You just don't have nice access to it yet.