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