src/Gargantext/Database/Admin/Trigger/ContextNodeNgrams.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
  16 module Gargantext.Database.Admin.Trigger.ContextNodeNgrams
  17   where
  18
  19 import Database.PostgreSQL.Simple.SqlQQ (sql)
  20 import Gargantext.Core
  21 import Gargantext.Core.Types.Main (ListType(CandidateTerm))
  22 import Gargantext.Database.Admin.Types.Node -- (ListId, CorpusId, NodeId)
  23 import Gargantext.Database.Prelude (Cmd, execPGSQuery)
  24 import Gargantext.Prelude
  25 import qualified Database.PostgreSQL.Simple as DPS
  26
  27 triggerCountInsert :: HasDBid NodeType => Cmd err Int64
  28 triggerCountInsert = execPGSQuery query (toDBid NodeDocument, toDBid NodeList)
  29   where
  30     query :: DPS.Query
  31     query = [sql|
  32           CREATE OR REPLACE FUNCTION set_ngrams_global_count() RETURNS trigger AS $$
  33           BEGIN
  34             IF pg_trigger_depth() <> 1 THEN
  35               RETURN NEW;
  36             END IF;
  37             IF TG_OP = 'INSERT' THEN
  38                 INSERT INTO context_node_ngrams (context_id, node_id, ngrams_id, ngrams_type, weight)
  39                 select n.parent_id, n.id, new0.ngrams_id, new0.ngrams_type, count(*) from NEW as new0
  40                     INNER JOIN contexts n ON n.id  = new0.context_id
  41                     INNER JOIN nodes n2 ON n2.id = new0.node_id
  42                     WHERE n2.typename = ?  -- not mandatory
  43                       AND n.typename = ?   -- not mandatory
  44                       AND n.parent_id <> n2.id -- not mandatory
  45                     GROUP BY n.parent_id, n.id, new0.ngrams_id, new0.ngrams_type
  46                 ON CONFLICT (context_id, node_id, ngrams_id, ngrams_type)
  47                    DO UPDATE set weight = context_node_ngrams.weight + excluded.weight
  48                    ;
  49             END IF;
  50
  51             RETURN NULL;
  52           END
  53           $$ LANGUAGE plpgsql;
  54
  55           -- DROP trigger trigger_count_insert on context_node_ngrams;
  56
  57           CREATE TRIGGER trigger_count_insert AFTER INSERT on context_node_ngrams
  58           REFERENCING NEW TABLE AS NEW
  59           FOR EACH STATEMENT
  60           EXECUTE PROCEDURE set_ngrams_global_count();
  61    |]
  62
  63 triggerCountInsert2 :: HasDBid NodeType => Cmd err Int64
  64 triggerCountInsert2 = execPGSQuery query ( toDBid NodeCorpus
  65                                          , toDBid NodeDocument
  66                                          , toDBid NodeList
  67                                          )
  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 context_node_ngrams2 (context_id, nodengrams_id, weight)
  78                   SELECT corpus.id, nng.id, count(*) from NEW as new3
  79                         INNER JOIN node_ngrams nng    ON nng.id      = new3.nodengrams_id
  80                         INNER JOIN nodes       list   ON list.id     = nng.node_id
  81                         INNER JOIN nodes_nodes nn     ON nn.node2_id = new3.context_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 (context_id, nodengrams_id)
  90                    DO UPDATE set weight = context_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 context_node_ngrams2;
  99
 100           CREATE TRIGGER trigger_count_insert2 AFTER INSERT on context_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 -- TODO use context instead of nodes of type doc
 108 triggerCoocInsert :: HasDBid NodeType => Cmd err Int64
 109 triggerCoocInsert = execPGSQuery query ( toDBid NodeCorpus
 110                                        , toDBid NodeDocument
 111                                        , toDBid NodeList
 112                                        , toDBid CandidateTerm
 113                                        , toDBid CandidateTerm
 114                                        )
 115   where
 116     query :: DPS.Query
 117     query = [sql|
 118           CREATE OR REPLACE FUNCTION set_cooc() RETURNS trigger AS $$
 119           BEGIN
 120             IF pg_trigger_depth() <> 1 THEN
 121               RETURN NEW;
 122             END IF;
 123             IF TG_OP = 'INSERT' THEN
 124                 INSERT INTO node_nodengrams_nodengrams (node_id, node_ngrams1_id, node_ngrams2_id, weight)
 125                 WITH input(corpus_id, nn1, nn2, weight) AS (
 126                   SELECT corpus.id, nng1.id, nng2.id, count(*) from NEW as new2
 127                         INNER JOIN node_ngrams nng1   ON nng1.id     = new2.nodengrams_id
 128                         INNER JOIN nodes       list   ON list.id     = nng1.node_id
 129                         INNER JOIN nodes_nodes nn     ON nn.node2_id = new2.node_id
 130                         INNER JOIN nodes       corpus ON corpus.id   = nn.node1_id
 131                         INNER JOIN nodes       doc    ON doc.id      = nn.node2_id
 132
 133                         INNER JOIN node_node_ngrams2 nnng2 ON nnng2.node_id  = doc.id
 134                         INNER JOIN node_ngrams       nng2  ON nng2.id        = nnng2.nodengrams_id
 135
 136                         WHERE corpus.typename = ? -- 30 -- corpus
 137                           AND doc.typename    = ? -- 4  -- maybe not mandatory
 138                           AND list.typename   = ? -- 5  -- list
 139                           AND nng2.node_id    = list.id
 140                           AND nng1.id < nng2.id
 141                           AND nng1.node_subtype >= ?
 142                           AND nng2.node_subtype >= ?
 143                         GROUP BY corpus.id, nng1.id, nng2.id
 144                         )
 145                     SELECT * from input where weight > 1
 146
 147                 ON CONFLICT (node_id, node_ngrams1_id, node_ngrams2_id)
 148                    DO UPDATE set weight = node_nodengrams_nodengrams.weight + excluded.weight
 149                    ;
 150             END IF;
 151
 152             RETURN NULL;
 153           END
 154           $$ LANGUAGE plpgsql;
 155
 156           -- DROP trigger trigger_cooc on node_node_ngrams2;
 157
 158           CREATE TRIGGER trigger_cooc_insert AFTER INSERT on node_node_ngrams2
 159           REFERENCING NEW TABLE AS NEW
 160           FOR EACH STATEMENT
 161           EXECUTE PROCEDURE set_cooc();
 162    |]