CF_UaaToLDAP.sql

/*DROP TABLE IF EXISTS `update_statements`;
create table update_statements (
commands varchar(1024)
);*/

-- ------------------- UAA TO LDAP PROCEDURE FOR CLOUD FOUNDRY ---------------------------------------------------------
DELIMITER $$
DROP PROCEDURE IF EXISTS UAA_MIG$$
CREATE PROCEDURE UAA_MIG()
Begin
DECLARE done INT DEFAULT FALSE;
DECLARE var_id char(36);
DECLARE var_external_id varchar(255);
DECLARE loop_done INT DEFAULT 0;
DECLARE loop_users CURSOR for 
	select id, external_id from users where username not like "user2@myorg.com" and username not like "user1.%@myorg.com" and username not like "user3@myorg.com" and users.id in (
		select id from users where username like "%@myorg.com" or username like "%@externalcompany.com" or username like "%@myorg-sub1.com");
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
select count(id) into @nb_users_ldap_before from users where origin="ldap";
select count(id) into @nb_users_uaa_before from users where origin="uaa";
Open loop_users;
read_loop: Loop 
SET done = FALSE ;
FETCH loop_users INTO var_id, var_external_id;
IF done THEN
	LEAVE read_loop;
END IF;
--  update command
 update users set origin="ldap", external_id=concat("uid=", var_external_id ,",ou=people,dc=myorg,dc=com") where users.id=var_id;
-- solution with temp table  
-- insert into update_statements values (concat("update users set origin=\'ldap\', external_id=\"uid=", var_external_id ,",ou=people,dc=myorg,dc=com\" where users.id=\"", var_id,"\""));
END LOOP;
CLOSE loop_users;

select count(id) into @nb_users from users;
select count(id) into @nb_users_ldap_after from users where origin="ldap";
select count(id) into @nb_users_uaa_after from users where origin="uaa";
select concat ('Before Migration : ', @nb_users_uaa_before, ' / ', @nb_users ,' | After Migration : ', @nb_users_uaa_after, ' / ', @nb_users ) as 'NB of users <UAA>';
select concat ('Before Migration ', @nb_users_ldap_before, ' / ', @nb_users , ' | After Migration : ', @nb_users_ldap_after, ' / ', @nb_users) as 'NB of users <LDAP>';
END;
$$
DELIMITER ;
call UAA_MIG();
-- END ------------------- Procedure pour la migration---------------------------------------------------------

DROP PROCEDURE IF EXISTS UAA_MIG;
-- drop table update_statements;