src/Gargantext/Database/Triggers/NodesNodes.hs

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