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