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