]> Git — Sourcephile - gargantext.git/blob - src/Gargantext/Database/Triggers/NodeNodeNgrams.hs
Merge branch 'dev-phylo' of ssh://gitlab.iscpif.fr:20022/gargantext/haskell-gargantex...
[gargantext.git] / 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.Core.Types.Main (listTypeId, ListType(CandidateTerm))
27 import Gargantext.Database.Utils (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, nodeTypeId NodeDocument, nodeTypeId NodeList)
69 where
70 query :: DPS.Query
71 query = [sql|
72 CREATE OR REPLACE FUNCTION set_ngrams_global_count2() RETURNS trigger AS $$
73 BEGIN
74 IF pg_trigger_depth() <> 1 THEN
75 RETURN NEW;
76 END IF;
77 IF TG_OP = 'INSERT' THEN
78 INSERT INTO node_node_ngrams2 (node_id, nodengrams_id, weight)
79 SELECT corpus.id, nng.id, count(*) from NEW as new1
80 INNER JOIN node_ngrams nng ON nng.id = new1.nodengrams_id
81 INNER JOIN nodes list ON list.id = nng.node_id
82 INNER JOIN nodes_nodes nn ON nn.node2_id = new1.node_id
83 INNER JOIN nodes corpus ON corpus.id = nn.node1_id
84 INNER JOIN nodes doc ON doc.id = nn.node2_id
85 WHERE corpus.typename = ? -- 30 -- corpus
86 AND doc.typename = ? -- 4 -- maybe not mandatory
87 AND list.typename = ? -- 5 -- list
88 GROUP BY corpus.id, nng.id
89
90 ON CONFLICT (node_id, nodengrams_id)
91 DO UPDATE set weight = node_node_ngrams2.weight + excluded.weight
92 ;
93 END IF;
94
95 RETURN NULL;
96 END
97 $$ LANGUAGE plpgsql;
98
99 -- DROP trigger trigger_count_insert2 on node_node_ngrams2;
100
101 CREATE TRIGGER trigger_count_insert2 AFTER INSERT on node_node_ngrams2
102 REFERENCING NEW TABLE AS NEW
103 FOR EACH STATEMENT
104 EXECUTE PROCEDURE set_ngrams_global_count2();
105 |]
106
107 -- TODO add the groups
108 triggerCoocInsert :: Cmd err Int64
109 triggerCoocInsert = execPGSQuery query ( nodeTypeId NodeCorpus
110 , nodeTypeId NodeDocument
111 , nodeTypeId NodeList
112 , listTypeId CandidateTerm
113 , listTypeId 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 new1
127 INNER JOIN node_ngrams nng1 ON nng1.id = new1.nodengrams_id
128 INNER JOIN nodes list ON list.id = nng1.node_id
129 INNER JOIN nodes_nodes nn ON nn.node2_id = new1.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 |]
163