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 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         -- DROP TRIGGER search_update_trigger on contexts;
  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 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
 113       CREATE TRIGGER nodes_hash_insert BEFORE INSERT ON nodes FOR EACH ROW EXECUTE PROCEDURE hash_insert_nodes();
 114       CREATE TRIGGER nodes_hash_update BEFORE UPDATE ON nodes FOR EACH ROW EXECUTE PROCEDURE hash_update_nodes();
 115
 116       CREATE TRIGGER contexts_hash_insert BEFORE INSERT ON contexts FOR EACH ROW EXECUTE PROCEDURE hash_insert_nodes();
 117       CREATE TRIGGER contexts_hash_update BEFORE UPDATE ON nodes FOR EACH ROW EXECUTE PROCEDURE hash_update_nodes();
 118
 119   |]
 120
 121