| -- Licensed to the Apache Software Foundation (ASF) under one or more |
| -- contributor license agreements. See the NOTICE file distributed with |
| -- this work for additional information regarding copyright ownership. |
| -- The ASF licenses this file to You under the Apache License, Version 2.0 |
| -- (the "License"); you may not use this file except in compliance with |
| -- the License. You may obtain a copy of the License at |
| -- |
| -- http://www.apache.org/licenses/LICENSE-2.0 |
| -- |
| -- Unless required by applicable law or agreed to in writing, software |
| -- distributed under the License is distributed on an "AS IS" BASIS, |
| -- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. |
| -- See the License for the specific language governing permissions and |
| -- limitations under the License. |
| |
| -- -------------------------------------------------------------------------------- |
| -- Procedure which shall remove duplicate entries from x_group table |
| -- Duplicate entries were previously created due to unavailablity of unique index |
| -- -------------------------------------------------------------------------------- |
| DELIMITER $$ |
| |
| DROP PROCEDURE if exists deleteXGroupDuplicateReferences $$ |
| CREATE PROCEDURE `deleteXGroupDuplicateReferences`() |
| BEGIN |
| Block1: BEGIN |
| |
| DECLARE donecursor1 INT; |
| DECLARE group_name1 varchar(1024); |
| DECLARE mingroupid1 bigint; |
| DECLARE id2 bigint; |
| |
| DECLARE cursor1 CURSOR FOR |
| SELECT group_name,min(id) FROM x_group GROUP BY group_name HAVING count(group_name)>1; |
| |
| DECLARE CONTINUE HANDLER FOR NOT FOUND SET donecursor1 = 1; |
| OPEN cursor1; |
| REPEAT |
| FETCH cursor1 into group_name1, mingroupid1; |
| Block2: BEGIN |
| DECLARE donecursor2 INT DEFAULT 0; |
| DECLARE cursor2 CURSOR FOR SELECT id FROM x_group WHERE group_name= group_name1 AND id > mingroupid1; |
| DECLARE CONTINUE HANDLER FOR NOT FOUND SET donecursor2 = 1; |
| OPEN cursor2; |
| REPEAT |
| FETCH cursor2 INTO id2; |
| UPDATE x_group_users SET p_group_id=mingroupid1 where p_group_id=id2; |
| UNTIL donecursor2 END REPEAT; |
| CLOSE cursor2; |
| END Block2; |
| UNTIL donecursor1 END REPEAT; |
| CLOSE cursor1; |
| END Block1; |
| |
| Block3: BEGIN |
| |
| DECLARE donecursor3 INT; |
| DECLARE group_name3 varchar(1024); |
| DECLARE user_id3 bigint; |
| DECLARE minrowid3 bigint; |
| |
| DECLARE cursor3 CURSOR FOR |
| SELECT group_name,user_id,min(id) FROM x_group_users GROUP BY group_name,user_id HAVING count(1)>1; |
| |
| DECLARE CONTINUE HANDLER FOR NOT FOUND SET donecursor3 = 1; |
| OPEN cursor3; |
| REPEAT |
| FETCH cursor3 into group_name3, user_id3, minrowid3; |
| DELETE FROM x_group_users WHERE group_name=group_name3 AND user_id=user_id3 AND id > minrowid3; |
| UNTIL donecursor3 END REPEAT; |
| CLOSE cursor3; |
| END Block3; |
| |
| Block4: BEGIN |
| |
| DECLARE donecursor4 INT; |
| DECLARE group_name4 varchar(1024); |
| DECLARE group_id4 bigint; |
| DECLARE minrowid4 bigint; |
| |
| DECLARE cursor4 CURSOR FOR |
| SELECT group_name,min(id) FROM x_group GROUP BY group_name HAVING count(1)>1; |
| |
| DECLARE CONTINUE HANDLER FOR NOT FOUND SET donecursor4 = 1; |
| OPEN cursor4; |
| REPEAT |
| FETCH cursor4 into group_name4, minrowid4; |
| DELETE FROM x_group WHERE group_name=group_name4 AND id > minrowid4; |
| UNTIL donecursor4 END REPEAT; |
| CLOSE cursor4; |
| END Block4; |
| |
| END $$ |
| DELIMITER ; |
| call deleteXGroupDuplicateReferences(); |
| |
| DROP PROCEDURE if exists deleteXGroupDuplicateReferences; |