]> Git — Sourcephile - gargantext.git/blob - src/Gargantext/Database/Triggers/NodesNodes.hs
Merge branch 'dev-phylo' of ssh://gitlab.iscpif.fr:20022/gargantext/haskell-gargantex...
[gargantext.git] / src / Gargantext / Database / Triggers / NodesNodes.hs
1 {-|
2 Module : Gargantext.Database.Triggers.NodesNodes
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 NodesNodes table.
11
12 -}
13
14 {-# LANGUAGE QuasiQuotes #-}
15 {-# LANGUAGE NoImplicitPrelude #-}
16 {-# LANGUAGE OverloadedStrings #-}
17 {-# LANGUAGE RankNTypes #-}
18
19 module Gargantext.Database.Triggers.NodesNodes
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 type MasterListId = ListId
31
32 triggerDeleteCount :: MasterListId -> Cmd err Int64
33 triggerDeleteCount lId = execPGSQuery query (lId, nodeTypeId NodeList)
34 where
35 query :: DPS.Query
36 query = [sql|
37 CREATE OR REPLACE FUNCTION set_delete_count() RETURNS trigger AS $$
38 BEGIN
39 UPDATE node_node_ngrams SET weight = weight - d.delete_count
40 FROM (SELECT old1.node1_id as node1_id, lists.id as node2_id, nnn.ngrams_id as ngrams_id, nnn.ngrams_type as ngrams_type, count(*) as delete_count FROM OLD as old1
41 INNER JOIN nodes doc ON doc.id = old1.node2_id
42 INNER JOIN nodes lists ON lists.parent_id = old1.node1_id
43 INNER JOIN node_node_ngrams nnn ON nnn.node2_id = doc.id
44 WHERE nnn.node1_id in (?, lists.id)
45 AND lists.typename = ?
46 GROUP BY old1.node1_id, lists.id, nnn.ngrams_id, nnn.ngrams_type
47 ) AS d
48 WHERE node_node_ngrams.node1_id = d.node1_id
49 AND node_node_ngrams.node2_id = d.node2_id
50 AND node_node_ngrams.ngrams_id = d.ngrams_id
51 AND node_node_ngrams.ngrams_type = d.ngrams_type
52 ;
53 RETURN NULL;
54 END
55 $$ LANGUAGE plpgsql;
56
57 -- DROP trigger trigger_delete_count on nodes_nodes;
58 CREATE TRIGGER trigger_delete_count AFTER DELETE on nodes_nodes
59 REFERENCING OLD TABLE AS OLD
60 FOR EACH STATEMENT
61 EXECUTE PROCEDURE set_delete_count();
62 |]
63
64 triggerInsertCount :: MasterListId -> Cmd err Int64
65 triggerInsertCount lId = execPGSQuery query (lId, nodeTypeId NodeList)
66 where
67 query :: DPS.Query
68 query = [sql|
69 CREATE OR REPLACE FUNCTION set_insert_count() RETURNS trigger AS $$
70 BEGIN
71 INSERT INTO node_node_ngrams (node1_id, node2_id, ngrams_id, ngrams_type, weight)
72 SELECT new1.node1_id , lists.id, nnn.ngrams_id, nnn.ngrams_type, count(*) as weight from NEW as new1
73 INNER JOIN nodes doc ON doc.id = new1.node2_id
74 INNER JOIN nodes lists ON lists.parent_id = new1.node1_id
75 INNER JOIN node_node_ngrams nnn ON nnn.node2_id = doc.id
76 WHERE nnn.node1_id in (?, lists.id)
77 AND lists.typename = ?
78 GROUP BY new1.node1_id, lists.id, nnn.ngrams_id, nnn.ngrams_type
79 ON CONFLICT (node1_id, node2_id, ngrams_id, ngrams_type)
80 DO UPDATE set weight = node_node_ngrams.weight + excluded.weight
81 ;
82 RETURN NULL;
83 END
84 $$ LANGUAGE plpgsql;
85
86 -- DROP trigger trigger_insert_count on nodes_nodes;
87 CREATE TRIGGER trigger_insert_count AFTER INSERT on nodes_nodes
88 REFERENCING NEW TABLE AS NEW
89 FOR EACH STATEMENT
90 EXECUTE PROCEDURE set_insert_count();
91 |]
92
93 triggerUpdateAdd :: MasterListId -> Cmd err Int64
94 triggerUpdateAdd lId = execPGSQuery query (lId, nodeTypeId NodeList)
95 where
96 query :: DPS.Query
97 query = [sql|
98 CREATE OR REPLACE FUNCTION set_update_ngrams_add() RETURNS trigger AS $$
99 BEGIN
100 UPDATE node_node_ngrams nnn0 SET weight = weight + d.fix_count
101 FROM (SELECT new1.node1_id as node1_id, lists.id as node2_id, nnn.ngrams_id as ngrams_id, nnn.ngrams_type as ngrams_type, count(*) as fix_count
102 FROM NEW as new1
103 INNER JOIN nodes lists ON new1.node1_id = lists.parent_id
104 INNER JOIN node_node_ngrams nnn ON new1.node2_id = nnn.node2_id
105 WHERE nnn.node1_id in (?, lists.id) -- (masterList_id, userLists)
106 AND lists.typename = ?
107 GROUP BY new1.node1_id, lists.id, nnn.ngrams_id, nnn.ngrams_type
108 ) as d
109 WHERE nnn0.node1_id = d.node1_id
110 AND nnn0.node2_id = d.node2_id
111 AND nnn0.ngrams_id = d.ngrams_id
112 AND nnn0.ngrams_type = d.ngrams_type
113 ;
114 RETURN NULL;
115 END
116 $$ LANGUAGE plpgsql;
117
118 -- DROP trigger trigger_count_update_add on nodes_nodes;
119 CREATE TRIGGER trigger_count_update_add AFTER UPDATE on nodes_nodes
120 REFERENCING OLD TABLE AS OLD NEW TABLE AS NEW
121 FOR EACH ROW
122 WHEN (OLD.category <= 0 AND NEW.category >= 1)
123 EXECUTE PROCEDURE set_update_ngrams_add();
124 |]
125
126 triggerUpdateDel :: MasterListId -> Cmd err Int64
127 triggerUpdateDel lId = execPGSQuery query (lId, nodeTypeId NodeList)
128 where
129 query :: DPS.Query
130 query = [sql|
131 CREATE OR REPLACE FUNCTION set_update_ngrams_count_del() RETURNS trigger AS $$
132 BEGIN
133 UPDATE node_node_ngrams nnn0 SET weight = weight - d.fix_count
134 FROM (SELECT new1.node1_id as node1_id, lists.id as node2_id, nnn.ngrams_id as ngrams_id, nnn.ngrams_type as ngrams_type, count(*) as fix_count
135 FROM NEW as new1
136 INNER JOIN nodes lists ON new1.node1_id = lists.parent_id
137 INNER JOIN node_node_ngrams nnn ON new1.node2_id = nnn.node2_id
138 WHERE nnn.node1_id in (?, lists.id) -- (masterList_id, userLists)
139 AND lists.typename = ?
140 GROUP BY new1.node1_id, lists.id, nnn.ngrams_id, nnn.ngrams_type
141 ) as d
142 WHERE nnn0.node1_id = d.node1_id
143 AND nnn0.node2_id = d.node2_id
144 AND nnn0.ngrams_id = d.ngrams_id
145 AND nnn0.ngrams_type = d.ngrams_type
146 ;
147 RETURN NULL;
148 END
149 $$ LANGUAGE plpgsql;
150
151 -- DROP trigger trigger_count_delete2 on nodes_nodes;
152 CREATE TRIGGER trigger_count_delete2 AFTER UPDATE on nodes_nodes
153 REFERENCING OLD TABLE AS OLD NEW TABLE AS NEW
154 FOR EACH ROW
155 WHEN (OLD.category >= 1 AND NEW.category <= 0)
156 EXECUTE PROCEDURE set_update_ngrams_count_del();
157
158 |]
159