Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- /*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;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement