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.Database.Utils (Cmd, execPGSQuery)
  27 import Gargantext.Prelude
  28 import qualified Database.PostgreSQL.Simple as DPS
  29
  30 type MasterListId = ListId
  31
  32 triggerDeleteCount :: MasterListId -> Cmd err Int64
  33 triggerDeleteCount lId = execPGSQuery query (lId, nodeTypeId NodeList)
  34   where
  35     query :: DPS.Query
  36     query = [sql|
  37       CREATE OR REPLACE FUNCTION set_delete_count() RETURNS trigger AS $$
  38       BEGIN
  39             UPDATE node_node_ngrams SET weight = weight - d.delete_count
  40              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
  41                 INNER JOIN nodes doc            ON doc.id          = old1.node2_id
  42                 INNER JOIN nodes lists          ON lists.parent_id = old1.node1_id
  43                 INNER JOIN node_node_ngrams nnn ON nnn.node2_id    = doc.id
  44                 WHERE nnn.node1_id in (?, lists.id)
  45                   AND lists.typename = ?
  46                 GROUP BY old1.node1_id, lists.id, nnn.ngrams_id, nnn.ngrams_type
  47             ) AS d
  48             WHERE node_node_ngrams.node1_id = d.node1_id
  49               AND node_node_ngrams.node2_id = d.node2_id
  50               AND node_node_ngrams.ngrams_id = d.ngrams_id
  51               AND node_node_ngrams.ngrams_type = d.ngrams_type
  52                 ;
  53         RETURN NULL;
  54       END
  55       $$ LANGUAGE plpgsql;
  56
  57       -- DROP trigger trigger_delete_count on nodes_nodes;
  58       CREATE TRIGGER trigger_delete_count AFTER DELETE on nodes_nodes
  59       REFERENCING OLD TABLE AS OLD
  60       FOR EACH STATEMENT
  61       EXECUTE PROCEDURE set_delete_count();
  62   |]
  63
  64 triggerInsertCount :: MasterListId -> Cmd err Int64
  65 triggerInsertCount lId = execPGSQuery query (lId, nodeTypeId NodeList)
  66   where
  67     query :: DPS.Query
  68     query = [sql|
  69         CREATE OR REPLACE FUNCTION set_insert_count() RETURNS trigger AS $$
  70         BEGIN
  71               INSERT INTO node_node_ngrams (node1_id, node2_id, ngrams_id, ngrams_type, weight)
  72               SELECT new1.node1_id , lists.id, nnn.ngrams_id, nnn.ngrams_type, count(*) as weight from NEW as new1
  73                   INNER JOIN nodes doc            ON doc.id          = new1.node2_id
  74                   INNER JOIN nodes lists          ON lists.parent_id = new1.node1_id
  75                   INNER JOIN node_node_ngrams nnn ON nnn.node2_id    = doc.id
  76                   WHERE nnn.node1_id in (?, lists.id)
  77                     AND lists.typename = ?
  78                   GROUP BY new1.node1_id, lists.id, nnn.ngrams_id, nnn.ngrams_type
  79               ON CONFLICT (node1_id, node2_id, ngrams_id, ngrams_type)
  80                  DO UPDATE set weight = node_node_ngrams.weight + excluded.weight
  81                   ;
  82           RETURN NULL;
  83         END
  84         $$ LANGUAGE plpgsql;
  85
  86         -- DROP trigger trigger_insert_count on nodes_nodes;
  87         CREATE TRIGGER trigger_insert_count AFTER INSERT on nodes_nodes
  88         REFERENCING NEW TABLE AS NEW
  89         FOR EACH STATEMENT
  90         EXECUTE PROCEDURE set_insert_count();
  91   |]
  92
  93 triggerUpdateAdd :: MasterListId -> Cmd err Int64
  94 triggerUpdateAdd lId = execPGSQuery query (lId, nodeTypeId NodeList)
  95   where
  96     query :: DPS.Query
  97     query = [sql|
  98         CREATE OR REPLACE FUNCTION set_update_ngrams_add() RETURNS trigger AS $$
  99         BEGIN
 100                UPDATE node_node_ngrams nnn0 SET weight = weight + d.fix_count
 101                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
 102                    FROM NEW as new1
 103                    INNER JOIN nodes       lists    ON new1.node1_id = lists.parent_id
 104                    INNER JOIN node_node_ngrams nnn ON new1.node2_id = nnn.node2_id
 105                   WHERE nnn.node1_id in (?, lists.id)  -- (masterList_id, userLists)
 106                     AND lists.typename = ?
 107                   GROUP BY new1.node1_id, lists.id, nnn.ngrams_id, nnn.ngrams_type
 108                     ) as d
 109                 WHERE nnn0.node1_id = d.node1_id
 110                 AND   nnn0.node2_id = d.node2_id
 111                 AND   nnn0.ngrams_id = d.ngrams_id
 112                 AND   nnn0.ngrams_type = d.ngrams_type
 113                 ;
 114           RETURN NULL;
 115         END
 116         $$ LANGUAGE plpgsql;
 117
 118         -- DROP trigger trigger_count_update_add on nodes_nodes;
 119         CREATE TRIGGER trigger_count_update_add AFTER UPDATE on nodes_nodes
 120         REFERENCING OLD TABLE AS OLD NEW TABLE AS NEW
 121         FOR EACH ROW
 122         WHEN (OLD.category <= 0 AND NEW.category >= 1)
 123         EXECUTE PROCEDURE set_update_ngrams_add();
 124   |]
 125
 126 triggerUpdateDel :: MasterListId -> Cmd err Int64
 127 triggerUpdateDel lId = execPGSQuery query (lId, nodeTypeId NodeList)
 128   where
 129     query :: DPS.Query
 130     query = [sql|
 131         CREATE OR REPLACE FUNCTION set_update_ngrams_count_del() RETURNS trigger AS $$
 132         BEGIN
 133                UPDATE node_node_ngrams nnn0 SET weight = weight - d.fix_count
 134                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
 135                    FROM NEW as new1
 136                    INNER JOIN nodes       lists    ON new1.node1_id = lists.parent_id
 137                    INNER JOIN node_node_ngrams nnn ON new1.node2_id = nnn.node2_id
 138                   WHERE nnn.node1_id in (?, lists.id)  -- (masterList_id, userLists)
 139                     AND lists.typename = ?
 140                   GROUP BY new1.node1_id, lists.id, nnn.ngrams_id, nnn.ngrams_type
 141                     ) as d
 142                 WHERE nnn0.node1_id = d.node1_id
 143                 AND   nnn0.node2_id = d.node2_id
 144                 AND   nnn0.ngrams_id = d.ngrams_id
 145                 AND   nnn0.ngrams_type = d.ngrams_type
 146                 ;
 147           RETURN NULL;
 148         END
 149         $$ LANGUAGE plpgsql;
 150
 151         -- DROP trigger trigger_count_delete2 on nodes_nodes;
 152         CREATE TRIGGER trigger_count_delete2 AFTER UPDATE on nodes_nodes
 153         REFERENCING OLD TABLE AS OLD NEW TABLE AS NEW
 154         FOR EACH ROW
 155         WHEN (OLD.category >= 1 AND NEW.category <= 0)
 156         EXECUTE PROCEDURE set_update_ngrams_count_del();
 157
 158   |]
 159