postgres_db_structure_functions.sql 11.2 KB
Newer Older
1

2
3
4
5
-- modifying the schema to allow an external_id

ALTER TABLE tce_user_groups ADD COLUMN external_id varchar(255);

6
-- functions for tcexam esb integration
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

-- assign_tcexam_user_to_group

CREATE OR REPLACE FUNCTION assign_tcexam_user_to_group(xmlbody text, pgroupid character varying, pappointmentid character varying)
  RETURNS void AS
$BODY$
    DECLARE
	login character varying;
	externalid character varying;	
        userid bigint;
        groupid bigint;
        uservo xml; 
        
     BEGIN
            
	uservo = xmlbody::xml;	
	externalid = CONCAT (pgroupid,':',pappointmentid);
	SELECT (xpath('/userVO/login/text()', uservo))[1] into login;
	
	SELECT user_id INTO userid FROM tce_users WHERE user_name = login;
	SELECT group_id INTO groupid FROM tce_user_groups WHERE external_id = externalid ;

	INSERT INTO tce_usrgroups (usrgrp_user_id, usrgrp_group_id) VALUES (userid, groupid);


END;
$BODY$
  LANGUAGE plpgsql VOLATILE
35
36
37
  SECURITY DEFINER
  -- Set a secure search_path: trusted schema(s), then 'pg_temp'.
  SET search_path = tcexam, pg_temp;
38
ALTER FUNCTION assign_tcexam_user_to_group(text, character varying, character varying)
39
  OWNER TO tcexam;
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54

--  delete_tcexam_user

CREATE OR REPLACE FUNCTION delete_tcexam_user(login character varying)
  RETURNS void AS
$BODY$
          
	BEGIN
	-- delete only if no tests for this user available
		IF (SELECT COUNT(*) FROM (SELECT user_id, user_name FROM tce_users u INNER JOIN tce_tests t ON (t.test_user_id = u.user_id) where u.user_name = login) AS numberofusers) = 0 THEN
			DELETE FROM tce_users WHERE user_name = login;
		END IF;		
	END;
$BODY$
  LANGUAGE plpgsql VOLATILE
55
56
57
  SECURITY DEFINER
  -- Set a secure search_path: trusted schema(s), then 'pg_temp'.
  SET search_path = tcexam, pg_temp;
58
ALTER FUNCTION delete_tcexam_user(character varying)
59
  OWNER TO tcexam;
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

--  get_tcexam_group

CREATE OR REPLACE FUNCTION get_tcexam_group(externalid character varying)
  RETURNS text AS
$BODY$


DECLARE 
	xoutput text;

BEGIN
	IF NOT EXISTS(SELECT group_name from tce_user_groups where external_id like concat(externalid,':%'))
	THEN
		RETURN '<appointments><appointment><appointmentId>' || '' || '</appointmentId></appointment></appointments>';
	END IF;
	
SELECT XMLSERIALIZE ( CONTENT
	 
	 XMLELEMENT(name "appointments",
	 xmlagg(
		XMLELEMENT(name "appointment",
		XMLFOREST(
			split_part(u.external_id,':',2) as "appointmentId",
			u.group_id as "tcexamGroupKey",
			u.group_name as "tcexamGroupName"
			)
		)
	)
	)
		 
	as text )
		INTO xoutput
		FROM tce_user_groups u
		WHERE u.external_id LIKE concat(externalid,':%')
		;
	RETURN xoutput;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
100
101
102
  SECURITY DEFINER
  -- Set a secure search_path: trusted schema(s), then 'pg_temp'.
  SET search_path = tcexam, pg_temp;
103
ALTER FUNCTION get_tcexam_group(character varying)
104
  OWNER TO tcexam;
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121


-- get_tcexam_group_appointment_users


CREATE OR REPLACE FUNCTION get_tcexam_group_appointment_users(pgroupid character varying, pappointmentid character varying)
  RETURNS text AS
$BODY$

DECLARE 
	xoutput text;

BEGIN
SELECT XMLSERIALIZE ( CONTENT
	XMLELEMENT(name "userVOes",
	 xmlagg(
		XMLELEMENT(name "userVO",
122
123
124
125
126
127
		XMLFOREST(u.user_id),
		XMLFOREST(u.user_name  as login),
		XMLFOREST(u.user_email),
		XMLFOREST(u.user_firstname),
		XMLFOREST(u.user_lastname),
		XMLFOREST(u.user_level)
128
129
130
		
))) as text )
		INTO xoutput
131
132
133
134
		FROM tce_user_groups g
		LEFT JOIN tce_usrgroups ug ON g.group_id = ug.usrgrp_group_id
		LEFT JOIN tce_users u ON u.user_id = ug.usrgrp_user_id
		WHERE g.external_id = concat(pgroupid,':',pappointmentid)
135
136
137
138
139
		;
	RETURN xoutput;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
140
141
142
  SECURITY DEFINER
  -- Set a secure search_path: trusted schema(s), then 'pg_temp'.
  SET search_path = tcexam, pg_temp;
143
ALTER FUNCTION get_tcexam_group_appointment_users(character varying, character varying)
144
  OWNER TO tcexam;
145
146
147
148
149
150
151
152
153
154
155
156


-- get_tcexam_group_users

CREATE OR REPLACE FUNCTION get_tcexam_group_users(group_id character varying)
  RETURNS text AS
$BODY$

SELECT XMLSERIALIZE ( CONTENT
	XMLELEMENT(name "userVOes",
	 xmlagg(
		XMLELEMENT(name "userVO",
157
158
159
160
161
162
163
164
165
166
167
168
169
170
		XMLFOREST(u.user_id),
		XMLFOREST(u.user_name as login),
		XMLFOREST(u.user_email),
		XMLFOREST(u.user_firstname),
		XMLFOREST(u.user_lastname),
		XMLFOREST(u.user_level)
		)
	 )
	) as text )

	FROM tce_user_groups g
	LEFT JOIN tce_usrgroups ug ON g.group_id = ug.usrgrp_group_id
	LEFT JOIN tce_users u ON u.user_id = ug.usrgrp_user_id
	WHERE g.external_id LIKE concat($1,':%');
171
172
173

$BODY$
  LANGUAGE sql VOLATILE
174
175
176
177
178
179
  SECURITY DEFINER
  -- Set a secure search_path: trusted schema(s), then 'pg_temp'.
  SET search_path = tcexam, pg_temp;
ALTER FUNCTION get_tcexam_group_users(character varying)
  OWNER TO tcexam;

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

-- insert_tcexam_group


CREATE OR REPLACE FUNCTION insert_tcexam_group(xmlbody text, pgroupid character varying, pappointmentid character varying, pgroupname character varying)
  RETURNS void AS
$BODY$
DECLARE
	externalid character varying;
	appointmentid character varying;
        groupvo xml;  
        
BEGIN
            
	groupvo = xmlbody::xml;	
	externalid = CONCAT (pgroupid,':',pappointmentid);
		
	IF NOT EXISTS(SELECT group_id FROM tce_user_groups WHERE external_id = externalid) 
		THEN
		INSERT INTO tce_user_groups (group_name, external_id) VALUES  (pgroupname,externalid);
	END IF;
	



END;
$BODY$
  LANGUAGE plpgsql VOLATILE
210
211
212
  SECURITY DEFINER
  -- Set a secure search_path: trusted schema(s), then 'pg_temp'.
  SET search_path = tcexam, pg_temp;
213
ALTER FUNCTION insert_tcexam_group(text, character varying, character varying, character varying)
214
  OWNER TO tcexam;
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

 
-- insert_tcexam_user


CREATE OR REPLACE FUNCTION insert_tcexam_user(xmlbody text, pgroupid character varying, pappointmentid character varying)
  RETURNS void AS
$BODY$
    DECLARE
	login character varying;	
	email character varying;	
	firstname character varying;	
	lastname character varying;	
        uservo xml; 
        
	BEGIN
		    
		uservo = xmlbody::xml;	
		SELECT (xpath('/userVO/login/text()', uservo))[1] into login;
		SELECT (xpath('/userVO/email/text()', uservo))[1] into email;
		SELECT (xpath('/userVO/firstName/text()', uservo))[1] into firstname;
		SELECT (xpath('/userVO/lastName/text()', uservo))[1] into lastname;

		IF NOT EXISTS(SELECT user_name from tce_users where user_name = login ) 
		THEN
			INSERT INTO tce_users (user_name, user_email, user_firstname, user_lastname, user_level, user_regdate, user_password, user_ip) 
			VALUES  (login,email,firstname,lastname, 1, now(), 'no£password', '127.0.0.1')		;
		END IF;

	END;
$BODY$
  LANGUAGE plpgsql VOLATILE
247
248
249
  SECURITY DEFINER
  -- Set a secure search_path: trusted schema(s), then 'pg_temp'.
  SET search_path = tcexam, pg_temp;
250
ALTER FUNCTION insert_tcexam_user(text, character varying, character varying)
251
  OWNER TO tcexam;
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


-- remove_tcexam_user_from_group

CREATE OR REPLACE FUNCTION remove_tcexam_user_from_group(xmlbody text, pgroupid character varying, pappointmentid character varying)
  RETURNS void AS
$BODY$
    DECLARE
	login character varying;	
	userid integer;	
	groupid integer;
        uservo xml; 
        
	BEGIN
		    
		uservo = xmlbody::xml;	
		SELECT (xpath('/userVO/login/text()', uservo))[1] into login;
		SELECT user_id INTO userid FROM tce_users WHERE user_name = login;
		SELECT group_id INTO groupid FROM tce_user_groups WHERE external_id = pgroupid || ':' || pappointmentid;
		UPDATE tce_users SET user_level = 0 WHERE user_id = userid;
		DELETE FROM tce_usrgroups WHERE usrgrp_user_id = userid AND usrgrp_group_id = groupid;

	END;
$BODY$
  LANGUAGE plpgsql VOLATILE
277
278
279
  SECURITY DEFINER
  -- Set a secure search_path: trusted schema(s), then 'pg_temp'.
  SET search_path = tcexam, pg_temp;
280
ALTER FUNCTION remove_tcexam_user_from_group(text, character varying, character varying)
281
  OWNER TO tcexam;
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302


-- update_tcexam_group


CREATE OR REPLACE FUNCTION update_tcexam_group(pgroupid character varying, pappointmentid character varying, pgroupname character varying)
  RETURNS void AS
$BODY$
DECLARE
       theKey character varying;
                
BEGIN

	theKey = pgroupid || ':' || pappointmentid;

	IF (SELECT group_name FROM tce_user_groups WHERE external_id = theKey) != pgroupname THEN 
		UPDATE tce_user_groups set group_name = pgroupname WHERE external_id = theKey;
	END IF;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
303
304
305
  SECURITY DEFINER
  -- Set a secure search_path: trusted schema(s), then 'pg_temp'.
  SET search_path = tcexam, pg_temp;
306
ALTER FUNCTION update_tcexam_group(character varying, character varying, character varying)
307
  OWNER TO tcexam;
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
  
  
-- update_tcexam_user

CREATE OR REPLACE FUNCTION update_tcexam_user(xmlbody text, pgroupid character varying, pappointmentid character varying)
  RETURNS void AS
$BODY$
    DECLARE
	login character varying;	
	email character varying;	
	firstname character varying;	
	lastname character varying;	
        uservo xml; 
        
	BEGIN
		    
		uservo = xmlbody::xml;	
		SELECT (xpath('/userVO/login/text()', uservo))[1] into login;
		SELECT (xpath('/userVO/email/text()', uservo))[1] into email;
		SELECT (xpath('/userVO/firstName/text()', uservo))[1] into firstname;
		SELECT (xpath('/userVO/lastName/text()', uservo))[1] into lastname;
		
		IF (SELECT user_email FROM tce_users WHERE user_name = login) != email
		THEN
			UPDATE tce_users SET user_email = email WHERE user_name = login;
		END IF;
		IF (SELECT user_firstname FROM tce_users WHERE user_name = login) != firstname
		THEN
			UPDATE tce_users SET user_firstname = firstname WHERE user_name = login;
		END IF;
		IF (SELECT user_lastname FROM tce_users WHERE user_name = login) != lastname
		THEN
			UPDATE tce_users SET user_lastname = lastname WHERE user_name = login;
		END IF;
	END;
$BODY$
  LANGUAGE plpgsql VOLATILE
345
346
347
  SECURITY DEFINER
  -- Set a secure search_path: trusted schema(s), then 'pg_temp'.
  SET search_path = tcexam, pg_temp;
348
ALTER FUNCTION update_tcexam_user(text, character varying, character varying)
349
  OWNER TO tcexam;
350
  
351
352
353
354
355
356
  
-- create esb user

-- CREATE USER esb WITH PASSWORD '....';

-- grant execute right on the functions
357
358
359
360
361
362
363
364
365
366
367
368
369
370
--
-- # Generated with:
-- # grep 'CREATE OR REPLACE FUNCTION' postgres_db_structure_functions.sql | sed 's/CREATE OR REPLACE FUNCTION //' | while read f; do echo "GRANT EXECUTE ON FUNCTION $f TO esb;"; done

GRANT EXECUTE ON FUNCTION assign_tcexam_user_to_group(xmlbody text, pgroupid character varying, pappointmentid character varying) TO esb;
GRANT EXECUTE ON FUNCTION delete_tcexam_user(login character varying) TO esb;
GRANT EXECUTE ON FUNCTION get_tcexam_group(externalid character varying) TO esb;
GRANT EXECUTE ON FUNCTION get_tcexam_group_appointment_users(pgroupid character varying, pappointmentid character varying) TO esb;
GRANT EXECUTE ON FUNCTION get_tcexam_group_users(group_id character varying) TO esb;
GRANT EXECUTE ON FUNCTION insert_tcexam_group(xmlbody text, pgroupid character varying, pappointmentid character varying, pgroupname character varying) TO esb;
GRANT EXECUTE ON FUNCTION insert_tcexam_user(xmlbody text, pgroupid character varying, pappointmentid character varying) TO esb;
GRANT EXECUTE ON FUNCTION remove_tcexam_user_from_group(xmlbody text, pgroupid character varying, pappointmentid character varying) TO esb;
GRANT EXECUTE ON FUNCTION update_tcexam_group(pgroupid character varying, pappointmentid character varying, pgroupname character varying) TO esb;
GRANT EXECUTE ON FUNCTION update_tcexam_user(xmlbody text, pgroupid character varying, pappointmentid character varying) TO esb;