-
Notifications
You must be signed in to change notification settings - Fork 2
/
05_observers.sql
315 lines (277 loc) · 13 KB
/
05_observers.sql
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
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
/*
OBSERVERS
---------
Various functions to import and get observers into UsersHub tables from VisioNature
*/
BEGIN;
DROP INDEX IF EXISTS utilisateurs.i_t_roles_champ_addi_id_universal;
CREATE INDEX IF NOT EXISTS i_t_roles_champ_addi_id_universal ON utilisateurs.t_roles (
(champs_addi #>> '{from_vn, id_universal}')
);
CREATE UNIQUE INDEX IF NOT EXISTS i_uniq_t_roles_email ON utilisateurs.t_roles (
email
);
CREATE OR REPLACE FUNCTION public.jsonb_arr_record_keys(JSONB)
RETURNS TEXT []
LANGUAGE sql
IMMUTABLE AS
'SELECT array(
SELECT DISTINCT
k
FROM jsonb_array_elements($1) elem
, jsonb_object_keys(elem) k
)';
COMMENT ON FUNCTION public.jsonb_arr_record_keys(JSONB) IS '
Generates text array of unique keys in jsonb array of records.
Fails if any array element is not a record!';
/* Fonction to create observers if not already registered */
DROP FUNCTION IF EXISTS src_lpodatas.fct_c_create_usershub_roles_from_visionature (
_site VARCHAR, _item JSONB, _rq TEXT
);
CREATE OR REPLACE FUNCTION src_lpodatas.fct_c_create_usershub_roles_from_visionature(
_site CHARACTER VARYING, _item JSONB,
_rq TEXT DEFAULT 'Utilisateur VisioNature'::TEXT
) RETURNS INTEGER
LANGUAGE plpgsql
AS
$$
DECLARE
therolerecord RECORD;
theorganismid INT;
BEGIN
/* PROCESS
-------
Si id_universal, alors on récupère l'utilisateur et on fait une MaJ
Si pas d'id_universal mais mail.
*/
-- Si l'utilisateur existe déjà (via id_universal VisioNature)
-- Si id_entity existe dans la donnée source, alors on vérifie que la donnée et est identique dans UsersHub, sinon on la créée ou met à jour
-- Sinon on créée l'utilisateur.
IF (SELECT exists(SELECT 1
FROM utilisateurs.t_roles
WHERE t_roles.champs_addi #>> '{from_vn,id_universal}' LIKE _item ->> 'id_universal')) THEN
SELECT *
INTO therolerecord
FROM utilisateurs.t_roles
WHERE t_roles.champs_addi #>> '{from_vn,id_universal}' LIKE _item ->> 'id_universal';
-- RAISE NOTICE 'from_vn exists %',therolerecord.champs_addi ? 'from_vn';
-- RAISE NOTICE 'id_universal exists %',therolerecord.champs_addi #> '{from_vn}' ? 'id_universal';
-- RAISE NOTICE 'from_vn or id_univorsal %',(therolerecord.champs_addi ? 'from_vn' OR
-- therolerecord.champs_addi #> '{from_vn}' ? 'id_universal');
-- IF NOT (therolerecord.champs_addi ? 'from_vn' OR therolerecord.champs_addi #> '{from_vn}' ? 'id_universal') THEN
-- RAISE DEBUG 'Create site % key within "from_vn" for role %', _site,therolerecord.id_role;
-- UPDATE utilisateurs.t_roles
-- SET
-- champs_addi = jsonb_set(champs_addi, ('{from_vn,id_universal}')::TEXT[], _item -> 'id_universal', TRUE)
-- WHERE
-- id_role = therolerecord.id_role;
-- END IF;
-- Si utilisateur (via email) mais que la valeur id_universal n'est pas renseignée.
-- Si nom/prenom/email est différent de ce qui est stocké alors ok sinon on update le mail et les noms
IF (_item ->> 'name' <> therolerecord.nom_role OR
_item ->> 'surname' <> therolerecord.prenom_role OR
_item ->> 'anonymous' <> therolerecord.champs_addi #>> '{from_vn,anonymous}'
-- OR _item ->> 'email' <> therolerecord.email
)
THEN
RAISE DEBUG 'Observer % with email % already exists', _item ->> 'id_universal', _item ->> 'email';
RAISE DEBUG '_item %', (_item ->> 'name',
_item ->> 'surname',
_item ->> 'email');
RAISE DEBUG 'therecord %', (therolerecord.nom_role, therolerecord.prenom_role, therolerecord.email);
UPDATE
utilisateurs.t_roles v
SET nom_role = _item ->> 'name'
, prenom_role = _item ->> 'surname'
, champs_addi = jsonb_set(champs_addi, '{from_vn,anonymous}'::TEXT[], _item -> 'anonymous', TRUE)
-- , email = _item ->> 'email'
, remarques = _rq
WHERE id_role = therolerecord.id_role;
RAISE DEBUG 'Observer % with email % updated', _item ->> 'id_universal', _item ->> 'email';
END IF;
-- Si la donnée source contient une entité, on tente vérifie si elle existe déjà dans usershub
IF (_item ? 'id_entity')
THEN
-- Si from_vn contient déjà un rattachement à une entité pour le site
SELECT src_lpodatas.fct_c_get_organisme_from_vn_id(_site, _item ->> 'id_entity')
INTO theorganismid;
RAISE DEBUG '<ID ORGANISM> is % | % | % ', _site, _item ->> 'id_entity', theorganismid;
IF (therolerecord.id_organisme IS NULL OR
(therolerecord.id_organisme IS NOT NULL AND therolerecord.id_organisme != theorganismid))
THEN
UPDATE utilisateurs.t_roles SET id_organisme = theorganismid WHERE id_role = therolerecord.id_role;
END IF;
IF NOT (therolerecord.champs_addi #> '{from_vn}' ? _site) THEN
RAISE DEBUG 'Create site % key within "from_vn" for role %', _site,therolerecord.id_role;
UPDATE utilisateurs.t_roles
SET champs_addi = jsonb_set(t_roles.champs_addi, ('{from_vn,' || _site || '}')::TEXT[], '{}'::JSONB,
TRUE)
WHERE id_role = therolerecord.id_role;
END IF;
IF NOT (therolerecord.champs_addi #> ('{from_vn,' || _site || '}')::TEXT[] ? 'id_entity') OR
((therolerecord.champs_addi #> ('{from_vn,' || _site || '}')::TEXT[] ? 'id_entity') AND
(therolerecord.champs_addi #>> ('{from_vn,' || _site || ',id_entity}')::TEXT[] !=
_item ->> 'id_entity'))
THEN
RAISE DEBUG 'create or update id_entity % for site % for user % with email %', _item ->> 'id_entity', _site, _item ->> 'id_universal', _item ->> 'email';
UPDATE utilisateurs.t_roles
SET champs_addi = jsonb_set(t_roles.champs_addi, ('{from_vn,' || _site || ', id_entity}')::TEXT[],
_item -> 'id_entity',
TRUE)
WHERE id_role = therolerecord.id_role;
ELSE
RAISE DEBUG 'id_entity % for site % and user % with email % already exists', _item ->> 'id_entity', _site, _item ->> 'id_universal', _item ->> 'email';
END IF;
END IF;
RAISE DEBUG 'Observer % with email % already exists', _item ->> 'id_universal', _item ->> 'email';
IF _item ->> 'anonymous' <> therolerecord.champs_addi #>> '{from_vn,anonymous}' THEN
PERFORM src_lpodatas.fct_c_update_user_observations(_item #>> '{id_universal}');
END IF;
ELSE
INSERT INTO utilisateurs.t_roles (nom_role, prenom_role, email, champs_addi, remarques, active, date_insert)
VALUES ( _item ->> 'name'
, _item ->> 'surname'
, _item ->> 'email'
, jsonb_build_object(
'from_vn',
jsonb_build_object(
_site,
jsonb_build_object(
'id_entity',
_item ->>
'id_entity'),
'id_universal',
_item ->>
'id_universal',
'anonymous',
_item ->>
'anonymous'))
, _rq
, FALSE
, now())
ON CONFLICT (email)
DO UPDATE SET nom_role = _item ->> 'name'
, prenom_role = _item ->> 'surname'
, champs_addi = jsonb_set(t_roles.champs_addi, '{from_vn}',
jsonb_build_object(
_site,
jsonb_build_object(
'id_entity',
_item ->>
'id_entity'), 'id_universal',
_item ->>
'id_universal',
'anonymous',
_item ->>
'anonymous'))
, remarques = _rq
RETURNING id_role INTO therolerecord;
RAISE DEBUG 'Observer % inserted with id %', _item ->> 'id_universal', therolerecord.id_role;
RETURN therolerecord.id_role;
END IF;
RETURN therolerecord.id_role;
END
$$;
COMMENT ON FUNCTION src_lpodatas.fct_c_create_usershub_roles_from_visionature(
_site VARCHAR, _item JSONB, _rq TEXT
) IS 'créée ou mets à jour un observervateur à partir des entrées json VisioNature';
/* Function that returns id_role from VisioNature user universal id */
DROP FUNCTION IF EXISTS src_lpodatas.fct_c_get_id_role_from_visionature_uid (
_uid TEXT, _check_anonymous BOOL
);
CREATE OR REPLACE FUNCTION src_lpodatas.fct_c_get_id_role_from_visionature_uid(
_uid TEXT, _check_anonymous BOOL DEFAULT FALSE
)
RETURNS INT
AS
$$
DECLARE
the_roleid INT;
BEGIN
SELECT INTO the_roleid CASE
WHEN (_check_anonymous AND t_roles.champs_addi #>> '{from_vn,anonymous}' = '1')
THEN NULL
ELSE
t_roles.id_role END
FROM utilisateurs.t_roles
WHERE champs_addi #>> '{from_vn,id_universal}' = _uid;
RETURN the_roleid;
END
$$
LANGUAGE plpgsql;
COMMENT ON FUNCTION src_lpodatas.fct_c_get_id_role_from_visionature_uid(
_uid TEXT, _check_anonymous BOOL
) IS 'Retourne un id_role à partir d''un id_universal de visionature';
/* Function that returns id_role from VisioNature user universal id */
DROP FUNCTION IF EXISTS src_lpodatas.fct_c_get_role_name_from_visionature_uid (
_uid TEXT, _check_anonymous BOOL
);
CREATE OR REPLACE FUNCTION src_lpodatas.fct_c_get_role_name_from_visionature_uid(
_uid TEXT, _check_anonymous BOOL DEFAULT FALSE
)
RETURNS TEXT
AS
$$
DECLARE
the_rolename TEXT;
BEGIN
SELECT INTO the_rolename CASE
WHEN (_check_anonymous AND t_roles.champs_addi #>> '{from_vn,anonymous}' = '1')
THEN 'Anonyme'
ELSE
trim(concat(nom_role, ' ', prenom_role)) END
FROM utilisateurs.t_roles
WHERE champs_addi #>> '{from_vn,id_universal}' = _uid;
RETURN the_rolename;
END
$$
LANGUAGE plpgsql;
COMMENT ON FUNCTION src_lpodatas.fct_c_get_role_name_from_visionature_uid(
_uid TEXT, _check_anonymous BOOL
) IS 'Retourne un id_role à partir d''un id_universal de visionature';
/* TESTS */
-- WITH
-- titem AS
-- (SELECT
-- jsonb_set(item, '{name}', '"test2"') AS item
-- FROM
-- src_vn_json.observers_json
-- LIMIT 1)
--
-- SELECT
-- src_lpodatas.fct_create_observer_from_visionature(item)
-- FROM
-- titem;
-- WITH
-- titem AS
-- (SELECT
-- jsonb_set(item, '{name}', '"test2"') AS item
-- FROM
-- src_vn_json.observers_json
-- LIMIT 1)
--
-- SELECT
-- src_lpodatas.fct_get_id_role_from_visionature_uid(item ->> 'id_universal')
-- FROM
-- titem;
/* Trigger pour peupler automatiquement la table t_roles à partir des entrées observateurs de VisioNature*/
DROP TRIGGER IF EXISTS tri_upsert_vn_observers_to_geonature ON src_vn_json.observers_json;
CREATE OR REPLACE FUNCTION src_lpodatas.fct_tri_c_vn_observers_to_usershub()
RETURNS TRIGGER
LANGUAGE plpgsql
AS
$$
BEGIN
PERFORM
src_lpodatas.fct_c_create_usershub_roles_from_visionature(new.site, new.item);
RETURN new;
END;
$$;
COMMENT ON FUNCTION src_lpodatas.fct_tri_c_vn_observers_to_usershub() IS 'Function de trigger permettant de peupler automatiquement la table des observateurs utilisateurs.t_roles à partir des données VisioNature';
CREATE TRIGGER tri_upsert_vn_observers_to_geonature
AFTER INSERT OR UPDATE
ON src_vn_json.observers_json
FOR EACH ROW
EXECUTE FUNCTION src_lpodatas.fct_tri_c_vn_observers_to_usershub();
COMMENT ON TRIGGER tri_upsert_vn_observers_to_geonature ON src_vn_json.observers_json IS 'Trigger permettant de peupler automatiquement la table des observateurs utilisateurs.t_roles à partir des données VisioNature';
COMMIT;