src/Gargantext/Database/Triggers/Nodes.hs

   1 {-|
   2 Module      : Gargantext.Database.Triggers.Nodes
   3 Description : Triggers configuration
   4 Copyright   : (c) CNRS, 2017-Present
   5 License     : AGPL + CECILL v3
   6 Maintainer  : team@gargantext.org
   7 Stability   : experimental
   8 Portability : POSIX
   9
  10 Triggers on Nodes table.
  11
  12 -}
  13
  14 {-# LANGUAGE QuasiQuotes       #-}
  15 {-# LANGUAGE NoImplicitPrelude #-}
  16 {-# LANGUAGE OverloadedStrings #-}
  17 {-# LANGUAGE RankNTypes        #-}
  18
  19 module Gargantext.Database.Triggers.Nodes
  20   where
  21
  22 import Database.PostgreSQL.Simple.SqlQQ (sql)
  23 -- import Database.PostgreSQL.Simple.Types (Values(..), QualifiedIdentifier(..))
  24 import Gargantext.Database.Config (nodeTypeId)
  25 import Gargantext.Database.Types.Node -- (ListId, CorpusId, NodeId)
  26 import Gargantext.Database.Utils (Cmd, execPGSQuery)
  27 import Gargantext.Prelude
  28 import qualified Database.PostgreSQL.Simple as DPS
  29
  30
  31 triggerSearchUpdate :: Cmd err Int64
  32 triggerSearchUpdate = execPGSQuery query ( nodeTypeId NodeDocument
  33                                          , nodeTypeId NodeDocument
  34                                          , nodeTypeId NodeContact
  35                                          )
  36   where
  37     query :: DPS.Query
  38     query = [sql|
  39         CREATE OR REPLACE FUNCTION public.search_update()
  40         RETURNS trigger AS $$
  41         begin
  42           IF new.typename = ? AND new.hyperdata @> '{"language_iso2":"EN"}' THEN
  43             new.search := to_tsvector( 'english' , (new.hyperdata ->> 'title') || ' ' || (new.hyperdata ->> 'abstract'));
  44
  45           ELSIF new.typename = ? AND new.hyperdata @> '{"language_iso2":"FR"}' THEN
  46             new.search := to_tsvector( 'french' , (new.hyperdata ->> 'title') || ' ' || (new.hyperdata ->> 'abstract'));
  47
  48           ELSIF new.typename = ? THEN
  49             new.search := to_tsvector( 'french' , (new.hyperdata ->> 'prenom')
  50                                          || ' ' || (new.hyperdata ->> 'nom')
  51                                          || ' ' || (new.hyperdata ->> 'fonction')
  52                                      );
  53           ELSE
  54             new.search := to_tsvector( 'english' , new.name);
  55           END IF;
  56           return new;
  57         end
  58         $$ LANGUAGE plpgsql;
  59
  60         ALTER FUNCTION public.search_update() OWNER TO gargantua;
  61
  62         CREATE TRIGGER search_update_trigger
  63           BEFORE INSERT OR UPDATE
  64           ON nodes FOR EACH ROW
  65           EXECUTE PROCEDURE search_update();
  66
  67       -- Initialize index with already existing data
  68       UPDATE nodes SET hyperdata = hyperdata;
  69
  70   |]
  71