zAGikJRe

1/*DROP TABLE IF EXISTS `update_statements`;
2create table update_statements (
3commands varchar(1024)
4);*/
5
6-- ------------------- UAA TO LDAP PROCEDURE FOR CLOUD FOUNDRY ---------------------------------------------------------
7DELIMITER $$
8DROP PROCEDURE IF EXISTS UAA_MIG$$
9CREATE PROCEDURE UAA_MIG()
10Begin
11DECLARE done INT DEFAULT FALSE;
12DECLARE var_id char(36);
13DECLARE var_external_id varchar(255);
14DECLARE loop_done INT DEFAULT 0;
15DECLARE loop_users CURSOR for 
16	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 (
17		select id from users where username like "%@myorg.com" or username like "%@externalcompany.com" or username like "%@myorg-sub1.com");
18DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
19select count(id) into @nb_users_ldap_before from users where origin="ldap";
20select count(id) into @nb_users_uaa_before from users where origin="uaa";
21Open loop_users;
22read_loop: Loop 
23SET done = FALSE ;
24FETCH loop_users INTO var_id, var_external_id;
25IF done THEN
26	LEAVE read_loop;
27END IF;
28--  update command
29 update users set origin="ldap", external_id=concat("uid=", var_external_id ,",ou=people,dc=myorg,dc=com") where users.id=var_id;
30-- solution with temp table  
31-- 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,"\""));
32END LOOP;
33CLOSE loop_users;
34
35select count(id) into @nb_users from users;
36select count(id) into @nb_users_ldap_after from users where origin="ldap";
37select count(id) into @nb_users_uaa_after from users where origin="uaa";
38select concat ('Before Migration : ', @nb_users_uaa_before, ' / ', @nb_users ,' | After Migration : ', @nb_users_uaa_after, ' / ', @nb_users ) as 'NB of users <UAA>';
39select concat ('Before Migration ', @nb_users_ldap_before, ' / ', @nb_users , ' | After Migration : ', @nb_users_ldap_after, ' / ', @nb_users) as 'NB of users <LDAP>';
40END;
41$$
42DELIMITER ;
43call UAA_MIG();
44-- END ------------------- Procedure pour la migration---------------------------------------------------------
45
46DROP PROCEDURE IF EXISTS UAA_MIG;
47-- drop table update_statements;