1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
{-|
Module : Gargantext.Database.Admin.Trigger.Nodes
Description : Triggers configuration
Copyright : (c) CNRS, 2017-Present
License : AGPL + CECILL v3
Maintainer : team@gargantext.org
Stability : experimental
Portability : POSIX
Triggers on Nodes table.
-}
{-# LANGUAGE QuasiQuotes #-}
module Gargantext.Database.Admin.Trigger.Contexts
where
import Data.Text (Text)
import Database.PostgreSQL.Simple.SqlQQ (sql)
import Gargantext.Core (HasDBid(..))
import Gargantext.Database.Admin.Types.Node -- (ListId, CorpusId, NodeId)
import Gargantext.Database.Prelude (Cmd, execPGSQuery)
import Gargantext.Prelude
import qualified Database.PostgreSQL.Simple as DPS
triggerSearchUpdate :: HasDBid NodeType => Cmd err Int64
triggerSearchUpdate = execPGSQuery query ( toDBid NodeDocument
, toDBid NodeDocument
, toDBid NodeContact
)
where
query :: DPS.Query
query = [sql|
CREATE OR REPLACE FUNCTION public.search_update()
RETURNS trigger AS $$
begin
IF new.typename = ? AND new.hyperdata @> '{"language_iso2":"EN"}' THEN
new.search := to_tsvector( 'english' , (new.hyperdata ->> 'title') || ' ' || (new.hyperdata ->> 'abstract'));
ELSIF new.typename = ? AND new.hyperdata @> '{"language_iso2":"FR"}' THEN
new.search := to_tsvector( 'french' , (new.hyperdata ->> 'title') || ' ' || (new.hyperdata ->> 'abstract'));
ELSIF new.typename = ? THEN
new.search := to_tsvector( 'french' , (new.hyperdata ->> 'prenom')
|| ' ' || (new.hyperdata ->> 'nom')
|| ' ' || (new.hyperdata ->> 'fonction')
);
ELSE
new.search := to_tsvector( 'english' , (new.hyperdata ->> 'title') || ' ' || (new.hyperdata ->> 'abstract'));
END IF;
return new;
end
$$ LANGUAGE plpgsql;
ALTER FUNCTION public.search_update() OWNER TO gargantua;
DROP TRIGGER IF EXISTS search_update_trigger on contexts;
CREATE TRIGGER search_update_trigger
BEFORE INSERT OR UPDATE
ON contexts FOR EACH ROW
EXECUTE PROCEDURE search_update();
-- Initialize index with already existing data
UPDATE contexts SET hyperdata = hyperdata;
|]
type Secret = Text
triggerUpdateHash :: HasDBid NodeType => Secret -> Cmd err Int64
triggerUpdateHash secret = execPGSQuery query ( toDBid NodeDocument
, toDBid NodeContact
, secret
, secret
, toDBid NodeDocument
, toDBid NodeContact
, secret
, secret
)
where
query :: DPS.Query
query = [sql|
CREATE OR REPLACE FUNCTION hash_insert_nodes()
RETURNS trigger AS $$
BEGIN
IF NEW.hash_id = ''
THEN
IF NEW.typename = ? OR NEW.typename = ?
THEN NEW.hash_id = digest(CONCAT(?, NEW.typename, NEW.name, NEW.parent_id, NEW.hyperdata), 'sha256');
ELSE NEW.hash_id = digest(CONCAT(?, NEW.typename, NEW.name, NEW.id, NEW.hyperdata), 'sha256');
END IF;
END IF;
RETURN NEW;
END
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION hash_update_nodes()
RETURNS trigger AS $$
BEGIN
IF NEW.typename = ? OR NEW.typename = ?
THEN NEW.hash_id = digest(CONCAT(?, NEW.typename, NEW.name, NEW.parent_id, NEW.hyperdata), 'sha256');
ELSE NEW.hash_id = digest(CONCAT(?, NEW.typename, NEW.name, NEW.id, NEW.hyperdata), 'sha256');
END IF;
RETURN NEW;
END
$$ LANGUAGE plpgsql;
DROP TRIGGER IF EXISTS nodes_hash_insert ON nodes;
DROP TRIGGER IF EXISTS nodes_hash_update ON nodes;
CREATE TRIGGER nodes_hash_insert BEFORE INSERT ON nodes FOR EACH ROW EXECUTE PROCEDURE hash_insert_nodes();
CREATE TRIGGER nodes_hash_update BEFORE UPDATE ON nodes FOR EACH ROW EXECUTE PROCEDURE hash_update_nodes();
DROP TRIGGER IF EXISTS contexts_hash_insert ON contexts;
DROP TRIGGER IF EXISTS contexts_hash_update ON contexts;
CREATE TRIGGER contexts_hash_insert BEFORE INSERT ON contexts FOR EACH ROW EXECUTE PROCEDURE hash_insert_nodes();
CREATE TRIGGER contexts_hash_update BEFORE UPDATE ON contexts FOR EACH ROW EXECUTE PROCEDURE hash_update_nodes();
|]