]> Git — Sourcephile - gargantext.git/blob - src/Gargantext/Database/Triggers/NodeNodeNgrams.hs
[DB][Optim] Cooc without groups (wip).
[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.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