src/Gargantext/Database/Admin/Trigger/Contexts.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.Contexts
  17   where
  18
  19 import Data.Text (Text)
  20 import Database.PostgreSQL.Simple.SqlQQ (sql)
  21 import Gargantext.Core (HasDBid(..))
  22 import Gargantext.Database.Admin.Types.Node -- (ListId, CorpusId, NodeId)
  23 import Gargantext.Database.Prelude (Cmd, execPGSQuery)
  24 import Gargantext.Prelude
  25 import qualified Database.PostgreSQL.Simple as DPS
  26
  27
  28 triggerSearchUpdate :: HasDBid NodeType => Cmd err Int64
  29 triggerSearchUpdate = execPGSQuery query ( toDBid NodeDocument
  30                                          , toDBid NodeDocument
  31                                          , toDBid NodeContact
  32                                          )
  33   where
  34     query :: DPS.Query
  35     query = [sql|
  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         DROP TRIGGER IF EXISTS search_update_trigger on contexts;
  60         CREATE TRIGGER search_update_trigger
  61           BEFORE INSERT OR UPDATE
  62           ON contexts FOR EACH ROW
  63           EXECUTE PROCEDURE search_update();
  64
  65       -- Initialize index with already existing data
  66       UPDATE contexts SET hyperdata = hyperdata;
  67
  68   |]
  69
  70 type Secret = Text
  71
  72 triggerUpdateHash :: HasDBid NodeType => Secret -> Cmd err Int64
  73 triggerUpdateHash secret = execPGSQuery query ( toDBid NodeDocument
  74                                               , toDBid NodeContact
  75                                               , secret
  76                                               , secret
  77                                               , toDBid NodeDocument
  78                                               , toDBid NodeContact
  79                                               , secret
  80                                               , secret
  81                                               )
  82   where
  83     query :: DPS.Query
  84     query = [sql|
  85
  86       CREATE OR REPLACE FUNCTION hash_insert_nodes()
  87       RETURNS trigger AS $$
  88       BEGIN
  89        IF NEW.hash_id = ''
  90          THEN
  91            IF NEW.typename = ? OR NEW.typename = ?
  92              THEN NEW.hash_id = digest(CONCAT(?, NEW.typename, NEW.name, NEW.parent_id, NEW.hyperdata), 'sha256');
  93              ELSE NEW.hash_id = digest(CONCAT(?, NEW.typename, NEW.name, NEW.id, NEW.hyperdata), 'sha256');
  94            END IF;
  95         END IF;
  96         RETURN NEW;
  97       END
  98       $$ LANGUAGE plpgsql;
  99
 100
 101       CREATE OR REPLACE FUNCTION hash_update_nodes()
 102       RETURNS trigger AS $$
 103       BEGIN
 104         IF NEW.typename = ? OR NEW.typename = ?
 105              THEN NEW.hash_id = digest(CONCAT(?, NEW.typename, NEW.name, NEW.parent_id, NEW.hyperdata), 'sha256');
 106              ELSE NEW.hash_id = digest(CONCAT(?, NEW.typename, NEW.name, NEW.id, NEW.hyperdata), 'sha256');
 107         END IF;
 108         RETURN NEW;
 109       END
 110       $$ LANGUAGE plpgsql;
 111
 112       DROP TRIGGER IF EXISTS nodes_hash_insert ON nodes;
 113       DROP TRIGGER IF EXISTS nodes_hash_update ON nodes;
 114       CREATE TRIGGER nodes_hash_insert BEFORE INSERT ON nodes FOR EACH ROW EXECUTE PROCEDURE hash_insert_nodes();
 115       CREATE TRIGGER nodes_hash_update BEFORE UPDATE ON nodes FOR EACH ROW EXECUTE PROCEDURE hash_update_nodes();
 116
 117       DROP TRIGGER IF EXISTS contexts_hash_insert ON contexts;
 118       DROP TRIGGER IF EXISTS contexts_hash_update ON contexts;
 119       CREATE TRIGGER contexts_hash_insert BEFORE INSERT ON contexts FOR EACH ROW EXECUTE PROCEDURE hash_insert_nodes();
 120       CREATE TRIGGER contexts_hash_update BEFORE UPDATE ON contexts FOR EACH ROW EXECUTE PROCEDURE hash_update_nodes();
 121   |]