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