src/Gargantext/Database/Admin/Trigger/Nodes.hs

   1 {-|
   2 Module      : Gargantext.Database.Admin.Trigger.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
  16 module Gargantext.Database.Admin.Trigger.Nodes
  17   where
  18
  19 import Database.PostgreSQL.Simple.SqlQQ (sql)
  20 import Gargantext.Database.Admin.Config (nodeTypeId)
  21 import Gargantext.Database.Admin.Types.Node -- (ListId, CorpusId, NodeId)
  22 import Gargantext.Database.Prelude (Cmd, execPGSQuery)
  23 import Gargantext.Prelude
  24 import qualified Database.PostgreSQL.Simple as DPS
  25
  26
  27 triggerSearchUpdate :: Cmd err Int64
  28 triggerSearchUpdate = execPGSQuery query ( nodeTypeId NodeDocument
  29                                          , nodeTypeId NodeDocument
  30                                          , nodeTypeId NodeContact
  31                                          )
  32   where
  33     query :: DPS.Query
  34     query = [sql|
  35         -- DROP TRIGGER search_update_trigger on nodes;
  36         CREATE OR REPLACE FUNCTION public.search_update()
  37         RETURNS trigger AS $$
  38         begin
  39           IF new.typename = ? AND new.hyperdata @> '{"language_iso2":"EN"}' THEN
  40             new.search := to_tsvector( 'english' , (new.hyperdata ->> 'title') || ' ' || (new.hyperdata ->> 'abstract'));
  41
  42           ELSIF new.typename = ? AND new.hyperdata @> '{"language_iso2":"FR"}' THEN
  43             new.search := to_tsvector( 'french' , (new.hyperdata ->> 'title') || ' ' || (new.hyperdata ->> 'abstract'));
  44
  45           ELSIF new.typename = ? THEN
  46             new.search := to_tsvector( 'french' , (new.hyperdata ->> 'prenom')
  47                                          || ' ' || (new.hyperdata ->> 'nom')
  48                                          || ' ' || (new.hyperdata ->> 'fonction')
  49                                      );
  50           ELSE
  51             new.search := to_tsvector( 'english' , (new.hyperdata ->> 'title') || ' ' || (new.hyperdata ->> 'abstract'));
  52           END IF;
  53           return new;
  54         end
  55         $$ LANGUAGE plpgsql;
  56
  57         ALTER FUNCTION public.search_update() OWNER TO gargantua;
  58
  59         CREATE TRIGGER search_update_trigger
  60           BEFORE INSERT OR UPDATE
  61           ON nodes FOR EACH ROW
  62           EXECUTE PROCEDURE search_update();
  63
  64       -- Initialize index with already existing data
  65       UPDATE nodes SET hyperdata = hyperdata;
  66
  67   |]
  68