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 qualified Database.PostgreSQL.Simple as DPS
  21
  22 import Gargantext.Database.Admin.Config (nodeTypeId)
  23 import Gargantext.Database.Admin.Types.Node -- (ListId, CorpusId, NodeId)
  24 import Gargantext.Database.Prelude (Cmd, execPGSQuery)
  25 import Gargantext.Prelude
  26
  27
  28 triggerSearchUpdate :: Cmd err Int64
  29 triggerSearchUpdate = execPGSQuery query ( nodeTypeId NodeDocument
  30                                          , nodeTypeId NodeDocument
  31                                          , nodeTypeId NodeContact
  32                                          )
  33   where
  34     query :: DPS.Query
  35     query = [sql|
  36         -- DROP TRIGGER search_update_trigger on nodes;
  37         CREATE OR REPLACE FUNCTION public.search_update()
  38         RETURNS trigger AS $$
  39         begin
  40           IF new.typename = ? AND new.hyperdata @> '{"language_iso2":"EN"}' THEN
  41             new.search := to_tsvector( 'english' , (new.hyperdata ->> 'title') || ' ' || (new.hyperdata ->> 'abstract'));
  42
  43           ELSIF new.typename = ? AND new.hyperdata @> '{"language_iso2":"FR"}' THEN
  44             new.search := to_tsvector( 'french' , (new.hyperdata ->> 'title') || ' ' || (new.hyperdata ->> 'abstract'));
  45
  46           ELSIF new.typename = ? THEN
  47             new.search := to_tsvector( 'french' , (new.hyperdata ->> 'prenom')
  48                                          || ' ' || (new.hyperdata ->> 'nom')
  49                                          || ' ' || (new.hyperdata ->> 'fonction')
  50                                      );
  51           ELSE
  52             new.search := to_tsvector( 'english' , (new.hyperdata ->> 'title') || ' ' || (new.hyperdata ->> 'abstract'));
  53           END IF;
  54           return new;
  55         end
  56         $$ LANGUAGE plpgsql;
  57
  58         ALTER FUNCTION public.search_update() OWNER TO gargantua;
  59
  60         CREATE TRIGGER search_update_trigger
  61           BEFORE INSERT OR UPDATE
  62           ON nodes FOR EACH ROW
  63           EXECUTE PROCEDURE search_update();
  64
  65       -- Initialize index with already existing data
  66       UPDATE nodes SET hyperdata = hyperdata;
  67
  68   |]
  69
  70 triggerUpdateHash :: Cmd err Int64
  71 triggerUpdateHash = execPGSQuery query ( nodeTypeId NodeDocument
  72                                        , nodeTypeId NodeContact
  73                                        )
  74   where
  75     query :: DPS.Query
  76     query = [sql|
  77
  78       CREATE OR REPLACE FUNCTION hash_update_nodes()
  79       RETURNS trigger AS $$
  80       BEGIN
  81           IF tg_op = 'INSERT' OR tg_op = 'UPDATE' THEN
  82               IF NEW.hash_id = ''
  83                 THEN
  84                   IF NEW.typename = ? OR NEW.typename = ?
  85                     THEN NEW.hash_id = digest(CONCAT(NEW.parent_id, NEW.hyperdata), 'sha256');
  86                     ELSE NEW.hash_id = digest(CONCAT(NEW.id, NEW.hyperdata), 'sha256');
  87                 END IF;
  88               END IF;
  89               RETURN NEW;
  90           END IF;
  91       END
  92       $$ LANGUAGE plpgsql;
  93
  94       CREATE TRIGGER some_table_hash_update
  95       BEFORE INSERT OR UPDATE ON nodes FOR EACH ROW EXECUTE PROCEDURE hash_update_nodes();
  96   |]
  97
  98