add user to all projects under a business_group

join_all_projects.sql

DROP FUNCTION IF EXISTS `join_all_projects`;
DELIMITER $$

CREATE DEFINER = `cloudchef`@`%` FUNCTION `join_all_projects`(`tenant_id` varchar(64),`business_group_name` varchar(225),`user_name` varchar(225))
 RETURNS int(4)
BEGIN
	DECLARE projectCount int;
	DECLARE done INT DEFAULT FALSE;
	DECLARE user_id varchar(64);
	DECLARE proj_id varchar(64);
	DECLARE cursor_i CURSOR FOR SELECT p.id FROM project p, business_group bg
		WHERE bg.`name` = business_group_name AND p.business_group_id = bg.id AND p.tenant_id = tenant_id;
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
	
	set projectCount = 0;
	
	select id into user_id from `user` where `user`.`name` = user_name;

  OPEN cursor_i;
  read_loop: LOOP
    IF done THEN
      LEAVE read_loop;
    END IF;

    FETCH cursor_i INTO proj_id;

    INSERT INTO role_mapping(`id`,
			`created_by`,
			`created_at`, 
			`deleted`, 
			`deleted_timestamp`, 
			`exts`, 
			`lock_version`, 
			`updated_by`, 
			`updated_at`, 
			`tenant_id`, 
			`entity_instance_id`,
			`role_id`,
			`user_id`)
		VALUES (UUID(), 
			'ROLE_SOLUTION_USER', 
			now(), 
			b'0', 
			'0', 
			'{}', 
			'0', 
			'ROLE_SOLUTION_USER', 
			now(), 
			tenant_id,
			proj_id, 
			'ROLE_GROUP_USER', 
			user_id);
			set projectCount = projectCount +1;
		END LOOP;
	CLOSE cursor_i;

	RETURN projectCount;	#Routine body goes here...
END;
$$