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