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