src/Gargantext/Database/Triggers/NodeNodeNgrams.hs

   1 {-|
   2 Module      : Gargantext.Database.Triggers.NodeNodeNgrams
   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 NodeNodeNgrams table.
  11
  12 -}
  13
  14 {-# LANGUAGE QuasiQuotes       #-}
  15 {-# LANGUAGE NoImplicitPrelude #-}
  16 {-# LANGUAGE OverloadedStrings #-}
  17 {-# LANGUAGE RankNTypes        #-}
  18
  19 module Gargantext.Database.Triggers.NodeNodeNgrams
  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 triggerCountInsert :: Cmd err Int64
  31 triggerCountInsert = execPGSQuery query (nodeTypeId NodeDocument, nodeTypeId NodeList)
  32   where
  33     query :: DPS.Query
  34     query = [sql|
  35           CREATE OR REPLACE FUNCTION set_ngrams_global_count() RETURNS trigger AS $$
  36           BEGIN
  37             IF pg_trigger_depth() <> 1 THEN
  38               RETURN NEW;
  39             END IF;
  40             IF TG_OP = 'INSERT' THEN
  41                 INSERT INTO node_node_ngrams (node1_id, node2_id, ngrams_id, ngrams_type, weight)
  42                 select n.parent_id, n.id, new1.ngrams_id, new1.ngrams_type, count(*) from NEW as new1
  43                     INNER JOIN nodes n  ON n.id = new1.node1_id
  44                     INNER JOIN nodes n2 ON n2.id = new1.node2_id
  45                     WHERE n2.typename = ?  -- not mandatory
  46                       AND n.typename = ?   -- not mandatory
  47                       AND n.parent_id <> n2.id -- not mandatory
  48                     GROUP BY n.parent_id, n.id, new1.ngrams_id, new1.ngrams_type
  49                 ON CONFLICT (node1_id, node2_id, ngrams_id, ngrams_type)
  50                    DO UPDATE set weight = node_node_ngrams.weight + excluded.weight
  51                    ;
  52             END IF;
  53
  54             RETURN NULL;
  55           END
  56           $$ LANGUAGE plpgsql;
  57
  58           -- DROP trigger trigger_count_insert on node_node_ngrams;
  59
  60           CREATE TRIGGER trigger_count_insert AFTER INSERT on node_node_ngrams
  61           REFERENCING NEW TABLE AS NEW
  62           FOR EACH STATEMENT
  63           EXECUTE PROCEDURE set_ngrams_global_count();
  64    |]
  65
  66 triggerCountInsert2 :: Cmd err Int64
  67 triggerCountInsert2 = execPGSQuery query (nodeTypeId NodeCorpus, nodeTypeId NodeDocument, nodeTypeId NodeList)
  68   where
  69     query :: DPS.Query
  70     query = [sql|
  71           CREATE OR REPLACE FUNCTION set_ngrams_global_count2() RETURNS trigger AS $$
  72           BEGIN
  73             IF pg_trigger_depth() <> 1 THEN
  74               RETURN NEW;
  75             END IF;
  76             IF TG_OP = 'INSERT' THEN
  77                 INSERT INTO node_node_ngrams2 (node_id, nodengrams_id, weight)
  78                   SELECT corpus.id, nng.id, count(*) from NEW as new1
  79                         INNER JOIN node_ngrams nng    ON nng.id = new1.nodengrams_id
  80                         INNER JOIN nodes       list   ON list.id   = nng.node_id
  81                         INNER JOIN nodes_nodes nn     ON nn.node2_id = new1.node_id
  82                         INNER JOIN nodes       corpus ON corpus.id   = nn.node1_id
  83                         INNER JOIN nodes       doc    ON doc.id      = nn.node2_id
  84                         WHERE corpus.typename = ? -- 30 -- corpus
  85                           AND doc.typename    = ? -- 4  -- maybe not mandatory
  86                           AND list.typename   = ? -- 5  -- list
  87                         GROUP BY corpus.id, nng.id
  88
  89                 ON CONFLICT (node_id, nodengrams_id)
  90                    DO UPDATE set weight = node_node_ngrams2.weight + excluded.weight
  91                    ;
  92             END IF;
  93
  94             RETURN NULL;
  95           END
  96           $$ LANGUAGE plpgsql;
  97
  98           -- DROP trigger trigger_count_insert2 on node_node_ngrams2;
  99
 100           CREATE TRIGGER trigger_count_insert2 AFTER INSERT on node_node_ngrams2
 101           REFERENCING NEW TABLE AS NEW
 102           FOR EACH STATEMENT
 103           EXECUTE PROCEDURE set_ngrams_global_count2();
 104    |]
 105
 106 -- TODO add the groups
 107 triggerCoocInsert :: Cmd err Int64
 108 triggerCoocInsert = execPGSQuery query (nodeTypeId NodeCorpus, nodeTypeId NodeDocument, nodeTypeId NodeList)
 109   where
 110     query :: DPS.Query
 111     query = [sql|
 112           CREATE OR REPLACE FUNCTION set_cooc() RETURNS trigger AS $$
 113           BEGIN
 114             IF pg_trigger_depth() <> 1 THEN
 115               RETURN NEW;
 116             END IF;
 117             IF TG_OP = 'INSERT' THEN
 118                 INSERT INTO node_nodengrams_nodengrams (node_id, node_ngrams1_id, node_ngrams2_id, weight)
 119
 120                   SELECT corpus.id, nng1.id, nng2.id, count(*) from NEW as new1
 121                         INNER JOIN node_ngrams nng1   ON nng1.id     = new1.nodengrams_id
 122                         INNER JOIN nodes       list   ON list.id     = nng1.node_id
 123                         INNER JOIN nodes_nodes nn     ON nn.node2_id = new1.node_id
 124                         INNER JOIN nodes       corpus ON corpus.id   = nn.node1_id
 125                         INNER JOIN nodes       doc    ON doc.id      = nn.node2_id
 126
 127                         INNER JOIN node_node_ngrams2 nnng2 ON nnng2.node_id  = doc.id
 128                         INNER JOIN node_ngrams       nng2  ON nng2.id        = nnng2.nodengrams_id
 129
 130                         WHERE corpus.typename = ? -- 30 -- corpus
 131                           AND doc.typename    = ? -- 4  -- maybe not mandatory
 132                           AND list.typename   = ? -- 5  -- list
 133                           AND nng2.node_id    = list.id
 134                           -- AND nng1.id <> nng2.id
 135                         GROUP BY corpus.id, nng1.id, nng2.id
 136
 137                 ON CONFLICT (node_id, node_ngrams1_id, node_ngrams2_id)
 138                    DO UPDATE set weight = node_nodengrams_nodengrams.weight + excluded.weight
 139                    ;
 140             END IF;
 141
 142             RETURN NULL;
 143           END
 144           $$ LANGUAGE plpgsql;
 145
 146           -- DROP trigger trigger_cooc on node_node_ngrams2;
 147
 148           CREATE TRIGGER trigger_cooc_insert AFTER INSERT on node_node_ngrams2
 149           REFERENCING NEW TABLE AS NEW
 150           FOR EACH STATEMENT
 151           EXECUTE PROCEDURE set_cooc();
 152    |]
 153