postgres_db_structure_functions.sql 17.1 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
7
8
9
10
11
-- modifying the schema to assign a study key to user in group

ALTER TABLE tce_usrgroups ADD COLUMN usrgrp_studykey character varying(255);

ALTER TABLE tce_tests_users ADD COLUMN testuser_studykey character varying(255);

12
13
14
15
16
17
18
19
20
21
22
-- modifying to log sent reminder emails

ALTER TABLE tce_user_groups ADD email1_sent character varying(255);
ALTER TABLE tce_user_groups ADD email2_sent character varying(255);
ALTER TABLE tce_user_groups ADD email3_sent character varying(255);
ALTER TABLE tce_user_groups ADD disabled character varying(255);

-- restraint dropped because a user locked in VIS can be registered again in TCExam later, but of course keeps his  matrikelnr.
ALTER TABLE tce_users DROP CONSTRAINT ak_user_regnumber;


23

24
-- functions for tcexam esb integration
25
26
27
28
29
30
31
32

-- 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;
33
34
35
36
37
	externalid character varying;
	userid bigint;
	groupid bigint;
	studykey character varying;
	uservo xml; 
38
        
39
40
41
    BEGIN

	uservo = xmlbody::xml;
42
43
	externalid = CONCAT (pgroupid,':',pappointmentid);
	SELECT (xpath('/userVO/login/text()', uservo))[1] into login;
44
	SELECT (xpath('/userVO/appointmentStudyKey/text()', uservo))[1] into studykey;
45

46
47
	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;
48

49
50
51
52
	IF NOT EXISTS(SELECT usrgrp_user_id FROM tce_usrgroups WHERE usrgrp_group_id = groupid AND usrgrp_user_id = userid) 
		THEN INSERT INTO tce_usrgroups (usrgrp_user_id, usrgrp_group_id, usrgrp_studykey) VALUES (userid, groupid, studykey);
	ELSE UPDATE tce_usrgroups SET usrgrp_studykey = studykey WHERE usrgrp_group_id = groupid and usrgrp_user_id = userid;
	END IF;
53
54
55
56

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
57
58
59
  SECURITY DEFINER
  -- Set a secure search_path: trusted schema(s), then 'pg_temp'.
  SET search_path = tcexam, pg_temp;
60
ALTER FUNCTION assign_tcexam_user_to_group(text, character varying, character varying)
61
  OWNER TO tcexam;
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


--  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
102
103
104
  SECURITY DEFINER
  -- Set a secure search_path: trusted schema(s), then 'pg_temp'.
  SET search_path = tcexam, pg_temp;
105
ALTER FUNCTION get_tcexam_group(character varying)
106
  OWNER TO tcexam;
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122


-- 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",
123
124
125
126
127
128
		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)
129

130
131
))) as text )
		INTO xoutput
132
133
134
135
		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)
136
137
138
139
140
		;
	RETURN xoutput;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
141
142
143
  SECURITY DEFINER
  -- Set a secure search_path: trusted schema(s), then 'pg_temp'.
  SET search_path = tcexam, pg_temp;
144
ALTER FUNCTION get_tcexam_group_appointment_users(character varying, character varying)
145
  OWNER TO tcexam;
146
147
148
149
150
151
152
153
154
155
156
157


-- 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",
158
159
160
161
162
163
164
165
166
167
168
169
170
171
		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,':%');
172
173
174

$BODY$
  LANGUAGE sql VOLATILE
175
176
177
178
179
180
  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;

181
182
183
-- get tcexam_user
CREATE OR REPLACE FUNCTION get_tcexam_user(userid character varying)
 RETURNS text
184
185
186
 LANGUAGE plpgsql
 SECURITY DEFINER
 SET search_path TO tcexam, pg_temp
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
AS $function$

DECLARE
xoutput text;

BEGIN
SELECT XMLSERIALIZE ( CONTENT
	XMLELEMENT(name "userVOes",
	 xmlagg(
		XMLELEMENT(name "userVO",
		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_regnumber),
		XMLFOREST(u.user_level)
	))) as text )
INTO xoutput
FROM tce_users u
WHERE u.user_name = userid
;
RETURN xoutput;
END;
$function$
212
  
213
  
214
215
216

-- insert_tcexam_group

217
CREATE OR REPLACE FUNCTION insert_tcexam_group(pgroupid character varying, pappointmentid character varying, pgroupname character varying)
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
  RETURNS void AS
$BODY$
DECLARE
	externalid character varying;
	appointmentid character varying;
        groupvo xml;  
        
BEGIN
            
	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
237
238
239
  SECURITY DEFINER
  -- Set a secure search_path: trusted schema(s), then 'pg_temp'.
  SET search_path = tcexam, pg_temp;
240
ALTER FUNCTION insert_tcexam_group(text, character varying, character varying, character varying)
241
  OWNER TO tcexam;
242
243
244
245

 
-- insert_tcexam_user

246
247
CREATE OR REPLACE FUNCTION insert_tcexam_user(xmlbody text)
 RETURNS void
248
249
250
 LANGUAGE plpgsql
 SECURITY DEFINER
 SET search_path TO tcexam, pg_temp
251
AS $function$
252
    DECLARE
253
254
255
256
257
258
259
	login character varying;
	email character varying;
	firstname character varying;
	lastname character varying;
	matrikelnr character varying;
	uservo xml;

260
	BEGIN
261
		uservo = xmlbody::xml;
262
263
264
265
		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;
266
		SELECT (xpath('/userVO/properties/property/name[.="institutionalUserIdentifier"]/../value/text()', uservo))[1] 		into matrikelnr;
267
268

		IF NOT EXISTS(SELECT user_name from tce_users where user_name = login ) 
269
270
271
			THEN
				INSERT INTO tce_users (user_name, user_email, user_firstname, user_lastname, user_regnumber, user_level, user_regdate, user_password, user_ip) 
				VALUES  (login,email,firstname,lastname, matrikelnr, 1, now(), 'no£password', '127.0.0.1');
272
				INSERT INTO tce_usrgroups (usrgrp_user_id,usrgrp_group_id) VALUES ((SELECT user_id FROM tce_users WHERE user_name = login),1);
273
274
275
		END IF;

	END;
276
$function$
277

278
ALTER FUNCTION insert_tcexam_user(text)
279
  OWNER TO tcexam;
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303


-- 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;
		DELETE FROM tce_usrgroups WHERE usrgrp_user_id = userid AND usrgrp_group_id = groupid;

	END;
$BODY$
  LANGUAGE plpgsql VOLATILE
304
305
306
  SECURITY DEFINER
  -- Set a secure search_path: trusted schema(s), then 'pg_temp'.
  SET search_path = tcexam, pg_temp;
307
ALTER FUNCTION remove_tcexam_user_from_group(text, character varying, character varying)
308
  OWNER TO tcexam;
309
310


311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
-- Function: set_tcexam_user_level(character varying, integer)

CREATE OR REPLACE FUNCTION set_tcexam_user_level(login character varying, ulevel integer)
  RETURNS void AS
$BODY$

	BEGIN
		UPDATE tce_users SET user_level = ulevel WHERE user_name = login;
	END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  SECURITY DEFINER
  -- Set a secure search_path: trusted schema(s), then 'pg_temp'.
  SET search_path = tcexam, pg_temp;
ALTER FUNCTION set_tcexam_user_level(character varying, integer)
  OWNER TO tcexam;


329
330
331
332
333
334
335
336
-- 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;
337

338
339
340
341
342
343
344
345
346
347
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
348
349
350
  SECURITY DEFINER
  -- Set a secure search_path: trusted schema(s), then 'pg_temp'.
  SET search_path = tcexam, pg_temp;
351
ALTER FUNCTION update_tcexam_group(character varying, character varying, character varying)
352
  OWNER TO tcexam;
353
354
355
356
  
  
-- update_tcexam_user

357
358
CREATE OR REPLACE FUNCTION update_tcexam_user(xmlbody text)
 RETURNS void
359
360
361
 LANGUAGE plpgsql
 SECURITY DEFINER
 SET search_path TO tcexam, pg_temp
362
AS $function$
363
    DECLARE
364
365
366
367
368
369
370
371
	login character varying;
	email character varying;
	firstname character varying;
	lastname character varying;
	matrikelnr character varying;
	userstatus character varying;
	userlevel integer;
	uservo xml;
372

373
	BEGIN
374
375

		uservo = xmlbody::xml;
376
377
378
379
		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;
380
381
382
383
384
385
386
387
388
389
		SELECT (xpath('/userVO/properties/property/name[.="institutionalUserIdentifier"]/../value/text()', uservo))[1] into matrikelnr;
		SELECT (xpath('/userVO/status/text()', uservo))[1] into userstatus;
		SELECT user_level FROM tce_users WHERE user_name = login INTO userlevel;

		UPDATE tce_users SET user_email = email WHERE user_name = login;
		UPDATE tce_users SET user_firstname = firstname WHERE user_name = login;
		UPDATE tce_users SET user_lastname = lastname WHERE user_name = login;
		UPDATE tce_users SET user_regnumber = matrikelnr WHERE user_name = login;

		IF (userstatus != 'aktiv')
390
		THEN
391
			UPDATE tce_users SET user_level = 0 WHERE user_name = login;
392
		END IF;
393
394

		IF (userstatus = 'aktiv' AND userlevel = 0)
395
		THEN
396
			UPDATE tce_users SET user_level = 1 WHERE user_name = login;
397
		END IF;
398

399
	END;
400
401

$function$
402

403
ALTER FUNCTION update_tcexam_user(text)
404
  OWNER TO tcexam;
405

406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
-- Function: get_tcexam_recent_tests(character varying)

-- DROP FUNCTION get_tcexam_recent_tests(character varying);

CREATE OR REPLACE FUNCTION get_tcexam_recent_tests(externalid character varying)
  RETURNS text AS
$BODY$
	DECLARE 
		xoutput text;
	BEGIN
	SELECT XMLSERIALIZE ( CONTENT
		 
		 XMLELEMENT(name "recentTests",
		 xmlagg(
			XMLELEMENT(name "test",
			XMLFOREST(
				t.test_id as "testId",
				t.test_name as "testName",
				u.group_id as "groupId"
				)
			)
		)
		)
		as text )
			INTO xoutput
			FROM tce_user_groups u, tce_testgroups tg, tce_tests t
				WHERE u.group_id  = tg.tstgrp_group_id
				AND u.external_id LIKE concat(externalid,':%') AND u.external_id != concat(externalid,':owner') AND u.email2_sent IS NULL
				AND t.test_id = tg.tstgrp_test_id AND t.test_end_time BETWEEN current_date - 8  AND current_date - 1;
			RETURN xoutput;
	END;
$BODY$
438
439
  LANGUAGE plpgsql VOLATILE SECURITY DEFINER
  SET search_path = tcexam, pg_temp;
440
ALTER FUNCTION get_tcexam_recent_tests(character varying) OWNER TO tcexam;
441

442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463

-- Function: set_tcexam_email_sent(character varying, integer)

-- DROP FUNCTION set_tcexam_email_sent(character varying, integer);

CREATE OR REPLACE FUNCTION set_tcexam_email_sent(groupid character varying, emailnumber integer)
  RETURNS text AS
$BODY$

DECLARE found text;

BEGIN
	EXECUTE 'SELECT email' || emailnumber || '_sent FROM tce_user_groups WHERE group_id =' || groupid  ||';' INTO found;
	IF found  > '' THEN	
		RETURN '<result>existing</result>';
	ELSE
		EXECUTE 'UPDATE tce_user_groups SET email' || emailnumber || '_sent = current_date WHERE group_id = ' || groupid || ';';
		RETURN '<result>new</result>';	
	END IF;	
END;

$BODY$
464
465
  LANGUAGE plpgsql VOLATILE SECURITY DEFINER
  SET search_path = tcexam, pg_temp;
466
ALTER FUNCTION set_tcexam_email_sent(character varying, integer)  OWNER TO tcexam;
467
468


469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510


-- Function: count_tcexam_users_by_exam(character varying, character varying)

-- DROP FUNCTION count_tcexam_users_by_exam(character varying, character varying);

CREATE OR REPLACE FUNCTION count_tcexam_users_by_exam(external_group_id character varying, external_appointment_id character varying)
  RETURNS text AS
$BODY$
        DECLARE
		xoutput text;
BEGIN

SELECT XMLSERIALIZE ( CONTENT
	 XMLELEMENT(name "Appointments",
	 xmlagg(
		XMLELEMENT(name "Appointment",
		XMLFOREST(
			u.external_id as "GroupId",
			u.lv as "LV",
			u.testdate as "TestDate",
			u.usercount as "UserCount"
			)
		)
	))
	as text )
		INTO xoutput
		FROM 
		(SELECT foo.external_id, split_part(foo.group_name,' Gruppe 0 - Prüfungstermin: ',1) as lv, split_part(foo.group_name,' Gruppe 0 - Prüfungstermin: ',2) as testdate, count(usrgrp_user_id) as UserCount FROM (
		SELECT * FROM tce_usrgroups g
		LEFT JOIN tce_user_groups ug ON ug.group_id = g.usrgrp_group_id
		WHERE ug.external_id LIKE CONCAT($1,':',$2) AND ug.external_id NOT LIKE ('%owner%')
		) 
		AS foo
		GROUP BY foo.external_id, foo.group_name
		ORDER BY foo.external_id) as u
		; 
	RETURN xoutput;
END;
	

$BODY$
511
512
  LANGUAGE plpgsql VOLATILE SECURITY DEFINER
  SET search_path = tcexam, pg_temp;
513
ALTER FUNCTION count_tcexam_users_by_exam(character varying, character varying)  OWNER TO tcexam;
514

515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536

-- Function: get_tcexam_groupid(character varying, character varying)

-- DROP FUNCTION get_tcexam_groupid(character varying, character varying);

CREATE OR REPLACE FUNCTION get_tcexam_groupid(pgroupid character varying, pappointmentid character varying)
  RETURNS text AS
$BODY$
DECLARE
	groupid text;        
BEGIN
SELECT XMLSERIALIZE ( CONTENT
	 
	XMLFOREST(
		group_id as "groupId")
	
	as text )
		INTO groupid
		FROM tce_user_groups WHERE external_id = CONCAT(pgroupid,':',pappointmentid) ;
	RETURN groupid;	
END;
$BODY$
537
538
  LANGUAGE plpgsql VOLATILE SECURITY DEFINER
  SET search_path = tcexam, pg_temp;
539
ALTER FUNCTION get_tcexam_groupid(character varying, character varying)  OWNER TO tcexam;
540

541

542
  
543
544
545
546
547
-- create esb user

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

-- grant execute right on the functions
548
549
--
-- # Generated with:
550
-- # 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
551
552

GRANT EXECUTE ON FUNCTION assign_tcexam_user_to_group(xmlbody text, pgroupid character varying, pappointmentid character varying) TO esb;
553
GRANT EXECUTE ON FUNCTION count_tcexam_users_by_exam(external_group_id character varying, external_appointment_id character varying) TO esb;
554
555
556
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;
557
558
GRANT EXECUTE ON FUNCTION get_tcexam_groupid(pgroupid character varying, pappointmentid character varying) TO esb;
GRANT EXECUTE ON FUNCTION get_tcexam_recent_tests(externalid character varying) TO esb;
559
GRANT EXECUTE ON FUNCTION get_tcexam_user(userid character varying) TO esb;
560
GRANT EXECUTE ON FUNCTION insert_tcexam_group(pgroupid character varying, pappointmentid character varying, pgroupname character varying) TO esb;
561
GRANT EXECUTE ON FUNCTION insert_tcexam_user(xmlbody text) TO esb;
562
GRANT EXECUTE ON FUNCTION remove_tcexam_user_from_group(xmlbody text, pgroupid character varying, pappointmentid character varying) TO esb;
563
GRANT EXECUTE ON FUNCTION set_tcexam_email_sent(groupid character varying, emailnumber integer) TO esb;
564
GRANT EXECUTE ON FUNCTION set_tcexam_user_level(login character varying, ulevel integer) TO esb;
565
GRANT EXECUTE ON FUNCTION update_tcexam_group(pgroupid character varying, pappointmentid character varying, pgroupname character varying) TO esb;
566
GRANT EXECUTE ON FUNCTION update_tcexam_user(xmlbody text) TO esb;
567