postgres_db_structure_functions.sql

-- modifying the schema to allow an external_id

ALTER TABLE tce_user_groups ADD COLUMN external_id varchar(255);

-- 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);

-- 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);

-- modifying the schema to assign group to test
ALTER TABLE tce_user_groups ADD COLUMN group_type character varying (5) DEFAULT 'T';
ALTER TABLE tce_tests ADD COLUMN test_group_id bigint;

-- 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;

-- functions for administrative group
-- Function: get_admin_group(bigint, bigint)

-- DROP FUNCTION get_admin_group(bigint, bigint);

-- Function: get_admin_group(bigint, bigint)

-- DROP FUNCTION get_admin_group(bigint, bigint);

CREATE OR REPLACE FUNCTION get_admin_group(itest_id bigint, igroup_id bigint)
  RETURNS bigint AS
$BODY$
DECLARE xgroup_id bigint;
BEGIN
	SELECT group_id FROM tce_user_groups
		WHERE external_id = (
			SELECT LEFT(external_id,strpos(external_id,':')-1)
				FROM tce_user_groups
				WHERE group_id = igroup_id ) || ':owner'
		AND group_type = 'B'
		INTO xgroup_id;
		IF xgroup_id IS NULL THEN
			xgroup_id = igroup_id;
		END IF;
RETURN xgroup_id;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE SECURITY DEFINER
  COST 100;
ALTER FUNCTION get_admin_group(bigint, bigint) SET search_path=tcexam, pg_temp;

ALTER FUNCTION get_admin_group(bigint, bigint)
  OWNER TO tcexam;

-- Function: set_admin_group(bigint, bigint)

-- DROP FUNCTION set_admin_group(bigint, bigint);

CREATE OR REPLACE FUNCTION set_admin_group(itest_id bigint, igroup_id bigint)
  RETURNS void AS
$BODY$
BEGIN
	IF NOT EXISTS (SELECT * FROM tce_testgroups WHERE tstgrp_group_id = (SELECT * FROM get_admin_group(itest_id, igroup_id)) AND tstgrp_test_id = itest_id)
	THEN
	INSERT INTO tce_testgroups (
		tstgrp_test_id,
		tstgrp_group_id
		) VALUES ( itest_id, (SELECT * FROM get_admin_group(itest_id, igroup_id)));
	END IF;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE SECURITY DEFINER
  COST 100;
ALTER FUNCTION set_admin_group(bigint, bigint) SET search_path=tcexam, pg_temp;

ALTER FUNCTION set_admin_group(bigint, bigint)
  OWNER TO tcexam;







-- functions for tcexam esb integration

-- 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;
	studykey character varying;
	uservo xml; 
        
    BEGIN

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

	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;

	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;

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 assign_tcexam_user_to_group(text, character varying, character varying)
  OWNER TO tcexam;


--  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
  SECURITY DEFINER
  -- Set a secure search_path: trusted schema(s), then 'pg_temp'.
  SET search_path = tcexam, pg_temp;
ALTER FUNCTION get_tcexam_group(character varying)
  OWNER TO tcexam;


-- 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",
		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 )
		INTO xoutput
		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)
		;
	RETURN xoutput;
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 get_tcexam_group_appointment_users(character varying, character varying)
  OWNER TO tcexam;


-- 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",
		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,':%');

$BODY$
  LANGUAGE sql VOLATILE
  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;

-- get tcexam_user
CREATE OR REPLACE FUNCTION get_tcexam_user(userid character varying)
 RETURNS text 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$
 LANGUAGE plpgsql VOLATILE
 SECURITY DEFINER
 SET search_path TO tcexam, pg_temp;
ALTER FUNCTION get_tcexam_user(character varying)
  OWNER TO tcexam;  

-- insert_tcexam_group

CREATE OR REPLACE FUNCTION insert_tcexam_group(pgroupid character varying, pappointmentid character varying, pgroupname character varying)
  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
  SECURITY DEFINER
  -- Set a secure search_path: trusted schema(s), then 'pg_temp'.
  SET search_path = tcexam, pg_temp;
ALTER FUNCTION insert_tcexam_group(character varying, character varying, character varying)
  OWNER TO tcexam;

 
-- insert_tcexam_user

CREATE OR REPLACE FUNCTION insert_tcexam_user(xmlbody text)
 RETURNS void AS 
$function$
    DECLARE
	login character varying;
	email character varying;
	firstname character varying;
	lastname character varying;
	matrikelnr 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;
		SELECT (xpath('/userVO/properties/property/name[.="institutionalUserIdentifier"]/../value/text()', uservo))[1] 		into matrikelnr;

		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_regnumber, user_level, user_regdate, user_password, user_ip) 
				VALUES  (login,email,firstname,lastname, matrikelnr, 1, now(), 'no£password', '127.0.0.1');
				INSERT INTO tce_usrgroups (usrgrp_user_id,usrgrp_group_id) VALUES ((SELECT user_id FROM tce_users WHERE user_name = login),1);
		END IF;

	END;
$function$
 LANGUAGE plpgsql VOLATILE
 SECURITY DEFINER
 SET search_path TO tcexam, pg_temp;
ALTER FUNCTION insert_tcexam_user(text)
  OWNER TO tcexam;


-- 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
  SECURITY DEFINER
  -- Set a secure search_path: trusted schema(s), then 'pg_temp'.
  SET search_path = tcexam, pg_temp;
ALTER FUNCTION remove_tcexam_user_from_group(text, character varying, character varying)
  OWNER TO tcexam;


-- 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;


-- 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
  SECURITY DEFINER
  -- Set a secure search_path: trusted schema(s), then 'pg_temp'.
  SET search_path = tcexam, pg_temp;
ALTER FUNCTION update_tcexam_group(character varying, character varying, character varying)
  OWNER TO tcexam;
  
  
-- update_tcexam_user

CREATE OR REPLACE FUNCTION update_tcexam_user(xmlbody text)
 RETURNS void AS 
$function$
    DECLARE
	login character varying;
	email character varying;
	firstname character varying;
	lastname character varying;
	matrikelnr character varying;
	userstatus character varying;
	userlevel integer;
	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;
		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')
		THEN
			UPDATE tce_users SET user_level = 0 WHERE user_name = login;
		END IF;

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

	END;

$function$

 LANGUAGE plpgsql VOLATILE
 SECURITY DEFINER
 SET search_path TO tcexam, pg_temp;
ALTER FUNCTION update_tcexam_user(text)
  OWNER TO tcexam;

-- 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$
  LANGUAGE plpgsql VOLATILE SECURITY DEFINER
  SET search_path = tcexam, pg_temp;
ALTER FUNCTION get_tcexam_recent_tests(character varying) OWNER TO tcexam;


-- 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$
  LANGUAGE plpgsql VOLATILE SECURITY DEFINER
  SET search_path = tcexam, pg_temp;
ALTER FUNCTION set_tcexam_email_sent(character varying, integer)  OWNER TO tcexam;




-- 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$
  LANGUAGE plpgsql VOLATILE SECURITY DEFINER
  SET search_path = tcexam, pg_temp;
ALTER FUNCTION count_tcexam_users_by_exam(character varying, character varying)  OWNER TO tcexam;


-- 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$
  LANGUAGE plpgsql VOLATILE SECURITY DEFINER
  SET search_path = tcexam, pg_temp;
ALTER FUNCTION get_tcexam_groupid(character varying, character varying)  OWNER TO tcexam;


  
-- create esb user

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

-- grant execute right on the functions
--
-- # 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 count_tcexam_users_by_exam(external_group_id character varying, external_appointment_id 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 get_tcexam_groupid(pgroupid character varying, pappointmentid character varying) TO esb;
GRANT EXECUTE ON FUNCTION get_tcexam_recent_tests(externalid character varying) TO esb;
GRANT EXECUTE ON FUNCTION get_tcexam_user(userid character varying) TO esb;
GRANT EXECUTE ON FUNCTION insert_tcexam_group(pgroupid character varying, pappointmentid character varying, pgroupname character varying) TO esb;
GRANT EXECUTE ON FUNCTION insert_tcexam_user(xmlbody text) 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 set_tcexam_email_sent(groupid character varying, emailnumber integer) TO esb;
GRANT EXECUTE ON FUNCTION set_tcexam_user_level(login character varying, ulevel integer) 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) TO esb;