src/Gargantext/Database/Init.hs

   1 {-|
   2 Module      : Gargantext.Database.Init
   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 Ngrams by node enable contextual metrics.
  11
  12 -}
  13
  14 {-# LANGUAGE QuasiQuotes       #-}
  15 {-# LANGUAGE NoImplicitPrelude #-}
  16 {-# LANGUAGE OverloadedStrings #-}
  17 {-# LANGUAGE RankNTypes        #-}
  18
  19 module Gargantext.Database.Init
  20   where
  21
  22 import Database.PostgreSQL.Simple.SqlQQ (sql)
  23 -- import Database.PostgreSQL.Simple.Types (Values(..), QualifiedIdentifier(..))
  24 import Gargantext.Database.Config (nodeTypeId)
  25 import Gargantext.Database.Types.Node -- (ListId, CorpusId, NodeId)
  26 import Gargantext.Database.Utils (Cmd, execPGSQuery)
  27 import Gargantext.Prelude
  28 import qualified Database.PostgreSQL.Simple as DPS
  29
  30 ------------------------------------------------------------------------
  31 type MasterListId = ListId
  32
  33 initTriggers :: MasterListId -> Cmd err [Int64]
  34 initTriggers lId = do
  35   t1 <- triggerCountInsert
  36   t2 <- triggerDeleteCount lId
  37   t3 <- triggerInsertCount lId
  38   t4 <- triggerUpdateAdd   lId
  39   t5 <- triggerUpdateDel   lId
  40   pure [t1,t2,t3,t4,t5]
  41
  42 triggerCountInsert :: Cmd err Int64
  43 triggerCountInsert = execPGSQuery query (nodeTypeId NodeDocument, nodeTypeId NodeList)
  44   where
  45     query :: DPS.Query
  46     query = [sql|
  47           CREATE OR REPLACE FUNCTION set_ngrams_global_count() RETURNS trigger AS $$
  48           BEGIN
  49             IF pg_trigger_depth() <> 1 THEN
  50               RETURN NEW;
  51             END IF;
  52             IF TG_OP = 'INSERT' THEN
  53                 INSERT INTO node_node_ngrams (node1_id, node2_id, ngrams_id, ngrams_type, weight)
  54                 select n.parent_id, n.id, new1.ngrams_id, new1.ngrams_type, count(*) from NEW as new1
  55                     INNER JOIN nodes n  ON n.id = new1.node1_id
  56                     INNER JOIN nodes n2 ON n2.id = new1.node2_id
  57                     WHERE n2.typename = ?  -- not mandatory
  58                       AND n.typename = ?   -- not mandatory
  59                       AND n.parent_id <> n2.id -- not mandatory
  60                     GROUP BY n.parent_id, n.id, new1.ngrams_id, new1.ngrams_type
  61                 ON CONFLICT (node1_id, node2_id, ngrams_id, ngrams_type)
  62                    DO UPDATE set weight = node_node_ngrams.weight + excluded.weight
  63                    ;
  64             END IF;
  65
  66             RETURN NULL;
  67           END
  68           $$ LANGUAGE plpgsql;
  69
  70           -- DROP trigger trigger_count_insert on node_node_ngrams;
  71
  72           CREATE TRIGGER trigger_count_insert AFTER INSERT on node_node_ngrams
  73           REFERENCING NEW TABLE AS NEW
  74           FOR EACH STATEMENT
  75           EXECUTE PROCEDURE set_ngrams_global_count();
  76    |]
  77
  78 -- Triggers NodesNodes
  79 triggerDeleteCount :: MasterListId -> Cmd err Int64
  80 triggerDeleteCount lId = execPGSQuery query (lId, nodeTypeId NodeList)
  81   where
  82     query :: DPS.Query
  83     query = [sql|
  84       CREATE OR REPLACE FUNCTION set_delete_count() RETURNS trigger AS $$
  85       BEGIN
  86             UPDATE node_node_ngrams SET weight = weight - d.delete_count
  87              FROM (SELECT old1.node1_id as node1_id, lists.id as node2_id, nnn.ngrams_id as ngrams_id, nnn.ngrams_type as ngrams_type, count(*) as delete_count FROM OLD as old1
  88                 INNER JOIN nodes doc            ON doc.id          = old1.node2_id
  89                 INNER JOIN nodes lists          ON lists.parent_id = old1.node1_id
  90                 INNER JOIN node_node_ngrams nnn ON nnn.node2_id    = doc.id
  91                 WHERE nnn.node1_id in (?, lists.id)
  92                   AND lists.typename = ?
  93                 GROUP BY old1.node1_id, lists.id, nnn.ngrams_id, nnn.ngrams_type
  94             ) AS d
  95             WHERE node_node_ngrams.node1_id = d.node1_id
  96               AND node_node_ngrams.node2_id = d.node2_id
  97               AND node_node_ngrams.ngrams_id = d.ngrams_id
  98               AND node_node_ngrams.ngrams_type = d.ngrams_type
  99                 ;
 100         RETURN NULL;
 101       END
 102       $$ LANGUAGE plpgsql;
 103
 104       -- DROP trigger trigger_delete_count on nodes_nodes;
 105       CREATE TRIGGER trigger_delete_count AFTER DELETE on nodes_nodes
 106       REFERENCING OLD TABLE AS OLD
 107       FOR EACH STATEMENT
 108       EXECUTE PROCEDURE set_delete_count();
 109   |]
 110
 111
 112 triggerInsertCount :: MasterListId -> Cmd err Int64
 113 triggerInsertCount lId = execPGSQuery query (lId, nodeTypeId NodeList)
 114   where
 115     query :: DPS.Query
 116     query = [sql|
 117         CREATE OR REPLACE FUNCTION set_insert_count() RETURNS trigger AS $$
 118         BEGIN
 119               INSERT INTO node_node_ngrams (node1_id, node2_id, ngrams_id, ngrams_type, weight)
 120               SELECT new1.node1_id , lists.id, nnn.ngrams_id, nnn.ngrams_type, count(*) as weight from NEW as new1
 121                   INNER JOIN nodes doc            ON doc.id          = new1.node2_id
 122                   INNER JOIN nodes lists          ON lists.parent_id = new1.node1_id
 123                   INNER JOIN node_node_ngrams nnn ON nnn.node2_id    = doc.id
 124                   WHERE nnn.node1_id in (?, lists.id)
 125                     AND lists.typename = ?
 126                   GROUP BY new1.node1_id, lists.id, nnn.ngrams_id, nnn.ngrams_type
 127               ON CONFLICT (node1_id, node2_id, ngrams_id, ngrams_type)
 128                  DO UPDATE set weight = node_node_ngrams.weight + excluded.weight
 129                   ;
 130           RETURN NULL;
 131         END
 132         $$ LANGUAGE plpgsql;
 133
 134         -- DROP trigger trigger_insert_count on nodes_nodes;
 135         CREATE TRIGGER trigger_insert_count AFTER INSERT on nodes_nodes
 136         REFERENCING NEW TABLE AS NEW
 137         FOR EACH STATEMENT
 138         EXECUTE PROCEDURE set_insert_count();
 139   |]
 140
 141 triggerUpdateAdd :: MasterListId -> Cmd err Int64
 142 triggerUpdateAdd lId = execPGSQuery query (lId, nodeTypeId NodeList)
 143   where
 144     query :: DPS.Query
 145     query = [sql|
 146         CREATE OR REPLACE FUNCTION set_update_ngrams_add() RETURNS trigger AS $$
 147         BEGIN
 148                UPDATE node_node_ngrams nnn0 SET weight = weight + d.fix_count
 149                FROM (SELECT new1.node1_id as node1_id, lists.id as node2_id, nnn.ngrams_id as ngrams_id, nnn.ngrams_type as ngrams_type, count(*) as fix_count
 150                    FROM NEW as new1
 151                    INNER JOIN nodes       lists    ON new1.node1_id = lists.parent_id
 152                    INNER JOIN node_node_ngrams nnn ON new1.node2_id = nnn.node2_id
 153                   WHERE nnn.node1_id in (?, lists.id)  -- (masterList_id, userLists)
 154                     AND lists.typename = ?
 155                   GROUP BY new1.node1_id, lists.id, nnn.ngrams_id, nnn.ngrams_type
 156                     ) as d
 157                 WHERE nnn0.node1_id = d.node1_id
 158                 AND   nnn0.node2_id = d.node2_id
 159                 AND   nnn0.ngrams_id = d.ngrams_id
 160                 AND   nnn0.ngrams_type = d.ngrams_type
 161                 ;
 162           RETURN NULL;
 163         END
 164         $$ LANGUAGE plpgsql;
 165
 166         -- DROP trigger trigger_count_update_add on nodes_nodes;
 167         CREATE TRIGGER trigger_count_update_add AFTER UPDATE on nodes_nodes
 168         REFERENCING OLD TABLE AS OLD NEW TABLE AS NEW
 169         FOR EACH ROW
 170         WHEN (OLD.category <= 0 AND NEW.category >= 1)
 171         EXECUTE PROCEDURE set_update_ngrams_add();
 172   |]
 173
 174 triggerUpdateDel :: MasterListId -> Cmd err Int64
 175 triggerUpdateDel lId = execPGSQuery query (lId, nodeTypeId NodeList)
 176   where
 177     query :: DPS.Query
 178     query = [sql|
 179         CREATE OR REPLACE FUNCTION set_update_ngrams_count_del() RETURNS trigger AS $$
 180         BEGIN
 181                UPDATE node_node_ngrams nnn0 SET weight = weight - d.fix_count
 182                FROM (SELECT new1.node1_id as node1_id, lists.id as node2_id, nnn.ngrams_id as ngrams_id, nnn.ngrams_type as ngrams_type, count(*) as fix_count
 183                    FROM NEW as new1
 184                    INNER JOIN nodes       lists    ON new1.node1_id = lists.parent_id
 185                    INNER JOIN node_node_ngrams nnn ON new1.node2_id = nnn.node2_id
 186                   WHERE nnn.node1_id in (?, lists.id)  -- (masterList_id, userLists)
 187                     AND lists.typename = ?
 188                   GROUP BY new1.node1_id, lists.id, nnn.ngrams_id, nnn.ngrams_type
 189                     ) as d
 190                 WHERE nnn0.node1_id = d.node1_id
 191                 AND   nnn0.node2_id = d.node2_id
 192                 AND   nnn0.ngrams_id = d.ngrams_id
 193                 AND   nnn0.ngrams_type = d.ngrams_type
 194                 ;
 195           RETURN NULL;
 196         END
 197         $$ LANGUAGE plpgsql;
 198
 199         -- DROP trigger trigger_count_delete2 on nodes_nodes;
 200         CREATE TRIGGER trigger_count_delete2 AFTER UPDATE on nodes_nodes
 201         REFERENCING OLD TABLE AS OLD NEW TABLE AS NEW
 202         FOR EACH ROW
 203         WHEN (OLD.category >= 1 AND NEW.category <= 0)
 204         EXECUTE PROCEDURE set_update_ngrams_count_del();
 205
 206   |]